数据库MySQL学习——day10()

1. 什么是子查询（Subquery）？

子查询是嵌套在其他 SQL 语句中的查询。它可以用在 SELECT、INSERT、UPDATE、DELETE 等语句中，也可以出现在 WHERE、FROM、SELECT 子句里。

简单说：子查询是 “** 先查询一部分结果，再用这个结果做下一步操作** ”。

2. 创建样例表：商品表 products

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    category VARCHAR(50)
);

3. 插入示例数据

INSERT INTO products (product_id, name, price, category) VALUES
(1, 'Laptop', 8000.00, 'Electronics'),
(2, 'Smartphone', 5000.00, 'Electronics'),
(3, 'Tablet', 3000.00, 'Electronics'),
(4, 'Desk', 1500.00, 'Furniture'),
(5, 'Chair', 800.00, 'Furniture'),
(6, 'Monitor', 2000.00, 'Electronics');

4. 子查询的三种常用位置

4.1 子查询在 WHERE 子句中（最常见）

需求 ：查找价格高于平均价格的商品。

SELECT * FROM products
WHERE price > (
    SELECT AVG(price) FROM products
);

解释 ：

• SELECT AVG(price) 是子查询，先计算所有商品的平均价格。
• 主查询再找出价格高于这个平均值的商品。

4.2 子查询在 FROM 子句中（可以当成临时表）

需求 ：先计算每个分类的平均价格，再按平均价格降序显示。

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
ORDER BY avg_price DESC;

也可以写成这样，子查询放在 FROM 子句中：

SELECT * FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS avg_table
ORDER BY avg_table.avg_price DESC;

解释：

• 子查询先得到每个分类的平均价。
• 外层查询再排序这个结果。

4.3 子查询在 SELECT 子句中（用于显示动态数据）

需求 ：在每一行后面显示所有商品的平均价格。

SELECT name, price,
       (SELECT AVG(price) FROM products) AS average_price
FROM products;

解释：

• 每一行都多出一列，显示整个表的平均价格。

5. 关联子查询 vs 非关联子查询

5.1 非关联子查询（独立执行）

子查询可以先单独执行，结果固定。
例如：

SELECT * FROM products
WHERE price > (
    SELECT AVG(price) FROM products
);

• 平均值只查一次 → 效率高。

5.2 关联子查询（需要与主查询逐行配合）

子查询依赖主查询的每一行。
需求 ：查找每个分类中价格最低的商品。

SELECT * FROM products p1
WHERE price = (
    SELECT MIN(price)
    FROM products p2
    WHERE p2.category = p1.category
);

解释 ：

• 外层的每一行都会带入内层的查询 → 根据当前分类找最便宜的。

6. 子查询在 UPDATE 和 DELETE 中的应用

6.1 UPDATE 中的子查询

需求 ：将价格高于平均值的商品价格上调 10%。

UPDATE products
SET price = price * 1.10
WHERE price > (
    SELECT AVG(price) FROM products
);

6.2 DELETE 中的子查询

需求 ：删除价格低于平均价格的商品。

DELETE FROM products
WHERE price < (
    SELECT AVG(price) FROM products
);

• 注意：部分数据库（如 MySQL）不允许在同一张表中即使用又删除。解决办法是使用临时表：

DELETE FROM products
WHERE product_id IN (
    SELECT * FROM (
        SELECT product_id FROM products
        WHERE price < (SELECT AVG(price) FROM products)
    ) AS temp
);

7. 总结

子查询位置	应用场景举例
WHERE 中	比较、筛选（如：大于平均值）
FROM 中	作为虚拟表使用，进一步查询或排序
SELECT 中	动态计算并展示额外字段（如平均值）
UPDATE/DELETE	根据计算结果来更新或删除记录