【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_id
FROM orders
GROUP BY user_id
HAVING 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，用于复杂的数据计算和处理。
子查询和嵌套查询在实际应用中非常重要，能够帮助我们从多个表中提取和组合数据，以满足复杂的查询需求。