【MYSQL】 三大范式 表的关系 外键 ER图

三大范式

• 第一范式（每列保持原子性）

每一列属性都是不可再分的属性值，确保每一列的原子性

两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

• 第二范式（属性完全依赖于主键）

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

通俗点说就一张表只干一件事，每一列都要和主键有关

例如：创建一个信息表

CREATE TABLE message(
	student_id INT,
  student_name VARCHAR(20),
  age INT,
  course_id INT,
  score INT，
  PRIMARY KEY (student_number,course_id)
);
1
2
3
4
5
6
7
8

在这个表中 student_name, age 只依赖于student_id,score（学分） 只依赖于 score_id，所以说 student_name和score（学分）是没有关系的，这就不满足第二范式

下面将表进行拆分

CREATE TABLE message(
  id INT PRIMARY KEY
	student_id INT,
  course_id INT
);
1
2
3
4
5

CREATE TABLE student(
	student_id INT PRIMARY KEY,
  student_name VARCHAR(20),
  age INT
);
1
2
3
4
5

CREATE TABLE class(
  course_id INT PRIMARY KEY,
  score INT
);
1
2
3
4

拆分之后，message 表中的 student_id 和 course_id 完全依赖于 id 主键，而 student 和 class 表中的其他字段又完全依赖于主键。

• 第三范式（属性不依赖于其它非主属性，属性直接依赖于主键）

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

例如：

从上表可知，所有属性都完全依赖于学号，所以满足2NF。但是 学院电话 是直接依赖 学院名称 ，而不是 学号 。所以调整如下：

学生表：

学院表：

主键、外键

主键： 主键是表的一个特殊字段，可以唯一地标识表中的每条信息。

**外键：**如果一个实体的某个字段指向另一个实体的主键，就称为外键。如果表B的一个字段（外键）依赖于表A的主键。则称表A为父表，表B为子表。

原则：外键必须依赖于数据库中已经存在的父表的主键，外键可以为空

作用：建立该表与父表的关联关系

增加子表记录时，先查询是否有与之对应的父表记录，如果主表没有相关的记录，从表不能插入

使用外键，当父表有删除和更新操作时，从表有以下数据处理方式：

以删除时的影响为例

1. NO ACTION = RESTRICT（限制），表示不允许删除，只有子表中的数据删除后才能删除父表中的数据。
2. CASCADE（级联），删除父表中的数据后，将关联的子表数据也删除。
3. SET NULL（表示设置为空），父表中的数据删除后，将子表中关联的字段设置为空。

现在我们开发更多人偏向不使用外键，理由是：

1. 程序逻辑：某些程序逻辑中，程序的逻辑已经足够保证完整性，我会在存储过程或包等地方做严谨的判断
2. 性能问题：比如一个业务流水表，频繁插入数据，如果这个表身上有3外键，那么每次插入一条，就必须对这3个外键对应的3个表做相应的查找判断有无对应数据，如果这3个表也很大，那就这3个表的判断时间就很长。并且这个操作可以在代码逻辑里进行控制，通过外键再判断一次，就是降低性能。并且有些地方判不判断也无所谓的，但是用了外键，就必须花时间去判断
3. 维护麻烦

表的关系

• 一对一（1:1）

比如：一个人只能有一个身份证号码，一个身份证号码只能属于一个人

实现方式：

可以在任意一方添加唯一外键指向另一方的主键。

• 一对多（1:N）

比如：一个班级有多个学生

实现方式：

在从表(N对应的表)创建一个字段作为外键,指向主表(1对应的表)的主键

• 多对多（M:N）

例如：一个课程可以被多个学生选择，一个学生可以选择多个课程

实现方式：

三个表。一个定义第一个实体，一个定义第二个实体，第三个表示它们之间的关系

ER图

实体：矩形

属性（实体的属性）：椭圆形

关系（两个实体之间的关系）：菱形

如图：