mysql中有巢狀查詢語句嗎

WBOY
發布: 2022-05-27 15:12:33
原創
2333 人瀏覽過

mysql中有巢狀查詢語句,語法為「SELECT語句WHERE 條件(SELECT語句)」;此語句也稱為子查詢語句,能夠在已有的查詢語句中的where後面再嵌套一層查詢語句,也即將內層查詢結果當作外層查詢參照的資料來使用。

mysql中有巢狀查詢語句嗎

本教學操作環境:windows10系統、mysql8.0.22版本、Dell G3電腦。

mysql中有巢狀查詢語句嗎

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

在工作中,常常會遇見4種子查詢,即含有比較運算子(>、>=、

# 创建学员信息表 
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);
登入後複製

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

(2)含ANY或ALL關鍵字的巢狀查詢
對於含比較運算子的巢狀查詢來說,巢狀部分的查詢語句只能傳回一個值。那如果子查詢回傳多個值,就需要用到ANY或ALL關鍵字了。通常,ANY / ALL 關鍵字經常和比較運算子連用,以下是6種比較運算子與ANY / ALL 關鍵字的搭配結果:mysql中有巢狀查詢語句嗎

# 1.查询非管理系中比管理系任意一个学员年龄小的学员信息 SELECT * FROM stu_info 
WHERE age <p><img src="https://img.php.cn/upload/article/000/000/067/4324f62c03299e4da154e72d7d28ebb8-1.png" alt="mysql中有巢狀查詢語句嗎"><br># 這裡的查詢邏輯是這樣的:先查詢管理系中學生的年齡(去重),得到的結果是22和24;然後查詢出非管理系中年齡比22或24年齡小的學生資料(也就是年齡小於24的非管理系學生資訊)。 </p><pre class="brush:php;toolbar:false"># 2.查询非管理系中比管理系所有学员年龄大的学员信息 SELECT * FROM stu_info 
WHERE age > ALL (SELECT DISTINCT age FROM stu_info WHERE department = '管理系') 
      AND department != '管理系';
登入後複製

mysql中有巢狀查詢語句嗎
這裡的查詢邏輯是這樣的:首先查詢管理系中學生的年齡(去重),得到的結果是22和24;然後查詢出非管理系中年齡比22和24都大的學生資料(也就是年齡大於24的非管理系學生資訊)。

(3)含IN關鍵字的巢狀查詢
當查詢條件涉及某些已知的可枚舉離散值的時候,我們就可以選擇IN關鍵字來完成資料的擷取。 IN關鍵字有兩種用法:

  1. 將可枚舉的離散值直接寫在值列表中
  2. 當離散值是基於其他表的篩選結果時,就可以使用巢狀查詢,即把另一個表格的查詢語句區塊寫在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欄位),如果傳回兩個及以上字段資訊則會出現語法錯誤。

(4)包含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語句後面。

推薦學習:mysql影片教學

#

以上是mysql中有巢狀查詢語句嗎的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板