准备工作

-- 在MySQL中创建城市表
CREATE TABLE cities (
    city_id INT NOT NULL AUTO_INCREMENT,
    city_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO cities (city_name) VALUES
('北京市'),
('上海市'),
('广州市'),
('深圳市'),
('成都市'),
('武汉市'),
('南京市'),
('杭州市'),
('重庆市'),
('西安市'),
('苏州市'),
('天津市'),
('郑州市'),
('长沙市'),
('青岛市'),
('沈阳市');

-- 创建商品品类表
CREATE TABLE categories (
    category_id INT NOT NULL AUTO_INCREMENT,
    category_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--插入数据
INSERT INTO categories (category_name) VALUES
('电子产品'),
('家用电器'),
('服装鞋帽'),
('食品饮料'),
('图书音像'),
('运动户外'),
('家居建材'),
('母婴用品'),
('汽车用品');

-- 创建商品表
CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    category_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (product_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO products (category_id, product_name) VALUES
(1, '智能手机'),
(1, '笔记本电脑'),
(1, '平板电脑'),
(2, '空调'),
(2, '洗衣机'),
(3, '男士外套'),
(3, '女士裙子'),
(4, '碳酸饮料'),
(4, '矿泉水'),
(5, '现代小说'),
(5, '历史书籍'),
(6, '跑步鞋'),
(6, '瑜伽垫'),
(7, '实木家具'),
(7, '床上用品'),
(8, '婴儿奶粉'),
(8, '儿童玩具');

-- 创建订单表
CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    order_time DATETIME NOT NULL,
    shipping_city_id INT NOT NULL,
    shipping_address TEXT NOT NULL,
    PRIMARY KEY (order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id),
    FOREIGN KEY (shipping_city_id) REFERENCES cities(city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO orders (product_id, quantity, unit_price, amount, order_time, shipping_city_id, shipping_address) VALUES
(1, 1, 4999.00, 4999.00, '2024-04-01 10:00:00', 1, '北京市海淀区某小区'),
(2, 1, 6999.00, 6999.00, '2024-04-02 11:00:00', 2, '上海市浦东新区某小区'),
(3, 2, 3999.00, 7998.00, '2024-04-03 12:00:00', 3, '广州市天河区某小区'),
(4, 1, 5999.00, 5999.00, '2024-04-04 13:00:00', 4, '深圳市南山区某小区'),
(5, 1, 999.00, 999.00, '2024-04-05 14:00:00', 5, '成都市武侯区某小区'),
(6, 1, 699.00, 699.00, '2024-04-06 15:00:00', 6, '武汉市江汉区某小区'),
(7, 1, 2999.00, 2999.00, '2024-04-07 16:00:00', 7, '南京市鼓楼区某小区'),
(8, 1, 3999.00, 3999.00, '2024-04-08 17:00:00', 8, '杭州市西湖区某小区'),
(9, 1, 4999.00, 4999.00, '2024-04-09 18:00:00', 9, '重庆市渝中区某小区'),
(10, 1, 1999.00, 1999.00, '2024-04-10 19:00:00', 10, '西安市碑林区某小区');