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

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

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

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

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

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

1.1 多條件篩選查詢

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

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

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

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

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 自定義排序查詢

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

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

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

場景 2：按「月薪 ×12 + 獎(jiǎng)金」計(jì)算年薪排序（處理空值）

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：查詢每個(gè)部門的平均工資和最高工資

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ù)

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

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

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

需求 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)條件：員工表部門號(hào)=部門表部門號(hào)

需求 2：顯示 10 號(hào)部門的部門名、員工名和工資（關(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號(hào)部門

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

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

需求：顯示每個(gè)員工的姓名、工資、部門名稱及工資等級(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（工資在等級(jí)范圍內(nèi)）

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

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

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

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

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

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

4. 子查詢：嵌套查詢的靈活應(yīng)用

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

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

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

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

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

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

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

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

場景 1：用in查詢與 10 號(hào)部門崗位相同的員工（不含 10 號(hào)部門）

select ename, job, sal, deptno 
from emp 
where job in (select distinct job from emp where deptno = 10)  -- 子查詢：10號(hào)部門的所有崗位
  and deptno != 10;  -- 排除10號(hào)部門

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

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

4.3 子查詢嵌入 from 子句

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

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

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  -- 子查詢作為臨時(shí)表tmp
where e.deptno = tmp.deptno  -- 關(guān)聯(lián)員工表和臨時(shí)表
  and e.sal > tmp.部門平均工資;  -- 篩選高于平均工資的員工

5. 合并查詢：union 與 union all

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

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

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

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

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

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

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

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

6.1.1 內(nèi)連接語法

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

-- 格式1：顯式內(nèi)連接（推薦，明確標(biāo)注 inner join）
select 字段名 
from 表1 inner join 表2 
on 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段  -- 核心：表間關(guān)聯(lián)條件
and 其他篩選條件;  -- 可選：對(duì)結(jié)果進(jìn)一步篩選
-- 格式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)條件：員工部門號(hào)=部門表部門號(hào)
  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篩選目標(biāo)員工

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

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

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

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

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

左外連接的邏輯是：以 “左表” 為基準(zhǔn)，保留左表的所有記錄，右表只保留與左表匹配的記錄；若右表無匹配項(xiàng)，對(duì)應(yīng)字段顯示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（學(xué)生表）和exam（成績表），其中部分學(xué)生無成績，部分成績無對(duì)應(yīng)學(xué)生。

準(zhǔn)備測試數(shù)據(jù)

-- 1. 創(chuàng)建并插入學(xué)生表數(shù)據(jù)（左表，需保留全部學(xué)生）
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ù)（右表，部分成績無對(duì)應(yīng)學(xué)生）
create table exam (id int, grade int);
insert into exam values(1,56),(2,76),(11,8);  -- id=11的成績無對(duì)應(yīng)學(xué)生

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

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

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

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

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

右外連接的邏輯與左外連接相反：以 “右表” 為基準(zhǔn)，保留右表的所有記錄，左表只保留與右表匹配的記錄；若左表無匹配項(xiàng)，對(duì)應(yīng)字段顯示NULL。

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

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

右外連接案例

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

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

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

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

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

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

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

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

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

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

要不要我?guī)湍阊a(bǔ)充一份 MySQL 復(fù)合查詢核心語法對(duì)照表？包含本文所有場景的語法模板、關(guān)鍵字說明和注意事項(xiàng)，方便你日常開發(fā)時(shí)直接查閱。

到此這篇關(guān)于MySQL 復(fù)合查詢從單表到多表的實(shí)戰(zhàn)攻略的文章就介紹到這了,更多相關(guān)mysql復(fù)合查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

国产无遮挡裸体免费直播视频,久久精品国产蜜臀av,动漫在线视频一区二区,欧亚日韩一区二区三区,久艹在线免费视频,国产精品美女网站免费,正在播放 97超级视频在线观看,斗破苍穹年番在线观看免费,51最新乱码中文字幕

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

目錄

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

1.1 多條件篩選查詢

1.2 自定義排序查詢

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

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

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

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

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

4. 子查詢：嵌套查詢的靈活應(yīng)用

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

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

4.3 子查詢嵌入 from 子句

5. 合并查詢：union 與 union all

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

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

6.1.1 內(nèi)連接語法

6.1.2 內(nèi)連接案例

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

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

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

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

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

国产无遮挡裸体免费直播视频,久久精品国产蜜臀av,动漫在线视频一区二区,欧亚日韩一区二区三区,久艹在线 免费视频,国产精品美女网站免费,正在播放 97超级视频在线观看,斗破苍穹年番在线观看免费,51最新乱码中文字幕

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

目錄

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

1.1 多條件篩選查詢

1.2 自定義排序查詢

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

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

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

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

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

4. 子查詢：嵌套查詢的靈活應(yīng)用

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

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

4.3 子查詢嵌入 from 子句

5. 合并查詢：union 與 union all

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

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

6.1.1 內(nèi)連接語法

6.1.2 內(nèi)連接案例

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

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

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

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

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具

国产无遮挡裸体免费直播视频,久久精品国产蜜臀av,动漫在线视频一区二区,欧亚日韩一区二区三区,久艹在线免费视频,国产精品美女网站免费,正在播放 97超级视频在线观看,斗破苍穹年番在线观看免费,51最新乱码中文字幕