编辑代码

-- 创建数据库
DROP DATABASE IF EXISTS test_shop;
CREATE DATABASE test_shop;
USE test_shop;

-- 用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    full_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- 产品表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- 订单详情表
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 插入测试数据 - 用户
INSERT INTO users (username, password, email, full_name, is_active) VALUES
('johndoe', '$2y$10$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'john@example.com', 'John Doe', TRUE),
('janedoe', '$2y$10$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'jane@example.com', 'Jane Smith', TRUE),
('bobsmith', '$2y$10$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'bob@example.com', 'Bob Johnson', FALSE),
('alicegreen', '$2y$10$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'alice@example.com', 'Alice Green', TRUE);

-- 插入测试数据 - 产品
INSERT INTO products (name, description, price, stock_quantity, category) VALUES
('智能手机', '高性能智能手机，6.5英寸屏幕，128GB存储', 599.99, 100, '电子产品'),
('无线耳机', '降噪无线蓝牙耳机，30小时续航', 199.99, 50, '电子产品'),
('笔记本电脑', '轻薄笔记本电脑，16GB内存，512GB SSD', 1299.99, 30, '电子产品'),
('咖啡机', '全自动咖啡机，可制作多种咖啡', 249.99, 20, '家用电器'),
('运动鞋', '轻便透气运动鞋，适合跑步', 89.99, 200, '服装鞋帽'),
('智能手表', '健康监测智能手表，7天续航', 179.99, 80, '电子产品'),
('背包', '防水旅行背包，30升容量', 49.99, 150, '箱包'),
('水壶', '不锈钢保温水壶，500ml', 19.99, 300, '日用品');

-- 插入测试数据 - 订单
INSERT INTO orders (user_id, order_date, total_amount, status) VALUES
(1, '2023-01-15 10:30:00', 799.98, 'delivered'),
(2, '2023-01-16 14:45:00', 1299.99, 'shipped'),
(1, '2023-01-18 09:15:00', 269.98, 'processing'),
(3, '2023-01-20 16:20:00', 89.99, 'pending'),
(4, '2023-01-21 11:10:00', 428.97, 'delivered');

-- 插入测试数据 - 订单详情
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 599.99),
(1, 2, 1, 199.99),
(2, 3, 1, 1299.99),
(3, 4, 1, 249.99),
(3, 8, 1, 19.99),
(4, 5, 1, 89.99),
(5, 6, 2, 179.99),
(5, 7, 1, 49.99);

select * from products where price like '%99%';
select * from products where category='电子产品';