深入實例，掌握SQL子查詢
SQL子查詢是數據庫查詢中的一項重要且強大的技術，是數據庫開發人員提升查詢能力的關鍵工具之一。通過嵌套在主查詢中，子查詢可以實現更精確和靈活的數據過濾，為數據管理和查詢優化提供無限可能。對于數據庫開發人員來說，了解不同類型的子查詢及其實際應用是優化查詢和數據管理的關鍵。
1 理解子查詢
子查詢是嵌套在另一個查詢中的 SQL 查詢，作為增強主查詢功能的構建塊。這些子查詢可以應用于 SELECT、UPDATE、DELETE 和 INSERT 語句，實現了廣泛的應用。子查詢的主要目的是以一種無法通過常規查詢實現的方式來過濾或操作數據。
接下來我們通過具體示例來深入了解各種類型的子查詢及其應用。
2 示例
假設在 MySQL 數據庫管理系統中有以下數據庫表：
一個包含有關員工信息的 Employees 表，一個存儲有關產品詳細信息的 Products 表，一個將產品分類為不同類別的 Categories 表，以及一個記錄有關客戶訂單信息的 Orders 表。
這些表之間的關系通過主鍵和外鍵建立。
Employees 表
- employee_id（主鍵）：每個員工的唯一標識符。
- employee_name：員工姓名。
- department_id：員工所屬部門的標識符。
- salary：員工的薪水。
+---------+--------------+---------------+--------+
| employee_id | employee_name | department_id | salary |
+---------+--------------+---------------+--------+
| 1 | John Doe | 101 | 50000 |
| 2 | Jane Smith | 102 | 60000 |
| 3 | Mark Johnson | 101 | 55000 |
+---------+--------------+---------------+--------+Products 表
- product_id（主鍵）：每個產品的唯一標識符。
- product_name：產品名稱。
- category_id：產品所屬類別的標識符。
+---------+--------------+-------------+
| product_id | product_name | category_id |
+---------+--------------+-------------+
| 101 | Laptop | 1 |
| 102 | Smartphone | 1 |
| 103 | T-shirt | 2 |
+---------+--------------+-------------+Categories 表
- category_id（主鍵）：每個類別的唯一標識符。
- category_name：類別名稱。
+-------------+----------------+
| category_id | category_name |
+-------------+----------------+
| 1 | Electronics |
| 2 | Clothing |
| 3 | Furniture |
+-------------+----------------+Orders 表
- order_id（主鍵）：每個訂單的唯一標識符。
- product_id（外鍵）：引用 Products 表中的product_id。
- order_date：訂單下達日期。
+---------+-------------+------------+
| order_id | product_id | order_date |
+---------+-------------+------------+
| 1001 | 101 | 2023–01–01 |
| 1002 | 102 | 2023–01–02 |
| 1003 | 103 | 2023–01–03 |
+---------+-------------+------------+3 子查詢應用
把子查詢應用于各種數據庫表，以突出它們的實際用途。
3.1 SELECT 語句
假設想要檢索所有工資高于其各自部門平均工資的員工。
SELECT employee_name
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);在此示例中，子查詢計算每個部門的平均工資，主查詢選擇工資超過該部門平均工資的員工。
+--------------+
| employee_name |
+--------------+
| Jane Smith |
+--------------+主查詢選擇工資高于其各自部門平均工資的員工。
在本例中，只有簡-史密斯滿足這一條件。
3.2 UPDATE 語句
假設想要將特定部門中所有員工的工資提高10%，使其高于該部門的平均工資。
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 101
AND salary < (SELECT AVG(salary)
FROM employees
WHERE department_id = 101);在這里，子查詢確定指定部門的平均工資，主查詢更新符合條件的員工的工資。
更新后，Employees 表被修改如下：
+-------------+---------------+---------------+--------+
| employee_id | employee_name | department_id | salary |
+-------------+---------------+---------------+--------+
| 1 | John Doe | 101 | 55000 |
| 2 | Jane Smith | 102 | 60000 |
| 3 | Mark Johnson | 101 | 60500 |
+-------------+---------------+---------------+--------+主查詢將部門101中的員工工資提高10%，使其高于該部門的平均工資。
3.3 DELETE 語句
假設想要刪除所有已經沒有庫存的產品的訂單。
DELETE FROM orders
WHERE product_id
NOT IN (SELECT product_id FROM products WHERE stock_quantity > 0);在這種情況下，子查詢檢索具有正庫存數量的產品的 product_id，主查詢刪除不在此列表中的產品的訂單。
刪除操作后，Orders 表被修改如下：
+----------+------------+------------+
| order_id | product_id | order_date |
+----------+------------+------------+
| 1001 | 101 | 2023-01-01 |
| 1002 | 102 | 2023-01-02 |
+----------+------------+------------+主查詢刪除了已經沒有庫存的產品的訂單，依據子查詢檢索到具有正庫存數量的產品的 product_id。
3.4 INSERT 語句
假設只有當某個部門的員工數量超過5人時，才想要插入一個新員工。
INSERT INTO employees (employee_id, employee_name, department_id, salary)
VALUES (1001, 'John Doe', 201, 60000)
WHERE (SELECT COUNT(*)
FROM employees
WHERE department_id = 201) > 5;在這個例子中，子查詢計算指定部門的員工數量，主查詢只有在數量大于5時才插入新員工。
沒有插入任何行，Employees 表保持不變。
主查詢嘗試僅在部門201中的員工數量大于5時將新員工插入。
在這種情況下，條件沒有滿足，因此插入操作沒有發生。
4 子查詢分類
4.1 單行子查詢
這種類型的子查詢僅返回一行結果。
通常在需要將單個值與子查詢的結果進行比較的情況下使用。
SELECT employee_name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);在這個例子中，子查詢從 employees 表中檢索最高工資，主查詢選擇具有該工資的員工。
4.2 多行子查詢
多行子查詢返回多行結果。
當主查詢需要與一組值進行比較時使用。
SELECT product_name
FROM products
WHERE category_id
IN (SELECT category_id
FROM categories
WHERE category_name = 'Electronics');在這種情況下，子查詢獲取“電子產品”類別的 category_id，主查詢選擇屬于該類別的所有產品。
4.3 多列子查詢
這種類型的子查詢返回多列但只有一行。它在主查詢需要單行的一組值時使用。
SELECT employee_name
FROM employees
WHERE (salary, department_id) = (SELECT MAX(salary), department_id FROM employees);這里的子查詢檢索最高工資及其關聯的 department_id，主查詢選擇具有相同工資和部門的員工。
總結
通過了解子查詢的類型和實際應用，數據庫開發人員能夠掌握這種高級 SQL 技術并優化其數據庫交互。
通過示例可以看到，子查詢打開了許多可能性，使其成為任何 SQL 實踐者工具箱中有價值的補充。通過靈活運用子查詢，我們可以根據具體需求精確地檢索和操作數據，提高查詢的效率和準確性。
無論是用于數據篩選、數據匯總、數據更新還是數據插入，子查詢都展示了其強大的功能。了解如何編寫和優化子查詢，可以讓我們在數據庫管理和查詢優化方面更具競爭力。
