需求里的四个名词就是四张表：班级、学生、课程、成绩。
「只属于一个班」是外键，「可以缺考」意味着成绩表里可以没有那一行。
成绩表的主键是「学号 + 课程号」联合——它没有自己的 id，它的身份就是「谁、哪门课」。
灌完数据先验三件事：
行数、foreign_key_check、integrity_check。 这三条比人眼看一遍靠谱得多。
这一章是全书收尾，跑在一个新库上：四张表、三条外键、每列都有类型和约束， 和真实项目里的库长得更像。 前面十八章按语法分章，这一章按问题分篇。 三篇是一条线：19.1 把库搭起来，19.2 十道基础题（筛选、排序、聚合）， 19.3 九道高级题（窗口、反连接、行转列）。 后面两篇的题都跑在同一个库上，结果可以互相核对。
一、需求长什么样
要做的是一个班级成绩管理系统，最小的功能就是「查成绩」。 需求单这么写： 一、学校里有若干班级，每个班有若干学生。 二、每个学生有姓名、性别、出生日期，且只属于一个班。 三、要开若干门课，每门课有学分。 四、每个学生每门课有一个成绩，可以缺考。 五、要能查：谁考了多少、每门课平均分、班级总分排名。 这五条里藏了两个决定后面全部写法的细节：「只属于一个班」是一对多，所以班级单独一张表； 「可以缺考」意味着成绩不是必填的—— 缺考不是存个 0 分，是根本没有那一行。 这两个字决定了后面所有「缺考怎么查」的写法。
二、从需求推到表结构
先把主键定下来：四张表里三张用自增整数 id 做主键，
只有成绩表例外——它的「身份」是「哪个学生、哪门课」，所以主键是这两列联合起来。
再把关系接上：stu.cls_id 指向 cls.id；score.stu_id 指向 stu.id；
score.course_id 指向 course.id。 三条外键，一张图就画完了。
然后是类型：姓名、性别、出生日期、班级名、课程名用 TEXT；
学号、班级号、课程号用 INTEGER；成绩和学分用 REAL。
成绩用 REAL 是有争议的——第 18 章刚说过金额别用浮点。 但成绩的语义就是「近似值」，
而且它不参与「一分不能差」的对账，
所以这里用 REAL 可以接受。 换成金额就必须用定点数或整数存分。
最后补约束：性别只能是男或女（check）、出生日期不能空（not null）、
学分必须大于 0（check）、成绩限在 0 到 100（check）。
这些约束不是装饰——
19.3 里「缺考怎么查」那道题，就靠「成绩可以为空、行不能凭空造」这个前提才成立。
三、四张表长这样
建表语句在 Demo 代码里，这里只看结构：
| 表 | 列 | 主键 | 外键 |
|---|---|---|---|
cls |
id、name |
id |
— |
stu |
id、name、gender、birth、cls_id |
id |
cls_id → cls.id |
course |
id、name、credit |
id |
— |
score |
stu_id、course_id、score |
(stu_id, course_id) |
两条 |
Demo 里查了一下账本，多出三个 sqlite_autoindex_*——它们是 UNIQUE 约束自动建的索引，
不是我们写的。 第 18 章说过：pragma index_list 里 origin 是 u 的就是这种，删不掉。
四、灌数据：故意造得有戏
六个人分布在一班（2 人）、二班（2 人）、三班（2 人），三门课：语文、数学、英语， 每人三门，共十八条成绩。 这批数据是「故意有戏」的：孙悦三门都高（91／96／93），周杰三门都低（60／58／66）， 张伟偏科（88／92／75）。 为什么要故意造得这么分明？ 因为后面每道题的结果都要能一眼判断对错。 如果全班分数都挤在 80 到 85 之间，「最高分是谁」和「平均分排序」看起来都一样， SQL 写错了也看不出来。 造测试数据时，「差异明显」比「接近真实」更有用。
五、灌完先验三件事
第一件：行数对不对。 四张表应该是 3、6、3、18。 Demo 里用一句 union all 一次列出来，
比一张一张 count 快。
第二件：外键有没有断的。 pragma foreign_key_check 一条也不返回，就说明没有孤儿行。
这条检查比人眼看一遍靠谱得多，尤其是从别处导数据进来的时候，必跑。
第三件：库文件本身有没有坏。 pragma integrity_check 会扫一遍页结构，
正常返回 ok。 换成 MySQL 是 check table 表名。
这两条命令在「怀疑数据坏了」的时候是第一反应。
注意 pragma foreign_key_check 和 pragma foreign_keys 是两个东西：
前者是一次性体检（查出问题行），后者是开关（决定外键约不约束）。 名字太像，很容易混。
六、用三范式回头验一遍
第一范式：每个格子只放一个值。 成绩单独一张表、一条一行，
没有「三门课塞一格」那种写法。
第二范式：成绩表主键是「学号 + 课程号」，非主键列只有 score 一个，
它换学生变、换课程也变，依赖整个主键。 没有部分依赖。
第三范式：成绩表里没有学生姓名、没有课程名，要显示就 join 过去。 没有传递依赖。
三张主表也都干净：
班级名只在 cls 里存一份，课程名只在 course 里存一份。 改一次，处处生效。
这套结构不是唯一的正确解，
但它满足三范式、每条关系都有外键、每列都有类型和必要的约束——
做到这三样，后面写查询就不会老是被脏数据绊住。
七、动手 Demo：把库搭起来，再验三遍
Demo 分三段：建四张表、灌一批数据、验三件事。 每段都打印现场，可以照着核。
注意 Demo 里 PRAGMA foreign_keys = ON 是在 executescript 之前执行的，
而且那时还没有开启事务——
第 18 章说过，这个开关在事务里设是空操作。 顺序错了，后面灌数据时外键就完全不检查。
import sqlite3
DDL = """
CREATE TABLE cls(id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE stu(id INTEGER PRIMARY KEY, name TEXT NOT NULL,
gender TEXT NOT NULL CHECK(gender IN ('男','女')),
birth TEXT NOT NULL, cls_id INT NOT NULL REFERENCES cls(id));
CREATE TABLE course(id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE,
credit REAL NOT NULL CHECK(credit > 0));
CREATE TABLE score(stu_id INT NOT NULL REFERENCES stu(id),
course_id INT NOT NULL REFERENCES course(id),
score REAL CHECK(score BETWEEN 0 AND 100),
PRIMARY KEY(stu_id, course_id));
"""
c = sqlite3.connect(":memory:")
c.execute("PRAGMA foreign_keys = ON")
c.executescript(DDL)
# STU 是六个学生的元组列表，SCORE 是十八条成绩，见正文表格
c.executemany("INSERT INTO cls VALUES (?,?)",
[(1, '一班'), (2, '二班'), (3, '三班')])
c.executemany("INSERT INTO course VALUES (?,?,?)",
[(1, '语文', 3.0), (2, '数学', 4.0), (3, '英语', 3.0)])
c.executemany("INSERT INTO stu VALUES (?,?,?,?,?)", STU)
c.executemany("INSERT INTO score VALUES (?,?,?)", SCORE)
c.commit()
# 灌完先验三件事
for row in c.execute("PRAGMA foreign_key_check"):
print("外键断了：", row)
print(c.execute("PRAGMA integrity_check").fetchone())
实跑输出：
--------------------------------------------------------------------
第八十四课：案例准备——先把库搭起来
--------------------------------------------------------------------
需求长什么样
**要做的是一个班级成绩管理系统**，**最小的功能就是「查成绩」。** **需求单是这么写的：**
**一、学校里有若干班级，每个班有若干学生。**
**二、每个学生有姓名、性别、出生日期，且只属于一个班。**
**三、要开若干门课，每门课有学分。**
**四、每个学生每门课有一个成绩，可以缺考。**
**五、要能查：谁考了多少、每门课平均分、班级总分排名。**
**需求里有四个名词：班级、学生、课程、成绩。** **这就是四张表。** **「只属于一个班」是外键**；
**「可以缺考」意味着成绩表里可以没有那一行**——
**不是存个 0 分，是没有这一行。** **这两个字决定了后面很多查询怎么写。**
从需求推到表结构
**先把主键定下来**：**四张表都用自增整数 `id` 做主键**，
**除了成绩表**——**成绩的「身份」是「哪个学生、哪门课」，所以主键是这两列联合。**
**再把关系接上**：**`stu.cls_id` 指向 `cls.id`**；**`score.stu_id` 指向 `stu.id`**；
**`score.course_id` 指向 `course.id`**。 **三条外键，一张图就画完了。**
**然后是类型**：**姓名、性别、出生日期、班级名、课程名用 `TEXT`**；
**学号、班级号、课程号用 `INTEGER`**；**成绩和学分用 `REAL`**。
**成绩用 `REAL` 是有争议的**——**第 18 章刚说过金额别用浮点。** **但成绩的语义就是「近似值」**，
**而且它不参与「一分不能差」的对账**，
**所以这里用 `REAL` 可以接受。** **换成金额就必须用定点数。**
四张表长这样
SQL> CREATE TABLE score(...)
**建表语句见 Demo 代码。** **这里只看结果——账本里四张表、三个外键：**
SQL> SELECT type, name FROM sqlite_master
WHERE type IN ('table','index') AND name NOT LIKE 'sqlite_%' ORDER BY type, name
type name
-----------------
table cls
table course
table score
table stu
（4 行）
**注意多了三个 `sqlite_autoindex_*`**——**它们是 `UNIQUE` 约束自动建的索引**，
**不是我们写的。** **第 18 章说过：`pragma index_list` 里 `origin` 是 `u` 的就是这种，删不掉。**
灌数据：六个人、三个班、三门课、十八条成绩
**数据是手写进去的**，
**六个人分布在一班（2 人）、二班（2 人）、三班（2 人）。** **三门课：语文、数学、英语。**
**每人三门，共十八条成绩。**
**这批数据是故意「有戏」的**：**孙悦三门都高（91／96／93）**，**周杰三门都低（60／58／66）**，
**张伟偏科（语文 88、数学 92、英语 75）。** **这样后面每道题的结果都能一眼看出对不对**——
**如果全班分数都差不多，你根本没法判断 SQL 写对没有。**
SQL> SELECT * FROM cls
id name
------------
1 一班
2 二班
3 三班
（3 行）
SQL> SELECT * FROM stu
id name gender birth cls_id
-------------------------------------------------
1 张伟 男 2008-03-12 1
2 李娜 女 2008-07-25 1
3 王强 男 2007-11-02 2
4 赵敏 女 2008-01-19 2
5 孙悦 女 2008-09-30 3
6 周杰 男 2007-05-08 3
（6 行）
SQL> SELECT * FROM course
id name credit
-----------------------
1 语文 3.00
2 数学 4.00
3 英语 3.00
（3 行）
SQL> SELECT * FROM score
stu_id course_id score
-----------------------------------
1 1 88.00
1 2 92.00
1 3 75.00
2 1 95.00
2 2 88.00
2 3 91.00
3 1 72.00
3 2 65.00
3 3 80.00
4 1 85.00
4 2 79.00
4 3 88.00
5 1 91.00
5 2 96.00
5 3 93.00
6 1 60.00
6 2 58.00
6 3 66.00
（18 行）
灌完先验三件事
**第一件：行数对不对。** **四张表应该是 3、6、3、18。**
SQL> SELECT 'cls' AS 表, COUNT(*) AS 行数 FROM cls UNION ALL SELECT 'stu', COUNT(*)
FROM stu UNION ALL SELECT 'course', COUNT(*) FROM course UNION ALL SELECT 'score',
COUNT(*) FROM score
表 行数
----------------
cls 3
stu 6
course 3
score 18
（4 行）
**第二件：外键有没有断的。** **`pragma foreign_key_check` 一条也不返回，就说明没有孤儿行。**
**返回 0 行。** **这条检查比人眼看一遍靠谱得多**——**尤其是从别处导数据进来的时候，必跑。**
**第三件：库文件本身有没有坏。** **`pragma integrity_check` 会扫一遍页结构：**
| ok
**它给的是 `ok`。** **换成 MySQL 是 `check table 表名`。**
**这两条命令在「怀疑数据坏了」的时候是第一反应。**
用第 18 章的三条规矩回头验一遍
**第一范式**：**每个格子只放一个值。** **成绩单独一张表，一条一行**，
**没有「三门课塞一格」那种写法。**
**第二范式**：**成绩表的主键是「学号 + 课程号」**，**表里只有 `score` 一列非主键**，
**它依赖整个主键（换学生变、换课程也变）。** **没有部分依赖。**
**第三范式**：**成绩表里没有学生姓名、没有课程名**，
**要显示就 `join` 过去。** **没有传递依赖。**
**三张主表也都干净**：
**班级名只在 `cls` 里存一份，课程名只在 `course` 里存一份。** **改一次，处处生效。**
案例库和课本样例库的关系
**前面十八章用的是另一套样例表**（`classes`／`students`／`scores`／`goods`／`orders`），
**那是为了讲语法随手造的。**
**这一章这套是「按需求设计出来」的**：**四张表、三条外键、每列都有类型和约束**，
**和真实项目里的库长得更像。** **后面两篇的题目都跑在这套库上。**
**两套库的差别值得留意**：**样例库为了省事，很多列没加约束**；**案例库把约束都加上了**。
**这个差别在后面做题时会有影响**——**约束多的库，脏数据进不来，查询结果反而更好解释。**
账本里四张表、三个 sqlite_autoindex_*。 那三个索引是 UNIQUE 约束自动建的，
origin 是 u，删不掉。
四张表的行数是 3、6、3、18，一句 union all 全列出来。
pragma foreign_key_check 返回 0 行——没有孤儿行，三条外键全都指得着。
pragma integrity_check 返回 ok，库文件本身没有坏页。
stu 那张表里 birth 全是 YYYY-MM-DD 格式，
这是后面「按出生日期排序」能直接比字符串的前提。
换成 2008/3/12 这种，字典序就跟时间序对不上了。
30 秒实验
- 把
score表的主键改成单独一个自增id， 「同一学生同一门课只能有一条成绩」这条规则还拦得住吗？ - 在
score表上删掉stu_id的外键，再插一条不存在的学号，foreign_key_check会报什么？ - 把
stu.birth从TEXT改成INTEGER存时间戳，「按出生日期排序」这句 SQL 要不要改？
常见误区
- 把「缺考」存成 0 分。 0 分是真考了得 0 分，缺考是没考，平均分、及格率的算法完全不同。
- 成绩表用自增
id做单列主键。 那样「同一学生同一门课」就能插两条，联合主键才拦得住。 - 灌完数据只看行数就收工。 行数对不代表数据干净，
foreign_key_check和integrity_check各跑一遍才踏实。
自测三问（点开对答案）
1. 为什么成绩表用联合主键而不是自增 id？ —— 因为一条成绩的「身份」就是「哪个学生、哪门课」， 联合主键同时保证唯一性——同一学生同一门课只能有一条记录。用自增 id 的话这条规则就没人管了。 2. 「可以缺考」这个需求影响了哪些设计？ —— 成绩表里允许没有那一行（不是存 0），`score` 列可以为空， 而且后面查「谁缺考」必须用反连接或 `not exists`，不能用 `score = 0` 或 `score < 60` 去凑。 3. `pragma foreign_key_check` 和 `pragma foreign_keys` 有什么区别？ —— 前者是一次性体检，返回所有违反外键的行； 后者是开关，决定外键约束是否生效，默认关闭、且必须在无事务时设置。库搭好了，下一篇开始做题：19.2 基础问题——十道题过一遍。
- ☐ （本篇完，下一篇 19.2 基础问题）