波仔
🪟 第5章 数据表操作
📄 一句话概括

一对多：多的那边加外键；一对一：两边主键相同；多对多：必须单独建一张中间表。 中间表的主键是两列合起来，这个设置同时保证了「同一个人不会重复选同一门课」。 判断方法只有一个：拿 A 的一条记录看能对应几条 B，再反过来看一遍。

📝 本章和前面几章的关系

这一节是第 5 章的收尾，也是整章最重要的一节。前面建表、加约束，都是为了把「关系」表达对。 教材 5.5 分三小节：一对一、一对多、多对多，最后带「小结」和「实践与练习」。 实践与练习那个 ER 图，就是让你把这三小节套到一张具体的业务上。 演示里三张表全跑了一遍：班级和学生的一对多、学生和档案的一对一、学生和课程的多对多， 每种关系都用一条查询验证它确实成立了。

一、一对多：最常用的一种

一个班有多个学生，一个学生只属于一个班。实现方法只有一句话：多的那边存一的那边的 id。 所以 tb_stu 里有个 cid。 反过来不行——让 tb_class 里存学生 id 的列表， 那就把多个值塞进一列，违反第一范式，后面查询、更新、删改全都难写。 这个 cid 就是外键。 它保证学生的班级必须真实存在，也就是 5.4 讲的那条规矩。 「一对多」和「外键」是同一件事的两个说法：一个讲关系，一个讲实现。 注意「一」那一边不存任何东西。 班级表里没有「学生列表」这一列—— 这个「列表」是靠 JOIN 查出来的，不是存出来的。这是关系库跟文档库最大的区别。

二、一对一：拆表存不常用的列

一个学生只有一份档案，一份档案只属于一个学生。 实现方法是「两边主键相同」——从表的主键同时是外键，指回主表的主键。 一对一为什么存在？ 因为拆表有实际好处： 一行越小，同样一页能装的行越多，查询越快。所以「常用列放主表、 大文本和极少用的列放从表」是常见做法。 还有一个理由是安全。 学生的身份证号、家庭住址这类敏感字段，放到单独的从表上， 只给少数账户授权——主表可以给所有人看，从表不行。 一对一也可以合成一张表。 如果两边的列都常用、也没有权限区分，硬拆成两张表反而多一次 JOIN。 所以「要不要拆」是个权衡，不是教条。

三、多对多：必须有中间表

一个学生选多门课，一门课被多个学生选。 这种关系没地方放外键——学生表里放课程 id 列表不行，课程表里放学生 id 列表也不行。 唯一的办法是单独建一张中间表，每行记一条「谁选了谁」：

CREATE TABLE tb_pick (
    sno TEXT, cid INT, picktime TEXT,
    PRIMARY KEY (sno, cid)
)

那个复合主键是关键：PRIMARY KEY (sno, cid) 同时保证了「同一个人不会重复选同一门课」。 没有它，同一个人可以把同一门课选两遍，而且数据上看着很正常。 中间表还可以带自己的字段。 上面那个 picktime 就是—— 「什么时候选的」这件事属于「选课」这个动作，既不属于学生也不属于课程。 这是判断「该不该建中间表」的一个信号：有没有只属于这段关系的属性。 多对多拆成两个一对多。 tb_pick 跟 tb_stu 是一对多，跟 tb_course 也是一对多。 所以关系库只有一种基本关系——一对多，其它两种都是它的变形。

四、三种关系对照与判断方法

关系 怎么实现 例子
一对多 多的那边加外键 班级 → 学生
一对一 两边主键相同 学生 → 档案
多对多 单独一张中间表 学生 ↔ 课程

判断方法只有一句话：拿 A 的一条记录去看，能对应几条 B？再反过来看一遍。1 对 N、N 对 1、 N 对 N——只有这三种可能。 「1 对 1」和「1 对 N」的区别，取决于业务规则，不取决于数据现状。现在每个学生只有一个班， 不代表以后不会分班——规则是「一个学生同时只能在一个班」，那就还是一对多。 教材那个「实践与练习」让你画 ER 图，就是做这件事。 画出实体、连上关系、标上 1 和 N， 再翻译成表——这一步做对了，后面所有的查询都顺；做错了，后面全是补丁。 最后提一句：关系不是越多越好。 每多一个外键，插入时多一次校验、删除时多一层约束。 能用业务规则保证的，不一定都要做成外键——但这句话的前提是「业务规则真的可靠」。

五、动手 Demo：三种关系各建一遍

这一段建五张表：班级、学生、学生档案、课程、选课中间表，把三种关系各建一遍， 每种关系都用一条查询验证——一对多看人数、一对一看地址、多对多看选了哪几门。

import sqlite3

c = sqlite3.connect(u":memory:")
c.execute(u"PRAGMA foreign_keys = ON")

# 一对多：班级 1 -- N 学生
c.execute(u"CREATE TABLE tb_class (cid INTEGER PRIMARY KEY, cname TEXT)")
c.execute(u"CREATE TABLE tb_stu (sno TEXT PRIMARY KEY, sname TEXT, cid INT)")
c.execute(u"INSERT INTO tb_class VALUES (1,'一班'), (2,'二班')")
c.executemany(u"INSERT INTO tb_stu VALUES (?,?,?)",
              [('S01','张三',1), ('S02','李四',1), ('S03','王五',2)])

# 一对一：学生 1 -- 1 档案（主键相同）
c.execute(u"CREATE TABLE tb_stu_ext (sno TEXT PRIMARY KEY, addr TEXT)")
c.execute(u"INSERT INTO tb_stu_ext VALUES ('S01','宜昌市西陵区')")

# 多对多：学生 N -- N 课程，中间表
c.execute(u"CREATE TABLE tb_course (cid INTEGER PRIMARY KEY, cname TEXT)")
c.execute(u"CREATE TABLE tb_pick (sno TEXT, cid INT, PRIMARY KEY (sno, cid))")
c.execute(u"INSERT INTO tb_course VALUES (1,'语文'), (2,'数学')")
c.executemany(u"INSERT INTO tb_pick VALUES (?,?)",
              [('S01',1), ('S01',2), ('S02',1)])

q1 = u"""SELECT c.cname, COUNT(s.sno) FROM tb_class c
        LEFT JOIN tb_stu s ON s.cid = c.cid GROUP BY c.cname"""
print(c.execute(q1).fetchall())
q2 = u"""SELECT s.sname, GROUP_CONCAT(k.cname, '/') FROM tb_stu s
        JOIN tb_pick p ON p.sno = s.sno
        JOIN tb_course k ON k.cid = p.cid GROUP BY s.sname"""
print(c.execute(q2).fetchall())

实跑输出：

--------------------------------------------------------------------
一对一、一对多、多对多
--------------------------------------------------------------------


  ① 一对多：一个班多个学生
  --------------------------------------------------------------------
  一个班多个学生，学生表里放班的 id：
  班级  人数
  一班  3
  二班  2
  一对多的实现就一句话：多的那边存一的那边的 id。所以 tb_stu 里有个 cid。
  反过来不行——让 tb_class 里存学生 id 列表，那违反第一范式。

  ② 一对一：拆表存不常用的列
  --------------------------------------------------------------------
  两张表用同一个主键对应，一人一条：
  sno  sname  addr
  S01  张三     宜昌市西陵区
  S02  李四     宜昌市伍家岗区
  一对一的实现是「两边主键相同」，从表的主键同时是外键。
  为什么要拆？常见理由是「常用列和极少用的列分开」，
  一行越小，同样一页装的行越多，查询越快。大文本字段拆出去就是这个道理。

  ③ 多对多：必须有中间表
  --------------------------------------------------------------------
  一个学生选多门课，一门课被多个学生选：
  学生  选的课
  孙七  数学
  张三  语文、数学
  李四  语文、英语
  多对多必须有一张中间表，因为「谁选了谁」这件事本身是个集合。
  中间表的主键是两列合起来——这一个设置同时保证了「同一个人不会重复选同一门课」。
  GROUP_CONCAT 是 SQLite 的函数，SQL Server 里对应 STRING_AGG（2017 起）。
  2017 之前只能用 FOR XML PATH 那种绕法，写法很难看。

  ④ 三种关系怎么认
  --------------------------------------------------------------------
| 关系 | 怎么实现 | 例子 |
|---|---|---|
| 一对多 | 多的那边加外键 | 班级 → 学生 |
| 一对一 | 两边主键相同 | 学生 → 学生档案 |
| 多对多 | 单独一张中间表 | 学生 ↔ 课程 |

  判断方法：拿 A 的一条记录去看，能对应几条 B？再反过来看一遍。
  1 对 N、N 对 1、N 对 N——只有这三种可能，没有第四种。
  教材 5.5 最后那个「实践与练习」，就是让你画一张三张表的 ER 图，按这个思路走就行。

一对多那张表里，班级表没有「学生列表」这一列。 人数是靠 LEFT JOIN 加 COUNT 查出来的—— 关系是查出来的，不是存出来的。 这是关系库最核心的一点。 LEFT JOIN 而不是 JOIN，因为要显示人数为 0 的班级。 用内连接的话，没有学生的班级会整行消失——这是统计「哪个班没人」时最常见的错。 一对一那张表用同一个 sno 当主键。 主键唯一，所以一个学生只能有一条档案； 主键非空，所以不能有「没有学生的档案」。两条规矩一次到位，不用额外写约束。 多对多那段的关键是 PRIMARY KEY (sno, cid)。 有了它，重复选课插不进去； 没有它，同一个人同一门课可以有两条记录， 而且数据看着完全正常——等到统计「选课人次」的时候才发现对不上。

30 秒实验

  1. 把你手上的一张业务表画成 ER 图：实体用方框、关系用连线、两端标 1 和 N。 画完再看有没有哪条线两端都是 N——那就是需要中间表的地方。
  2. 试着往 tb_pick 里插一条重复的 ('S01', 1)，看报什么错。 这就是复合主键在起作用，它不报「重复选课」，报的是主键冲突。
  3. 想一想：为什么一对一不干脆合成一张表？ 三个理由：一行越小查得越快、敏感字段可以单独授权、不常用的列不拖累常用查询。

常见误区

  1. 「多对多就在一个字段里存 id 列表」。cid_list = '1,2,3' 这种写法违反第一范式：查不了、 改不了、也保证不了一致性。必须用中间表。
  2. 「一对一没用，都合成一张表」。合成之后行变宽，一页装的行变少，常用查询跟着变慢。 而且权限没法分层——要看敏感字段的、和不需要看的，只能给同一张表的权限。
  3. 「关系建得越全越好」。每多一个外键，插入多一次校验、删除多一层约束。 能用业务规则保证的可以不加外键，但前提是那条规则真的可靠——多数时候它不可靠。
自测三问（点开对答案） 1. 一对多的外键放在哪一边？ —— **放在「多」的那一边。** 一个班多个学生，外键 `cid` 就在学生表里。 **因为外键一列只能存一个值**，放在班级表里就变成「一个班只能有一个学生」了。 2. 多对多的中间表为什么主键要用两列？ —— **为了保证「同一个人不会重复选同一门课」。** 两列合起来唯一， 单独一列都不唯一——一个人可以选多门课，一门课可以被多人选。**这个设置一次解决两个问题。** 3. 怎么判断两个实体是一对多还是多对多？ —— **拿 A 的一条记录看能对应几条 B，再反过来看一遍。** 两边都是多就是多对多。 **注意判断依据是业务规则，不是现在的数据**——现在每个学生一个班，不代表规则是一对一。

第 5 章到此结束。 下一篇进第 6 章 6.1，开始讲 SQL 语言本身： 它分几类、T-SQL 跟标准 SQL 差在哪、为什么同一个功能各家写法不一样。

相关笔记