MySQL中幾種常見的巢狀查詢詳解

2022-08-05 22:02:11

幾種常見的巢狀查詢——以學員成績為例

巢狀查詢，也稱為子查詢，是實際工作中經常用到的一種查詢方式。子查詢其實就是在已有的查詢語句中的where後面再巢狀一層查詢語句，也就是把內層查詢結果當做外層查詢參照的資料表來使用。

在工作中，經常會遇見4種子查詢，即含有比較運運算元（>、>=、<、<=、=、!=）、IN關鍵詞、ANY/ALL關鍵詞以及EXISTS關鍵詞的巢狀查詢。下面我們以學員考試成績為例，來學習一下這四種子查詢的應用。

# 建立學員資訊表 
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)
);
# 向學員表中插入資料 
INSERT INTO stu_info(iname,gender,department,age,province,email,mobilephone) VALUES 
('張勇','男','數學系',23,'河南','sfddf123dd@163.com','13323564321'), 
('王兵','男','數學系',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'), 
('趙伊美','女','數學系',21,'江蘇','zhaomeimei@163.com','13417723980'), 
('王建國','男','管理系',24,'浙江','9213228402@qq.com','13768329901'), 
('劉清','女','統計系',23,'安徽','lq1128@gmail.com','17823651180'), 
('趙家和','男','計算機系',28,'山東','dcrzdbjh@163.com','13827811311');

# 建立學員成績表 
CREATE TABLE stu_score( id INT , Excel TINYINT, Tableau TINYINT, MySQL TINYINT ); 
# 向成績表中插入資料 
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.查詢年齡超過所有學員平均年齡的學員資訊 
SELECT * FROM stu_info 
WHERE age >= avg(age); 
#需要注意的是Where後面不能使用聚合函數
#應該修改成
SELECT AVG(age) FROM stu_info;
SELECT * FROM stu_info
WHERE age>=23.3
#二合一 
# 1.查詢年齡超過所有學員平均年齡的學員資訊 
SELECT * FROM stu_info 
WHERE age >= (SELECT AVG(age) FROM stu_info);

# 2.查詢年齡不低於所屬系平均年齡的學員資訊 
SELECT * FROM stu_info AS s1 
WHERE age>= ( SELECT avg(age) FROM stu_info AS s2 
			  WHERE s1.department = s2.department);

使用含比較運運算元的巢狀查詢時，需要注意，比較運運算元後面的子查詢只能返回一個結果。

含ANY或ALL關鍵詞的巢狀查詢

對於含比較運運算元的巢狀查詢來說，巢狀部分的查詢語句只能返回一個值。那如果子查詢返回多個值，就需要用到ANY或者ALL關鍵詞了。通常，ANY / ALL 關鍵詞經常和比較運運算元連用，下面是6種比較運運算元與ANY / ALL 關鍵詞的搭配結果：

# 1.查詢非管理系中比管理系任意一個學員年齡小的學員資訊 
SELECT * FROM stu_info 
WHERE age < ANY(SELECT DISTINCT age FROM stu_info WHERE department = '管理系') 
			AND department != '管理系';

這裡的查詢邏輯是這樣的：首先查詢管理系中學生的年齡（去重），得到的結果是22和24；然後查詢出非管理系中年齡比22或24年齡小的學生資訊（也就是年齡小於24的非管理系學生資訊）。

# 2.查詢非管理系中比管理系所有學員年齡大的學員資訊 
SELECT * FROM stu_info 
WHERE age > ALL (SELECT DISTINCT age FROM stu_info WHERE department = '管理系') 
      AND department != '管理系';

這裡的查詢邏輯是這樣的：首先查詢管理系中學生的年齡（去重），得到的結果是22和24；然後查詢出非管理系中年齡比22和24都大的學生資訊（也就是年齡大於24的非管理系學生資訊）。

含IN關鍵詞的巢狀查詢

當查詢條件涉及某些已知的可列舉離散值的時候，我們就可以選擇IN關鍵詞來完成資料的提取。IN關鍵詞有兩種用法：

將可列舉的離散值直接寫在值列表中
當離散值是基於其他表的篩選結果時，就可以使用巢狀查詢，即把另一個表的查詢語句塊寫在IN關鍵詞後面的括號裡。

# 1.查詢數學系和計算機系的學員資訊 
SELECT * FROM stu_info WHERE department IN('數學系','計算機系'); 
# 2.查詢與張勇、劉偉同一個系的學員資訊 
SELECT * FROM stu_info 
WHERE department IN (SELECT department FROM stu_info WHERE iname IN('張勇','劉偉')); 
# 3.查詢MySQL成績大於85分的學員資訊 
SELECT * FROM stu_info 
WHERE id IN (SELECT id FROM stu_score WHERE MySQL > 85);

需要注意的是，在使用IN關鍵詞的巢狀查詢的時候，巢狀部分只能返回一個欄位的資訊（比如上面的department欄位或者id欄位），如果返回兩個及以上欄位資訊則會出現語法錯誤。

含EXISTS關鍵詞的巢狀查詢

EXISTS 關鍵詞的作用和 IN關鍵詞非常類似，不同的是，通過EXISTS關鍵詞的巢狀查詢返回的不是具體的值集合，而是滿足條件的邏輯值（也就是True / False）。也就是說，EXISTS的作用就是“判斷是否存在滿足某種條件的記錄”，如果存在這樣的記錄就返回真（True），如果不存在這樣的記錄就返回假（False）。

# 查詢MySQL成績大於85分的學員資訊 
SELECT * FROM stu_info 
WHERE EXISTS
(SELECT * FROM stu_score WHERE stu_score.id = stu_info.id AND MySQL > 85);

需要注意的是，使用EXISTS關鍵詞的巢狀語句 WHERE與EXISTS關鍵詞之間沒有任何引數，這是因為EXISTS只需要一個引數，通常是在EXISTS右側加一個子查詢語句。此外，EXISTS後面的子查詢中SELECT後面可以寫表中任何一個欄位或者星號或者一個常數，因為EXISTS後面的子查詢只關心是否存在滿足條件的記錄。下面返回的結果都是一樣：

【補充】關於IN和EXISTS兩個關鍵詞還有兩個延伸關鍵詞NOT IN和NOT EXISTS

# 查詢數學系和計算機系之外的學員資訊 
# 方法一 
SELECT * FROM stu_info 
WHERE department NOT IN('數學系','計算機系'); 
#方法二 
SELECT * FROM stu_info 
WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('數學系','計算機系') and stu_score.id = stu_info.id); 
# not exists的邏輯比較複雜，需要大家慢慢領會 
# 主要看not exists括號中的sql語句是否有結果，無結果：才會繼續執行where條件；有結果：視為 where條件不成立。 
# 當子查詢和主查詢有關聯條件時，相當於從主查詢中去掉子查詢的資料。

對於IN和EXISTS兩個關鍵詞，大多數情況下都可以相互替換，主要差別是使用效率問題，通常情況下采用EXISTS要比IN效率要高，但也要看實際情況具體使用：IN適合於外表大而內表小的情況；EXISTS適合於外表小而內表大的情況。

以上我們只是瞭解了where語句後面的子查詢，除此之外，子查詢還可以放在select語句、from語句、having語句後面。

附：其他使用方法和注意

除了上面這些還有很多很多，不過就不去細講了，因為這些跟別的資料庫差不多，只是為了給大家一個參考，提提就夠了。

         SELECT (SELECT s1 FROM t2) FROM t1;
         SELECT (SELECT s2 FROM t1);

支援子查詢的語法有：SELECT，INSERT，UPDATE，DELETE，SET和DO。

子查詢可以使用任何普通查詢中使用的關鍵詞：如DINSTINCT，GROUP BY，LIMIT，ORDER BY，UNION，ALL，UNION ALL等。可以使用<,>, <=, >=, =, <>運運算元進行比較，也可以使用ANY ，IN和SOME進行集合的匹配。

總結

到此這篇關於MySQL中幾種常見的巢狀查詢的文章就介紹到這了,更多相關MySQL巢狀查詢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！