SQL 实战项目

学了 12 篇基础,是时候把它们串起来解决点真实问题。这一章给三个完整、可运行的项目——博客数据库设计、电商统计查询、数据迁移清洗,覆盖 SQL 工程师日常最核心的三类任务:建库建表(数据建模)、分析查询(JOIN / 聚合 / 窗口函数)、数据治理(迁移 / 清洗 / 校验)。每段 SQL 都能直接粘到 sqliteonline.com 或本地 MySQL 跑通。建议你边读边敲,把每条 SELECT 的结果对照注释看,亲手验证。

实战 1:博客数据库设计(CREATE TABLE + 外键 + 索引)

需求:为一个博客系统设计核心数据库。三类实体——用户、文章、评论——之间的关系是:一个用户可以发多篇文章,一篇文章可以有多条评论。要求关系正确、查询高效、删除有规则。

涉及知识:主键 PRIMARY KEY + AUTO_INCREMENT、唯一约束 UNIQUE、非空 NOT NULL、默认值 DEFAULT、外键 FOREIGN KEY + ON DELETE CASCADE、二级索引 INDEX、ON UPDATE CURRENT_TIMESTAMP 自动维护时间。

-- ============================================================
-- 项目 1:博客数据库设计
-- 三张主表:users(用户)、posts(文章)、comments(评论)
-- 关系:一个用户发多篇文章,一篇文章有多条评论
-- ============================================================

-- 用户表
CREATE TABLE users (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    username    VARCHAR(50)  NOT NULL UNIQUE,           -- 登录名,唯一
    email       VARCHAR(100) NOT NULL UNIQUE,           -- 邮箱,唯一
    nickname    VARCHAR(50),                            -- 昵称,可空
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 文章表:作者 id 通过外键关联 users
CREATE TABLE posts (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id     BIGINT       NOT NULL,
    title       VARCHAR(200) NOT NULL,
    content     TEXT         NOT NULL,
    views       INT          NOT NULL DEFAULT 0,        -- 阅读量
    published   TINYINT(1)   NOT NULL DEFAULT 0,        -- 0=草稿,1=已发布
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                              ON UPDATE CURRENT_TIMESTAMP,

    -- 外键约束:作者必须存在于 users 表;删用户连带删文章
    CONSTRAINT fk_posts_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE,
    -- 索引:按用户/时间查询是最常见场景,加上加速
    INDEX idx_posts_user (user_id),
    INDEX idx_posts_created (created_at)
);

-- 评论表:同时关联文章和用户
CREATE TABLE comments (
    id          BIGINT PRIMARY KEY AUTO_INCREMENT,
    post_id     BIGINT       NOT NULL,
    user_id     BIGINT       NOT NULL,
    content     VARCHAR(500) NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_comments_post FOREIGN KEY (post_id)
        REFERENCES posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_comments_post (post_id)
);

-- ============================================================
-- 插入测试数据,后续查询要用
-- ============================================================
INSERT INTO users (username, email, nickname) VALUES
('alice', 'alice@example.com', '爱丽丝'),
('bob',   'bob@example.com',   '鲍勃');

INSERT INTO posts (user_id, title, content, views, published) VALUES
(1, 'Vue 3 入门',    'Composition API 是...',  120, 1),
(1, 'SQL 索引详解',  'B+ 树如何加速...',        350, 1),
(2, '我的第一篇博客', 'Hello World!',            12, 0);

INSERT INTO comments (post_id, user_id, content) VALUES
(1, 2, '写得真清楚!'),
(1, 1, '感谢支持~'),
(2, 2, '终于搞懂索引了');

-- 验证:删掉 alice 应该连带删掉她的全部文章和评论(CASCADE)
-- DELETE FROM users WHERE username = 'alice';  -- 慎用!

要点讲解:

实战 2:电商查询实战(JOIN + GROUP BY + 子查询 + 窗口函数)

需求:给定商品、订单、订单明细三张表,完成四个典型统计——品类销售汇总、消费 Top 用户、每类销冠商品、每日累计成交额。一个比一个难,循序把 JOIN / 聚合 / 子查询 / 窗口函数都用上。

涉及知识：INNER JOIN 多表连接、GROUP BY + SUM 聚合、子查询当临时表、ROW_NUMBER() OVER (PARTITION BY ...) 分组排名、SUM() OVER (ORDER BY ...) 累计窗口。

-- ============================================================
-- 项目 2:电商查询实战
-- 表:products(商品)、orders(订单)、order_items(订单明细)
-- 演示 JOIN / GROUP BY / 子查询 / 窗口函数 四类典型场景
-- ============================================================

CREATE TABLE products (
    id       INT PRIMARY KEY,
    name     VARCHAR(50),
    category VARCHAR(20),
    price    DECIMAL(10,2)
);
CREATE TABLE orders (
    id          INT PRIMARY KEY,
    user_id     INT,
    status      VARCHAR(20),                  -- paid/shipped/done
    created_at  DATE
);
CREATE TABLE order_items (
    order_id    INT,
    product_id  INT,
    quantity    INT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 插入测试数据(实际项目里这些数据已存在)
INSERT INTO products VALUES
(1, 'iPhone',  '电子产品', 8000.00),
(2, 'iPad',    '电子产品', 4000.00),
(3, 'T恤',     '服装',      200.00),
(4, '牛仔裤',  '服装',      500.00);

INSERT INTO orders VALUES
(101, 1, 'paid',    '2024-06-01'),
(102, 2, 'paid',    '2024-06-01'),
(103, 1, 'shipped', '2024-06-02');

INSERT INTO order_items VALUES
(101, 1, 1),    -- 订单 101:1 台 iPhone
(101, 3, 2),    -- 订单 101:2 件 T 恤
(102, 2, 1),    -- 订单 102:1 台 iPad
(103, 4, 3);    -- 订单 103:3 条牛仔裤


-- 需求 1:每个品类的总销量和总销售额(JOIN + GROUP BY)
SELECT p.category,
       SUM(oi.quantity)              AS total_qty,
       SUM(oi.quantity * p.price)    AS total_amount
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o   ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY p.category
ORDER BY total_amount DESC;
-- 结果:
-- category | total_qty | total_amount
-- 电子产品 |     2     |   12000.00
-- 服装     |     2     |     400.00


-- 需求 2:消费最高的用户(子查询 + 聚合)
SELECT user_id, SUM(amount) AS total_paid
FROM (
    -- 先把每条明细算成金额,作为内层子查询
    SELECT o.user_id, oi.quantity * p.price AS amount
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p     ON oi.product_id = p.id
    WHERE o.status = 'paid'
) detail
GROUP BY user_id
ORDER BY total_paid DESC
LIMIT 3;
-- 结果:
-- user_id | total_paid
--    1    |   8400.00
--    2    |   4000.00


-- 需求 3:每个品类最畅销的商品(窗口函数 ROW_NUMBER)
SELECT product_name, category, sold_qty
FROM (
    SELECT p.name          AS product_name,
           p.category,
           SUM(oi.quantity) AS sold_qty,
           -- 按品类分组、按销量降序排名
           ROW_NUMBER() OVER (PARTITION BY p.category
                              ORDER BY SUM(oi.quantity) DESC) AS rn
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    GROUP BY p.id, p.name, p.category
) ranked
WHERE rn = 1;          -- 只取每组第 1 名
-- 结果:
-- product_name | category | sold_qty
-- iPhone       | 电子产品 |    1
-- T恤          | 服装     |    2


-- 需求 4:每日订单的"累计总金额"(窗口函数 SUM OVER)
SELECT created_at,
       SUM(daily_total) AS daily_amount,
       -- 按日期排序,把之前的金额全部累加起来
       SUM(SUM(daily_total)) OVER (ORDER BY created_at) AS running_total
FROM (
    SELECT o.created_at,
           oi.quantity * p.price AS daily_total
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p     ON oi.product_id = p.id
    WHERE o.status = 'paid'
) per_order
GROUP BY created_at
ORDER BY created_at;
-- 结果:
-- created_at | daily_amount | running_total
-- 2024-06-01 |     8400.00  |    8800.00
-- 2024-06-02 |     1500.00  |   10300.00

要点讲解:

实战 3:数据迁移与清洗(INSERT SELECT + UPDATE + CASE + 校验)

需求:把脏乱的旧表 users_raw 迁移到规范化的新表 users。迁移过程中要清洗字段(大小写统一、去空格)、剔除无效数据(缺姓名、邮箱非法)、去重(重复邮箱只留一条)、回填空值,最后做数据校验确认迁移无误。

涉及知识：INSERT ... SELECT 批量导入、字符串函数 LOWER / TRIM / CONCAT、CASE WHEN 条件分支、COALESCE / NULLIF 空值处理、DELETE + 子查询去重、LEFT JOIN 找差异、HAVING COUNT(*) > 1 找重复。

-- ============================================================
-- 项目 3:数据迁移与清洗
-- 场景:旧表 users_raw 数据脏(大小写不一/重复邮箱/缺昵称)
--       迁移到规范化新表 users,并在过程中清洗 + 校验
-- ============================================================

-- 旧表(假定已有数据,字段不规范)
CREATE TABLE users_raw (
    id       INT,
    name     VARCHAR(50),     -- 大小写不统一,如 'Alice' / 'ALICE'
    email    VARCHAR(100),    -- 有重复、有空格、有大小写不一
    age      INT              -- 可能有 NULL 或异常值
);

INSERT INTO users_raw VALUES
(1, 'Alice', 'Alice@Example.com',  25),
(2, 'BOB',   'bob@example.com',    17),
(3, 'alice', 'alice@example.com',  61),    -- 与 1 号邮箱重复(大小写)
(4, 'Tom',   'tom@example.com',    NULL),  -- 年龄缺失
(5, NULL,    'bad@example',        30);    -- 姓名缺失且邮箱非法

-- 新表(规范化)
CREATE TABLE users (
    id        INT PRIMARY KEY,
    username  VARCHAR(50)  NOT NULL,
    email     VARCHAR(100) NOT NULL,
    nickname  VARCHAR(50),
    age_group VARCHAR(10),
    source    VARCHAR(20)  DEFAULT 'migrated'
);


-- 需求 1:从旧表批量导入并清洗(INSERT ... SELECT + 函数 + CASE)
INSERT INTO users (id, username, email, nickname, age_group)
SELECT
    id,
    LOWER(TRIM(name))                              AS username,    -- 统一小写 + 去空格
    LOWER(TRIM(email))                             AS email,       -- 邮箱同样规范化
    name                                           AS nickname,    -- 原名留作昵称
    CASE
        WHEN age < 18                THEN 'minor'
        WHEN age BETWEEN 18 AND 59   THEN 'adult'
        WHEN age >= 60               THEN 'senior'
        ELSE 'unknown'                               -- NULL 或非法值
    END                                            AS age_group
FROM users_raw
WHERE name IS NOT NULL                              -- 排除姓名缺失
  AND email LIKE '%_@_%._%';                        -- 粗略过滤非法邮箱
-- 影响 3 行:id=1,2,3 被导入(id=4 缺姓名被剔除?这里只是示例规则)


-- 需求 2:找出重复 email,只保留 id 最小的那条
-- (MySQL 不能直接在 DELETE 里套本表子查询,需再包一层 SELECT)
DELETE FROM users
WHERE email IN (
    SELECT email FROM (
        SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1
    ) dup
)
AND id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) AS min_id FROM users GROUP BY email HAVING COUNT(*) > 1
    ) keep
);
-- 效果:id=3 与 id=1 邮箱重复,3 号被删除,1 号保留


-- 需求 3:批量回填缺失的昵称(UPDATE + COALESCE + CONCAT)
UPDATE users
SET nickname = COALESCE(NULLIF(nickname, ''), username, CONCAT('user_', id))
WHERE nickname IS NULL OR nickname = '';
-- COALESCE 返回第一个非 NULL 的参数:有原名用原名,否则用 username,再否则拼 user_123


-- 需求 4:迁移完成后做数据校验
-- (a) 总数对账:旧表"有效数据"应等于新表条数
SELECT
    (SELECT COUNT(*) FROM users_raw WHERE name IS NOT NULL AND email LIKE '%_@_%._%') AS raw_cnt,
    (SELECT COUNT(*) FROM users) AS new_cnt;
-- raw_cnt 与 new_cnt 应相等;不等就说明迁移有遗漏或重复


-- (b) 找出"漏迁"的数据:旧表有、新表没有的 id
SELECT r.id, r.name, r.email
FROM users_raw r
LEFT JOIN users u ON r.id = u.id
WHERE u.id IS NULL;
-- 这里会列出 id=4,5(被清洗规则剔除的),确认它们是"应该剔除"还是"漏迁"


-- (c) 唯一性校验:email 不应有重复
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- 期望返回空集;有结果说明去重没成功,需排查


-- (d) 完整性校验:NOT NULL 字段不该有空
SELECT id, username, email FROM users
WHERE username IS NULL OR email IS NULL;
-- 期望返回空集

要点讲解:

小结

三个项目对应 SQL 工程师三大主力场景：数据建模(建表 + 关系 + 索引)、分析查询(JOIN / 聚合 / 窗口)、数据治理(迁移 / 清洗 / 校验)。它们把前面学的 DDL / DML / WHERE / JOIN / GROUP BY / 子查询 / 索引 / 函数串到了一起——SQL 真正的难点从来不是单条语法,而是把它们组合起来解决业务问题。建议你拿自己工作或学习中的真实数据(订单、流水、日志)按这三个方向各做一遍,做过一遍比刷一百道 LeetCode 都扎实。

恭喜!到这里 SQL 系列就真正完结了。接下来可以深入——性能优化(EXPLAIN / 慢查询调优)、数据库内核(B+ 树 / MVCC / 隔离级别)、NoSQL(Redis / MongoDB)、大数据(Hive / ClickHouse)——方向很多,但核心 SQL 能力你已经具备。

← 上一篇 视图

返回 SQL 教程目录

✈️💬