玩轉Mysql系列 – 第12篇:子查詢(非常重要,高手必備)

  • 2019 年 10 月 6 日
  • 筆記

這是Mysql系列第12篇。

環境:mysql5.7.25,cmd命令中進行演示。

本章節非常重要。

子查詢

出現在select語句中的select語句,稱為子查詢或內查詢。

外部的select查詢語句,稱為主查詢或外查詢。

子查詢分類

按結果集的行列數不同分為4種

  • 標量子查詢(結果集只有一行一列)
  • 列子查詢(結果集只有一列多行)
  • 行子查詢(結果集有一行多列)
  • 表子查詢(結果集一般為多行多列)

按子查詢出現在主查詢中的不同位置分

  • select後面:僅僅支援標量子查詢。
  • from後面:支援表子查詢。
  • where或having後面:支援標量子查詢(單列單行)、列子查詢(單列多行)、行子查詢(多列多行)
  • exists後面(即相關子查詢):表子查詢(多行、多列)

準備測試數據

測試數據比較多,放在我的個人部落格上了。

瀏覽器中打開鏈接:http://www.itsoku.com/article/209

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;  

示例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 部門名;  

from後面的子查詢

將子查詢的結果集充當一張表,要求必須起別名,否者這個表找不到。 然後將真實的表和子查詢結果表進行連接查詢。

示例1

查詢每個部門平均工資的工資等級

-- 查詢每個部門平均工資  SELECT    department_id,    avg(a.salary)  FROM employees a  GROUP BY a.department_id;    -- 薪資等級表  SELECT *  FROM job_grades;    -- 將上面2個結果連接查詢,篩選條件:平均工資 between lowest_sal and highest_sal;  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;  

運行最後一條結果如下:

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)  

where和having後面的子查詢

where或having後面,可以使用

  1. 標量子查詢(單行單列行子查詢)
  2. 列子查詢(單列多行子查詢)
  3. 行子查詢(多行多列)

特點

  1. 子查詢放在小括弧內。
  2. 子查詢一般放在條件的右側。
  3. 標量子查詢,一般搭配著單行操作符使用,多行操作符 >、<、>=、<=、=、<>、!=
  4. 列子查詢,一般搭配著多行操作符使用

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);

  1. 子查詢的執行優先於主查詢執行,因為主查詢的條件用到了子查詢的結果。

mysql中的in、any、some、all

in,any,some,all分別是子查詢關鍵詞之一。

in:in常用於where表達式中,其作用是查詢某個範圍內的數據

any和some一樣: 可以與=、>、>=、<、<=、<>結合起來使用,分別表示等於、大於、大於等於、小於、小於等於、不等於其中的任何一個數據。

all:可以與=、>、>=、<、<=、<>結合是來使用,分別表示等於、大於、大於等於、小於、小於等於、不等於其中的其中的所有數據。

下文中會經常用到這些關鍵字。

標量子查詢

一般標量子查詢,示例

查詢誰的工資比Abel的高?

/*①查詢abel的工資【改查詢是標量子查詢】*/  SELECT salary  FROM employees  WHERE last_name = 'Abel';    /*②查詢員工資訊,滿足salary>①的結果*/  SELECT *  FROM employees a  WHERE a.salary > (SELECT salary                    FROM employees                    WHERE last_name = 'Abel');  
多個標量子查詢,示例

返回job_id與141號員工相同,salary比143號員工多的員工、姓名、job_id和工資

/*返回job_id與141號員工相同,salary比143號員工多的員工、姓名、job_id和工資*/  /*①查詢141號員工的job_id*/  SELECT job_id  FROM employees  WHERE employee_id = 141;  /*②查詢143好員工的salary*/  SELECT salary  FROM employees  WHERE employee_id = 143;  /*③查詢員工的姓名、job_id、工資,要求job_id=① and salary>②*/  SELECT    a.last_name 姓名,    a.job_id,    a.salary    工資  FROM employees a  WHERE a.job_id = (SELECT job_id                    FROM employees                    WHERE employee_id = 141)        AND        a.salary > (SELECT salary                    FROM employees                    WHERE employee_id = 143);  
子查詢+分組函數,示例

查詢最低工資大於50號部門最低工資的部門id和其最低工資【having】

/*查詢最低工資大於50號部門最低工資的部門id和其最低工資【having】*/  /*①查詢50號部門的最低工資*/  SELECT min(salary)  FROM employees  WHERE department_id = 50;  /*②查詢每個部門的最低工資*/  SELECT    min(salary),    department_id  FROM employees  GROUP BY department_id;  /*③在②的基礎上篩選,滿足min(salary)>①*/  SELECT    min(a.salary) minsalary,    department_id  FROM employees a  GROUP BY a.department_id  HAVING min(a.salary) > (SELECT min(salary)                          FROM employees                          WHERE department_id = 50);
錯誤的標量子查詢,示例

將上面的示例③中子查詢語句中的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 row

錯誤提示:子查詢返回的結果超過了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_id  FROM departments  WHERE location_id IN (1400, 1700);    /*②查詢員工姓名,要求部門是①列表中的某一個*/  SELECT a.last_name  FROM employees a  WHERE a.department_id IN (SELECT DISTINCT department_id                            FROM departments                            WHERE location_id IN (1400, 1700));    /*方式2:使用any實現*/  SELECT a.last_name  FROM employees a  WHERE a.department_id = ANY (SELECT DISTINCT department_id                               FROM departments                               WHERE location_id IN (1400, 1700));    /*拓展,下面與not in等價*/  SELECT a.last_name  FROM employees a  WHERE a.department_id <> ALL (SELECT DISTINCT department_id                               FROM departments                               WHERE location_id IN (1400, 1700));
示例2

返回其他工種中比job_id為'IT_PROG'工種任意工資低的員工的員工號、姓名、job_id、salary

/*返回其他工種中比job_id為'IT_PROG'工種任一工資低的員工的員工號、姓名、job_id、salary*/  /*①查詢job_id為'IT_PROG'部門任-工資*/  SELECT DISTINCT salary  FROM employees  WHERE job_id = 'IT_PROG';    /*②查詢員工號、姓名、job_id、salary,slary<①的任意一個*/  SELECT    last_name,    employee_id,    job_id,    salary  FROM employees  WHERE salary < ANY (SELECT DISTINCT salary                      FROM employees                      WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';    /*或者*/  SELECT    last_name,    employee_id,    job_id,    salary  FROM employees  WHERE salary < (SELECT max(salary)                  FROM employees                  WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
示例3

返回其他工種中比job_id為'IT_PROG'部門所有工資低的員工的員工號、姓名、job_id、salary

/*返回其他工種中比job_id為'IT_PROG'部門所有工資低的員工的員工號、姓名、job_id、salary*/  SELECT    last_name,    employee_id,    job_id,    salary  FROM employees  WHERE salary < ALL (SELECT DISTINCT salary                      FROM employees                      WHERE 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 employees                  WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

行子查詢(結果集一行多列)

示例

查詢員工編號最小並且工資最高的員工資訊,3種方式。

/*查詢員工編號最小並且工資最高的員工資訊*/  /*①查詢最小的員工編號*/  SELECT min(employee_id)  FROM employees;  /*②查詢最高工資*/  SELECT max(salary)  FROM employees;  /*③方式1:查詢員工資訊*/  SELECT *  FROM employees a  WHERE a.employee_id = (SELECT min(employee_id)                         FROM employees)        AND salary = (SELECT max(salary)                      FROM employees);    /*方式2*/  SELECT *  FROM employees a  WHERE (a.employee_id, a.salary) = (SELECT                                       min(employee_id),                                       max(salary)                                     FROM employees);  /*方式3*/  SELECT *  FROM employees a  WHERE (a.employee_id, a.salary) in (SELECT                                       min(employee_id),                                       max(salary)                                     FROM employees);

方式1比較常見,方式2、3更簡潔。

exists後面(也叫做相關子查詢)

  1. 語法:exists(玩轉的查詢語句)。
  2. exists查詢結果:1或0,exists查詢的結果用來判斷子查詢的結果集中是否有值。
  3. 一般來說,能用exists的子查詢,絕對都能用in代替,所以exists用的少。
  4. 和前面的查詢不同,這先執行主查詢,然後主查詢查詢的結果,在根據子查詢進行過濾,子查詢中涉及到主查詢中用到的欄位,所以叫相關子查詢。
示例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)
示例2

查詢所有員工的部門名稱

/*exists入門案例*/  SELECT exists(SELECT employee_id                FROM employees                WHERE salary = 300000) AS 'exists返回1或者0';    /*查詢所有員工部門名*/  SELECT department_name  FROM departments a  WHERE exists(SELECT 1               FROM employees b               WHERE a.department_id = b.department_id);    /*使用in實現*/  SELECT department_name  FROM departments a  WHERE a.department_id IN (SELECT department_id                            FROM employees);
示例3

查詢沒有員工的部門

/*查詢沒有員工的部門*/  /*exists實現*/  SELECT *  FROM departments a  WHERE 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 a  WHERE a.department_id NOT IN (SELECT department_id                                FROM employees b                                WHERE b.department_id IS NOT NULL);

上面腳本中有b.department_id IS NOT NULL,為什麼,有大坑,向下看。

NULL的大坑

示例1

使用in的方式查詢沒有員工的部門,如下:

SELECT *  FROM departments a  WHERE a.department_id NOT IN (SELECT department_id                                FROM employees b);

運行結果:

mysql> SELECT *      -> FROM departments a      -> WHERE a.department_id NOT IN (SELECT department_id      ->                               FROM employees b);  Empty set (0.00 sec)

in的情況下,子查詢中列的值為NULL的時候,外查詢的結果為空。

建議:建表是,列不允許為空。

總結

  1. 本文中講解了常見的子查詢,請大家務必多練習
  2. 注意in、any、some、any的用法
  3. 欄位為空的時候,in查詢有大坑,這個要注意
  4. 建議創建表的時候,列不允許為空