MySQL数据库基础练习系列49、邮件群发系统

-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    name VARCHAR(100) NOT NULL COMMENT '姓名',
    email VARCHAR(255) NOT NULL UNIQUE COMMENT '邮箱地址',
    status ENUM('活跃', '不活跃') DEFAULT '活跃' COMMENT '用户状态'
);


-- 邮件模板表
CREATE TABLE Email_Templates (
    template_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '模板ID',
    template_name VARCHAR(100) NOT NULL COMMENT '模板名称',
    subject VARCHAR(255) NOT NULL COMMENT '邮件主题',
    content TEXT NOT NULL COMMENT '邮件内容',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
);


-- 用户订阅表
CREATE TABLE User_Subscriptions (
    subscription_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '订阅ID',
    user_id INT NOT NULL COMMENT '用户ID',
    template_id INT NOT NULL COMMENT '模板ID',
    subscribe_status ENUM('已订阅', '未订阅') DEFAULT '未订阅' COMMENT '订阅状态',
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (template_id) REFERENCES Email_Templates(template_id)
);


-- 发送计划表
CREATE TABLE Send_Schedules (
    schedule_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '发送计划ID',
    template_id INT NOT NULL COMMENT '模板ID',
    send_time DATETIME NOT NULL COMMENT '发送时间',
    status ENUM('待处理', '处理中', '已完成', '失败') DEFAULT '待处理' COMMENT '发送状态',
    FOREIGN KEY (template_id) REFERENCES Email_Templates(template_id)
);


-- 邮件发送记录表
CREATE TABLE Email_Logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    schedule_id INT NOT NULL COMMENT '发送计划ID',
    user_id INT NOT NULL COMMENT '用户ID',
    send_time DATETIME NOT NULL COMMENT '发送时间',
    status ENUM('已发送', '发送失败') NOT NULL COMMENT '发送状态',
    FOREIGN KEY (schedule_id) REFERENCES Send_Schedules(schedule_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);


-- 邮件服务器配置表（可选）
CREATE TABLE Email_Servers (
    server_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '服务器ID',
    server_name VARCHAR(100) NOT NULL COMMENT '服务器名称',
    host VARCHAR(255) NOT NULL COMMENT '服务器地址',
    port INT NOT NULL COMMENT '服务器端口',
    username VARCHAR(100) NOT NULL COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    encryption ENUM('SSL', 'TLS', '无') NOT NULL COMMENT '加密方式'
);

-- 用户表
INSERT INTO Users (username, password, name, email, status) VALUES
('孙悟空', '123', '孙悟空', 'sunwukong@example.com', '活跃'),
('猪八戒', '123', '猪八戒', 'zhubajie@example.com', '活跃'),
('唐僧', '123', '唐僧', 'tangseng@example.com', '活跃');


-- 邮件模板表
INSERT INTO Email_Templates (template_name, subject, content) VALUES
('欢迎邮件', '欢迎加入我们的社区', '感谢您加入我们的社区，期待您的参与！'),
('活动通知', '社区活动即将开始', '我们的社区活动即将开始，敬请关注并参与！'),
('订单确认', '您的订单已确认', '您的订单已确认，我们会尽快为您发货。');


-- 发送计划表

-- 注意：send_time 需要根据实际情况进行设置，这里使用假设的时间点
INSERT INTO Send_Schedules (template_id, send_time, status) VALUES
(1, '2023-04-01 10:00:00', '待处理'),
(2, '2023-04-02 15:00:00', '待处理'),
(3, '2023-04-03 09:00:00', '待处理');


-- 邮件发送记录表

-- 注意：这里的schedule_id、user_id、send_time 需要根据实际计划和发送情况填写
INSERT INTO Email_Logs (schedule_id, user_id, send_time, status) VALUES
(1, 1, '2023-04-01 10:01:00', '已发送'),
(2, 2, '2023-04-02 15:05:00', '已发送'),
(3, 3, '2023-04-03 09:02:00', '发送失败');


-- 用户订阅表

-- 假设用户孙悟空订阅了模板1和模板2，猪八戒订阅了模板2
INSERT INTO User_Subscriptions (user_id, template_id, subscribe_status) VALUES
(1, 1, '已订阅'),
(1, 2, '已订阅'),
(2, 2, '已订阅');


-- 邮件服务器配置表（可选）
INSERT INTO Email_Servers (server_name, host, port, username, password, encryption) VALUES
('SMTP服务器1', 'smtp.example.com', 587, 'user1', 'password1', 'TLS');