MySQL 復合查詢核心指南之多表、子查詢與實戰(zhàn)技巧

更新時間：2026年03月27日 10:56:44 作者：草莓熊Lotso

本文全面拆解 MySQL 復合查詢的核心玩法,包括多表查詢、自連接、子查詢、合并查詢,所有 SQL 均采用小寫形式,貼合開發(fā)規(guī)范,附帶實戰(zhàn)案例和避坑要點,感興趣的朋友跟隨小編一起看看吧

前言：

在實際開發(fā)中，單表查詢遠不能滿足復雜業(yè)務需求 —— 員工信息散落在員工表、部門表、薪資等級表中，需要跨表關聯(lián)才能獲取完整數(shù)據；統(tǒng)計分析時需嵌套查詢篩選條件；多結果集合并需用到集合操作符。本文全面拆解 MySQL 復合查詢的核心玩法，包括多表查詢、自連接、子查詢、合并查詢，所有 SQL 均采用小寫形式，貼合開發(fā)規(guī)范，附帶實戰(zhàn)案例和避坑要點

一. 基礎回顧：復合查詢的前置知識

在學習復雜復合查詢前，先回顧基礎查詢的核心語法，為后續(xù)進階打基礎：

-- 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位小數(shù)）和最高工資
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;

二. 多表查詢：跨表關聯(lián)核心玩法

多表查詢是復合查詢的基礎，用于從多個關聯(lián)表中提取數(shù)據，核心是通過 “關聯(lián)字段” 消除笛卡爾積（無關聯(lián)條件時，表 1 所有行與表 2 所有行組合，數(shù)據量爆炸）。

2.1 測試表結構

本次實戰(zhàn)基于 3 張經典表，先明確表結構和關聯(lián)關系：

emp（員工表）：存儲員工基本信息，關聯(lián)字段deptno（關聯(lián)部門表）、sal（關聯(lián)薪資等級表）；
dept（部門表）：存儲部門信息，關聯(lián)字段deptno；
salgrade（薪資等級表）：存儲薪資等級規(guī)則，關聯(lián)字段losal（最低工資）、hisal（最高工資）。

2.2 多表查詢核心語法

select 表1.字段, 表2.字段 
from 表1, 表2 
where 表1.關聯(lián)字段 = 表2.關聯(lián)字段 [and 其他篩選條件];

2.3 實戰(zhàn)案例

2.3.1 關聯(lián)兩張表：員工 + 部門信息

需求：查詢員工姓名、工資及所在部門名稱

-- 核心：通過deptno關聯(lián)emp和dept表，消除笛卡爾積
select emp.ename, emp.sal, dept.dname 
from emp, dept 
where emp.deptno = dept.deptno;

2.3.2 多表 + 條件篩選：指定部門員工信息

需求：查詢 10 號部門的員工姓名、工資及部門名稱

select emp.ename, emp.sal, dept.dname 
from emp, dept 
where emp.deptno = dept.deptno 
  and dept.deptno = 10; -- 篩選10號部門

2.3.3 關聯(lián)多張表：員工 + 薪資等級

需求：查詢員工姓名、工資及對應的薪資等級

select emp.ename, emp.sal, salgrade.grade 
from emp, salgrade 
where emp.sal between salgrade.losal and salgrade.hisal; -- 工資在薪資等級區(qū)間內

2.4 多表查詢避坑點

必須加關聯(lián)條件：無關聯(lián)條件會產生笛卡爾積（如 emp 有 14 行、dept 有 4 行，會產生 14×4=56 行無效數(shù)據）；
字段歧義需加表名前綴：若多個表有同名字段（如deptno），需用表名.字段區(qū)分；
關聯(lián)字段類型必須一致：emp.deptno 和 dept.deptno 需同為 int 類型，否則關聯(lián)失效。

三. 自連接：同表關聯(lián)查詢

自連接是多表查詢的特殊形式 —— 將同一張表當作兩張表使用，通過別名區(qū)分，適用于查詢表內關聯(lián)數(shù)據（如員工與上級領導的關系）。

3.1 核心語法

select 表別名1.字段, 表別名2.字段 
from 表 表別名1, 表 表別名2 
where 表別名1.關聯(lián)字段 = 表別名2.關聯(lián)字段 [and 篩選條件];

3.2 實戰(zhàn)案例：查詢員工的上級領導

需求：查詢員工 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

3.3 自連接關鍵技巧

必須給表起不同別名（如leader、worker），否則 MySQL 無法區(qū)分兩張 “虛擬表”；
關聯(lián)字段需是表內的關聯(lián)關系（如員工表的mgr與自身的empno）。

四. 子查詢：嵌套查詢的靈活用法

子查詢（嵌套查詢）是指嵌入在其他 SQL 語句中的 select 語句，按返回結果可分為單行、多行、多列子查詢，按位置可分為 where 子句、from 子句中的子查詢。

4.1 單行子查詢：返回 1 行 1 列結果

適用于篩選條件為 “等于、大于、小于” 單個值的場景，常用比較運算符（=、>、<、>=、<=）。

實戰(zhàn)案例：
需求：查詢與 smith 同部門的所有員工（不含 smith）

select * from emp 
where deptno = (select deptno from emp where ename='smith') -- 子查詢返回smith的部門號
  and ename != 'smith'; -- 排除smith本人

4.2 多行子查詢：返回多行 1 列結果

適用于篩選條件為 “在多個值中”“大于所有值”“大于任意值” 的場景，常用關鍵字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 deptno=10) -- 子查詢返回10號部門的所有崗位
  and deptno != 10; -- 排除10號部門

4.2.2 all 關鍵字：大于 / 小于所有值

需求：查詢工資比 30 號部門所有員工工資都高的員工

select ename, sal, deptno from emp 
where sal > all(select sal from emp where deptno=30); -- 工資>30號部門所有員工工資

4.2.3 any 關鍵字：大于 / 小于任意一個值

需求：查詢工資比 30 號部門任意員工工資高的員工（含自身部門）

select ename, sal, deptno from emp 
where sal > any(select sal from emp where deptno=30); -- 工資>30號部門至少一個員工工資

4.3 多列子查詢：返回多行多列結果

適用于篩選條件需匹配 “多個字段組合” 的場景，子查詢返回多列，主查詢用括號接收字段組合。

實戰(zhàn)案例：
需求：查詢與 smith 部門和崗位完全相同的員工（不含 smith）

select ename from emp 
where (deptno, job) = (select deptno, job from emp where ename='smith') -- 匹配部門+崗位組合
  and ename != 'smith';

4.4 from 子句中的子查詢：臨時表用法

將子查詢結果當作 “臨時表”，用于復雜統(tǒng)計分析（如先聚合再關聯(lián)），核心是給臨時表起別名。

實戰(zhàn)案例 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; -- 員工工資>部門平均工資

實戰(zhàn)案例 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;

4.5 子查詢避坑指南

單行子查詢只能用單行運算符：若子查詢返回多行，不能用=，需用in；
from 子句的子查詢必須起別名：MySQL 要求臨時表必須有別名，否則報錯；
子查詢盡量簡化：復雜子查詢可拆分為臨時表或多步查詢，提升可讀性和性能。

五. 合并查詢：union 與 union all

合并查詢用于將多個 select 語句的結果集合并為一個，適用于多條件獨立查詢后合并結果的場景，核心是union（去重）和union all（不去重）。

5.1 核心語法

-- 去重合并（自動刪除重復行）
select 字段 from 表1 where 條件
union
select 字段 from 表2 where 條件;
-- 不去重合并（保留重復行，性能更優(yōu)）
select 字段 from 表1 where 條件
union all
select 字段 from 表2 where 條件;

5.2 實戰(zhàn)案例

案例 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';

六. 實戰(zhàn) OJ 真題：復合查詢落地應用

結合牛客網經典 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'; -- 替換為你的數(shù)據庫名

真題 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 復合查詢是解決復雜業(yè)務需求的核心，核心要點總結：

多表查詢：通過關聯(lián)字段消除笛卡爾積，適用于跨表提取數(shù)據；
自連接：同表當作兩張表，適用于表內關聯(lián)（如員工與領導）；
子查詢：嵌套在 where/from 子句中，靈活篩選和統(tǒng)計，需注意單行 / 多行匹配規(guī)則；
合并查詢：union（去重）和 union all（不去重），適用于多結果集合并；
避坑關鍵：關聯(lián)字段一致、臨時表起別名、優(yōu)先選擇高效語法（如 union all 替代 union）。

到此這篇關于MySQL 復合查詢核心指南之多表、子查詢與實戰(zhàn)技巧的文章就介紹到這了,更多相關mysql多表、子查詢內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

MySQL 刪除大表的性能問題解決方案
MySQL在刪除大表engine=innodb（30G+）時，如何減少MySQL hang的時間,本為將提供詳細的解決方案,需要了解的朋友可以參考下
2012-11-11
利用mycat實現(xiàn)mysql數(shù)據庫讀寫分離的示例
本篇文章主要介紹了利用mycat實現(xiàn)mysql數(shù)據庫讀寫分離的示例，mycat是最近很火的一款國人發(fā)明的分布式數(shù)據庫中間件，它是基于阿里的cobar的基礎上進行開發(fā)的，有一定的參考價值，感興趣的小伙伴們可以參考一下
2018-03-03
MySQL 雙機互備的項目實踐
MySQL雙機互備實現(xiàn)主主復制的高可用架構,通過配置兩臺服務器互為主從,實現(xiàn)數(shù)據實時同步和服務冗余,下面就來詳細的介紹一下,感興趣的可以了解一下
2026-03-03
聽說mysql中的join很慢?是你用的姿勢不對吧
這篇文章主要介紹了聽說mysql中的join很慢？是你用的姿勢不對吧，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2022-09-09
MySQL數(shù)據庫誤刪回滾的解決
本文主要介紹了MySQL數(shù)據庫誤刪回滾的解決，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2022-06-06
MySQL外鍵約束的實例講解
這篇文章主要介紹了MySQL外鍵約束的實例講解，幫助大家更好的重溫MySQL 外鍵約束的相關知識，感興趣的朋友可以了解下
2020-11-11
MySQL讀寫分離服務配置方式
通過Mycat代理實現(xiàn)MySQL的讀寫分離涉及準備工作、配置文件修改、權限設置、啟動方式選擇等關鍵步驟,首先,安裝JDK1.8并配置環(huán)境變量；接著,對Mycat的server.xml和schema.xml進行配置,特別是schema.xml中對數(shù)據庫的配置需關注
2024-11-11
mysql服務啟動不了解決方案
最近在Windows 2003上的MySQL出現(xiàn)過多次正常運行時無法連接數(shù)據庫故障，現(xiàn)象是無法連接數(shù)據庫，也無法停止MySQL或重啟MYSQL,由于每次都是草草嘗試各種方法搞定即可本文將詳細介紹解決方法
2012-11-11
一篇文章學會SQL中的遞歸用法(Mysql)
這篇文章主要給大家介紹了關于如何一篇文章學會SQL中的遞歸用法,眾所周知目前的mysql版本中并不支持直接的遞歸查詢,但是通過遞歸到迭代轉化的思路,還是可以在一句SQL內實現(xiàn)樹的遞歸查詢的,需要的朋友可以參考下
2023-10-10
MySQL默認值選型問題(是空,還是?NULL)
這篇文章主要介紹了MySQL默認值選型問題(是空,還是?NULL),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2023-10-10