MySQL 学习笔记（第四期）：SQL 语言之多表查询
本笔记承接第三期，进入多表查询的核心内容。涵盖：交叉连接、内连接、外连接（左/右/全）、自连接、联合查询（UNION/UNION ALL），并深入讲解外键约束与关联设计。以王者荣耀实战案例贯穿，帮助理解复杂业务场景下的多表操作。
一、多表查询基础
1.1 为什么需要多表查询？
在规范化的数据库中，数据被分散存储到多张表中以减少冗余。例如：学生信息存于学生表，课程信息存于课程表，选课记录存于中间表。要查询“学生选了哪些课程”，就必须将多张表关联起来。
1.2 多表查询的核心：连接条件
连接（JOIN）通过两张表中共同的字段（通常是外键关联主键）将数据组合在一起。缺少连接条件会产生笛卡尔积（所有行两两组合），通常不是期望的结果。
二、连接类型详解
2.1 交叉连接（CROSS JOIN）
定义：返回两张表的笛卡尔积，即左表的每一行与右表的所有行组合。极少直接使用。
语法：
sql
SELECT * FROM table1 CROSS JOIN table2; -- 等价于 SELECT * FROM table1, table2;示例：假设bumen有 3 行，yuangong有 4 行，结果返回 12 行。
2.2 内连接（INNER JOIN）
定义：只返回两张表中连接条件能匹配上的行（交集）。这是最常用的连接类型。
语法：
sql
SELECT columns FROM table1 INNER JOIN table2 ON table1.key = table2.key;示例：查询员工及其所属部门（只显示有部门的员工）。
sql
-- 员工表 yuangong (id, name, bumen_id) -- 部门表 bumen (id, name) SELECT e.name AS 员工, b.name AS 部门 FROM yuangong e INNER JOIN bumen b ON e.bumen_id = b.id;注意：
INNER JOIN可简写为JOIN。
2.3 左外连接（LEFT JOIN）
定义：返回左表的全部行，右表只返回匹配的行，不匹配的右表字段填充 NULL。
语法：
sql
SELECT columns FROM table1 LEFT JOIN table2 ON table1.key = table2.key;示例：查询所有员工及其部门（包括无部门的员工）。
sql
SELECT e.name AS 员工, b.name AS 部门 FROM yuangong e LEFT JOIN bumen b ON e.bumen_id = b.id;2.4 右外连接（RIGHT JOIN）
定义：返回右表的全部行，左表只返回匹配的行，不匹配的左表字段填充 NULL。
语法：
sql
SELECT columns FROM table1 RIGHT JOIN table2 ON table1.key = table2.key;示例：查询所有部门及其员工（包括无员工的部门）。
sql
SELECT e.name AS 员工, b.name AS 部门 FROM yuangong e RIGHT JOIN bumen b ON e.bumen_id = b.id;注意：左连接和右连接可以互相转换，通常习惯使用左连接。
2.5 全外连接（FULL JOIN）
定义：返回左表和右表的所有行，匹配不上的填充 NULL。MySQL 不直接支持FULL JOIN，但可以通过LEFT JOIN UNION RIGHT JOIN模拟。
模拟语法：
sql
SELECT columns FROM table1 LEFT JOIN table2 ON condition UNION SELECT columns FROM table1 RIGHT JOIN table2 ON condition;示例：查询所有员工和所有部门的全部组合（并集）。
sql
SELECT e.name, b.name FROM yuangong e LEFT JOIN bumen b ON e.bumen_id = b.id UNION SELECT e.name, b.name FROM yuangong e RIGHT JOIN bumen b ON e.bumen_id = b.id;2.6 自连接（SELF JOIN）
定义：一张表与自身连接，必须使用不同的别名来区分。常用于处理层级关系（如员工-上级、邀请人-被邀请人）。
语法：
sql
SELECT alias1.col, alias2.col FROM table alias1 JOIN table alias2 ON alias1.ref_id = alias2.id;示例：查询每个员工的上级姓名。
sql
-- 假设员工表有 leader_id 字段 SELECT e.name AS 员工, l.name AS 上级 FROM yuangong e LEFT JOIN yuangong l ON e.leader_id = l.id;2.7 联合查询（UNION / UNION ALL）
定义：将多个 SELECT 查询的结果上下拼接成一个结果集。要求各查询的列数和数据类型一致。
UNION：自动去重（效率稍低）UNION ALL：不去重（效率更高，推荐）
语法：
sql
SELECT ... UNION [ALL] SELECT ...;示例：将部门名称和员工名称合并成一列。
sql
SELECT name AS 名称 FROM bumen UNION SELECT name FROM yuangong;三、外键约束与关联完整性
3.1 外键的概念
外键（FOREIGN KEY）是一张表中的字段，用来关联另一张表的主键，保证数据的完整性。外键约束防止出现“孤儿数据”（如没有对应英雄的皮肤）。
3.2 外键的创建
建表时创建：
sql
CREATE TABLE pifu ( pifu_id INT PRIMARY KEY AUTO_INCREMENT, yingxiong_id INT NOT NULL, pifu_ming VARCHAR(50), FOREIGN KEY (yingxiong_id) REFERENCES yingxiong(yingxiong_id) );建表后添加：
sql
ALTER TABLE zhanji ADD FOREIGN KEY (wanjia_id) REFERENCES wanjia(wanjia_id);3.3 外键的约束模式
| 模式 | 含义 |
|---|---|
ON DELETE CASCADE | 删除主表记录时，自动删除从表中关联的记录 |
ON DELETE SET NULL | 删除主表记录时，将从表的外键字段设为 NULL（该字段必须允许 NULL） |
示例：
sql
-- 级联删除 CREATE TABLE pifu_jilian ( ..., FOREIGN KEY (yingxiong_id) REFERENCES yingxiong_jilian(yingxiong_id) ON DELETE CASCADE ); -- 置空 CREATE TABLE wanjia_zhikong ( ..., FOREIGN KEY (yaoqingren_id) REFERENCES wanjia_zhikong(wanjia_id) ON DELETE SET NULL );3.4 外键的注意事项
- 外键字段的数据类型必须与关联的主键字段完全一致。
- 关联的表必须使用 InnoDB 存储引擎（MyISAM 不支持外键）。
- 外键字段可以允许 NULL，但若设为 NOT NULL，必须填写存在的值。
- 大数据量、高并发场景不建议使用外键（影响性能），可用程序逻辑控制数据一致性。
四、王者荣耀实战案例
4.1 创建数据库和表
sql
CREATE DATABASE IF NOT EXISTS wzry_test DEFAULT CHARACTER SET utf8mb4; USE wzry_test; -- 英雄表 CREATE TABLE yingxiong ( yingxiong_id INT PRIMARY KEY AUTO_INCREMENT, yingxiong_ming VARCHAR(50) NOT NULL COMMENT '英雄名', zhiye VARCHAR(20) NOT NULL COMMENT '职业', fenlu VARCHAR(20) NOT NULL COMMENT '分路' ); -- 玩家表 CREATE TABLE wanjia ( wanjia_id INT PRIMARY KEY AUTO_INCREMENT, wanjia_nicheng VARCHAR(50) NOT NULL COMMENT '玩家昵称', duanwei VARCHAR(20) NOT NULL COMMENT '段位', yaoqingren_id INT COMMENT '邀请人ID' ); -- 皮肤表 CREATE TABLE pifu ( pifu_id INT PRIMARY KEY AUTO_INCREMENT, yingxiong_id INT NOT NULL, pifu_ming VARCHAR(50) NOT NULL, jiage INT NOT NULL, FOREIGN KEY (yingxiong_id) REFERENCES yingxiong(yingxiong_id) ); -- 战绩表（中间表） CREATE TABLE zhanji ( zhanji_id INT PRIMARY KEY AUTO_INCREMENT, wanjia_id INT NOT NULL, yingxiong_id INT NOT NULL, shenglv DECIMAL(4,1) NOT NULL COMMENT '胜率', zongchangci INT NOT NULL COMMENT '总场次', mvp_cishu INT NOT NULL COMMENT 'MVP次数', FOREIGN KEY (wanjia_id) REFERENCES wanjia(wanjia_id), FOREIGN KEY (yingxiong_id) REFERENCES yingxiong(yingxiong_id) );4.2 插入测试数据
sql
-- 英雄 INSERT INTO yingxiong (yingxiong_ming, zhiye, fenlu) VALUES ('韩信', '刺客', '打野'), ('李白', '刺客', '打野'), ('妲己', '法师', '中路'), ('鲁班七号', '射手', '下路'), ('蔡文姬', '辅助', '游走'), ('澜', '刺客', '打野'), ('星', '刺客', '打野'); -- 新英雄，无人使用 -- 玩家 INSERT INTO wanjia (wanjia_nicheng, duanwei, yaoqingren_id) VALUES ('国服韩信', '王者', NULL), ('野王妹妹', '星耀', 1), ('菜鸡互啄', '青铜', 2), ('新玩家小A', '青铜', NULL), ('法王妲己', '王者', 1); -- 皮肤 INSERT INTO pifu (yingxiong_id, pifu_ming, jiage) VALUES (1, '街头霸王', 888), (1, '白龙吟', 1188), (2, '凤求凰', 1788), (3, '魅力维加斯', 0), (4, '电玩小子', 2888); -- 战绩 INSERT INTO zhanji (wanjia_id, yingxiong_id, shenglv, zongchangci, mvp_cishu) VALUES (1, 1, 68.5, 1200, 450), (1, 2, 55.2, 300, 80), (2, 1, 62.3, 500, 180), (2, 6, 58.1, 400, 150), (5, 3, 72.8, 800, 320), (3, 4, 42.5, 200, 20);4.3 多表查询示例
内连接：查询有对战记录的玩家战绩
sql
SELECT wj.wanjia_nicheng AS 玩家昵称, yx.yingxiong_ming AS 使用英雄, zj.shenglv AS 胜率, zj.zongchangci AS 总场次 FROM wanjia wj INNER JOIN zhanji zj ON wj.wanjia_id = zj.wanjia_id INNER JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id;左连接：查询所有玩家（包括无战绩的）
sql
SELECT wj.wanjia_nicheng, yx.yingxiong_ming, zj.shenglv FROM wanjia wj LEFT JOIN zhanji zj ON wj.wanjia_id = zj.wanjia_id LEFT JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id;右连接：查询所有英雄（包括无人使用的）
sql
SELECT wj.wanjia_nicheng, yx.yingxiong_ming, zj.shenglv FROM zhanji zj RIGHT JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id LEFT JOIN wanjia wj ON zj.wanjia_id = wj.wanjia_id; -- 注：这里用 LEFT 保留英雄的 NULL 玩家自连接：查询每个玩家的邀请人
sql
SELECT u.wanjia_nicheng AS 玩家昵称, inviter.wanjia_nicheng AS 邀请人昵称 FROM wanjia u LEFT JOIN wanjia inviter ON u.yaoqingren_id = inviter.wanjia_id;联合查询：高低胜率英雄合并
sql
SELECT yx.yingxiong_ming, '高胜率英雄' AS 类型, zj.shenglv FROM zhanji zj JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id WHERE zj.shenglv > 70 UNION ALL SELECT yx.yingxiong_ming, '低胜率英雄' AS 类型, zj.shenglv FROM zhanji zj JOIN yingxiong yx ON zj.yingxiong_id = yx.yingxiong_id WHERE zj.shenglv < 45;五、多表查询注意事项
- 必须加连接条件：否则产生笛卡尔积，数据量指数级增长。
- 优先使用 UNION ALL：除非必须去重，否则
UNION ALL效率更高。 - 关联字段加索引：
ON条件中的字段应建立索引，避免全表扫描。 - 注意 NULL 值：外连接会产生 NULL，
WHERE条件中要正确处理（如IS NULL）。 - 使用表别名：简化 SQL，提高可读性。
六、本期知识点归纳一览表
| 连接类型 | 关键字 | 特点 | 典型场景 |
|---|---|---|---|
| 交叉连接 | CROSS JOIN | 笛卡尔积，极少用 | 测试或生成组合数据 |
| 内连接 | INNER JOIN | 只返回匹配行 | 查询有对应关系的数据 |
| 左外连接 | LEFT JOIN | 左表全保留 | 查询左表全部 + 右表匹配部分 |
| 右外连接 | RIGHT JOIN | 右表全保留 | 查询右表全部 + 左表匹配部分 |
| 全外连接 | LEFT+UNION+RIGHT | 左右表全保留 | 需要完整并集时 |
| 自连接 | JOIN同一张表 | 必须用别名 | 层级关系（邀请人、上级） |
| 联合查询 | UNION/UNION ALL | 上下合并结果集 | 合并多个相似查询 |
| 外键约束 | FOREIGN KEY | 保证数据完整性 | 防止孤儿数据，级联删除/置空 |
下一期预告：用户管理与权限控制（创建/删除用户、密码管理、授权与回收、远程连接配置）。
