MySQL數據庫中復合查詢的操作詳解
在 MySQL 日常開發(fā)里，單表查詢只能處理最簡單的數據需求，真正的業(yè)務場景幾乎都要用到復合查詢—— 也就是多表關聯(lián)、嵌套查詢、自連接、結果合并這類高級查詢。
今天這篇文章，我就帶著大家把復合查詢從基礎到實戰(zhàn)徹底講透，每一個知識點都配案例 + 解釋，小白也能輕松學會。
一、先回顧：單表基礎查詢（溫故知新）
復合查詢是單表查詢的進階，我們先用幾個經典案例快速過一遍重點語法。
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. 計算年薪并排序
獎金為空時用 IFNULL 轉 0，避免計算錯誤：
SELECT ename, sal*12+IFNULL(comm,0) AS '年薪' FROM EMP ORDER BY 年薪 DESC;
4. 聚合函數搭配子查詢
查工資最高的員工：
SELECT ename, job FROM EMP WHERE sal = (SELECT MAX(sal) FROM EMP);
查高于平均工資的員工：
SELECT ename, sal FROM EMP WHERE sal > (SELECT AVG(sal) FROM EMP);
5. 分組統(tǒng)計 + 分組后過濾
每個部門平均工資（保留兩位小數）、最高工資：
SELECT deptno, FORMAT(AVG(sal), 2), MAX(sal) FROM EMP GROUP BY deptno;
這里的FORMAT 是 MySQL 里專門用來「格式化數字 / 日期」的函數，最常用作用是：把數字保留指定位小數、加千分位分隔符。標準格式：FORMAT(數字, 保留小數位數)。
平均工資低于 2000 的部門：
SELECT deptno, AVG(sal) AS avg_sal FROM EMP GROUP BY deptno HAVING avg_sal < 2000;
二、多表查詢：跨表取數的核心
實際開發(fā)中，數據分散在多張表里，必須用多表連接才能拿到完整信息。
本文用經典 3 張表演示：
EMP：員工表（員工號、姓名、崗位、工資、部門號…）DEPT：部門表（部門號、部門名、位置…）SALGRADE：工資等級表（等級、最低工資、最高工資）
1. 什么是笛卡爾積
不加連接條件直接查多張表，會出現全組合，數據量爆炸，絕對不能用。
-- 錯誤示例：產生笛卡爾積 SELECT * FROM EMP, DEPT;
2. 正確多表查詢（內連接）
必須加上關聯(lián)條件（通常是外鍵 = 主鍵）。
案例 1：員工名、工資、所在部門名
SELECT EMP.ename, EMP.sal, DEPT.dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno;
案例 2：只看 10 號部門的員工與部門名
SELECT ename, sal, dname FROM EMP, DEPT WHERE EMP.deptno = DEPT.deptno AND DEPT.deptno = 10;
案例 3：員工姓名、工資、工資等級
SELECT ename, sal, grade FROM EMP, SALGRADE WHERE EMP.sal BETWEEN losal AND hisal;
三、自連接：一張表自己連自己
自連接：同一張表起兩個別名，當成兩張表用。典型場景：員工與領導關系（員工表的 mgr 指向領導的 empno）。
案例：查員工 FORD 的上級編號與姓名
方式 1：子查詢
SELECT empno, ename FROM emp WHERE empno = (SELECT mgr FROM emp WHERE ename='FORD');
方式 2：自連接（更優(yōu)雅）
SELECT leader.empno, leader.ename FROM emp leader, emp worker WHERE leader.empno = worker.mgr AND worker.ename='FORD';
要點：給表起別名，區(qū)分 “領導表”leader 和 “員工表”worker。
四、子查詢（嵌套查詢）：復合查詢靈魂
子查詢：把一個 SELECT 嵌套在另一個 SQL 里，先執(zhí)行內層，再執(zhí)行外層。
1. 單行子查詢（返回 1 行 1 列）
用于 = > < >= <= 這類單值比較。
案例：和 SMITH 同一部門的員工
SELECT * FROM EMP WHERE deptno = (SELECT deptno FROM EMP WHERE ename='SMITH');
2. 多行子查詢（返回多行 1 列）
必須搭配 IN / ANY / ALL 使用。
① IN（在結果列表里）
查詢和 10 部門崗位相同，但不屬于 10 部門的員工：
SELECT ename, job, sal, deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10) AND deptno != 10;
② ALL（比所有都…）
工資比 30 部門所有人都高的員工：
SELECT ename, sal, deptno FROM EMP WHERE sal > ALL(SELECT sal FROM EMP WHERE deptno=30);
③ ANY（比任意一個…）
工資比 30 部門任意一人高即可：
SELECT ename, sal, deptno FROM EMP WHERE sal > ANY(SELECT sal FROM EMP WHERE deptno=30);
3. 多列子查詢（返回多列）
同時匹配多個字段，用 (字段1, 字段2) = (子查詢列1, 列2)。
案例：和 SMITH 部門、崗位完全相同的人（排除 SMITH）：
SELECT ename FROM EMP WHERE (deptno, job) = (SELECT deptno, job FROM EMP WHERE ename='SMITH') AND ename <> 'SMITH';
4. FROM 里的子查詢（臨時表 / 派生表）
把子查詢結果當臨時表使用，非常適合先分組統(tǒng)計、再關聯(lián)查詢。
案例 1：高于本部門平均工資的員工
SELECT ename, deptno, sal, FORMAT(asal,2) FROM EMP, (SELECT AVG(sal) asal, deptno dt FROM EMP GROUP BY deptno) tmp WHERE EMP.sal > tmp.asal AND EMP.deptno = tmp.dt;
案例 2：每個部門工資最高的人
SELECT EMP.ename, EMP.sal, EMP.deptno, ms FROM EMP, (SELECT MAX(sal) ms, deptno FROM EMP GROUP BY deptno) tmp WHERE EMP.deptno = tmp.deptno AND EMP.sal = tmp.ms;
案例 3：部門信息 + 部門人數
SELECT DEPT.deptno, dname, mycnt, loc FROM DEPT, (SELECT COUNT(*) mycnt, deptno FROM EMP GROUP BY deptno) tmp WHERE DEPT.deptno = tmp.deptno;
五、合并查詢：UNION 與 UNION ALL
把多個 SELECT 結果縱向拼接，要求：
- 列數相同
- 對應列類型兼容
- 列名以第一個 SELECT 為準
1. UNION：合并并自動去重
SELECT ename, sal, job FROM EMP WHERE sal>2500 UNION SELECT ename, sal, job FROM EMP WHERE job='MANAGER';
2. UNION ALL：直接合并，不去重
性能比 UNION 高很多，確定無重復時優(yōu)先用它。
SELECT ename, sal, job FROM EMP WHERE sal>2500 UNION ALL SELECT ename, sal, job FROM EMP WHERE job='MANAGER';
對比速記
| 關鍵字 | 是否去重 | 性能 | 適用場景 |
|---|---|---|---|
| UNION | 是 | 較低 | 需去重 |
| UNION ALL | 否 | 高 | 允許重復 / 確定無重復 |
六、復合查詢核心總結
- 多表查詢一定要加連接條件，避免笛卡爾積
- 自連接 = 同表起別名，處理層級關系
- 子查詢分：單行 / 多行 / 多列 / FROM 子查詢
IN / ANY / ALL專門處理多行子查詢UNION去重，UNION ALL性能更高- 分組后過濾用
HAVING，不是WHERE
七、學習建議
- 先把本文案例手敲一遍
- 用
EXPLAIN看執(zhí)行計劃，理解查詢原理 - 多刷?？?/ LeetCode SQL 專題，強化手感
復合查詢是 MySQL 最核心、面試最高頻的知識點，吃透它，你的 SQL 水平會直接上一個臺階。
相關文章
這篇文章主要介紹了解決MySQL存儲時間出現不一致的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-04-04
解決Navicat導入數據庫數據結構sql報錯datetime(0)的問題
這篇文章主要介紹了解決Navicat導入數據庫數據結構sql報錯datetime(0)的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-07-07
被Qt連接數據庫折磨了三天之后終于連接成功了,記錄一下希望對看到的人有所幫助,下面這篇文章主要給大家介紹了關于QT連接Mysql數據庫的詳細教程,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2023-05-05
本篇文章是對利用word vb宏來生成sql的方法進行了詳細的分析介紹，需要的朋友參考下2013-06-06
這篇文章主要介紹了mysql清除log-bin日志的方法,同時介紹了log-bin日志的作用,需要的朋友可以參考下2014-06-06
這篇文章主要介紹了MySql連接數據庫常用參數及代碼解讀，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
在使用FlinkSQL的mysql-cdc連接器來監(jiān)聽MySQL數據庫時,通常需要將MySQL的binlog模式設置為ROW模式,當我們將MySQL主庫的binlog模式從STATEMENT切換為ROW并重啟MySQL服務后,MySQL從庫在同步時可能會報錯,所以本文介紹了MYSQL主庫切換binlog模式后主從同步錯誤的解決方案2024-08-08
auto_increment是用于主鍵自動增長的,從1開始增長,下面這篇文章主要給大家介紹了關于MySQL中auto_increment有什么作用的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2022-06-06
Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解
這篇文章主要介紹了Mysql中LEFT JOIN和JOIN查詢區(qū)別及原理詳解,Nested Loop Join 實際上就是通過驅動表的結果集作為循環(huán)基礎數據，然后一條一條的通過該結果集中的數據作為過濾條件到下一個表中查詢數據，然后合并結果,需要的朋友可以參考下2023-08-08
這篇文章主要介紹了Mysql誤刪數據解決方案及kill語句原理,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-09-09