MySQL数据库设计与管理

在掌握了MySQL的基础操作和高级特性后，我们现在来学习数据库设计的基本原则和管理技巧。良好的数据库设计是构建高性能、可维护应用的基础，而有效的管理策略则能确保数据库系统的稳定运行。

数据库设计原则（规范化）

数据库设计的目标是创建一个既能满足业务需求又能保证数据一致性和完整性的结构。规范化是数据库设计中的重要概念，它通过消除数据冗余来提高数据质量和减少存储空间。

三大范式

第一范式（1NF）

第一范式要求表中的每个字段都是不可分割的原子值，即每个字段都只包含单一值，而不是集合或数组。

-- 不符合第一范式的表
CREATE TABLE bad_example_1nf (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phone_numbers VARCHAR(200)  -- 包含多个电话号码，如"13800138001,13900139002"
);

-- 符合第一范式的表
CREATE TABLE good_example_1nf (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE phone_numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    phone_number VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES good_example_1nf(id)
);

第二范式（2NF）

第二范式要求表满足第一范式，并且所有非主键字段都完全依赖于主键，而不是依赖于主键的一部分。

-- 不符合第二范式的表（复合主键部分依赖）
CREATE TABLE bad_example_2nf (
    student_id INT,
    course_id INT,
    student_name VARCHAR(50),  -- 只依赖于student_id
    course_name VARCHAR(100),   -- 只依赖于course_id
    grade DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id)
);

-- 符合第二范式的表
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    grade DECIMAL(5,2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

第三范式（3NF）

第三范式要求表满足第二范式，并且所有非主键字段都不传递依赖于主键，即非主键字段之间不能有依赖关系。

-- 不符合第三范式的表（传递依赖）
CREATE TABLE bad_example_3nf (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    department_name VARCHAR(50),  -- 依赖于department_id，而不是直接依赖于id
    manager_name VARCHAR(50)      -- 依赖于department_id，而不是直接依赖于id
);

-- 符合第三范式的表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

反范式化

虽然规范化有助于减少数据冗余，但在某些情况下，为了提高查询性能，我们需要有意识地引入一些冗余数据，这就是反范式化。

-- 规范化的订单系统
CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    address VARCHAR(200)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 查询订单详情需要多次连接
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    c.address AS customer_address,
    o.order_date,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.id = 12345;

-- 反范式化的订单系统（为了提高查询性能）
CREATE TABLE orders_denormalized (
    id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(50),    -- 冗余字段
    customer_address VARCHAR(200), -- 冗余字段
    order_date DATE,
    product_id INT,
    product_name VARCHAR(100),     -- 冗余字段
    quantity INT,
    unit_price DECIMAL(10,2)
);

-- 查询订单详情变得更简单快速
SELECT 
    id AS order_id,
    customer_name,
    customer_address,
    order_date,
    quantity,
    unit_price
FROM orders_denormalized
WHERE id = 12345;

ER图设计

实体-关系图（ER Diagram）是数据库设计的重要工具，用于可视化地表示数据模型。

实体、属性、关系

-- 实体：学生
CREATE TABLE students (
    id INT PRIMARY KEY,
    student_id VARCHAR(20) UNIQUE,
    name VARCHAR(50),
    birth_date DATE,
    gender ENUM('男', '女')
);

-- 实体：课程
CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_code VARCHAR(20) UNIQUE,
    course_name VARCHAR(100),
    credits INT
);

-- 关系：一对多（一个学生可以选多门课程）
-- 实体：选课记录
CREATE TABLE enrollments (
    id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade DECIMAL(5,2),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 关系：一对一（一个学生有一个学生档案）
CREATE TABLE student_profiles (
    id INT PRIMARY KEY,
    student_id INT UNIQUE,
    address VARCHAR(200),
    phone VARCHAR(20),
    email VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 关系：多对多（一个学生可以有多个导师，一个导师可以指导多个学生）
CREATE TABLE student_advisors (
    student_id INT,
    advisor_id INT,
    PRIMARY KEY (student_id, advisor_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (advisor_id) REFERENCES advisors(id)
);

CREATE TABLE advisors (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50)
);

视图与存储过程

视图（View）

视图是基于SQL查询结果的虚拟表，它不存储数据，而是在查询时动态生成结果。

创建视图

-- 创建简单视图
CREATE VIEW student_grades AS
SELECT 
    s.name AS student_name,
    c.course_name,
    e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

-- 创建复杂视图
CREATE VIEW student_summary AS
SELECT 
    s.id,
    s.name,
    s.student_id,
    COUNT(e.id) AS course_count,
    AVG(e.grade) AS average_grade,
    MAX(e.grade) AS highest_grade,
    MIN(e.grade) AS lowest_grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name, s.student_id;

-- 带条件的视图
CREATE VIEW high_performers AS
SELECT 
    s.name,
    s.student_id,
    AVG(e.grade) AS average_grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id, s.name, s.student_id
HAVING AVG(e.grade) > 85;

使用视图

-- 查询视图
SELECT * FROM student_grades WHERE student_name = '张三';

-- 带条件查询视图
SELECT * FROM student_summary WHERE average_grade > 80;

-- 连接视图
SELECT 
    ss.name,
    ss.average_grade,
    hp.average_grade AS high_performer_avg
FROM student_summary ss
JOIN high_performers hp ON ss.name = hp.name;

可更新视图

某些视图是可以更新的，但需要满足特定条件：

-- 创建可更新视图
CREATE VIEW student_basic_info AS
SELECT id, name, student_id, gender
FROM students
WHERE status = '在读';

-- 更新可更新视图
UPDATE student_basic_info 
SET name = '李四' 
WHERE student_id = '2021001';

-- 插入数据到可更新视图
INSERT INTO student_basic_info (name, student_id, gender) 
VALUES ('王五', '2021005', '男');

存储过程（Stored Procedure）

存储过程是预编译的SQL代码块，存储在数据库中，可以通过调用执行。

创建存储过程

-- 简单存储过程
DELIMITER //
CREATE PROCEDURE get_student_count()
BEGIN
    SELECT COUNT(*) AS student_count FROM students;
END //
DELIMITER ;

-- 带参数的存储过程
DELIMITER //
CREATE PROCEDURE get_students_by_major(IN major_name VARCHAR(50))
BEGIN
    SELECT name, student_id, enrollment_date
    FROM students
    WHERE major = major_name;
END //
DELIMITER ;

-- 带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE get_student_stats(
    IN major_name VARCHAR(50),
    OUT student_count INT,
    OUT avg_grade DECIMAL(5,2)
)
BEGIN
    SELECT COUNT(*) INTO student_count
    FROM students
    WHERE major = major_name;
    
    SELECT AVG(grade) INTO avg_grade
    FROM students s
    JOIN enrollments e ON s.id = e.student_id
    WHERE s.major = major_name;
END //
DELIMITER ;

-- 复杂存储过程（带条件逻辑）
DELIMITER //
CREATE PROCEDURE update_student_status()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE student_id_var INT;
    DECLARE avg_grade_var DECIMAL(5,2);
    
    -- 声明游标
    DECLARE student_cursor CURSOR FOR
        SELECT s.id, AVG(e.grade)
        FROM students s
        JOIN enrollments e ON s.id = e.student_id
        GROUP BY s.id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE temp_status_updates (
        student_id INT,
        new_status VARCHAR(20)
    );
    
    OPEN student_cursor;
    
    read_loop: LOOP
        FETCH student_cursor INTO student_id_var, avg_grade_var;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 根据平均成绩更新状态
        IF avg_grade_var >= 90 THEN
            INSERT INTO temp_status_updates VALUES (student_id_var, '优秀');
        ELSEIF avg_grade_var >= 80 THEN
            INSERT INTO temp_status_updates VALUES (student_id_var, '良好');
        ELSEIF avg_grade_var >= 60 THEN
            INSERT INTO temp_status_updates VALUES (student_id_var, '及格');
        ELSE
            INSERT INTO temp_status_updates VALUES (student_id_var, '不及格');
        END IF;
    END LOOP;
    
    CLOSE student_cursor;
    
    -- 更新学生状态
    UPDATE students s
    JOIN temp_status_updates t ON s.id = t.student_id
    SET s.status = t.new_status;
    
    -- 删除临时表
    DROP TEMPORARY TABLE temp_status_updates;
END //
DELIMITER ;

调用存储过程

-- 调用简单存储过程
CALL get_student_count();

-- 调用带输入参数的存储过程
CALL get_students_by_major('计算机科学');

-- 调用带输出参数的存储过程
CALL get_student_stats('计算机科学', @count, @avg);
SELECT @count AS student_count, @avg AS average_grade;

-- 调用复杂存储过程
CALL update_student_status();

触发器与事件

触发器（Trigger）

触发器是与表相关联的特殊程序，当表上发生特定事件（INSERT、UPDATE、DELETE）时自动执行。

创建触发器

-- 创建INSERT触发器
DELIMITER //
CREATE TRIGGER before_student_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    -- 自动生成学生ID
    IF NEW.student_id IS NULL OR NEW.student_id = '' THEN
        SET NEW.student_id = CONCAT('STU', LPAD(NEW.id, 6, '0'));
    END IF;
    
    -- 设置默认入学日期
    IF NEW.enrollment_date IS NULL THEN
        SET NEW.enrollment_date = CURDATE();
    END IF;
END //
DELIMITER ;

-- 创建UPDATE触发器
DELIMITER //
CREATE TRIGGER after_grade_update
AFTER UPDATE ON enrollments
FOR EACH ROW
BEGIN
    -- 记录成绩变更历史
    INSERT INTO grade_history (
        student_id, 
        course_id, 
        old_grade, 
        new_grade, 
        change_date
    ) VALUES (
        OLD.student_id, 
        OLD.course_id, 
        OLD.grade, 
        NEW.grade, 
        NOW()
    );
END //
DELIMITER ;

-- 创建DELETE触发器
DELIMITER //
CREATE TRIGGER before_enrollment_delete
BEFORE DELETE ON enrollments
FOR EACH ROW
BEGIN
    -- 检查是否允许删除
    IF OLD.grade IS NOT NULL AND OLD.grade >= 90 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '不能删除优秀学生的成绩记录';
    END IF;
END //
DELIMITER ;

-- 创建记录审计日志的触发器
DELIMITER //
CREATE TRIGGER audit_students
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (
        table_name, 
        operation, 
        record_id, 
        old_values, 
        new_values, 
        timestamp
    ) VALUES (
        'students', 
        'INSERT', 
        NEW.id, 
        NULL, 
        CONCAT('name:', NEW.name, ',student_id:', NEW.student_id), 
        NOW()
    );
END //
DELIMITER ;

管理触发器

-- 查看触发器
SHOW TRIGGERS;

-- 查看特定表的触发器
SHOW TRIGGERS LIKE 'students';

-- 删除触发器
DROP TRIGGER IF EXISTS before_student_insert;

事件（Event）

事件是MySQL的定时任务功能，可以在指定时间自动执行SQL语句。

创建事件

-- 启用事件调度器
SET GLOBAL event_scheduler = ON;

-- 创建一次性事件
CREATE EVENT cleanup_old_logs
ON SCHEDULE AT '2023-12-31 23:59:59'
DO
  DELETE FROM logs WHERE log_date < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 创建重复事件（每天凌晨2点执行）
CREATE EVENT daily_backup
ON SCHEDULE EVERY 1 DAY
STARTS '2023-01-01 02:00:00'
DO
  INSERT INTO backup_log (backup_time, status) 
  VALUES (NOW(), 'Backup started');

-- 创建复杂事件（每月第一天执行）
DELIMITER //
CREATE EVENT monthly_report
ON SCHEDULE EVERY 1 MONTH
STARTS '2023-01-01 03:00:00'
DO
BEGIN
    -- 生成月度报告
    INSERT INTO monthly_reports (report_date, total_students, avg_grade)
    SELECT 
        LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS report_date,
        COUNT(*) AS total_students,
        AVG(grade) AS avg_grade
    FROM students s
    JOIN enrollments e ON s.id = e.student_id
    WHERE e.enrollment_date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
    AND e.enrollment_date < DATE_FORMAT(NOW(), '%Y-%m-01');
    
    -- 发送报告通知
    INSERT INTO notifications (message, created_at)
    VALUES ('月度报告已生成', NOW());
END //
DELIMITER ;

-- 创建条件事件（每小时检查一次）
CREATE EVENT check_low_stock
ON SCHEDULE EVERY 1 HOUR
DO
  INSERT INTO alerts (alert_type, message, created_at)
  SELECT 'LOW_STOCK', CONCAT('产品 ', product_name, ' 库存不足'), NOW()
  FROM products
  WHERE stock < min_stock_level;

管理事件

-- 查看所有事件
SHOW EVENTS;

-- 查看特定事件
SHOW CREATE EVENT monthly_report;

-- 启用/禁用事件
ALTER EVENT monthly_report ENABLE;
ALTER EVENT monthly_report DISABLE;

-- 修改事件
ALTER EVENT daily_backup
ON SCHEDULE EVERY 2 DAY
STARTS '2023-01-01 02:00:00';

-- 删除事件
DROP EVENT IF EXISTS cleanup_old_logs;

权限管理与安全

用户管理

创建用户

-- 创建具有密码的用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password123';

-- 创建可以从任何主机连接的用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password456';

-- 创建具有特定认证插件的用户
CREATE USER 'ldap_user'@'localhost' IDENTIFIED WITH authentication_ldap_simple;

-- 创建具有过期密码的用户
CREATE USER 'temp_user'@'localhost' IDENTIFIED BY 'temp_password' PASSWORD EXPIRE;

删除用户

-- 删除用户
DROP USER 'temp_user'@'localhost';

-- 删除多个用户
DROP USER 'user1'@'localhost', 'user2'@'%';

权限控制

授予权限

-- 授予数据库级别的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'app_user'@'localhost';

-- 授予表级别的权限
GRANT SELECT, INSERT ON school.students TO 'app_user'@'localhost';

-- 授予列级别的权限
GRANT SELECT (name, email) ON school.students TO 'app_user'@'localhost';

-- 授予管理权限
GRANT CREATE, ALTER, DROP ON school.* TO 'admin_user'@'localhost';

-- 授予所有权限
GRANT ALL PRIVILEGES ON school.* TO 'super_user'@'localhost';

-- 授予特定操作权限
GRANT SELECT, INSERT, UPDATE ON school.enrollments TO 'teacher_user'@'localhost';
GRANT SELECT ON school.students TO 'teacher_user'@'localhost';

回收权限

-- 回收特定权限
REVOKE INSERT, UPDATE ON school.students FROM 'app_user'@'localhost';

-- 回收所有权限
REVOKE ALL PRIVILEGES ON school.* FROM 'app_user'@'localhost';

-- 回收代理权限
REVOKE PROXY ON '' FROM 'proxy_user'@'localhost';

查看权限

-- 查看当前用户的权限
SHOW GRANTS;

-- 查看特定用户的权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 查看所有用户
SELECT User, Host FROM mysql.user;

安全最佳实践

密码安全

-- 设置密码复杂度要求
-- 在my.cnf中配置
[mysqld]
validate_password_policy=STRONG
validate_password_length=12
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1

-- 修改用户密码
ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'New_Strong_Password123!';

-- 设置密码过期策略
ALTER USER 'app_user'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

连接安全

-- 限制用户连接数
ALTER USER 'app_user'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 100;

-- 限制用户查询数
ALTER USER 'app_user'@'localhost' WITH MAX_QUERIES_PER_HOUR 1000;

-- 限制用户更新数
ALTER USER 'app_user'@'localhost' WITH MAX_UPDATES_PER_HOUR 500;

SSL/TLS加密

-- 要求SSL连接
ALTER USER 'app_user'@'localhost' REQUIRE SSL;

-- 要求特定的SSL证书
ALTER USER 'app_user'@'localhost' REQUIRE SUBJECT '/C=US/ST=California/L=San Francisco/O=My Company/CN=app_user';

-- 要求X509证书
ALTER USER 'app_user'@'localhost' REQUIRE X509;

实践项目

为了巩固所学知识，建议进行以下实践项目：

设计电商数据库

-- 电商数据库设计
CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecommerce;

-- 用户表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 地址表
CREATE TABLE addresses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    type ENUM('billing', 'shipping') DEFAULT 'shipping',
    street VARCHAR(200),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    is_default BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 分类表
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id INT,
    level INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

-- 产品表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id INT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    sku VARCHAR(50) UNIQUE,
    status ENUM('active', 'inactive', 'discontinued') DEFAULT 'active',
    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 product_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    image_url VARCHAR(500),
    is_primary BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- 购物车表
CREATE TABLE shopping_cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    quantity INT DEFAULT 1,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_product (user_id, product_id)
);

-- 订单表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_address_id INT,
    billing_address_id INT,
    payment_method VARCHAR(50),
    payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (shipping_address_id) REFERENCES addresses(id),
    FOREIGN KEY (billing_address_id) REFERENCES addresses(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) 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)
);

-- 支付记录表
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    payment_method VARCHAR(50),
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
    transaction_id VARCHAR(100),
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 评价表
CREATE TABLE reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    user_id INT,
    rating INT CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(200),
    content TEXT,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_product_review (user_id, product_id)
);

-- 创建视图：订单详情
CREATE VIEW order_details AS
SELECT 
    o.id AS order_id,
    o.order_number,
    u.username,
    u.email,
    o.status AS order_status,
    o.total_amount,
    o.created_at AS order_date,
    oi.product_id,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.total_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- 创建视图：产品评价统计
CREATE VIEW product_rating_stats AS
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    COUNT(r.id) AS review_count,
    AVG(r.rating) AS average_rating,
    SUM(CASE WHEN r.rating = 5 THEN 1 ELSE 0 END) AS five_star_count,
    SUM(CASE WHEN r.rating = 4 THEN 1 ELSE 0 END) AS four_star_count,
    SUM(CASE WHEN r.rating = 3 THEN 1 ELSE 0 END) AS three_star_count,
    SUM(CASE WHEN r.rating = 2 THEN 1 ELSE 0 END) AS two_star_count,
    SUM(CASE WHEN r.rating = 1 THEN 1 ELSE 0 END) AS one_star_count
FROM products p
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name;

-- 创建存储过程：计算订单总金额
DELIMITER //
CREATE PROCEDURE calculate_order_total(IN order_id INT)
BEGIN
    DECLARE total DECIMAL(10,2) DEFAULT 0;
    
    SELECT SUM(total_price) INTO total
    FROM order_items
    WHERE order_id = order_id;
    
    UPDATE orders 
    SET total_amount = COALESCE(total, 0)
    WHERE id = order_id;
END //
DELIMITER ;

-- 创建触发器：更新产品库存
DELIMITER //
CREATE TRIGGER update_product_stock_after_order
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE id = NEW.product_id;
END //
DELIMITER ;

-- 创建触发器：记录订单状态变更
DELIMITER //
CREATE TRIGGER log_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_status_log (order_id, old_status, new_status, changed_at)
        VALUES (NEW.id, OLD.status, NEW.status, NOW());
    END IF;
END //
DELIMITER ;

-- 创建订单状态日志表
CREATE TABLE order_status_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    old_status VARCHAR(50),
    new_status VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

为数据库创建视图

-- 创建销售统计视图
CREATE VIEW sales_statistics AS
SELECT 
    DATE(o.created_at) AS sale_date,
    COUNT(DISTINCT o.id) AS order_count,
    COUNT(oi.id) AS item_count,
    SUM(oi.total_price) AS daily_revenue,
    AVG(o.total_amount) AS average_order_value
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY DATE(o.created_at)
ORDER BY sale_date DESC;

-- 创建热销产品视图
CREATE VIEW best_selling_products AS
SELECT 
    p.id,
    p.name,
    p.sku,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.total_price) AS total_revenue,
    COUNT(DISTINCT o.user_id) AS unique_customers
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY p.id, p.name, p.sku
ORDER BY total_sold DESC
LIMIT 20;

-- 创建客户价值视图
CREATE VIEW customer_value AS
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    COUNT(o.id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    AVG(o.total_amount) AS average_order_value,
    MAX(o.created_at) AS last_order_date,
    DATEDIFF(NOW(), MAX(o.created_at)) AS days_since_last_order
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status IN ('shipped', 'delivered') OR o.id IS NULL
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

编写存储过程

-- 创建存储过程：生成月度销售报告
DELIMITER //
CREATE PROCEDURE generate_monthly_sales_report(IN report_month DATE)
BEGIN
    DECLARE month_start DATE;
    DECLARE month_end DATE;
    
    SET month_start = DATE_FORMAT(report_month, '%Y-%m-01');
    SET month_end = LAST_DAY(report_month);
    
    -- 创建临时表存储报告数据
    CREATE TEMPORARY TABLE temp_monthly_report (
        metric_name VARCHAR(100),
        metric_value DECIMAL(15,2)
    );
    
    -- 插入销售数据
    INSERT INTO temp_monthly_report VALUES 
    ('总订单数', (
        SELECT COUNT(*) 
        FROM orders 
        WHERE created_at BETWEEN month_start AND month_end
        AND status IN ('shipped', 'delivered')
    ));
    
    INSERT INTO temp_monthly_report VALUES 
    ('总销售额', (
        SELECT COALESCE(SUM(total_amount), 0)
        FROM orders 
        WHERE created_at BETWEEN month_start AND month_end
        AND status IN ('shipped', 'delivered')
    ));
    
    INSERT INTO temp_monthly_report VALUES 
    ('新增客户数', (
        SELECT COUNT(*)
        FROM users
        WHERE created_at BETWEEN month_start AND month_end
    ));
    
    INSERT INTO temp_monthly_report VALUES 
    ('客单价', (
        SELECT COALESCE(AVG(total_amount), 0)
        FROM orders 
        WHERE created_at BETWEEN month_start AND month_end
        AND status IN ('shipped', 'delivered')
    ));
    
    -- 返回报告数据
    SELECT * FROM temp_monthly_report;
    
    -- 清理临时表
    DROP TEMPORARY TABLE temp_monthly_report;
END //
DELIMITER ;

-- 创建存储过程：处理购物车结算
DELIMITER //
CREATE PROCEDURE checkout_cart(
    IN user_id INT,
    IN shipping_address_id INT,
    IN billing_address_id INT,
    IN payment_method VARCHAR(50),
    OUT order_id INT
)
BEGIN
    DECLARE total_amount DECIMAL(10,2) DEFAULT 0;
    DECLARE order_number VARCHAR(50);
    DECLARE item_cursor CURSOR FOR
        SELECT product_id, quantity
        FROM shopping_cart
        WHERE user_id = user_id;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = TRUE;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 生成订单号
    SET order_number = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 10000), 4, '0'));
    
    -- 创建订单
    INSERT INTO orders (
        user_id, 
        order_number, 
        shipping_address_id, 
        billing_address_id, 
        payment_method,
        total_amount
    ) VALUES (
        user_id, 
        order_number, 
        shipping_address_id, 
        billing_address_id, 
        payment_method,
        0
    );
    
    SET order_id = LAST_INSERT_ID();
    
    -- 处理购物车中的商品
    OPEN item_cursor;
    read_loop: LOOP
        FETCH item_cursor INTO @product_id, @quantity;
        IF @done THEN
            LEAVE read_loop;
        END IF;
        
        -- 检查库存
        SELECT price, stock_quantity INTO @unit_price, @stock 
        FROM products 
        WHERE id = @product_id;
        
        IF @stock < @quantity THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
        END IF;
        
        -- 计算总价
        SET @item_total = @unit_price * @quantity;
        SET total_amount = total_amount + @item_total;
        
        -- 添加订单项
        INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price)
        VALUES (order_id, @product_id, @quantity, @unit_price, @item_total);
        
        -- 更新库存
        UPDATE products 
        SET stock_quantity = stock_quantity - @quantity
        WHERE id = @product_id;
    END LOOP;
    CLOSE item_cursor;
    
    -- 更新订单总金额
    UPDATE orders 
    SET total_amount = total_amount
    WHERE id = order_id;
    
    -- 清空购物车
    DELETE FROM shopping_cart WHERE user_id = user_id;
    
    -- 记录支付信息
    INSERT INTO payments (order_id, payment_method, amount, status)
    VALUES (order_id, payment_method, total_amount, 'pending');
    
    -- 提交事务
    COMMIT;
END //
DELIMITER ;

通过以上实践项目，您将掌握MySQL数据库设计与管理的核心技能，包括规范化设计、视图和存储过程的使用、触发器和事件的应用，以及权限管理和安全策略的实施。这些技能对于构建和维护高质量的数据库系统至关重要。