MySQL数据库基础练习系列12、论坛管理系统

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    email VARCHAR(100) UNIQUE NOT NULL COMMENT '电子邮箱',
    gender ENUM('男', '女') COMMENT '性别',
    role ENUM('普通用户', '版主', '管理员') NOT NULL DEFAULT '普通用户' COMMENT '用户角色'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';
CREATE TABLE forum_categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
    name VARCHAR(100) NOT NULL UNIQUE COMMENT '分类名称',
    description TEXT COMMENT '分类描述'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛分类表';
CREATE TABLE forum_threads (
    thread_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '帖子ID',
    title VARCHAR(255) NOT NULL COMMENT '帖子标题',
    content TEXT NOT NULL COMMENT '帖子内容',
    category_id INT NOT NULL COMMENT '所属分类ID',
    creator_id INT NOT NULL COMMENT '发帖者ID',
    creation_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发帖时间',
    last_reply_time DATETIME COMMENT '最后回复时间',
    view_count INT DEFAULT 0 COMMENT '浏览次数',
    reply_count INT DEFAULT 0 COMMENT '回复次数',
    status ENUM('正常', '锁定', '删除') NOT NULL DEFAULT '正常' COMMENT '帖子状态',
    FOREIGN KEY (category_id) REFERENCES forum_categories(category_id) ON DELETE CASCADE,
    FOREIGN KEY (creator_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛帖子表';
CREATE TABLE forum_replies (
    reply_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '回复ID',
    thread_id INT NOT NULL COMMENT '所属帖子ID',
    creator_id INT NOT NULL COMMENT '回复者ID',
    content TEXT NOT NULL COMMENT '回复内容',
    reply_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '回复时间',
    FOREIGN KEY (thread_id) REFERENCES forum_threads(thread_id) ON DELETE CASCADE,
    FOREIGN KEY (creator_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛回复表';
CREATE TABLE forum_favorites (
    favorite_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '收藏ID',
    user_id INT NOT NULL COMMENT '用户ID',
    thread_id INT NOT NULL COMMENT '帖子ID',
    favorite_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '收藏时间',
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (thread_id) REFERENCES forum_threads(thread_id) ON DELETE CASCADE,
    UNIQUE KEY unique_favorite (user_id, thread_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='论坛收藏表';

INSERT INTO users (username, password, email, gender, role) VALUES
('user1', 'password1', 'user1@example.com', '男', '普通用户'),
('user2', 'password2', 'user2@example.com', '女', '普通用户'),
('user3', 'password3', 'user3@example.com', '男', '版主'),
('user4', 'password4', 'user4@example.com', '女', '版主'),
('user5', 'password5', 'user5@example.com', '男', '管理员');
INSERT INTO forum_categories (name, description) VALUES
('技术讨论', '关于技术问题的讨论'),
('生活分享', '生活琐事、趣闻分享'),
('求职招聘', '求职、招聘信息发布'),
('活动公告', '论坛活动、线下活动公告'),
('建议反馈', '对论坛的建议和意见反馈');
INSERT INTO forum_threads (title, content, category_id, creator_id) VALUES
('Python 编程入门', 'Python 是一种解释型...', 1, 1),
('如何选择合适的电脑', '购买电脑时需要考虑...', 2, 2),
('Java 开发者招聘', '寻找有经验的 Java 开发者...', 3, 3),
('论坛周年庆活动', '为了庆祝论坛成立一周年...', 4, 4),
('论坛加载速度慢', '最近发现论坛加载速度...', 5, 5);
INSERT INTO forum_replies (thread_id, creator_id, content) VALUES
(1, 2, '请问有推荐的Python教程吗？'),
(1, 3, '可以参考官方文档和在线课程。'),
(2, 4, '我觉得Macbook Pro不错。'),
(3, 5, '请发送简历到hr@example.com'),
(4, 1, '期待参加，有什么奖品吗？');
INSERT INTO forum_favorites (user_id, thread_id) VALUES
(1, 1), -- user1 收藏了帖子1
(2, 2), -- user2 收藏了帖子2
(3, 3), -- user3 收藏了帖子3
(4, 4), -- user4 收藏了帖子4
(5, 1), -- user5 也收藏了帖子1（允许重复收藏）
(5, 5); -- user5 收藏了帖子5