站長資訊網(wǎng)
mysql中有嵌套查詢語句，語法為“SELECT語句 WHERE 條件(SELECT語句)”；該語句也被稱為子查詢語句，能夠在已有的查詢語句中的where后面再嵌套一層查詢語句，也即將內(nèi)層查詢結(jié)果當(dāng)做外層查詢參照的數(shù)據(jù)來使用。
本教程操作環(huán)境：windows10系統(tǒng)、mysql8.0.22版本、Dell G3電腦。
mysql中有嵌套查詢語句嗎
嵌套查詢，也稱為子查詢，是實際工作中經(jīng)常用到的一種查詢方式。子查詢其實就是在已有的查詢語句中的where后面再嵌套一層查詢語句，也就是把內(nèi)層查詢結(jié)果當(dāng)做外層查詢參照的數(shù)據(jù)表來使用。
在工作中，經(jīng)常會遇見4種子查詢，即含有比較運算符（>、>=、<、<=、=、!=）、IN關(guān)鍵詞、ANY/ALL關(guān)鍵詞以及EXISTS關(guān)鍵詞的嵌套查詢。下面我們以學(xué)員考試成績?yōu)槔瑏韺W(xué)習(xí)一下這四種子查詢的應(yīng)用。
# 創(chuàng)建學(xué)員信息表 CREATE TABLE stu_info ( id INT AUTO_INCREMENT PRIMARY KEY, iname VARCHAR(20), gender CHAR(1), department VARCHAR(10), age TINYINT, province VARCHAR(10), email VARCHAR(50), mobilephone CHAR(11) ); # 向?qū)W員表中插入數(shù)據(jù) INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES ('張勇','男','數(shù)學(xué)系',23,'河南','sfddf123dd@163.com','13323564321'), ('王兵','男','數(shù)學(xué)系',25,'江蘇','lss1993@163.com','17823774329'), ('劉偉','男','計算機系',21,'江蘇','qawsed112@126.com','13834892240'), ('張峰','男','管理系',22,'上海','102945328@qq.com','13923654481'), ('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'), ('徐曉紅','女','計算機系',24,'浙江','xixiaohong@gmail.com','13720097528'), ('趙伊美','女','數(shù)學(xué)系',21,'江蘇','zhaomeimei@163.com','13417723980'), ('王建國','男','管理系',24,'浙江','9213228402@qq.com','13768329901'), ('劉清','女','統(tǒng)計系',23,'安徽','lq1128@gmail.com','17823651180'), ('趙家和','男','計算機系',28,'山東','dcrzdbjh@163.com','13827811311'); # 創(chuàng)建學(xué)員成績表 CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT ); # 向成績表中插入數(shù)據(jù) INSERT INTO stu_score VALUES (1,87,72,88), (3,90,66,72), (2,90,70,86), (4,88,82,76), (8,92,67,80), (10,88,82,89), (5,79,66,60), (7,91,78,90), (6,82,79,88), (9,85,70,85); # 1.查詢年齡超過所有學(xué)員平均年齡的學(xué)員信息 SELECT * FROM stu_info WHERE age >= avg(age); #需要注意的是Where后面不能使用聚合函數(shù) #應(yīng)該修改成 SELECT AVG(age) FROM stu_info; SELECT * FROM stu_info WHERE age>=23.3 #二合一 # 1.查詢年齡超過所有學(xué)員平均年齡的學(xué)員信息 SELECT * FROM stu_info WHERE age >= (SELECT AVG(age) FROM stu_info); # 2.查詢年齡不低于所屬系平均年齡的學(xué)員信息 SELECT * FROM stu_info AS s1 WHERE age>= ( SELECT avg(age) FROM stu_info AS s2 WHERE s1.department = s2.department);
使用含比較運算符的嵌套查詢時，需要注意，比較運算符后面的子查詢只能返回一個結(jié)果。
（2）含ANY或ALL關(guān)鍵詞的嵌套查詢
對于含比較運算符的嵌套查詢來說，嵌套部分的查詢語句只能返回一個值。那如果子查詢返回多個值，就需要用到ANY或者ALL關(guān)鍵詞了。通常，ANY / ALL 關(guān)鍵詞經(jīng)常和比較運算符連用，下面是6種比較運算符與ANY / ALL 關(guān)鍵詞的搭配結(jié)果：
# 1.查詢非管理系中比管理系任意一個學(xué)員年齡小的學(xué)員信息 SELECT * FROM stu_info WHERE age < ANY(SELECT DISTINCT age FROM stu_info WHERE department = '管理系') AND department != '管理系';
這里的查詢邏輯是這樣的：首先查詢管理系中學(xué)生的年齡（去重），得到的結(jié)果是22和24；然后查詢出非管理系中年齡比22或24年齡小的學(xué)生信息（也就是年齡小于24的非管理系學(xué)生信息）。
# 2.查詢非管理系中比管理系所有學(xué)員年齡大的學(xué)員信息 SELECT * FROM stu_info WHERE age > ALL (SELECT DISTINCT age FROM stu_info WHERE department = '管理系') AND department != '管理系';
這里的查詢邏輯是這樣的：首先查詢管理系中學(xué)生的年齡（去重），得到的結(jié)果是22和24；然后查詢出非管理系中年齡比22和24都大的學(xué)生信息（也就是年齡大于24的非管理系學(xué)生信息）。
（3）含IN關(guān)鍵詞的嵌套查詢
當(dāng)查詢條件涉及某些已知的可枚舉離散值的時候，我們就可以選擇IN關(guān)鍵詞來完成數(shù)據(jù)的提取。IN關(guān)鍵詞有兩種用法：
- 將可枚舉的離散值直接寫在值列表中
- 當(dāng)離散值是基于其他表的篩選結(jié)果時，就可以使用嵌套查詢，即把另一個表的查詢語句塊寫在IN關(guān)鍵詞后面的括號里。
# 1.查詢數(shù)學(xué)系和計算機系的學(xué)員信息 SELECT * FROM stu_info WHERE department IN('數(shù)學(xué)系','計算機系'); # 2.查詢與張勇、劉偉同一個系的學(xué)員信息 SELECT * FROM stu_info WHERE department IN (SELECT department FROM stu_info WHERE iname IN('張勇','劉偉')); # 3.查詢MySQL成績大于85分的學(xué)員信息 SELECT * FROM stu_info WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);
需要注意的是，在使用IN關(guān)鍵詞的嵌套查詢的時候，嵌套部分只能返回一個字段的信息（比如上面的department字段或者id字段），如果返回兩個及以上字段信息則會出現(xiàn)語法錯誤。
（4）含EXISTS關(guān)鍵詞的嵌套查詢
EXISTS 關(guān)鍵詞的作用和 IN關(guān)鍵詞非常類似，不同的是，通過EXISTS關(guān)鍵詞的嵌套查詢返回的不是具體的值集合，而是滿足條件的邏輯值（也就是True / False）。也就是說，EXISTS的作用就是“判斷是否存在滿足某種條件的記錄”，如果存在這樣的記錄就返回真（True），如果不存在這樣的記錄就返回假（False）。
# 查詢MySQL成績大于85分的學(xué)員信息 SELECT * FROM stu_info WHERE EXISTS(SELECT * FROM stu_score WHERE stu_score.id = stu_info.id AND MySQL > 85);
需要注意的是，使用EXISTS關(guān)鍵詞的嵌套語句 WHERE與EXISTS關(guān)鍵詞之間沒有任何參數(shù)，這是因為EXISTS只需要一個參數(shù)，通常是在EXISTS右側(cè)加一個子查詢語句。此外，EXISTS后面的子查詢中SELECT后面可以寫表中任何一個字段或者星號或者一個常數(shù)，因為EXISTS后面的子查詢只關(guān)心是否存在滿足條件的記錄。下面返回的結(jié)果都是一樣：
【補充】關(guān)于IN和EXISTS兩個關(guān)鍵詞還有兩個延伸關(guān)鍵詞NOT IN和NOT EXISTS
# 查詢數(shù)學(xué)系和計算機系之外的學(xué)員信息 # 方法一 SELECT * FROM stu_info WHERE department NOT IN('數(shù)學(xué)系','計算機系'); #方法二 SELECT * FROM stu_info WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('數(shù)學(xué)系','計算機系') and stu_score.id = stu_info.id); # not exists的邏輯比較復(fù)雜，需要大家慢慢領(lǐng)會 # 主要看not exists括號中的sql語句是否有結(jié)果，無結(jié)果：才會繼續(xù)執(zhí)行where條件；有結(jié)果：視為 where條件不成立。 # 當(dāng)子查詢和主查詢有關(guān)聯(lián)條件時，相當(dāng)于從主查詢中去掉子查詢的數(shù)據(jù)。
對于IN和EXISTS兩個關(guān)鍵詞，大多數(shù)情況下都可以相互替換，主要差別是使用效率問題，通常情況下采用EXISTS要比IN效率要高，但也要看實際情況具體使用：IN適合于外表大而內(nèi)表小的情況；EXISTS適合于外表小而內(nèi)表大的情況。
以上我們只是了解了where語句后面的子查詢，除此之外，子查詢還可以放在select語句、from語句、having語句后面。
推薦學(xué)習(xí)：mysql視頻教程
