实战项目

通过实际项目巩固 SQL 知识。本章提供完整的实战案例。

项目 1：博客系统

需求分析

• 用户可以发布文章
• 文章可以有多个标签
• 用户可以评论文章

数据库设计

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 文章表
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    view_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- 标签表
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL
);

-- 文章标签关联表
CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

-- 评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_post_id (post_id)
);

常用查询

-- 1. 获取文章列表（带作者和标签）
SELECT 
    p.id,
    p.title,
    u.username AS author,
    p.view_count,
    p.created_at,
    GROUP_CONCAT(t.name) AS tags
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id
ORDER BY p.created_at DESC
LIMIT 10;

-- 2. 获取文章详情（带评论）
SELECT 
    p.*,
    u.username AS author,
    c.id AS comment_id,
    c.content AS comment_content,
    cu.username AS commenter
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
LEFT JOIN users cu ON c.user_id = cu.id
WHERE p.id = 1;

-- 3. 热门标签
SELECT 
    t.name,
    COUNT(pt.post_id) AS post_count
FROM tags t
JOIN post_tags pt ON t.id = pt.tag_id
GROUP BY t.id
ORDER BY post_count DESC
LIMIT 10;

项目 2：在线商城

核心功能

• 商品管理
• 购物车
• 订单处理
• 库存管理

关键查询

-- 1. 添加到购物车
INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (1, 101, 2)
ON DUPLICATE KEY UPDATE quantity = quantity + 2;

-- 2. 创建订单
START TRANSACTION;

-- 创建订单
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 299.99, 'pending');

SET @order_id = LAST_INSERT_ID();

-- 添加订单项
INSERT INTO order_items (order_id, product_id, quantity, price)
SELECT @order_id, product_id, quantity, price
FROM cart_items
WHERE user_id = 1;

-- 更新库存
UPDATE products p
JOIN cart_items c ON p.id = c.product_id
SET p.stock = p.stock - c.quantity
WHERE c.user_id = 1;

-- 清空购物车
DELETE FROM cart_items WHERE user_id = 1;

COMMIT;

-- 3. 销售报表
SELECT 
    DATE(o.created_at) AS date,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_amount) AS total_sales,
    AVG(o.total_amount) AS avg_order_value
FROM orders o
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND o.status = 'paid'
GROUP BY DATE(o.created_at)
ORDER BY date;

练习题

初级

1. 查询所有用户及其订单数量
2. 查询价格最高的 10 个商品
3. 统计每个分类的商品数量

中级

4. 查询最近 7 天每天的订单数量
5. 查询购买过商品 A 但没购买过商品 B 的用户
6. 查询每个用户的消费总额排名

高级

7. 实现商品推荐（购买了 A 的用户还购买了什么）
8. 计算用户留存率
9. 实现库存预警系统

小结

• 通过实战项目学习 SQL
• 理解业务需求
• 设计合理的数据库结构
• 编写高效的查询

下一步： 学习 RESOURCES 学习资源