SQL实战

引言 本文通过电商订单系统的实战案例，综合运用前面学到的SQL知识，实现各种复杂的业务查询。 一、表结构设计 1.1 核心表 -- 用户表 CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(100), phone VARCHAR(20), register_date DATE, level INT DEFAULT 1 -- 用户等级 ); -- 商品表 CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200) NOT NULL, category VARCHAR(50), price DECIMAL(10,2), stock INT DEFAULT 0, sales INT DEFAULT 0 ); -- 订单表 CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) UNIQUE, user_id INT, total_amount DECIMAL(10,2), status VARCHAR(20), -- pending, paid, shipped, completed, cancelled created_at DATETIME, paid_at DATETIME, FOREIGN KEY (user_id) REFERENCES users(id) ); -- 订单明细表 CREATE TABLE order_items ( id INT PRIMARY KEY AUTO_INCREMENT, order_id INT, product_id INT, quantity INT, price DECIMAL(10,2), -- 下单时价格 FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ); 二、基础查询实战 2.1 用户订单查询 -- 查询用户的所有订单 SELECT u.username, o.order_no, o.total_amount, o.status, DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i') AS order_time FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.id = 1 ORDER BY o.created_at DESC; 2.2 订单详情查询 -- 查询订单明细（包含商品信息） SELECT o.order_no, p.name AS product_name, oi.quantity, oi.price, oi.quantity * oi.price AS subtotal FROM orders o INNER JOIN order_items oi ON o.id = oi.order_id INNER JOIN products p ON oi.product_id = p.id WHERE o.order_no = 'ORD20241121000001'; 三、统计分析实战 3.1 用户消费统计 -- 统计每个用户的消费情况 SELECT u.id, u.username, u.level, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent, ROUND(AVG(o.total_amount), 2) AS avg_order_value, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.level ORDER BY total_spent DESC; 3.2 商品销售排行 -- 销量Top 10商品 SELECT p.name, p.category, SUM(oi.quantity) AS total_sales, SUM(oi.quantity * oi.price) AS total_revenue FROM products p INNER JOIN order_items oi ON p.id = oi.product_id INNER JOIN orders o ON oi.order_id = o.id WHERE o.status = 'completed' GROUP BY p.id, p.name, p.category ORDER BY total_sales DESC LIMIT 10; 3.3 按月销售统计 -- 每月销售额趋势 SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, COUNT(*) AS order_count, COUNT(DISTINCT user_id) AS customer_count, SUM(total_amount) AS total_sales, ROUND(AVG(total_amount), 2) AS avg_order_value FROM orders WHERE status = 'completed' AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) GROUP BY DATE_FORMAT(created_at, '%Y-%m') ORDER BY month; 四、高级查询实战 4.1 用户购买行为分析 -- 分析用户首次购买、最近购买、购买频次 SELECT u.username, u.register_date, MIN(o.created_at) AS first_order_date, MAX(o.created_at) AS last_order_date, COUNT(o.id) AS order_count, TIMESTAMPDIFF(DAY, u.register_date, MIN(o.created_at)) AS days_to_first_order, TIMESTAMPDIFF(DAY, MIN(o.created_at), MAX(o.created_at)) AS active_days FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.register_date HAVING order_count > 0; 4.2 复购率分析 -- 计算复购率（购买2次及以上的用户占比） SELECT COUNT(DISTINCT user_id) AS total_customers, COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) AS repeat_customers, ROUND(COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repeat_rate FROM ( SELECT user_id, COUNT(*) AS order_count FROM orders WHERE status = 'completed' GROUP BY user_id ) t; 4.3 客户分层（RFM模型） -- RFM分析：Recency（最近购买）、Frequency（购买频次）、Monetary（消费金额） SELECT user_id, username, DATEDIFF(NOW(), last_order_date) AS recency, order_count AS frequency, total_amount AS monetary, CASE WHEN DATEDIFF(NOW(), last_order_date) <= 30 AND order_count >= 5 AND total_amount >= 10000 THEN 'VIP客户' WHEN DATEDIFF(NOW(), last_order_date) <= 90 AND order_count >= 3 THEN '活跃客户' WHEN DATEDIFF(NOW(), last_order_date) > 180 THEN '流失客户' ELSE '普通客户' END AS customer_segment FROM ( SELECT u.id AS user_id, u.username, MAX(o.created_at) AS last_order_date, COUNT(o.id) AS order_count, SUM(o.total_amount) AS total_amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username ) t ORDER BY monetary DESC; 五、复杂业务场景 5.1 购物车转化率 -- 统计下单未支付的订单（购物车） SELECT COUNT(*) AS cart_count, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count, ROUND(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS conversion_rate FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY); 5.2 商品关联分析 -- 查找经常一起购买的商品 SELECT p1.name AS product1, p2.name AS product2, COUNT(*) AS co_purchase_count FROM order_items oi1 INNER JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id INNER JOIN products p1 ON oi1.product_id = p1.id INNER JOIN products p2 ON oi2.product_id = p2.id GROUP BY p1.id, p1.name, p2.id, p2.name ORDER BY co_purchase_count DESC LIMIT 10; 5.3 库存预警 -- 库存不足且销售较好的商品 SELECT name, category, stock, sales, CASE WHEN stock = 0 THEN '缺货' WHEN stock < sales * 0.1 THEN '严重不足' WHEN stock < sales * 0.3 THEN '库存紧张' ELSE '正常' END AS stock_status FROM products WHERE stock < sales * 0.3 ORDER BY stock_status, sales DESC; 六、性能优化实战 6.1 创建索引 -- 为常用查询字段创建索引 CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_status ON orders(status); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_order_id ON order_items(order_id); CREATE INDEX idx_product_id ON order_items(product_id); -- 联合索引 CREATE INDEX idx_status_user_id ON orders(status, user_id); CREATE INDEX idx_status_created_at ON orders(status, created_at); 6.2 创建视图简化查询 -- 用户订单统计视图 CREATE VIEW v_user_order_stats AS SELECT u.id AS user_id, u.username, u.level, COUNT(o.id) AS order_count, IFNULL(SUM(o.total_amount), 0) AS total_spent, MAX(o.created_at) AS last_order_date FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' GROUP BY u.id, u.username, u.level; -- 使用视图 SELECT * FROM v_user_order_stats WHERE total_spent > 10000; 七、总结 知识点回顾 本案例综合运用了： ...