电商系统SQL实战

概述

本章节通过一个完整的电商系统案例，展示SQL在实际业务场景中的应用。我们将从数据库设计开始，逐步实现各种业务功能的SQL查询。

数据库设计

核心表结构

用户表 (users)

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active'
);

商品表 (products)

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category_id INT,
    brand VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_status (status)
);

订单表 (orders)

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    payment_method ENUM('credit_card', 'debit_card', 'paypal', 'alipay', 'wechat') NOT NULL,
    shipping_address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

订单详情表 (order_items)

CREATE TABLE order_items (
    item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
);

商品分类表 (categories)

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    parent_id INT DEFAULT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(category_id),
    INDEX idx_parent (parent_id)
);

业务场景实战

1. 用户管理

用户注册

-- 检查用户名和邮箱是否已存在
SELECT COUNT(*) as count 
FROM users 
WHERE username = 'john_doe' OR email = 'john@example.com';

-- 插入新用户
INSERT INTO users (username, email, password_hash, phone) 
VALUES ('john_doe', 'john@example.com', 'hashed_password', '13800138000');

用户登录验证

SELECT user_id, username, password_hash, status
FROM users 
WHERE (username = 'john_doe' OR email = 'john@example.com') 
  AND status = 'active';

获取用户详细信息

SELECT 
    u.user_id,
    u.username,
    u.email,
    u.phone,
    u.created_at,
    COUNT(o.order_id) as total_orders,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.status != 'cancelled'
WHERE u.user_id = 1
GROUP BY u.user_id;

2. 商品管理

商品搜索

-- 按关键词搜索商品
SELECT 
    p.product_id,
    p.name,
    p.price,
    p.stock_quantity,
    c.name as category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'active'
  AND (p.name LIKE '%手机%' OR p.description LIKE '%手机%')
ORDER BY p.created_at DESC
LIMIT 20;

按分类获取商品

-- 获取指定分类及其子分类的所有商品
WITH RECURSIVE category_tree AS (
    -- 起始分类
    SELECT category_id, name, parent_id
    FROM categories
    WHERE category_id = 1
    
    UNION ALL
    
    -- 递归获取子分类
    SELECT c.category_id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT 
    p.product_id,
    p.name,
    p.price,
    p.stock_quantity,
    c.name as category_name
FROM products p
INNER JOIN category_tree ct ON p.category_id = ct.category_id
INNER JOIN categories c ON p.category_id = c.category_id
WHERE p.status = 'active'
ORDER BY p.price ASC;

热销商品排行

SELECT 
    p.product_id,
    p.name,
    p.price,
    SUM(oi.quantity) as total_sold,
    SUM(oi.total_price) as total_revenue
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status IN ('paid', 'shipped', 'delivered')
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY p.product_id, p.name, p.price
ORDER BY total_sold DESC
LIMIT 10;

3. 订单管理

创建订单

-- 开始事务
START TRANSACTION;

-- 检查库存
SELECT product_id, stock_quantity 
FROM products 
WHERE product_id IN (1, 2, 3) 
FOR UPDATE;

-- 创建订单
INSERT INTO orders (user_id, order_number, total_amount, payment_method, shipping_address)
VALUES (1, 'ORD20231201001', 299.99, 'credit_card', '北京市朝阳区xxx街道xxx号');

SET @order_id = LAST_INSERT_ID();

-- 添加订单项
INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
VALUES 
    (@order_id, 1, 2, 99.99, 199.98),
    (@order_id, 2, 1, 100.01, 100.01);

-- 更新库存
UPDATE products SET stock_quantity = stock_quantity - 2 WHERE product_id = 1;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 2;

-- 提交事务
COMMIT;

订单状态更新

-- 更新订单状态为已支付
UPDATE orders 
SET status = 'paid', updated_at = CURRENT_TIMESTAMP 
WHERE order_id = 1 AND status = 'pending';

-- 批量更新超时未支付订单为已取消
UPDATE orders 
SET status = 'cancelled', updated_at = CURRENT_TIMESTAMP 
WHERE status = 'pending' 
  AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE);

订单详情查询

SELECT 
    o.order_id,
    o.order_number,
    o.total_amount,
    o.status,
    o.payment_method,
    o.created_at,
    u.username,
    u.email,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'product_id', oi.product_id,
            'product_name', p.name,
            'quantity', oi.quantity,
            'unit_price', oi.unit_price,
            'total_price', oi.total_price
        )
    ) as order_items
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_id = 1
GROUP BY o.order_id;

4. 数据分析查询

销售统计

-- 每日销售统计
SELECT 
    DATE(created_at) as sale_date,
    COUNT(*) as order_count,
    SUM(total_amount) as daily_revenue,
    AVG(total_amount) as avg_order_value
FROM orders 
WHERE status IN ('paid', 'shipped', 'delivered')
  AND created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY sale_date DESC;

用户行为分析

-- 用户购买频次分析
SELECT 
    CASE 
        WHEN order_count = 1 THEN '首次购买'
        WHEN order_count BETWEEN 2 AND 5 THEN '偶尔购买'
        WHEN order_count BETWEEN 6 AND 10 THEN '经常购买'
        ELSE '忠实客户'
    END as customer_type,
    COUNT(*) as user_count,
    AVG(total_spent) as avg_spent
FROM (
    SELECT 
        u.user_id,
        COUNT(o.order_id) as order_count,
        SUM(o.total_amount) as total_spent
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id AND o.status != 'cancelled'
    GROUP BY u.user_id
) user_stats
GROUP BY customer_type
ORDER BY avg_spent DESC;

商品分类销售分析

SELECT 
    c.name as category_name,
    COUNT(DISTINCT oi.product_id) as product_count,
    SUM(oi.quantity) as total_quantity,
    SUM(oi.total_price) as total_revenue,
    AVG(oi.unit_price) as avg_price
FROM categories c
INNER JOIN products p ON c.category_id = p.category_id
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status IN ('paid', 'shipped', 'delivered')
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY c.category_id, c.name
ORDER BY total_revenue DESC;

性能优化实践

索引优化

-- 为常用查询添加复合索引
CREATE INDEX idx_orders_user_status_date ON orders(user_id, status, created_at);
CREATE INDEX idx_products_category_status_price ON products(category_id, status, price);
CREATE INDEX idx_order_items_product_order ON order_items(product_id, order_id);

查询优化

-- 使用EXISTS替代IN提高性能
SELECT p.product_id, p.name, p.price
FROM products p
WHERE p.status = 'active'
  AND EXISTS (
      SELECT 1 FROM order_items oi 
      INNER JOIN orders o ON oi.order_id = o.order_id
      WHERE oi.product_id = p.product_id 
        AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  );

常见问题解决

1. 库存并发问题

-- 使用乐观锁解决库存并发
UPDATE products 
SET stock_quantity = stock_quantity - 1, 
    updated_at = CURRENT_TIMESTAMP
WHERE product_id = 1 
  AND stock_quantity >= 1
  AND updated_at = '2023-12-01 10:00:00';

2. 订单号重复问题

-- 使用UUID生成唯一订单号
SELECT CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(UUID_SHORT() % 1000000, 6, '0')) as order_number;

3. 分页性能优化

-- 使用游标分页替代OFFSET
SELECT * FROM orders 
WHERE order_id > 1000 
ORDER BY order_id 
LIMIT 20;

最佳实践

1. 事务管理：确保数据一致性，合理使用事务
2. 索引设计：根据查询模式设计合适的索引
3. 查询优化：避免全表扫描，使用合适的查询条件
4. 数据类型：选择合适的数据类型，节省存储空间
5. 安全性：使用参数化查询，防止SQL注入
6. 监控：定期监控慢查询，及时优化

总结

通过电商系统的实战案例，我们学习了：

• 复杂业务场景的数据库设计
• 各种业务功能的SQL实现
• 性能优化的实践方法
• 常见问题的解决方案

这些经验可以应用到其他类似的业务系统中，帮助开发者更好地使用SQL解决实际问题。