子查詢
出現在select語句中的select語句,稱為子查詢或內查詢。
外部的select查詢語句,稱為主查詢或外查詢。
子查詢分類
按結果集的行列數不同分為4種
-
標量子查詢(結果集只有一行一列)
-
列子查詢(結果集只有一列多行)
-
行子查詢(結果集有一行多列)
-
表子查詢(結果集一般為多行多列)
按子查詢出現在主查詢中的不同位置分
準備測試資料
測試資料比較多,放在我的個人部落格上了。
瀏覽器中開啟連結:
mysql中執行裡面的javacode2018_employees庫部分的指令碼。
成功建立javacode2018_employees庫及5張表,如下:
表名 |
描述 |
departments |
部門表 |
employees |
員工資訊表 |
jobs |
職位資訊表 |
locations |
位置表(部門表中會用到) |
job_grades |
薪資等級表 |
select後面的子查詢
子查詢位於select後面的,僅僅支援標量子查詢。
示例1
查詢每個部門員工個數
SELECT
a.*,
(SELECT count(*)
FROM employees b WHERE b.department_id = a.department_id) AS 員工個數FROM departments a;123456
示例2
查詢員工號=102的部門名稱
SELECT (SELECT a.department_name FROM departments a, employees b WHERE a.department_id = b.department_id AND b.employee_id = 102) AS 部門名;1234
from後面的子查詢
將子查詢的結果集充當一張表,要求必須起別名,否者這個表找不到。
然後將真實的表和子查詢結果表進行連線查詢。
示例1
查詢每個部門平均工資的工資等級
-- 查詢每個部門平均工資SELECT
department_id,
avg(a.salary)FROM employees aGROUP BY a.department_id;-- 薪資等級表SELECT *FROM job_grades;-- 將上面2個結果連線查詢,篩選條件:平均工資 between lowest_sal and highest_sal;SELECT
t1.department_id,
sa AS '平均工資',
t2.grade_levelFROM (SELECT
department_id,
avg(a.salary) sa FROM employees a GROUP BY a.department_id) t1, job_grades t2WHERE
t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;1234567891011121314151617181920212223
執行最後一條結果如下:
mysql> SELECT
t1.department_id,
sa AS '平均工資',
t2.grade_level FROM (SELECT
department_id,
avg(a.salary) sa FROM employees a GROUP BY a.department_id) t1, job_grades t2 WHERE
t1.sa BETWEEN t2.lowest_sal AND t2.highest_sal;+---------------+--------------+-------------+| department_id | 平均工資 | grade_level |+---------------+--------------+-------------+| NULL | 7000.000000 | C || 10 | 4400.000000 | B || 20 | 9500.000000 | C || 30 | 4150.000000 | B || 40 | 6500.000000 | C || 50 | 3475.555556 | B || 60 | 5760.000000 | B || 70 | 10000.000000 | D || 80 | 8955.882353 | C || 90 | 19333.333333 | E || 100 | 8600.000000 | C || 110 | 10150.000000 | D |+---------------+--------------+-------------+12 rows in set (0.00 sec)12345678910111213141516171819202122232425262728
where和having後面的子查詢
where或having後面,可以使用
-
標量子查詢(單行單列行子查詢)
-
列子查詢(單列多行子查詢)
-
行子查詢(多行多列)
特點
-
in(not in):列表中的“任意一個”
-
any或者some:和子查詢返回的“某一個值”比較,比如a>som(10,20,30),a大於子查詢中任意一個即可,a大於子查詢中最小值即可,等同於a>min(10,20,30)。
-
all:和子查詢返回的“所有值”比較,比如a>all(10,20,30),a大於子查詢中所有值,換句話說,a大於子查詢中最大值即可滿足查詢條件,等同於a>max(10,20,30);
- 子查詢的執行優先於主查詢執行,因為主查詢的條件用到了子查詢的結果。
mysql中的in、any、some、all
in,any,some,all分別是子查詢關鍵詞之一。
-
in:in常用於where表示式中,其作用是查詢某個範圍內的資料
-
any和some一樣: 可以與=、>、>=、<、<=、<>結合起來使用,分別表示等於、大於、大於等於、小於、小於等於、不等於其中的任何一個資料。
-
all:可以與=、>、>=、<、<=、<>結合是來使用,分別表示等於、大於、大於等於、小於、小於等於、不等於其中的其中的所有資料。
下文中會經常用到這些關鍵字。
標量子查詢
一般標量子查詢,示例
查詢誰的工資比Abel的高?
/*①查詢abel的工資【改查詢是標量子查詢】*/SELECT salaryFROM employeesWHERE last_name = 'Abel';/*②查詢員工資訊,滿足salary>①的結果*/SELECT *FROM employees aWHERE a.salary > (SELECT salary FROM employees WHERE last_name = 'Abel');1234567891011
多個標量子查詢,示例
返回job_id與141號員工相同,salary比143號員工多的員工、姓名、job_id和工資
/*返回job_id與141號員工相同,salary比143號員工多的員工、姓名、job_id和工資*//*①查詢141號員工的job_id*/SELECT job_idFROM employeesWHERE employee_id = 141;/*②查詢143好員工的salary*/SELECT salaryFROM employeesWHERE employee_id = 143;/*③查詢員工的姓名、job_id、工資,要求job_id=① and salary>②*/SELECT
a.last_name 姓名,
a.job_id,
a.salary 工資FROM employees aWHERE a.job_id = (SELECT job_id FROM employees WHERE employee_id = 141)
AND
a.salary > (SELECT salary FROM employees WHERE employee_id = 143);12345678910111213141516171819202122
子查詢+分組函式,示例
查詢最低工資大於50號部門最低工資的部門id和其最低工資【having】
/*查詢最低工資大於50號部門最低工資的部門id和其最低工資【having】*//*①查詢50號部門的最低工資*/SELECT min(salary)FROM employeesWHERE department_id = 50;/*②查詢每個部門的最低工資*/SELECT
min(salary),
department_idFROM employeesGROUP BY department_id;/*③在②的基礎上篩選,滿足min(salary)>①*/SELECT
min(a.salary) minsalary,
department_idFROM employees aGROUP BY a.department_idHAVING min(a.salary) > (SELECT min(salary)
FROM employees WHERE department_id = 50);1234567891011121314151617181920
錯誤的標量子查詢,示例
將上面的示例③中子查詢語句中的min(salary)改為salary,執行效果如下:
mysql> SELECT
min(a.salary) minsalary,
department_id FROM employees a GROUP BY a.department_id HAVING min(a.salary) > (SELECT salary FROM employees WHERE department_id = 500000);ERROR 1242 (21000): Subquery returns more than 1 row123456789
錯誤提示:子查詢返回的結果超過了1行記錄。
說明:上面的子查詢只支援最多一列一行記錄。
列子查詢
列子查詢需要搭配多行運算子使用:in(not in)、any/some、all。
為了提升效率,最好去重一下distinct關鍵字。
示例1
返回location_id是1400或1700的部門中的所有員工姓名
/*返回location_id是1400或1700的部門中的所有員工姓名*//*方式1*//*①查詢location_id是1400或1700的部門編號*/SELECT DISTINCT department_idFROM departmentsWHERE location_id IN (1400, 1700);/*②查詢員工姓名,要求部門是①列表中的某一個*/SELECT a.last_nameFROM employees aWHERE a.department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700));/*方式2:使用any實現*/SELECT a.last_nameFROM employees aWHERE a.department_id = ANY (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700));/*擴充,下面與not in等價*/SELECT a.last_nameFROM employees aWHERE a.department_id <> ALL (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700));123456789101112131415161718192021222324252627
示例2
返回其他工種中比job_id為’IT_PROG’工種任意工資低的員工的員工號、姓名、job_id、salary
/*返回其他工種中比job_id為'IT_PROG'工種任一工資低的員工的員工號、姓名、job_id、salary*//*①查詢job_id為'IT_PROG'部門任-工資*/SELECT DISTINCT salaryFROM employeesWHERE job_id = 'IT_PROG';/*②查詢員工號、姓名、job_id、salary,slary<①的任意一個*/SELECT
last_name,
employee_id,
job_id,
salaryFROM employeesWHERE salary < ANY (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';/*或者*/SELECT
last_name,
employee_id,
job_id,
salaryFROM employeesWHERE salary < (SELECT max(salary)
FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';123456789101112131415161718192021222324252627
示例3
返回其他工種中比job_id為’IT_PROG’部門所有工資低的員工的員工號、姓名、job_id、salary
/*返回其他工種中比job_id為'IT_PROG'部門所有工資低的員工的員工號、姓名、job_id、salary*/SELECT
last_name,
employee_id,
job_id,
salaryFROM employeesWHERE salary < ALL (SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';/*或者*/SELECT
last_name,
employee_id,
job_id,
salaryFROM employeesWHERE salary < (SELECT min(salary)
FROM employees WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';123456789101112131415161718192021
行子查詢(結果集一行多列)
示例
查詢員工編號最小並且工資最高的員工資訊,3種方式。
/*查詢員工編號最小並且工資最高的員工資訊*//*①查詢最小的員工編號*/SELECT min(employee_id)FROM employees;/*②查詢最高工資*/SELECT max(salary)FROM employees;/*③方式1:查詢員工資訊*/SELECT *FROM employees aWHERE a.employee_id = (SELECT min(employee_id)
FROM employees)
AND salary = (SELECT max(salary)
FROM employees);/*方式2*/SELECT *FROM employees aWHERE (a.employee_id, a.salary) = (SELECT
min(employee_id),
max(salary)
FROM employees);/*方式3*/SELECT *FROM employees aWHERE (a.employee_id, a.salary) in (SELECT
min(employee_id),
max(salary)
FROM employees);1234567891011121314151617181920212223242526272829
方式1比較常見,方式2、3更簡潔。
exists後面(也叫做相關子查詢)
語法:exists(玩轉的查詢語句)。
-
exists查詢結果:1或0,exists查詢的結果用來判斷子查詢的結果集中是否有值。
-
一般來說,能用exists的子查詢,絕對都能用in代替,所以exists用的少。
-
和前面的查詢不同,這先執行主查詢,然後主查詢查詢的結果,在根據子查詢進行過濾,子查詢中涉及到主查詢中用到的欄位,所以叫相關子查詢。
示例1
簡單示例
mysql> SELECT exists(SELECT employee_id FROM employees WHERE salary = 300000) AS 'exists返回1或者0';+----------------------+| exists返回1或者0 |+----------------------+| 0 |+----------------------+1 row in set (0.00 sec)123456789
示例2
查詢所有員工的部門名稱
/*exists入門案例*/SELECT exists(SELECT employee_id FROM employees WHERE salary = 300000) AS 'exists返回1或者0';/*查詢所有員工部門名*/SELECT department_nameFROM departments aWHERE exists(SELECT 1
FROM employees b WHERE a.department_id = b.department_id);/*使用in實現*/SELECT department_nameFROM departments aWHERE a.department_id IN (SELECT department_id FROM employees);1234567891011121314151617
示例3
查詢沒有員工的部門
/*查詢沒有員工的部門*//*exists實現*/SELECT *FROM departments aWHERE NOT exists(SELECT 1
FROM employees b WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL);/*in的方式*/SELECT *FROM departments aWHERE a.department_id NOT IN (SELECT department_id FROM employees b WHERE b.department_id IS NOT NULL);12345678910111213
上面指令碼中有b.department_id IS NOT NULL,為什麼,有大坑,向下看。
NULL的大坑
使用in的方式查詢沒有員工的部門,如下:
SELECT *FROM departments aWHERE a.department_id NOT IN (SELECT department_id FROM employees b);1234
執行結果:
mysql> SELECT *
-> FROM departments a -> WHERE a.department_id NOT IN (SELECT department_id -> FROM employees b);Empty set (0.00 sec)12345
in的情況下,子查詢中列的值為NULL的時候,外查詢的結果為空。
建議:建表是,列不允許為空。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30239065/viewspace-2725566/,如需轉載,請註明出處,否則將追究法律責任。