小叶-duck个人主页❄️个人专栏《Data-Structure-Learning》《C入门到进阶自我学习过程记录》《Linux系统从入门到实践》《Linux网络从入门到实践》《Qt 方寸极境》 《MySQL》✨未择之路不须回头已择之路纵是荆棘遍野亦作花海遨游目录前言一、基础查询回顾二、多表查询跨数据表关联查询核心用法2.1 测试表结构2.2 实战演示案例(附详细图示)2.2.1 双表关联整合员工与部门数据2.2.2 多表联动 条件过滤筛选指定部门员工信息2.2.3 关联多张表员工信息搭配薪资等级2.3 多表查询避坑点三、自连接单张数据表内部关联查询3.1 自连接语法解析3.2 实战示例查找员工对应的直属上级(附详细图示)3.3 使用自连接的实用技巧四、子查询嵌套查询多样化实践4.1 单行子查询返回单行单列结果集4.2 多行子查询返回多行单列结果集4.2.1 in 关键字匹配集合内任意数值4.2.2 all 关键字对比集合全部数据4.2.3 any 关键字匹配集合任一满足条件数据4.2.4 关键字 in VS 关键字 any 区别补充4.3 多列子查询返回多行多列数据集4.4 FROM 后嵌入子查询充当临时数据表(重点)4.5 子查询避坑指南五、合并查询union 与 union all 使用详解5.1 核心语法5.2 实战案例六. 实战 OJ 真题复合查询综合落地练习七、复合查询总结结束语前言日常业务开发场景里仅依靠单表查询很难支撑复杂的数据检索需求。业务数据通常分库分表存储例如员工信息、部门信息、薪资等级分散在不同数据表想要整合完整数据就离不开跨表关联进行条件筛选与数据统计时经常需要嵌套查询构建过滤逻辑面对多组结果集整合还需要使用集合操作语法。本文系统讲解 MySQL 复合查询各类核心用法覆盖多表联查、自连接、子查询以及合并查询。文中全部 SQL 语句统一采用小写书写贴近工程开发规范搭配大量实操示例同时梳理开发过程中常见误区与优化建议。一、基础查询回顾在学习复杂复合查询前先回顾基础查询的核心语法为后续进阶打基础-- 1. 条件筛选工资500或岗位为manager且姓名首字母为J select * from emp where (sal500 or jobmanager) and ename like j%-- 2. 多字段排序部门号升序工资降序 select * from emp order by deptno asc, sal desc;-- 3. 计算字段排序年薪sal*12补贴降序 select ename, sal*12ifnull(comm,0) as 年薪 from emp order by 年薪 desc;-- 4. 显示工资最高的员工的名字和工作岗位 select ename, job from emp where sal (select max(sal) from emp);-- 5. 显示工资高于平均工资的员工信息 select ename, sal from emp where sal (select avg(sal) from emp);-- 6. 显示每个部门的平均工资和最高工资 select deptno, format(avg(sal),2) as 平均工资, max(sal) as 最高工资 from emp group by deptno;-- 7. 显示平均工资低于2000的部门号和它的平均工资 select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal 2000;-- 8. 显示每种岗位的雇员总数平均工资 select job, count(*) as 雇员总数, format(avg(sal),2) as 平均工资 from emp group by job;二、多表查询跨数据表关联查询核心用法多表查询是复合查询的基础用于从多个关联表中提取数据核心是通过 “关联字段” 消除笛卡尔积无关联条件时表 1 所有行与表 2 所有行组合数据量爆炸。2.1 测试表结构本次实战基于 3 张经典表先明确表结构和关联关系emp员工表存储员工基本信息关联字段deptno关联部门表、sal关联薪资等级表dept部门表存储部门信息关联字段deptnosalgrade薪资等级表存储薪资等级规则关联字段losal最低工资、hisal最高工资。2.2 实战演示案例(附详细图示)接下来我们会例举出非常多的示例来帮助大家建立对多表查询的认识并且在图中我会为大家详细讲解为什么需要多表查询以及怎么实现多表查询。2.2.1 双表关联整合员工与部门数据需求查询员工姓名、工资及所在部门名称代码演示-- 核心通过deptno关联emp和dept表消除笛卡尔积 select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno dept.deptno;图示分析2.2.2 多表联动 条件过滤筛选指定部门员工信息需求查询 10 号部门的员工姓名、工资及部门名称代码演示select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno dept.deptno and dept.deptno 10; -- 筛选10号部门图示分析2.2.3 关联多张表员工信息搭配薪资等级需求查询员工姓名、工资及对应的薪资等级代码演示select emp.ename, emp.sal, salgrade.grade from emp, salgrade where emp.sal between salgrade.losal and salgrade.hisal; -- 工资在薪资等级区间内图示分析2.3 多表查询避坑点必须加关联条件无关联条件会产生笛卡尔积如 emp 有 14 行、dept 有 4 行会产生 14×456 行无效数据字段歧义需加表名前缀若多个表有同名字段如deptno需用表名.字段区分关联字段类型必须一致emp.deptno 和 dept.deptno 需同为 int 类型否则关联失效。三、自连接单张数据表内部关联查询自连接是多表查询的特殊形式 —— 将同一张表当作两张表使用通过别名区分适用于查询表内关联数据如员工与上级领导的关系。3.1 自连接语法解析select 表别名1.字段, 表别名2.字段 from 表 表别名1, 表 表别名2 where 表别名1.关联字段 表别名2.关联字段 [and 筛选条件];3.2 实战示例查找员工对应的直属上级(附详细图示)需求查询员工 ford 的上级领导编号和姓名emp表中mgr字段是领导的empno-- 方法1子查询简单场景 select empno, ename from emp where empno (select mgr from emp where enameford);-- 方法2自连接复杂场景更灵活 select leader.empno as 领导编号, leader.ename as 领导姓名 from emp leader, emp worker -- leader领导表worker员工表 where leader.empno worker.mgr -- 领导编号员工的上级编号 and worker.ename ford; -- 筛选员工为ford3.3 使用自连接的实用技巧必须给表起不同别名如leader、worker否则 MySQL 无法区分两张 “虚拟表”关联字段需是表内的关联关系如员工表的mgr与自身的empno。四、子查询嵌套查询多样化实践子查询嵌套查询是指嵌入在其他 SQL 语句中的 select 语句按返回结果可分为单行、多行、多列子查询按位置可分为 where 子句、from 子句中的子查询。4.1 单行子查询返回单行单列结果集适用于筛选条件为 “等于、大于、小于” 单个值的场景常用比较运算符、、、、。实战案例需求查询与 smith 同部门的所有员工不含 smith代码演示select * from emp where deptno (select deptno from emp where enamesmith) -- 子查询返回smith的部门号 and ename ! smith; -- 排除smith本人图示分析4.2 多行子查询返回多行单列结果集适用于筛选条件为 “在多个值中”“大于所有值”“大于任意值” 的场景常用关键字in、all、any。4.2.1 in 关键字匹配集合内任意数值实战案例需求查询和 10 号部门岗位相同但不属于 10 号部门的员工代码演示select ename, job, sal, deptno from emp where job in (select distinct job from emp where deptno10) -- 子查询返回10号部门的所有岗位 and deptno ! 10; -- 排除10号部门图示分析4.2.2 all 关键字对比集合全部数据实战案例需求查询工资比 30 号部门所有员工工资都高的员工代码演示select ename, sal, deptno from emp where sal all(select sal from emp where deptno30); -- 工资30号部门所有员工工资图示分析4.2.3 any 关键字匹配集合任一满足条件数据实战案例需求查询工资比 30 号部门任意员工工资高的员工含自身部门代码演示select ename, sal, deptno from emp where sal any(select sal from emp where deptno30); -- 工资30号部门至少一个员工工资图示分析4.2.4 关键字 in VS 关键字 any 区别补充4.3 多列子查询返回多行多列数据集适用于筛选条件需匹配 “多个字段组合” 的场景子查询返回多列主查询用括号接收字段组合。实战案例需求查询与 smith 部门和岗位完全相同的员工不含 smith代码演示select ename from emp where (deptno, job) (select deptno, job from emp where enamesmith) -- 匹配部门岗位组合 and ename ! smith;图示分析4.4 FROM 后嵌入子查询充当临时数据表(重点)将子查询结果当作 “临时表”用于复杂统计分析如先聚合再关联核心是给临时表起别名。实战案例 1显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资代码演示select emp.ename, emp.deptno, emp.sal, format(tmp.asal,2) as 部门平均工资 from emp, (select avg(sal) as asal, deptno as dt from emp group by deptno) tmp -- 临时表各部门平均工资 where emp.deptno tmp.dt -- 员工部门临时表部门 and emp.sal tmp.asal; -- 员工工资部门平均工资图示分析实战案例 1 拓展练习(显示这些员工所对应的部门名称)实战案例 2查找每个部门工资最高的人的姓名、工资、部门、最高工资代码演示select emp.ename, emp.sal, emp.deptno, tmp.ms as 部门最高工资 from emp, (select max(sal) as ms, deptno from emp group by deptno) tmp -- 临时表各部门最高工资 where emp.deptno tmp.deptno and emp.sal tmp.ms;图示分析实战案例 3显示每个部门的信息部门名编号地址和人员数量代码演示select DEPT.dname, DEPT.deptno, DEPT.loc,count(*) 部门人数 from EMP, DEPT where EMP.deptnoDEPT.deptno group by DEPT.deptno,DEPT.dname,DEPT.loc;图示分析4.5 子查询避坑指南单行子查询只能用单行运算符若子查询返回多行不能用需用infrom 子句的子查询必须起别名MySQL 要求临时表必须有别名否则报错子查询尽量简化复杂子查询可拆分为临时表或多步查询提升可读性和性能。五、合并查询union 与 union all 使用详解合并查询用于将多个 select 语句的结果集合并为一个适用于多条件独立查询后合并结果的场景核心是union去重和union all不去重。5.1 核心语法-- 去重合并自动删除重复行 select 字段 from 表1 where 条件 union select 字段 from 表2 where 条件; -- 不去重合并保留重复行性能更优 select 字段 from 表1 where 条件 union all select 字段 from 表2 where 条件;5.2 实战案例案例 1union 去重合并查询工资 2500 或岗位为 manager 的员工去重代码演示select ename, sal, job from emp where sal2500 union -- 自动去重manager中工资2500的员工只显示一次 select ename, sal, job from emp where jobmanager;--------------------------- | ename | sal | job | --------------------------- | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | CLARK | 2450.00 | MANAGER | ---------------------------案例 2union all 不去重合并查询工资 2500 或岗位为 manager 的员工保留重复代码演示select ename, sal, job from emp where sal2500 union all -- 保留重复行manager中工资2500的员可能会显示多次 select ename, sal, job from emp where jobmanager;--------------------------- | ename | sal | job | --------------------------- | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | SCOTT | 3000.00 | ANALYST | | KING | 5000.00 | PRESIDENT | | FORD | 3000.00 | ANALYST | | JONES | 2975.00 | MANAGER | | BLAKE | 2850.00 | MANAGER | | CLARK | 2450.00 | MANAGER | ---------------------------六. 实战 OJ 真题复合查询综合落地练习真题 1查找所有员工入职时的薪水情况emp_nosalary逆序查找所有员工入职时候的薪水情况_牛客题霸_牛客网select employees.emp_no, salary from employees, salaries where employees.emp_nosalaries.emp_no and hire_datefrom_date order by employees.emp_no desc;真题 2获取所有非 manager 的员工 emp_no获取所有非manager的员工emp_no_牛客题霸_牛客网select emp_no from employees where emp_no not in(select emp_no from dept_manager);真题 3获取所有员工当前的 manager排除 manager 是自己的情况获取所有员工当前的manager_牛客题霸_牛客网select dept_emp.emp_no, dept_manager.emp_no as manager from dept_emp, dept_manager where dept_emp.dept_nodept_manager.dept_no and dept_emp.emp_nodept_manager.emp_no;七、复合查询总结MySQL 复合查询是解决复杂业务需求的核心核心要点总结多表查询通过关联字段消除笛卡尔积适用于跨表提取数据自连接同表当作两张表适用于表内关联如员工与领导子查询嵌套在 where/from 子句中灵活筛选和统计需注意单行 / 多行匹配规则合并查询union去重和 union all不去重适用于多结果集合并避坑关键关联字段一致、临时表起别名、优先选择高效语法如 union all 替代 union。结束语本篇系统梳理了 MySQL 复合查询各类实现方式涵盖多表联查、自连接、子查询与合并查询等核心语法。不同查询方案各有适用场景开发中不能一味套用模板需要结合表数据量、索引情况合理选择写法。熟练掌握复合查询是编写高级 SQL 的基础后续遇到复杂业务统计、多维度数据检索需求时便能灵活组合各类查询语法。同时需要留意文中提到的性能隐患避免写出低效 SQL。大家可以结合配套练习题动手实操加深理解。
