MySQL -复合查询

目录

一、先回顾：单表查询的基础操作（以EMP表为例）

1. 带条件的筛选

2. 排序与计算字段

（1）按部门号升序、工资降序排列

（2）计算年薪（工资 ×12 + 奖金，奖金为空则按 0 算）并排序

3. 聚合与分组查询

（1）查每个部门的平均工资、最高工资

（2）筛选平均工资 < 2000的部门

二、多表查询：跨表关联数据

DEPT部门表

SALGRADE工资级别表

1. 基础多表联查（等值连接）

（1）查 “员工名、工资、所属部门名”

（2）限定部门号为 10的员工

2. 三表联查（含工资级别表）

三、自连接：同一张表查上下级

四、子查询：用查询结果当条件 / 临时表

1. 单行子查询（返回一条结果）

2. 多行子查询（返回多条结果）

（1）查 “和 10 号部门岗位相同、但不属于 10 号部门” 的员工

（2）查 “工资比 30 号部门所有员工都高” 的员工

3. from 子句子查询（把子查询当临时表）

五、合并查询：union/union all

1. union（自动去重）

2. union all（保留重复）

写在最后

---

在实际开发中，仅用单表查询显然无法满足复杂业务需求。今天我们就以经典的员工管理系统（EMP员工表、DEPT部门表、SALGRADE工资级别表）为例，聊聊 MySQL 复合查询的核心玩法 —— 从单表查询到多表联查、子查询，再到合并查询，每一步都附上真实查询结果，帮你直观理解。

一、先回顾：单表查询的基础操作（以EMP表为例）

先明确EMP表基础数据（对应图片中表内容）：

empno	ename	job	mgr	hiredate	sal	comm	deptno
7369	SMITH	CLERK	7902	1980-12-17	800	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL	10

1. 带条件的筛选

SELECT * FROM EMP 
WHERE (sal>500 OR job='MANAGER') 
AND ename LIKE 'J%';

查询结果：

empno	ename	job	mgr	hiredate	sal	comm	deptno
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950	NULL	30

2. 排序与计算字段

（1）按部门号升序、工资降序排列

SELECT * FROM EMP 
ORDER BY deptno, sal DESC;

查询结果（节选核心字段）：

empno	ename	sal	deptno
7839	KING	5000	10
7782	CLARK	2450	10
7934	MILLER	1300	10
7788	SCOTT	3000	20
7902	FORD	3000	20
7566	JONES	2975	20
7876	ADAMS	1100	20
7369	SMITH	800	20
7698	BLAKE	2850	30
7499	ALLEN	1600	30
7844	TURNER	1500	30
7521	WARD	1250	30
7654	MARTIN	1250	30
7900	JAMES	950	30

（2）计算年薪（工资 ×12 + 奖金，奖金为空则按 0 算）并排序

SELECT ename, sal*12+IFNULL(comm,0) AS '年薪' 
FROM EMP 
ORDER BY 年薪 DESC;

查询结果：

ename	年薪
KING	60000
SCOTT	36000
FORD	36000
JONES	35700
BLAKE	34200
CLARK	29400
ALLEN	19500
TURNER	18000
MARTIN	16400
MILLER	15600
WARD	15500
ADAMS	13200
JAMES	11400
SMITH	9600

3. 聚合与分组查询

（1）查每个部门的平均工资、最高工资

SELECT deptno, FORMAT(AVG(sal),2) AS avg_sal, MAX(sal) AS max_sal 
FROM EMP 
GROUP BY deptno;

查询结果：

deptno	avg_sal	max_sal
10	2,916.67	5000
20	2,175.00	3000
30	1,566.67	2850

（2）筛选平均工资 < 2000的部门

SELECT deptno, AVG(sal) AS avg_sal 
FROM EMP 
GROUP BY deptno 
HAVING avg_sal<2000;

查询结果：

deptno	avg_sal
30	1566.6667

二、多表查询：跨表关联数据

先明确关联表基础数据：

DEPT部门表

deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

SALGRADE工资级别表

grade	losal	hisal
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999

1. 基础多表联查（等值连接）

（1）查 “员工名、工资、所属部门名”

SELECT EMP.ename, EMP.sal, DEPT.dname 
FROM EMP, DEPT 
WHERE EMP.deptno = DEPT.deptno;

查询结果（节选）：

ename	sal	dname
SMITH	800	RESEARCH
ALLEN	1600	SALES
WARD	1250	SALES
JONES	2975	RESEARCH
MARTIN	1250	SALES
BLAKE	2850	SALES
CLARK	2450	ACCOUNTING
SCOTT	3000	RESEARCH
KING	5000	ACCOUNTING
TURNER	1500	SALES
ADAMS	1100	RESEARCH
JAMES	950	SALES
FORD	3000	RESEARCH
MILLER	1300	ACCOUNTING

（2）限定部门号为 10的员工

SELECT ename, sal, dname 
FROM EMP, DEPT 
WHERE EMP.deptno=DEPT.deptno 
AND DEPT.deptno=10;

查询结果：

ename	sal	dname
CLARK	2450	ACCOUNTING
KING	5000	ACCOUNTING
MILLER	1300	ACCOUNTING

2. 三表联查（含工资级别表）

SELECT ename, sal, grade 
FROM EMP, SALGRADE 
WHERE EMP.sal BETWEEN losal AND hisal;

查询结果：

ename	sal	grade
SMITH	800	1
ALLEN	1600	3
WARD	1250	2
JONES	2975	4
MARTIN	1250	2
BLAKE	2850	4
CLARK	2450	4
SCOTT	3000	4
KING	5000	5
TURNER	1500	3
ADAMS	1100	1
JAMES	950	1
FORD	3000	4
MILLER	1300	2

三、自连接：同一张表查上下级

-- 别名leader代表领导，worker代表员工
SELECT leader.empno, leader.ename 
FROM emp leader, emp worker 
WHERE leader.empno = worker.mgr 
AND worker.ename='FORD';

查询结果：

empno	ename
7566	JONES

四、子查询：用查询结果当条件 / 临时表

1. 单行子查询（返回一条结果）

SELECT ename, job 
FROM EMP 
WHERE sal = (SELECT MAX(sal) FROM EMP);

查询结果：

ename	job
KING	PRESIDENT

2. 多行子查询（返回多条结果）

（1）查 “和 10 号部门岗位相同、但不属于 10 号部门” 的员工

SELECT ename,job,sal,deptno 
FROM emp 
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10) 
AND deptno<>10;

查询结果：

ename	job	sal	deptno
JONES	MANAGER	2975	20
BLAKE	MANAGER	2850	30
SMITH	CLERK	800	20
ADAMS	CLERK	1100	20
JAMES	CLERK	950	30

（2）查 “工资比 30 号部门所有员工都高” 的员工

SELECT ename, sal, deptno 
FROM EMP 
WHERE sal > ALL(SELECT sal FROM EMP WHERE deptno=30);

查询结果：

ename	sal	deptno
JONES	2975	20
SCOTT	3000	20
KING	5000	10
FORD	3000	20

3. from 子句子查询（把子查询当临时表）

-- 先查各部门平均工资（临时表tmp），再关联员工表
SELECT ename, deptno, sal, FORMAT(tmp.avg_sal,2) AS dept_avg_sal
FROM EMP, 
(SELECT AVG(sal) avg_sal, deptno dt FROM EMP GROUP BY deptno) tmp
WHERE EMP.sal > tmp.avg_sal 
AND EMP.deptno=tmp.dt;

查询结果：

ename	deptno	sal	dept_avg_sal
KING	10	5000	2,916.67
JONES	20	2975	2,175.00
SCOTT	20	3000	2,175.00
FORD	20	3000	2,175.00
BLAKE	30	2850	1,566.67
ALLEN	30	1600	1,566.67

五、合并查询：union/union all

1. union（自动去重）

SELECT ename, sal, job FROM EMP WHERE sal>2500 
UNION
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';

查询结果（无重复数据）：

ename	sal	job
JONES	2975	MANAGER
BLAKE	2850	MANAGER
SCOTT	3000	ANALYST
KING	5000	PRESIDENT
FORD	3000	ANALYST
CLARK	2450	MANAGER

2. union all（保留重复）

SELECT ename, sal, job FROM EMP WHERE sal>2500 
UNION ALL
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';

查询结果（JONES、BLAKE 重复出现）：

ename	sal	job
JONES	2975	MANAGER
BLAKE	2850	MANAGER
SCOTT	3000	ANALYST
KING	5000	PRESIDENT
FORD	3000	ANALYST
JONES	2975	MANAGER
BLAKE	2850	MANAGER
CLARK	2450	MANAGER

写在最后

MySQL 复合查询是实际开发的核心技能，核心是理清表关系、灵活组合单表 / 多表 / 子查询语法。本文所有示例均基于真实员工管理表数据，查询结果可直接验证，建议你复制 SQL 语句在本地数据库中实操，更快掌握各类查询技巧～