一：表数据
1: 用户表
---创建用户表 -- 创建用户表 CREATE TABLE users ( user_id INT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), register_date DATE, user_type VARCHAR(20) DEFAULT 'normal', -- normal/test/vip city VARCHAR(50), last_login_date DATE ); -- 插入测试数据 INSERT INTO users (user_id, username, email, phone, register_date, user_type, city, last_login_date) VALUES (101, '张三', 'zhangsan@example.com', '13800138001', '2023-10-15', 'normal', '北京', '2024-01-28'), (102, '李四', 'lisi@example.com', '13800138002', '2023-11-20', 'normal', '上海', '2024-01-26'), (103, '王五', 'wangwu@example.com', '13800138003', '2023-12-05', 'vip', '广州', '2024-02-01'), (104, '赵六', 'zhaoliu@example.com', '13800138004', '2024-01-10', 'normal', '深圳', '2024-01-25'), (105, '测试用户1', 'test1@example.com', '13800138005', '2024-01-01', 'test', '测试城市', '2024-01-15'), (106, '孙七', 'sunqi@example.com', '13800138006', '2023-09-10', 'vip', '杭州', '2024-01-30'), (107, '周八', 'zhouba@example.com', '13800138007', '2023-08-22', 'normal', '南京', '2024-01-28'), (108, '吴九', 'wujiu@example.com', '13800138008', '2024-01-05', 'normal', '成都', '2024-01-29');2：订单表 (orders)- 主订单信息
-- 创建订单表 CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, order_number VARCHAR(50) UNIQUE, order_date DATETIME, total_amount DECIMAL(10,2), status VARCHAR(20) DEFAULT 'pending', -- pending/paid/shipped/completed/cancelled payment_method VARCHAR(30), shipping_address VARCHAR(200), FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ); -- 插入测试数据 (包含最近一个月和一个月前的数据) INSERT INTO orders (order_id, user_id, order_number, order_date, total_amount, status, payment_method, shipping_address) VALUES -- 最近30天内的订单 (假设今天是2024-02-10) (1, 101, 'ORD202401151001', '2024-01-15 10:30:00', 300.00, 'completed', 'alipay', '北京市朝阳区'), (2, 102, 'ORD202401161002', '2024-01-16 14:20:00', 150.00, 'completed', 'wechat', '上海市浦东新区'), (3, 101, 'ORD202401181003', '2024-01-18 09:15:00', 450.00, 'completed', 'alipay', '北京市海淀区'), (4, 103, 'ORD202401201004', '2024-01-20 16:45:00', 600.00, 'completed', 'credit_card', '广州市天河区'), (5, 101, 'ORD202401221005', '2024-01-22 11:10:00', 500.00, 'completed', 'alipay', '北京市朝阳区'), (6, 102, 'ORD202401251006', '2024-01-25 13:30:00', 200.00, 'completed', 'wechat', '上海市徐汇区'), (7, 104, 'ORD202401281007', '2024-01-28 15:20:00', 800.00, 'completed', 'alipay', '深圳市南山区'), (8, 101, 'ORD202401311008', '2024-01-31 10:05:00', 350.00, 'completed', 'alipay', '北京市朝阳区'), (9, 106, 'ORD202402011009', '2024-02-01 14:40:00', 1200.00, 'completed', 'credit_card', '杭州市西湖区'), (10, 107, 'ORD202402021010', '2024-02-02 09:50:00', 250.00, 'completed', 'alipay', '南京市鼓楼区'), (11, 103, 'ORD202402031011', '2024-02-03 16:15:00', 750.00, 'completed', 'wechat', '广州市越秀区'), (12, 101, 'ORD202402051012', '2024-02-05 11:25:00', 420.00, 'completed', 'alipay', '北京市朝阳区'), (13, 108, 'ORD202402061013', '2024-02-06 13:10:00', 180.00, 'completed', 'wechat', '成都市锦江区'), (14, 103, 'ORD202402081014', '2024-02-08 10:45:00', 900.00, 'completed', 'credit_card', '广州市天河区'), -- 30天前的订单 (用于测试时间过滤) (15, 101, 'ORD202312101015', '2023-12-10 09:30:00', 280.00, 'completed', 'alipay', '北京市朝阳区'), (16, 102, 'ORD202312151016', '2023-12-15 14:20:00', 320.00, 'completed', 'wechat', '上海市浦东新区'), (17, 105, 'ORD202312201017', '2023-12-20 10:15:00', 150.00, 'completed', 'alipay', '测试城市'), (18, 105, 'ORD202312251018', '2023-12-25 11:30:00', 200.00, 'completed', 'alipay', '测试城市');3.订单详情表 (order_items)- 订单商品明细
-- 创建订单详情表 CREATE TABLE order_items ( item_id INT PRIMARY KEY, order_id INT, product_id INT, product_name VARCHAR(100), quantity INT, unit_price DECIMAL(10,2), subtotal DECIMAL(10,2), FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE ); -- 插入测试数据 INSERT INTO order_items (item_id, order_id, product_id, product_name, quantity, unit_price, subtotal) VALUES (1, 1, 1001, 'iPhone 15', 1, 6999.00, 6999.00), (2, 1, 1002, 'AirPods Pro', 1, 1499.00, 1499.00), (3, 2, 1003, '小米电视', 1, 2999.00, 2999.00), (4, 3, 1004, '华为笔记本', 1, 5999.00, 5999.00), (5, 4, 1005, '索尼耳机', 1, 899.00, 899.00), (6, 5, 1006, '戴尔显示器', 1, 1999.00, 1999.00), (7, 6, 1007, '罗技键盘', 1, 499.00, 499.00), (8, 7, 1008, '三星手机', 1, 3999.00, 3999.00), (9, 8, 1009, '联想平板', 1, 1999.00, 1999.00), (10, 9, 1010, '佳能相机', 1, 5999.00, 5999.00), (11, 10, 1011, '美的空调', 1, 2999.00, 2999.00), (12, 11, 1012, '海尔冰箱', 1, 3999.00, 3999.00), (13, 12, 1013, '格力洗衣机', 1, 2999.00, 2999.00), (14, 13, 1014, '苏泊尔电饭煲', 1, 299.00, 299.00), (15, 14, 1015, '九阳豆浆机', 1, 399.00, 399.00);4.支付表 (payments)- 支付信息
-- 创建支付表 CREATE TABLE payments ( payment_id INT PRIMARY KEY, order_id INT, payment_date DATETIME, amount DECIMAL(10,2), payment_status VARCHAR(20), -- success/failed/pending transaction_id VARCHAR(100), FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE ); -- 插入测试数据 INSERT INTO payments (payment_id, order_id, payment_date, amount, payment_status, transaction_id) VALUES (1, 1, '2024-01-15 10:35:00', 300.00, 'success', 'TRX00120240115'), (2, 2, '2024-01-16 14:25:00', 150.00, 'success', 'TRX00220240116'), (3, 3, '2024-01-18 09:20:00', 450.00, 'success', 'TRX00320240118'), (4, 4, '2024-01-20 16:50:00', 600.00, 'success', 'TRX00420240120'), (5, 5, '2024-01-22 11:15:00', 500.00, 'success', 'TRX00520240122'), (6, 6, '2024-01-25 13:35:00', 200.00, 'success', 'TRX00620240125'), (7, 7, '2024-01-28 15:25:00', 800.00, 'success', 'TRX00720240128'), (8, 8, '2024-01-31 10:10:00', 350.00, 'success', 'TRX00820240131'), (9, 9, '2024-02-01 14:45:00', 1200.00, 'success', 'TRX00920240201'), (10, 10, '2024-02-02 09:55:00', 250.00, 'success', 'TRX01020240202');
