从入门到精通【 MySQL】 数据库约束与设计

📕1. 数据库约束

数据库约束是指对数据库表中的数据所施加的规则或条件，用于确保数据的准确性和可靠性。这
些约束可以是基于数据类型、值范围、唯一性、非空等规则，以确保数据的正确性和相容性。

✏️1.1 NOT NULL 非空约束

create table 表名(
	列名1 数据类型 not null,
)

非空约束是用来保证某列的值不能为空的，类似于学生姓名，学号，身份证号这些必要的信息都可以设置为非空约束，如果这些信息为null的话是没有意义的。

当我们查看表结构时，有非空约束就会显示NO，没有非空约束就会显示YES

✏️1.2 DEFAULT 默认值约束

create table 表名(
	列名1 数据类型 default 默认值,
)

默认值约束是指当我们没有在某列填写数据时，则mysq会自动帮我们生成一个默认值填进去，而这个默认值就是我们自己设置的 default 默认值约束。

通过表结构我们可以发现，不设置默认值的时候default对应的时null。

当插入数据时，如我们指定了插入的值，则插入我们指定的值，否则自动填充默认值。如果我们想指定插入null值，也是可以的。

✏️1.3 UNIQUE 唯一约束

create table 表名(
	列名1 数据类型 unique,
)

指定了唯一约束的列，该列的值在所有记录中不能重复，比如一个人的身份证号，学生的学号等。

当我们查看表结构时，Key列显示UNI表示唯一约束

✏️1.4 PRIMARY KEY 主键约束

-- primary key 是主键关键字
-- auto_ increment是自增长关键字
create table 表名(
	列名1 数据类型 PRIMARY KEY auto_increment,
)

1. 主键约束唯一标识数据库表中的每条记录。
2. 主键必须包含唯一的值，且不能包含 NULL 值。
3. 每个表只能有一个主键，可以由单个列或多个列组成。
4. 通常为每张表都指定一个主键，主键列建议使用BIGINT类型
5. 通常把主键列设置为自动增长，让数据库维护主键值
6. 如果某条记录写入失败，新生成的主键值将会作废
7. 主键值可以不连续，手动指定一个值，下一次自增从主键的最大值开始

主键或唯一键冲突时的更新操作，插入否则更新

# 插⼊ID为100，学号为100100的学⽣记录时，报主键冲突
insert into student(id, name, sno) values (100, '赵六', '100100');
ERROR 1062 (23000): Duplicate entry '100' for key 'student.PRIMARY'

# 可以使⽤以上语法，如果插⼊时有冲突则更新当前列的值
insert into student(id, name, sno) values (100, '赵六', '100100')
 on duplicate key update  name = '赵六', sno = '100100';
 
# Query OK, 2 rows affected (0.01 sec) 
# 两⾏受影响，表⽰删除了原来的记录，⼜新写⼊了⼀条记录

替换，如果存在冲突则替换，不存在冲突则插入

# 写⼊或更新Id为101的记录
replace into student(id, name, sno) values (101, '钱七', '100101');
Query OK, 2 rows affected (0.01 sec) # 受影响两⾏

# 写⼊⼀条新数据
REPLACE into student(id, name, sno) values (102, '吴⼋', '100102');
Query OK, 1 row affected (0.01 sec) # 受影响⼀⾏

复合主键：由多个列共同组成的主键，主键是否冲突以多个列的组成进行判定

drop table student;
# 重构学⽣表
create table student (
 id bigint,
 name varchar(20),
 PRIMARY KEY (id, name) # 指定复合主键
);

# 插⼊数据
insert into student(id, name) values (1, '张三');
Query OK, 1 row affected (0.01 sec)

# 重复插⼊主键冲突，此时主键值由id和name两个列共同决定
insert into student(id, name) values (1, '张三');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'student.PRIMARY'

# 修改id值插⼊成功
insert into student(id, name) values (2, '张三');
Query OK, 1 row affected (0.00 sec)

select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 张三 |
+----+--------+
2 rows in set (0.00 sec)

✏️1.5 FOREIGN KEY 外键约束

foreign key (从表列名) references 主表名(主表列名);

1. 外键用于定义主表和从表之间的关系
2. 外键约束主定义在从表的列上，主表关联的列必须是主键或唯一约束
3. 当定义外键后，要求从表中的外键列数据必须在主表的主键或唯一列存在或为null。
4. Key列的值为MUL表示外键约束的列
5. 删除主表某条记录时，从表中不能有对该记录的引用
6. 删除主表时要先删除从表

✏️1.6 CHECK 约束

在8.0.16开始全面支持CHECK约束，之前的版本会忽略CHECK的定义

重构学生表，有以下要求，年龄不能小于16岁，性别只能是男或女

drop table if exists student;

# 加⼊CHECK约束
create table student(
 id bigint PRIMARY KEY auto_increment, # 设置⾃增主键
 name varchar(20) not null,
 age int DEFAULT 18,
 gender char(1),
 check (age >= 16),
 check (gender = '男' or gender = '⼥')
);

# 正常插⼊数据
insert into student(name, age, gender) values ('张三', 17, '男'), ('李
四', 19, '⼥');

# 插⼊年龄⼩于16岁的记录，失败
insert into student(name, age, gender) values ('张三', 15, '男');
# ERROR 3819 (HY000): Check constraint 'student_chk_1' is violated.

# 插⼊性别的值不是男或⼥的记录，失败
insert into student(name, age, gender) values ('张三', 17, '1');
# ERROR 3819 (HY000): Check constraint 'student_chk_2' is violated.

📕2. 数据库设计

关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德
范式（BCNF）、第四范式（4NF）和第五范式（5NF，又称完美范式），越高的范式数据库冗余越
小。然而，普遍认为范式越高虽然对数据关系有更好的约束性，但也可能导致数据库IO更繁忙，因此在实际应用中，数据库设计通常只需满足第三范式即可。

✏️2.1 第一范式

定义：数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，对象等非原子数据，在关系型数据库中，每一列都可以用基本数据类型表示，就天然满足第一范式。

反例：

学校是一个对象，可以继续进行拆分，所以不满足第一范式

正例：

学校信息包含在一行中，每一列都不能再进行拆分，此时已满足第一范式

✏️2.2 第二范式

定义：第二范式存在于表中定义了复合主键的情况下，在满足第一范式的基础上，不存在非关键字段对任意候选键的部分函数依赖。第二范式强调的是部分函数依赖，当一张表中的主键只有一列时，天然满足第二范式

候选键：可以唯一标识一行数据的列或列的组合，可以从候选键中选一个或多个当做表的主键

反例：

1. 这张表中使用学号+课程名定义复合主键来唯一标识一个学生某门课程的成绩
2. 学生是通过学号来确定的，与学生的姓名、年龄和性别和课程没有关系，即学生的信息只依赖学号，不依赖课程名；学分是通过课程来确定的，课程的学分与学生没有关系，即学分只依赖课程名，不依赖学生
3. 对于使用复合主键的表，如果一行数据中的有些列只与复合主键中的一个或其中几个列有关系，那么就说他存在部分函数依赖，也就不满足第二范式。（满足第二范式意味着一行中所有的列都必须和复合主键有关系）

正例：

不满足第二范式时可能出现的问题：

1. 数据冗余
2. 更新异常
3. 插入异常
4. 删除异常

✏️2.3 第三范式

定义：在满足第二范式的基础上，不存在非关键字段，对任一候选键的传递依赖。

反例：

正例：

✏️2.4 设计过程

1. 从现实业务中抽象得到概念类
2. 根据E-R图完成SQL语句的编号并创建数据库
3. 确定实体与实体之间的关系，并画出E-R画

✏️2.5 实体-关系图

实体-关系图（Entity-Relationship Diagram）简称E-R图，也称作实体联系模型、实体关系模型，是
一种用于描述数据模型的概念图，主要用于数据库设计阶段。

E-R图包含了以下三种基本成分：

• 实体：即数据对象，用矩形框表示，比如用户、学生、班级等。
• 属性：实体的特性，用椭圆形或圆角矩形表示，如学生的姓名、年龄等。
• 关系：实体之间的联系，用菱形框表示，并标明关系的类型，并用直线将相关实体与关系连接起
来。

关系的类型：

1. 一对一关系（1 ：1）
   

2. 一对多关系（1 ：N）
   

3. 多对多关系（N : N）