MySQL 復合查詢從單表到多表的實戰(zhàn)攻略

更新時間：2025年10月31日 10:01:29 作者：藤椒味的火腿腸真不錯

本文將從單表查詢回顧入手,逐步深入多表查詢、自連接、子查詢等復合場景,結(jié)合真實案例拆解用法,幫你掌握企業(yè)級查詢技巧,感興趣的朋友跟隨小編一起看看吧

在 MySQL 日常使用中，單表查詢僅能滿足基礎(chǔ)數(shù)據(jù)需求，而實際開發(fā)中，數(shù)據(jù)往往分散在多張表中，且需要復雜的條件篩選與統(tǒng)計。本文將從單表查詢回顧入手，逐步深入多表查詢、自連接、子查詢等復合場景，結(jié)合真實案例拆解用法，幫你掌握企業(yè)級查詢技巧。

1. 單表查詢回顧：夯實基礎(chǔ)操作

單表查詢是復合查詢的基石，核心圍繞「篩選條件」「排序規(guī)則」「聚合統(tǒng)計」三大維度展開，以下通過經(jīng)典案例復習關(guān)鍵用法。

1.1 多條件篩選查詢

需求：查詢工資高于 3000 或崗位為「ANALYST」的雇員，且姓名首字母為大寫「S」。實現(xiàn)方式有兩種：通配符匹配或字符串截取函數(shù)，結(jié)果一致但適用場景不同。

方式 1：使用like通配符（更簡潔，適合模糊匹配場景）

select * from emp 
where (sal > 3000 or job = 'ANALYST') 
  and ename like 'S%';

方式 2：使用substring函數(shù)（更精準，適合固定位置匹配場景）

select * from emp 
where (sal > 3000 or job = 'ANALYST') 
  and substring(ename, 1, 1) = 'S';

查詢結(jié)果（示例）：

+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)

1.2 自定義排序查詢

排序不僅支持表中原有字段，還能基于「計算字段」排序，比如按「年薪」排序。需注意：獎金comm可能為NULL，需用ifnull函數(shù)處理空值，避免計算結(jié)果異常。

場景 1：按「月薪 ×12」計算年薪排序

select ename, sal*12 as 年薪 
from emp 
order by 年薪 desc;

場景 2：按「月薪 ×12 + 獎金」計算年薪排序（處理空值）

select ename, sal*12 + ifnull(comm, 0) as 年薪 
from emp 
order by 年薪 desc;

1.3 聚合與篩選結(jié)合查詢

聚合查詢（avg/max/count等）需搭配group by分組，若需過濾聚合結(jié)果，需用having（區(qū)別于where過濾行數(shù)據(jù)）。

場景 1：查詢每個部門的平均工資和最高工資

select deptno, avg(sal) as 平均工資, max(sal) as 最高工資 
from emp 
group by deptno;

場景 2：查詢平均工資低于 2500 的部門（聚合后篩選）

select deptno, avg(sal) as 平均工資 
from emp 
group by deptno 
having 平均工資 < 2500;

查詢結(jié)果（示例）：

+--------+-------------+
| deptno | 平均工資    |
+--------+-------------+
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
2 rows in set (0.00 sec)

2. 多表查詢：關(guān)聯(lián)多張表取數(shù)

實際開發(fā)中，數(shù)據(jù)常分散在多張表（如員工表emp、部門表dept、工資等級表salgrade），需通過「關(guān)聯(lián)字段」（如deptno）將表連接，獲取完整信息。

2.1 兩表關(guān)聯(lián)查詢

核心邏輯：找到兩張表的共同字段（關(guān)聯(lián)字段），用where指定關(guān)聯(lián)條件，避免笛卡爾積（數(shù)據(jù)重復）。

需求 1：顯示雇員名、工資及所在部門名稱（關(guān)聯(lián)emp和dept）

select e.ename, e.sal, d.dname 
from emp e, dept d  -- 給表起別名，簡化代碼
where e.deptno = d.deptno;  -- 關(guān)聯(lián)條件：員工表部門號=部門表部門號

需求 2：顯示 10 號部門的部門名、員工名和工資（關(guān)聯(lián) + 篩選）

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

2.2 三表關(guān)聯(lián)查詢

當需要從三張表取數(shù)時，需依次指定表間關(guān)聯(lián)條件，確保數(shù)據(jù)邏輯正確。

需求：顯示每個員工的姓名、工資、部門名稱及工資等級（關(guān)聯(lián)emp/dept/salgrade）

select e.ename, e.sal, d.dname, s.grade 
from emp e, dept d, salgrade s 
where e.deptno = d.deptno  -- 關(guān)聯(lián)emp和dept
  and e.sal between s.losal and s.hisal;  -- 關(guān)聯(lián)emp和salgrade（工資在等級范圍內(nèi)）

3. 自連接：同一張表的 “自我關(guān)聯(lián)”

自連接是特殊的多表查詢，指同一張表通過別名視為兩張表，解決 “表內(nèi)數(shù)據(jù)關(guān)聯(lián)” 場景（如查詢員工的上級領(lǐng)導）。

需求：顯示員工「FORD」的上級領(lǐng)導編號和姓名（emp表中mgr字段是領(lǐng)導的empno）

select leader.empno as 領(lǐng)導編號, leader.ename as 領(lǐng)導姓名 
from emp emp, emp leader  -- 同一張表起兩個別名：員工表(emp)、領(lǐng)導表(leader)
where emp.ename = 'FORD'  -- 篩選員工FORD
  and emp.mgr = leader.empno;  -- 關(guān)聯(lián)條件：員工的領(lǐng)導編號=領(lǐng)導的員工編號

查詢結(jié)果（示例）：

+--------+-----------+
| 領(lǐng)導編號 | 領(lǐng)導姓名  |
+--------+-----------+
|  007566 | JONES     |
+--------+-----------+
1 row in set (0.00 sec)

4. 子查詢：嵌套查詢的靈活應用

子查詢（嵌套查詢）指將一個select語句嵌入到另一個 SQL 語句中，按返回結(jié)果行數(shù)可分為「單行子查詢」「多行子查詢」，按位置可嵌入where或from子句。

4.1 單行子查詢（返回 1 行結(jié)果）

適用于 “基于單個值篩選” 的場景，常用=匹配子查詢結(jié)果。

需求：查詢與「SMITH」同一部門的所有員工（不含 SMITH）

select * from emp 
where deptno = (select deptno from emp where ename = 'SMITH')  -- 子查詢：獲取SMITH的部門號
  and ename != 'SMITH';  -- 排除SMITH本人

4.2 多行子查詢（返回多行結(jié)果）

適用于 “基于多個值篩選” 的場景，需搭配in/all/any等關(guān)鍵字。

關(guān)鍵字	作用說明
`in`	匹配子查詢結(jié)果中的任意一個值
`all`	匹配所有子查詢結(jié)果（如`> all`表示大于所有值）
`any`	匹配任意一個子查詢結(jié)果（如`> any`表示大于其中一個值）

場景 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號部門

場景 2：用all查詢工資高于 30 號部門所有員工的員工

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

4.3 子查詢嵌入 from 子句

將子查詢結(jié)果視為「臨時表」，用于復雜統(tǒng)計（如查詢 “高于部門平均工資的員工”）。

需求：顯示每個高于自己部門平均工資的員工姓名、部門、工資及部門平均工資

select e.ename, e.deptno, e.sal, format(tmp.部門平均工資, 2)  -- format格式化小數(shù)
from emp e, 
     (select deptno, avg(sal) as 部門平均工資 from emp group by deptno) tmp  -- 子查詢作為臨時表tmp
where e.deptno = tmp.deptno  -- 關(guān)聯(lián)員工表和臨時表
  and e.sal > tmp.部門平均工資;  -- 篩選高于平均工資的員工

5. 合并查詢：union 與 union all

當需要合并多個select的結(jié)果集時，可使用union或union all，兩者核心區(qū)別是是否去重。

操作符	去重情況	性能	適用場景
`union`	自動去重	較低（需比對去重）	需避免結(jié)果重復
`union all`	不去重	較高（直接合并）	結(jié)果無重復或允許重復

需求：查詢工資高于 4000 或崗位為「PRESIDENT」的員工

-- union去重（若有重復數(shù)據(jù)會自動剔除）
select * from emp where sal > 4000 
union 
select * from emp where job = 'PRESIDENT';
-- union all不去重（性能更優(yōu)，適合確認無重復的場景）
select * from emp where sal > 4000 
union all 
select * from emp where job = 'PRESIDENT';

5. 表的連接：內(nèi)連接與外連接詳解

在多表查詢中，表的連接方式直接決定了數(shù)據(jù)的查詢范圍和結(jié)果形態(tài)。常用的連接方式分為內(nèi)連接和外連接，外連接又可細分為左外連接與右外連接。本節(jié)將結(jié)合實例，拆解不同連接方式的語法、邏輯及適用場景。

6.1 內(nèi)連接：只保留匹配的記錄

內(nèi)連接是最常用的連接方式，核心邏輯是只保留兩張表中 “關(guān)聯(lián)條件匹配” 的記錄，不匹配的記錄會被過濾掉。本質(zhì)上，它等同于用where子句篩選兩張表的笛卡爾積，我們之前學習的多表查詢都屬于內(nèi)連接。

6.1.1 內(nèi)連接語法

內(nèi)連接支持兩種語法格式，核心都是通過on指定關(guān)聯(lián)條件（推薦用on，邏輯更清晰）：

-- 格式1：顯式內(nèi)連接（推薦，明確標注 inner join）
select 字段名 
from 表1 inner join 表2 
on 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段  -- 核心：表間關(guān)聯(lián)條件
and 其他篩選條件;  -- 可選：對結(jié)果進一步篩選
-- 格式2：隱式內(nèi)連接（即之前的多表查詢寫法）
select 字段名 
from 表1, 表2 
where 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段  -- 用where代替on指定關(guān)聯(lián)條件
and 其他篩選條件;

6.1.2 內(nèi)連接案例

需求：顯示員工「SMITH」的姓名和所在部門名稱（關(guān)聯(lián)emp和dept表）。

方式 1：隱式內(nèi)連接（笛卡爾積 + where 篩選）

select e.ename, d.dname 
from emp e, dept d 
where e.deptno = d.deptno  -- 關(guān)聯(lián)條件：員工部門號=部門表部門號
  and e.ename = 'SMITH';  -- 篩選條件：員工姓名為SMITH

方式 2：顯式內(nèi)連接（inner join + on）

-- 寫法1：篩選條件放在on后
select e.ename, d.dname 
from emp e inner join dept d 
on e.deptno = d.deptno  -- 關(guān)聯(lián)條件
and e.ename = 'SMITH';  -- 篩選條件
-- 寫法2：篩選條件放在where后（更易理解，先關(guān)聯(lián)表再篩選）
select e.ename, d.dname 
from emp e inner join dept d 
on e.deptno = d.deptno  -- 先通過on完成表關(guān)聯(lián)
where e.ename = 'SMITH';  -- 再通過where篩選目標員工

三種寫法的查詢結(jié)果一致：

+-------+----------+
| ename | dname    |
+-------+----------+
| SMITH | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

6.2 外連接：保留某一張表的全部記錄

外連接與內(nèi)連接的核心區(qū)別是：會保留其中一張表的 “全部記錄”，即使這些記錄在另一張表中沒有匹配項（無匹配的字段會顯示NULL）。根據(jù) “保留哪張表”，外連接分為左外連接和右外連接。

6.2.1 左外連接：保留左表全部記錄

左外連接的邏輯是：以 “左表” 為基準，保留左表的所有記錄，右表只保留與左表匹配的記錄；若右表無匹配項，對應字段顯示NULL。

select 字段名 
from 左表 left join 右表 
on 左表.關(guān)聯(lián)字段 = 右表.關(guān)聯(lián)字段;  -- 關(guān)聯(lián)條件（與內(nèi)連接一致）

注：left join可省略outer（即left outer join），效果相同。

左外連接案例

為了更直觀展示 “保留左表全部記錄”，先創(chuàng)建兩張測試表：stu（學生表）和exam（成績表），其中部分學生無成績，部分成績無對應學生。

準備測試數(shù)據(jù)

-- 1. 創(chuàng)建并插入學生表數(shù)據(jù)（左表，需保留全部學生）
create table stu (id int, name varchar(30));
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
-- 2. 創(chuàng)建并插入成績表數(shù)據(jù)（右表，部分成績無對應學生）
create table exam (id int, grade int);
insert into exam values(1,56),(2,76),(11,8);  -- id=11的成績無對應學生

需求：查詢所有學生的成績，即使學生沒有成績也要顯示其個人信息

select s.id as 學生ID, s.name as 學生姓名, e.grade as 成績 
from stu s left join exam e 
on s.id = e.id;  -- 關(guān)聯(lián)條件：學生ID=成績表ID

查詢結(jié)果（關(guān)鍵：學生 kity、nono 無成績，成績字段顯示 NULL，但仍保留記錄）：

+--------+----------+--------+
| 學生ID | 學生姓名 | 成績   |
+--------+----------+--------+
|      1 | jack     |     56 |
|      2 | tom      |     76 |
|      3 | kity     |   NULL |  -- 無成績，顯示NULL
|      4 | nono     |   NULL |  -- 無成績，顯示NULL
+--------+----------+--------+
4 rows in set (0.00 sec)

6.2.2 右外連接：保留右表全部記錄

右外連接的邏輯與左外連接相反：以 “右表” 為基準，保留右表的所有記錄，左表只保留與右表匹配的記錄；若左表無匹配項，對應字段顯示NULL。

select 字段名 
from 左表 right join 右表 
on 左表.關(guān)聯(lián)字段 = 右表.關(guān)聯(lián)字段;  -- 關(guān)聯(lián)條件

注：right join可省略outer（即right outer join），效果相同。

右外連接案例

需求：查詢所有成績記錄，即使成績沒有對應學生也要顯示成績信息（以exam表為右表，保留全部成績）。

方式 1：直接使用右外連接

select s.id as 學生ID, s.name as 學生姓名, e.grade as 成績 
from stu s right join exam e 
on s.id = e.id;  -- 關(guān)聯(lián)條件：學生ID=成績表ID

方式 2：等價于 “左表與右表互換的左外連接”右外連接可通過調(diào)換表的順序，用左外連接實現(xiàn)（更符合直覺，推薦）：

select s.id as 學生ID, s.name as 學生姓名, e.grade as 成績 
from exam e left join stu s  -- 成績表作為左表，保留全部成績
on e.id = s.id;  -- 關(guān)聯(lián)條件不變

兩種寫法的查詢結(jié)果一致（關(guān)鍵：id=11 的成績無對應學生，學生信息顯示 NULL，但成績記錄保留）：

+--------+----------+--------+
| 學生ID | 學生姓名 | 成績   |
+--------+----------+--------+
|      1 | jack     |     56 |
|      2 | tom      |     76 |
|   NULL | NULL     |     8  |  -- 無對應學生，顯示NULL
+--------+----------+--------+
3 rows in set (0.00 sec)

6.2.3 內(nèi)連接與外連接的核心區(qū)別

為了更清晰區(qū)分，用表格對比三種連接方式的邏輯差異（以stu左表、exam右表為例）：

連接方式	保留的記錄范圍	無匹配項的處理	適用場景
內(nèi)連接	只保留兩表匹配的記錄	不保留無匹配的記錄	需獲取 “雙方都有數(shù)據(jù)” 的結(jié)果（如：有成績的學生）
左外連接	保留左表全部記錄，右表匹配記錄	右表無匹配項顯示 NULL	需 “以左表為基準”（如：所有學生的成績，含無成績的）
右外連接	保留右表全部記錄，左表匹配記錄	左表無匹配項顯示 NULL	需 “以右表為基準”（如：所有成績