深入實例，掌握SQL子查詢
SQL子查詢是數(shù)據(jù)庫查詢中的一項重要且強大的技術，是數(shù)據(jù)庫開發(fā)人員提升查詢能力的關鍵工具之一。通過嵌套在主查詢中，子查詢可以實現(xiàn)更精確和靈活的數(shù)據(jù)過濾，為數(shù)據(jù)管理和查詢優(yōu)化提供無限可能。對于數(shù)據(jù)庫開發(fā)人員來說，了解不同類型的子查詢及其實際應用是優(yōu)化查詢和數(shù)據(jù)管理的關鍵。
1 理解子查詢
子查詢是嵌套在另一個查詢中的 SQL 查詢，作為增強主查詢功能的構建塊。這些子查詢可以應用于 SELECT、UPDATE、DELETE 和 INSERT 語句，實現(xiàn)了廣泛的應用。子查詢的主要目的是以一種無法通過常規(guī)查詢實現(xiàn)的方式來過濾或操作數(shù)據(jù)。
接下來我們通過具體示例來深入了解各種類型的子查詢及其應用。
2 示例
假設在 MySQL 數(shù)據(jù)庫管理系統(tǒng)中有以下數(shù)據(jù)庫表：
一個包含有關員工信息的 Employees 表，一個存儲有關產(chǎn)品詳細信息的 Products 表，一個將產(chǎn)品分類為不同類別的 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（主鍵）：每個產(chǎn)品的唯一標識符。
- product_name：產(chǎn)品名稱。
- category_id：產(chǎn)品所屬類別的標識符。
+---------+--------------+-------------+
| 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 子查詢應用
把子查詢應用于各種數(shù)據(jù)庫表，以突出它們的實際用途。
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 |
+--------------+主查詢選擇工資高于其各自部門平均工資的員工。
在本例中，只有簡-史密斯?jié)M足這一條件。
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 語句
假設想要刪除所有已經(jīng)沒有庫存的產(chǎn)品的訂單。
DELETE FROM orders
WHERE product_id
NOT IN (SELECT product_id FROM products WHERE stock_quantity > 0);在這種情況下，子查詢檢索具有正庫存數(shù)量的產(chǎn)品的 product_id，主查詢刪除不在此列表中的產(chǎn)品的訂單。
刪除操作后，Orders 表被修改如下：
+----------+------------+------------+
| order_id | product_id | order_date |
+----------+------------+------------+
| 1001 | 101 | 2023-01-01 |
| 1002 | 102 | 2023-01-02 |
+----------+------------+------------+主查詢刪除了已經(jīng)沒有庫存的產(chǎn)品的訂單，依據(jù)子查詢檢索到具有正庫存數(shù)量的產(chǎn)品的 product_id。
3.4 INSERT 語句
假設只有當某個部門的員工數(shù)量超過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;在這個例子中，子查詢計算指定部門的員工數(shù)量，主查詢只有在數(shù)量大于5時才插入新員工。
沒有插入任何行，Employees 表保持不變。
主查詢嘗試僅在部門201中的員工數(shù)量大于5時將新員工插入。
在這種情況下，條件沒有滿足，因此插入操作沒有發(fā)生。
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');在這種情況下，子查詢獲取“電子產(chǎn)品”類別的 category_id，主查詢選擇屬于該類別的所有產(chǎn)品。
4.3 多列子查詢
這種類型的子查詢返回多列但只有一行。它在主查詢需要單行的一組值時使用。
SELECT employee_name
FROM employees
WHERE (salary, department_id) = (SELECT MAX(salary), department_id FROM employees);這里的子查詢檢索最高工資及其關聯(lián)的 department_id，主查詢選擇具有相同工資和部門的員工。
總結
通過了解子查詢的類型和實際應用，數(shù)據(jù)庫開發(fā)人員能夠掌握這種高級 SQL 技術并優(yōu)化其數(shù)據(jù)庫交互。
通過示例可以看到，子查詢打開了許多可能性，使其成為任何 SQL 實踐者工具箱中有價值的補充。通過靈活運用子查詢，我們可以根據(jù)具體需求精確地檢索和操作數(shù)據(jù)，提高查詢的效率和準確性。
無論是用于數(shù)據(jù)篩選、數(shù)據(jù)匯總、數(shù)據(jù)更新還是數(shù)據(jù)插入，子查詢都展示了其強大的功能。了解如何編寫和優(yōu)化子查詢，可以讓我們在數(shù)據(jù)庫管理和查詢優(yōu)化方面更具競爭力。
