一文了解MySQL的四大子查詢
前言
本節(jié)將為大家?guī)鞰ySQL標(biāo)量/單行子查詢、列子/表子查詢的講解?
一、子查詢概念
子查詢指一個查詢語句嵌套在另一個查詢語句內(nèi)部的查詢，這個特性從
MySQL 4.1開始引入；在特定情況下，一個查詢語句的條件需要另一個查詢語句來獲取，內(nèi)層查詢(inner query）語句的查詢結(jié)果，可以為外層查詢(outer query）語句提供查詢條件。
內(nèi)層查詢即子查詢，外層查詢即主查詢，只是叫法不同而已
?查詢需求：
查詢公司中工資最低的員工信息
工資最低是多少？
SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp);
?子查詢規(guī)范：
- 子查詢必須放在小括號中
- 子查詢一般放在比較操作符的右邊，以增強代碼可讀性
- 子查詢可以出現(xiàn)在幾乎所有的
SELECT字句中（如：SELECT、FROM、WHERE、ORDER BY、HAVING子句）
?子查詢分類：
1、根據(jù)子查詢返回的數(shù)據(jù)分類：
- 標(biāo)量子查詢(scalar subquery)：返回1行1列一個值
- 行子查詢(row subquery)：返回的結(jié)果集是 1 行 N 列
- 列子查詢(column subquery)：返回的結(jié)果集是 N 行 1列
- 表子查詢(table subquery)：返回的結(jié)果集是 N 行 N 列
子查詢可以返回一個標(biāo)量(就一個值)、一個行、一個列或一個表，這些子查詢分別稱之為標(biāo)量、行、列和表子查詢。
2、根據(jù)子查詢和主查詢之間是否有條件關(guān)聯(lián)分類：
- 相關(guān)子查詢：兩個查詢之間有一定的條件關(guān)聯(lián)(相互聯(lián)系)
- 不相關(guān)子查詢：兩個查詢之間沒有條件關(guān)聯(lián)(相互獨立)
3、為了方便，對于在何處使用子查詢給大家給出幾點個人建議：
- 子查詢出現(xiàn)在WHERE子句中：此時子查詢返回的結(jié)果一般都是單列單行、單行多列、多行單列
- 子查詢出現(xiàn)在HAVING子句中：此時子查詢返回的都是單行單列數(shù)據(jù)，同時為了使用統(tǒng)計函數(shù)操作
- 子查詢出現(xiàn)在FROM子句中：此時子查詢返回的結(jié)果圖一般都是多行多列，可以按照一張數(shù)據(jù)表(臨時表)的形式操作
二、標(biāo)量/單行子查詢、列子/表子查詢
??標(biāo)量子查詢
子查詢返回的是單行單列的數(shù)據(jù)，就是一個值
查詢出基本工資比ALLEN低的全部員工信息
SELECT * FROM emp WHERE sal<(SELECT sal FROM emp WHERE eanme='ALLEN');
查詢基本工資高于公司平均工資的全部員工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);
查詢出與ALLEN從事同一工作，并且基本工資高于員工編號為7521的全部員工信息
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='ALLEN') AND sal>(SELECT sal FROM emp WHERE empno=7521) #把ALLEN自己去掉 AND ename<>'ALLEN';
?效果如下：
??單行子查詢
子查詢返回的是單行多列的數(shù)據(jù)，就是一條記錄
查詢與SCOTT從事統(tǒng)一工作且工資相同的員工信息
SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename='SCOTT') AND ename<>'SCOTT';
查詢與員工編號為7566從事統(tǒng)一工作且領(lǐng)導(dǎo)相同的全部員工信息
SELECT * FROM emp WHERE (job,mgr)=(SELECT job,mgr FROM emp WHERE empno=7566) AND emp<>7566;
?效果如下：
??列子查詢（多行子查詢）
子查詢返回的是多行單列的數(shù)據(jù)，就是一列數(shù)據(jù)。多行子查詢也稱為集合比較子查詢，
在使用多行子查詢需要使用多行比較操作符：
| 操作符 | 含義 |
|---|---|
| IN | 等于列表中的任意一個 |
| ANY | 需要和單行比較操作符一起使用(>、<、=、<>…)，與子查詢結(jié)果中任何一個值比較，一個成立 |
| ALL | 需要和單行比較操作符一起使用(>、<、=、<>…)，和子查詢返回的所有值比較，同時成立 |
| SOME | 實際上是ANY的別名，作用相同，一般用ANY |
IN操作符
IN 運算符用來判斷表達(dá)式的值是否位于給出的列表中；如果是，返回值為 1，否則返回值為 0。
NOT IN 的作用和 IN 恰好相反，NOT IN 用來判斷表達(dá)式的值是否不存在于給出的列表中；如果不是，返回值為 1，否則返回值為 0。
查詢出與每個部門中最低工資相同的員工信息
- 按照部門分組，統(tǒng)計每個部門的最低工資
- 根據(jù)最低工資查詢出員工信息
SELECT * FROM emp WHERE sal IN(SELECT MIN(sal) FROM emp GROUP BY deptno) AND deptno IS NOT NULL;
ANY操作符
ANY關(guān)鍵字是一個MySQL運算符，如果子查詢條件中ANY的比較結(jié)果為TRUE，則它會返回布爾值TRUE 。
查詢工資比任何管理工資都要高的員工信息
- 查找出每個管理的薪資
- 每個員工的薪資與每個管理的薪資比較
SELECT * FROM emp WHERE sal >ANY( SELECT MIN(sal) FROM emp WHERE job='MANAGER' GROUP BY deptno);
ALL操作符
ALL關(guān)鍵字是一個MySQL運算符，如果子查詢條件中ALL的比較結(jié)果為TRUE，則它會返回布爾值TRUE 。
案例同ANY操作符
?效果如下：
??表子查詢
子查詢返回的是多行多列的數(shù)據(jù)，就是一個表格，必須使用 IN、ANY 和 ALL 操作符對子查詢返回的結(jié)果進(jìn)行比較
?綜合練習(xí)：
在emp表中，得到與10號部門任何一個員工入職年份和領(lǐng)導(dǎo)相同的員工信息(用在where子句中)
需要用到DATE_FORMAT(hiredate,'%Y')將入職日期轉(zhuǎn)換為年份
SELECT * FROM emp WHERE (DATE_FORMAT(hiredate,'%Y'),mgr) IN (SELECT DATE_FORMAT(hiredate,'%Y') hiryear,mgr FROM emp WHERE deptno=10);
查詢出每個部門的編號、名稱、位置、部門人數(shù)、平均工資（用在from子句中）
以前學(xué)的多表聯(lián)合查詢——-emp、dept
用子查詢聯(lián)合查詢
SELECT d.deptno,d.dname,d.loc,COUNT(e.deptno),ROUND(AVG(sal),2) FROM dept d LEFT JOIN emp e ON e.deptno=d.deptno GROUP BY d.deptno,d.dname,d.loc; SELECT dept.deptno,dept.dname,dept.loc,d.count,d.avgsal FROM dept LEFT JOIN (SELECT deptno,COUNT(*) count,AVG(sal) avgsal FROM emp GROUP BY deptno) d ON dept.deptno=d.deptno;
查詢出所有在’SALES’部門工作的員工編號、姓名、基本工資、獎金、職位、雇傭日期、部門的最高和最低工資。(where和from子句同時使用)
#1 SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,minsal,maxsal,e.deptno FROM emp e JOIN (SELECT deptno,MIN(sal) minsal,MAX(sal) maxsal FROM emp GROUP BY deptno) td ON e.deptno=td.deptno AND e.deptno=(SELECT deptno FROM dept WHERE dname='SALES'); #2 SELECT e.empno,e.ename,e.sal,e.comm,e.job,e.hiredate,minsal,maxsal,e.deptno FROM emp e JOIN (SELECT deptno,MIN(sal) minsal,MAX(sal) maxsal FROM emp GROUP BY deptno HAVING deptno=(SELECT deptno FROM dept WHERE dname='SALES')) td ON e.deptno=td.deptno;
查詢出比‘ALLEN’或‘CLACRK’薪資多的所有員工的編號、姓名、基本工資、部門名稱、領(lǐng)導(dǎo)姓名、部門人數(shù)。
#隱式方式
SELECT e.empno,e.ename,e.sal,d.dname,me.ename 領(lǐng)導(dǎo),temp.count FROM emp e,dept d,emp me,
(SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp
WHERE e.deptno=d.deptno AND e.mgr=me.empno AND temp.deptno=e.deptno
AND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))
AND e.ename NOT IN('ALLEN','CLARK');
#顯示方式
SELECT e.empno,e.ename,e.sal,d.dname,me.ename 領(lǐng)導(dǎo),temp.count
FROM emp e
JOIN dept d ON e.deptno=d.deptno
LEFT JOIN emp me ON e.mgr=me.empno
JOIN (SELECT deptno,COUNT(deptno) count FROM emp e GROUP BY deptno) temp ON temp.deptno=e.deptno
AND e.sal >ANY(SELECT sal FROM emp WHERE ename IN('ALLEN','CLARK'))
AND e.ename NOT IN('ALLEN','CLARK');
列出公司各個部門的經(jīng)理(假設(shè)每個部門只有一個經(jīng)理，job為‘MANAGER’)的姓名、薪資、部門名稱、部門人數(shù)、部門平均工資。
#隱式方式 SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsal FROM emp e,dept d,(SELECT deptno, COUNT(deptno) count,AVG(sal) avgsal FROM emp GROUP BY deptno) temp WHERE job='MANAGER' AND e.deptno=d.deptno AND temp.deptno=e.deptno; #顯示方式 SELECT e.ename,e.sal,d.dname,temp.count,temp.avgsal FROM emp e JOIN dept d ON e.deptno=d.deptno JOIN (SELECT deptno, COUNT(deptno) count,AVG(sal) avgsal FROM emp GROUP BY deptno) temp ON temp.deptno=e.deptno AND job='MANAGER';
查詢出所有薪資高于公司平均薪資的員工編號、姓名、基本工資、職位、雇傭日期、所在部門名稱、部門位置、上級領(lǐng)導(dǎo)姓名、工資等級、部門人數(shù)、平均工資、平均服務(wù)年限。
#隱式方式 SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 領(lǐng)導(dǎo),s.grade,temp.count,temp.avgsal,temp.avgyear FROM emp e,dept d,emp me,salgrade s,(SELECT deptno,COUNT(deptno) count,AVG(sal) avgsal,AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) avgyear FROM emp GROUP BY deptno) temp WHERE e.deptno=d.deptno AND e.sal>(SELECT AVG(sal) FROM emp) AND e.mgr=me.empno AND e.sal BETWEEN s.losal AND s.hisal AND temp.deptno=e.deptno; #顯示方式 SELECT e.empno,e.ename,e.sal,e.job,e.hiredate,d.dname,d.loc,me.ename 領(lǐng)導(dǎo),s.grade,temp.count,temp.avgsal,temp.avgyear FROM emp e JOIN dept d ON e.deptno=d.deptno AND e.sal>(SELECT AVG(sal) FROM emp) LEFT JOIN emp me ON e.mgr=me.empno JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal JOIN (SELECT deptno,COUNT(deptno) count,AVG(sal) avgsal,AVG(TIMESTAMPDIFF(MONTH,hiredate,CURDATE())/12) avgyear FROM emp GROUP BY deptno) temp ON temp.deptno=e.deptno;
三、總結(jié)
?? 子查詢允許結(jié)構(gòu)化的查詢，這樣就可以把一個查詢語句的每個部分隔開。??子查詢提供了另一種方法來執(zhí)行有些需要復(fù)雜的join和union來實現(xiàn)的操作。??在許多人看來，子查詢可讀性較高。 而實際上，這也是子查詢的由來。
到此這篇關(guān)于一文了解MySQL的四大子查詢的文章就介紹到這了,更多相關(guān)MySQL 子查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
最左前綴原則是發(fā)生在復(fù)合索引上的,只有復(fù)合索引才會有所謂的左和右之分,下面這篇文章主要給大家介紹了關(guān)于Mysql索引的最左前綴原則的相關(guān)資料,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-03-03
Mysql誤刪數(shù)據(jù)解決方案及kill語句原理
這篇文章主要介紹了Mysql誤刪數(shù)據(jù)解決方案及kill語句原理,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-09-09
MySQL用戶管理與PostgreSQL用戶管理的區(qū)別說明
這篇文章主要介紹了MySQL用戶管理與PostgreSQL用戶管理的區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
JSON我相信大家都已經(jīng)很熟悉了,但在 MySQL中,直至 5.7 版本中,才正式引入 JSON數(shù)據(jù)類型,下面這篇文章主要給大家介紹了關(guān)于MySQL中JSON字段數(shù)據(jù)類型的相關(guān)資料,需要的朋友可以參考下2022-06-06
使用MySQL 數(shù)據(jù)庫的站點,當(dāng)訪問連接數(shù)過多時,就會出現(xiàn) "Too many connections" 的錯誤,所以我們需要設(shè)置MySQL查看和修改最大連接數(shù),具有一定的參考價值,感興趣的可以了解一下2023-10-10
這篇文章主要介紹了CentOS7.5 安裝MySql的教程,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2020-10-10
MySQL與瀚高數(shù)據(jù)庫的范圍分區(qū)語法及實例代碼
MySQL分區(qū)技術(shù)通過將大表分割成多個小分區(qū),提升查詢性能和管理效率,分區(qū)可獨立存儲、備份和優(yōu)化,適用于大數(shù)據(jù)集和時效性數(shù)據(jù)處理,這篇文章主要介紹了MySQL與瀚高數(shù)據(jù)庫的范圍分區(qū)語法及實例代碼的相關(guān)資料,需要的朋友可以參考下2026-05-05
mysql單一的事務(wù)single-transaction選項詳解
利用 --single-transaction 選項可以有效地確保 LOAD DATA INFILE 操作的原子性和數(shù)據(jù)一致性,特別適合于需要高可靠性的批量數(shù)據(jù)導(dǎo)入場景,這篇文章給大家介紹mysql單一的事務(wù)single-transaction選項的相關(guān)知識,感興趣的朋友跟隨小編一起看看吧2025-05-05
Mysql中STR_TO_DATE函數(shù)使用(字符串轉(zhuǎn)為日期/時間值)
這篇文章主要給大家介紹了關(guān)于Mysql中STR_TO_DATE函數(shù)使用的相關(guān)資料,STR_TO_DATE函數(shù)的主要功能是字符串轉(zhuǎn)為日期/時間值,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-09-09
mysql?子查詢的概述和分類及單行子查詢功能實現(xiàn)
本文詳細(xì)介紹了MySQL的子查詢概念和應(yīng)用,解釋了子查詢是在主查詢中嵌套另一個查詢,包括外查詢和內(nèi)查詢,并從多個角度進(jìn)行分類,文章還深入探討了子查詢的編寫技巧和使用場景,對于學(xué)習(xí)和應(yīng)用MySQL的人來說,這是一篇非常有價值的指南2024-10-10