深入实例，掌握SQL子查询-51CTO.COM

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 实践者工具箱中有价值的补充。通过灵活运用子查询，我们可以根据具体需求精确地检索和操作数据，提高查询的效率和准确性。

无论是用于数据筛选、数据汇总、数据更新还是数据插入，子查询都展示了其强大的功能。了解如何编写和优化子查询，可以让我们在数据库管理和查询优化方面更具竞争力。