<ul id="uqioc"></ul>

當前位置：首頁 > sql > 正文內容

關于MySQL子查詢

hxing6411年前 (2024-01-11)sql7525

子查詢指一個查詢語句嵌套在另一個查詢語句內部的查詢，這個特性從MySQL4.1開始引入。

SQL中子查詢的使用大大增強了SELECT查詢的能力，因為很多時候查詢需要從結果集中獲取數據，或者需要從同一個表中先計算出一個數據結果，然后與這個數據結果(可能是單行，也可能是多行)進行比較。

1.需求分析與問題解決

1.1 實際問題

查詢出employees表中，比Abel工資高的員工信息。

-- 方式1多表查詢 自連接
SELECT
    e2.`last_name`,
    e2.`salary`
FROM
    employees e1,
    employees e2
WHERE e2.`salary` > e1.`salary`
    AND e1.`last_name` = 'Abel';-- 相當于自連接e1表(一張只有一行數據的employees表)
-- 方式2 子查詢
SELECT
    last_name,
    salary
FROM
    employees
WHERE salary >
    (SELECT
        salary
    FROM
        employees
    WHERE last_name = 'Abel');

1.2 子查詢的基本使用

子查詢的基本語法結構。

SELECT select_list
FROM table_name
WHERE expr operator
(SELECT select_list FROM table_name);

子查詢(內查詢)在主查詢之前一次執行完成。

子查詢的結果被主查詢(外查詢)使用。

注意事項

子查詢要包含在括號內。
將子查詢放在比較條件的右側。
單行操作符對于單行子查詢，多行操作符對于多行子查詢。

1.3 子查詢的分類

分類方式1:

按照內查詢的結果返回一條還是多條記錄，將子查詢分為單行子查詢，多行子查詢。

分類方式2:

我們按內查詢是否被執行多次，將子查詢劃分為相關(或關聯)子查詢和不相關(或非關聯)子查詢。

子查詢從數據表中查詢了數據結果，如果這個數據結果只執行一次，然后這個數據結果作為主查詢的條件進行執行，那么這樣的子查詢叫做不相關子查詢。

如果，子查詢需要執行多次，即采用循環的方式，先從外部查詢開始，每次都傳入子查詢進行查詢，然后再將結果反饋給外部，這種嵌套的方式，就稱為相關子查詢。

2.單行子查詢

2.1 單行比較操作符

2.2 代碼示例

題目：查詢工資大于149號員工工資的員工的信息

mysql> SELECT last_name,employee_id,salary
    -> FROM employees
    -> WHERE salary > (
    ->                 SELECT salary FROM employees WHERE employee_id = 149
    ->                 );
+-----------+-------------+----------+
| last_name | employee_id | salary   |
+-----------+-------------+----------+
| King      |         100 | 24000.00 |
| Kochhar   |         101 | 17000.00 |
| De Haan   |         102 | 17000.00 |
...
| Hartstein |         201 | 13000.00 |
| Higgins   |         205 | 12000.00 |
+-----------+-------------+----------+
13 rows in set (0.00 sec)

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

mysql> SELECT last_name,job_id,salary
    -> FROM employees
    -> WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 149)
    -> AND salary > (SELECT salary FROM employees WHERE employee_id = 143);
+-----------+--------+----------+
| last_name | job_id | salary   |
+-----------+--------+----------+
| Russell   | SA_MAN | 14000.00 |
| Partners  | SA_MAN | 13500.00 |
| Errazuriz | SA_MAN | 12000.00 |
| Cambrault | SA_MAN | 11000.00 |
| Zlotkey   | SA_MAN | 10500.00 |
+-----------+--------+----------+
5 rows in set (0.00 sec)

題目 :返回公司工資最少的員工的last_name,job_id和salary

mysql> SELECT last_name,job_id,salary
    -> FROM employees
    -> WHERE salary = (SELECT MIN(salary) FROM employees);
+-----------+----------+---------+
| last_name | job_id   | salary  |
+-----------+----------+---------+
| Olson     | ST_CLERK | 2100.00 |
+-----------+----------+---------+
1 row in set (0.00 sec)

擴展,如何求出工資倒數第二的工資的員工的last_name,job_id和salary

mysql> SELECT last_name,job_id,salary
    -> FROM employees
    -> WHERE salary = (SELECT MIN(salary) FROM employees WHERE salary != (SELECT MIN(salary) FROM employees));
+------------+----------+---------+
| last_name  | job_id   | salary  |
+------------+----------+---------+
| Markle     | ST_CLERK | 2200.00 |
| Philtanker | ST_CLERK | 2200.00 |
+------------+----------+---------+
2 rows in set (0.00 sec)

來源，這是我再leetcode刷到過的sql的中等題目

思路解析，WHERE salary != (SELECT MIN(salary) FROM employees) 就是，過濾掉倒數第一的工資，排除倒數第一的工資的倒數第一的工資就是倒數第二，當時覺得這個思路，特別好，記憶比較深刻。

題目，查詢與141號或174號員工的manager_id和department_id相同的其他員工的employee_id，manager_id，department_id

題目其實是多行子查詢(返回結果是多行的)

不成對比較

mysql> SELECT employee_id,manager_id,department_id
    -> FROM employees
    -> WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (141,174))
    -> AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (141,174))
    -> AND employee_id NOT IN(141,174);
+-------------+------------+---------------+
| employee_id | manager_id | department_id |
+-------------+------------+---------------+
|         142 |        124 |            50 |
|         143 |        124 |            50 |
|         144 |        124 |            50 |
|         196 |        124 |            50 |
|         197 |        124 |            50 |
|         198 |        124 |            50 |
|         199 |        124 |            50 |
|         175 |        149 |            80 |
|         176 |        149 |            80 |
|         177 |        149 |            80 |
|         179 |        149 |            80 |
+-------------+------------+---------------+
11 rows in set (0.00 sec)

成對比較(使用的條件很苛刻，用的很少)

mysql> SELECT employee_id,manager_id,department_id
    -> FROM employees
    -> WHERE (manager_id,department_id) IN (SELECT manager_id,department_id FROM employees WHERE employee_id IN (141,174))
    -> AND employee_id NOT IN(141,174);
+-------------+------------+---------------+
| employee_id | manager_id | department_id |
+-------------+------------+---------------+
|         142 |        124 |            50 |
|         143 |        124 |            50 |
|         144 |        124 |            50 |
|         196 |        124 |            50 |
|         197 |        124 |            50 |
|         198 |        124 |            50 |
|         199 |        124 |            50 |
|         175 |        149 |            80 |
|         176 |        149 |            80 |
|         177 |        149 |            80 |
|         179 |        149 |            80 |
+-------------+------------+---------------+
11 rows in set (0.00 sec)

2.3 HAVING中的子查詢

首先執行子查詢
向主查詢中的HAVING子句返回結果

題目查詢最低工資大于50號部門最低工資的部門id和其最低工資

mysql> SELECT department_id,MIN(salary)
    -> FROM employees
    -> GROUP BY department_id
    -> HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);
+---------------+-------------+
| department_id | MIN(salary) |
+---------------+-------------+
|          NULL |     7000.00 |
|            10 |     4400.00 |
|            20 |     6000.00 |
|            30 |     2500.00 |
|            40 |     6500.00 |
|            60 |     4200.00 |
|            70 |    10000.00 |
|            80 |     6100.00 |
|            90 |    17000.00 |
|           100 |     6900.00 |
|           110 |     8300.00 |
+---------------+-------------+
11 rows in set (0.00 sec)

2.4 CASE中的子查詢

題目：顯示員工的employee_id,last_name和location。其中，若員工department_id與location_id為1800的department_id相同，則location為’Canada’，其余則為’USA’。

SELECT
    employee_id,
    last_name,
    (
        CASE
            department_id
            WHEN
            (SELECT
                department_id
            FROM
                departments
            WHERE location_id = 1800)
            THEN 'Canadad'
            ELSE 'USA'
        END
    ) AS 'location'
FROM
    employees

2.5 子查詢中的空值現象

出現原因是，子查詢中為查到數據，也可能是子查詢中查到的數據中包含null(多行子查詢中出現空值現象的原因)

mysql> SELECT last_name,salary,job_id
    -> FROM employees
    -> WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'wind');
Empty set (0.00 sec)

2.6 非法使用子查詢

mysql> SELECT last_name,salary,job_id
    -> FROM employees
    -> WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id);
ERROR 1242 (21000): Subquery returns more than 1 row

多行子查詢使用單行比較符

3.多行子查詢

也稱為集合比較子查詢
子查詢返回多行
使用多行比較操作符

3.1 多行比較操作符

3.2 代碼示例

題目返回其它job_id中比job_id為‘IT_PROG’部門任一工資低的員工的員工號、姓名、job_id 以及salary

mysql> SELECT employee_id,last_name,job_id,salary
    -> FROM employees
    -> WHERE salary < ANY (SELECT salary FROM employees
    -> WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
+-------------+-------------+------------+---------+
| employee_id | last_name   | job_id     | salary  |
+-------------+-------------+------------+---------+
...
|         202 | Fay         | MK_REP     | 6000.00 |
|         203 | Mavris      | HR_REP     | 6500.00 |
|         206 | Gietz       | AC_ACCOUNT | 8300.00 |
+-------------+-------------+------------+---------+
76 rows in set (0.00 sec)
-- 上述sql語句也可以轉化為下面這條sql語句，是因為比任一工資低的條件，可以轉化為比最大值低即可
-- 這是因為比除最大值外的值低的條件，會隱形的包含在比最大值低的情況下
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

題目：返回其它job_id中比job_id為‘IT_PROG’部門所有工資都低的員工的員工號、姓名、job_id以及salary

mysql> SELECT employee_id,last_name,job_id,salary
    -> FROM employees
    -> WHERE salary < ALL (SELECT salary FROM employees
    -> WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';
+-------------+-------------+----------+---------+
| employee_id | last_name   | job_id   | salary  |
+-------------+-------------+----------+---------+
|         115 | Khoo        | PU_CLERK | 3100.00 |
|         116 | Baida       | PU_CLERK | 2900.00 |
|         117 | Tobias      | PU_CLERK | 2800.00 |
...
|         197 | Feeney      | SH_CLERK | 3000.00 |
|         198 | OConnell    | SH_CLERK | 2600.00 |
|         199 | Grant       | SH_CLERK | 2600.00 |
+-------------+-------------+----------+---------+
44 rows in set (0.00 sec)
-- 上述sql語句也可以轉化成下面這條SQL語句，是因為比所有的工資都低的員工，可以轉成比最小值低即可
-- 這是因為比除最小值的值低的條件，會隱形的包含在比最小值低的情況下
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (SELECT MIN(salary) FROM employees
WHERE job_id = 'IT_PROG') AND job_id != 'IT_PROG';

題目：查詢平均工資最低的部門id

思路，按照部門id分類，查出各部門的平均工資，然后找出小于等于所有部門平均工資的平均工資對于的部門id。

mysql> SELECT
    ->     department_id
    -> FROM
    ->     employees
    -> GROUP BY department_id
    -> HAVING AVG(salary) <= ALL
    ->     (SELECT
    ->         AVG(salary)
    ->     FROM
    ->         employees
    ->     GROUP BY department_id);
+---------------+
| department_id |
+---------------+
|            50 |
+---------------+
1 row in set (0.00 sec)
-- 上述sql可以轉化為下面SQL語句
-- 思路，找出最小的平均工資對應的部門
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
            SELECT MIN(avg_salary)
            FROM (
                SELECT AVG(salary) AS avg_salary
                FROM employees
                GROUP BY department_id
            ) t
        );

注意，在MYSQL中聚合函數不能嵌套使用。

mysql> SELECT
    ->         MIN(AVG(salary))
    ->     FROM
    ->         employees
    ->     GROUP BY department_id;
ERROR 1111 (HY000): Invalid use of group function

3.3 空值問題

mysql> SELECT employee_id
    -> FROM employees
    -> WHERE employee_id NOT IN(
    ->         SELECT manager_id
    ->         FROM employees
    ->         );
Empty set (0.00 sec)
-- sql分析，原本是想獲取不在管理者中的員工信息，但是因為子查詢中存在null值，導致整個查詢為空
SELECT employee_id,last_name
FROM employees
WHERE employee_id NOT IN(
            SELECT manager_id
            FROM employees
            );
-- 獲取不在管理者中的員工信息 正確的寫法            
SELECT employee_id,last_name
FROM employees
WHERE employee_id NOT IN(
            SELECT manager_id
            FROM employees
                WHERE manager_id IS NOT NULL
            );

4.相關子查詢

4.1 相關子查詢執行流程

如果子查詢的執行依賴于外部查詢，通常情況下都是因為子查詢中的表用到了外部的表，并進行了條件關聯，因此每執行一次外部查詢，子查詢都要重新計算一次，這樣的子查詢就被稱為關聯子查詢。

相關子查詢按照一行一行的順序執行，主查詢的每一行都執行一次子查詢。

-- 相關子查詢語法 子查詢中使用主查詢中的列
SELECT column1,column2,...
FROM table1 outer
WHERE column1 operator 
(SELECT column1,column2 FROM table2 WHERE expr1 = outer.expr2);

4.2 代碼示例

題目：查詢員工中工資大于本部門平均工資的員工的last_name,salary和其department_id

方式一：相關子查詢

mysql> SELECT last_name,salary,department_id
    -> FROM employees e
    -> WHERE salary > (
    ->             SELECT AVG(salary)
    ->             FROM employees
    ->             WHERE department_id = e.`department_id`
    ->             GROUP BY department_id
    ->             );
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| King      | 24000.00 |            90 |
| Hunold    |  9000.00 |            60 |
| Ernst     |  6000.00 |            60 |
| Greenberg | 12000.00 |           100 |
...
| Everett   |  3900.00 |            50 |
| Hartstein | 13000.00 |            20 |
| Higgins   | 12000.00 |           110 |
+-----------+----------+---------------+
38 rows in set (0.00 sec)

方式二：FROM子句中使用子查詢

mysql> SELECT last_name,salary,e1.department_id
    -> FROM employees e1,(SELECT AVG(salary) AS avg_sal,department_id FROM employees GROUP BY department_id) e2
    -> WHERE e1.`salary` > e2.avg_sal AND e1.`department_id` = e2.department_id;
+-----------+----------+---------------+
| last_name | salary   | department_id |
+-----------+----------+---------------+
| Hartstein | 13000.00 |            20 |
| Raphaely  | 11000.00 |            30 |
...
| Greenberg | 12000.00 |           100 |
| Faviet    |  9000.00 |           100 |
| Higgins   | 12000.00 |           110 |
+-----------+----------+---------------+
38 rows in set (0.00 sec)

FROM子句中使用子查詢，子查詢是作為FROM的一部分，子查詢要用()引起來，并且要給這個子查詢起別名，把它當作一張臨時表使用。

在ORDER BY中使用子查詢

題目：查詢員工的id,salary,按照department_name 排序

SELECT employee_id,salary
FROM employees e
ORDER BY (
    SELECT department_name 
    FROM departments 
    WHERE department_id = e.`department_id`
    );

題目：若employees表中employee_id與job_history表中employee_id相同的數目不小于2，輸出這些相同id的員工的employee_id,last_name和其job_id

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
    SELECT COUNT(*)
    FROM job_history
    WHERE employee_id = e.`employee_id`
    );

4.3 EXISTS與NOT EXISTS關鍵字

關聯子查詢通常也會和EXISTS操作符一起來使用，用來檢查在子查詢中是否存在滿足條件的行。

如果在子查詢中不存在滿足條件的行

條件返回false

繼續在子查詢中查找

如果在子查詢中存在滿足條件的行

不在子查詢中繼續查找

條件返回true

NOT EXISTS關鍵字表示如果不存在返回true，存在返回false

題目：查詢公司管理者的employee_id，last_name，job_id，department_id信息

-- 方式1 相關子查詢 + EXISTS關鍵字
SELECT employee_id,last_name,job_id,department_id
FROM employees e1
WHERE EXISTS (SELECT manager_id FROM employees e2 WHERE e2.manager_id = e1.`employee_id`);

-- 方式2 自連接
-- 加DISTINCT關鍵字的原因，因為滿足e1.employee_id = e2.manager_id的條件有很多條，而顯示的又是e1表的列數據，就會導致重復的數據
SELECT DISTINCT e1.employee_id,e1.last_name,e1.job_id,e1.department_id
FROM employees e1 JOIN employees e2
WHERE e1.employee_id = e2.manager_id;
-- 或者這樣理解，因為顯示的是管理者的列信息，而一個管理者下面會有很多員工，所以滿足條件的管理者就會重復出現，所以需要加DISTINCT關鍵字
SELECT DISTINCT manager.employee_id,manager.last_name,manager.job_id,manager.department_id
FROM employees manager JOIN employees worker
WHERE manager.employee_id = worker.manager_id;

-- 方式3 
SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (SELECT manager_id FROM employees);

題目：查詢departments表中，不存在于employees表中的部門的department_id和department_name

SELECT department_id,department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X' FROM employees e WHERE e.`department_id` = d.`department_id`)

4.4 相關更新

語法

UPDATE table1 alias1
SET column = (SELECT expression FROM table2 alias2 WHERE alias1.column = alias2.column);

使用相關子查詢依據一個表中的數據更新另一個表的數據。

案例

題目：在employees中增加一個department_name字段，數據為員工對應的部門名稱。

ALTER TABLE employees 
MODIFY department_name VARCHAR(30)
UPDATE employees e
SET e.department_name = (SELECT department_name FROM departments d WHERE d.`department_id` = e.`department_id`);

4.5 相關刪除

語法

DELETE FROM table1 alias1
WHERE column operator (
SELECT expression
    FROM table2 alias2
    WHERE alias1.column = alias2.column
);

案例

題目：刪除表employees中，其與emp_history表皆有的數據

DELETE FROM  employees e
WHERE e.employee_id IN (
    SELECT employee_id 
    FROM emp_history e1
    WHERE e1.employee_id = e.employee_id
    );

5.思考題

誰的工資比Abel的高？

-- 方式1多表查詢 自連接
SELECT
    e2.`last_name`,
    e2.`salary`
FROM
    employees e1,
    employees e2
WHERE e2.`salary` > e1.`salary`
    AND e1.`last_name` = 'Abel';-- 相當于自連接e1表(一張只有一行數據的employees表)
    
-- 方式2 子查詢
SELECT
    last_name,
    salary
FROM
    employees
WHERE salary >
    (SELECT
        salary
    FROM
        employees
    WHERE last_name = 'Abel');

問題

自連接和子查詢那種好？

答案

自連接更好！如果需求可以使用自連接，也可以使用子查詢的情況下，建議使用自連接。原因，因為在許多DBMS的處理過程中，對于自連接的處理速度要比子查詢快的多。

可以這樣理解，子查詢實際上是通過未知表進行查詢后的條件判斷，而自連接是通過已知的自身數據表進行條件判斷，因此大部分的DBMS中都對自連接進行了優化。

掃描二維碼推送至手機訪問。

本文鏈接：http://www.7811333.com/?id=510

分享給朋友：

返回列表

上一篇：MySQL修改用戶名密碼：詳細步驟及代碼演示

下一篇：mysql基礎查詢的使用

發表評論

<ul id="oswom"></ul>

<abbr id="oswom"></abbr>

<ul id="oswom"></ul>

關于MySQL子查詢

1.需求分析與問題解決

1.1 實際問題

1.2 子查詢的基本使用

1.3 子查詢的分類

2.單行子查詢

2.1 單行比較操作符

2.2 代碼示例

2.3 HAVING中的子查詢

2.4 CASE中的子查詢

2.5 子查詢中的空值現象

2.6 非法使用子查詢

3.多行子查詢

3.1 多行比較操作符

3.2 代碼示例

3.3 空值問題

4.相關子查詢

4.1 相關子查詢執行流程

4.2 代碼示例

4.3 EXISTS與NOT EXISTS關鍵字

4.4 相關更新

5.思考題

“關于MySQL子查詢” 的相關文章

MySQL語法及用法

HANA數據庫用戶表PASSWORD_CHANGE_NEEDED和PASSWORD_LIFETIME_CHECK的作用

SQL 數據庫 - 創建/管理數據庫

MySQL觸發器的使用

MySQL修改用戶名密碼：詳細步驟及代碼演示

發表評論

? 2024 星星學習 - http://xingxinghan.cn . All rights reserved 贛ICP備17016689號-1

Powered By Z-BlogPHP. Theme by TOYEAN.

關于MySQL子查詢

1.需求分析與問題解決

1.1 實際問題

1.2 子查詢的基本使用

1.3 子查詢的分類

2.單行子查詢

2.1 單行比較操作符

2.2 代碼示例

2.3 HAVING中的子查詢

2.4 CASE中的子查詢

2.5 子查詢中的空值現象

2.6 非法使用子查詢

3.多行子查詢

3.1 多行比較操作符

3.2 代碼示例

3.3 空值問題

4.相關子查詢

4.1 相關子查詢執行流程

4.2 代碼示例

4.3 EXISTS與NOT EXISTS關鍵字

4.4 相關更新

5.思考題

“關于MySQL子查詢” 的相關文章

MySQL語法及用法

HANA數據庫用戶表PASSWORD_CHANGE_NEEDED和PASSWORD_LIFETIME_CHECK的作用

SQL 數據庫 - 創建/管理數據庫

MySQL觸發器的使用

MySQL修改用戶名密碼：詳細步驟及代碼演示

發表評論取消回復

Powered By Z-BlogPHP. Theme by TOYEAN.

發表評論