数据库（Database Management Systems，DBMS）是计算机科学专业最重要的中级课程之一，也是就业市场需求最高的技术之一。但很多同学发现：SQL 写起来感觉会了，考试却总是出错——这是因为理论部分（规范化、事务、并发）没有真正理解。
课程对应关系：
- UNSW：COMP3311 Database Systems（PostgreSQL）
- Monash：FIT2094 Databases（Oracle SQL）
- USYD：INFO2120 Database Systems I
- UQ：INFS2200 Relational Database Systems
- UofT：CSC343 Introduction to Databases
考核结构（以 UNSW COMP3311 为例）
| 组成部分 | 权重 |
|---|---|
| Assignment 1（SQL 查询） | 15% |
| Assignment 2（Python + PostgreSQL） | 20% |
| 期中测试 | 15% |
| 期末考试 | 50% |
第一部分：关系模型（Relational Model）
核心概念
| 术语 | 含义 | 类比 |
|---|---|---|
| Relation（关系） | 一张表 | Table |
| Tuple（元组） | 一行数据 | Row |
| Attribute（属性） | 一列数据 | Column |
| Domain（值域） | 属性的合法取值范围 | Data Type |
| Primary Key（主键） | 唯一标识每个元组的属性集 | ID |
| Foreign Key（外键） | 引用另一个表主键的属性 | 关联字段 |
完整性约束（Integrity Constraints）
实体完整性：主键不能为 NULL
参照完整性：外键值必须在被引用表的主键中存在（或为 NULL）
违反参照完整性时的处理选项：
FOREIGN KEY (dept_id) REFERENCES Department(id)
ON DELETE CASCADE -- 删除父表行时，级联删除子表行
ON DELETE SET NULL -- 删除父表行时，子表外键设为NULL
ON DELETE RESTRICT -- 若有子表引用，拒绝删除（默认）
ON DELETE SET DEFAULT -- 设为默认值
第二部分：SQL 查询（期末核心）
基础 SELECT 结构
SELECT [DISTINCT] column1, column2, ...
FROM table1 [JOIN table2 ON condition]
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column [ASC|DESC]
LIMIT n;
执行顺序（与书写顺序不同！）：
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
JOIN 类型（期末最易混淆点）
-- INNER JOIN：只返回两表都匹配的行
SELECT s.name, e.grade
FROM Student s INNER JOIN Enrolment e ON s.id = e.student_id;
-- LEFT JOIN：返回左表所有行，右表无匹配则NULL
SELECT s.name, e.grade
FROM Student s LEFT JOIN Enrolment e ON s.id = e.student_id;
-- RIGHT JOIN：返回右表所有行
-- FULL OUTER JOIN：返回两表所有行（PostgreSQL支持）
记忆技巧：LEFT JOIN = 左表全保留，右表没有就 NULL；想清楚"哪张表的数据一定要全要"。
聚合函数与 GROUP BY
-- 每个院系的平均 GPA（只统计选修了超过2门课的学生）
SELECT s.department, AVG(e.grade) AS avg_grade, COUNT(*) AS num_students
FROM Student s JOIN Enrolment e ON s.id = e.student_id
GROUP BY s.department
HAVING COUNT(DISTINCT e.course_id) > 2
ORDER BY avg_grade DESC;
WHERE vs HAVING 的区别：
WHERE：过滤行（在 GROUP BY 之前）HAVING：过滤分组（在 GROUP BY 之后）
子查询（Subquery）
-- 找出 GPA 高于平均 GPA 的学生
SELECT name, gpa
FROM Student
WHERE gpa > (SELECT AVG(gpa) FROM Student);
-- 用 EXISTS 找选了 'COMP3311' 的学生
SELECT s.name
FROM Student s
WHERE EXISTS (
SELECT 1
FROM Enrolment e JOIN Course c ON e.course_id = c.id
WHERE e.student_id = s.id AND c.code = 'COMP3311'
);
窗口函数（Window Functions）——UNSW 期末重点
-- 对每个院系内的学生按 GPA 排名
SELECT name, department, gpa,
RANK() OVER (PARTITION BY department ORDER BY gpa DESC) AS dept_rank
FROM Student;
-- 计算累计平均
SELECT name, semester, grade,
AVG(grade) OVER (PARTITION BY name ORDER BY semester) AS running_avg
FROM Enrolment;
第三部分：ER 图（Entity-Relationship Diagram）
ER 图核心元素
| 元素 | 表示 | 含义 |
|---|---|---|
| 矩形 | 实体（Entity） | 有独立存在的对象 |
| 椭圆 | 属性（Attribute） | 实体的特征，下划线=主键 |
| 菱形 | 关系（Relationship） | 实体间的联系 |
| 双线矩形 | 弱实体（Weak Entity） | 依赖其他实体存在 |
基数约束（Cardinality）
| 关系类型 | 标注 | 例子 |
|---|---|---|
| 一对一（1:1） | 两端各 1 | 员工–停车位 |
| 一对多（1:N） | 一端 1，多端 N | 院系–学生 |
| 多对多（M:N） | 两端 N | 学生–课程 |
ER 图转关系模式
多对多关系必须创建关联表：
Student(id, name, department)
Course(id, code, title)
Enrolment(student_id, course_id, grade, semester)
-- student_id → FK to Student.id
-- course_id → FK to Course.id
-- (student_id, course_id) → PRIMARY KEY
第四部分：规范化（Normalization）——期末必考理论
规范化是减少数据冗余、消除异常（更新/插入/删除异常）的设计过程。
函数依赖（Functional Dependency, FD）
$A \rightarrow B$：知道 A 的值，可以唯一确定 B 的值
例：StudentID → StudentName（知道学号就能确定姓名）
范式层级
第一范式（1NF）：
- 所有属性值都是原子的（不可再分）
- 违反例：一个 Phone 字段存多个电话号码
第二范式（2NF）：
- 在 1NF 基础上
- 每个非主属性完全函数依赖于整个主键（消除部分依赖）
- 违反例：复合主键
(StudentID, CourseID)时，StudentName只依赖StudentID
第三范式（3NF）：
- 在 2NF 基础上
- 消除传递依赖（非主属性不能依赖另一个非主属性）
- 违反例：
StudentID → Department → DepartmentHead（学号→院系→院长，传递依赖）
BCNF（Boyce-Codd Normal Form）：
- 比 3NF 更严格
- 每个函数依赖的左侧都必须是超键（Superkey）
分解示例：
原表：Enrollment(StudentID, CourseID, Instructor, Department)
FDs：(StudentID, CourseID) → Instructor
Instructor → Department ← 违反BCNF（Instructor不是超键）
分解为：
Teaching(StudentID, CourseID, Instructor)
InstructorDept(Instructor, Department)
第五部分：事务与并发控制
ACID 属性（期末必背）
| 属性 | 全称 | 含义 |
|---|---|---|
| A | Atomicity（原子性） | 事务要么全执行，要么全不执行 |
| C | Consistency（一致性） | 事务执行前后数据库保持一致状态 |
| I | Isolation（隔离性） | 并发事务互不干扰 |
| D | Durability（持久性） | 已提交事务的结果永久保存 |
并发问题
| 问题 | 描述 | 例子 |
|---|---|---|
| 脏读（Dirty Read） | 读到未提交的数据 | T1 更新但未提交，T2 读到了 |
| 不可重复读（Non-repeatable Read） | 同一查询两次结果不同 | T1 读两次之间 T2 修改了数据 |
| 幻读（Phantom Read） | 同一查询返回不同行数 | T1 查两次之间 T2 插入了新行 |
隔离级别（从低到高）：
Read Uncommitted → Read Committed → Repeatable Read → Serializable
（解决问题越来越多，并发性能越来越低）
相关资源：
