MySQL 多表查询详解：从外键到连接查询

MySQL 多表查询详解从外键到连接查询在设计关系型数据库时为了减少数据冗余我们通常会将不同维度的数据存储在多张表中。当需要从多张表中联合提取数据时多表查询就成为了核心技能。本文将系统讲解 MySQL 中的外键约束、内连接与外连接以及如何用 UNION 实现全外连接。一、外键约束连接两张表的桥梁外键约束FOREIGN KEY用于建立两张表之间的关联保证数据的引用完整性。语法CREATETABLE子表(...关联字段 类型,CONSTRAINT外键名FOREIGNKEY(关联字段)REFERENCES父表(主键字段));作用保证子表中外键字段的值必须在父表主键中存在或为 NULL防止误删父表中被子表引用的数据默认会阻止删除示例-- 父表部门CREATETABLEdept(idINTPRIMARYKEY,nameVARCHAR(20));-- 子表员工dept_id 作为外键关联到 dept 表的 idCREATETABLEemp(idINTPRIMARYKEY,nameVARCHAR(20),dept_idINT,CONSTRAINTfk_deptFOREIGNKEY(dept_id)REFERENCESdept(id));二、内连接INNER JOIN获取两表的交集内连接返回两张表中满足连接条件的数据即两表的交集部分。如果某行在任意一张表中没有匹配该行就不会出现在结果集中。语法SELECT字段列表FROM表1INNERJOIN表2ON表1.关联字段表2.关联字段;示例-- 查询员工姓名及其所属部门名称只显示有部门的员工SELECTemp.name,dept.nameASdept_nameFROMempINNERJOINdeptONemp.dept_iddept.id;若某员工没有分配部门dept_id 为 NULL或部门已不存在该员工不会出现在查询结果中。三、外连接OUTER JOIN外连接不仅能返回匹配的记录还能保留其中一张表的所有记录不匹配的部分用 NULL 填充。MySQL 支持左外连接和右外连接。1. 左外连接LEFT JOIN返回左表所有记录右表只返回匹配记录匹配不上则置 NULL。SELECT字段列表FROM表1LEFTJOIN表2ON表1.关联字段表2.关联字段;示例-- 查询所有员工及其部门名称包含没部门的员工SELECTemp.name,dept.nameASdept_nameFROMempLEFTJOINdeptONemp.dept_iddept.id;即使员工没有所属部门该员工信息仍会保留对应的部门字段为空。2. 右外连接RIGHT JOIN与左连接相反返回右表所有记录左表只返回匹配记录匹配不上则置 NULL。SELECT字段列表FROM表1RIGHTJOIN表2ON表1.关联字段表2.关联字段;示例-- 查询所有部门及部门下的员工姓名包含没员工的部门SELECTemp.name,dept.nameASdept_nameFROMempRIGHTJOINdeptONemp.dept_iddept.id;即使某个部门下没有员工部门信息也会显示员工字段为空。实际开发中右连接完全可以通过交换左右表位置转换为左连接因此左连接使用频率更高。四、全外连接与 UNION全外连接FULL JOIN返回左右两张表的并集MySQL 原生并不直接支持 FULL JOIN 语法但可以通过UNION组合左连接和右连接来实现。UNION 与 UNION ALL 的区别UNION合并两个查询结果自动去掉重复行UNION ALL直接合并保留所有行包括重复使用条件两个查询结果的列数必须相同且对应列的数据类型兼容。全外连接实现方式-- 左连接获取左表全量 右连接获取左表未匹配到的部分SELECTemp.name,dept.nameASdept_nameFROMempLEFTJOINdeptONemp.dept_iddept.idUNIONSELECTemp.name,dept.nameFROMempRIGHTJOINdeptONemp.dept_iddept.id;这样得到的结果集既包含所有员工包括无部门的也包含所有部门包括无员工的实现了两张表的并集。如果不希望去掉重复行可将UNION改为UNION ALL。五、连接查询总结对比表连接类型返回结果描述关键字内连接两表的交集INNER JOIN左外连接左表全部 右表匹配无匹配为 NULLLEFT JOIN右外连接右表全部 左表匹配无匹配为 NULLRIGHT JOIN全外连接两表并集MySQL 用 UNION 实现(LEFT JOIN) UNION (RIGHT JOIN)小结多表查询是数据库操作中最常用的高级特性之一。理解外键约束的关联意义掌握内连接与外连接的区别以及使用 UNION 实现全外连接能够帮助我们灵活地从多张表中提取业务所需的数据。建议结合实例多练习并留意查询结果中 NULL 值的处理逻辑。