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

2021-03-02 路人甲Java

打算提升sql技能的，可以加我微信itsoku，帶你成為sql高手。

這是Mysql系列第12篇。

環境：mysql5.7.25，cmd命令中進行演示。

本章節非常重要。

子查詢

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

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

子查詢分類 按結果集的行列數不同分為4種

標量子查詢（結果集只有一行一列）

列子查詢（結果集只有一列多行）

行子查詢（結果集有一行多列）

表子查詢（結果集一般為多行多列）

按子查詢出現在主查詢中的不同位置分準備測試數據

測試數據比較多，放在我的個人博客上了。

瀏覽器中打開連結：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後面，可以使用

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

列子查詢（單列多行子查詢）

行子查詢（多行多列）

特點

子查詢放在小括號內。

子查詢一般放在條件的右側。

標量子查詢，一般搭配著單行操作符使用，多行操作符   >、<、>=、<=、=、<>、!=

列子查詢，一般搭配著多行操作符使用

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的高？


SELECT salary
FROM employees
WHERE last_name = 'Abel';


SELECT *
FROM employees a
WHERE a.salary > (SELECT salary
                  FROM employees
                  WHERE last_name = 'Abel');

多個標量子查詢，示例

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



SELECT job_id
FROM employees
WHERE employee_id = 141;

SELECT salary
FROM employees
WHERE employee_id = 143;

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】



SELECT min(salary)
FROM employees
WHERE department_id = 50;

SELECT
  min(salary),
  department_id
FROM employees
GROUP BY department_id;

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的部門中的所有員工姓名




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


SELECT a.last_name
FROM employees a
WHERE a.department_id = ANY (SELECT DISTINCT department_id
                             FROM departments
                             WHERE location_id IN (1400, 1700));


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



SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';


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


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;

SELECT *
FROM employees a
WHERE a.employee_id = (SELECT min(employee_id)
                       FROM employees)
      AND salary = (SELECT max(salary)
                    FROM employees);


SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) = (SELECT
                                     min(employee_id),
                                     max(salary)
                                   FROM employees);

SELECT *
FROM employees a
WHERE (a.employee_id, a.salary) in (SELECT
                                     min(employee_id),
                                     max(salary)
                                   FROM employees);

方式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)

示例2

查詢所有員工的部門名稱


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


SELECT department_name
FROM departments a
WHERE a.department_id IN (SELECT department_id
                          FROM employees);

示例3

查詢沒有員工的部門



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

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的時候，外查詢的結果為空。

建議：建表是，列不允許為空。

總結

本文中講解了常見的子查詢，請大家務必多練習

注意in、any、some、any的用法

欄位為空的時候，in查詢有大坑，這個要注意

建議創建表的時候，列不允許為空

Mysql系列目錄

第1篇：mysql基礎知識

第2篇：詳解mysql數據類型（重點）

第3篇：管理員必備技能(必須掌握)

第4篇：DDL常見操作

第5篇：DML操作匯總（insert,update,delete）

第6篇：select查詢基礎篇

第7篇：玩轉select條件查詢，避免採坑

第8篇：詳解排序和分頁(order by & limit)

第9篇：分組查詢詳解（group by & having）

第10篇：常用的幾十個函數詳解

第11篇：深入了解連接查詢及原理

mysql系列大概有20多篇，喜歡的請關注一下，歡迎大家加我微信itsoku或者留言交流mysql相關技術!

相關焦點

  • 玩轉Mysql系列 - 第6篇:select查詢基礎篇
    這是Mysql系列第6篇。環境：mysql5.7.25，cmd命令中進行演示。DQL(Data QueryLanguage)：數據查詢語言，通俗點講就是從資料庫獲取數據的，按照DQL的語法給資料庫發送一條指令，資料庫將按需求返回數據。DQL分多篇來說，本文屬於第1篇。
  • 玩轉Mysql系列 - 第25篇:sql中where條件在資料庫中提取與應用淺析
    Mysql系列的目標是：通過這個系列從入門到全面掌握一個高級開發所需要的全部技能。歡迎大家加我微信itsoku一起交流java、算法、資料庫相關技術。這是Mysql系列第25篇。p=577Mysql系列目錄 第1篇：mysql基礎知識 第2篇：詳解mysql數據類型（重點） 第3篇：管理員必備技能(必須掌握) 第4篇：DDL常見操作 第5篇：DML
  • 玩轉Mysql系列 - 第7篇:詳解排序和分頁(order by & limit),及存在的坑
    打算提升sql技能的，可以加我微信itsoku，帶你成為sql高手。這是Mysql系列第7篇。環境：mysql5.7.25，cmd命令中進行演示。代碼中被[]包含的表示可選，|符號分開的表示可選其一。本章內容 詳解排序查詢詳解limitlimit存在的坑分頁查詢中的坑排序查詢（order by） 電商中：我們想查看今天所有成交的訂單，按照交易額從高到低排序，此時我們可以使用資料庫中的排序功能來完成。
  • MySQL全面瓦解—子查詢和組合查詢
    概述 　　子查詢是SQL查詢中的重要一塊，是我們基於多表之間進行數據聚合和判斷的一種手段，使得我們的處理複雜數據更加的便捷，這一節我們主要來了解一下子查詢。4、多行多列（表子查詢）：查詢返回的結果是一張臨時表；　　按子查詢位置區分 　　select後的子查詢：僅僅支持標量子查詢，即只能返回一個單值數據。
  • Java從零開始學 - 第64篇:分組查詢詳解(group by & having)
    這是Mysql系列第9篇。環境：mysql5.7.25，cmd命令中進行演示。本篇內容 分組查詢語法聚合函數單欄位分組多欄位分組分組前篩選數據分組後篩選數據where和having的區別分組後排序where & group by & having & order by & limit 一起協作mysql分組中的坑
  • 拿什麼拯救你,我的MySQL子查詢
    mysql的子查詢一直被人所詬病，主要原因是mysql子查詢奉行的是「由外到內」的政策。
  • MySQL 這該死的 「IN (子查詢)」
  • 06-mysql基礎-mysql中的DQL-子查詢
    在上一期05-mysql基礎-mysql中的DQL-連接查詢的推文中我們學習了mysql的連接查詢知識，本期我們將繼續學習DQL中的子查詢。子查詢介紹含義：出現在其他語句中的select語句，成為子查詢或內查詢外部的查詢語句，成為主查詢或外查詢分類：按子查詢出現的位置： select後面 僅支持標量子查詢 from後面 支持表子查詢 where或having後面 支持標量子查詢(*) 列子查詢(*) 行子查詢
  • Java從零開始學 - 第73篇:流程控制語句(高手進階)
    這是Mysql系列第18篇。環境：mysql5.7.25，cmd命令中進行演示。代碼中被[]包含的表示可選，|符號分開的表示可選其一。上一篇存儲過程&自定義函數，對存儲過程和自定義函數做了一個簡單的介紹，但是如何能夠寫出複雜的存儲過程和函數呢？
  • mysql的查詢、子查詢及連接查詢
    子查詢1、where型子查詢（把內層查詢結果當作外層查詢的比較條件）FROM goods WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id);2、from型子查詢(把內層的查詢結果當成臨時表，供外層sql再次查詢。
  • Mysql使用exists的子查詢
    形式：where exists ( 任何子查詢 )含義：該子查詢如果「有數據結果」， 則該exists()的結果為「true」>該子查詢如果「沒有數據結果」，則該exists()的結果為「false」特別注意：1，該子查詢，如果其僅僅是為了得到「有沒有數據」的結果，則通常此時對主查詢就失去應用意義；2，實際上，該子查詢，通常都需要在子查詢（內部）中來使用主查詢（外部）的某些欄位作為條件數據，這樣才能具有一定的實用意義
  • 原創MySql專題之第二篇--連接查詢和子查詢
    連接查詢和子查詢一、連接查詢1.1 概念
  • 技術分享 | MySQL 子查詢優化
    有這麼一個 SQL，外查詢 where 子句的 bizCustomerIncoming_id 欄位，和子查詢 where 字句的 cid 欄位都有高效索引，為什麼這個 SQL 執行的非常慢，需要全表掃描？
  • Mysql 手工注入【常規union查詢篇】
    兩個操作xml數據的函數,通常我們可以利用這種特性一句話就把所有的數據全部查出來,顯錯注入常用extractvalue(xml_doc,xpath) 意思就是從指定xml文檔中查詢指定的字符串updatexml
  • 從Web查詢資料庫之PHP與MySQL篇
    這與處理函數非常類似 mysqli的大多數函數都有面向對象接口和過程接口，二者的差異則在於過程版本的函數名稱以mysqli_開頭，同時要求傳入mysqli_connect()函數獲得的資源句柄。 對於這個規則來說，數據可連接是一個異常，因為它是由mysqli對象的構造函數來創建的。
  • MySQL使用子查詢教程
    #MYSQL#這是我MyySQL教程的第四篇了，可能對於一些大神來說這些都是小兒科，但是我還是相信這些東西會對一些人有幫助的，本篇主要會介紹上面是子查詢以及如何使用它們。大概會從，什麼是子查詢，利用子查詢進行數據篩選，還有就是把子查詢作為計算欄位使用。1.什麼是子查詢以及利用子查詢進行數據過濾在使用子查詢之前你要檢查一下你的餓MySQL版本了。最少4.1，這個子查詢也是在4.1版本之後才引入的功能。
  • 如何查看 sql 查詢是否用到索引 ( mysql )
    用於區別普通查詢、聯合查詢、子查詢等的複雜查詢。table: 輸出結果集的表 顯示這一步所訪問資料庫中表名稱（顯示這一行的數據是關於哪張表的），有時不是真實的表名字，可能是簡稱，例如上面的e，d，也可能是第幾步執行的結果的簡稱type:對表訪問方式，表示MySQL在表中找到所需行的方式，又稱「訪問類型」。
  • Java從零開始學 - 第65篇:常用的幾十個函數詳解
    這是Mysql系列第10篇。環境：mysql5.7.25，cmd命令中進行演示。也可以對pos使用負值，在這種情況下，子字符串的開頭是字符串末尾的pos字符，而不是開頭。在這個函數的任何形式中pos可以使用負值。對於所有形式的substring()，從中提取子串的字符串中第一個字符的位置被認為是1。
  • 入門MySQL——查詢語法練習
    前言：前面幾篇文章為大家介紹了DML以及DDL語句的使用方法，本篇文章將主要講述常用的查詢語法。
  • 「MySQL系列」分析Sql執行時間及查詢執行計劃(附資料庫和一千萬數據)
    Netty系列文章先告一段落了，因為這些知識已經滿足我當前工作的需求。後續項目中遇到問題和問題解決，會繼續更新Netty相關文章。哈哈哈 囉嗦的話有點多。接下來我將更新MySQL系列的文章，主要涉及MySQL的索引、調優、整體架構、引擎、分庫分表、擴容、索引的深入探究等等。感興趣的可以持續關注，不感興趣的略過。