一、三张表，一条链

这是一个标准的三层链：班级 → 学生 → 成绩。 每张表只指向上游一层，不跨层。 这一点看着平淡，其实是关键。 「不跨层」的意思是：tb_score 里没有 class_id。 学生转班了，他的历史成绩不该跟着变班。 需要「按班统计成绩」时，从成绩连到学生再连班级——多一次 JOIN，换「转班不改历史」。 如果图快在 tb_score 里冗余一个 class_id，那就要在「转班」时同步更新它。 同步一旦漏，数据就自相矛盾：同一个学生的同一条成绩，从学生表查是 2 班，从成绩表查是 1 班。 这类矛盾最难查。

二、三列联合唯一：挡住「录两次」的唯一防线

三个一起才是「一条成绩」。 少任何一个，都会出现「重复」或者「覆盖」。 比如去掉 term，下学期的成绩会把上学期的盖掉——而且不报错，因为 UPDATE 语义上就是这样。 这条经验可以推得很广： 凡是「按时间/学期/批次」产生的数据，时间维度必须进唯一键。 库存按天、账单按月、考勤按日，都是这个道理。

三、三表连接：一条 SQL 看全

v_score 把三张表拼成「人话」，页面上每一行都直接用它。 它带来的最大好处不是省字，是「改一处、全站生效」。 排序口径要和业务一致。 排名按分数降序，同分时按什么？ 教材没写，实务里一般按学号，保证结果稳定。 不写第二排序键的话，同分的人每次刷新顺序可能都不一样。

四、四条约束试撞

第三条是最有价值的。 它证明「同一人同科同学期只能有一条」真的生效了—— 这一条挡住的不是恶意操作，是「老师手抖点了两次保存」。 第四条说明外键默认是 RESTRICT。 要「删班级时学生跟着删」，得写 ON DELETE CASCADE—— 成绩系统里没人这么干，删班级导致删学生，这是事故。 还有一条要记住：CHECK 拦不住「跨行」的规则。 「一个班最多 50 人」这种限制，CHECK 写不出来（它只能看当前这一行）， 得用触发器或者业务层校验。

五、动手 Demo：建三张表，跑通四条约束和三表连接

这一段把三张表建出来、灌上四个班十二人三十六条成绩，然后用四条约束各撞一次， 最后跑一遍三表连接和班级统计。 重点看第四节「丙」那条——重复成绩被拦住，这是这一章的安全网。

# 三表连接（走视图）
sql = '''SELECT sno, sname, cname, subject, score
           FROM v_score
          WHERE cname = ? AND subject = ?
          ORDER BY score DESC'''
for r in c.execute(sql, ('2026级计算机1班', '数学')):
    print(r)

# 四条约束试撞
for label, sql in TESTS:
    try:
        c.execute(sql)
        print(label, '竟然成功了')
    except Exception as e:
        print(label, '被拦住：', e)

# 班级统计：人数 vs 人次
sql = '''SELECT c.cname,
               COUNT(DISTINCT s.id) AS 人数,
               COUNT(sc.id)         AS 条数
          FROM tb_class c
          LEFT JOIN tb_student s ON s.class_id = c.id
          LEFT JOIN tb_score  sc ON sc.stu_id = s.id
         GROUP BY c.id ORDER BY c.id'''
for r in c.execute(sql):
    print(r)

实跑输出：

--------------------------------------------------------------------
数据库设计：班级、学生、成绩三表
--------------------------------------------------------------------


  ① 三张表，一条链
  --------------------------------------------------------------------
| 表 | 一行代表 | 指向上游 |
|---|---|---|
| tb_class | 一个班 | —（链的起点） |
| tb_student | 一个学生 | class_id → class |
| tb_score | 某人某科某学期的一条分 | stu_id → student |
| tb_user | 一个能登进来的人 | stu_id／class_id，看角色 |

  这是一个标准的三层链：班级 → 学生 → 成绩。每张表只指向上游一层，不跨层。
  tb_score 里没有 class_id，这是对的。学生转班了，他的历史成绩不该跟着变班。
  需要「按班统计成绩」时，从 tb_score 连到 tb_student 再连 tb_class——多一次 JOIN，
  但「转班不改历史」这件事才成立。

  ② 成绩表的三个字段都是主键的一部分
  --------------------------------------------------------------------
| 字段 | 为什么在唯一键里 |
|---|---|
| stu_id | 谁的分 |
| subject | 哪一科 |
| term | 哪一学期 |

  三个一起才是「一条成绩」。 少任何一个，都会出现「重复」或者「覆盖」。
  比如去掉 term，下学期的成绩会把上学期的盖掉——而且不报错，因为 UPDATE 语义上就是这样。
  这条经验可以推得很广： 凡是「按时间/学期/批次」产生的数据，时间维度必须进唯一键。
  库存按天、账单按月、考勤按日，都是这个道理。

  ③ 三表连接：一条 SQL 看全
  --------------------------------------------------------------------


  1> SELECT sno, sname, cname, subject, score
     FROM v_score
     WHERE cname = '2026级计算机1班' AND subject = '数学'
     ORDER BY score DESC
  sno       sname   cname            subject  score
  -------------------------------------------------
  20260101  张一鸣  2026级计算机1班  数学     92
  20260102  李思彤  2026级计算机1班  数学     88
  20260104  赵雨欣  2026级计算机1班  数学     78
  20260103  王浩然  2026级计算机1班  数学     65
  (4 行受影响)
  视图 v_score 把三张表拼成「人话」：学号、姓名、班级、科目、学期、分数。页面上每一行都直接用它。
  注意这条 SQL 没有写 ORDER BY sname——排名要看分数，不看名字。
  排序口径要和业务一致：排名按分数降序，同分时按什么？教材没写，实务里一般按学号，保证结果稳定。

  ④ 四条约束试撞
  --------------------------------------------------------------------
| 试的事 | 期望 |
|---|---|
| 插 class_id=99 的学生 | 外键拦住 |
| 插 score=120 的成绩 | CHECK 拦住 |
| 同一人同科同term 插两条 | UNIQUE 拦住 |
| 删掉有学生的班级 | 外键拦住（RESTRICT） |


  甲 不存在的班级：
    → 被拦住：FOREIGN KEY constraint failed

  乙 超范围分数：
    → 被拦住：CHECK constraint failed: score >= 0 AND score <= 100

  丙 重复成绩：
    → 被拦住：UNIQUE constraint failed: tb_score.stu_id, tb_score.subject, tb_score.term

  丁 删有学生的班：
    → 被拦住：FOREIGN KEY constraint failed
  丙那条是最有价值的。
  它证明「同一人同科同学期只能有一条」真的生效了——这一条挡住的不是恶意操作，
  是「老师手抖点了两次保存」。
  丁那条说明外键默认是 RESTRICT。
  要「删班级时学生跟着删」，得写 ON DELETE CASCADE——成绩系统里没人这么干，删班级导致删学生，
  这是事故。

  ⑤ 再确认一次：三张表的行数关系
  --------------------------------------------------------------------


  1> SELECT c.cname, COUNT(DISTINCT s.id) AS 人数, COUNT(sc.id) AS 成绩条数
     FROM tb_class c
     LEFT JOIN tb_student s ON s.class_id = c.id
     LEFT JOIN tb_score sc ON sc.stu_id = s.id
     GROUP BY c.id
     ORDER BY c.id
  cname            人数  成绩条数
  -------------------------------
  2026级计算机1班  4     12
  2026级计算机2班  3     9
  2026级软件1班    3     9
  2025级计算机1班  2     6
  (4 行受影响)
  用 LEFT JOIN 而不是 JOIN 是关键。
  新班没有学生时，JOIN 会让这个班整行消失——管理员会以为班级没建上。
  LEFT JOIN 会显示「人数 0、条数 0」，那才是真相。
  COUNT(DISTINCT s.id) 里的 DISTINCT 也不能省。
  因为一个学生有三条成绩，JOIN 之后学生会重复出现三次，不加 DISTINCT 数出来的人数是「人次」，
  不是「人数」。

统计那张表里「人数」和「条数」差三倍（4 人 12 条）—— 因为每个学生有三科成绩。 这两个数经常被人混用，混用的结果是「班级有 12 个人」这种荒谬结论。 COUNT(DISTINCT s.id) 里的 DISTINCT 不能省。 因为一个学生有三条成绩，JOIN 之后他会重复出现三次—— 不加 DISTINCT 数出来的是「人次」，不是「人数」。 统计用的是 LEFT JOIN 不是 JOIN。 新班没有学生时，JOIN 会让这个班整行消失—— 管理员会以为班级没建上，然后再建一遍，于是重名报错。

30 秒实验

1. 把 tb_score 的唯一键改成 (stu_id, subject)（去掉 term）， 再插一条下学期的数学成绩，看会不会覆盖。 会——而且不报错。
2. 把统计 SQL 里的 LEFT JOIN 改成 JOIN，先建一个空班再跑， 看这个班是不是从结果里消失了。
3. 想一想：如果把「班级统计」写成 COUNT(*)，得到的是 12 还是 4？ 是 12（人次）。 这就是为什么不加 DISTINCT 会错。

常见误区

1. 「为了查询快，在成绩表里冗余一个 class_id」。 转班时要同步更新，一旦漏就自相矛盾。 多一次 JOIN 远比修数据便宜。
2. 「唯一键用 id 就够了」。 id 只保证「不重复的行」，不保证「不重复的成绩」。 业务唯一性得靠业务字段。
3. 「COUNT(*) 和 COUNT(id) 差不多」。 COUNT(*) 数行，COUNT(列) 数非空值，COUNT(DISTINCT 列) 数去重值。 三个数是三件事。

下一篇 20.4 讲 JDBC： 五步走、问号占位符、三种执行方法、还有那个最容易忘的 setAutoCommit(true)。

• ☐ （本篇完，下一篇 20.4 关键技术）

相关笔记

• 系统设计
• 数据库设计
• 连接查询
• 关系的创建与维护
• 🪟 SQL Server从入门到精通 · 索引