init.sql

-- MySQL优化系统初始化脚本

-- 创建示例数据库
CREATE DATABASE IF NOT EXISTS mysql_optimizer;
USE mysql_optimizer;

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_email (email),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建订单表
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_number VARCHAR(50) NOT NULL UNIQUE,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    shipped_date TIMESTAMP NULL,
    delivered_date TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_order_date (order_date),
    INDEX idx_total_amount (total_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建产品表
CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    category VARCHAR(50),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_status (status),
    INDEX idx_price (price),
    INDEX idx_stock (stock)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建订单项表
CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 创建日志表
CREATE TABLE IF NOT EXISTS logs (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(50) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入示例数据
INSERT INTO users (username, email, password_hash, status) VALUES
('张三', 'zhangsan@example.com', '$2b$12$LJ3m4ys3Lz0YBGQxKvGqeO8V8Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y', 'active'),
('李四', 'lisi@example.com', '$2b$12$LJ3m4ys3Lz0YBGQxKvGqeO8V8Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y', 'active'),
('王五', 'wangwu@example.com', '$2b$12$LJ3m4ys3Lz0YBGQxKvGqeO8V8Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y', 'inactive'),
('赵六', 'zhaoliu@example.com', '$2b$12$LJ3m4ys3Lz0YBGQxKvGqeO8V8Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y', 'active'),
('钱七', 'qianqi@example.com', '$2b$12$LJ3m4ys3Lz0YBGQxKvGqeO8V8Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y5Y', 'active');

INSERT INTO products (name, description, price, stock, category) VALUES
('iPhone 15', '最新款iPhone', 7999.00, 100, '手机'),
('MacBook Pro', '专业级笔记本电脑', 14999.00, 50, '电脑'),
('AirPods Pro', '无线降噪耳机', 1899.00, 200, '配件'),
('iPad Air', '轻薄平板电脑', 4799.00, 80, '平板'),
('Apple Watch', '智能手表', 2999.00, 120, '穿戴');

INSERT INTO orders (user_id, order_number, total_amount, status, order_date) VALUES
(1, 'ORD-2024-001', 9898.00, 'delivered', '2024-01-15 10:30:00'),
(2, 'ORD-2024-002', 14999.00, 'shipped', '2024-01-16 14:20:00'),
(3, 'ORD-2024-003', 1899.00, 'processing', '2024-01-17 09:15:00'),
(4, 'ORD-2024-004', 7999.00, 'pending', '2024-01-18 16:45:00'),
(5, 'ORD-2024-005', 2999.00, 'cancelled', '2024-01-19 11:30:00');

INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price) VALUES
(1, 1, 1, 7999.00, 7999.00),
(1, 3, 1, 1899.00, 1899.00),
(2, 2, 1, 14999.00, 14999.00),
(3, 3, 1, 1899.00, 1899.00),
(4, 1, 1, 7999.00, 7999.00),
(5, 5, 1, 2999.00, 2999.00);

INSERT INTO logs (user_id, action, details, ip_address, created_at) VALUES
(1, 'login', '用户登录成功', '192.168.1.100', '2024-01-15 10:00:00'),
(1, 'purchase', '购买产品: iPhone 15', '192.168.1.100', '2024-01-15 10:30:00'),
(2, 'login', '用户登录成功', '192.168.1.101', '2024-01-16 14:00:00'),
(2, 'purchase', '购买产品: MacBook Pro', '192.168.1.101', '2024-01-16 14:20:00'),
(3, 'login', '用户登录成功', '192.168.1.102', '2024-01-17 09:00:00');

-- 创建视图：用户订单统计
CREATE OR REPLACE VIEW user_order_stats AS
SELECT 
    u.id as user_id,
    u.username,
    u.email,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent,
    AVG(o.total_amount) as avg_order_amount,
    MAX(o.order_date) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 创建视图：产品销售统计
CREATE OR REPLACE VIEW product_sales_stats AS
SELECT 
    p.id as product_id,
    p.name as product_name,
    p.category,
    p.price,
    COUNT(oi.id) as times_sold,
    SUM(oi.quantity) as total_quantity_sold,
    SUM(oi.total_price) as total_revenue
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name, p.category, p.price;

-- 显示创建成功
SELECT '数据库初始化完成！' as message;