MySQL 学习笔记第四期SQL 语言之多表查询本笔记承接第三期进入多表查询的核心内容。涵盖交叉连接、内连接、外连接左/右/全、自连接、联合查询UNION/UNION ALL并深入讲解外键约束与关联设计。以王者荣耀实战案例贯穿帮助理解复杂业务场景下的多表操作。一、多表查询基础1.1 为什么需要多表查询在规范化的数据库中数据被分散存储到多张表中以减少冗余。例如学生信息存于学生表课程信息存于课程表选课记录存于中间表。要查询“学生选了哪些课程”就必须将多张表关联起来。1.2 多表查询的核心连接条件连接JOIN通过两张表中共同的字段通常是外键关联主键将数据组合在一起。缺少连接条件会产生笛卡尔积所有行两两组合通常不是期望的结果。二、连接类型详解2.1 交叉连接CROSS JOIN定义返回两张表的笛卡尔积即左表的每一行与右表的所有行组合。极少直接使用。语法sqlSELECT * FROM table1 CROSS JOIN table2; -- 等价于 SELECT * FROM table1, table2;示例假设bumen有 3 行yuangong有 4 行结果返回 12 行。2.2 内连接INNER JOIN定义只返回两张表中连接条件能匹配上的行交集。这是最常用的连接类型。语法sqlSELECT 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。语法sqlSELECT columns FROM table1 LEFT JOIN table2 ON table1.key table2.key;示例查询所有员工及其部门包括无部门的员工。sqlSELECT e.name AS 员工, b.name AS 部门 FROM yuangong e LEFT JOIN bumen b ON e.bumen_id b.id;2.4 右外连接RIGHT JOIN定义返回右表的全部行左表只返回匹配的行不匹配的左表字段填充 NULL。语法sqlSELECT columns FROM table1 RIGHT JOIN table2 ON table1.key table2.key;示例查询所有部门及其员工包括无员工的部门。sqlSELECT 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模拟。模拟语法sqlSELECT columns FROM table1 LEFT JOIN table2 ON condition UNION SELECT columns FROM table1 RIGHT JOIN table2 ON condition;示例查询所有员工和所有部门的全部组合并集。sqlSELECT 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定义一张表与自身连接必须使用不同的别名来区分。常用于处理层级关系如员工-上级、邀请人-被邀请人。语法sqlSELECT 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不去重效率更高推荐语法sqlSELECT ... UNION [ALL] SELECT ...;示例将部门名称和员工名称合并成一列。sqlSELECT name AS 名称 FROM bumen UNION SELECT name FROM yuangong;三、外键约束与关联完整性3.1 外键的概念外键FOREIGN KEY是一张表中的字段用来关联另一张表的主键保证数据的完整性。外键约束防止出现“孤儿数据”如没有对应英雄的皮肤。3.2 外键的创建建表时创建sqlCREATE 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) );建表后添加sqlALTER 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 创建数据库和表sqlCREATE 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 多表查询示例内连接查询有对战记录的玩家战绩sqlSELECT 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;左连接查询所有玩家包括无战绩的sqlSELECT 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;右连接查询所有英雄包括无人使用的sqlSELECT 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 玩家自连接查询每个玩家的邀请人sqlSELECT u.wanjia_nicheng AS 玩家昵称, inviter.wanjia_nicheng AS 邀请人昵称 FROM wanjia u LEFT JOIN wanjia inviter ON u.yaoqingren_id inviter.wanjia_id;联合查询高低胜率英雄合并sqlSELECT 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 值外连接会产生 NULLWHERE条件中要正确处理如IS NULL。使用表别名简化 SQL提高可读性。六、本期知识点归纳一览表连接类型关键字特点典型场景交叉连接CROSS JOIN笛卡尔积极少用测试或生成组合数据内连接INNER JOIN只返回匹配行查询有对应关系的数据左外连接LEFT JOIN左表全保留查询左表全部 右表匹配部分右外连接RIGHT JOIN右表全保留查询右表全部 左表匹配部分全外连接LEFTUNIONRIGHT左右表全保留需要完整并集时自连接JOIN同一张表必须用别名层级关系邀请人、上级联合查询UNION/UNION ALL上下合并结果集合并多个相似查询外键约束FOREIGN KEY保证数据完整性防止孤儿数据级联删除/置空下一期预告用户管理与权限控制创建/删除用户、密码管理、授权与回收、远程连接配置。