三、MySQL子查詢學習筆記

7：子查詢

含義：
一條查詢語句中又嵌套了另一條完整的select語句，其中被嵌套的select語句，稱為子查詢或內查詢；在外面的查詢語句，稱為主查詢或外查詢
分類：
一、按子查詢出現的位置：

1）select后面：
?????????????????????? 僅支持標量子查詢

2）from后面：
?????????????????????? 支持表子查詢

3）where或having后面：
?????????????????????? 標量子查詢（1行1列）
?????????????????????? 列子查詢（n行1列）
?????????????????????? 行子查詢（1行n列）

4）exists后面（相關子查詢）:
?????????????????????? 表子查詢

二、按結果集的行列數不同：

標量子查詢（單行子查詢）：結果集為1行1列

列子查詢（多行子查詢）：結果集為n行1列

行子查詢：結果集為1行n列

表子查詢：結果集為n行n列

特點：

1、子查詢都放在小括號內
2、子查詢可以放在from后面、select后面、where后面、having后面，但一般放在條件的右側
3、子查詢優先于主查詢執行，主查詢使用了子查詢的執行結果
4、子查詢根據查詢結果的行數不同分為以下兩類：

① 單行子查詢：結果集只有一行
一般搭配單行操作符使用：> < = <> >= <=
非法使用子查詢的情況：
a、子查詢的結果為一組值
b、子查詢的結果為空

② 多行子查詢：結果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 屬于子查詢結果中的任意一個就行
any和all往往可以用其他查詢代替

一、where 或 having后面可以加一下三種子查詢：

標量子查詢（單行子查詢）：1行1列

列子查詢（多行子查詢）：n行1列

行子查詢：1行n列

特點

子查詢放在小括號內

子查詢一遍放在條件的右側
3. 標量子查詢，一般會搭配著單行操作符來使用：> < >= <= = <>；
列子查詢，一般搭配著多行操作符來使用：in、any/some、all

注意：
all 是“任意”，是與所有值比較
例如：小于 any() 等價于 小于 min()
any 是“任一個”， 是與其中的某個值比較滿足即可。
例如：小于 any() 等價于 小于 max()

子查詢的執行優先于主查詢執行，因為 主查詢的條件用到了子查詢的結果

1.標量子查詢

# 1.標量子查詢（一行一列）# 【案例1】查詢誰的工資比Abel高？ # 1）查詢Abel的工資 SELECT salary FROM employees WHERE last_name = 'Abel';# 2) 查詢員工的信息，滿足 salary > 1)的結果 SELECT * FROM employees WHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel' );# 【案例2】返回job_id（工種號） 與 141號員工相同，salary 比 143 號 員工多的員工姓名，job_id，和工資 # 1）查詢 員工號 為141 的員工的job_id SELECT job_id FROM employees WHERE employee_id = 141;# 2）查詢 員工號為143的員工的工資 SELECT salary FROM employees WHERE employee_id = 143;# 3)查詢員工的姓名，job_id，和工資，要求job_id = 1）并且salary > 2） SELECT last_name , job_id ,salary FROM employees WHERE job_id = (SELECT job_id FROM employeesWHERE employee_id = 141 )AND salary >(SELECT salaryFROM employeesWHERE employee_id = 143 ) # 【案例3】返回公司工資最少的員工的last_name ,job_id 和salary # 1) 查詢公司的最低工資 SELECT MIN(salary) FROM employees;# 2) 查詢 last_name , job_id和salary，要求salary = 1) SELECT last_name, job_id,salary FROM employees WHERE salary = (SELECT MIN(salary)FROM employees )# 【案例4】 查詢最低工資大于 50號部門最低工資 的部門id 和其最低工資# 1) 查詢50號部門的最低工資 SELECT MIN(salary) FROM employees WHERE department_id = 50;# 2) 查詢每個部門的最低工資 SELECT department_id , min(salary) FROM employees GROUP BY department_id;# 3) 在2）的基礎上篩選，滿足min(salary)>1) 查詢department_id > 50 的部門號 和 最低工資 SELECT department_id , MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employeesWHERE department_id = 50 )

2.列子查詢

# 2.列子查詢（n行1列） # 【案例1】 返回location_id是1400或1700 的部門中的所有員工姓名# 1）查詢location_id 是1400或1700的部門編號（多行1列） SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700);# 2）查詢員工姓名，要求部門號是1）列表中的某一個 SELECT last_name FROM employees WHERE department_id in(SELECT DISTINCT department_idFROM departmentsWHERE location_id IN(1400,1700) )# 【案例2】 返回其他工種中比 job_id為“IT_PROG” 工種的所有員工的工資 中 "任一" 低的員工的員工號、姓名、job_id和salary# 1) 查詢job_id為“IT_PROG” 部門的所有員工的工資 SELECT DISTINCT salary FROM employees WHERE job_id = "IT_PROG";# 2) 查詢員工的員工號、姓名、job_id和salary，要求salary< 1）中的任一（比其中某一個小就行）一個結果 SELECT last_name ,employee_id , job_id , salary FROM employees WHERE salary < ANY(SELECT DISTINCT salaryFROM employeesWHERE job_id = "IT_PROG" ) AND job_id <> 'IT_PROG';# 2) 或者 小于其中任何一個就行 等價于 小于最大值 SELECT last_name ,employee_id , job_id , salary FROM employees WHERE salary < (SELECT max(salary)FROM employeesWHERE job_id = "IT_PROG" ) AND job_id <> 'IT_PROG';# 【案例3】 返回其他部門中比job_id為‘IT_PROG’部門所有工資 ”都要“ 低的員工 的員工號、姓名、job_id 和salary SELECT last_name ,employee_id , job_id , salary FROM employees WHERE salary < ALL(SELECT DISTINCT salaryFROM employeesWHERE job_id = "IT_PROG" ) AND job_id <> 'IT_PROG';# 或者 小于所有（任意）的 等價于 大于最小值 SELECT last_name ,employee_id , job_id , salary FROM employees WHERE salary < (SELECT MIN(salary)FROM employeesWHERE job_id = "IT_PROG" ) AND job_id <> 'IT_PROG';

3.行子查詢（結果集為1行多列 或 多行多列）

# 3.行子查詢（結果集為1行多列 或 多行多列）# 【案例1】 查詢員工編號最小并且工資最高的員工信息# 先用標量子查詢來實現 # 1) 查詢最小的員工編號 SELECT MIN(employee_id) FROM employees;# 2) 查詢最高的工資 SELECT MAX(salary) FROM employees;# 3) 查詢員工的信息 SELECT * FROM employees WHERE employee_id = (SELECT MIN(employee_id)FROM employees )AND salary = (SELECT MAX(salary)FROM employees )# 再用行子查詢來實現 SELECT * FROM employees WHERE (employee_id,salary) = (SELECT MIN(employee_id),MAX(salary)FROM employees )

二、select后面（僅僅支持標量子查詢）

二、select后面（僅僅支持標量子查詢）# 【案例1】 查詢每個部門的員工個數 SELECT d.* , (SELECT COUNT(*) FROM employees eWHERE e.department_id = d.department_id ) 個數 FROM departments d# 【案例2】 查詢員工號 = 102 的部門名 SELECT (SELECT department_nameFROM departments dINNER JOIN employees eON e.department_id = d.department_idWHERE e.employee_id = 102 ) 部門名;

三、from后面
注意：將子查詢結果充當一張表時，必須要起別名，否則會報錯

# 三、from后面 # 注意：將子查詢結果充當一張表時，必須要起別名，否則會報錯 # 【案例1】 查詢每個部門的平均工資的工資等級# 1) 查詢每個部門的平均工資 SELECT AVG(salary) , department_id FROM employees GROUP BY department_idSELECT * FROM job_grades;# 2) 連接1）的結果集 和 job_grades表，篩選條件為 平均工資 between lowest_sal and highest_sal SELECT ag_dep.* , g.grade_level FROM (SELECT AVG(salary) ag , department_idFROM employeesGROUP BY department_id ) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

四、exists后面（相關子查詢）:只關心查詢的東西是否存在，存在返回1，不存在返回0
語法：EXISTS(完整的查詢語句)
結果：1或0

# 四、exists后面（相關子查詢）:只關心查詢的東西是否存在，存在返回1，不存在返回0 # 語法： # EXISTS(完整的查詢語句) # 結果： # 1或0 SELECT EXISTS (SELECT employee_id FROM employees);SELECT EXISTS (SELECT employee_id FROM employees WHERE salary = 300000);# 【案例1】 查詢有員工的部門名 # 方法1 , exists SELECT department_name FROM departments d WHERE EXISTS(SELECT *FROM employees e , departments dWHERE d.department_id = e.department_id );# 方法2 , in SELECT department_name FROM departments d WHERE d.department_id IN(SELECT department_id FROM employees );# 【案例2】 查詢沒有女朋友的男神信息 USE girls; # 方法一，not in SELECT bo.* FROM boys bo WHERE bo.id NOT IN (SELECT boyfriend_idFROM beauty );# 方法二 not EXISTS SELECT bo.* FROM boys bo WHERE NOT EXISTS(SELECT boyfriend_idFROM beauty bWHERE bo.id = b.boyfriend_id );

子查詢練習部分：

# 子查詢練習部分：# 1、查詢和Zlotkey相同部門的員工姓名和工資 use myemployees; # 1）查詢Zlotkey的部門id SELECT department_id FROM employees WHERE last_name = 'Zlotkey';# 2) 查詢部門號等于 1）的查詢結果的 員工姓名和工資 SELECT last_name , salary,department_id FROM employees WHERE department_id = (SELECT department_idFROM employeesWHERE last_name = 'Zlotkey' );# 2、查詢工資比公司平均工資高的員工的 員工號、姓名和工資# 1) 查詢平均工資 SELECT AVG(salary) FROM employees;# 2) 查詢工資高于 1）的查詢結果的 員工的 員工號、姓名和工資 SELECT employee_id , last_name , salary FROM employees WHERE salary > (SELECT AVG(salary)FROM employees );# 3、查詢各部門中工資比本部門平均工資高的員工 的 員工號、姓名和工資# 1) 查詢每個部門的平均工資 SELECT department_id , AVG(salary) FROM employees GROUP BY department_id;# 2) 把1)查詢的結果 與 employees表連接起來，進行篩選 SELECT employee_id , last_name , salary,e.department_id FROM employees e INNER JOIN (SELECT AVG(salary) ag, department_idFROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.department_id WHERE salary > ag_dep.ag# 4、查詢和 姓名中包含字母 u 的員工 在相同部門的員工的員工號和姓名# 1) 查詢姓名中包含字母 u 的員工的部門 SELECT DISTINCT last_name,department_id FROM employees e WHERE last_name LIKE '%u%';# 2)查詢 部門號 = 1）中任意一個的 員工的員工號和姓名 SELECT employee_id ,last_name ,department_id FROM employees WHERE department_id IN(SELECT DISTINCT department_idFROM employeesWHERE last_name LIKE '%u%' )# 5、查詢在部門的location_id 為 1700的部門工作的員工的員工號# 1) 查詢location_id為1700的部門號 SELECT DISTINCT department_id FROM departments WHERE location_id = 1700;# 2) 查詢部門號 = 1) 中任意一個部門號的 員工號 SELECT employee_id FROM employees WHERE department_id = ANY(SELECT DISTINCT department_idFROM departmentsWHERE location_id = 1700 );# 6、查詢管理者是K_ing 的員工編號# 1) 查詢姓名為King的員工編號 SELECT employee_id FROM employees WHERE last_name = 'K_ing';# 2) 查詢哪個員工的manger_id = 1）的查詢結果 SELECT last_name ,salary FROM employees WHERE manager_id IN (SELECT employee_idFROM employeesWHERE last_name = 'K_ing' );# 7、查詢工資最高的員工的姓名，要求first_name 和 last_name顯示為一列，列名為 姓.名# 1) 查詢最高工資 SELECT MAX(salary) FROM employees;# 2) 查詢工資 = 1)的姓.名 SELECT CONCAT(first_name , last_name) "姓.名" FROM employees WHERE salary = (SELECT MAX(salary)FROM employees);

總結

以上是生活随笔為你收集整理的三、MySQL子查询学习笔记（标量子查询、列子查询、行子查询、表子查询 详解）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。