首页 › MySQL论坛 › MySQL中的复合查询使用解读

山水之乐 發表於 2025-12-26 08:41:04

MySQL中的复合查询使用解读

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、先回顾：单表查询的基础操作（以EMP表为例）</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 带条件的筛选</a></li><li><a href="#_lab2_0_1">2. 排序与计算字段</a></li><ul class="third_class_ul"><li><a href="#_label3_0_1_0">（1）按部门号升序、工资降序排列</a></li><li><a href="#_label3_0_1_1">（2）计算年薪（工资 &times;12 + 奖金，奖金为空则按 0 算）并排序</a></li></ul><li><a href="#_lab2_0_2">3. 聚合与分组查询</a></li><ul class="third_class_ul"><li><a href="#_label3_0_2_2">（1）查每个部门的平均工资、最高工资</a></li><li><a href="#_label3_0_2_3">（2）筛选平均工资 &lt; 2000的部门</a></li></ul></ul><li><a href="#_label1">二、多表查询：跨表关联数据</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_3">DEPT部门表</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_1_4">SALGRADE工资级别表</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_1_5">1. 基础多表联查（等值连接）</a></li><ul class="third_class_ul"><li><a href="#_label3_1_5_4">（1）查 &ldquo;员工名、工资、所属部门名&rdquo;</a></li><li><a href="#_label3_1_5_5">（2）限定部门号为 10的员工</a></li></ul><li><a href="#_lab2_1_6">2. 三表联查（含工资级别表）</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label2">三、自连接：同一张表查上下级</a></li><ul class="second_class_ul"></ul><li><a href="#_label3">四、子查询：用查询结果当条件 / 临时表</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_7">1. 单行子查询（返回一条结果）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_8">2. 多行子查询（返回多条结果）</a></li><ul class="third_class_ul"><li><a href="#_label3_3_8_6">（1）查 &ldquo;和 10 号部门岗位相同、但不属于 10 号部门&rdquo; 的员工</a></li><li><a href="#_label3_3_8_7">（2）查 &ldquo;工资比 30 号部门所有员工都高&rdquo; 的员工</a></li></ul><li><a href="#_lab2_3_9">3. from 子句子查询（把子查询当临时表）</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">五、合并查询：union/union all</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_10">1. union（自动去重）</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_11">2. union all（保留重复）</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label5">总结</a></li><ul class="second_class_ul"></ul></ul></div><p>在实际开发中，仅用单表查询显然无法满足复杂业务需求。今天我们就以经典的员工管理系统（<code>EMP</code>员工表、<code>DEPT</code>部门表、<code>SALGRADE</code>工资级别表）为例，聊聊 MySQL 复合查询的核心玩法 &mdash;&mdash; 从单表查询到多表联查、子查询，再到合并查询，每一步都附上真实查询结果，帮你直观理解。</p>
<p class="maodian"><a name="_label0"></a></p><h2>一、先回顾：单表查询的基础操作（以EMP表为例）</h2>
<p>先明确<code>EMP</code>表基础数据（对应图片中表内容）：</p>
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
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 带条件的筛选</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM EMP
WHERE (sal&gt;500 OR job='MANAGER')
AND ename LIKE 'J%';
</pre></div>
<p><strong>查询结果</strong>：</p>
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
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 排序与计算字段</h3>
<p class="maodian"><a name="_label3_0_1_0"></a></p><h4>（1）按部门号升序、工资降序排列</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM EMP
ORDER BY deptno, sal DESC;
</pre></div>
<p><strong>查询结果</strong>（节选核心字段）：</p>
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
<p class="maodian"><a name="_label3_0_1_1"></a></p><h4>（2）计算年薪（工资 &times;12 + 奖金，奖金为空则按 0 算）并排序</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, sal*12+IFNULL(comm,0) AS '年薪'
FROM EMP
ORDER BY 年薪 DESC;
</pre></div>
<p><strong>查询结果</strong>：</p>
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
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. 聚合与分组查询</h3>
<p class="maodian"><a name="_label3_0_2_2"></a></p><h4>（1）查每个部门的平均工资、最高工资</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT deptno, FORMAT(AVG(sal),2) AS avg_sal, MAX(sal) AS max_sal
FROM EMP
GROUP BY deptno;
</pre></div>
<p><strong>查询结果</strong>：</p>
deptno	avg_sal	max_sal
10	2,916.67	5000
20	2,175.00	3000
30	1,566.67	2850
<p class="maodian"><a name="_label3_0_2_3"></a></p><h4>（2）筛选平均工资 &lt; 2000的部门</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT deptno, AVG(sal) AS avg_sal
FROM EMP
GROUP BY deptno
HAVING avg_sal&lt;2000;
</pre></div>
<p><strong>查询结果</strong>：</p>
deptno	avg_sal
30	1566.6667
<p class="maodian"><a name="_label1"></a></p><h2>二、多表查询：跨表关联数据</h2>
<p>先明确关联表基础数据：</p>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>DEPT部门表</h3>
deptno	dname	loc
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>SALGRADE工资级别表</h3>
grade	losal	hisal
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999
<p class="maodian"><a name="_lab2_1_5"></a></p><h3>1. 基础多表联查（等值连接）</h3>
<p class="maodian"><a name="_label3_1_5_4"></a></p><h4>（1）查 &ldquo;员工名、工资、所属部门名&rdquo;</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT EMP.ename, EMP.sal, DEPT.dname
FROM EMP, DEPT
WHERE EMP.deptno = DEPT.deptno;
</pre></div>
<p><strong>查询结果</strong>（节选）：</p>
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
<p class="maodian"><a name="_label3_1_5_5"></a></p><h4>（2）限定部门号为 10的员工</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, sal, dname
FROM EMP, DEPT
WHERE EMP.deptno=DEPT.deptno
AND DEPT.deptno=10;
</pre></div>
<p><strong>查询结果</strong>：</p>
ename	sal	dname
CLARK	2450	ACCOUNTING
KING	5000	ACCOUNTING
MILLER	1300	ACCOUNTING
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>2. 三表联查（含工资级别表）</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, sal, grade
FROM EMP, SALGRADE
WHERE EMP.sal BETWEEN losal AND hisal;
</pre></div>
<p><strong>查询结果</strong>：</p>
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
<p class="maodian"><a name="_label2"></a></p><h2>三、自连接：同一张表查上下级</h2>
<div class="jb51code"><pre class="brush:sql;">-- 别名leader代表领导，worker代表员工
SELECT leader.empno, leader.ename
FROM emp leader, emp worker
WHERE leader.empno = worker.mgr
AND worker.ename='FORD';
</pre></div>
<p><strong>查询结果</strong>：</p>
empno	ename
7566	JONES
<p class="maodian"><a name="_label3"></a></p><h2>四、子查询：用查询结果当条件 / 临时表</h2>
<p class="maodian"><a name="_lab2_3_7"></a></p><h3>1. 单行子查询（返回一条结果）</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, job
FROM EMP
WHERE sal = (SELECT MAX(sal) FROM EMP);
</pre></div>
<p><strong>查询结果</strong>：</p>
ename	job
KING	PRESIDENT
<p class="maodian"><a name="_lab2_3_8"></a></p><h3>2. 多行子查询（返回多条结果）</h3>
<p class="maodian"><a name="_label3_3_8_6"></a></p><h4>（1）查 &ldquo;和 10 号部门岗位相同、但不属于 10 号部门&rdquo; 的员工</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT ename,job,sal,deptno
FROM emp
WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10)
AND deptno&lt;&gt;10;
</pre></div>
<p><strong>查询结果</strong>：</p>
ename	job	sal	deptno
JONES	MANAGER	2975	20
BLAKE	MANAGER	2850	30
SMITH	CLERK	800	20
ADAMS	CLERK	1100	20
JAMES	CLERK	950	30
<p class="maodian"><a name="_label3_3_8_7"></a></p><h4>（2）查 &ldquo;工资比 30 号部门所有员工都高&rdquo; 的员工</h4>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, sal, deptno
FROM EMP
WHERE sal &gt; ALL(SELECT sal FROM EMP WHERE deptno=30);
</pre></div>
<p><strong>查询结果</strong>：</p>
ename	sal	deptno
JONES	2975	20
SCOTT	3000	20
KING	5000	10
FORD	3000	20
<p class="maodian"><a name="_lab2_3_9"></a></p><h3>3. from 子句子查询（把子查询当临时表）</h3>
<div class="jb51code"><pre class="brush:sql;">-- 先查各部门平均工资（临时表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 &gt; tmp.avg_sal
AND EMP.deptno=tmp.dt;
</pre></div>
<p><strong>查询结果</strong>：</p>
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
<p class="maodian"><a name="_label4"></a></p><h2>五、合并查询：union/union all</h2>
<p class="maodian"><a name="_lab2_4_10"></a></p><h3>1. union（自动去重）</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, sal, job FROM EMP WHERE sal&gt;2500
UNION
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';
</pre></div>
<p><strong>查询结果</strong>（无重复数据）：</p>
ename	sal	job
JONES	2975	MANAGER
BLAKE	2850	MANAGER
SCOTT	3000	ANALYST
KING	5000	PRESIDENT
FORD	3000	ANALYST
CLARK	2450	MANAGER
<p class="maodian"><a name="_lab2_4_11"></a></p><h3>2. union all（保留重复）</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT ename, sal, job FROM EMP WHERE sal&gt;2500
UNION ALL
SELECT ename, sal, job FROM EMP WHERE job='MANAGER';
</pre></div>
<p><strong>查询结果</strong>（JONES、BLAKE 重复出现）：</p>
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
<p class="maodian"><a name="_label5"></a></p><h2>总结</h2>
<p>MySQL 复合查询是实际开发的核心技能，核心是<strong>理清表关系、灵活组合单表 / 多表 / 子查询语法</strong>。</p>
<p>本文所有示例均基于真实员工管理表数据，查询结果可直接验证，建议你复制 SQL 语句在本地数据库中实操，更快掌握各类查询技巧～</p>
<p>这些仅为个人经验，希望能给大家一个参考，也希望大家多多支持琼殿技术社区。</p>

查看完整版本: MySQL中的复合查询使用解读