全网最全MySQL的增删改查（进阶）

目录

1.什么是表约束？

2.为什么需要表约束？

3.数据库约束

3.1NULL约束

3.2 唯一约束unique

 3.3.默认值约束DEFAULT

3.4主键约束PRIMARY KEY

3.5外键约束 foreign key

3.6检查约束 check

3.7⾃增 auto_increment

3.7.1介绍：

 3.7.2手动指定自增值（新增表时使用）

3.7.3修改⾃增值

3.8delete 和 truncate 重置⾃增验证

 3.9 默认约束 default

4.扩展知识

 5.表设计规则

5.1数据库设计三范式

5.1.1第一范式：

5.1.2第二范式：(确保表中的每列都和主键相关)

5.1.3第三范式：(确保每列都和主键列直接相关,⽽不是间接相关)

---

1.什么是表约束？

表约束就是在创建表的时候，设计一些表的约束条件，用来保证数据的合法性和数据的正确性。例如一个账号只能绑定一个手机号，那么手机号就不能重复，不能被多个账号绑定使用，所以它应该设置唯一约束。而主键是用来标识数据的，因此它不能为null，也不能为空，所以它需要设置非空约束或（主键约束，主键约束也不能为null）。

2.为什么需要表约束？

例如法律适用来规范人们的正确行为一样，表约束也是为了规范程序员正确使用表的，但表约束是预先设置的，设置之后就对所有插入和修改立即生效，比如非空约束设置好之后，如果添加和修改为null的值就会报错。

-- 总之如果没有约束条件，就会导致数据不正确，从而导致程序或现实中的业务无法推进和执行。

常见的表约束如下表：

NOT NULL	非空约束，指示某列不能存储 NULL 值
UNIQUE	唯一约束，保证某列的每行必须有唯一的值
PRIMARY KEY	主键约束，NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录
FOREIGN KEY	外键约束，保证一个表中的数据匹配另一个表中的值的参照完整性
CHECK	检查约束，保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句
AUTO_INCREMENT	自增约束，插入可忽略此字段，此字段会自己增加存储数值。默认是从1开始，每次递增1
DEFAULT	默认值约束，规定没有给列赋值时的默认值

3.数据库约束

3.1NULL约束

创建表时，可以指定某列不为空。

语法：create table table_name[id int not noll[......]];

一个表中的非空约束是没有限制的，可以使1~n个。

3.2 唯一约束unique

语法：字段名 数据类型 unique;

唯一约束的作用：指定sn列为唯一的，不重复的。

一张表里面可以有一个或者多个唯一约束。

注意：在MySQL中NULL值比较特殊，这个NULL不是一直叫做NULL.它表示没有。

1.唯一约束字段可以插入null。

2.唯一约束字段的null可以有多个。

 3.3.默认值约束DEFAULT

指定插入数据时，name列为空，默认值为unkown.

例子：

DROP TABLE IF EXISTS student;

CREATE TABLE student (

  id INT NOT NULL ,

  sn INT UNIQUE,

  name VARCHAR ( 20 ) DEFAULT 'unkown' ,

  qq_mail VARCHAR ( 20 )

);

3.4主键约束PRIMARY KEY

主键是可以用来表示一张表中某条数据的代表凭证，例如对于人这张表来说，唯一的身份证就可以作为主键来代表这个人，“姓名”不行，因为姓名可能会重复。

联合主键：多个字段联合起来形成的主键，因此主要多个字段不重复，那么插入的数据就可以成功。

独立主键：只有一个字段作为主键，因此此主键不能出现重复。

特征：1.主键可以由多个字段或单个字段组成。

           2.主键不能为空且唯一。

           3.一个表中只能有一个主键。

语法：字段 字段类型 primary key;

注意：即使主键是字符类型，依然是不能插入空值。和 not null 约束不同，not null 字符串字段可以穿入空值，而primary key不行。 

主键约束VS唯一约束：

1.一个表中主键约束只能有一个，但唯一约束可以有多个。

2.唯一约束可以插入一个或多个null，而主键约束不允许插入null。

3.5外键约束 foreign key

用法：外键⽤于关联其他表的主键或唯⼀键.

foreign key (字段名) references 主表(列) 

检查外键约束：

 外键是可以插入null的，并且可以插入多个null，但是如果指定了数据（指定了具体的值），那么这个值一定得是有效的才行。（所谓的有效指的是这个值已经在主表中存在了）

3.6检查约束 check

检查约束是保证列中的值符合指定的条件，⽐如性别只能插⼊“男”或者“⼥”。
检查约束 check 使⽤语法：

   CHECK(<检查约束>)

 注意：检查约束在 MySQL 8.0.15 以前约束不起作⽤，MySQL 8.0.16 才可以使⽤。

3.7⾃增 auto_increment

3.7.1介绍：

⾃增 auto_increment ⽤在建表的时候，它的语法如下：

create table test_user(
id int primary key auto_increment,
username varchar(250)
);

查看自增值 

⾃增从 1 开始，每次递增 1。

当给自增列 插入null时，它的执行逻辑和不设置是一样的，也就是自增列会自动插入此值。

 注意事项：

1.⼀个表中只能有⼀个⾃增约束。

2.⾃增列的字段类型只能为整数类型（TINYINT、SMALLINT、INT、BIGINT 等）。

3.auto_increment 必须配合 key ⼀起使⽤，这个 key 可以是 primary key，foreign key，unique如果没有 key，就会报错。唯⼀列可以为⾃增列，外键也可以为⾃增列。

4.自增列可以手动指定值的，在指定指定值后，也可以添加前面没有标号的自增值，但最大自增值没变。

 

 3.7.2手动指定自增值（新增表时使用）

⾃增值可以在创建表的时候⼿动指定，如下图所示：

3.7.3修改⾃增值

语法：使⽤ alter table table_name auto_increment=n，可修改⾃增值。

注意：auto_increment 的值只能设置⽐⽬前存储的最⼤值⼤，否则设置不会⽣效。

           auto_increment不能只和not null一块使用。

 

3.8delete 和 truncate 重置⾃增验证

delete 不重置⾃增：

delete vs truncate 区别6：delete 删除不会重置自增值，但truncate会重置自增列。

 3.9 默认约束 default

默认约束是给没有给列赋值时的默认值.

create table test_user(
id int primary key auto_increment,
sex varchar(1) default '男'
);

4.扩展知识

4.1insert...select插入方法

insert...select插入时，是根据列的顺序匹配查询结果的，不关注列名。

 5.表设计规则

5.1数据库设计三范式

5.1.1第一范式：

第⼀范式是最基本的范式，如果数据库表中的所有字段值都是不可分解的原⼦值，就说明该数据库表满⾜了第⼀范式。（及表的列要保证是最小的不可分割的原子值）

第一范式的优点：减少了数据冗余，更利于数据的维护和更新。

5.1.2第二范式：(确保表中的每列都和主键相关)

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

有点：更利于维护和修改，还利于展示。

5.1.3第三范式：(确保每列都和主键列直接相关,⽽不是间接相关)

在满足第一范式和第二范式的基础上，要满足所有的字段不能出现对非主键的（间接）依赖。（及每⼀列数据都和主键直接相关，⽽不能间接相关。）

优点：实现了列和非主键列的解耦，修改了一个字段不会影响其它字段，这个就叫做解耦，这是一个优秀的设计理念。