示例：

-- 创建数据库
CREATE DATABASE ecommerce_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 使用数据库
USE ecommerce_db;

-- 创建用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    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) NOT NULL,
    stock INT NOT NULL,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 创建订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
            

4.2 索引

-- 为用户表创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE UNIQUE INDEX uniq_users_email ON users(email);

-- 为产品表创建索引
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_price ON products(price);

-- 为订单表创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
            

-- 创建用户订单视图
CREATE VIEW v_user_orders AS
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    o.id AS order_id,
    o.total_amount,
    o.status,
    o.created_at AS order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 创建产品库存视图
CREATE VIEW v_product_stock AS
SELECT 
    p.id,
    p.name,
    p.price,
    p.stock,
    c.name AS category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE p.stock > 0;
            

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE sp_get_user_by_id(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

-- 创建函数
DELIMITER //
CREATE FUNCTION fn_calculate_discount(price DECIMAL(10,2), discount_percent INT) RETURNS DECIMAL(10,2)
BEGIN
    RETURN price * (1 - discount_percent / 100);
END //
DELIMITER ;
            

-- 创建触发器
DELIMITER //
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER after_update_products
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.stock != NEW.stock THEN
        INSERT INTO product_stock_history (product_id, old_stock, new_stock, change_time)
        VALUES (OLD.id, OLD.stock, NEW.stock, NOW());
    END IF;
END //
DELIMITER ;
            

CREATE TABLE `order` (
    id INT PRIMARY KEY,
    `date` DATE
);