两张表的建表语句（PPT 第 8 页）#

PPT 第 8 页把这两张表的建表语句直接摆出来（下面按课程脚本 多表关系.sql 的写法整理，加上了注释）：

1
-- 部门表（"一"的一方）
2
create table dept(
3
    id int unsigned primary key auto_increment comment 'ID, 主键',
4
    name varchar(10) not null unique comment '部门名称',
5
    create_time datetime default null comment '创建时间',
6
    update_time datetime default null comment '修改时间'
7
) comment '部门表';
8

9
-- 员工表（"多"的一方）
10
create table emp(
11
    id int unsigned primary key auto_increment comment 'ID,主键',
12
    username varchar(20) not null unique comment '用户名',
13
    password varchar(32) default '123456' comment '密码',
14
    name varchar(10) not null comment '姓名',
15
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
16
    phone char(11) not null unique comment '手机号',
17
    job tinyint unsigned comment '职位, 1 班主任, 2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
18
    salary int unsigned comment '薪资',
19
    image varchar(255) comment '头像',
20
    entry_date date comment '入职日期',
21
    dept_id int unsigned comment '关联的部门ID',      -- ← 就是这一个字段，把两张表连了起来
22
    create_time datetime comment '创建时间',
23
    update_time datetime comment '修改时间'
24
) comment '员工表';

两张表长什么样，PPT 第 9 页给了数据截图：

父表与子表（PPT 第 9-10 页）#

PPT 第 9 页在两张表之间标了”一”和”多”，并给出了实现方式（这是这一节最核心的一句话）：

一对多的关系如何实现？——在数据库表中多的一方，添加字段，来关联一的一方的主键。

顺着这句话，就有了”父表 / 子表”的叫法：

PPT 第 10 页把这句话变成一个问答：

本机实测（MySQL 9.0.1，库 tlias）里这 30 条员工数据在部门上的分布是这样的——它同时也是后面 65 篇做多表查询时的”标准答案表”：

第一步：看页面原型，找出”有几件事”#

员工模块的页面原型里其实藏了两件事：

• “员工管理”列表上的那些列（姓名、性别、头像、所属部门、职位、入职日期、最后操作时间）——这是员工本人的信息；
• “新增员工”表单里的”工作经历”区块——这是员工工作过的地方，一个员工可以填多段经历（开始时间、结束时间、公司名称、职位）。

一件事一张表，于是员工模块对应两张表（关系是”员工 → 工作经历”的一对多）：

1
dept(1) ──────> emp(n)          emp(1) ──────> emp_expr(n)
2
一个部门有多个员工               一个员工有多段工作经历
3
（emp 加 dept_id）              （emp_expr 加 emp_id）

第二步：逐个字段定下来，写成建表语句#

员工表 emp（“多”的一方：dept_id 指向 dept 的主键）：

1
create table emp(
2
    id int unsigned primary key auto_increment comment 'ID,主键',
3
    username varchar(20) not null unique comment '用户名',
4
    password varchar(32) default '123456' comment '密码',
5
    name varchar(10) not null comment '姓名',
6
    gender tinyint unsigned not null comment '性别, 1:男, 2:女',
7
    phone char(11) not null unique comment '手机号',
8
    job tinyint unsigned comment '职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师',
9
    salary int unsigned comment '薪资',
10
    image varchar(255) comment '头像',
11
    entry_date date comment '入职日期',
12
    dept_id int unsigned comment '部门ID',          -- 关联部门（逻辑外键）
13
    create_time datetime comment '创建时间',
14
    update_time datetime comment '修改时间'
15
) comment '员工表';

工作经历表 emp_expr（“多”的一方：emp_id 指向 emp 的主键）：

1
create table emp_expr(
2
    id int unsigned primary key auto_increment comment 'ID, 主键',
3
    emp_id int unsigned comment '员工ID',           -- 关联员工（逻辑外键）
4
    begin date comment '开始时间',
5
    end  date comment '结束时间',
6
    company varchar(50) comment '公司名称',
7
    job varchar(50) comment '职位'
8
) comment '工作经历';

字段从哪来，一张表梳理清楚：

本机实测（MySQL 9.0.1，库 tlias）这两张表的状态：

• emp：30 条员工数据（按 dept_id 分布见前面那张表）；
• emp_expr：0 条——工作经历是”新增员工”功能才会写入的，员工模块的新增功能这一章才刚开始做，所以这张表还是空的；
• emp 表带着课程脚本加的那个物理外键 fk_emp_dept_id（所以前面才能复现出 1451/1452 两条报错）；而 emp_expr 与 emp 之间没有加物理外键——按 PPT 第 13 页的建议，实际项目里这种关联交给业务层用逻辑外键去保证。

一、知识回顾（读完直接做下面的实践题）#

1. 多表关系的三种类型：一对多（多对一）、一对一、多对多；关系来源于业务之间的相互关联，表结构设计要跟着业务走
2. 一对多的落地方式：在多的一方添加字段，关联一的一方的主键——dept（一 / 父表）→ emp（多 / 子表），靠 emp.dept_id 关联
3. 一对多的字段可以为空：本机 30 条员工数据里就有 1 条 dept_id 为 NULL（没有分配部门），另外 dept 表里的就业部、人事部、行政部下面一个员工都没有
4. 多表问题（PPT 第 11 页）：两张表在数据库层面没建立关联 → 部门能被直接删掉而员工还挂在下面 → 数据不完整、不一致；解决方案是外键约束
5. 加外键的两种语法：建表时在字段列表末尾写 [constraint] [外键名称] foreign key (外键字段名) references 主表 (字段名)；建表后用 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名);（课程脚本给 emp 加的就是这一句，外键名 fk_emp_dept_id）
6. 两条实测报错原文：删有员工的部门 → ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails；插入 dept_id = 999 的员工 → ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails；删”没有员工的部门”则成功
7. 外键约束的作用：多表操作中保证数据的一致性、完整性和正确性；物理外键的三个缺点是影响增删改效率、仅适用于单节点（不适用分布式/集群）、容易引发死锁消耗性能 → 推荐逻辑外键（在业务层用代码保证关联）
8. 一对一的落地方式：在任意一方加外键关联另一方主键，并给这个外键加 UNIQUE（一对一本是一种特殊的一对多）；常见用途是单表拆分（tb_user + tb_user_card）
9. 多对多的落地方式：建第三张中间表，中间表里至少两个外键分别关联两方主键（tb_student_course 的 student_id → tb_student.id、course_id → tb_course.id），中间表一行表示”某学生选了某门课”
10. 员工模块的两张表：emp（员工，含 dept_id）与 emp_expr（工作经历，含 emp_id），关系是 dept(1)→emp(n) 和 emp(1)→emp_expr(n)；本机实测 emp 30 条、emp_expr 0 条

二、裸写题#

• 2-1 给”班级—学生”设计一对多的两张表 需求里说：一个班级里有很多学生，每个学生只属于一个班级。请设计这两张表并让它们在数据库层面建立关联：

  1. 写出两张表的建表语句（班级表 tb_class：主键 id、班级名称 name、创建时间 create_time；学生表 tb_student2：主键 id、姓名 name、学号 no、创建时间 create_time）；
  2. 学生表里要有”属于哪个班级”的字段（起名 class_id），并用建表后添加的方式给它加上外键约束，外键名取 fk_student_class。
  提示（先自己想，实在想不出再点开）

  一级 · 思路：判断”哪张是多的一方”——一个班级对多个学生，所以学生表是多的一方，关联字段要加在学生表上，指向班级表的主键；外键约束用”建完表后添加”的写法 二级 · 方法：建表 create table 表名(字段 类型 约束, ...)；加外键 alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(字段名); 三级 · 骨架：create table tb_class(id int unsigned primary key auto_increment, name varchar(20) not null unique, create_time datetime); → create table tb_student2(id int unsigned primary key auto_increment, name varchar(10) not null, no varchar(10) not null unique, class_id int unsigned, create_time datetime); → alter table tb_student2 ____ constraint fk_student_class ____ key (____) references ____ (____);

  参考答案（做完再点开）

  1
  -- 班级表（"一"的一方 / 父表）
  2
  create table tb_class(
  3
      id int unsigned primary key auto_increment comment '主键ID',
  4
      name varchar(20) not null unique comment '班级名称',
  5
      create_time datetime comment '创建时间'
  6
  ) comment '班级表';
  7
  
  8
  -- 学生表（"多"的一方 / 子表）：多的一方加字段
  9
  create table tb_student2(
  10
      id int unsigned primary key auto_increment comment '主键ID',
  11
      name varchar(10) not null comment '姓名',
  12
      no varchar(10) not null unique comment '学号',
  13
      class_id int unsigned comment '所属班级ID',
  14
      create_time datetime comment '创建时间'
  15
  ) comment '学生表';
  16
  
  17
  -- 建完表后，添加外键约束
  18
  alter table tb_student2 add constraint fk_student_class foreign key (class_id) references tb_class(id);

  显示更多显示更少

  检查点：class_id 加在学生表（多的一方）上；外键名写在 constraint 后面；references 后面跟的是父表名 + 它的主键字段。加完约束后可以复现一下规则——delete from tb_class where id = 1; 如果下面还有学生，就会报 1451。

• 2-2 给”用户—身份证”设计一对一两张表 需求：一个用户最多只有一份身份信息，一份身份信息也只属于一个用户。请在 tb_user（id、name、phone）之外设计一张 tb_user_card（id、idcard 身份证号、user_id 用户ID），并用建表时指定的方式让它和用户表建立一对一关系。

  提示（先自己想，实在想不出再点开）

  一级 · 思路：一对一的关键有两步——先选一方放外键（任意一方都行，这里放在身份证表上），再给这个外键加上唯一约束；少了唯一约束就退化成了”一个用户能有多张身份证”的一对多 二级 · 方法：唯一约束 unique；外键写在字段列表里 constraint 外键名 foreign key (外键字段名) references 主表(字段名) 三级 · 骨架：create table tb_user_card(id int unsigned primary key auto_increment, idcard char(18) not null, user_id int unsigned not null ____, constraint fk_card_user ____ key (____) references ____(____));

  参考答案（做完再点开）

  1
  create table tb_user(
  2
      id int unsigned primary key auto_increment comment 'ID',
  3
      name varchar(10) not null comment '姓名',
  4
      phone char(11) comment '手机号'
  5
  ) comment '用户信息表';
  6
  
  7
  create table tb_user_card(
  8
      id int unsigned primary key auto_increment comment 'ID',
  9
      idcard char(18) not null comment '身份证号',
  10
      user_id int unsigned not null unique comment '用户ID',   -- 外键 + 唯一约束
  11
      constraint fk_card_user foreign key (user_id) references tb_user(id)
  12
  ) comment '用户身份信息表';

  验证一下唯一约束真的生效：先插一条 user_id = 1 的身份信息，再插第二条 user_id = 1 的记录，数据库会报主键/唯一键冲突（ERROR 1062 (23000): Duplicate entry '1' for key '...'），插不进去——这就是”一对一”。课程脚本里的 tb_user_card 用的就是这套写法，只是外键名叫 fk_user_id。

• 2-3 给”学生—课程”设计多对多的三张表 需求：一个学生可以选多门课，一门课可以被多个学生选。请设计 tb_student（id、name、no）、tb_course（id、name）和它们之间的中间表 tb_student_course，把两张表关联起来。

  提示（先自己想，实在想不出再点开）

  一级 · 思路：两张主表里都放不下对方的信息，所以要请出第三张中间表；中间表不需要业务字段，只需要两个外键，分别指向两张主表的主键 二级 · 方法：中间表两个字段都加 not null，各写一条 constraint 外键名 foreign key (字段) references 主表(id) 三级 · 骨架：create table tb_student_course(id int primary key auto_increment, student_id int not null, course_id int not null, constraint fk_studentid foreign key (student_id) references ____(____), constraint fk_courseid foreign key (____) references ____(____));

  参考答案（做完再点开）

  1
  create table tb_student(
  2
      id int auto_increment primary key comment '主键ID',
  3
      name varchar(10) comment '姓名',
  4
      no varchar(10) comment '学号'
  5
  ) comment '学生表';
  6
  
  7
  create table tb_course(
  8
      id int auto_increment primary key comment '主键ID',
  9
      name varchar(10) comment '课程名称'
  10
  ) comment '课程表';
  11
  
  12
  -- 中间表：只有两个外键，一行 = 一个学生选了一门课
  13
  create table tb_student_course(
  14
      id int auto_increment comment '主键' primary key,
  15
      student_id int not null comment '学生ID',
  16
      course_id int not null comment '课程ID',
  17
      constraint fk_studentid foreign key (student_id) references tb_student(id),
  18
      constraint fk_courseid foreign key (course_id) references tb_course(id)
  19
  ) comment '学生课程中间表';

  显示更多显示更少

  插几条选课数据试试：insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);——第 1 个学生选了 3 门、第 2 个选了 2 门、第 3 个选了 1 门。两个外键各有各的作用：student_id 保证”选课的学生存在”，course_id 保证”选的课存在”。

• 2-4 排错：这两条语句为什么跑不了 （1）本机的 emp 表带着外键约束 fk_emp_dept_id，执行 delete from dept where id = 2;（教研部下面有 15 名员工）报错，把报错原文抄下来，并解释这句话的意思。 （2）接着执行 insert into emp(username, password, name, gender, phone, dept_id, create_time, update_time) values ('test999','123456','测试员工',1,'13300000999',999,now(),now()); 又报错，这次是为什么？两条报错分别在”保护”什么？

  提示（先自己想，实在想不出再点开）

  一级 · 思路：两条报错一个发生在删父表、一个发生在插子表，都是外键约束在拦；报错信息里的英文关键词能帮你判断是哪一种——parent row（父表的行）和 child row（子表的行） 二级 · 方法：外键约束的规则是”子表引用的父表记录必须存在，父表被引用的记录不能删”；报错码 1451 对应”删/改父表”，1452 对应”子表引用了不存在的父记录” 三级 · 骨架：第 1 条抓住报错里的两处关键信息——出问题的是 tlias 库的 emp 表、约束名是 fk_emp_dept_id（谁在引用 dept）；第 2 条说清 dept 表里根本没有 id 为 999 的部门

  参考答案（做完再点开）

  （1） 报错原文（本机实测）：

  1
  ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
  2
    (`tlias`.`emp`, CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

  意思是”不能删除或修改父表的行：外键约束失败了”，括号里点名了是 tlias 库的 emp 表、约束名 fk_emp_dept_id（emp.dept_id → dept.id）。因为教研部（id=2）下面还挂着 15 名员工，一旦把这个部门删掉，这 15 行 emp.dept_id = 2 就”指向空气”了——外键在数据库层面把这种会造成数据不一致的操作拦下来了。

  （2） 报错原文（本机实测）：

  1
  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  2
    (`tlias`.`emp`, CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))

  这次是”不能新增或修改子表的行”：要插入的这条员工记录 dept_id = 999，而 dept 表里根本没有 id 为 999 的部门，子表不能引用一个不存在的父记录。

  两条报错合起来才是外键的完整规则：子表不许指向不存在的父记录（1452），父表里被引用的记录不许删（1451）。反过来验证一下就知道它只拦”会被破坏的操作”——如果某个部门下面一个员工都没有，delete 它是能成功的（本机实测：临时建的”临时部”删除成功）。

三、综合题#

• 3-1 照着页面原型，把员工模块的表结构设计出来 这是 PPT 第 22 页的案例，完整走一遍。要求设计员工模块涉及的两张表，并说清它们之间的关系：

  1. 读原型：打开员工模块的两个页面原型（“员工管理”列表、“新增员工”表单），把页面上出现的字段抄成清单，并判断哪些字段属于”员工本人”、哪些字段属于”工作经历”；
  2. 判关系：写出员工模块涉及的表以及表与表的关系（提示：dept 已经在第 7 章建好了，这里只需要新设计两张）；
  3. 定字段：给每张表逐个字段写出”类型 + 约束”，约束要能对上页面原型上的字段限制（用户名、手机号唯一且必填；姓名必填；性别必填；薪资、职位、入职日期、头像可空）；
  4. 写建表语句：把两张表的 create table 写全（字段都要带 comment，两张表都要有 id / create_time / update_time 三个基础字段）；
  5. 连关系：在”多”的一方补上关联字段；如果要在数据库层面建立约束，把对应的 alter table ... add constraint ... foreign key ... 也写出来（课程脚本给 emp 加的是 fk_emp_dept_id；emp_expr 与 emp 之间课程没有加物理外键）；
  6. 答一问：PPT 第 13 页说实际项目推荐逻辑外键，那”物理外键的那两条规则”（不许指向不存在的记录、被引用的记录不许删）由谁来保证？举一个员工模块里的例子。

  涉及知识点

  知识点	在这里的应用
  一对多的实现	第 3-5 步——emp.dept_id、emp_expr.emp_id 都加在”多”的一方
  页面原型到表结构	第 1-3 步——先看页面上有哪些字段，再判断类型与约束
  外键约束语法	第 5 步——alter table ... add constraint ... foreign key ... references ...
  物理外键与逻辑外键	第 6 步——库里有外键时数据库兜底，靠逻辑外键时业务代码兜底

  提示（先自己想，实在想不出再点开）

  一级 · 思路：整题的骨架是”找字段 → 判关系 → 写语句 → 连关系”。第 1 步要抓住关键：新增员工表单下半部分的”工作经历”是一组可以加多条的记录，所以它必须单独成表 二级 · 方法：一对多 = 在多的一方加字段 +（可选）foreign key ... references ...；每个字段的约束从原型上的提示文字里读——“必填”= not null，“不能重复”= unique，“图片上传”= 用 varchar 存 URL 三级 · 骨架：第 3 步按 emp（id/username/password/name/gender/phone/job/salary/image/entry_date/dept_id/create_time/update_time）与 emp_expr（id/emp_id/begin/end/company/job）两张清单逐个定；第 5 步 alter table emp add constraint ____ foreign key (dept_id) references dept(____);

  参考答案（做完再点开）

  1~2. 字段归属与表关系

  页面上看到的	属于哪张表
  用户名、姓名、性别、手机号、职位、薪资、头像、入职日期	emp（员工本人）
  “所属部门”下拉框	选的是 dept 表里的部门 → emp 只存 dept_id
  工作经历（开始时间、结束时间、公司、职位）可以添加多条	emp_expr（一个员工多段经历）
  列表页的”最后操作时间”	emp.update_time

  三张表的关系：dept(1) → emp(n)、emp(1) → emp_expr(n)——两条都是”一对多，在多的一方加字段”。

  3~5. 建表语句

  1
  -- 员工表（dept 表第 7 章已经建好）
  2
  create table emp(
  3
      id int unsigned primary key auto_increment comment 'ID,主键',
  4
      username varchar(20) not null unique comment '用户名',
  5
      password varchar(32) default '123456' comment '密码',
  6
      name varchar(10) not null comment '姓名',
  7
      gender tinyint unsigned not null comment '性别, 1:男, 2:女',
  8
      phone char(11) not null unique comment '手机号',
  9
      job tinyint unsigned comment '职位, 1 班主任, 2 讲师 , 3 学工主管, 4 教研主管, 5 咨询师',
  10
      salary int unsigned comment '薪资',
  11
      image varchar(255) comment '头像',
  12
      entry_date date comment '入职日期',
  13
      dept_id int unsigned comment '部门ID',
  14
      create_time datetime comment '创建时间',
  15
      update_time datetime comment '修改时间'
  16
  ) comment '员工表';
  17
  
  18
  -- 工作经历表
  19
  create table emp_expr(
  20
      id int unsigned primary key auto_increment comment 'ID, 主键',
  21
      emp_id int unsigned comment '员工ID',
  22
      begin date comment '开始时间',
  23
      end date comment '结束时间',
  24
      company varchar(50) comment '公司名称',
  25
      job varchar(50) comment '职位'
  26
  ) comment '工作经历';
  27
  
  28
  -- 在数据库层面建立约束（可选，课程脚本对 emp 做了这一步，外键名 fk_emp_dept_id）
  29
  alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

  显示更多显示更少

  6. 答一问：物理外键撤掉之后，PPT 第 11 页那两条规则（“子表不许指向不存在的父记录""父表里被引用的记录不许删”）依然要遵守，只是执行的人从数据库换成了业务代码——比如”删除部门”接口在删之前要先查一下这个部门下面还有没有员工（60 篇那个接口之后就要补上这一步）；“新增/修改员工”接口要确认传上来的 dept_id 在 dept 表里真实存在。数据库不拦，脏数据就会悄悄写进去，等到页面上出现”所属部门”是空白的一行时才发现。 本机实测的现状：emp 30 条、emp_expr 0 条（工作经历要等”新增员工”功能做完才会有数据），emp 表带着 fk_emp_dept_id 这个物理外键——所以上面”删教研部报 1451”的实验才能做出来。