MySQL多表查询详解

MySQL 多表查询详解在实际数据库中数据通常分散在多个表中通过关联字段外键连接。多表查询就是从两个或更多表中检索数据核心是JOIN操作。一、多表查询的基本语法SELECT列列表FROM表1[连接类型]JOIN表2ON连接条件[WHERE筛选][GROUPBY分组][ORDERBY排序];其中连接类型包括INNER JOIN内连接默认LEFT JOIN左外连接RIGHT JOIN右外连接FULL JOIN全外连接MySQL 不直接支持可通过UNION模拟CROSS JOIN交叉连接笛卡尔积二、连接类型详解假设有以下两张示例表students学生表student_idnameclass_id1张三12李四23王五NULLclasses班级表class_idclass_name1一班2二班3三班1. INNER JOIN内连接作用返回两个表中连接条件匹配的行。不匹配的行不出现。结果只包含双方都有的记录。SELECTs.name,c.class_nameFROMstudents sINNERJOINclasses cONs.class_idc.class_id;结果nameclass_name张三一班李四二班王五无班级三班无学生均不出现2. LEFT JOIN左外连接作用返回左表FROM后的表所有行右表无匹配则填充NULL。结果左表行全保留。SELECTs.name,c.class_nameFROMstudents sLEFTJOINclasses cONs.class_idc.class_id;结果nameclass_name张三一班李四二班王五NULL3. RIGHT JOIN右外连接作用返回右表所有行左表无匹配则填充NULL。SELECTs.name,c.class_nameFROMstudents sRIGHTJOINclasses cONs.class_idc.class_id;结果nameclass_name张三一班李四二班NULL三班4. FULL JOIN全外连接MySQL 不直接支持可通过LEFT JOIN UNION RIGHT JOIN模拟SELECTs.name,c.class_nameFROMstudents sLEFTJOINclasses cONs.class_idc.class_idUNIONSELECTs.name,c.class_nameFROMstudents sRIGHTJOINclasses cONs.class_idc.class_id;结果nameclass_name张三一班李四二班王五NULLNULL三班5. CROSS JOIN交叉连接 / 笛卡尔积返回两表的笛卡尔积行数 左表行数 × 右表行数。慎用除非需要所有组合。SELECTs.name,c.class_nameFROMstudents sCROSSJOINclasses c;结果有 3×3 9 行。6. 自连接Self JOIN一张表与自身连接通常用于树形结构或比较同一表中不同行。-- 查询每个学生的同班同学SELECTa.nameASstudent,b.nameASclassmateFROMstudents aJOINstudents bONa.class_idb.class_idANDa.student_id!b.student_id;7. 自然连接NATURAL JOIN自动根据同名列进行等值连接不推荐使用隐式、不可控。SELECT*FROMstudentsNATURALJOINclasses;-- 等价于 ON students.class_id classes.class_id三、联合查询UNION / UNION ALL将多个SELECT结果纵向堆叠要求各查询列数相同、对应数据类型兼容。UNION自动去重。UNION ALL保留所有行效率更高。SELECTnameFROMstudentsUNIONSELECTclass_nameFROMclasses;四、子查询Subquery子查询是嵌套在另一个 SQL 中的查询可出现在SELECT、FROM、WHERE、HAVING子句中。1. 标量子查询返回单值-- 查询班级人数超过平均人数的班级SELECTclass_nameFROMclassesWHEREclass_idIN(SELECTclass_idFROMstudentsGROUPBYclass_idHAVINGCOUNT(*)(SELECTAVG(cnt)FROM(SELECTCOUNT(*)cntFROMstudentsGROUPBYclass_id)t));2. 列子查询返回一列多行使用IN、ANY、ALL等操作符。-- 查询有学生的班级SELECTclass_nameFROMclassesWHEREclass_idIN(SELECTDISTINCTclass_idFROMstudentsWHEREclass_idISNOTNULL);3. 表子查询返回多行多列—— 派生表-- 统计每个班级的学生数SELECTt.class_id,t.cntFROM(SELECTclass_id,COUNT(*)cntFROMstudentsGROUPBYclass_id)t;4. 相关子查询子查询依赖外层-- 查询工资大于本部门平均工资的员工SELECTe.name,e.salary,e.dept_idFROMemployees eWHEREsalary(SELECTAVG(salary)FROMemployeesWHEREdept_ide.dept_id);5. EXISTS / NOT EXISTS通常比IN更高效因为只需要判断存在性。-- 查询有学生的班级SELECT*FROMclasses cWHEREEXISTS(SELECT1FROMstudents sWHEREs.class_idc.class_id);五、多表查询优化建议使用合适的连接类型不需要左表所有行时用INNER JOIN。为连接列建立索引尤其是ON和WHERE中使用的列。避免SELECT *只取出需要的列减少数据传输。用小结果集驱动大结果集例如先筛选再连接。将子查询改写为JOIN通常性能更好。分析执行计划EXPLAIN SELECT ...查看索引使用情况。注意NULL的影响外连接中NULL可能导致WHERE条件意外过滤掉行。六、综合示例场景电商数据库包含用户表、订单表、订单明细表、商品表。-- 查询每个用户的订单总金额含用户名、订单号、总金额SELECTu.user_name,o.order_id,SUM(oi.quantity*oi.price)AStotal_amountFROMusers uJOINorders oONu.user_ido.user_idJOINorder_items oiONo.order_idoi.order_idGROUPBYu.user_name,o.order_id;七、小结连接类型关键字返回结果内连接INNER JOIN只返回匹配的行左外连接LEFT JOIN左表全部右表匹配或 NULL右外连接RIGHT JOIN右表全部左表匹配或 NULL全外连接FULL JOINMySQL模拟两表全部不匹配处 NULL交叉连接CROSS JOIN笛卡尔积自连接JOIN同一表表内行间关联联合查询UNION/UNION ALL结果集纵向合并子查询嵌套 SELECT作为条件、派生表等掌握这些多表查询技巧可以灵活应对 90% 以上的数据提取需求。实际开发中优先使用显式JOIN语法ANSI SQL 92 风格避免隐式连接逗号连接。