-- MySQL 初始化脚本
-- 创建测试表和数据
-- 用户表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
full_name VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 产品表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10, 2),
stock_quantity INT DEFAULT 0,
category VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 订单表
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
total_amount DECIMAL(10, 2),
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- 订单项表
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2),
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- 插入测试数据
INSERT INTO users (username, email, full_name) VALUES
('alice_brown', 'alice@example.com', 'Alice Brown'),
('bob_jones', 'bob@example.com', 'Bob Jones'),
('carol_davis', 'carol@example.com', 'Carol Davis');
INSERT INTO products (name, description, price, stock_quantity, category) VALUES
('Smartphone', 'Latest model smartphone with advanced features', 899.99, 15, 'Electronics'),
('Headphones', 'Noise-cancelling wireless headphones', 199.99, 30, 'Electronics'),
('Tablet', '10-inch tablet for work and entertainment', 449.99, 20, 'Electronics'),
('Book: MySQL Guide', 'Comprehensive MySQL database guide', 49.99, 80, 'Books'),
('Water Bottle', 'Stainless steel water bottle', 19.99, 60, 'Accessories');
INSERT INTO orders (user_id, total_amount, status) VALUES
(1, 1099.98, 'completed'),
(2, 249.98, 'shipped'),
(3, 69.98, 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 899.99),
(1, 5, 1, 19.99),
(2, 2, 1, 199.99),
(2, 4, 1, 49.99),
(3, 4, 1, 49.99),
(3, 5, 1, 19.99);
-- 创建索引以提高查询性能
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_products_category ON products(category);
