Mysql数据准备

本节目标：

1、理解电商 AI 系统的数据基础结构

2、掌握 MySQL 数据库的基础搭建流程

3、学会构建模拟业务数据

一数据准备说明

在本项目中，我们使用 MySQL 构建电商业务数据，用于支撑 AI 系统完成数据查询、统计分析和业务问答。

整体数据围绕四张核心表：

user（用户表）：存储用户信息，用于用户分析、地域分析

product（商品表）：存储商品信息，用于商品和类目分析

orders（订单表）：存储订单数据，用于销售额和趋势分析

order_item（订单明细表）：记录订单中的商品信息，是销量分析的核心

同时，脚本中还插入了完整的模拟数据（用户、商品、订单等），使系统可以直接进行真实场景的数据分析。

此外，还提供了表结构和数据查看语句，方便快速验证数据是否准备成功。

二建数据库

CREATE DATABASE IF NOT EXISTS sales_analysis_db
    DEFAULT CHARACTER SET utf8mb4;

三建表

USE sales_analysis_db;

CREATE TABLE IF NOT EXISTS user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID，主键',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    city VARCHAR(50) NOT NULL COMMENT '用户所在城市，用于地域分析',
    register_time DATETIME NOT NULL COMMENT '用户注册时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表，存储用户基础信息';

CREATE TABLE IF NOT EXISTS product (
   id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '商品ID，主键',
   product_name VARCHAR(100) NOT NULL COMMENT '商品名称',
   category VARCHAR(50) NOT NULL COMMENT '商品分类，如手机、电脑',
   price DECIMAL(10,2) NOT NULL COMMENT '商品单价'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表，存储商品信息';

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID，主键',
    user_id BIGINT NOT NULL COMMENT '用户ID，关联user表',
    total_amount DECIMAL(10,2) NOT NULL COMMENT '订单总金额',
    order_time DATETIME NOT NULL COMMENT '下单时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表，存储每一笔订单';

CREATE TABLE IF NOT EXISTS order_item (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '明细ID，主键',
    order_id BIGINT NOT NULL COMMENT '订单ID，关联orders表',
    product_id BIGINT NOT NULL COMMENT '商品ID，关联product表',
    quantity INT NOT NULL COMMENT '购买数量',
    total_price DECIMAL(10,2) NOT NULL COMMENT '该商品总金额'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表，存储每个订单中的商品信息';

四插入用户表数据

USE sales_analysis_db;

INSERT INTO user (id, username, city, register_time) VALUES
  (1, '老李', '北京', '2023-10-09 07:00:00'),
  (2, '美美', '广州', '2023-04-15 21:00:00'),
  (3, '大庆', '上海', '2024-08-27 13:00:00'),
  (4, '陈静', '北京', '2023-04-06 06:00:00'),
  (5, '娜娜', '南京', '2024-09-08 00:00:00'),
  (6, '老周', '深圳', '2024-10-27 22:00:00'),
  (7, '大庆', '武汉', '2023-08-14 14:00:00'),
  (8, '梅姐', '杭州', '2023-01-07 05:00:00'),
  (9, '璐璐', '成都', '2023-10-12 04:00:00'),
  (10, '阿强', '成都', '2023-04-15 02:00:00'),
  (11, '佳佳', '上海', '2024-01-03 11:00:00'),
  (12, '云姐', '杭州', '2023-02-14 23:00:00'),
  (13, '乐乐', '南京', '2023-05-08 12:00:00'),
  (14, '小明', '南京', '2023-10-28 20:00:00'),
  (15, '春梅', '成都', '2024-08-14 06:00:00'),
  (16, '周杰', '北京', '2024-11-08 07:00:00'),
  (17, '洋洋', '上海', '2023-08-27 03:00:00'),
  (18, '佳佳', '杭州', '2024-04-09 20:00:00'),
  (19, '磊磊', '广州', '2024-01-15 11:00:00'),
  (20, '小北', '杭州', '2024-11-30 20:00:00'),
  (21, '吴磊', '重庆', '2024-10-12 05:00:00'),
  (22, '娟子', '深圳', '2023-06-17 14:00:00'),
  (23, '佳佳', '杭州', '2024-10-17 22:00:00'),
  (24, '老周', '深圳', '2023-11-29 01:00:00'),
  (25, '娜娜', '北京', '2023-11-20 12:00:00'),
  (26, '赵哥', '上海', '2023-08-05 18:00:00'),
  (27, '阿超', '深圳', '2024-11-02 15:00:00'),
  (28, '倩倩', '西安', '2023-05-27 08:00:00'),
  (29, '小丽', '深圳', '2024-07-28 17:00:00'),
  (30, '李姨', '重庆', '2024-03-14 18:00:00'),
  (31, '峰峰', '成都', '2023-08-13 04:00:00'),
  (32, '阿龙', '西安', '2023-04-04 01:00:00'),
  (33, '老李', '广州', '2024-10-04 05:00:00'),
  (34, '璐璐', '重庆', '2023-03-07 12:00:00'),
  (35, '佳佳', '重庆', '2024-04-24 16:00:00'),
  (36, '王叔', '南京', '2023-01-12 21:00:00'),
  (37, '老李', '南京', '2023-10-01 20:00:00'),
  (38, '萍萍', '上海', '2023-10-28 13:00:00'),
  (39, '建国', '西安', '2023-01-04 23:00:00'),
  (40, '李姨', '南京', '2023-07-02 16:00:00'),
  (41, '婷婷', '杭州', '2024-10-16 16:00:00'),
  (42, '云姐', '深圳', '2023-06-06 11:00:00'),
  (43, '建国', '南京', '2024-06-27 00:00:00'),
  (44, '强子', '成都', '2024-05-15 00:00:00'),
  (45, '老李', '成都', '2023-11-11 07:00:00'),
  (46, '黄磊', '深圳', '2024-08-03 02:00:00'),
  (47, '小明', '西安', '2023-03-12 17:00:00'),
  (48, '大伟', '广州', '2024-11-06 15:00:00'),
  (49, '小敏', '广州', '2023-09-29 16:00:00'),
  (50, '云姐', '武汉', '2023-08-05 17:00:00'),
  (51, '铁军', '杭州', '2024-02-13 21:00:00'),
  (52, '林林', '西安', '2024-06-13 14:00:00'),
  (53, '王姐', '深圳', '2023-08-19 02:00:00'),
  (54, '萍萍', '北京', '2024-08-25 17:00:00'),
  (55, '娜娜', '重庆', '2023-08-14 00:00:00'),
  (56, '吴磊', '北京', '2023-08-23 02:00:00'),
  (57, '陈静', '成都', '2023-03-14 16:00:00'),
  (58, '大壮', '杭州', '2024-11-16 15:00:00'),
  (59, '阿强', '南京', '2023-05-16 23:00:00'),
  (60, '大刘', '重庆', '2024-04-29 07:00:00'),
  (61, '朵朵', '武汉', '2023-07-14 03:00:00'),
  (62, '阿杰', '武汉', '2023-12-29 13:00:00'),
  (63, '欣欣', '西安', '2023-02-25 21:00:00'),
  (64, '阿杰', '北京', '2024-02-17 23:00:00'),
  (65, '萍萍', '上海', '2023-09-12 06:00:00'),
  (66, '桂花', '南京', '2024-04-04 04:00:00'),
  (67, '璐璐', '广州', '2023-10-13 14:00:00'),
  (68, '二丫', '上海', '2024-03-29 17:00:00'),
  (69, '阿杰', '北京', '2024-10-29 17:00:00'),
  (70, '李娜', '上海', '2023-08-31 05:00:00'),
  (71, '欣欣', '西安', '2024-05-07 06:00:00'),
  (72, '峰峰', '北京', '2023-06-18 12:00:00'),
  (73, '张伟', '武汉', '2023-09-29 14:00:00'),
  (74, '周周', '武汉', '2024-07-23 21:00:00'),
  (75, '丫丫', '广州', '2023-07-14 09:00:00'),
  (76, '阿强', '北京', '2024-08-16 23:00:00'),
  (77, '大庆', '北京', '2023-11-18 01:00:00'),
  (78, '赵敏', '重庆', '2024-05-03 16:00:00'),
  (79, '华子', '广州', '2023-02-28 16:00:00'),
  (80, '小明', '广州', '2023-03-12 19:00:00'),
  (81, '周杰', '深圳', '2024-02-18 03:00:00'),
  (82, '小陈', '深圳', '2024-08-15 19:00:00'),
  (83, '杨洋', '重庆', '2023-03-25 13:00:00'),
  (84, '阿斌', '重庆', '2024-06-19 10:00:00'),
  (85, '李姨', '深圳', '2024-11-16 22:00:00'),
  (86, '阿超', '深圳', '2023-09-29 12:00:00'),
  (87, '大伟', '杭州', '2024-04-13 10:00:00'),
  (88, '吴磊', '北京', '2024-04-14 19:00:00'),
  (89, '小陈', '上海', '2023-03-17 17:00:00'),
  (90, '阿强', '南京', '2023-09-29 04:00:00'),
  (91, '军军', '上海', '2023-09-08 11:00:00'),
  (92, '周周', '广州', '2024-03-24 17:00:00'),
  (93, '萌萌', '重庆', '2024-10-31 16:00:00'),
  (94, '李娜', '南京', '2023-11-03 21:00:00'),
  (95, '婷婷', '广州', '2023-09-28 03:00:00'),
  (96, '婷婷', '南京', '2023-06-09 08:00:00'),
  (97, '周周', '重庆', '2023-08-04 22:00:00'),
  (98, '萍萍', '深圳', '2024-10-11 08:00:00'),
  (99, '丽丽', '西安', '2023-09-15 01:00:00'),
  (100, '小红', '武汉', '2023-10-11 01:00:00'),
  (101, '张伟', '成都', '2023-05-14 20:00:00'),
  (102, '李姨', '广州', '2024-03-28 17:00:00'),
  (103, '璐璐', '南京', '2023-01-10 03:00:00'),
  (104, '吴磊', '广州', '2024-07-12 01:00:00'),
  (105, '林林', '重庆', '2024-07-19 04:00:00'),
  (106, '凯凯', '广州', '2023-02-12 09:00:00'),
  (107, '磊磊', '北京', '2024-01-02 06:00:00'),
  (108, '二丫', '上海', '2023-12-29 17:00:00'),
  (109, '欣欣', '重庆', '2023-06-08 07:00:00'),
  (110, '建国', '广州', '2024-02-27 00:00:00'),
  (111, '海燕', '成都', '2024-02-26 21:00:00'),
  (112, '二丫', '杭州', '2023-06-13 22:00:00'),
  (113, '婷婷', '武汉', '2023-02-09 15:00:00'),
  (114, '美美', '深圳', '2024-04-16 11:00:00'),
  (115, '阿涛', '深圳', '2023-08-17 00:00:00'),
  (116, '桂花', '武汉', '2023-12-03 08:00:00'),
  (117, '周杰', '杭州', '2023-12-26 20:00:00'),
  (118, '阿龙', '武汉', '2024-11-26 17:00:00'),
  (119, '东东', '北京', '2023-04-29 08:00:00'),
  (120, '海燕', '重庆', '2023-09-29 01:00:00');

五插入产品表数据

USE sales_analysis_db;

INSERT INTO product (id, product_name, category, price) VALUES
  (1, 'iPhone 15 Pro', '手机', 1092.74),
  (2, 'MacBook Air M3', '电脑', 4350.8),
  (3, '海尔冰箱BCD-500', '家电', 7287.41),
  (4, 'AirPods Pro 2', '数码', 3143.25),
  (5, '得力订书机套装', '办公', 6065.38),
  (6, '优衣库纯棉T恤', '服饰', 5118.56),
  (7, '蒙牛纯牛奶250ml*16', '食品', 3857.66),
  (8, '《Python编程从入门到实践》', '图书', 5769.5),
  (9, 'Keep瑜伽垫', '运动', 2554.35),
  (10, '兰蔻小黑瓶精华', '美妆', 7090.03),
  (11, '小米14 Ultra', '手机', 26.79),
  (12, '联想小新Pro16', '电脑', 9255.56),
  (13, '美的空调酷省电1.5匹', '家电', 5388.55),
  (14, '小米手环8', '数码', 7196.36),
  (15, '齐心A4复印纸500张', '办公', 7421.31),
  (16, '李宁运动鞋', '服饰', 6708.88),
  (17, '三只松鼠坚果礼盒', '食品', 3648.14),
  (18, '《数据结构与算法》', '图书', 708.88),
  (19, '迪卡侬哑铃5kg', '运动', 6645.04),
  (20, '雅诗兰黛小棕瓶', '美妆', 3308.3),
  (21, '华为 Mate 60 Pro', '手机', 3145.63),
  (22, '戴尔灵越3530', '电脑', 8480.81),
  (23, '格力云佳挂机', '家电', 7199.6),
  (24, '罗技MX Master 3S', '数码', 3009.85),
  (25, '晨光中性笔一盒', '办公', 3099.38),
  (26, '波司登羽绒服', '服饰', 4089.38),
  (27, '奥利奥饼干', '食品', 4029.52),
  (28, '《活着》', '图书', 2963.23),
  (29, '斯伯丁篮球', '运动', 1281.39),
  (30, '欧莱雅洗面奶', '美妆', 4209.78),
  (31, 'OPPO Find X7', '手机', 9403.29),
  (32, '华硕天选5', '电脑', 6775.7),
  (33, '小米电视S Pro 65', '家电', 9028.11),
  (34, '西部数据移动硬盘2T', '数码', 6158.34),
  (35, '文件收纳盒', '办公', 3016.12),
  (36, '海澜之家衬衫', '服饰', 5483.3),
  (37, '金龙鱼大米5kg', '食品', 13.95),
  (38, '《三体》全集', '图书', 2875.91),
  (39, '尤尼克斯羽毛球拍', '运动', 4304.1),
  (40, '美宝莲口红', '美妆', 5803.43),
  (41, 'vivo X100 Pro', '手机', 6549.82),
  (42, '惠普战66六代', '电脑', 4654.71),
  (43, '西门子洗碗机', '家电', 4426.68),
  (44, '绿联拓展坞', '数码', 2144.58),
  (45, '白板记号笔', '办公', 4736.6),
  (46, '太平鸟牛仔裤', '服饰', 9011.89),
  (47, '康师傅方便面整箱', '食品', 7961.47),
  (48, '《人类简史》', '图书', 1704.96),
  (49, '跑步机家用折叠', '运动', 856.93),
  (50, '安耐晒防晒霜', '美妆', 5158.8),
  (51, '荣耀Magic6', '手机', 6332.41),
  (52, 'ThinkPad E14', '电脑', 3358.13),
  (53, '小天鹅滚筒洗衣机', '家电', 8185.21),
  (54, '索尼WH-1000XM5', '数码', 7513.09),
  (55, '人体工学椅', '办公', 6730.52),
  (56, '安踏卫衣', '服饰', 2253.86),
  (57, '农夫山泉矿泉水', '食品', 1999.03),
  (58, '《原则》', '图书', 253.89),
  (59, '动感单车', '运动', 2455.66),
  (60, '敷尔佳面膜', '美妆', 4756.08),
  (61, '一加12', '手机', 8498.01),
  (62, '华为MateBook 14', '电脑', 737.39),
  (63, '苏泊尔电饭煲', '家电', 4149.79),
  (64, '大疆 Osmo Pocket 3', '数码', 6300.69),
  (65, '办公桌1.2米', '办公', 1952.13),
  (66, '回力帆布鞋', '服饰', 6965.85),
  (67, '良品铺子肉脯', '食品', 4948.28),
  (68, '《深度工作》', '图书', 2447.08),
  (69, '跳绳计数款', '运动', 6563.33),
  (70, '施华蔻洗发水', '美妆', 65.29),
  (71, '红米K70', '手机', 7511.36),
  (72, '小米笔记本Pro', '电脑', 7701.97),
  (73, '九阳破壁机', '家电', 1074.61),
  (74, 'Kindle Paperwhite', '数码', 4256.73),
  (75, '惠普激光打印机', '办公', 1766.85),
  (76, '探路者冲锋衣', '服饰', 9579.12),
  (77, '百草味果干', '食品', 5183.83),
  (78, '《统计学习方法》', '图书', 511.54),
  (79, '护膝一对', '运动', 2499.17),
  (80, '玉兰油面霜', '美妆', 8484.02),
  (81, '三星 Galaxy S24', '手机', 4569.54),
  (82, '机械革命极光Pro', '电脑', 8015.33),
  (83, '戴森吸尘器V12', '家电', 6678.4),
  (84, '佳能打印机G3800', '数码', 9878.06),
  (85, '碎纸机小型', '办公', 5957.93),
  (86, '南极人保暖内衣', '服饰', 9499.94),
  (87, '德芙巧克力', '食品', 8914.44),
  (88, '《SQL必知必会》', '图书', 6129.75),
  (89, '泳镜泳帽套装', '运动', 7194.8),
  (90, '花西子散粉', '美妆', 5052.18),
  (91, '魅族21', '手机', 8306.54),
  (92, '宏碁暗影骑士', '电脑', 5482.65),
  (93, '石头扫地机器人', '家电', 8972.2),
  (94, '雷蛇键盘黑寡妇', '数码', 7438.35),
  (95, '保险柜家用', '办公', 4751.47),
  (96, '蕉下防晒衣', '服饰', 2598.99),
  (97, '洽洽瓜子', '食品', 2479.6),
  (98, '《精益数据分析》', '图书', 6379.56),
  (99, '登山杖', '运动', 7659.69),
  (100, '完美日记眼影盘', '美妆', 5217.22),
  (101, 'realme GT5 Pro', '手机', 6270.55),
  (102, '微软Surface Laptop', '电脑', 2752.88),
  (103, '方太油烟机', '家电', 783.89),
  (104, '飞利浦显示器27寸', '数码', 2864.07),
  (105, '投影仪便携', '办公', 2724.09),
  (106, '耐克运动袜', '服饰', 3203.51),
  (107, '统一阿萨姆奶茶', '食品', 5405.53),
  (108, '《红楼梦》', '图书', 1392.13),
  (109, '乒乓球拍双面反胶', '运动', 2319.99),
  (110, '资生堂红腰子', '美妆', 6941.83),
  (111, '努比亚Z60', '手机', 7066.39),
  (112, '雷蛇灵刃14', '电脑', 651.49),
  (113, '老板燃气灶', '家电', 4081.45),
  (114, '安克氮化镓充电器', '数码', 5430.1),
  (115, '会议麦克风', '办公', 4163.11),
  (116, '阿迪达斯棒球帽', '服饰', 2075.99),
  (117, '双汇火腿肠', '食品', 4206.76),
  (118, '《围城》', '图书', 9048.42),
  (119, '足球5号', '运动', 5844.33),
  (120, '珂润保湿乳液', '美妆', 6957.55);

六插入订单表数据

USE sales_analysis_db;

INSERT INTO orders (id, user_id, total_amount, order_time) VALUES
  (1, 110, 27773.06, '2026-03-24 03:38:45'),
  (2, 97, 36736.1, '2025-10-30 21:52:40'),
  (3, 115, 12859.61, '2025-06-21 07:08:21'),
  (4, 4, 55944.16, '2025-10-30 00:49:25'),
  (5, 108, 42420.1, '2025-12-30 00:47:01'),
  (6, 4, 4017.29, '2025-11-03 01:04:11'),
  (7, 24, 14423.71, '2025-02-09 01:22:24'),
  (8, 42, 30961.31, '2025-09-20 03:27:58'),
  (9, 61, 19416.55, '2025-01-16 01:19:50'),
  (10, 100, 13551.4, '2025-02-01 06:27:57'),
  (11, 108, 30709.1, '2025-01-16 19:54:39'),
  (12, 73, 38135.61, '2025-06-19 07:09:12'),
  (13, 22, 5803.43, '2025-03-30 23:02:50'),
  (14, 75, 29691.74, '2025-01-20 22:40:49'),
  (15, 10, 15156.54, '2025-07-08 15:02:26'),
  (16, 109, 50145.11, '2025-04-05 00:37:47'),
  (17, 9, 24238.58, '2026-01-28 23:47:33'),
  (18, 14, 48674.4, '2025-12-03 17:27:42'),
  (19, 56, 48209.57, '2025-05-17 19:25:51'),
  (20, 60, 43452.31, '2025-12-05 07:41:49'),
  (21, 36, 9918.4, '2025-12-17 03:33:30'),
  (22, 73, 19532.97, '2025-10-22 08:52:21'),
  (23, 24, 27035.67, '2026-01-14 16:35:22'),
  (24, 44, 32755.07, '2025-08-06 05:12:43'),
  (25, 11, 76923.98, '2025-07-07 11:12:10'),
  (26, 83, 30519.56, '2026-01-17 05:34:22'),
  (27, 29, 56936.59, '2025-11-11 02:35:00'),
  (28, 71, 46932.64, '2026-02-17 09:06:07'),
  (29, 90, 21620.07, '2025-12-19 10:25:29'),
  (30, 93, 46303.52, '2025-05-30 14:01:53'),
  (31, 25, 78970.97, '2025-06-18 01:42:07'),
  (32, 77, 10811.06, '2025-08-08 19:28:41'),
  (33, 38, 43352.8, '2025-06-26 16:35:36'),
  (34, 17, 19288.3, '2025-08-02 01:32:01'),
  (35, 38, 22572.51, '2025-04-09 02:01:13'),
  (36, 61, 15862.14, '2026-01-07 19:52:46'),
  (37, 111, 3203.51, '2026-01-07 01:31:03'),
  (38, 52, 4256.73, '2026-01-17 22:49:13'),
  (39, 20, 41887.68, '2025-04-26 21:14:40'),
  (40, 78, 41775.26, '2025-06-25 07:06:47'),
  (41, 55, 23742.31, '2025-08-26 04:42:01'),
  (42, 81, 12249.19, '2025-06-13 21:47:41'),
  (43, 23, 3145.63, '2026-03-05 17:52:52'),
  (44, 53, 36240.57, '2025-12-16 21:59:51'),
  (45, 91, 59978.61, '2025-08-08 14:15:08'),
  (46, 101, 39669.33, '2025-06-03 15:37:02'),
  (47, 117, 7090.03, '2025-07-26 07:44:53'),
  (48, 22, 42139.24, '2025-08-27 22:17:59'),
  (49, 60, 45037.8, '2025-08-25 04:40:14'),
  (50, 11, 15789.03, '2025-01-31 23:03:31'),
  (51, 4, 44572.2, '2025-03-12 22:27:23'),
  (52, 76, 26626.38, '2025-01-17 02:43:23'),
  (53, 61, 30061.78, '2026-02-08 02:11:13'),
  (54, 12, 29210.34, '2026-01-01 01:37:43'),
  (55, 110, 32796.64, '2025-05-05 21:59:33'),
  (56, 105, 2455.66, '2026-03-04 06:32:38'),
  (57, 41, 65017.19, '2025-07-16 01:03:20'),
  (58, 106, 29997.09, '2025-01-01 21:27:26'),
  (59, 67, 34576.12, '2025-10-09 00:03:21'),
  (60, 27, 25649.42, '2025-07-27 10:17:20'),
  (61, 81, 46993.69, '2025-07-05 20:49:40'),
  (62, 12, 4756.08, '2025-06-24 12:43:25'),
  (63, 83, 41770.2, '2025-04-30 14:13:47'),
  (64, 54, 42125.52, '2026-01-10 18:26:38'),
  (65, 50, 42931.29, '2025-05-29 01:14:05'),
  (66, 44, 3245.36, '2026-01-30 08:54:58'),
  (67, 108, 39233.95, '2026-04-01 21:18:43'),
  (68, 62, 40984.52, '2025-09-26 01:34:35'),
  (69, 120, 10104.36, '2025-09-07 22:21:52'),
  (70, 74, 27767.32, '2025-10-25 11:05:38'),
  (71, 6, 50610.33, '2025-09-05 03:14:11'),
  (72, 17, 33975.39, '2026-01-26 03:23:08'),
  (73, 117, 17944.4, '2025-12-21 21:45:24'),
  (74, 53, 25494.03, '2025-04-30 21:30:06'),
  (75, 44, 37360.31, '2025-11-05 17:37:34'),
  (76, 81, 49144.22, '2026-01-15 00:00:01'),
  (77, 30, 9386.61, '2025-03-12 04:47:34'),
  (78, 89, 15355.68, '2025-08-21 14:27:08'),
  (79, 102, 31543.39, '2025-02-13 14:08:17'),
  (80, 32, 19110.84, '2026-02-17 13:39:28'),
  (81, 11, 57053.03, '2025-10-25 03:08:33'),
  (82, 60, 17332.07, '2025-07-17 06:28:50'),
  (83, 116, 52173.7, '2025-08-12 09:50:34'),
  (84, 39, 59429.93, '2025-11-26 13:41:30'),
  (85, 110, 37613.16, '2026-01-11 07:39:04'),
  (86, 74, 43260.67, '2025-10-06 19:29:01'),
  (87, 73, 37046.99, '2025-02-08 02:15:03'),
  (88, 78, 16030.66, '2025-10-01 15:44:19'),
  (89, 80, 11604.73, '2025-07-14 16:01:19'),
  (90, 40, 5300.55, '2025-12-09 09:32:23'),
  (91, 94, 12620.28, '2025-04-13 01:41:37'),
  (92, 42, 8178.76, '2025-11-19 17:32:20'),
  (93, 101, 24855.68, '2026-02-23 23:17:21'),
  (94, 107, 25419.25, '2025-05-08 19:19:49'),
  (95, 96, 4756.08, '2025-09-21 02:06:35'),
  (96, 19, 55450.22, '2025-06-25 05:45:45'),
  (97, 102, 30791.7, '2025-11-03 11:24:10'),
  (98, 16, 33810.72, '2025-12-20 05:49:33'),
  (99, 106, 17828.88, '2025-10-16 12:46:14'),
  (100, 61, 51705.44, '2025-01-20 10:29:35'),
  (101, 82, 37177.58, '2025-12-27 14:34:26'),
  (102, 39, 10777.1, '2026-01-18 15:11:18'),
  (103, 114, 20635.2, '2026-02-22 12:35:42'),
  (104, 48, 40878.64, '2026-02-24 13:55:04'),
  (105, 84, 15479.98, '2025-03-18 21:53:49'),
  (106, 89, 7996.12, '2025-10-15 19:10:44'),
  (107, 31, 18389.25, '2025-10-09 16:57:28'),
  (108, 70, 18997.23, '2025-10-06 14:09:54'),
  (109, 25, 51903.12, '2025-04-05 21:13:19'),
  (110, 3, 44904.15, '2025-02-09 06:51:06'),
  (111, 27, 34927.42, '2025-02-23 06:59:09'),
  (112, 101, 32124.36, '2025-01-03 04:51:30'),
  (113, 33, 5957.93, '2025-05-16 12:51:43'),
  (114, 17, 34243.12, '2025-01-12 13:00:59'),
  (115, 3, 26721.2, '2025-05-16 08:11:04'),
  (116, 68, 45808.32, '2025-03-30 06:34:35'),
  (117, 14, 49216.15, '2025-12-18 01:59:30'),
  (118, 83, 2949.56, '2025-01-25 04:41:24'),
  (119, 55, 29020.63, '2025-03-25 20:33:15'),
  (120, 42, 10944.42, '2025-04-26 05:08:34'),
  (121, 80, 28073.84, '2026-03-31 15:59:19'),
  (122, 59, 39715.89, '2026-01-28 16:09:18'),
  (123, 93, 25063.41, '2025-06-17 00:19:13'),
  (124, 106, 33712.37, '2025-12-19 05:49:56'),
  (125, 41, 15787.55, '2025-07-17 23:29:24'),
  (126, 107, 2253.86, '2025-03-08 07:18:25'),
  (127, 96, 7701.97, '2025-10-17 10:38:27'),
  (128, 76, 31588.27, '2026-03-13 04:43:46'),
  (129, 112, 24563.38, '2025-02-09 23:01:27'),
  (130, 74, 13233.2, '2026-01-30 02:51:19'),
  (131, 29, 13304.91, '2025-03-26 01:20:12'),
  (132, 98, 55023.96, '2025-08-05 08:53:16'),
  (133, 79, 1534.62, '2026-03-09 10:22:52'),
  (134, 4, 42301.8, '2025-05-21 03:11:57'),
  (135, 45, 1302.98, '2025-01-05 17:49:12'),
  (136, 73, 6645.04, '2025-11-08 07:09:42'),
  (137, 12, 3427.72, '2026-02-17 00:54:50'),
  (138, 59, 5114.88, '2025-09-22 18:45:42'),
  (139, 93, 17230.53, '2025-09-09 22:56:07'),
  (140, 97, 28825.44, '2025-02-08 15:38:24'),
  (141, 57, 37080.08, '2025-11-18 16:56:40'),
  (142, 56, 65321.15, '2025-03-28 03:08:06'),
  (143, 86, 5125.56, '2025-08-28 13:21:15'),
  (144, 77, 12088.56, '2025-02-12 13:12:35'),
  (145, 28, 15336.61, '2025-04-17 14:14:31'),
  (146, 64, 14592.56, '2025-12-01 11:59:28'),
  (147, 69, 58532.42, '2025-06-28 17:34:28'),
  (148, 56, 30247.59, '2025-01-15 13:22:03'),
  (149, 74, 19666.56, '2025-11-30 10:26:20'),
  (150, 42, 46709.21, '2025-05-14 11:56:53');

七插入订单详情表数据

USE sales_analysis_db;

INSERT INTO order_item (id, order_id, product_id, quantity, total_price) VALUES
  (1, 1, 62, 1, 737.39),
  (2, 1, 46, 3, 27035.67),
  (3, 2, 69, 5, 32816.65),
  (4, 2, 103, 5, 3919.45),
  (5, 3, 29, 3, 3844.17),
  (6, 3, 56, 4, 9015.44),
  (7, 4, 86, 4, 37999.76),
  (8, 4, 93, 2, 17944.4),
  (9, 5, 80, 5, 42420.1),
  (10, 6, 73, 1, 1074.61),
  (11, 6, 11, 4, 107.16),
  (12, 6, 18, 4, 2835.52),
  (13, 7, 49, 3, 2570.79),
  (14, 7, 28, 4, 11852.92),
  (15, 8, 36, 4, 21933.2),
  (16, 8, 33, 1, 9028.11),
  (17, 9, 70, 1, 65.29),
  (18, 9, 45, 2, 9473.2),
  (19, 9, 84, 1, 9878.06),
  (20, 10, 32, 2, 13551.4),
  (21, 11, 20, 2, 6616.6),
  (22, 11, 17, 4, 14592.56),
  (23, 11, 86, 1, 9499.94),
  (24, 12, 90, 3, 15156.54),
  (25, 12, 99, 3, 22979.07),
  (26, 13, 40, 1, 5803.43),
  (27, 14, 74, 4, 17026.92),
  (28, 14, 51, 2, 12664.82),
  (29, 15, 90, 3, 15156.54),
  (30, 16, 101, 1, 6270.55),
  (31, 16, 45, 5, 23683.0),
  (32, 16, 55, 3, 20191.56),
  (33, 17, 44, 1, 2144.58),
  (34, 17, 109, 4, 9279.96),
  (35, 17, 106, 4, 12814.04),
  (36, 18, 82, 4, 32061.32),
  (37, 18, 91, 2, 16613.08),
  (38, 19, 67, 3, 14844.84),
  (39, 19, 79, 5, 12495.85),
  (40, 19, 100, 4, 20868.88),
  (41, 20, 76, 3, 28737.36),
  (42, 20, 42, 2, 9309.42),
  (43, 20, 107, 1, 5405.53),
  (44, 21, 97, 4, 9918.4),
  (45, 22, 4, 4, 12573.0),
  (46, 22, 109, 3, 6959.97),
  (47, 23, 46, 3, 27035.67),
  (48, 24, 90, 3, 15156.54),
  (49, 24, 72, 1, 7701.97),
  (50, 24, 67, 2, 9896.56),
  (51, 25, 53, 4, 32740.84),
  (52, 25, 72, 2, 15403.94),
  (53, 25, 89, 4, 28779.2),
  (54, 26, 102, 1, 2752.88),
  (55, 26, 12, 3, 27766.68),
  (56, 27, 32, 3, 20327.1),
  (57, 27, 85, 5, 29789.65),
  (58, 27, 48, 4, 6819.84),
  (59, 28, 55, 5, 33652.6),
  (60, 28, 43, 3, 13280.04),
  (61, 29, 40, 3, 17410.29),
  (62, 29, 30, 1, 4209.78),
  (63, 30, 16, 5, 33544.4),
  (64, 30, 98, 2, 12759.12),
  (65, 31, 62, 3, 2212.17),
  (66, 31, 93, 5, 44861.0),
  (67, 31, 98, 5, 31897.8),
  (68, 32, 107, 2, 10811.06),
  (69, 33, 23, 3, 21598.8),
  (70, 33, 2, 5, 21754.0),
  (71, 34, 7, 5, 19288.3),
  (72, 35, 112, 4, 2605.96),
  (73, 35, 14, 1, 7196.36),
  (74, 35, 74, 3, 12770.19),
  (75, 36, 44, 2, 4289.16),
  (76, 36, 7, 3, 11572.98),
  (77, 37, 106, 1, 3203.51),
  (78, 38, 74, 1, 4256.73),
  (79, 39, 39, 1, 4304.1),
  (80, 39, 32, 1, 6775.7),
  (81, 39, 72, 4, 30807.88),
  (82, 40, 49, 4, 3427.72),
  (83, 40, 117, 4, 16827.04),
  (84, 40, 39, 5, 21520.5),
  (85, 41, 80, 1, 8484.02),
  (86, 41, 79, 1, 2499.17),
  (87, 41, 98, 2, 12759.12),
  (88, 42, 85, 1, 5957.93),
  (89, 42, 21, 2, 6291.26),
  (90, 43, 21, 1, 3145.63),
  (91, 44, 77, 4, 20735.32),
  (92, 44, 38, 1, 2875.91),
  (93, 44, 30, 3, 12629.34),
  (94, 45, 111, 4, 28265.56),
  (95, 45, 10, 2, 14180.06),
  (96, 45, 119, 3, 17532.99),
  (97, 46, 15, 5, 37106.55),
  (98, 46, 29, 2, 2562.78),
  (99, 47, 10, 1, 7090.03),
  (100, 48, 96, 5, 12994.95),
  (101, 48, 118, 3, 27145.26),
  (102, 48, 57, 1, 1999.03),
  (103, 49, 52, 3, 10074.39),
  (104, 49, 65, 5, 9760.65),
  (105, 49, 64, 4, 25202.76),
  (106, 50, 95, 3, 14254.41),
  (107, 50, 78, 3, 1534.62),
  (108, 51, 87, 5, 44572.2),
  (109, 52, 106, 3, 9610.53),
  (110, 52, 74, 1, 4256.73),
  (111, 52, 98, 2, 12759.12),
  (112, 53, 57, 3, 5997.09),
  (113, 53, 24, 5, 15049.25),
  (114, 53, 56, 4, 9015.44),
  (115, 54, 53, 3, 24555.63),
  (116, 54, 42, 1, 4654.71),
  (117, 55, 53, 4, 32740.84),
  (118, 55, 37, 4, 55.8),
  (119, 56, 59, 1, 2455.66),
  (120, 57, 15, 4, 29685.24),
  (121, 57, 111, 5, 35331.95),
  (122, 58, 112, 5, 3257.45),
  (123, 58, 60, 4, 19024.32),
  (124, 58, 7, 2, 7715.32),
  (125, 59, 97, 4, 9918.4),
  (126, 59, 81, 4, 18278.16),
  (127, 59, 98, 1, 6379.56),
  (128, 60, 17, 3, 10944.42),
  (129, 60, 57, 4, 7996.12),
  (130, 60, 16, 1, 6708.88),
  (131, 61, 21, 3, 9436.89),
  (132, 61, 71, 1, 7511.36),
  (133, 61, 71, 4, 30045.44),
  (134, 62, 60, 1, 4756.08),
  (135, 63, 120, 3, 20872.65),
  (136, 63, 66, 3, 20897.55),
  (137, 64, 32, 4, 27102.8),
  (138, 64, 71, 2, 15022.72),
  (139, 65, 66, 2, 13931.7),
  (140, 65, 111, 1, 7066.39),
  (141, 65, 36, 4, 21933.2),
  (142, 66, 106, 1, 3203.51),
  (143, 66, 37, 3, 41.85),
  (144, 67, 85, 4, 23831.72),
  (145, 67, 111, 2, 14132.78),
  (146, 67, 58, 5, 1269.45),
  (147, 68, 71, 5, 37556.8),
  (148, 68, 49, 4, 3427.72),
  (149, 69, 90, 2, 10104.36),
  (150, 70, 110, 4, 27767.32),
  (151, 71, 61, 4, 33992.04),
  (152, 71, 50, 2, 10317.6),
  (153, 71, 64, 1, 6300.69),
  (154, 72, 43, 1, 4426.68),
  (155, 72, 112, 4, 2605.96),
  (156, 72, 13, 5, 26942.75),
  (157, 73, 93, 2, 17944.4),
  (158, 74, 61, 3, 25494.03),
  (159, 75, 11, 3, 80.37),
  (160, 75, 110, 5, 34709.15),
  (161, 75, 49, 3, 2570.79),
  (162, 76, 5, 5, 30326.9),
  (163, 76, 9, 2, 5108.7),
  (164, 76, 81, 3, 13708.62),
  (165, 77, 13, 1, 5388.55),
  (166, 77, 57, 2, 3998.06),
  (167, 78, 6, 3, 15355.68),
  (168, 79, 46, 3, 27035.67),
  (169, 79, 56, 2, 4507.72),
  (170, 80, 73, 2, 2149.22),
  (171, 80, 22, 2, 16961.62),
  (172, 81, 88, 2, 12259.5),
  (173, 81, 64, 5, 31503.45),
  (174, 81, 19, 2, 13290.08),
  (175, 82, 33, 1, 9028.11),
  (176, 82, 116, 4, 8303.96),
  (177, 83, 70, 2, 130.58),
  (178, 83, 10, 4, 28360.12),
  (179, 83, 45, 5, 23683.0),
  (180, 84, 33, 4, 36112.44),
  (181, 84, 109, 3, 6959.97),
  (182, 84, 26, 4, 16357.52),
  (183, 85, 31, 4, 37613.16),
  (184, 86, 38, 3, 8627.73),
  (185, 86, 3, 4, 29149.64),
  (186, 86, 36, 1, 5483.3),
  (187, 87, 96, 4, 10395.96),
  (188, 87, 107, 3, 16216.59),
  (189, 87, 100, 2, 10434.44),
  (190, 88, 82, 2, 16030.66),
  (191, 89, 97, 2, 4959.2),
  (192, 89, 81, 1, 4569.54),
  (193, 89, 116, 1, 2075.99),
  (194, 90, 75, 3, 5300.55),
  (195, 91, 117, 3, 12620.28),
  (196, 92, 26, 2, 8178.76),
  (197, 93, 68, 5, 12235.4),
  (198, 93, 117, 3, 12620.28),
  (199, 94, 117, 4, 16827.04),
  (200, 94, 104, 3, 8592.21),
  (201, 95, 60, 1, 4756.08),
  (202, 96, 93, 4, 35888.8),
  (203, 96, 109, 5, 11599.95),
  (204, 96, 47, 1, 7961.47),
  (205, 97, 34, 5, 30791.7),
  (206, 98, 87, 3, 26743.32),
  (207, 98, 75, 4, 7067.4),
  (208, 99, 87, 2, 17828.88),
  (209, 100, 114, 5, 27150.5),
  (210, 100, 42, 5, 23273.55),
  (211, 100, 29, 1, 1281.39),
  (212, 101, 39, 4, 17216.4),
  (213, 101, 15, 2, 14842.62),
  (214, 101, 6, 1, 5118.56),
  (215, 102, 13, 2, 10777.1),
  (216, 103, 50, 4, 20635.2),
  (217, 104, 76, 2, 19158.24),
  (218, 104, 114, 4, 21720.4),
  (219, 105, 79, 4, 9996.68),
  (220, 105, 36, 1, 5483.3),
  (221, 106, 57, 4, 7996.12),
  (222, 107, 88, 3, 18389.25),
  (223, 108, 51, 3, 18997.23),
  (224, 109, 12, 2, 18511.12),
  (225, 109, 83, 5, 33392.0),
  (226, 110, 32, 2, 13551.4),
  (227, 110, 101, 5, 31352.75),
  (228, 111, 27, 5, 20147.6),
  (229, 111, 28, 2, 5926.46),
  (230, 111, 43, 2, 8853.36),
  (231, 112, 110, 2, 13883.66),
  (232, 112, 17, 5, 18240.7),
  (233, 113, 85, 1, 5957.93),
  (234, 114, 102, 2, 5505.76),
  (235, 114, 76, 3, 28737.36),
  (236, 115, 7, 2, 7715.32),
  (237, 115, 95, 4, 19005.88),
  (238, 116, 9, 4, 10217.4),
  (239, 116, 58, 3, 761.67),
  (240, 116, 66, 5, 34829.25),
  (241, 117, 29, 5, 6406.95),
  (242, 117, 6, 5, 25592.8),
  (243, 117, 39, 4, 17216.4),
  (244, 118, 62, 4, 2949.56),
  (245, 119, 92, 4, 21930.6),
  (246, 119, 10, 1, 7090.03),
  (247, 120, 17, 3, 10944.42),
  (248, 121, 92, 3, 16447.95),
  (249, 121, 49, 5, 4284.65),
  (250, 121, 68, 3, 7341.24),
  (251, 122, 56, 1, 2253.86),
  (252, 122, 102, 1, 2752.88),
  (253, 122, 110, 5, 34709.15),
  (254, 123, 58, 2, 507.78),
  (255, 123, 53, 3, 24555.63),
  (256, 124, 54, 1, 7513.09),
  (257, 124, 41, 4, 26199.28),
  (258, 125, 20, 4, 13233.2),
  (259, 125, 9, 1, 2554.35),
  (260, 126, 56, 1, 2253.86),
  (261, 127, 72, 1, 7701.97),
  (262, 128, 43, 1, 4426.68),
  (263, 128, 53, 3, 24555.63),
  (264, 128, 112, 4, 2605.96),
  (265, 129, 77, 3, 15551.49),
  (266, 129, 46, 1, 9011.89),
  (267, 130, 20, 4, 13233.2),
  (268, 131, 109, 5, 11599.95),
  (269, 131, 48, 1, 1704.96),
  (270, 132, 29, 4, 5125.56),
  (271, 132, 109, 5, 11599.95),
  (272, 132, 99, 5, 38298.45),
  (273, 133, 78, 3, 1534.62),
  (274, 134, 90, 3, 15156.54),
  (275, 134, 118, 3, 27145.26),
  (276, 135, 112, 2, 1302.98),
  (277, 136, 19, 1, 6645.04),
  (278, 137, 49, 4, 3427.72),
  (279, 138, 48, 3, 5114.88),
  (280, 139, 77, 1, 5183.83),
  (281, 139, 114, 1, 5430.1),
  (282, 139, 20, 2, 6616.6),
  (283, 140, 11, 3, 80.37),
  (284, 140, 57, 4, 7996.12),
  (285, 140, 63, 5, 20748.95),
  (286, 141, 28, 5, 14816.15),
  (287, 141, 15, 3, 22263.93),
  (288, 142, 87, 5, 44572.2),
  (289, 142, 63, 5, 20748.95),
  (290, 143, 29, 4, 5125.56),
  (291, 144, 27, 3, 12088.56),
  (292, 145, 38, 3, 8627.73),
  (293, 145, 16, 1, 6708.88),
  (294, 146, 17, 4, 14592.56),
  (295, 147, 72, 3, 23105.91),
  (296, 147, 10, 4, 28360.12),
  (297, 147, 111, 1, 7066.39),
  (298, 148, 118, 1, 9048.42),
  (299, 148, 111, 3, 21199.17),
  (300, 149, 52, 4, 13432.52),
  (301, 149, 38, 1, 2875.91),
  (302, 149, 52, 1, 3358.13),
  (303, 150, 59, 3, 7366.98),
  (304, 150, 12, 4, 37022.24),
  (305, 150, 109, 1, 2319.99);

八查看所有表

SELECT
    TABLE_NAME,
    TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sales_analysis_db';

九查看表结构语句

SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'user'
  AND TABLE_SCHEMA = 'sales_analysis_db';

十查看示例数据

use sales_analysis_db;
SELECT * FROM product ORDER BY id DESC LIMIT 5;

十一总结

通过以上步骤，我们完成了电商 AI 系统的 MySQL 数据准备，包括数据库创建、核心表设计以及样例数据导入。整体数据围绕用户、商品、订单和订单明细四张表构建，为数据查询和分析提供了基础。

基于这套数据，系统可以结合 Dify 和大模型，实现自然语言查询数据、自动分析结果，从而支撑电商场景下的智能分析能力。

Mysql数据准备

本节目标：

一 数据准备说明

二 建数据库

三 建表

四 插入用户表数据

五 插入产品表数据

六 插入订单表数据

七 插入订单详情表数据

八 查看所有表

九 查看表结构语句

十 查看示例数据

十一 总结