1.2 关系型数据库的基本理论 · 波仔的 AI 编程实战课

📄 一句话概括

关系型数据库只有一种数据结构：二维表。 范式不是数学考试，它只回答一个问题：同一件事，我是不是被迫写了很多遍？写了很多遍，改的时候就会漏；漏一次，数据就没有真相了。

📝 本章和前面几章的关系

上一篇讲的是「它怎么来的」，这一篇讲「它凭什么这么组织数据」。 这是全书唯一一篇纯理论，但后面每一章都在用它。 4.3 讲的连接查询、9.4 讲的约束、9.2 讲的建表，根子都在这一篇：表怎么切、键怎么定。 切错了，后面十八篇都在还债。

一、二维表：三个词就够

表（table）是一张二维网格： 行（row）是一条记录，列（column）是一个属性。行和列的交叉点叫单元格，它存一个值， 这个值不能再拆。 「不能再拆」是第一条硬规矩，叫第一范式（1NF）。教材里那个反例很典型：一格里写「张三、李四」，看着省事，可你要查「李四选了哪门课」，就得去拆字符串。 为什么一定要二维？因为二维网格能用集合运算处理：选行、选列、按条件合并。 一旦允许「格子里套表格」，数学就做不下去了，做不下去就没法用一句 SQL 描述你要什么。

二、键：给每一行一个说得清的身份

超键：能唯一确定一行的属性组合（可以带多余的列）。 候选键：最精简的超键（去掉任何一列就不再唯一）。主键：从候选键里挑一个当代表，一张表只能有一个，且不许为空。 外键是另一回事：它不是「唯一」，是「引用」。员工表里的部门号， 唯一性不重要，重要的是它得在部门表里存在。

键	管什么	能不能重复	能不能为空
主键	唯一标识本表一行	不能	不能
唯一键	唯一标识本表一行	不能	能（一个）
外键	指向另一张表的主键	能	能（表示「暂无」）

这三行差别，第 9.4 节会逐条用 SQL 验一遍。现在只要记住：主键管身份，外键管关系。

三、E-R 模型：先在纸上画，再落到表

E-R 模型（实体-联系模型）是设计阶段的草图，不是数据库里的东西。三个元素：实体（矩形，比如「学生」）、属性（椭圆，比如「姓名」）、联系（菱形，比如「选课」）。联系分三种，转成表的方法完全不同：

联系	例子	怎么落表
一对一（1:1）	学生—学生证	把一边的主键放进另一边，别单开表
一对多（1:n）	部门—员工	把「一」的主键放进「多」那边当外键
多对多（m:n）	学生—课程	必须单开一张中间表，两个外键做联合主键

「多对多必须单开表」这条最容易踩。有人在学生表里加一列「所选课程」，塞成「数据库,操作系统」——这就退回 1NF 之前了，查「谁选了数据库」只能 like 模糊匹配。

四、三范式：为了少改几行

第一范式（1NF）：每格只放一个值。 做法是拆列或者拆行。 第二范式（2NF）：非主键列必须依赖整个主键。这条只在联合主键的表上才可能违反—— 成绩表主键是（学号，课程），「姓名」只依赖学号，不依赖课程，就该挪走。 第三范式（3NF）：非主键列之间不能互相依赖。成绩表里存了「系名」，而「系名」由「学号」决定、「系主任」又由「系名」决定——传递依赖，也得挪走。 一句话记法： 1NF 管格子、2NF 管联合主键、3NF 管列与列之间的拐弯关系。 范式不是越严越好。报表库经常故意反范式：把系名冗余进成绩表，省掉一次连接。 代价是改的时候要同步多处——这就是「用写入的麻烦换读取的快」，是个权衡，不是错误。

五、动手 Demo：把一张烂表跑出四种异常，再拆成四张好表

这一篇的 Demo 是本课最值得亲眼看一遍的。同一份数据，先用一张「什么都塞在一起」的表跑， 四种异常会一条条当场出现；再拆成四张表，同样的问题一个都不剩。数据很小：两个系、三个学生、两门课、四条成绩，小到每一行你都能手算， 这样异常才看得清是谁造成的。

# 一张没规范化的表：系、课程、老师、成绩挤在一起
c.execute("CREATE TABLE raw_score ("
          "sno TEXT NOT NULL, sname TEXT, dept TEXT, "
          "course TEXT NOT NULL, teacher TEXT, score REAL, "
          "PRIMARY KEY (sno, course))")

# 问题一：同一个事实写了几遍
c.execute("select dept, count(*) from raw_score group by dept")
c.execute("select teacher, count(*) from raw_score group by teacher")

# 问题二：只改一行，同一门课出现两个老师
c.execute("update raw_score set teacher = '王建国' "
          "where sno = 'S01' and course = '数据库'")
c.execute("select distinct course, teacher from raw_score "
          "where course = '数据库'")

# 问题三：还没人选的课插不进去（主键凑不齐）
c.execute("insert into raw_score (course, teacher) "
          "values ('编译原理','赵老师')")

# 拆成四张表，一个事实只落一处
c.execute("CREATE TABLE course_t (course TEXT PRIMARY KEY, teacher TEXT NOT NULL)")
c.execute("CREATE TABLE score_t (sno TEXT, course TEXT, score REAL, "
          "PRIMARY KEY (sno, course))")

实跑输出：

--------------------------------------------------------------------
第二课：关系型数据库的基本理论——三范式是为了少改几行
--------------------------------------------------------------------

  **范式不是数学考试，它只回答一个问题：**
  **同一件事，我是不是被迫写好几十遍？**
  **写了一百遍，改的时候就得改一百处，改漏一处，数据就前后不一致。**


  先造一张没规范化的表
  --------------------------------------------------------------------

  SQL> CREATE TABLE raw_score (sno TEXT NOT NULL, sname TEXT, dept TEXT, course TEXT NOT NULL, teacher TEXT, score REAL, PRIMARY KEY (sno, course))

  SQL> select * from raw_score order by sno, course
  SNO SNAME DEPT     COURSE   TEACHER SCORE
  -----------------------------------------
  S01 张伟  计算机系 操作系统 李老师  92
  S01 张伟  计算机系 数据库   王老师  88
  S02 李娜  计算机系 数据库   王老师  95
  S03 王强  数学系   数据库   王老师  72
  已选择 4 行

  问题一：同一个事实写了几遍
  --------------------------------------------------------------------

  SQL> select dept, count(*) as rows_of_dept from raw_score group by dept
  DEPT     ROWS_OF_DEPT
  ---------------------
  数学系   1
  计算机系 3
  已选择 2 行
  **「计算机系」写了三遍。**
  **只要有一行写错成「计算机」，同一个系就变成了两个系。**

  SQL> select teacher, count(*) as times_written from raw_score group by teacher
  TEACHER TIMES_WRITTEN
  ---------------------
  李老师  1
  王老师  3
  已选择 2 行
  **王老师写了三遍**，**他要是换了名字，得改三处。**

  问题二：改一处忘一处，数据就自相矛盾
  --------------------------------------------------------------------

  SQL> update raw_score set teacher = '王建国' where sno = 'S01' and course = '数据库'

  SQL> select distinct course, teacher from raw_score where course = '数据库'
  COURSE TEACHER
  --------------
  数据库 王建国
  数据库 王老师
  已选择 2 行
  **同一门「数据库」，teacher 现在是两个人。**
  **这就是「更新异常」：**
  **冗余没消掉，改的时候就会漏，漏了就没有真相了。**

  问题三：插不进、删不掉
  --------------------------------------------------------------------
  **想加一门新开的课「编译原理」，可还没人选。**
  **这张表的主键是（学号，课程）**——**没学生就没有学号，主键凑不齐，插不进去。**
  **真跑一次，报错 `NOT NULL constraint failed: raw_score.sno`**
  **主键列不许为空，所以「还没有人选的课」在这张表里根本没有位置。** **这叫插入异常。**
  **这里补一句引擎差异：**
  **SQLite 有个老毛病——非整数类型的主键列允许 NULL（为了兼容旧库）；**
  **所以上面建表时我显式写了 `NOT NULL`，不然这条根本拦不住。**
  **Oracle 的主键列天生不许为空，没有这个坑。**

  **反过来还有一种：删掉最后一条记录，顺带把别的事实也删了。**

  SQL> delete from raw_score where sno = 'S03'

  SQL> select * from raw_score order by sno, course
  SNO SNAME DEPT     COURSE   TEACHER SCORE
  -----------------------------------------
  S01 张伟  计算机系 操作系统 李老师  92
  S01 张伟  计算机系 数据库   王建国  88
  S02 李娜  计算机系 数据库   王老师  95
  已选择 3 行
  **王强退课了，可「数学系」这个系也一起消失了**——
  **它唯一的记录就是这条成绩。** **这叫删除异常：想删的是一行，删掉的是两类事实。**

  按三范式拆开：一事实一处
  --------------------------------------------------------------------

  SQL> CREATE TABLE dept_t (dept TEXT PRIMARY KEY)
  SQL> CREATE TABLE student_t (sno TEXT PRIMARY KEY, sname TEXT NOT NULL, dept TEXT NOT NULL REFERENCES dept_t(dept))
  SQL> CREATE TABLE course_t (course TEXT PRIMARY KEY, teacher TEXT NOT NULL)
  SQL> CREATE TABLE score_t (sno TEXT REFERENCES student_t(sno), course TEXT REFERENCES course_t(course), score REAL, PRIMARY KEY (sno, course))
  SQL> INSERT INTO dept_t VALUES ('计算机系')
  SQL> INSERT INTO dept_t VALUES ('数学系')
  SQL> INSERT INTO student_t VALUES ('S01','张伟','计算机系')
  SQL> INSERT INTO student_t VALUES ('S02','李娜','计算机系')
  SQL> INSERT INTO student_t VALUES ('S03','王强','数学系')
  SQL> INSERT INTO course_t VALUES ('数据库','王老师')
  SQL> INSERT INTO course_t VALUES ('操作系统','李老师')
  SQL> INSERT INTO score_t VALUES ('S01','数据库',88)
  SQL> INSERT INTO score_t VALUES ('S01','操作系统',92)
  SQL> INSERT INTO score_t VALUES ('S02','数据库',95)
  SQL> INSERT INTO score_t VALUES ('S03','数据库',72)
  **四张表，四个主键，一个事实只落一处：**
  - `dept_t`  **2 行**
  - `student_t`  **3 行**
  - `course_t`  **2 行**
  - `score_t`  **4 行**

  SQL> select s.sname, sc.course, sc.score, co.teacher from score_t sc join student_t s on s.sno = sc.sno join course_t co on co.course = sc.course order by s.sno, sc.course
  SNAME COURSE   SCORE TEACHER
  ----------------------------
  张伟  操作系统 92    李老师
  张伟  数据库   88    王老师
  李娜  数据库   95    王老师
  王强  数据库   72    王老师
  已选择 4 行
  **查出来的东西一模一样，但王老师只存了一行。**
  **改名就改一处，改完全库一致。**

  新开的课现在插得进去了
  --------------------------------------------------------------------

  SQL> insert into course_t values ('编译原理','赵老师')

  SQL> select * from course_t
  COURSE   TEACHER
  ----------------
  数据库   王老师
  操作系统 李老师
  编译原理 赵老师
  已选择 3 行
  **没有人选也不影响开课。这就是拆分买回来的东西。**

  范式到这里为止，别再拆
  --------------------------------------------------------------------
  **把 dept 再拆成「系—楼—电话」三张表，查询要连五次，收益却接近于零。**
  **范式是「为了少改几行」，不是「拆得越碎越专业」。** **拆到「一个事实只存一处」就够了。**

  SQL> select d.dept, count(s.sno) as stu, round(avg(sc.score), 2) as avg_score from dept_t d left join student_t s on s.dept = d.dept left join score_t sc on sc.sno = s.sno group by d.dept
  DEPT     STU AVG_SCORE
  ----------------------
  数学系   1   72
  计算机系 3   91.67
  已选择 2 行

冗余是肉眼可见的：「计算机系」在三行里写了三遍，「王老师」也在三行里写了三遍。 这不是排版问题，是同一个事实存了多份。 更新异常最阴。 只改了一行，同一门「数据库」就出现了两个老师——系统没报错，它只是悄悄地不一致了。 这类问题不会在测试里暴露，只会在半年后的报表里对不上。 插入异常是主键造成的：新开的课还没人 selected，就没有学号，联合主键凑不齐，插不进去。 删除异常正好相反： 删掉王强那行成绩，「数学系」这个系也跟着消失——它唯一的记录就是这条成绩。 这里有个引擎差异要说明： SQLite 的老毛病是非整数类型的主键列允许 NULL（为兼容旧库），所以我在建表时显式写了 NOT NULL，否则插入异常根本演示不出来。 Oracle 的主键列天生不许为空，没有这个坑。 拆完四张表，行数从 4 行变成 2＋3＋2＋4＝11 行——多了。 这是范式的代价：多存了几行键，省掉了无数处冗余。 换来的是：改一次就够，插得进，删得掉。

30 秒实验

把 raw_score 里的「计算机系」改成「计算机」，再按 dept 分组——你会看到同一个系被拆成两个组，各一两人。 这就是「一个字打错，统计就错」的最小例子。
在拆好的 student_t 上，把「计算机系」改名成「计算机学院」——只需要改 dept_t 一行，三个学生全部跟着变。 对比一下：在 raw_score 上做同样的事，要改三行，还容易漏。
给 score_t 加一条 ('S99','数据库',60)（这个学号不存在），看看在没有外键约束时能不能插进去。 再想想：为什么 9.4 节要给外键？

常见误区

以为「范式」是数据库自动帮你做的。 数据库只检查你写的约束， 表怎么切完全是你的事。 切错了它一声不响。
把「一格放多个值」当成省事。 用逗号分隔存「所选课程」， 看起来一行搞定，实际上把「查询」变成了「字符串处理」，索引、连接、统计全部失效。
以为三范式必须严格到 3NF 以上。 报表、日志、缓存表经常故意冗余。 判断标准是「这份冗余会不会让我改两处」，不是「有没有违反范式」。

自测三问（点开对答案）

1. 「姓名」这一列在成绩表里，为什么违反第二范式？ —— 因为成绩表主键是（学号，课程），**姓名只依赖学号、不依赖课程**——这叫部分依赖。 **2NF 要求非主键列依赖整个主键。** 2. 学生和课程是多对多，怎么落表？ —— **单开一张中间表**，两个外键（学号、课程号）做联合主键。不能塞成「课程1,课程2」这种字符串列。 3. 反范式什么时候是对的？ —— **当「读远多于写」的时候。** 报表库把系名冗余进去，省一次连接；代价是改名要同步多处。 **这是取舍，不是错误。**

下一篇讲安装与卸载。 沙箱里装不了 Oracle，所以那篇改成「把安装程序会检查的东西自己量一遍」，顺便说清卸载为什么不能只删目录。

☐ （本篇完，下一篇 1.3 Oracle 19c 的安装与卸载）