数据库的基本概念#

1. ⭐数据库、DBMS、数据库系统

   概念	含义
   数据库 DB	长期存储、有组织、可共享的大量数据的集合
   数据库管理系统 DBMS	管理数据库的软件，位于应用程序和数据库之间（如 MySQL、Oracle、SQLSever）
   **数据库系统 DBS **	数据库、DBMS、应用程序、数据库管理员、硬件等构成的整体
   数据库应用系统	面向具体业务开发的应用程序及其数据库环境 = 数据库 + DBMS + 应用程序 + 数据库管理员 +硬件环境

2. ⭐文件系统 vs 数据库

   对比角度	文件方式	数据库方式
   分散/统一	数据分散在不同文件	数据统一组织和管理
   共享	共享困难	支持多用户共享
   冗余	冗余较多	可以控制冗余
   耦合/独立	程序与数据耦合较强	具有数据独立性
   性质	一致性、安全性难保证	DBMS 统一保证完整性、安全和并发
   适用场景	适合简单、独立的小规模数据	适合长期、共享、结构化业务数据

数据模型的组成要素 & 常用数据模型#

1. ⭐数据模型#

   • 两大类、共三种数据模型：概念数据模型、 逻辑数据模型、物理数据模型
   • 数据模型的组成要素：数据结构、数据操作、数据约束 （二维关系表、原则/插入等、实体完整性）

   • ⭐关系模型基本概念#

     

     

数据库的三级模式#

1. 从数据库应用开发人员角度看，数据库通常采用 三级模式结构

   层次	描述内容
   外模式	某一类用户看到的局部数据，也叫用户视图
   模式/概念模式	整个数据库的全局逻辑结构
   内模式	数据的物理存储结构、文件组织和存取路径

   

两种数据独立性#

1. 逻辑数据独立性：概念模式改变时，外模式和应用程序尽量不用改变。
   例如增加一个属性，只需调整外模式与概念模式之间的映像，原有应用仍可使用原来的视图。
2. 物理数据独立性：内模式改变时，概念模式和应用程序不用改变。
   例如把顺序文件改成 B+ 树索引，应用程序仍然执行相同的 SQL。

⭐关系的完整性约束#

1. 实体完整性
   主码属性不能为空 NULL
2. 参照完整性
   1. 外码值要么为空，要么等于被参照关系的主码值
   2. 外码：
      参照关系：R（拿着外码的表，比如学生表）
      被参照关系：S（被引用主键的表，比如专业表）
      

      

      
3. 用户定义的完整性

⭐码与相关概念#

1. 超码#

   能 唯一标识 元组的任意属性集。

   • 关系：候选码是最小超码，候选码的任意超集都是超码。
   • 例如：S(Sno, Sdept, Sage)，若：Sno → Sno, Sdept, Sage，
     {Sno}是候选码 也是超码，(Sno, Sdept)也是超码。

2. 候选码（码）#

   能够唯一标识元组的 最小 超码
   {Sno}是候选码

3. 全码#

   如果关系的所有属性U才是R的码，称为全码
   例：$R(P,W,A)$（演奏者、作品、听众），全码为 $(P,W,A)$。P, W, A 都是关系R的主属性，在关系R中没有非主属性

4. 主码#

   如果一个关系模式有多个候选码，则从中选一个作为主码。

   • 例如：Student(Sno, Sname, Ssex, Sage, Sdept)：Sno 是候选码，Sname 也是候选码。
       → 可以选 Sno 作为主码。

5. 外码#

   R中 $X$ 不是当前关系R的码，是另一关系的码，则 $X$ 为 $R$ 的外码。
   例：$SC(<u>Sno,Cno</u>,Grade)$ 中 Sno不是码，Sno是 S(Sno,Sdept,Sage)的码，
     则 $Sno$ 是 $SC$ 的外码
     FOREIGN KEY (deptno) REFERENCES Department(deptno)
     外码的作用是：建立表之间的引用联系，并保证参照完整性。

• 主属性与非主属性#

  • 主属性：包含在任意候选码中的属性。
  • 非主属性：不包含在任何候选码中的属性。

⭐C2 关系代数#

• 关系运算 选择：$\sigma_{Sdept='IS'}(Student)$、投影：$\pi_{Sdept}(Student)$

• 例

  • 所有：除运算÷；只：

• 最大/最高 在当前的所有顾客中, 查询折扣(discnt)最高的顾客的编号？
  令 $S := C$, “折扣不是最高的顾客的id”
  $R_2 := \pi_{C.cid}(\sigma_{C.discnt < S.discnt}(C \times S))$
  答案是 $\pi_{cid}(C) - \pi_{C.cid}(\sigma_{C.discnt < S.discnt}(C \times S))$

  

  
  

  

• 象集
  该属性值对应的其他属性值集合，相当于除法

  

  

1. 除运算 R ÷ S
   找出R、S中相同的属性列(B、C)，R 剩下的属性列(A) 作为结果。
   $a_i$的所有($b_j$,$c_j$)，$a_i$包含S中的全部b、c。

   

   
   • 例
   

   

对查询结果的处理#

1. 对查询结果排序 ORDER BY 属性名 ASC升序/DESC降序 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

   1
   SELECT Sno, Grade
   2
   FROM SC
   3
   WHERE Cno= '3'
   4
   ORDER BY Grade DESC;
   

2. 对查询结果分组 GROUP BY/HAVING 求各个课程号及相应的选课人数。

   1
   SELECT Cno，COUNT(Sno)
   2
   FROM SC
   3
   GROUP BY Cno; // 按照Cno为一行,相同Cno为一行
   

   查询选修了3门以上课程的学生学号。

   1
   SELECT Sno
   2
   FROM SC
   3
   GROUP BY Sno
   4
   HAVING COUNT(*) >3;
   

   • 辨析 HAVING 与 WHERE：只有HAVING可以用AVG()等

基本表 TABLE#

1. 定义基本表
   PRIMARY KEY 主键，唯一标识一行数据，不能重复、不能为空
   UNIQUE 唯一，不能重复，但能为空
   外键 FOREIGN KEY (cno) PREFERENCE course(cno) ON UPDATE/DELETE 规约条件

   1
   CREATE TABLE Student
   2
       (Sno CHAR(9) PRIMARY KEY,  /* 列级完整性约束条件, Sno是**主码**/
   3
       Sname CHAR(20) UNIQUE,  /* **约束**，Sname的取值具有唯一性*/
   4
       Ssex CHAR(2) NOT NULL CHECK (Ssex IN ('男', '女')),/*  非空且只能是男/女 */
   5
       Sage SMALLINT,
   6
       Sdept CHAR(20)
   7
       );
   

   1
   CREATE TABLE enrollment(
   2
       sno char(9) NOT NULL,
   3
       clsno char(8) NOT NULL,
   4
       grade INT CHECK (grade BETWEEN 0 AND 100),
   5
       PRIMARY KEY(sno,clsno), -- 课程班代码和学号联合构成PRIMARY KEY
   6
       );
   

   1
   CREATE TABLE courseclass(
   2
   clsno char(8) PRIMARY KEY,
   3
   cno char(8) NOT NULL,
   4
   tno char(9) ,
   5
   -- 先定义再外键 + 违约规则
   6
   FOREIGN KEY (cno) REFERENCES coures(cno) ON UPDATE CASCADE ON DELETE RESTRICT,
   7
   FOREIGN KEY (tno) REFERENCES teachers(tno) ON UPDATE CASCADE ON DELETE SET NULL
   8
   -- 父表更新cno，子表cno自动同步更新；父表删除cno，子表还有该课程就禁止删除
   9
   -- 父表更新tno，子表tno自动同步更新；父表删除tno，子表对应的tno自动设为NULL
   10
   );
   

   • 违约规则
     什么是违约？ 父表更新主键/删除父表记录会破坏参照关系，触发违约

     关键字	中文	含义
     CASCADE	级联	父表改 / 删 → 子表自动跟着改 / 删
     SET NULL	置空	父表删 → 子表外键自动设为 NULL
     RESTRICT	限制 / 拒绝	数据库直接禁止父表的改/删操作

2. 修改基本表（ALTER TABLE）

   • ADD新增列/完整性约束

     1
     ALTER TABLE Student
     2
     ADD COLUMN S_email VARCHAR(50) NOT NULL UNIQUE;  -- 增加邮箱列，要求非空且唯一
     3
     ADD CONSTRAINT chk_sex CHECK (Ssex IN ('男', '女'));  -- 为 Ssex 增加 CHECK 约束（性别只能是男 / 女）
     

   • DROP 删除列

     1
     ALTER TABLE Student
     2
     DROP COLUMN S_email CASCADE; --删除“邮箱”列，默认RESTRICT
     3
     DROP CONSTRAINT chk_sex; -- 删除性别CHECK约束
     

   • 修改列的定义

     1
     ALTER TABLE Student
     2
     ALTER COLUMN Sage INT;  -- 修改数据类型：年龄从SMALLINT改为INT
     3
     ALTER COLUMN Sname CHAR(30); -- 修改列长度（姓名从 CHAR (20) 改为 CHAR (30)）
     4
     ALTER TABLE Course ADD UNIQUE(Cname); -- 增加课程名称必须取唯一值的约束条件
     

3. 删除基本表

   1
   DROP TABLE <表名>［RESTRICT| CASCADE］;
   

   • RESTRICT：只有在该表没有被其他数据库对象依赖时，才能删除。
   • CASCADE：删除该表的同时，还会删除依赖于该表的相关对象或依赖关系

视图 VIEW#

1. 视图的作用？
   简化复杂查询；
   为不同用户提供不同数据视图；
   隐藏敏感属性，提高安全性；
   阻止不符合条件的数据加入/修改成不符合条件的数据。
2. 定义视图 AS

   1
   CREATE VIEW S_G(Sno,Gavg) -- 可选：列名
   2
   AS
   3
    SELECT Sno,AVG(Grade)
   4
    FROM SC
   5
    GROUP BY Sno;
   

3. 查询视图
4. 更新视图
5. 删除视图 DROP VIEW S_G [RESTRICT | CASCADE]

4. 字符串匹配
   • ⭐字符匹配 LIKE/=

   1
   SELECT* FROM Student WHERE Sno LIKE '24%'  -- 通配符，查询学号以24开头的学生的详细信息
   2
   SELECT* FROM Student WHERE Sno = '24%' -- 查询学号就是'24%'学生的详细信息
   

   • ⭐字符串中的通配符

   1
   SELECT Sname, Sno, Ssex
   2
   FROM Student
   3
   WHERE Sname LIKE '刘%';  -- 查询所有姓刘学生的姓名、学号和性别
   4
   
   5
   SELECT Sname
   6
   FROM Student
   7
   WHERE Sname LIKE '欧阳_'; -- 查询姓"欧阳"且全名为三个汉字的学生的姓名
   8
   
   9
   SELECT Sname, Sno
   10
   FROM Student
   11
   WHERE Sname LIKE '_阳%'; -- 查询名字中第2个字为"阳"字的学生的姓名和学号
   12
   
   13
   SELECT Sname, Sno, Ssex
   14
   FROM Student
   15
   WHERE Sname NOT LIKE '刘%'; -- 查询所有不姓刘的学生姓名、学号和性别
   

5. 多表连接 JOIN

   1
   SELECT DISTINCT s.sid, s.sname
   2
   FROM Student s
   3
   JOIN Enroll e ON s.sid = e.sid
   4
   JOIN Course c ON e.cid = c.cid
   5
   WHERE c.cname = '数据管理基础'
   6
   AND c.dept = '智软学院';
   

   使用 DISTINCT 是为了消除同一学生由于多条匹配记录产生的重复
   • DISTINCT 的位置：SELECT 子句；聚集函数内部。

     1
     SELECT DISTINCT dept
     2
     FROM Student;
     

     1
     COUNT(DISTINCT sid)
     2
     AVG(DISTINCT score)
     3
     SUM(DISTINCT amount)
     

6. 分组与 HAVING先Group 分组后 HAVING
   
7. EXISTS 子查询有结果，返回TRUE；子查询无结果，返回FALSE

   1
   SELECT Sname
   2
   FROM Student S
   3
   WHERE EXISTS (
   4
       SELECT *
   5
       FROM SC
   6
       WHERE SC.Sno = S.Sno
   7
       AND Cno = '1' -- 从 Student 表里，找出那些‘存在’选修 1 号课程记录的学生
   8
   ); -- 查询选修了 1 号课程的学生
   9
   
   10
   SELECT Sname
   11
   FROM Student S
   12
   WHERE NOT EXISTS (
   13
       SELECT *
   14
       FROM SC
   15
       WHERE SC.Sno = S.Sno
   16
       AND Cno = '1'
   17
   ); -- 查询没选1号课程的学生
   

   • 查询选修全部课程的学生

     1
     SELECT Sname
     2
     FROM Student
     3
     WHERE NOT EXISTS ( -- ③ 除去没有选择课程的学生
     4
         SELECT *
     5
         FROM Course -- ② 有NOT EXISTS学生选择的课程中的：学生没有选择的课程
     6
         WHERE NOT EXISTS (
     7
             SELECT *
     8
             FROM SC
     9
             WHERE SC.Sno = Student.Sno
     10
             AND SC.Cno = Course.Cno -- ① 对SC中的每门课程，去Student、Course中寻找有无记录
     11
         ) -- 学生选择的课程
     12
     );
     13
     ✅ 理解：不存在一门课是这个学生没选的
     

   • 例
   

   
   

   

⭐例题#

1. 所有课程都及格：HAVING MIN(grade)>=60
   
2. 修完本院所有必修课
   
3. 每组最大值
   
4. 只讲授本院课程

嵌入式SQL#

1. 主变量：能在SQL中使用的主语言变量
   • 必须在声明区声明

   1
   -- 声明区
   2
   EXEC SQL BEGIN DECLARE SECTION;
   3
   char givensno[10];
   4
   char hsname[21];
   5
   int hsage;
   6
   EXEC SQL END DECLARE SECTION;
   7
   -- 声明区
   8
   EXEC SQL select Sno, Cno, Grade
   9
       into :hsno, :hcno, :hgrade, :gradeid -- 指示变量
   10
       from Student
   11
       where Sno = :givensno ; -- 查找给定学号的学生的信息
   

2. 指示变量 gradeid：说明成绩是否为空

   取值	含义
   0	返回的是正常非空值
   大于 0	字符串被截断
   -1	数据库值为 NULL

   • 用指示变量写入NULL：把成绩置为空

     1
     gradeIndicator = -1;
     2
     
     3
     EXEC SQL UPDATE SC
     4
     SET Grade = :hgrade :gradeIndicator -- 实际写入的是 grade = NULL
     5
     WHERE Sno = :hsno
     6
     AND Cno = :hcno;
     

3. 通信区 SQLCA：记录每条SQL执行后的状态信息
   SQLCODE = 0：执行成功；
   非0：没有找到数据、警告或者错误。

   1
   if (SQLCA.SQLCODE != 0) {
   2
       break;
   3
   } -- 前一条执行SQL语句，然后检查：若不为0 → 退出
   

4. ⭐游标 Cursor#

   游标是系统为查询结果建立的 数据缓冲区 和 当前位置指针，用于存放SQL查询的执行结果。解决SQL批量返回数据、主语言单条处理之间的矛盾

   • 声明游标 EXEC SQL DECLARE 游标名 CURSOR FOR 查询语句;
   • 打开游标 EXEC SQL OPEN 游标名;
   • 提取数据 EXEC SQL FETCH 游标名 INTO 主变量;
   • 关闭游标 EXEC SQL CLOSE 游标名;

• 例 依次检查某个系的学生记录，交互式更新某些学生年龄。

  1
  EXEC SQL BEGIN DECLARE SECTION; /*主变量声明开始*/
  2
      char Deptname[21];
  3
      char Hsno[10];
  4
      char Hsname[21];
  5
      char Hssex[3];
  6
      int HSage;
  7
      int NEWAGE;
  8
  EXEC SQL END DECLARE SECTION; /*主变量声明结束*/
  9
  
  10
  long SQLCODE;
  11
  EXEC SQL INCLUDE SQLCA; /*定义SQL通信区*/
  12
  
  13
  #include <sqlca.h>
  14
  
  15
  int main(void) /*C语言主程序开始*/
  16
  {
  17
      int count = 0;
  18
      char yn; /*变量yn代表yes或no*/
  19
      printf("Please choose the department name(CS/MA/IS): ");
  20
      scanf("%s",deptname); /*为主变量deptname赋值*/
  21
      EXEC SQL CONNECT TO TEST@localhost:54321 USER "SYSTEM"/"MANAGER"; /*连接数据库TEST*/
  22
      -- 我现在要登录连接到数据库，数据库地址是本机，端口是 54321，数据库名字叫 TEST，登录账号是 SYSTEM，密码是 MANAGER
  23
      EXEC SQL DECLARE SX CURSOR FOR /*定义游标SX*/
  24
          SELECT Sno,Sname,Ssex,Sage /*游标SX对应的查询语句*/
  25
          FROM Student
  26
          WHERE SDept = :deptname;
  27
      EXEC SQL OPEN SX;
  28
  /*打开游标SX，执行游标对应的查询，并指向查询结果的第一行*/
  29
  
  30
      for (;;) -- 循环读取游标里的每一条数据，直到游标读完 / 出错时退出
  31
      {
  32
          /* 推进游标，将当前数据放入主变量 */
  33
          EXEC SQL FETCH SX INTO :Hsno, :Hsname, :Hssex, :HSage;
  34
          if (sqlca.sqlcode != 0) { -- 操作不成功时退出
  35
              break;
  36
          }
  37
  
  38
          /* 是第一行的话，打印表头和学生信息 */
  39
          if (count++ == 0) {
  40
              printf("\n%-10s %-20s %-10s %-10s\n", "Sno", "Sname", "Ssex", "Sage");
  41
          }
  42
  
  43
          /* 打印当前学生信息 */
  44
          printf("%-10s %-20s %-10s %-10d\n", Hsno, Hsname, Hssex, HSage);
  45
  
  46
          /* 询问用户是否要更新该学生的年龄 */
  47
          printf("UPDATE AGE(y/n)? ");
  48
          do {
  49
              scanf("%c", &yn);
  50
              -- getchar();  --吃掉多余的换行符
  51
          } while (yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
  52
          -- 强制让用户必须输入 Y/y/ N/n 才往下走，输错就一直重新输
  53
  
  54
          /* 如果选择更新操作 */
  55
          if (yn == 'y' || yn == 'Y') {
  56
              printf("INPUT NEW AGE: ");
  57
              scanf("%d", &NEWAGE);
  58
              getchar();
  59
  
  60
              /* 嵌入式SQL更新语句*/
  61
              EXEC SQL UPDATE Student
  62
                  SET Sage = :NEWAGE  -- Sage更新为读入的NWEAGE
  63
                  WHERE CURRENT OF SX;
  64
          } /* 对当前游标指向的学生年龄进行更新 */
  65
      }
  66
  
  67
      /* 收尾 */
  68
      EXEC SQL CLOSE SX; -- 关闭游标SX，不再和查询结果对应
  69
      EXEC SQL COMMIT WORK; -- 提交更新
  70
      EXEC SQL DISCONNECT TEST; -- 断开数据库连接
  71
  } -- 只在 EXEC SQL FETCH SX INTO 语句之后检查了状态码 SQLCA.SQLCODE
  72
  -- 在其他嵌入式SQL语句之后，都没有检查SQL语句的执行状态码！
  73
  -- Solution：添加异常处理👇
  

动态SQL#

过程化SQL#

⭐触发器#

1. **什么是触发器？**当某张表发生插入、删除或修改时，数据库自动执行一段事先写好的程序。
   学生成绩提高超过10%时，自动记录日志；
   插入教师信息时，如果教授工资低于4000，自动改成4000；
   删除数据时，自动备份被删除的数据。

2. 格式 只要修改就会触发，满足条件才执行后续操作

   1
   CREATE TRIGGER 触发器名
   2
   BEFORE | AFTER
   3
   INSERT | UPDATE | DELETE(OR 其他操作)
   4
   ON 表名
   5
   FOR EACH ROW
   6
   BEGIN
   7
       -- 触发后自动执行的语句
   8
   END;
   9
   ……
   

• 例1：成绩提高10%时记录日志

  1
  CREATE TRIGGER SC_T -- 创建名为 SC_T 的触发器
  2
  AFTER UPDATE OF Grade ON SC -- SC 中的 Grade 修改完成后，触发该触发器
  3
  REFERENCING
  4
      OLD ROW AS OldTuple,  -- 代表修改前的旧记录
  5
      NEW ROW AS NewTuple   -- 代表修改后的新记录
  6
  FOR EACH ROW              -- 每修改一行执行一次
  7
  WHEN (NewTuple.Grade >= 1.1 * OldTuple.Grade) -- 新成绩比旧成绩至少提高10%，触发器才会执行下面的操作
  8
  INSERT INTO SC_U(Sno, Cno, OldGrade, NewGrade)
  9
  VALUES(         -- 把下面信息插入日志表 SC_U
  10
      OldTuple.Sno,
  11
      OldTuple.Cno,
  12
      OldTuple.Grade,
  13
      NewTuple.Grade
  14
  );
  

• 例2：教授工资不能低于4000
  插入/修改教师信息时，如果该教师是教授，并且工资低于4000，就自动把工资改成4000。

  1
  CREATE TRIGGER Insert_Or_Update_Sal
  2
  BEFORE INSERT OR UPDATE ON Teacher
  3
  REFERENCING NEW ROW AS newTuple
  4
  FOR EACH ROW
  5
  BEGIN
  6
      IF newTuple.Job = '教授'
  7
      AND newTuple.Sal < 4000 THEN
  8
          newTuple.Sal := 4000;
  9
      END IF;
  10
  END;
  

函数#

1. IS：声明部分：接下来存放查询结果的 局部变量、常量、游标等

• 例：计算总输入

  1
  CREATE OR REPLACE FUNCTION totalsal -- 创建/替换一个名为totalsal的函数
  2
  (
  3
      v_empno IN emp.empno%TYPE  -- 调用者输入
  4
  )
  5
  RETURN NUMBER  -- 函数最终返回一个 NUMBER 类型的数值
  6
  IS
  7
      totalsal1 NUMBER;
  8
  BEGIN
  9
      SELECT sal + comm
  10
      INTO totalsal1
  11
      FROM emp
  12
      WHERE empno = v_empno;
  13
  -- 把v_empno编号的员工的 sal工资+comm奖金，结果存入totalsal1
  14
      RETURN totalsal1;
  15
  END;
  

存储过程#

1. AS 声明部分：接下来存放查询结果的 局部变量、常量、游标等

   1
   CREATE OR REPLACE PROCEDURE TRANSFER (
   2
       inAccount  IN Account.AccountNum%TYPE, -- 转入账户：IN调用者传入
   3
       outAccount IN Account.AccountNum%TYPE, -- 转出账户
   4
       amount     IN NUMBER                   -- 转账金额
   5
   )
   6
   AS -- 内部局部变量
   7
       /* 保存转出账户的余额 */
   8
       totalDepositOut Account.Total%TYPE;
   9
   
   10
       /* 保存查询到的转入账户号 */
   11
       inAccountNum Account.AccountNum%TYPE;
   

2. IN 调用者传入

   1
   CREATE OR REPLACE PROCEDURE TRANSFER (
   2
       inAccount  IN Account.AccountNum%TYPE, -- 转入账户：IN调用者传入
   3
       outAccount IN Account.AccountNum%TYPE, -- 转出账户
   4
       amount     IN NUMBER                   -- 转账金额
   5
   )
   

3. INTO 查询并保存到局部变 → 异常处理

   1
   SELECT AccountNum
   2
   INTO inAccountNum
   3
   FROM Account
   4
   WHERE AccountNum = inAccount
   5
   FOR UPDATE;
   

   1
   EXCEPTION
   2
           /* SELECT INTO没有查询到账户 */
   3
           WHEN NO_DATA_FOUND THEN
   4
               ROLLBACK;
   5
   
   6
               RAISE_APPLICATION_ERROR(
   7
                   -20004,
   8
                   '转入账户或转出账户不存在'
   9
               );
   10
   
   11
           /* 处理其他异常 */
   12
           WHEN OTHERS THEN
   13
               ROLLBACK;
   14
               RAISE;
   

• 例：转账存储过程

  1
  /*====================================================
  2
  1. 创建账户基本表
  3
  ====================================================*/
  4
  CREATE TABLE Account (
  5
      AccountNum VARCHAR2(11) PRIMARY KEY,  -- 账户号
  6
      Total      NUMBER(15, 2) DEFAULT 0 NOT NULL, -- 账户余额
  7
  
  8
      CONSTRAINT CK_Account_Total
  9
          CHECK (Total >= 0)
  10
  -- 创建名为 CK_Account_Total 的检查约束，要求：账户余额必须大于或等于0
  11
  );
  12
  
  13
  
  14
  /*====================================================
  15
  2. 插入测试数据
  16
  ====================================================*/
  17
  
  18
  /* 转入账户：初始余额20000元 */
  19
  INSERT INTO Account(AccountNum, Total)
  20
  VALUES ('01003813828', 20000);
  21
  
  22
  /* 转出账户：初始余额50000元 */
  23
  INSERT INTO Account(AccountNum, Total)
  24
  VALUES ('01003815868', 50000);
  25
  
  26
  COMMIT; -- 提交插入操作
  27
  
  28
  
  29
  /*====================================================
  30
  3. 查看转账前的账户余额
  31
  ====================================================*/
  32
  SELECT AccountNum, Total
  33
  FROM Account
  34
  ORDER BY AccountNum;
  35
  
  36
  
  37
  /*====================================================
  38
  4. 创建转账存储过程
  39
  ====================================================*/
  40
  CREATE OR REPLACE PROCEDURE TRANSFER (
  41
      inAccount  IN Account.AccountNum%TYPE, -- 转入账户：IN调用者传入
  42
      outAccount IN Account.AccountNum%TYPE, -- 转出账户
  43
      amount     IN NUMBER                   -- 转账金额
  44
  )
  45
  AS -- 内部局部变量
  46
      /* 保存转出账户的余额 */
  47
      totalDepositOut Account.Total%TYPE;
  48
  
  49
      /* 保存查询到的转入账户号 */
  50
      inAccountNum Account.AccountNum%TYPE;
  51
  
  52
  BEGIN
  53
      /*------------------------------------------
  54
      1. 检查转账金额：小于0 不合法
  55
      ------------------------------------------*/
  56
      IF amount <= 0 THEN
  57
          RAISE_APPLICATION_ERROR(
  58
              -20001,
  59
              '转账金额必须大于0'
  60
          );
  61
      END IF;
  62
  
  63
      /* 转入账户和转出账户不能相同 */
  64
      IF inAccount = outAccount THEN
  65
          RAISE_APPLICATION_ERROR(
  66
              -20002,
  67
              '转入账户和转出账户不能相同'
  68
          );
  69
      END IF;
  70
  
  71
  
  72
      /*------------------------------------------
  73
      2. 查询并锁定转出账户
  74
      ------------------------------------------*/
  75
      SELECT Total
  76
      INTO totalDepositOut -- 查询并保存到局部变量totalDepositOut
  77
      FROM Account
  78
      WHERE AccountNum = outAccount
  79
      FOR UPDATE; -- 加行级锁。防止转账过程中，其他事务同时修改该账户余额。
  80
      -- 一直到COMMIT/ROLLBACK
  81
  
  82
      /*------------------------------------------
  83
      3. 检查转出账户余额：小于转账金额 抛出ERROR
  84
      ------------------------------------------*/
  85
      IF totalDepositOut < amount THEN
  86
          RAISE_APPLICATION_ERROR(
  87
              -20003,
  88
              '转出账户余额不足'
  89
          );
  90
      END IF;
  91
  
  92
  
  93
      /*------------------------------------------
  94
      4. 检查并锁定转入账户
  95
      ------------------------------------------*/
  96
      SELECT AccountNum
  97
      INTO inAccountNum
  98
      FROM Account
  99
      WHERE AccountNum = inAccount
  100
      FOR UPDATE;
  101
  
  102
  
  103
      /*------------------------------------------
  104
      5. 转出账户扣款
  105
      ------------------------------------------*/
  106
      UPDATE Account
  107
      SET Total = Total - amount
  108
      WHERE AccountNum = outAccount;
  109
  
  110
  
  111
      /*------------------------------------------
  112
      6. 转入账户加款
  113
      ------------------------------------------*/
  114
      UPDATE Account
  115
      SET Total = Total + amount
  116
      WHERE AccountNum = inAccount;
  117
  
  118
  
  119
      /*------------------------------------------
  120
      7. 提交转账事务
  121
      ------------------------------------------*/
  122
      COMMIT;
  123
  
  124
  
  125
  EXCEPTION
  126
      /* SELECT INTO没有查询到账户 */
  127
      WHEN NO_DATA_FOUND THEN
  128
          ROLLBACK;
  129
  
  130
          RAISE_APPLICATION_ERROR(
  131
              -20004,
  132
              '转入账户或转出账户不存在'
  133
          );
  134
  
  135
      /* 处理其他异常 */
  136
      WHEN OTHERS THEN
  137
          ROLLBACK;
  138
          RAISE;
  139
  
  140
  END TRANSFER;
  

静态SQL vs 动态SQL#

⭐数据依赖：函数依赖#

1. 定义
   X确定唯一Y，则称“X函数确定Y”或“Y函数依赖于X”，记作 $X \to Y$。
   • $X$：决定因素
   • $Y$：依赖因素
   

数据依赖：多值依赖#

范式 & 规范化#

1. 范式（NF）：是对一个关系中允许存在的数据依赖的要求
   规范化：通过投影分解将低范式转换为高范式。
2. 范式等级（从低到高包含关系）： $1NF \supset 2NF \supset 3NF \supset BCNF \supset 4NF \supset 5NF$
3. 规范化：将低范式通过模式分解转为高范式，消除不良数据依赖
   范式越高，对表中数据依赖的要求越严格。

1NF：属性原子性；2NF：不存在主属性部分函数依赖非主属性；
3NF：不存在 非主属性 对 候选码的传递依赖；BCNF：左边都是 超码
部分主 → 非主；非主 $\xRightarrow{传递}$ 候选码；超码 →

二、1NF（第一范式）#

1. 定义#

   关系模式的所有属性均为 不可再分的原子数据项，是关系数据库的最低要求。

2. 实例#

   关系模式 SLC(Sno, Sdept, Sloc, Cno, Grade) 中，所有属性均为原子属性，满足 1NF。

三、2NF（第二范式）#

1. 定义
   非主属性不能由候选码部分函数依赖推出，必须完全依赖。
   • 如：AF 是候选码，D 是非主属性。
     若A → D，说明非主属性 D对候选码部份依赖 $\Rightarrow$ 不满足2NF
2. 实例：SLC 关系
   
3. Solution
   分解成2个关系模式（但插入异常、删除异常任然存在）
   

四、3NF（第三范式）#

1. 定义
   不存在非主属性对候选码的传递函数依赖
   码 不能和 非主属性 有传递依赖关系
   不存在码X、属性Y、非主属性Z使得 $X→Y，Y→Z$ 成立 $\Rightarrow Y \nrightarrow X$

2. 实例：SL 关系（Sno是码）

五、BCNF（巴斯范式）#

1. 定义
   所有非平凡函数依赖中的决定因素（左边），都必须是包含候选码（都必须是超码）
2. 实例 1：仓库管理关系：把违反的（管理员，仓库名）单独成表
   

   

4NF（第四范式）#

1. 定义
   每个非平凡多值依赖 X →→ Y 都满足 X含有码
2. 分解
   把独立的多值事实拆开

🌟例题#

• ⭐例1：属性集闭包算法 -计算A关于F的闭包 $A_F^+$#

  
  初始：$A_F^+ = {A}$   用 A→B：$A_F^+ = {A,B}$   用 B→C：$A_F^+ = {A,B,C}$

  • 计算方法：迭代算法
    

• ⭐例：等价/最小函数依赖集 -属性集闭包算法#

  

• ⭐例2：极小函数依赖集算法#

  1. 右部拆分：AD → EF 拆成：AD → E，AD → F；
  2. 删左边多余属性：对于 ADF→ C：∵AD → F ，故可简化为 AD → C；
     对于 ADF → D：∵D已经包含在左边 $\rightarrow$ 可以删除该平凡函数依赖；
  3. 删冗余依赖：对于C → B，可以由C → F，F → B推出。 删除 C → B；

• ⭐（补充3）例：判断冗余依赖#

  1. 函数依赖集 M = {C → E，E → B，BCD → A}，断言：BCD → A 可以简化成 CD → A，它是一个冗余函数依赖
     构造 N = {C → E，E → B，CD → A}，只需证明：$M^+ = N^+$（M能推出的=N能推出的）
     ✅只需证明：M ⊨ CD → A？ N ⊨ BCD → A？

     ① 证明：M ⊨ CD → A
     只需证明 ${C，D}_M^+$ 中包含 A，就能证明 CD → A
     初始值：{CD}_M^+ = {C，D}
     第一遍：{CD}_M^+ = {C，D，E}（C → E）
     第二遍：{CD}_M^+ = {B，C，D，E}（E → B）
     第三遍：{CD}_M^+ = {A，B，C，D，E}（BCD → A）

     ② 同理得证：N ⊨ BCD → A

  2. 函数依赖集 M = {B → D，D → E，C → F，BC → A，EF → A}，断言：BC→ A 是一个冗余的函数依赖
     构造 N = {B → D，D → E，C → F，EF → A}，要证明 $M^+ = N^+$，
     只需证明：N ⊨ BC → A，即：${BC}_N^+$ 包含A

  3. 函数依赖集 F = {A → B，B → A，AB → C}，判断 AB → C 是否为部分函数依赖？
     只需假设把AB → C 换成 A → C/B → C是否依然成立。同上。

• ⭐例3：计算候选码#

  要求候选码K，只需找到能推出全部属性U的 $K_R^+=U$ 即可。

  1. 没有出现在右边的属性 → 一定在候选码内（因为没有属性能推出它）
  2. 只出现在右边的属性 → 一般不放在候选码（因为可以由其他属性推出）
  3. 其他属性待选跟1结合

  • 先算1必须出现在候选码中的属性K，是否有 $K_R^+ = U$
  • 再依次跟3中属性结合。候选码可能不止1个。

⭐模式分解#

1. 模式分解的三个理解#

   1. 无损连接：自然连接后完全等于原表
   2. 保持函数依赖：分解后所有依赖规则都能找到
   3. 既 无损连接 又 保持函数依赖

2. ⭐无损连接#

   • 有损连接：会产生多余元组。

   • 定义：R = R1 ⋈ R2 ⋈ … ⋈ Rn，能 自然连接$\bowtie$ 回来，完全等于原表。

   • 💫判定：两张表的公共属性，如果能推出某一张表的非公共属性，就是无损连接。
     例如1：设有一个关系模式T(A,B,C)，函数依赖集F， 请判断下述的 分解$ρ={T_1,T_2}$是否具有无损连接性？A是公共属性，能推出T1的非公共属性B。故满足无损连接性

     函数依赖集	分解ρ	是否满足无损连接性？	解释
     {A→B}	$T_1(A,B) T_2(A,C)$	✅	A是公共属性，能推出B
     {A→C，B→C}	$T_1(A,B) T_2(A,C)$	✅	A是公共属性，能推出C
     {A→B}	$T_1(A,B) T_2(B,C)$	❌	B是公共属性，不能推出非公共属性
     {A→B，B→C}	$T_1(A,C) T_2(B,C)$}	❌	C是公共属性，不能推出非公共属性

3. ⭐保持函数依赖#

   分解后，原来的函数依赖能在子关系中直接检查。

多值依赖#

1. 定义：一个属性决定的不是一个值，而是一组值。记作 X →→ Y，读作Y多值依赖于X（有其他属性Z，Y、Z都多值依赖于X的充要条件是 X、Y可以自由组合）
2. 多值依赖 vs 函数依赖
   1. 跟Z的关系
      函数依赖：只问“X 能不能唯一确定 Y”。
      多值依赖：还要问“Y 和剩下的属性是不是独立”。
   2. 跟子集的关系
      A → BC，则：A → B A → C
      多值依赖不一定成立

事务 & ACID特性#

1. 什么是事务？：原子操作，事务是一个不可分割的数据库操作序列：
2. ACID四个特性

   特性	含义	例子	主要由什么保证
   Atomicity 原子性	要么全做，要么全不做	扣款和加款必须一起完成	UNDO、ROLLBACK、日志
   Consistency 一致性	从一个一致状态变为另一个一致状态	转账前后总金额不变	正确事务程序、完整性约束、并发与恢复机制
   Isolation 隔离性	并发事务之间互不干扰	别人不能看到只扣款、未加款的中间状态	第11章并发控制、封锁
   Durability 持久性	提交后的结果不会因故障丢失	转账提交后，断电也不能丢失	REDO、日志、转储

COMMIT、ROLLBACK & 保存点 SAVEPOINT#

3. SQVEPOINT sql 操作1 操作2 SAVEPOINT sp1 -- 定义 操作3 操作4 ROLLBACK TO sp1 -- 操作3、操作4撤销；

三类故障及恢复方法#

这是判断UNDO、REDO的基础。

数据转储#

1. 静态转储 & 动态转储
2. 海量存储 & 增量存储

⭐日志文件#

1. **什么是日志文件？**日志文件记录事务对数据库的更新操作。
2. UNDO前像 & REDO后像

   1
    <START T1>
   2
    <T1, A, 100, 80>-- 账户 A 原来余额为100元，事务T1要扣除20元→修改为80元
   3
    <COMMIT T1>     -- 事务T1已经成功完成，修改结果应当永久保存
   4
   
   5
    <START T2>
   6
    <T2, B, 50, 70>
   7
    <ROLLBACK T2>   -- T2放弃事务，撤销自己的全部修改。回滚回前像B=50
   8
    <CHECKPOINT ...>
   

   • T1没有提交，系统故障
     没有COMMIT，T1尚未完成 → UNDO T1使用前像，把A恢复成100
   • T1已经提交，系统故障
     CONNIT已经提交，但80还没写入磁盘 → REDO T1使用后像，A写入80