MySQL数据库基础练习系列15、电子邮件管理系统

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    full_name VARCHAR(100) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE emails (
    email_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    alias_email VARCHAR(100) NOT NULL UNIQUE,
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
CREATE TABLE contacts (
    contact_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    group_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
CREATE TABLE emails_inbox (
    email_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    from_email VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
CREATE TABLE emails_sent (
    email_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    to_email VARCHAR(100) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

INSERT INTO users (username, password, email, full_name, gender) 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 emails (user_id, alias_email, is_default) VALUES
(1, 'alias1@example.com', TRUE),
(2, 'alias2@example.com', TRUE),
(3, 'alias3@example.com', TRUE),
(4, 'alias4@example.com', TRUE),
(5, 'alias5@example.com', TRUE);
INSERT INTO contacts (user_id, name, email, group_name) VALUES
(1, '联系人A', 'contactA@example.com', '朋友'),
(1, '联系人B', 'contactB@example.com', '同事'),
(2, '联系人C', 'contactC@example.com', '家人'),
(3, '联系人D', 'contactD@example.com', '同学'),
(4, '联系人E', 'contactE@example.com', '客户');
INSERT INTO emails_inbox (user_id, from_email, subject, content) VALUES
(1, 'sender1@example.com', '问候邮件', '这是一封问候邮件。'),
(2, 'sender2@example.com', '会议通知', '请参加下周的会议。'),
(3, 'sender3@example.com', '订单确认', '您的订单已经确认。'),
(4, 'sender4@example.com', '活动邀请', '邀请您参加我们的活动。'),
(5, 'sender5@example.com', '账单提醒', '您的账单已经生成，请尽快支付。');
INSERT INTO emails_sent (user_id, to_email, subject, content) VALUES
(1, 'receiver1@example.com', '询问信息', '请问有相关的资料吗？'),
(2, 'receiver2@example.com', '感谢邮件', '感谢您的帮助。'),
(3, 'receiver3@example.com', '合作提议', '我们是否可以合作一个项目？'),
(4, 'receiver4@example.com', '产品推荐', '我们的新产品非常适合您。'),
(5, 'receiver5@example.com', '反馈意见', '我对您的服务有一些建议。');