MySQL初学之旅（4）表的设计

1.前言

哈喽大家好吖，今天继续给大家分享MySQL的学习——表的设计，这一部分没有太多语法的讲解，有许多设计思路以及规则的讲解与剖析，那么话不多说让我们开始吧。

2.正文

表的设计的核心就是三大范式，在了解核心三大范式之前，让我们先知道范式是什么：

范式（Normal Form）是一套理论规范，用于组织数据库中的数据，旨在减少数据冗余并确保数据的一致性和完整性。范式通过一系列规则指导数据库设计，以避免常见的设计问题（如重复数据、插入异常、更新异常等）。

范式的目标：

• 减少数据冗余： 避免重复存储相同信息。
• 消除插入、删除、更新异常： 确保数据的逻辑一致性。
• 提高数据完整性： 避免由于设计问题导致的错误数据。
• 便于扩展和维护： 数据库结构清晰，便于修改和扩展。

下文每一条细讲的时候会逐个说明违反哪个原则会出现那种后果

2.1第一范式

先上定义：

• 原子性： 表中的每一列都只能包含单一值，而不能包含集合、列表或其他复杂结构。
• 表结构规范化： 每一列的数据类型必须一致。

让我们放到实例中讲解，我们本文均以学生为背景来讲解，假设我们现在要为建一个学生表，那么它包含的信息就应该有：

• 姓名，id，年龄，学校等信息

当我们在建表的时候就会发现：

CREATE table student(
id BIGINT,
name varchar(50),
school 

school这个变量里没有适合它的数据类型，学校可以包括学校的名字，学校的地址，学校的电话等，即学校这个这个字段可以进行拆分，那么这就违反了建表的第一范式。遇到这种情况我们会选择额外建立一张学校表来处理。

还有一种情况不符合，即在创建表时，该字段会被填入俩个及以上数据时，这样也违反了第一范式，一般遇到这种情况，我们可以将一行数据变成俩行，分开存储。

2.2第二范式

定义：

• 前提条件： 表必须满足第一范式（1NF）。
• 要求： 所有非主键字段必须完全依赖于主键，不能对主键产生部分依赖。

区分一下什么叫完全依赖，什么叫部分依赖：

• 部分依赖： 如果一个非主键字段只依赖于主键的一部分，而不是整个主键，就称为部分依赖。
• 完全依赖： 如果一个非主键字段依赖于主键的全部字段，则称为完全依赖。

看着定义又是完全又是部分，那么到底是什么意思呢，举个例子大家就懂咯：
假设我们现在创建一个有关学生选修课的表：

CREATE table student(
id BIGINT,
name varchar(50),
subject VARCHAR(50),--学科
credit BIGINT,--学分
score BIGINT--成绩
);

我们乍看这张表没有问题，包含信息还比较完整，但当我们仔细观察，发现学分这一列只会跟随学科的变化而变化，不会随着学生的变化而变化。那么也就说明当我们在插入足够多的数据的时候，可以想象，有一列完全一样的学科对应着一列完全相同的学分，这样就造成了数据的冗余，这也是违背了第二范式即所有非主键字段必须完全依赖于主键。

所以我们在解决这个问题采用分开建表的方式，分别可以建立三张表：

• 一个为学生基础信息表。
• 另一个为课程学分表。
• 还有一个单独储存成绩的表，因为成绩既和学生有关，又和学科有关，所以放在哪个表中都不合适，所以单独建表。

我们再来专门分析一下如果违反了第二范式会导致什么后果：

• 数据冗余： 每次记录学生学分名都会重复存储。
• 更新异常： 修改一个学生的姓名或课程信息时，需要更新多条记录，如果中间更新中断，我们无法确定哪一部分数据已更新，哪一部分数据未更新，会造成数据不一致。
• 插入异常： 如果课程尚未被选，则无法记录课程信息。既如果新加一个课程，但并没有考试成绩，会导致此时部分空没有意义。
• 删除异常： 删除某学生的选课记录后，课程信息也会丢失。既某些课程的学分信息被一并删去，且直到下一次再插入数据时均没有该信息，信息会短暂丢失。

以上都是违背了第二范式导致设计不合理的后果，应该严格去遵守这个原则。

2.3第三范式

定义：

• 前提条件： 表必须满足第二范式（2NF）。
• 要求： 非主键字段必须直接依赖于主键，而不能通过其他非主键字段间接依赖于主键（即，消除传递依赖）。

我们再来明确一下什么是传递依赖：

• 如果一个非主键字段依赖于另一个非主键字段，而这个非主键字段又依赖于主键，那么就存在传递依赖。

继续举例子详细讲解，假设我们现在这样创建一个表：
学生ID学生姓名专业ID专业名称专业院长1张三101计算机王教授2李四102数学李教授

主键：

• 主键是学生ID。

问题：

• 专业名称和专业院长依赖于专业ID，而专业ID又依赖于主键学生ID。
• 这意味着专业名称和专业院长并非直接依赖主键，而是通过专业ID间接依赖于主键，存在传递依赖。

处理方法就是将其拆成俩张表：

学生表：
学生ID学生姓名专业ID1张三1012李四102
专业表：
专业ID专业名称专业院长101计算机王教授102数学李教授
这样就可以避免问题。

2.4表的设计方法

我们了解完何为三大范式后，我们就要明白建表的流程了：

1. 先从需求中得到类，类对应数据库中的实体。
2. 实体在数据库中表现为一张一张的表。
3. 类的属性对应表中多个字段。
4. 确定类与类之间的关系。

确定类与类之间的关系很重要，类之间的关系有以下几种：

1. 一对一的关系，例如一个学生对应一个学校
2. 一对多的关系，一个班级包含许多个学生
3. 多对多的关系，例如一个学生可以选修许多学科，某个学科也可以被许多学生选择。

讲完了三大范式与设计方法，接下来来一段实操应用一下：

背景：创建数张表，用于存储课程信息，学生信息，以及学生成绩信息。

代码：

create table student_message(
st_id bigint PRIMARY KEY auto_increment,
name varchar(20) not NULL
);

CREATE table subject(
su_id BIGINT PRIMARY KEY auto_increment,
name varchar(20) not null
);

CREATE table score(
score_id BIGINT PRIMARY KEY auto_increment,
student_id BIGINT,
subject_id BIGINT,
grade bigint，
foreign key (student_id) REFERENCES student_message (st_id),
foreign key (subject_id) REFERENCES subject (su_id)
);

查询这三张表的语句：

select * from score;
select * from student_message;
select * from subject;

我们尝试插入一些数据看看效果如何：

INSERT into student_message values(1,'alice');
INSERT into student_message values(2,'tom');
INSERT into student_message values(3,'jerry');

insert into subject (su_id,name) values(1,'math'),(2,'english'),(3,'chinese');

insert into score values (1,1,1,90);
insert into score values (2,2,2,80);
insert into score values (4,2,3,95);

再次查询，结果如下：

分析一下结果：

其中多对多 关系，通过中间表（

score

表）实现：

• 学生（student_message） 与 科目（subject） 之间是多对多关系。 - 一个学生可以选修多门科目。- 一门科目可以被多个学生选修。
• score 表 是关系的桥梁，记录了某个学生在某门科目上的成绩。

3.小结

今天的分享到这里就结束了，喜欢的小伙伴点点赞点点关注，你的支持就是对我最大的鼓励，大家加油！