SQL算法
MySQL 复合查询核心指南：多表、子查询与实战技巧
MySQL 复合查询涵盖多表关联、自连接、子查询及合并查询。通过关联字段消除笛卡尔积实现跨表数据提取；自连接处理同表层级关系；子查询支持嵌套筛选与临时表统计；Union 用于结果集合并。掌握关联条件、别名管理及性能优化要点，可应对复杂业务场景下的数据检索需求。
MySQL 复合查询涵盖多表关联、自连接、子查询及合并查询。通过关联字段消除笛卡尔积实现跨表数据提取；自连接处理同表层级关系；子查询支持嵌套筛选与临时表统计；Union 用于结果集合并。掌握关联条件、别名管理及性能优化要点，可应对复杂业务场景下的数据检索需求。
在实际开发中，单表查询远不能满足复杂业务需求 —— 员工信息散落在员工表、部门表、薪资等级表中，需要跨表关联才能获取完整数据；统计分析时需嵌套查询筛选条件；多结果集合并需用到集合操作符。
在学习复杂复合查询前，先回顾基础查询的核心语法，为后续进阶打基础：
-- 1. 条件筛选：工资>500 或岗位为 manager，且姓名首字母为 J
select * from emp where (sal > 500 or job = 'manager') and ename like 'j%';
-- 2. 多字段排序：部门号升序，工资降序
select * from emp order by deptno asc, sal desc;
-- 3. 计算字段 + 排序：年薪（sal*12+补贴）降序
select ename, sal * 12 + ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;
-- 4. 聚合查询 + 筛选：各部门平均工资（保留 2 位小数）和最高工资
select deptno, format(avg(sal), 2) as 平均工资，max(sal) as 最高工资 from emp group by deptno;
-- 5. having 筛选聚合结果：平均工资低于 2000 的部门
select deptno, avg(sal) as avg_sal from emp group by deptno having avg_sal < 2000;
多表查询是复合查询的基础，用于从多个关联表中提取数据，核心是通过'关联字段'消除笛卡尔积（无关联条件时，表 1 所有行与表 2 所有行组合，数据量爆炸）。
本次实战基于 3 张经典表，先明确表结构和关联关系：
deptno（关联部门表）、sal（关联薪资等级表）；deptno；losal（最低工资）、hisal（最高工资）。select 表 1.字段，表 2.字段 from 表 1, 表 2
where 表 1.关联字段 = 表 2.关联字段 [and 其他筛选条件];
需求：查询员工姓名、工资及所在部门名称
-- 核心：通过 deptno 关联 emp 和 dept 表，消除笛卡尔积
select emp.ename, emp.sal, dept.dname from emp, dept where emp.deptno = dept.deptno;
需求：查询 10 号部门的员工姓名、工资及部门名称
select emp.ename, emp.sal, dept.dname from emp, dept
where emp.deptno = dept.deptno and dept.deptno = 10; -- 筛选 10 号部门
需求：查询员工姓名、工资及对应的薪资等级
select emp.ename, emp.sal, salgrade.grade from emp, salgrade
where emp.sal between salgrade.losal and salgrade.hisal; -- 工资在薪资等级区间内
deptno），需用表名。字段区分；自连接是多表查询的特殊形式 —— 将同一张表当作两张表使用，通过别名区分，适用于查询表内关联数据（如员工与上级领导的关系）。
select 表别名 1.字段，表别名 2.字段 from 表 表别名 1, 表 表别名 2
where 表别名 1.关联字段 = 表别名 2.关联字段 [and 筛选条件];
需求：查询员工 ford 的上级领导编号和姓名（emp 表中 mgr 字段是领导的 empno）
-- 方法 1：子查询（简单场景）
select empno, ename from emp where empno = (select mgr from emp where ename='ford');
-- 方法 2：自连接（复杂场景更灵活）
select leader.empno as 领导编号，leader.ename as 领导姓名
from emp leader, emp worker -- leader=领导表，worker=员工表
where leader.empno = worker.mgr -- 领导编号=员工的上级编号
and worker.ename = 'ford'; -- 筛选员工为 ford
leader、worker），否则 MySQL 无法区分两张'虚拟表'；mgr 与自身的 empno）。
子查询（嵌套查询）是指嵌入在其他 SQL 语句中的 select 语句，按返回结果可分为单行、多行、多列子查询，按位置可分为 where 子句、from 子句中的子查询。
适用于筛选条件为'等于、大于、小于'单个值的场景，常用比较运算符（=、>、<、>=、<=）。
实战案例： 需求：查询与 smith 同部门的所有员工（不含 smith）
select * from emp
where deptno = (select deptno from emp where ename='smith') -- 子查询返回 smith 的部门号
and ename != 'smith'; -- 排除 smith 本人
适用于筛选条件为'在多个值中''大于所有值''大于任意值'的场景，常用关键字 in、all、any。
需求：查询和 10 号部门岗位相同，但不属于 10 号部门的员工
select ename, job, sal, deptno from emp
where job in (select distinct job from emp where deptno=10) -- 子查询返回 10 号部门的所有岗位
and deptno != 10; -- 排除 10 号部门
需求：查询工资比 30 号部门所有员工工资都高的员工
select ename, sal, deptno from emp
where sal > all (select sal from emp where deptno=30); -- 工资>30 号部门所有员工工资
需求：查询工资比 30 号部门任意员工工资高的员工（含自身部门）
select ename, sal, deptno from emp
where sal > any (select sal from emp where deptno=30); -- 工资>30 号部门至少一个员工工资
适用于筛选条件需匹配'多个字段组合'的场景，子查询返回多列，主查询用括号接收字段组合。
实战案例： 需求：查询与 smith 部门和岗位完全相同的员工（不含 smith）
select ename from emp
where (deptno, job) = (select deptno, job from emp where ename='smith') -- 匹配部门 + 岗位组合
and ename != 'smith';
将子查询结果当作'临时表'，用于复杂统计分析（如先聚合再关联），核心是给临时表起别名。
实战案例 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; -- 员工工资>部门平均工资
实战案例 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;
=，需用 in；合并查询用于将多个 select 语句的结果集合并为一个，适用于多条件独立查询后合并结果的场景，核心是 union（去重）和 union all（不去重）。
-- 去重合并（自动删除重复行）
select 字段 from 表 1 where 条件 union
select 字段 from 表 2 where 条件;
-- 不去重合并（保留重复行，性能更优）
select 字段 from 表 1 where 条件 union all
select 字段 from 表 2 where 条件;
案例 1：union 去重合并 需求：查询工资 > 2500 或岗位为 manager 的员工（去重）
select ename, sal, job from emp where sal > 2500 union -- 自动去重（manager 中工资>2500 的员工只显示一次）
select ename, sal, job from emp where job='manager';
案例 2：union all 不去重合并 需求：查询工资 > 2500 或岗位为 manager 的员工（保留重复）
select ename, sal, job from emp where sal > 2500 union all -- 保留重复行（manager 中工资>2500 的员可能会显示多次）
select ename, sal, job from emp where job='manager';
结合牛客网经典 OJ 题，练习复合查询的实际应用： 真题 1：查找所有员工入职时的薪水情况（emp_no+salary，逆序）
select e.emp_no, s.salary from employees e, salaries s
where e.emp_no = s.emp_no and s.from_date = e.hire_date
order by e.emp_no desc;
真题 2：生成所有表的 count 查询语句
select concat('select count(*) from ', table_name, ';') as count_sql
from information_schema.tables where table_schema = 'your_database_name'; -- 替换为你的数据库名
真题 3：获取所有非 manager 的员工 emp_no
select emp_no from employees where emp_no not in (select emp_no from dept_manager);
真题 4：获取所有员工当前的 manager（排除 manager 是自己的情况）
select e.emp_no, d.emp_no as manager from dept_emp as e, dept_manager as d
where e.dept_no = d.dept_no and e.emp_no != d.emp_no;
MySQL 复合查询是解决复杂业务需求的核心，核心要点总结：
微信公众号「极客日志」，在微信中扫描左侧二维码关注。展示文案：极客日志 zeeklog
使用加密算法（如AES、TripleDES、Rabbit或RC4）加密和解密文本明文。 在线工具，加密/解密文本在线工具，online
在线格式化和美化您的 SQL 查询（它支持各种 SQL 方言）。 在线工具，SQL 美化和格式化在线工具，online
解析 INSERT 等受限 SQL，导出为 CSV、JSON、XML、YAML、HTML 表格（见页内语法说明）。 在线工具，SQL转CSV/JSON/XML在线工具，online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转，单页多 Tab。 在线工具，CSV 工具包在线工具，online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具，Base64 字符串编码/解码在线工具，online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具，Base64 文件转换器在线工具，online