数据库原理期末复习笔记

这份笔记面向期末考试，整理了数据库 1 到 8 章的核心知识、常考题型、SQL 模板、ER 图设计、规范化、事务并发恢复，以及本轮复习中反复出现的易错点（根据ai一边写题一边复习整理对话内容总结的笔记）。

一、数据库基础

1. 数据库系统的基本概念

数据库系统由数据库、数据库管理系统 DBMS、应用程序、数据库管理员 DBA 和用户组成。

常考区分：

概念	含义
数据库 DB	长期存储在计算机内、有组织、可共享的数据集合
DBMS	管理数据库的软件系统
数据库系统 DBS	DB + DBMS + 应用程序 + 用户 + 管理人员

2. 数据库系统特点

数据库系统的主要特点：

数据结构化
数据共享性高，冗余度低
数据独立性高
数据由 DBMS 统一管理和控制

重点是数据独立性：

类型	含义	例子
物理独立性	物理存储变化不影响应用程序	改索引、改存储结构、改文件组织方式
逻辑独立性	逻辑结构变化不影响应用程序	表中增加字段、拆表、改视图

易错点：

如果在 Student 表里增加 WechatID，原来的成绩录入系统不用修改，这是逻辑独立性，不是物理独立性。

3. 三级模式结构

数据库三级模式：

层次	名称	含义
外模式	子模式、用户模式	用户看到的数据视图
模式	概念模式、逻辑模式	全体数据的逻辑结构
内模式	存储模式、物理模式	数据的物理存储方式

常考判断：

一个数据库只有一个模式和一个内模式，但可以有多个外模式。

两层映像：

映像	保证
外模式/模式映像	逻辑独立性
模式/内模式映像	物理独立性

二、关系模型与关系代数

1. 关系模型基本概念

关系模型中的基本术语：

术语	含义
关系	一张二维表
元组	表中的一行
属性	表中的一列
域	属性的取值范围
候选键	能唯一标识元组且没有多余属性的属性组
主键	从候选键中选定的一个
主属性	包含在任一候选键中的属性
非主属性	不包含在任何候选键中的属性
外键	一个关系中的属性引用另一个关系的主键

易错点：

主属性不是“只包含在主键中的属性”，而是“包含在任一候选键中的属性”。

2. 三类完整性

完整性	规则
实体完整性	主键属性不能为空
参照完整性	外键要么为空，要么等于被参照关系中某个主键值
用户定义完整性	由具体业务决定，如成绩 0 到 100、性别只能 F/M

参照完整性易错：

严格来说，被参照关系上删除主码或修改主码最容易破坏参照完整性；插入被参照关系通常不会破坏参照完整性。部分教材或题目会用更宽泛表述，做题时以试卷口径为准。

3. 关系代数

常用运算：

运算	符号	含义
选择	`σ`	选行
投影	`π`	选列并去重
并	`∪`	合并两个同类关系
差	`-`	在 R 中但不在 S 中
笛卡尔积	`×`	两表所有组合
连接	`⋈`	按条件组合元组
自然连接	`⋈`	同名同域属性自动相等连接并去重
除法	`÷`	查询“至少包含全部指定项”的对象

关系代数和 SQL 的重要区别：

关系代数投影默认去重，SQL 的 SELECT 默认不去重，除非写 DISTINCT。

4. 除法运算

除法常用于“查询选修了全部课程的学生”“供应所有零件的供应商”。

例：

1	π Sno,Cno(SC) ÷ π Cno(Course)

含义是查询选修了所有课程的学生学号。

考试识别口诀：

题干出现“全部”“所有”“每一个”，优先想到除法或 SQL 中的 NOT EXISTS 双重否定。

5. 自然连接元组数和列数

若 r(A,B,C) 有 N1 个元组，s(B,C,D) 有 N2 个元组，自然连接：

列数为 3 + 3 - 2 = 4。

元组数最多 N1 * N2，最少可以为 0。

6. 差集元组数

若 R 有 5 个元组，S 有 3 个元组，则 R-S 最少为 5-3=2，最多为 5。所以 R-S 不可能只有 1 个元组。

三、SQL

1. SQL 功能分类

功能	关键字
数据查询	`SELECT`
数据定义	`CREATE`、`DROP`、`ALTER`
数据操纵	`INSERT`、`UPDATE`、`DELETE`
数据控制	`GRANT`、`REVOKE`

2. 建表模板

学生表：

CREATE TABLE Student(
    sno CHAR(8) PRIMARY KEY,
    sname VARCHAR(20) NOT NULL,
    ssex CHAR(1) CHECK (ssex IN ('男', '女')),
    sage INT CHECK (sage >= 15),
    sdept VARCHAR(20)
);

课程表：

CREATE TABLE Course(
    cno CHAR(6) PRIMARY KEY,
    cname VARCHAR(30) NOT NULL UNIQUE,
    credit INT DEFAULT 3,
    teacher VARCHAR(20)
);

选课表：

CREATE TABLE SC(
    sno CHAR(8),
    cno CHAR(6),
    score INT CHECK (score BETWEEN 0 AND 100),
    PRIMARY KEY (sno, cno),
    FOREIGN KEY (sno) REFERENCES Student(sno),
    FOREIGN KEY (cno) REFERENCES Course(cno)
);

外键常见错误：

错误写法：

1	sno CHAR(8) FOREIGN KEY REFERENCES ON Student(sno)

正确写法：

1	FOREIGN KEY (sno) REFERENCES Student(sno)

或者列级写法：

1	sno CHAR(8) REFERENCES Student(sno)

3. 常用约束

约束	写法
主键	`PRIMARY KEY`
外键	`FOREIGN KEY (...) REFERENCES ...`
非空	`NOT NULL`
唯一	`UNIQUE`
检查	`CHECK (...)`
默认值	`DEFAULT 3`

易错点：

PRIMARY KEY 自带 NOT NULL 和 UNIQUE。

字符串常量用单引号，如 'CS'、'数据库系统'。不要写成 CS。

SQL 中逗号要用英文逗号 ,，不要用中文逗号 ，。

4. 基本查询模板

条件查询：

1
2
3

SELECT sno, sname
FROM Student
WHERE sdept = 'CS';

连接查询：

SELECT s.sno, s.sname, c.cname, sc.score
FROM Student s
JOIN SC sc ON s.sno = sc.sno
JOIN Course c ON sc.cno = c.cno;

分组统计：

1
2
3

SELECT cno, AVG(score) AS avg_score
FROM SC
GROUP BY cno;

分组后筛选：

SELECT sno
FROM SC
GROUP BY sno
HAVING COUNT(*) >= 3;

WHERE 和 HAVING 区别：

WHERE 在分组前筛选元组，HAVING 在分组后筛选组。

5. IN、ANY、ALL

IN 表示属于子查询结果：

SELECT sname
FROM Student
WHERE sno IN (
    SELECT sno
    FROM SC
    WHERE score >= 60
);

ANY 和 ALL 必须与比较运算符配合使用：

> ANY (...)   -- 大于至少一个，等价于大于最小值
> ALL (...)   -- 大于全部，等价于大于最大值
< ANY (...)   -- 小于至少一个，等价于小于最大值
< ALL (...)   -- 小于全部，等价于小于最小值
= ANY (...)   -- 等价于 IN

例：查询其他院系中比信息学院任意一个学生年龄都大的学生：

SELECT sno, sname, sage
FROM Student
WHERE sdept <> '信息学院'
  AND sage > ANY (
      SELECT sage
      FROM Student
      WHERE sdept = '信息学院'
  );

6. 视图

视图是从一个或多个基本表导出的虚表，通常不存储实际数据。

单表视图：

CREATE VIEW CS_Student(sno, sname, sage)
AS
SELECT sno, sname, sage
FROM Student
WHERE sdept = 'CS';

连接视图：

CREATE VIEW V_SC(sno, sname, cname, score)
AS
SELECT s.sno, s.sname, c.cname, sc.score
FROM Student s
JOIN SC sc ON s.sno = sc.sno
JOIN Course c ON sc.cno = c.cno;

统计视图：

CREATE VIEW V_AvgScore(cno, avg_score)
AS
SELECT cno, AVG(score)
FROM SC
GROUP BY cno;

易错点：

视图列名不能写 AVG(score) 这种表达式，应该写 avg_score。

视图名不要写 V_SC-Score，中间的 - 会被看成减号，建议写 V_CS_Score。

SELECT 后面不要写成 SELECT (sno, sname, cno, score)，应直接写 SELECT sno, sname, cno, score。

7. 授权

授权模板：

1
2
3

GRANT SELECT
ON SC
TO U1;

只允许查询课程 C1 的成绩时，更完整的做法是先建视图，再授权：

CREATE VIEW V_C1_SCORE AS
SELECT sno, score
FROM SC
WHERE cno = 'C1';

GRANT SELECT
ON V_C1_SCORE
TO U1;

四、数据库设计与 ER 模型

1. 数据库设计阶段

阶段	主要工作
需求分析	数据流图、数据字典、需求说明
概念结构设计	画 ER 图
逻辑结构设计	ER 图转换为关系模式，设计主键外键
物理结构设计	设计索引、聚簇、分区、存储结构
数据库实施	建库、装载数据、编写程序
运行维护	备份、恢复、性能优化

易错点：

画 ER 图是概念设计；ER 图转换关系模式是逻辑设计；设计索引、聚簇、按时间拆分大表是物理设计。

2. ER 图元素

图形	含义
矩形	实体
椭圆	属性
菱形	联系
下划线属性	主键

联系类型：

类型	例子
1:1	一个班级一个班主任
1:N	一个学院有多个学生
M:N	学生选修课程

3. ER 转关系模式规则

实体转关系：

1	学生(学号, 姓名, 年龄, 系别)

1:N 联系：

把 1 方主键加入 N 方作为外键。

M:N 联系：

单独建立一个关系，主键通常是两边主键的组合。

1	选课(学号, 课程号, 成绩)

带属性的联系：

如果联系本身有属性，通常转成一个单独关系。

例如订单可作为联系：

1	订单(订单编号, 订单内容, 日期, 团购点编号, 客户电话)

4. ER 题常见套路

考场上先圈名词，再圈动词：

名词常是实体，如学生、课程、供应商、客户、快递公司、快递点、员工、电动车。

动词常是联系，如选修、供货、下单、上班、投递。

如果题目要求记录某件事情发生的时间、内容、状态，这个“事情”往往要单独建关系。

五、索引

1. 聚簇索引

聚簇索引的叶子结点就是数据页，数据按聚簇索引键的顺序存放。

特点：

一个表通常只能有一个聚簇索引
按聚簇键范围查询很快
插入、更新可能引起页分裂和数据移动

2. 二级索引

二级索引也叫辅助索引，叶子结点通常存储二级索引键和主键值，再通过主键回表查完整记录。

特点：

一个表可以有多个二级索引
查询非主键字段时常用
可能发生回表

3. 哈希索引

哈希索引通过哈希函数定位数据。

适合：

1	WHERE id = 1001

不适合：

1
2
3

WHERE id BETWEEN 1000 AND 2000
ORDER BY id
LIKE 'abc%'

因为哈希索引不保持顺序。

4. 索引选择题易错点

索引可以提高查询速度，但会降低插入、删除、修改速度，因为要维护索引。

索引不是越多越好。

六、关系模式规范化

1. 函数依赖

若属性集 X 的值能唯一决定属性集 Y 的值，则称 X -> Y。

常见类型：

类型	含义
完全函数依赖	`Y` 依赖整个复合键，去掉任一属性都不行
部分函数依赖	`Y` 只依赖复合键的一部分
传递函数依赖	`X -> Y`，`Y -> Z`，所以 `X -> Z`

易错点：

如果候选键是单属性，就不存在部分函数依赖，所以一定满足 2NF。

2. 候选键和闭包

求闭包步骤：

从给定属性集出发
根据函数依赖不断加入能推出的新属性
如果能推出全部属性，则该属性集是超键
如果没有多余属性，则是候选键

例：

1 2	R(A,B,C,D,E) F={A->B, B->C, A->D, D->E}

则：

1	A+ = {A,B,C,D,E}

所以 A 是候选键。

3. 范式判断

范式	判断标准
1NF	属性不可再分
2NF	1NF 且不存在非主属性对候选键的部分依赖
3NF	2NF 且不存在非主属性对候选键的传递依赖
BCNF	每个非平凡函数依赖 `X->Y` 中，`X` 都是超键

3NF 判断等价条件：

对每个非平凡依赖 X -> A，满足以下之一即可：

X 是超键
A 是主属性

BCNF 比 3NF 更严格。

4. 典型例题

若：

R(Student, Dept, DeptAddr, Course, Teacher, Score)
F={
  Student -> Dept,
  Dept -> DeptAddr,
  Course -> Teacher,
  (Student, Course) -> Score
}

候选键：

1	(Student, Course)

问题：

Student -> Dept 是对复合键的部分依赖
Dept -> DeptAddr 造成传递依赖
Course -> Teacher 也是部分依赖

分解：

StudentDept(Student, Dept)
DeptInfo(Dept, DeptAddr)
CourseTeacher(Course, Teacher)
SC(Student, Course, Score)

5. 最小函数依赖集

求最小依赖集步骤：

右部拆成单属性
去掉左部多余属性
去掉冗余依赖

例：

若经过化简得到：

1	{A->B, B->C, A->D}

不要保留能由其他依赖推出的冗余依赖。

6. 无损连接与依赖保持

无损连接：

分解后自然连接能恢复原关系，不产生伪元组。

二分解判断：

若 R 分解为 R1、R2，且：

1	(R1 ∩ R2) -> R1

或：

1	(R1 ∩ R2) -> R2

则无损连接。

依赖保持：

原函数依赖集中的每条依赖都能在分解后的关系中直接或间接推出。

易错点：

无损连接和依赖保持是两件事。一个分解可以无损但不保持依赖，也可以保持依赖但需要检查是否无损。

七、事务、并发控制与恢复

1. 事务 ACID

特性	含义
原子性 Atomicity	要么全做，要么全不做
一致性 Consistency	事务执行前后数据库保持一致
隔离性 Isolation	并发事务互不干扰
持久性 Durability	提交后的结果永久保存

事务是恢复和并发控制的基本单位。

2. 并发异常

异常	含义
丢失修改	两个事务读同一旧值，后写覆盖先写
读脏数据	读到其他事务未提交的数据
不可重复读	同一事务两次读同一数据结果不同
幻读	两次查询满足条件的元组集合不同
不一致分析	统计过程中读到一部分旧值和一部分新值

例：

T1 读 A=100
T2 读 A=100
T1 写 A=95
T2 写 A=92

最终 T1 的修改被覆盖，这是丢失修改。

3. 锁

锁	含义
S 锁	共享锁，读锁
X 锁	排他锁，写锁
IS 锁	意向共享锁
IX 锁	意向排他锁
SIX 锁	共享加意向排他锁

如果事务要读整个关系并修改其中部分元组，应对关系加 SIX 锁。

4. 两段锁协议 2PL

两段锁协议分两阶段：

增长阶段：只能加锁，不能解锁
收缩阶段：只能解锁，不能再加锁

性质：

遵守 2PL 的调度一定是冲突可串行化的
2PL 不能避免死锁

死锁例子：

T1: Xlock(A), read(A), write(A)
T2: Xlock(B), read(B), write(B)
T1: Xlock(B)  -- 等待 T2
T2: Xlock(A)  -- 等待 T1

等待图：

1 2	T1 -> T2 T2 -> T1

有环，所以死锁。

5. 可串行化

冲突操作：

不同事务、同一数据项、至少一个写。

常见冲突：

1
2
3

r1(A) w2(A)
w1(A) r2(A)
w1(A) w2(A)

判断冲突可串行化：

找冲突操作
建优先图
若无环，则冲突可串行化
若有环，则不可冲突可串行化

6. 日志与恢复

WAL 原则：

必须先写日志，后写数据库。

日志登记不要求严格按事务整体开始时间排序，而是按实际日志记录产生顺序写入。

恢复判断：

事务状态	操作
已提交	`REDO`
未提交	`UNDO`

有检查点时，不需要从头扫描日志，通常从最近检查点附近开始恢复。

例：

<T1 START>
<T1, A, old, new>
<T1 COMMIT>
<T2 START>
<T2, B, old, new>
CRASH

恢复时：

1 2	REDO: T1 UNDO: T2

八、触发器

1. 基本结构

触发器在表发生 INSERT、UPDATE、DELETE 时自动执行。

常见选择：

场景	触发时机
阻止非法插入、修改、删除	`BEFORE`
维护统计、写日志	`AFTER`

NEW 和 OLD：

操作	可用变量
`INSERT`	`NEW`
`DELETE`	`OLD`
`UPDATE`	`OLD` 和 `NEW`

2. 拒绝非法插入

CREATE TRIGGER tr_student_age_check
BEFORE INSERT ON Student
FOR EACH ROW
BEGIN
    IF NEW.sage < 15 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '年龄不合法';
    END IF;
END;

3. 拒绝删除核心课程

CREATE TRIGGER tr_course_delete_check
BEFORE DELETE ON Course
FOR EACH ROW
BEGIN
    IF OLD.cname IN ('数据库系统', '操作系统') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '禁止删除核心课程';
    END IF;
END;

4. 成绩下降超过 20 分禁止修改

CREATE TRIGGER tr_score_check
BEFORE UPDATE ON SC
FOR EACH ROW
BEGIN
    IF NEW.score < OLD.score - 20 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '成绩下降超过20分，不允许修改';
    END IF;
END;

5. 自动维护统计人数

插入选课后课程人数加 1：

CREATE TRIGGER tr_sc_insert_count
AFTER INSERT ON SC
FOR EACH ROW
BEGIN
    UPDATE Course
    SET student_count = student_count + 1
    WHERE cno = NEW.cno;
END;

删除选课后课程人数减 1：

CREATE TRIGGER tr_sc_delete_count
AFTER DELETE ON SC
FOR EACH ROW
BEGIN
    UPDATE Course
    SET student_count = student_count - 1
    WHERE cno = OLD.cno;
END;

6. 删除日志

CREATE TRIGGER tr_student_delete_log
AFTER DELETE ON Student
FOR EACH ROW
BEGIN
    INSERT INTO Student_Delete_Log(sno, sname, delete_time)
    VALUES(OLD.sno, OLD.sname, NOW());
END;

7. 限制最多选 5 门课

CREATE TRIGGER tr_sc_limit
BEFORE INSERT ON SC
FOR EACH ROW
BEGIN
    IF (
        SELECT COUNT(*)
        FROM SC
        WHERE sno = NEW.sno
    ) >= 5 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '该学生选课已满，不能超过5门';
    END IF;
END;

易错点：

INSERT 不能用 OLD
DELETE 不能用 NEW
拒绝操作要用 SIGNAL，不是 print
SIGNAL SQLSTATE '45000' 中间没有等号
修改另一张表时要写 UPDATE Course SET ... WHERE ...，不能直接写 SET Course.xxx = ...

九、真题高频点

1. 单选题高频

物理独立性和逻辑独立性：

加字段但应用程序不用改，是逻辑独立性。

索引：

索引提高查询效率，但会降低增删改效率。

日志：

先写日志，后写数据库。

意向锁：

读整个关系并改部分元组，用 SIX。

规范化：

单属性候选键不存在部分依赖，所以至少满足 2NF。

2. 判断题高频

模式描述全体数据逻辑结构，外模式描述用户视图。

事务是不可分割的操作序列，也是并发控制基本单位。

死锁检测用等待图，不是数据流图。

有检查点时恢复不必从头扫描日志。

ANY 和 ALL 一般必须与比较运算符一起使用。

3. SQL 高频模板

查询选了某课程的学生姓名：

SELECT s.sname
FROM Student s
JOIN SC sc ON s.sno = sc.sno
JOIN Course c ON sc.cno = c.cno
WHERE c.cname = '数据库系统';

查询选三门及以上课程的学生：

SELECT sno
FROM SC
GROUP BY sno
HAVING COUNT(*) >= 3;

查询平均成绩：

1
2
3

SELECT sno, AVG(score)
FROM SC
GROUP BY sno;

错误 SQL 识别：

1
2
3

SELECT sname
FROM S, SC
WHERE grade >= 60;

这句没有连接条件，会产生笛卡尔积，是错误的。