【MySQL】提高篇—复杂查询：子查询与嵌套查询

在关系数据库中，数据通常存储在多个表中，且这些表之间存在复杂的关系。为了从这些表中提取所需的信息，常常需要使用查询操作。子查询和嵌套查询是 SQL 中两种强大的查询方式，能够帮助我们从一个表中提取数据，并将其作为条件用于另一个查询。

子查询是指在一个 SQL 查询中嵌套另一个查询。它可以在 SELECT、INSERT、UPDATE 或 DELETE 语句中使用，允许我们在同一查询中进行多层次的数据操作。

嵌套查询是子查询的一种形式，通常指在 SELECT 语句中嵌套另一个 SELECT 语句。

在实际应用中，子查询和嵌套查询能够帮助我们解决复杂的数据检索问题。例如，在一个电商系统中，可能需要找出下单金额超过某个阈值的用户，或者找出某个产品的所有订单信息。通过子查询和嵌套查询，可以方便地实现这些需求。

1. 子查询的基本概念

子查询是一个查询嵌套在另一个查询中，通常用于过滤、计算或返回值。子查询可以返回单个值、单列值或多列值。

示例：子查询

假设我们有两个表：users 和 orders。

-- 创建 users 表
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL
);-- 创建 orders 表
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,amount DECIMAL(10, 2),FOREIGN KEY (user_id) REFERENCES users(user_id)
);

插入数据：

-- 插入用户数据
INSERT INTO users (username) VALUES
('Alice'),
('Bob'),
('Charlie');-- 插入订单数据
INSERT INTO orders (user_id, amount) VALUES
(1, 150.00),
(1, 200.00),
(2, 50.00),
(3, 300.00);

查询示例：

-- 查询下单金额超过 100 的用户
SELECT username
FROM users
WHERE user_id IN (SELECT user_id FROM orders WHERE amount > 100);

解释：

外查询：SELECT username FROM users：查询用户的用户名。
子查询：SELECT user_id FROM orders WHERE amount > 100：查询所有下单金额超过 100 的用户 ID。
WHERE user_id IN (...)：外查询根据子查询的结果过滤出符合条件的用户。

查询结果：

+----------+
| username |
+----------+
| Alice    |
| Charlie  |
+----------+

2. 嵌套查询的基本概念

嵌套查询通常指在一个查询的 SELECT 语句中嵌套另一个 SELECT 语句。它可以用于计算、聚合和其他复杂的查询。

示例：嵌套查询

查询示例：

-- 查询用户的用户名及其订单总金额
SELECT username,(SELECT SUM(amount) FROM orders WHERE orders.user_id = users.user_id) AS total_amount
FROM users;

解释：

SELECT username：查询用户的用户名。
(SELECT SUM(amount) FROM orders WHERE orders.user_id = users.user_id)：嵌套查询，用于计算每个用户的订单总金额。
AS total_amount：将嵌套查询的结果命名为 total_amount。

查询结果：

+----------+-------------+
| username | total_amount|
+----------+-------------+
| Alice    |       350.00|
| Bob      |        50.00|
| Charlie  |       300.00|
+----------+-------------+

3. 子查询与嵌套查询的应用场景

3.1 查找特定条件下的记录

在电商系统中，我们可能需要找出下单金额最高的用户。

-- 查询下单金额最高的用户
SELECT username
FROM users
WHERE user_id = (SELECT user_id FROM orders ORDER BY amount DESC LIMIT 1);

解释：

WHERE user_id = (...)：子查询返回下单金额最高的用户 ID。
ORDER BY amount DESC LIMIT 1：子查询按金额降序排列，并限制结果为 1 条记录。

查询结果：

+----------+
| username |
+----------+
| Alice    |
+----------+

3.2 复杂的条件查询

假设我们想要查询下单金额高于所有用户平均订单金额的用户。

-- 查询下单金额高于所有用户平均订单金额的用户
SELECT username
FROM users
WHERE user_id IN (SELECT user_idFROM ordersGROUP BY user_idHAVING SUM(amount) > (SELECT AVG(total) FROM (SELECT SUM(amount) AS total FROM orders GROUP BY user_id) AS avg_table)
);

解释：

内层子查询：SELECT SUM(amount) AS total FROM orders GROUP BY user_id：计算每个用户的总订单金额。
中间子查询：SELECT AVG(total) FROM (...) AS avg_table：计算所有用户的平均订单金额。
外查询：SELECT username FROM users WHERE user_id IN (...)：根据子查询的结果返回符合条件的用户。

查询结果：

+----------+
| username |
+----------+
| Alice    |
| Charlie  |
+----------+

4. 总结

通过本节的示例，您应该能够理解子查询和嵌套查询的基本概念及其应用场景：

子查询：可以在 WHERE、SELECT、FROM 子句中使用，用于过滤、计算或返回值。
嵌套查询：通常在 SELECT 语句中嵌套另一个 SELECT，用于复杂的数据计算和处理。

子查询和嵌套查询在实际应用中非常重要，能够帮助我们从多个表中提取和组合数据，以满足复杂的查询需求。