MySQL 多表与连接完全指南

本文档专门讲 多张表如何一起查：为什么分多张表、表与表之间有什么关系、怎样用 JOIN（连接） 把多表数据拼在一起。每一步都配有详细说明和大量示例，适合零基础新手跟着做。

---

目录

1. 为什么会有多张表
2. 表与表之间的关系
3. 连接（JOIN）是什么
4. 示例表与数据准备
5. 内连接（INNER JOIN）
6. 左连接（LEFT JOIN）
7. 右连接（RIGHT JOIN）
8. 多表连接（三张表及以上）
9. 自连接（同一张表连接）
10. 交叉连接（CROSS JOIN）
11. 连接 + 条件 + 排序 + 聚合
12. 连接方式如何选择
13. 常见错误与注意点
14. 综合示例与速查表

---

1. 为什么会有多张表

1.1 避免重复、便于维护

若把所有信息塞在一张表里，会有大量重复，例如：

• 每个学生一行，都写一遍“班级名、班主任、教室”，同一个班要重复很多次。
• 班级信息一旦改动（如换班主任），要改很多行，容易漏改、出错。

所以通常会把“学生”和“班级”拆成两张表：

• 学生表：学号、姓名、班级编号……
• 班级表：班级编号、班级名、班主任、教室……

班级信息只存一份，学生表只存“班级编号”，通过编号和班级表关联。这就是多表设计。

1.2 用“编号”建立关联

• 学生表里存 班级编号（class_id），班级表里存 班级编号（id）。
• 查“学生 + 班级名”时，需要把两张表按 class_id 和 id 对应起来再查，这就是连接（JOIN）。

多表 + 连接，既能减少重复，又能一次查出“跨表”的结果。

---

2. 表与表之间的关系

2.1 一对一（1:1）

A 表一行 只 对应 B 表一行，B 表一行 只 对应 A 表一行。

例子：用户表 ↔ 用户详情表（一个用户一份详情）。
实现：在任意一方存对方的主键（如详情表存 user_id）。

2.2 一对多（1:N）

A 表一行 对应 B 表多行，B 表一行 只 对应 A 表一行。

例子：班级 ↔ 学生（一个班多个学生，一个学生属于一个班）。
实现：在“多”的那张表（学生表）里存“一”的主键（如 class_id 对应班级 id）。

2.3 多对多（N:M）

A 表一行 对应 B 表多行，B 表一行 也 对应 A 表多行。

例子：学生 ↔ 课程（一个学生选多门课，一门课被多个学生选）。
实现：需要中间表（选课表），存“学生 id + 课程 id”，分别与学生表、课程表是一对多。

2.4 用“外键”表示关系（概念）

• 外键列：存的是“别人表的主键”，如学生表的 class_id 存班级表的 id。
• 连接时就用：学生.class_id = 班级.id，把两表“拼”起来。

下面用“班级—学生—成绩”等示例，具体演示怎么用 JOIN 查。

---

3. 连接（JOIN）是什么

3.1 通俗理解

连接就是把两张表（或多张表）按某种条件拼成一张“大表”：

• 条件一般是：A 表的某列 = B 表的某列（如 学生.class_id = 班级.id）。
• 拼好后，一条结果行里会同时有“学生”的列和“班级”的列，方便一次查出“学生姓名 + 班级名”等。

3.2 连接类型简述

类型	关键字	通俗理解
内连接	INNER JOIN	只保留两表都“匹配上”的行
左连接	LEFT JOIN	以左表为准，右表没有匹配则右表列补 NULL
右连接	RIGHT JOIN	以右表为准，左表没有匹配则左表列补 NULL
交叉连接	CROSS JOIN	两表所有行两两组合（少用）

ON 后面写“怎么对应”：通常是 左表.列 = 右表.列。

---

4. 示例表与数据准备

后面所有示例都基于下面三张表，建议在库里建好并插入数据，跟着练。

4.1 班级表 classes

CREATE TABLE classes (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    name     VARCHAR(20) NOT NULL COMMENT '班级名',
    teacher  VARCHAR(20) COMMENT '班主任'
);

INSERT INTO classes (name, teacher) VALUES
('高一1班', '王老师'),
('高一2班', '李老师');

4.2 学生表 students（含 class_id 关联班级）

CREATE TABLE students (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(50) NOT NULL,
    gender    CHAR(1) DEFAULT '男',
    age       INT,
    class_id  INT COMMENT '所在班级 id',
    CONSTRAINT fk_stu_class FOREIGN KEY (class_id) REFERENCES classes(id)
);

INSERT INTO students (name, gender, age, class_id) VALUES
('张三', '男', 18, 1),
('李四', '女', 17, 2),
('王五', '男', 18, 1),
('赵六', '女', 17, 2),
('钱七', '男', 19, 1),
('孙八', '女', 18, NULL);   -- 未分班，class_id 为 NULL

4.3 成绩表 scores（含 student_id 关联学生）

CREATE TABLE scores (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL COMMENT '学生 id',
    subject    VARCHAR(20) NOT NULL COMMENT '科目',
    score      DECIMAL(5,2) COMMENT '分数',
    CONSTRAINT fk_score_stu FOREIGN KEY (student_id) REFERENCES students(id)
);

INSERT INTO scores (student_id, subject, score) VALUES
(1, '语文', 85),
(1, '数学', 92),
(2, '语文', 78),
(2, '数学', 88),
(3, '语文', 90),
(3, '数学', 76),
(4, '语文', 82),
(5, '数学', 95);

关系小结：

• classes 与 students：一对多（一个班多个学生），通过 students.class_id = classes.id 关联。
• students 与 scores：一对多（一个学生多条成绩），通过 scores.student_id = students.id 关联。

---

5. 内连接（INNER JOIN）

5.1 含义

只保留“左表某行”和“右表某行”在 ON 条件上能对上的结果；两表都没匹配上的行不会出现。

5.2 基本语法

SELECT 列...
FROM 表A
INNER JOIN 表B ON 表A.某列 = 表B.某列
[WHERE ...] [ORDER BY ...];

INNER 可以省略，只写 JOIN 默认就是内连接：

FROM 表A JOIN 表B ON 表A.某列 = 表B.某列

5.3 示例：查每个学生的姓名和所在班级名

学生表有姓名和 class_id，班级表有班级名和 id，用 students.class_id = classes.id 连接：

SELECT s.name AS 学生姓名, c.name AS 班级名
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

这里 s、c 是表别名，方便写 s.name、c.name，避免列名歧义。

示例结果（示意）：

+----------+----------+
| 学生姓名 | 班级名   |
+----------+----------+
| 张三     | 高一1班  |
| 李四     | 高一2班  |
| 王五     | 高一1班  |
| 赵六     | 高一2班  |
| 钱七     | 高一1班  |
+----------+----------+

孙八的 class_id 是 NULL，和 classes 任何一行都对不上，所以不会出现在内连接结果里。

5.4 示例：查学生 + 班级 + 成绩（三列信息）

要“学生姓名、班级名、科目、分数”，需要：

• 学生 ↔ 班级：students JOIN classes ON students.class_id = classes.id
• 学生 ↔ 成绩：students JOIN scores ON students.id = scores.student_id

可以先把“学生+班级”当成一张逻辑表，再和成绩表连接（多表连接下一节会统一写）。这里先用两表：学生 + 成绩：

SELECT s.name AS 学生姓名, sc.subject AS 科目, sc.score AS 分数
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

示例结果（示意）：

+----------+--------+-------+
| 学生姓名 | 科目   | 分数  |
+----------+--------+-------+
| 张三     | 语文   | 85.00 |
| 张三     | 数学   | 92.00 |
| 李四     | 语文   | 78.00 |
| 李四     | 数学   | 88.00 |
| 王五     | 语文   | 90.00 |
| 王五     | 数学   | 76.00 |
| 赵六     | 语文   | 82.00 |
| 钱七     | 数学   | 95.00 |
+----------+--------+-------+

一个学生多门课会多行，这是正常的。

5.5 内连接小结

• 只保留 两表都能匹配上 的行。
• ON 写清楚“哪一列和哪一列相等”；多表时每个 JOIN 一个 ON。
• 用表别名（如 s、c、sc）写列名，清晰且避免重名报错。

---

6. 左连接（LEFT JOIN）

6.1 含义

以左表为基准：左表每一行都会出现；右表能匹配上的就拼上，匹配不上的，右表的列用 NULL 补。

6.2 基本语法

SELECT 列...
FROM 表A
LEFT JOIN 表B ON 表A.某列 = 表B.某列
[WHERE ...] [ORDER BY ...];

LEFT OUTER JOIN 和 LEFT JOIN 等价，一般写 LEFT JOIN 即可。

6.3 示例：查所有学生，有班级的显示班级名，没班级的也显示学生

“所有学生” = 以学生表为左表；班级能对上就显示班级名，对不上（如孙八）就班级列为 NULL：

SELECT s.name AS 学生姓名, s.class_id, c.name AS 班级名
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

示例结果（示意）：

+----------+----------+----------+
| 学生姓名 | class_id | 班级名   |
+----------+----------+----------+
| 张三     | 1        | 高一1班  |
| 李四     | 2        | 高一2班  |
| 王五     | 1        | 高一1班  |
| 赵六     | 2        | 高一2班  |
| 钱七     | 1        | 高一1班  |
| 孙八     | NULL     | NULL     |
+----------+----------+----------+

孙八 class_id 为 NULL，和 classes 对不上，班级名就是 NULL，但学生行还在。

6.4 示例：找出“没有班级”的学生（左连接 + WHERE 右表列为 NULL）

左连接后，右表没匹配上的行，右表列会是 NULL，所以用 WHERE 右表.主键 IS NULL 可以筛出“在右表没有对应”的左表行：

SELECT s.id, s.name
FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE c.id IS NULL;

结果就是“没分班”的学生（如孙八）。

6.5 左连接小结

• 左表全保留，右表能对上就拼，对不上就 NULL。
• 适合“要左表全部，右表有则显示”的场景；也可用来找“在右表没有对应”的左表行（WHERE 右表.列 IS NULL）。

---

7. 右连接（RIGHT JOIN）

7.1 含义

以右表为基准：右表每一行都会出现；左表能匹配上的就拼上，匹配不上的左表列用 NULL 补。

7.2 基本语法

SELECT 列...
FROM 表A
RIGHT JOIN 表B ON 表A.某列 = 表B.某列;

把“谁当基准”反过来就是左连接，所以：
A RIGHT JOIN B ON … 等价于 B LEFT JOIN A ON …。
实际中多数人习惯只写 LEFT JOIN，需要“右表全保留”时把表顺序换一下用 LEFT 即可。

7.3 示例（了解即可）

“所有班级，有学生就显示学生”：

SELECT c.name AS 班级名, s.name AS 学生姓名
FROM students s
RIGHT JOIN classes c ON s.class_id = c.id;

等价于：

SELECT c.name AS 班级名, s.name AS 学生姓名
FROM classes c
LEFT JOIN students s ON s.class_id = c.id;

新手掌握 INNER JOIN 和 LEFT JOIN 即可，RIGHT JOIN 能看懂即可。

---

8. 多表连接（三张表及以上）

8.1 思路

多个 JOIN 逐个写：先 A 和 B 连，再拿结果和 C 连，依此类推。每个 JOIN 都要一个 ON。

8.2 示例：学生 + 班级名 + 成绩（三张表）

要“学生姓名、班级名、科目、分数”：

• 学生 ↔ 班级：ON s.class_id = c.id
• 学生 ↔ 成绩：ON s.id = sc.student_id

SELECT s.name AS 学生姓名, c.name AS 班级名, sc.subject AS 科目, sc.score AS 分数
FROM students s
INNER JOIN classes c ON s.class_id = c.id
INNER JOIN scores sc ON s.id = sc.student_id;

示例结果（示意）：

+----------+----------+--------+-------+
| 学生姓名 | 班级名   | 科目   | 分数  |
+----------+----------+--------+-------+
| 张三     | 高一1班  | 语文   | 85.00 |
| 张三     | 高一1班  | 数学   | 92.00 |
| 李四     | 高一2班  | 语文   | 78.00 |
...

8.3 示例：三表 + 条件 + 排序

查“高一1班”的学生的成绩，按分数降序：

SELECT s.name AS 学生姓名, c.name AS 班级名, sc.subject AS 科目, sc.score AS 分数
FROM students s
INNER JOIN classes c ON s.class_id = c.id
INNER JOIN scores sc ON s.id = sc.student_id
WHERE c.name = '高一1班'
ORDER BY sc.score DESC;

8.4 多表连接小结

• 每多连一张表就多写一个 JOIN … ON …。
• 列名来自多张表时，尽量用表别名.列名，避免“列名歧义”报错。

---

9. 自连接（同一张表连接）

9.1 含义

自连接是同一张表当两次用，用别名区分“左表角色”和“右表角色”，常用于：上下级、同表内比较（如“和我同班的学生”）等。

9.2 示例：查“每个学生及其同班同学”（同班且不是自己）

学生表里“同班”即 class_id 相同。把 students 连两次，别名 s1、s2，条件：s1.class_id = s2.class_id 且 s1.id != s2.id：

SELECT s1.name AS 学生, s2.name AS 同班同学
FROM students s1
INNER JOIN students s2 ON s1.class_id = s2.class_id AND s1.id != s2.id
ORDER BY s1.name, s2.name;

就会得到“两两同班”的配对（每对会出现两次，如 A-B 和 B-A，若要去重可再加 s1.id < s2.id 等条件）。

9.3 自连接小结

• 同一张表写两次，用不同别名区分。
• ON 里写“同一张表”的两列如何对应（如 id 与 parent_id、class_id 与 class_id 等）。

---

10. 交叉连接（CROSS JOIN）

10.1 含义

CROSS JOIN 不做条件匹配，左表每一行 和 右表每一行 各组合一次，结果行数 = 左表行数 × 右表行数（笛卡尔积）。一般很少用，除非做“全部组合”（如规格 × 颜色）。

10.2 语法与示例

SELECT * FROM students CROSS JOIN classes;

或省略 CROSS，只写 JOIN 且不加 ON（不推荐，容易误写）：

SELECT * FROM students JOIN classes;

结果行数 = students 行数 × classes 行数。新手知道有这种写法即可，日常多用 INNER/LEFT + ON。

---

11. 连接 + 条件 + 排序 + 聚合

11.1 连接 + WHERE

条件写在 WHERE 里，可以和 JOIN 一起用：

SELECT s.name, c.name AS 班级名
FROM students s
INNER JOIN classes c ON s.class_id = c.id
WHERE s.age >= 18;

11.2 连接 + ORDER BY

SELECT s.name, sc.subject, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
ORDER BY s.name, sc.score DESC;

11.3 连接 + GROUP BY 聚合

例如：每个班级的学生人数、每班平均分等：

SELECT c.name AS 班级名, COUNT(s.id) AS 学生数
FROM classes c
LEFT JOIN students s ON s.class_id = c.id
GROUP BY c.id, c.name;

SELECT s.name AS 学生姓名, AVG(sc.score) AS 平均分
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id
GROUP BY s.id, s.name;

子句顺序：FROM … JOIN … [WHERE] [GROUP BY] [ORDER BY] [LIMIT]。

---

12. 连接方式如何选择

需求	用哪种连接
只要两表都匹配上的行	INNER JOIN
要左表全部，右表有则显示	LEFT JOIN
要右表全部，左表有则显示	RIGHT JOIN 或换顺序用 LEFT JOIN
找“左表有、右表没有”的行	LEFT JOIN + WHERE 右表.列 IS NULL
同一张表内比较/配对	自连接（同表 JOIN 两次用别名）
全部两两组合（少用）	CROSS JOIN

---

13. 常见错误与注意点

13.1 忘记写 ON 或 ON 写错

每个 JOIN 都要有 ON，且条件要写对（如 s.class_id = c.id），否则会变成笛卡尔积或结果错。

13.2 列名歧义（多表同名列）

多张表有同名列（如都有 id、name）时，必须用表别名.列名，否则报错 “Column ‘id’ in field list is ambiguous”：

-- 错误
SELECT id, name FROM students s JOIN classes c ON s.class_id = c.id;

-- 正确
SELECT s.id, s.name, c.name AS 班级名 FROM students s JOIN classes c ON s.class_id = c.id;

13.3 把“连接条件”和“过滤条件”混在 ON 里

• ON：只写“两表如何对应”（如 s.class_id = c.id）。
• WHERE：写业务过滤（如 age >= 18、c.name = ‘高一1班’）。
  过滤条件放 WHERE 更清晰；放 ON 在 LEFT JOIN 时会影响“是否算匹配”，容易搞混，建议区分开。

13.4 左连接后误用 WHERE 过滤右表列

例如：LEFT JOIN 后写 WHERE c.name = ‘高一1班’，会把“右表为 NULL”的行全部过滤掉，效果变成“只有高一1班的学生”，和 INNER JOIN 类似。若本意是“先左连接再筛班级”，这样写没问题；若本意是“要所有学生，但只显示高一1班的名字”，逻辑需要再想清楚。

---

14. 综合示例与速查表

14.1 综合示例（可整段执行）

USE school;

-- 1. 学生 + 班级名（内连接）
SELECT s.name AS 学生姓名, c.name AS 班级名
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

-- 2. 所有学生 + 班级名（左连接，没班级的也显示）
SELECT s.name AS 学生姓名, c.name AS 班级名
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

-- 3. 没有分班的学生（左连接 + WHERE c.id IS NULL）
SELECT s.id, s.name FROM students s
LEFT JOIN classes c ON s.class_id = c.id
WHERE c.id IS NULL;

-- 4. 学生 + 班级 + 成绩（三表内连接）
SELECT s.name AS 学生, c.name AS 班级, sc.subject AS 科目, sc.score AS 分数
FROM students s
INNER JOIN classes c ON s.class_id = c.id
INNER JOIN scores sc ON s.id = sc.student_id
ORDER BY s.name, sc.score DESC;

-- 5. 每个班级的学生人数（左连接 + 聚合）
SELECT c.name AS 班级名, COUNT(s.id) AS 学生数
FROM classes c
LEFT JOIN students s ON s.class_id = c.id
GROUP BY c.id, c.name;

14.2 连接速查表

连接类型	语法要点	典型用途
内连接	FROM A INNER JOIN B ON A.列 = B.列	只保留两表都匹配的行
左连接	FROM A LEFT JOIN B ON A.列 = B.列	保留左表全部，右表可 NULL
右连接	FROM A RIGHT JOIN B ON A.列 = B.列	保留右表全部，可改用 LEFT 换表顺序
多表连接	多个 JOIN ... ON ...，每表一个 ON	三张表及以上
自连接	同表写两次，用不同别名	同表内比较、配对
交叉连接	CROSS JOIN（无 ON）	笛卡尔积，少用

书写顺序：FROM 表1 [LEFT/INNER] JOIN 表2 ON 条件 [WHERE] [GROUP BY] [ORDER BY] [LIMIT]。

---

把“多表关系 + 内连接 + 左连接 + 多表连接 + 自连接”过一遍后，你就能在库里按关系把多张表拼起来查。建议用文档里的 classes、students、scores 在本地执行一遍，再自己改 ON、WHERE、ORDER BY 多做几种查询，巩固多表与连接。