编辑代码

-- 创建表
CREATE TABLE customers (
  customer_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20)
);

CREATE TABLE order_items (
  order_item_id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  sku_id INT,
  quantity INT,
  price DECIMAL(10, 2)
);

CREATE TABLE spus (
  spu_id INT AUTO_INCREMENT PRIMARY KEY,
  spu_name VARCHAR(100),
  category VARCHAR(50)
);

CREATE TABLE skus (
  sku_id INT AUTO_INCREMENT PRIMARY KEY,
  spu_id INT,
  sku_name VARCHAR(100),
  color VARCHAR(50),
  size VARCHAR(10)
);

CREATE TABLE inventory (
  sku_id INT PRIMARY KEY,
  stock INT
);

-- 插入示例数据
	INSERT INTO customers (name, email) VALUES
('张三', 'zhangsan@example.com'),
('李四', 'lisi@example.com'),
('王五', 'wangwu@example.com'),
('赵六', 'zhaoliu@example.com'),
('孙七', 'sunqi@example.com'),
('周八', 'zhouba@example.com'),
('吴九', 'wujiu@example.com'),
('郑十', 'zhengshi@example.com'),
('十一', 'shiyi@example.com');


INSERT INTO spus (spu_name, category) VALUES
('iPhone 12', '电子产品'),
('三星 Galaxy S21', '电子产品'),
('耐克 Air Max', '鞋类'),
('华为 Mate 40', '电子产品'),
('阿迪达斯 Ultraboost', '鞋类'),
('小米 11', '电子产品'),
('彪马 RS-X', '鞋类'),
('OPPO Reno 5', '电子产品');

INSERT INTO skus (spu_id, sku_name, color, size) VALUES
(1, 'iPhone 12 - 64GB 黑色', '黑色', '64GB'),
(1, 'iPhone 12 - 128GB 白色', '白色', '128GB'),
(2, '三星 Galaxy S21 - 红色', '红色', '128GB'),
(2, '三星 Galaxy S21 - 蓝色', '蓝色', '256GB'),
(3, '耐克 Air Max - 红色', '红色', '9'),
(3, '耐克 Air Max - 蓝色', '蓝色', '10'),
(4, '华为 Mate 40 - 黑色', '黑色', '128GB'),
(4, '华为 Mate 40 - 白色', '白色', '256GB'),
(5, '阿迪达斯 Ultraboost - 黑色', '黑色', '9'),
(5, '阿迪达斯 Ultraboost - 白色', '白色', '10'),
(6, '小米 11 - 黑色', '黑色', '128GB'),
(6, '小米 11 - 白色', '白色', '256GB'),
(7, '彪马 RS-X - 黑色', '黑色', '9'),
(7, '彪马 RS-X - 白色', '白色', '10'),
(8, 'OPPO Reno 5 - 黑色', '黑色', '128GB'),
(8, 'OPPO Reno 5 - 白色', '白色', '256GB');

INSERT INTO inventory (sku_id, stock) VALUES
(1, 100),
(2, 50),
(3, 75),
(4, 25),
(5, 30),
(6, 40),
(7, 60),
(8, 80),
(9, 50),
(10, 70),
(11, 90),
(12, 100),
(13, 45),
(14, 55),
(15, 65),
(16, 75);

-- 插入订单和订单项数据
DELIMITER ;;
CREATE PROCEDURE GenerateOrdersAndItems(IN num_orders INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE rand_customer_id INT;
  DECLARE rand_sku_id INT;
  DECLARE rand_quantity INT;
  DECLARE rand_price DECIMAL(10, 2);
  DECLARE rand_order_date DATE;

  WHILE i < num_orders DO
    SET rand_customer_id = FLOOR(1 + RAND() * 8);
    SET rand_sku_id = FLOOR(1 + RAND() * 16);
    SET rand_quantity = FLOOR(1 + RAND() * 5);
    SET rand_price = (RAND() * 1000) + 100;
    SET rand_order_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365) DAY);

    INSERT INTO orders (customer_id, order_date, status) VALUES (rand_customer_id, rand_order_date, '已完成');
    SET @last_order_id = LAST_INSERT_ID();

    INSERT INTO order_items (order_id, sku_id, quantity, price) VALUES (@last_order_id, rand_sku_id, rand_quantity, rand_price);

    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;

CALL GenerateOrdersAndItems(50);