MySQL 多表查询、多表关系建模全解析从基础语法到高阶技巧含子查询、连接、联合查询实战多表关系建模一对一 / 一对多 / 多对多 实战 本文基于 MySQL 实践结合实际业务场景讲解多表查询的核心知识。适合初学者系统学习也适合进阶者查漏补缺 ✅一、前言为什么要学多表查询在真实开发中一个系统往往涉及多个数据表。例如员工信息emp部门信息dept学生与课程关系stu,course,student_course这些表之间存在多对多、一对多、一对一等复杂关系。想要从这些表中查询出有意义的数据就必须掌握多表查询技术。本文将带你全面掌握多表连接查询内连接、外连接、自连接隐式 vs 显式连接的区别联合查询UNION与UNION ALL的差异子查询分类详解标量、列、行、表附完整建表语句 实战案例 常见误区解析二、建模准备准备两张典型业务表 ——emp与dept1. 创建部门表deptCREATETABLEdept(dept_idINTAUTO_INCREMENTPRIMARYKEY,dept_nameVARCHAR(50)NOTNULLUNIQUECOMMENT部门名称,locationVARCHAR(100)COMMENT办公地点)ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT部门表;2. 创建员工表emp带层级结构CREATETABLEemp(emp_idINTAUTO_INCREMENTPRIMARYKEY,emp_nameVARCHAR(50)NOTNULLCOMMENT姓名,genderCHAR(1)DEFAULTNULLCOMMENT性别男/女,ageINTDEFAULTNULLCOMMENT年龄,jobVARCHAR(50)DEFAULTNULLCOMMENT职位,salaryDECIMAL(10,2)DEFAULTNULLCOMMENT薪资,manager_idINTDEFAULTNULLCOMMENT上级 ID指向 emp_id,hire_dateDATEDEFAULTNULLCOMMENT入职日期,dept_idINTDEFAULTNULLCOMMENT部门 ID,CONSTRAINTfk_emp_deptFOREIGNKEY(dept_id)REFERENCESdept(dept_id),CONSTRAINTfk_emp_managerFOREIGNKEY(manager_id)REFERENCESemp(emp_id),CONSTRAINTchk_genderCHECK(genderIN(男,女)ORgenderISNULL))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT员工表;3. 添加索引提升查询性能CREATEINDEXidx_emp_dept_idONemp(dept_id);CREATEINDEXidx_emp_nameONemp(emp_name);CREATEINDEXidx_emp_managerONemp(manager_id);4. 插入测试数据-- 插入部门INSERTINTOdept(dept_name,location)VALUES(技术部,北京中关村),(市场部,上海浦东),(人事部,广州天河);-- 插入员工含上下级关系INSERTINTOemp(emp_name,gender,age,job,salary,hire_date,dept_id,manager_id)VALUES(董事长,男,55,CEO,100000.00,2010-01-01,1,NULL),(张三,男,40,技术总监,35000.00,2015-03-15,1,1),(李四,女,38,市场总监,32000.00,2016-07-01,2,1),(王五,男,42,人事总监,30000.00,2014-09-10,3,1),(赵六,男,28,Java 工程师,15000.00,2020-03-15,1,2),(钱七,女,26,前端工程师,13000.00,2021-09-10,1,2),(孙八,男,29,后端工程师,16000.00,2019-05-20,1,2),(周九,女,27,市场专员,12000.00,2020-11-15,2,3),(吴十,男,30,市场经理,18000.00,2019-02-01,2,3),(郑十一,女,25,招聘专员,10000.00,2021-06-20,3,4),(刘十二,男,33,薪酬主管,14000.00,2018-12-01,3,4);三、多表查询连接查询详解什么是笛卡尔积当你不加条件进行多表查询时会生成笛卡尔积Cartesian ProductSELECT*FROMemp,dept;-- 结果emp 有11条dept 有3条 → 共 11×3 33 条记录⚠️ 必须通过WHERE条件过滤1. 内连接返回两个表的交集数据1隐式内连接旧式写法SELECTe.emp_name,d.dept_name,e.dept_idFROMemp e,dept dWHEREe.dept_idd.dept_id;2显式内连接推荐写法SELECTe.emp_name,d.dept_name,e.dept_idFROMemp eINNERJOINdept dONe.dept_idd.dept_id;显式内连接 vs 隐式内连接 区别总结项目隐式内连接显式内连接语法FROM A, B WHERE A.id B.idFROM A INNER JOIN B ON A.id B.id可读性较差结构混乱清晰易理解推荐度不推荐强烈推荐标准性非标准 SQLSQL-89标准 SQLSQL-92扩展性无法嵌套复杂连接支持链式连接如多表 JOIN结论永远优先使用INNER JOIN显式连接2. 外连接包含不匹配的数据1左外连接LEFT OUTER JOIN保留左表所有数据右表没有匹配的则为NULLSELECTe.emp_name,d.dept_nameFROMemp eLEFTOUTERJOINdept dONe.dept_idd.dept_id;场景查询所有员工包括没有分配部门的员工2右外连接RIGHT OUTER JOIN保留右表所有数据SELECTe.emp_name,d.dept_nameFROMemp eRIGHTOUTERJOINdept dONe.dept_idd.dept_id;场景查询所有部门即使没有员工 一般很少用右外连接可用左外连接倒换表顺序实现。3. 自连接表与自身连接用于查询层级关系如员工与上级关系查询员工及其上级姓名使用别名SELECTe1.emp_nameAS员工,e2.emp_nameAS上级FROMemp e1LEFTJOINemp e2ONe1.manager_ide2.emp_id;常见用途组织架构图评论回复链parent_id多级分类树四、联合查询UNION vs UNION ALL将多个SELECT查询结果合并成一个结果集。1、前提条件查询列数相同每列数据类型兼容2、示例查询薪资低于 5000 或年龄大于 40 的人-- 使用 UNION ALL保留所有数据包含重复SELECTemp_name,salary,age,jobFROMempWHEREsalary5000UNIONALLSELECTemp_name,salary,age,jobFROMempWHEREage40;-- 使用 UNION会自动去重SELECTemp_name,salary,age,jobFROMempWHEREsalary5000UNIONSELECTemp_name,salary,age,jobFROMempWHEREage40;3、UNION vs UNION ALL 区别对比项目UNIONUNION ALL是否去重是否性能较慢需排序比对快使用建议想要去重时不关心重复或数据量大时是否允许重复否允许最佳实践能用UNION ALL就不用UNION五、子查询详解附实战案例1、子查询定义SQL 语句中嵌套一个SELECT语句称为子查询。⚠️ 注意子查询必须用小括号()包裹外层可以是SELECT / INSERT / UPDATE / DELETE可出现在WHERE、FROM、SET等子句中1标量子查询返回一个值返回单行单列的结果-- 查询技术部的部门 IDSELECTdept_idFROMdeptWHEREdept_name技术部;-- 查询属于技术部的所有员工SELECT*FROMempWHEREdept_id(SELECTdept_idFROMdeptWHEREdept_name技术部);常用操作符,,,,IN可搭配ANY2列子查询返回一列多行返回多行一列常用于IN,ANY,SOME,ALL-- 查询市场部、人事部的员工SELECT*FROMempWHEREdept_idIN(SELECTdept_idFROMdeptWHEREdept_nameIN(市场部,人事部));拓展ANY,SOME,ALL-- 薪资高于所有人事部员工的员工SELECT*FROMempWHEREsalaryALL(SELECTsalaryFROMempWHEREdept_id(SELECTdept_idFROMdeptWHEREdept_name人事部));-- 薪资比任意一个人事部员工高的员工SELECT*FROMempWHEREsalaryANY(SELECTsalaryFROMempWHEREdept_id(SELECTdept_idFROMdeptWHEREdept_name人事部));ANY≈SOMEALL表示“所有”3行子查询返回一行多列返回一行多列常用于(col1, col2) IN (...)-- 查询与赵六薪资、上级相同的所有员工SELECT*FROMempWHERE(salary,manager_id)IN(SELECTsalary,manager_idFROMempWHEREemp_name赵六);适合主键匹配、联合键匹配4表子查询返回一张表子查询结果是一张完整的二维表常用于FROM子句-- 查询 2019 年入职的员工连同其部门信息SELECTt1.*,d.dept_nameFROM(SELECT*FROMempWHEREhire_date2019-01-01ANDhire_date2019-12-31)ASt1LEFTJOINdept dONt1.dept_idd.dept_id;为什么必须加别名AS t1答案如果不加别名MySQL 将无法识别子查询结果的表名所有子查询在FROM中都必须有别名否则会报错You have an error in your SQL syntax... near FROM (...) as t1通用规则任何子查询在FROM或JOIN中都必须加别名六、常见误区 最佳实践误区正确做法用隐式连接,写法改用JOIN ON用UNION而不考虑性能优先用UNION ALL子查询没有别名FROM (SELECT ...) AS t必须写JOIN时忘记ON条件必须写清楚联合条件多表查询不加索引为JOIN字段创建索引七、总结多表查询核心要点类型核心语法推荐写法内连接JOIN ON✅ 显式推荐左/右外连接LEFT JOIN / RIGHT JOIN✅ 推荐自连接T1 JOIN T1 别名✅ 必须加别名联合查询UNION ALLUNION✅ 性能优先子查询别名不能省略✅AS t是必须的八、多表关系建模一对一 / 一对多 / 多对多实战详解在实际开发中我们常需要设计复杂的表关系。以下是三种最常见的数据关系建模方式并结合你提供的数据结构进行完整演示。1. 多对多关系学生 ↔ 课程一个学生可选多门课一门课可被多个学生选举例学生“小王”选了 Java 和 Oracle 课程“Java”课程有 3 个学生。建表设计三张表-- 课程表CREATETABLEcourse(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLUNIQUECOMMENT课程名称);-- 学生表CREATETABLEstu(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLCOMMENT学生姓名);-- 中间表学生-课程关系多对多CREATETABLEstudent_course(stu_idINTNOTNULL,course_idINTNOTNULL,PRIMARYKEY(stu_id,course_id),-- 联合主键防止重复选课FOREIGNKEY(stu_id)REFERENCESstu(id)ONDELETECASCADE,FOREIGNKEY(course_id)REFERENCEScourse(id)ONDELETECASCADE);插入数据INSERTINTOcourse(name)VALUES(Java),(Oracle),(MySQL);INSERTINTOstu(name)VALUES(小王),(小张),(小李),(小赵);INSERTINTOstudent_course(stu_id,course_id)VALUES(1,1),-- 小王 选 Java(1,2),-- 小王 选 Oracle(2,1),-- 小张 选 Java(3,3),-- 小李 选 MySQL(4,1);-- 小赵 选 Java查询示例查选了“Java”的学生SELECTs.nameAS学生姓名,c.nameAS课程名称FROMstu sJOINstudent_course scONs.idsc.stu_idJOINcourse cONsc.course_idc.idWHEREc.nameJava;特点总结必须使用中间表中间表主键为联合主键(stu_id, course_id)支持去重一个学生不能重复选同一门课外键级联删除数据一致性高2. 一对多关系老师 ↔ 学生一个老师教多个学生一个学生只能有一个老师举例“张老师”教了小王、小张“小李”只属于“李老师”。建表设计-- 老师表CREATETABLEteacher(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLUNIQUECOMMENT老师姓名);-- 学生表外键指向 teacher.idCREATETABLEstudent_teacher(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLCOMMENT学生姓名,teacher_idINT,FOREIGNKEY(teacher_id)REFERENCESteacher(id)ONDELETESETNULL);ON DELETE SET NULL当老师删除时学生teacher_id变为NULL不级联删除。插入数据INSERTINTOteacher(name)VALUES(张老师),(李老师);INSERTINTOstudent_teacher(name,teacher_id)VALUES(小王,1),(小张,1),(小李,2),(小赵,2);查询示例查“张老师”教的所有学生SELECTt.nameAS老师,s.nameAS学生FROMteacher tJOINstudent_teacher sONt.ids.teacher_idWHEREt.name张老师;特点总结从“多”的一方添加外键一对多关系建模标准写法外键可设为NULL表示未分配老师3. 一对一关系老师 ↔ 专属学生一个老师只能有一个专属学生举例“王老师”只有一个专用学生“小李”“小李”只能属于“王老师”。建表设计-- 老师表CREATETABLEteacher_one_to_one(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULLUNIQUE);-- 学生表一对一外键 唯一约束CREATETABLEstudent_one_to_one(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(20)NOTNULL,teacher_idINTUNIQUE,-- 唯一约束确保一个老师对应一个学生FOREIGNKEY(teacher_id)REFERENCESteacher_one_to_one(id)ONDELETECASCADE);关键点teacher_id字段加了UNIQUE保证“一个老师只能有一个专属学生”。插入数据INSERTINTOteacher_one_to_one(name)VALUES(王老师),(陈老师);INSERTINTOstudent_one_to_one(name,teacher_id)VALUES(小李,1),-- 王老师专属学生(小赵,2);-- 陈老师专属学生查询示例查所有专属师生关系SELECTt.nameAS老师,s.nameAS学生FROMteacher_one_to_one tJOINstudent_one_to_one sONt.ids.teacher_id;特点总结通过外键 UNIQUE实现可双向查询适合“专属角色”场景如秘书、导师、医生–患者等三类关系对比一图胜千言关系类型表结构主键/外键设计建模要点多对多3 张表A、B、中间表中间表用联合主键(a_id, b_id)必须用中间表防止重复一对多2 张表A主B从从表加外键可为NULL外键放在“多”的一方一对一2 张表A主B从从表外键 UNIQUE用UNIQUE约束保证唯一性九、总结如何选择正确的建模方式需求场景推荐关系建议写法一个用户可关注多个标签一个标签可被多个用户关注多对多用中间表 联合主键一个老师可带多个学生一个学生只能有一个老师一对多外键放学生表一个医生只负责一个病人一个病人只能由一个医生负责一对一外键 UNIQUE一个订单可有多个订单项一个订单项只能属于一个订单一对多外键放项表一个图书可被多个借阅人借阅一个借阅人可借多本书多对多中间表借阅记录表特别提醒建模时请遵守原则不要把外键放在主表中如将student_id放进teacher表不要用VARCHAR做主键除非必要外键必须有索引提高查询性能使用ON DELETE CASCADE/SET NULL考虑数据一致性中间表命名要清晰如student_course、order_item