P37 37. 基础-多表查询-多表关系介绍

一句话理解

表之间的关系就三种：一对一、一对多、多对多。判断方法很实在——看外键放在哪张表、要不要第三张中间表。

按这 4 步做

1. 先数“谁多”。一个部门有多名员工，一名员工只属于一个部门，所以 employee 是“多”的一方，外键 dept_id 放这里。为什么：外键放多的一方，每条员工记录只能填一个部门号，重复是天然的。
2. 一对一用于拆表。比如员工表和员工档案表，两边都只对应一条；外键放任意一方，但要加 UNIQUE，否则会退化成一堆一对多。
3. 多对多必须建中间表。学生选课，一个学生选多门课，一门课被多人选；中间表放 student_id、course_id 两个外键，联合起来当主键，重复选课才插不进去。
4. 关系定完再写查询。一对多靠 dept_id = id 连两张表；多对多要连三张表；一对一多一次 join。连接条件不是猜的，是外键指出来的。

小例

先准备本集练习数据：p37.sql · 练习库说明

-- 本页练习用的建表段：在 mysqlnotes_p37 里执行一次（后面各页用各自的 pNN.sql，不复用这里）
-- department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2

CREATE TABLE department (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);
CREATE TABLE employee (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age INT,
  dept_id INT,
  salary INT,
  manager_id INT
);
INSERT INTO department VALUES (1,'研发部'),(2,'测试部'),(3,'运营部');
INSERT INTO employee VALUES
 (1,'小林',22,1,8000,2),
 (2,'小周',25,1,10000,NULL),
 (3,'小陈',30,2,12000,2),
 (4,'小李',21,NULL,NULL,2);

-- 一对多：外键放在“多”的一方
ALTER TABLE employee
  ADD CONSTRAINT fk_emp_dept
  FOREIGN KEY (dept_id) REFERENCES department(id);

前面几段只建表灌数据；这两张表属于本页练习库 mysqlnotes_p37，后面各页用的是各自的 pNN.sql，列数和数据类型可能不同，别拿这里的 6 列结构去套别的页。最后一段给 employee.dept_id 加外键：以后 dept_id 只能填 1、2、3 或 NULL，写 99 会被拒绝。自检跑 SELECT id,name,dept_id FROM employee ORDER BY id，应得 4 行，小李的 dept_id 是 NULL——外键允许空值，不代表他有部门。

易错点

• 外键方向写反（在 department 里放 employee_id），一个部门只能记一个员工，一对多就装不下了。
• 多对多只在一边加外键，另一边的对应关系无处存放，迟早出现重复行。
• 外键列类型必须和被引用主键一致（INT 对 INT），类型不同建约束会失败。

30 秒速记

自测（2 题，先答再展开）

材料与来源