MySQL数据库基础练习系列目标
很多学生或者说是初学者在学习完成数据库的基础增删改查后就自认为在数据库这里就很熟悉了，但是不接触项目根本部知道需求，我这里准备了50个项目的基本需求来让大家来熟练各类项目的列信息，让大家更好的深入项目进行实战式的练习，可以让大家在后面面试的时候有更多更丰富的资历让大家可以与面试官侃侃而谈。
数据库环境
MySQL版本：5.7.31-log
数据库字符集，所有数据库通用字符集与排序规则，支持中文数据。
字符集：utf8
排序规则：utf8_general_ci
使用工具：Navicat Premium 15
项目名称与项目简介
电子邮件管理系统是一个用于集中管理用户邮件的应用。主要功能包括：
- 用户管理：允许管理员创建、编辑、删除用户账户，并设置用户角色和权限。
- 邮箱设置：为用户设置默认邮箱地址，允许用户自定义多个别名。
- 邮件管理：用户可以发送、接收、回复、转发邮件，支持邮件的归档、删除和搜索功能。
- 联系人管理：用户可以添加、编辑、删除联系人，支持联系人分组和搜索。
- 邮件过滤规则：用户可以设置邮件过滤规则，如自动归类、标记重要邮件等。
数据库DDL（注意创建顺序）
为了直接运行DDL语句并创建表，我们需要确保在创建含有外键约束的表之前，相关的被引用表（即外键指向的表）已经存在。所以我们在创建表的时候一定要按照一定的顺序来创建，否则就会出现没有外键关系导致的创建异常。
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 );
插入数据DML（注意插入数据顺序）
插入数据的时候也要注意主外键关系，如果没有外检的情况下是没有办法插入从表数据的。
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', '反馈意见', '我对您的服务有一些建议。');
遵循的数据库三范式
数据库建表的三范式（3NF，Third Normal Form）是关系型数据库设计的基本原则，用于确保数据库结构的逻辑性和减少数据冗余。这三个范式是逐步细化的，每一个范式都是在前一个范式的基础上建立的。下面我将详细解释这三个范式：
第一范式（1NF, First Normal Form）
定义：
- 列不可分割，即数据库表的每一列都是不可分割的原子数据项。
- 每一列都是不可再分的最小数据单元（也称为最小的原子单元）。
解释：
- 在第一范式中，主要关注的是列的原子性。也就是说，表中的每一列都应该只包含一个值，而不能包含集合、数组或其他复合数据类型。
- 例如，如果有一个“地址”列，它包含了街道、城市、省份和国家等信息，那么这就违反了第一范式。应该将这个“地址”列拆分成多个独立的列，如“街道”、“城市”、“省份”和“国家”。
第二范式（2NF, Second Normal Form）
定义：
- 满足1NF。
- 非主键列必须完全依赖于主键，而不能只依赖于主键的一部分（针对复合主键而言）。
解释：
- 第二范式建立在第一范式的基础上，主要关注于主键与非主键列之间的依赖关系。
- 在第二范式中，一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
- 如果表中的某一列只与复合主键的一部分有关，那么它就不应该存在于这个表中，而应该被分离出去形成另外一张新表。
第三范式（3NF, Third Normal Form）
定义：
- 满足2NF。
- 非主键列必须直接依赖于主键，不能存在传递依赖。即非主键列必须直接依赖于整个主键，而不能依赖于主键的一部分。
解释：
- 第三范式是在第二范式的基础上进一步细化的。它主要关注于消除传递依赖，即非主键列不应该依赖于主键的某一部分，而应该直接依赖于整个主键。
- 如果存在传递依赖，那么应该考虑将这个非主键列分离出去，形成新的表，并通过主键或外键与原表进行关联。
