SQL 练习题(12 题)

-- 建一张订单表演示(实际项目里表已存在)
CREATE TABLE orders (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    user_id    INT NOT NULL,
    amount     DECIMAL(10,2) NOT NULL,
    status     VARCHAR(20) DEFAULT 'paid',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 查全部列、全部行(* 在调试时用,生产别用,浪费带宽)
SELECT * FROM orders;

-- 只查需要的列(推荐,索引也能用上)
SELECT id, user_id, amount FROM orders;

-- 给列起别名(AS 可省略)
SELECT id AS 订单号, amount AS 金额 FROM orders;

-- 去重:看所有下过单的用户(不重复)
SELECT DISTINCT user_id FROM orders;

-- 比较运算:= != > >= < <=
SELECT * FROM orders WHERE amount > 100;

-- 逻辑组合:AND / OR / NOT(多个条件用括号分组)
SELECT * FROM orders
WHERE amount > 100 AND status = 'paid';

SELECT * FROM orders
WHERE (status = 'paid' OR status = 'shipped')
  AND amount >= 50;

-- 范围:BETWEEN(闭区间,含两端)
SELECT * FROM orders
WHERE amount BETWEEN 100 AND 500;

-- 集合:IN
SELECT * FROM orders
WHERE status IN ('paid', 'shipped', 'delivered');

-- 模糊匹配:LIKE(% 任意多字符,_ 单个字符)
SELECT * FROM users WHERE name LIKE '张%';     -- 姓张的
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 空值判断:必须用 IS NULL,不能用 = NULL
SELECT * FROM orders WHERE shipped_at IS NULL;

-- 按金额升序(ASC 是默认,可省略)
SELECT * FROM orders ORDER BY amount ASC;

-- 按金额降序(贵的在前)
SELECT * FROM orders ORDER BY amount DESC;

-- 多列排序:先按状态升序,同状态再按金额降序
SELECT * FROM orders
ORDER BY status ASC, amount DESC;

-- 按表达式排序(常见:按金额从大到小)
SELECT id, amount FROM orders
ORDER BY amount DESC;

-- 把 NULL 排在最后(MySQL 里 NULL 默认最小)
SELECT * FROM orders
ORDER BY shipped_at IS NULL, shipped_at;

-- 取金额前 3 的高价订单(排序 + 限量)
SELECT * FROM orders
ORDER BY amount DESC
LIMIT 3;

-- 分页:每页 10 条,取第 2 页(LIMIT 偏移 条数)
-- 偏移 = (页码 - 1) × 每页条数 = (2-1)×10 = 10
SELECT * FROM orders
ORDER BY id ASC
LIMIT 10 OFFSET 10;
-- 简写:LIMIT 10, 10(逗号前是偏移,后是条数)

-- 深分页的性能坑:偏移很大时(如 LIMIT 1000000, 10)
-- 数据库要扫 1000010 行再丢掉前 100 万行,很慢
-- 优化:用"游标分页"(记住上一页最后一条 id)
SELECT * FROM orders
WHERE id > 1000010          -- 上一页最后一条的 id
ORDER BY id ASC
LIMIT 10;

-- 插入一条(全字段)
INSERT INTO orders (user_id, amount, status)
VALUES (42, 199.00, 'paid');

-- 插入一条(部分字段,其余用默认值)
INSERT INTO orders (user_id, amount)
VALUES (42, 99.00);
-- id 自增、status 默认 'paid'、created_at 默认当前时间

-- 批量插入(性能比循环单条高得多)
INSERT INTO orders (user_id, amount, status) VALUES
(42, 199.00, 'paid'),
(43, 88.50,  'paid'),
(44, 12.00,  'pending');

-- 插入 + 冲突处理(MySQL:ON DUPLICATE KEY UPDATE)
-- 唯一键冲突时改成更新而不是报错
INSERT INTO users (id, name, email)
VALUES (1, '小明', 'xm@new.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- PostgreSQL 用 ON CONFLICT:
-- INSERT INTO users (id, name, email) VALUES (...)
-- ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

-- 更新单条(WHERE 必须带,否则全表更新!事故重灾区)
UPDATE orders
SET status = 'shipped', shipped_at = NOW()
WHERE id = 1001;

-- 批量更新:所有 pending 状态超过 1 天的改成 cancelled
UPDATE orders
SET status = 'cancelled'
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);

-- 基于另一列计算更新:全场打 9 折
UPDATE orders
SET amount = amount * 0.9
WHERE status = 'paid' AND amount > 1000;

-- 💡 救命习惯:正式 UPDATE 前先 SELECT 一遍确认影响范围
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);
-- 看清楚了再把它换成 UPDATE

-- 安全网:MySQL 默认是 safe update 关着的,
-- 可以 SET sql_safe_updates = 1; 让没 WHERE 的 UPDATE/DELETE 直接报错

-- 两张表:orders(订单) 和 users(用户)
-- 需求:查每笔订单的用户名(订单表只有 user_id)

-- INNER JOIN:只保留两边都能匹配上的行(最常用)
SELECT o.id, u.name, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN:保留左表全部行,右表匹配不上填 NULL
-- 比如"所有用户,包括从没下过单的"
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- 没下过单的用户 order_id 是 NULL

-- 三表连接:订单 + 用户 + 商品明细
SELECT o.id, u.name, p.title, oi.quantity
FROM orders o
JOIN users  u  ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 给表起短别名(o、u、p)是惯例,JOIN 多表时少打字

-- 按状态分组,统计每个状态的订单数和总金额
SELECT
    status,
    COUNT(*)           AS 订单数,
    SUM(amount)        AS 总金额,
    AVG(amount)        AS 平均金额,
    MAX(amount)        AS 最大金额,
    MIN(amount)        AS 最小金额
FROM orders
GROUP BY status;
-- 输出:
-- paid     120  45200.00  376.67  2000.00  12.00
-- shipped   80  31000.00  387.50  1800.00  30.00

-- 分组后再过滤用 HAVING(WHERE 是分组前过滤,HAVING 是分组后)
-- 找"总消费超过 1000 的用户"
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'              -- 先过滤未支付
GROUP BY user_id
HAVING SUM(amount) > 1000          -- 再筛总消费大的
ORDER BY total DESC;

-- 记住执行顺序:FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- 子查询:查询里嵌查询
-- 需求:找出"金额高于平均金额"的订单

-- 写法 1:子查询放 WHERE 里
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- 写法 2:IN 子查询,找"下过金额 > 1000 订单的用户"
SELECT name FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders WHERE amount > 1000
);

-- 写法 3:相关子查询(子查询依赖外层,逐行执行,较慢)
-- 查每个用户自己金额最高的那笔订单
SELECT * FROM orders o
WHERE amount = (
    SELECT MAX(amount) FROM orders
    WHERE user_id = o.user_id
);

-- 推荐替代:CTE(WITH 子句)更清晰
WITH big_orders AS (
    SELECT * FROM orders WHERE amount > 1000
)
SELECT u.name, COUNT(*) AS 大单数
FROM big_orders b
JOIN users u ON b.user_id = u.id
GROUP BY u.name;

-- 没有索引时,WHERE user_id = 42 要扫全表(慢)
-- 加上索引后,数据库用 B+ 树快速定位(快几个数量级)

-- 1. 建普通索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 建复合索引(多列,顺序很关键,遵循"最左前缀")
-- 适合同时按 user_id + status 查询
CREATE INDEX idx_user_status ON orders(user_id, status);

-- 3. 建唯一索引(同时保证不重复,业务唯一键用它)
CREATE UNIQUE INDEX uniq_email ON users(email);

-- 4. 用 EXPLAIN 看查询有没有走索引(必会!)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- key 列显示用到的索引名;rows 列显示预估扫描行数
-- 如果 key 是 NULL 或 rows 巨大,说明没走索引要优化

-- 5. 删除索引
DROP INDEX idx_user_id ON orders;

-- ⚠️ 索引不是越多越好:写操作(INSERT/UPDATE)要同步维护索引,
--    会变慢。读多写少的表多建索引,写多的表谨慎

-- 电商数据库表结构(简化):
-- users(id, name, city)
-- products(id, title, price, stock)
-- orders(id, user_id, amount, status, created_at)
-- order_items(id, order_id, product_id, quantity, price)

-- 1. 每个城市的总销售额(用户 JOIN 订单,按城市分组)
SELECT u.city,
       COUNT(DISTINCT o.id) AS 订单数,
       SUM(o.amount)        AS 总销售额
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.city
ORDER BY 总销售额 DESC;

-- 2. 销量 Top 10 的商品(订单明细 JOIN 商品,按销量排)
SELECT p.title,
       SUM(oi.quantity) AS 销量,
       SUM(oi.quantity * oi.price) AS 销售额
FROM order_items oi
JOIN products p ON oi.product_id = p.id
GROUP BY p.id, p.title
ORDER BY 销量 DESC
LIMIT 10;

-- 3. 近 7 天每天的销售曲线(按天分组,出报表)
SELECT DATE(o.created_at) AS 日期,
       COUNT(*)           AS 订单数,
       SUM(o.amount)      AS 销售额
FROM orders o
WHERE o.created_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  AND o.status = 'paid'
GROUP BY DATE(o.created_at)
ORDER BY 日期;

-- 4. 复购率:下过 2 次及以上订单的用户占比
SELECT
    ROUND(
      COUNT(CASE WHEN 订单数 >= 2 THEN 1 END) * 100.0
      / COUNT(*), 2
    ) AS 复购率百分比
FROM (
    SELECT user_id, COUNT(*) AS 订单数
    FROM orders WHERE status = 'paid'
    GROUP BY user_id
) t;

-- 场景:从用户表里发现脏数据,要清洗后再分析

-- 1. 找重复邮箱(同一邮箱多条记录)
SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- 输出:
-- xm@example.com  3

-- 2. 删重复,每组只保留 id 最小的那条
DELETE FROM users
WHERE id NOT IN (
    SELECT id FROM (
        SELECT MIN(id) AS id FROM users GROUP BY email
    ) tmp
);
-- 注意:MySQL 不让直接在 DELETE 里子查询同一张表,套一层临时表绕过

-- 3. 找缺失值:phone 为空字符串或 NULL 的用户
SELECT id, name, phone FROM users
WHERE phone IS NULL OR phone = '';

-- 4. 标准化手机号:去掉空格和短横线,统一成 11 位
UPDATE users
SET phone = REPLACE(REPLACE(phone, ' ', ''), '-', '')
WHERE phone IS NOT NULL;

-- 5. 找异常值:金额为负或异常巨大的订单
SELECT * FROM orders
WHERE amount < 0 OR amount > 100000;

-- 6. 用 CASE 给数据打标签(金额分档)
SELECT
    id, amount,
    CASE
        WHEN amount < 100   THEN '小额'
        WHEN amount < 1000  THEN '中额'
        WHEN amount < 5000  THEN '大额'
        ELSE '超大额'
    END AS 金额档位
FROM orders;

-- 7. 字符串清洗:去掉首尾空格,统一小写
UPDATE users
SET email = TRIM(LOWER(email));