1. 基本查询回顾
1.1 复杂条件查询
-- 查询工资高于500或岗位为MANAGER的雇员，同时满足姓名首字母为大写J SELECT * FROM EMP WHERE (sal > 500 OR job = 'MANAGER') AND ename LIKE 'J%';1.2 排序查询
-- 按照部门号升序而雇员的工资降序排序 SELECT * FROM EMP ORDER BY deptno, sal DESC; -- 使用年薪进行降序排序 SELECT ename, sal * 12 + ifnull(comm, 0) AS '年薪' FROM EMP ORDER BY 年薪 DESC;1.3 子查询应用
-- 显示工资最高的员工的名字和工作岗位 SELECT ename, job FROM EMP WHERE sal = (SELECT MAX(sal) FROM EMP); -- 显示工资高于平均工资的员工信息 SELECT ename, sal FROM EMP WHERE sal > (SELECT AVG(sal) FROM EMP);1.4 分组统计
-- 显示每个部门的平均工资和最高工资 SELECT deptno, FORMAT(AVG(sal), 2), MAX(sal) FROM EMP GROUP BY deptno; -- 显示平均工资低于2000的部门号和它的平均工资 SELECT deptno, AVG(sal) AS avg_sal FROM EMP GROUP BY deptno HAVING avg_sal < 2000; -- 显示每种岗位的雇员总数，平均工资 SELECT job, COUNT(*), FORMAT(AVG(sal), 2) FROM EMP GROUP BY job;2. 多表查询（重点）
2.1 多表查询的基本概念
实际开发中数据往往来自不同的表，需要进行多表查询。我们使用公司管理系统中的三张表演示：
EMP表：员工信息
DEPT表：部门信息
SALGRADE表：工资等级
2.2 笛卡尔积与连接条件
-- 错误的查询：会产生笛卡尔积（14×4=56条记录） SELECT * FROM EMP, DEPT; -- 正确的多表查询：添加连接条件 SELECT EMP.ename, EMP.sal, DEPT.dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno;2.3 多表查询示例
-- 显示部门号为10的部门名，员工名和工资 SELECT ename, sal, dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno AND DEPT.deptno = 10; -- 显示各个员工的姓名，工资，及工资级别 SELECT ename, sal, grade FROM EMP, SALGRADE WHERE EMP.sal BETWEEN losal AND hisal;3. 自连接查询
3.1 自连接概念
自连接是指在同一张表上进行连接查询，通常用于处理层次关系数据。
3.2 自连接示例
案例：显示员工FORD的上级领导的编号和姓名
方法1：使用子查询
SELECT empno, ename FROM emp WHERE emp.empno = (SELECT mgr FROM emp WHERE ename = 'FORD');方法2：使用自连接（推荐）
-- 使用表别名区分子查询 SELECT leader.empno, leader.ename FROM emp leader, emp worker WHERE leader.empno = worker.mgr AND worker.ename = 'FORD';自连接技巧：
给同一张表起不同的别名（如leader、worker）
通过别名区分不同角色的数据
性能通常优于子查询
4. 子查询（嵌套查询）
4.1 单行子查询
返回一行记录的子查询
-- 显示SMITH同一部门的员工 SELECT * FROM EMP WHERE deptno = (SELECT deptno FROM EMP WHERE ename = 'SMITH');4.2 多行子查询
返回多行记录的子查询，需要配合特定关键字使用
4.2.1 IN关键字
-- 查询和10号部门的工作岗位相同的雇员 -- 但不包含10号部门自己的员工 SELECT ename, job, sal, deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10) AND deptno <> 10;4.2.2 ALL关键字
-- 显示工资比部门30的所有员工的工资都高的员工 SELECT ename, sal, deptno FROM EMP WHERE sal > ALL(SELECT sal FROM EMP WHERE deptno = 30);4.2.3 ANY关键字
-- 显示工资比部门30的任意员工的工资高的员工 SELECT ename, sal, deptno FROM EMP WHERE sal > ANY(SELECT sal FROM EMP WHERE deptno = 30);关键字区别：
IN：等于子查询结果中的任意一个ALL：比子查询结果中的所有值都...ANY：比子查询结果中的任意一个值都...
4.3 多列子查询
查询返回多个列数据的子查询
-- 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人 SELECT ename FROM EMP WHERE (deptno, job) = (SELECT deptno, job FROM EMP WHERE ename = 'SMITH') AND ename <> 'SMITH';4.4 在FROM子句中使用子查询
将子查询结果作为临时表使用
案例1：显示每个高于自己部门平均工资的员工
SELECT ename, deptno, sal, FORMAT(asal, 2) FROM EMP, ( SELECT AVG(sal) asal, deptno dt FROM EMP GROUP BY deptno ) tmp WHERE EMP.sal > tmp.asal AND EMP.deptno = tmp.dt;案例2：查找每个部门工资最高的人
SELECT EMP.ename, EMP.sal, EMP.deptno, ms FROM EMP, ( SELECT MAX(sal) ms, deptno FROM EMP GROUP BY deptno ) tmp WHERE EMP.deptno = tmp.deptno AND EMP.sal = tmp.ms;案例3：显示每个部门的信息和人员数量
方法1：使用多表连接
SELECT DEPT.dname, DEPT.deptno, DEPT.loc, COUNT(*) AS '部门人数' FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno GROUP BY DEPT.deptno, DEPT.dname, DEPT.loc;方法2：使用子查询（推荐）
SELECT DEPT.deptno, dname, mycnt, loc FROM DEPT, ( SELECT COUNT(*) mycnt, deptno FROM EMP GROUP BY deptno ) tmp WHERE DEPT.deptno = tmp.deptno;5. 合并查询
5.1 UNION操作符
取得两个结果集的并集，自动去掉重复行
-- 将工资大于2500或职位是MANAGER的人找出来 SELECT ename, sal, job FROM EMP WHERE sal > 2500 UNION SELECT ename, sal, job FROM EMP WHERE job = 'MANAGER';5.2 UNION ALL操作符
取得两个结果集的并集，不会去掉重复行
-- 将工资大于2500或职位是MANAGER的人找出来（包含重复记录） SELECT ename, sal, job FROM EMP WHERE sal > 2500 UNION ALL SELECT ename, sal, job FROM EMP WHERE job = 'MANAGER';5.3 UNION vs UNION ALL
特性 | UNION | UNION ALL |
|---|---|---|
去重 | 自动去掉重复行 | 保留所有行 |
性能 | 较慢（需要去重） | 较快 |
排序 | 结果集自动排序 | 不保证顺序 |
使用场景 | 需要唯一结果时 | 需要完整结果时 |
6. 实战技巧与性能优化
6.1 查询执行顺序理解
-- 理解SQL执行顺序 SELECT deptno, AVG(sal) as avg_sal -- 5. 选择字段 FROM EMP -- 1. 数据源 WHERE sal > 1000 -- 2. 条件过滤 GROUP BY deptno -- 3. 分组 HAVING avg_sal > 2000 -- 4. 分组后过滤 ORDER BY avg_sal DESC; -- 6. 排序6.2 性能优化建议
连接条件优先：多表查询时先写连接条件，再写过滤条件
合理使用索引：连接字段和常用查询字段建立索引
避免SELECT*：只选择需要的字段
子查询优化：能用连接查询尽量不用子查询
分页查询：大数据量时使用LIMIT分页
6.3 复杂查询调试技巧
-- 分步调试复杂查询 -- 步骤1：先验证子查询结果 SELECT deptno FROM EMP WHERE ename = 'SMITH'; -- 步骤2：再验证主查询 SELECT * FROM EMP WHERE deptno = 20; -- 步骤3：组合成完整查询 SELECT * FROM EMP WHERE deptno = (SELECT deptno FROM EMP WHERE ename = 'SMITH');7. 实战OJ题目示例
7.1 牛客网典型题目
-- 查找所有员工入职时候的薪水情况 SELECT e.emp_no, s.salary FROM employees e, salaries s WHERE e.emp_no = s.emp_no AND e.hire_date = s.from_date ORDER BY e.emp_no DESC; -- 获取所有非manager的员工emp_no SELECT emp_no FROM employees WHERE emp_no NOT IN ( SELECT emp_no FROM dept_manager ); -- 获取所有员工当前的manager SELECT e.emp_no, m.emp_no as manager_no FROM dept_emp e, dept_manager m WHERE e.dept_no = m.dept_no AND e.to_date = '9999-01-01' AND m.to_date = '9999-01-01';8. 总结
8.1 查询类型选择指南
场景 | 推荐查询方式 | 理由 |
|---|---|---|
简单单表查询 | 基本SELECT | 性能最好 |
多表关联查询 | 多表连接 | 直观易懂 |
层次关系查询 | 自连接 | 性能优于子查询 |
存在性检查 | EXISTS子查询 | 效率高 |
结果集合并 | UNION/UNION ALL | 根据去重需求选择 |
8.2 最佳实践
明确需求：先分析需要什么数据，来自哪些表
选择最优方案：根据数据量和关系选择查询方式
分步验证：复杂查询先验证各部分结果
性能测试：大数据量时测试查询性能
代码可读性：合理使用别名和格式化
掌握复合查询是MySQL数据库开发的核心技能，通过大量实践可以熟练运用各种查询技巧，编写出高效、可维护的SQL语句。
