【MySQL 保姆级教学】表的约束--详细（6）

1. 什么是约束？

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确。比如有一个字段是email，要求是唯一的。

表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key。

为什么要有约束？
假如，你创建一个列，其他人在在该列中插入不同的数据，包括字符串、整型、日期类型，当你想处理这些数据时；会有很大的难度。通过约束，插入到数据库表中的数据都是合法的、正确的、可预期的。

2. 空属性 null or not null

2.1 说明

空属性有两个：null（默认）和 not null（不为空）
数据库默认字段基本上都是null，但实际开发中要尽可能保证不能为空，因为空数据没办法参与运算。

示例：
命令：select 1+null;

为什么1+null=null？
答：null相当于一个未知值，1+未知值还还是未知值。

2.2 示例

创建一个学生信息表，把学生的信息存储表中。

1. 使用 null默认值
   命令：

   create table t1 (id int, name varchar(20));
   insert into t1 values(1,‘黎明’);
   insert into t1 (id) values(2);
   insert into t1 (name) values(‘刘德华’);
   insert into t1 values(null, null);
   select * from t1;

   
   查询一个表，该表中有大量的空值，那么该表有什么意义呢？

   所以，我们要尽量阻止空值的发生。

2. 设置 not null
   命令：create table t2 (id int not null, name varchar(20) not null);
   

   插入缺少姓名的数据
   命令：insert into t1 (id) values(1);
   
   插入缺少id的数据
   命令：insert into t2 (name) values('李明');
   
   插入完整的数据
   命令：insert into t2 values(1,'李明');
   

3. 默认值 default

3.1 说明

DEFAULT 约束用于为表中的列指定默认值。当插入新记录时，如果没有为该列提供值，数据库会自动使用默认值。DEFAULT 约束可以帮助确保数据的完整性和一致性，减少数据输入错误，并简化数据插入操作。

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值

特殊情况：

• not null 插入时必须插入非空值，插入null 抛异常、不插入也抛异常。约束用户不能插入null，不能不插入。

• not NUll default '默认值'：插入时不能插入null，不插入时为default。约束用户不能插入null，可以不插入。

注意：一般not null 不需要和 default一起使用，因为default会默认补充，不会有空值。

语法：

CREATE TABLE 表名 (
列1 数据类型 [CONSTRAINT 约束名] DEFAULT 默认值,
列2 数据类型 [CONSTRAINT 约束名] DEFAULT 默认值,
…
);

1. 上面2.2中,当不插入数据时，null 值是从哪里来的呢？
   答：其实就是default起的作用

   证明：
   查看创建表
   命令：show create table t1;
   
   在创建表的时候没有设置default，但是这些default是哪里来的呢？
   答：我们执行mysql命令后，mysqld会介接收到这些命令，把这些命令进行优化、增添。

2. 当我们设置 not null 的后，字段中还会有default吗？
   答：没有
   

3.2 示例

创建一个学生表，字段有 id，name， gender

命令：create table t3 (id int , name varchar(20), gender varchar(1) default '男');
插入数据：

insert into t4 values(1,‘李明’,‘男’);
insert into t4 (id, name) values(2,‘李华’);
insert into t4 values(3, ‘雪见’, ‘女’);

再次查询，可以发现，李华的性别是自动补齐的，这是因为default '男'把默认的default null覆盖了。

4. 列描述 comment

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

语法：

CREATE TABLE 表名 (
列1 数据类型 [CONSTRAINT 约束名] COMMENT ‘注解’,
列2 数据类型 [CONSTRAINT 约束名] COMMENT ‘注解’,
…
);

注：使用查看表结构desc查看不到comment注解,需要使用show create table table_name;、

证明：
先创创建一个表：create table t2 (id int comment '学号', name varcahr(20) comment '姓名');

用desc查询desc t5;

用show create talbe t5;查询

5. 自动填充前导零 zerofill

5.1 int(n)是什么？

当我们设置 int 的时候并没有带()，为什么查看表结构的时候会出现int(11)呢？

int整型不是4个字节吗？这里显示的11是什么呢？
答：我们输入命令后，mysql的进行接收，mysqld接收到后进行优化，int(11)是mysqld添加的。11代表一种格式化输出，不是代表int类型的大小，可以使用zerofill进行修改。

用命令show create table t1 可以查看优化后的SQL语句

MySQL中，INT(n) 中的 n 并不影响 INT 类型的取值范围，而是用于指定显示宽度。显示宽度是指在某些情况下（如使用 ZEROFILL 属性时），MySQL 会用零填充数字，以达到指定的宽度。

5.2 定义

ZEROFILL 是MySQL中的一个属性，用于在数值类型（如 INT、BIGINT 等）的列上自动填充前导零，以达到指定的显示宽度。这个属性主要在显示数据时有用，尤其是在需要格式化输出的场景中。

语法：

CREATE TABLE 表名 (
列名 数据类型(n) ZEROFILL
);

5.3 示例

1. 创建一个表，设置有符号结合无符号类型，查看表结构
   命令：create table t1 (id int, age int unsigned);
   
   为int 比 int unsigned多出1为呢？

   因为 有符号的int中有一位是符号位，其实有符号的宽度也是10

2. 创建一个表，使用默认zerofill。插入2行数据再显示出来。
   命令：create table t2 (id int zerofill, age tinyint zerofill unsigned);
   

3. 创建表，自定义zerofill。插入2行数数据后显示出来。
   命令：create table t3 (id int(4) zerofill, age tinyint(2))
   
   插入数据后查询
   
   这次可以看到id的值由原来的111变成000111，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里
   设置的是6），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是111。为什
   么是这样呢？我们可以用hex函数来证明。

   命令：select id, convert(id, decimal) from t3;
   
   可以看出数据库内部存储的还是111,000111只是设置了zerofill属性后的一种格式化输出而已。

6. 主键和复合主键

6.1 主键的定义

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

语法：

CREATE TABLE 表名 (
列1 数据类型 [CONSTRAINT 约束名] PRIMARY KEY,
列2 数据类型 [CONSTRAINT 约束名] ,
…
);

6.2 主键的示例

1. 创建表时添加主键
   命令：create table t1 (id int primary key, name varchar(10));
   

2. 插入数据，主键约束：非空、唯一
   正确示例：
   id 和 name 都不为空，且id唯一
   命令：insert into t1 values(1, '李明');
   
   id 和 name 都不为空，且id唯一
   命令：insert into t1 values(2, '李华');
   
   id 不为空且id唯一
   命令：insert into t1(id) values(3);
   

   错误示例：
   插入的id为空
   命令：insert into t1(id) values(null);

   不插入id，插入name
   命令：insert into t1(name) values('李明');
   

   插入的id为重复值
   命令：insert into t1 values(1, '小红');
   

3. 创建的表没有主键，追加主键 alter table 表名 add primary key(字段列表);
   创建表命令：create table t2(id int, name varchar(10));
   

   追加主键：alter table t2 add primary key(id);
   
   查询表结构：desc t2;
   

4. 删除主键，alter table 表名 drop primary key;
   查询表结构：desc t2;
   
   删除表结构：alter talbe t2 drop primary key;
   
   查询表结构：desc t2;
   

6.3 复合主键的定义

复合主键是由多个列组成的主键，这些列的组合值在表中必须是唯一的。每个单独的列可以包含重复值，但它们的组合值必须是唯一的。

注：一个表中最多只有一个主键，但不意味着一个主键只能被添加到一个字段上，一个主键可以被添加到对个字段上。

语法：

CREATE TABLE 表名 (
列1 数据类型,
列2 数据类型,
…
列n 数据类型,
PRIMARY KEY (列1, 列2, …, 列n)
);

6.4 复合主键的示例

创建表，字段有 姓名name, 物品。每个人可以有多个物品，每个物品；可以被多个人拥有。

1. 创建表
   命令：

   create table t3(
   name varchar(10),
   store varchar(20),
   primary key(name, store)
   );

   

2. 插入数据，都是非空，主键的每组数据不重复
   正确示例：
   命令：insert into t3 values('李明', '书包');
   命令：insert into t3 values('李华', '钢笔');
   命令：insert into t3 values('李明', '钢笔');
   

   错误示例：
   组中的一个数据不插入（为空值）
   命令：insert into t3 (name) values('李世民');
   

   数据与存在的组数据重复
   命令：insert into t3 values('李明'， '书包');
   

7. 自增长 auto_increment

7.1 定义

auto_increment：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。

自增长的特点:

• 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

7.2 示例

1. 创建表
   命令：

   create table t1 (
   id primary key auto_increment,
   name varchar(20)
   );

   

2. 首次插入数据，然后查询
   命令：insert into table t1 (name) values('李明');
   
   首次插入数据，自增长值1。

3. 插入特定的数据后，再插入数据（自增长类型不插入）
   命令：insert into t1 values(100, '李世民');
   命令：insert into t1 (name) values('刘备');
   

   查询数据
   
   可以看出，刘备的id是根据他的上一个id递增的。

7.3 auto_increment 为什么会自增呢？

这是为什么呢？

答：创建表有默认起始值，而且默认起始值在插入数据后会不断地改变。AUTO_INCREMENT 的值不断进行改变。在创建表的时候也可以设置起始值。

证明：

1. 为什么起始值为 1？
   先创建一个自增类型的字段的表
   命令：

   create table t2 (
   id primary key auto_increment,
   name varchar(20)
   );

   
   再查看创建表语句
   命令：show create table t2;
   
   插入的新数据直接使用 auto_increment 的值，所以插入的新值的id为1

2. 插入特定值的时候后，为什么再插入的行的id是上一列的递增值呢？
   插入特定值：insert into t2 values(100,'李明');
   查看创建表
   
   同学们可以发现，当插入的id是100，auto_increment的值会变成101，那么这个101是不是给下一个行作为值呢？

   插入值：insert into t2 (name) values('吕布');
   再次查看数据

   
   显然，吕布的id是用了上面的auto_increment的值，那是不是此时的auto_increment的值变成了102了呢？

   查看创建表：show create table t2;
   

3. 自定义auto_increnment
   创建表时自定义auto_increment=100
   命令：

   create table t3(
   id int auto_increment primary key,
   name varchar(10)
   ) auto_increment = 100;

   创建表后修改表的auto_increment = 111
   命令：

   alter table t3 auto_increment = 111

4. 总结
   综上所述，当设置auto_increment属性后，会有auto_increment来记录插入表的值是什么。auto_increment默认的值为1，当插入新的值后，auto_increment会自增1

7.4 索引

索引：
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结
构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。
数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得
更快，可快速访问数据库表中的特定信息。

8. 唯一键

8.1 定义

我们知道主键的约束的非空和唯一，一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证不要和别的信息
出现重复。乍一听好像没啥区别，我们举一个例子

假设一个场景
比如在学校，我们需要一个学生管理系统，系统中有一个学生表，学生表中有两列信息，一个手机号，一个是学生学号，我们可以选择学号作为主键。
而我们设计学号的时候，需要一种约束，所有学生的学号都不能重复且非空，这样就把学号设置为主键。
我们设计表的时候也需要设置每人的手机号也不能相同，需要这个约束，那么就可以将手机号设计成为唯一键。
一般而言，建议将主键设计成几乎不改变的字段，这样，当同学换手机号的时候，我们可以尽量不会对
主键做过大的调整。

8.2 示例

1. 创建表，number电话号码设置唯一性,unique
   
   查看表结构：
   命令：desc t1;
   
   unique在key中是uni

2. 插入值
   正确插入
   第一次插入
   命令：insert into t1(name, number) values('李明', '12356');
   

   插入表中不存在的number值
   命令：insert into t1(name, number) values('李华', '98765');
   

   不插入number值
   命令：insert into t1 (name) values('张三');
   
   可以发现，不插入的number值为NULL，当我们再次插入NULL会发生错误吗？
   命令：insert into t1 (name, number) values('李四', null);
   
   可以看到，插入NULL的时候没有发生错误，这是为什么呢？
   答：NULL是未知值，未知值是不能进行=比较的。unique属性不对空值进行比较。

   错误插入
   插入表中存在的number值
   命令：insert into t1 (name, '123456');
   

9. 外键

9.1 定义

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。（先创建主表，再创建外表）

语法
创建主表：

CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

说明：

• student_id :主表的字段，可以在从表上建立外键，student_id必须是不同的，且所在行的数据一般也与其他行不同。

创建从表：

CREATE TABLE enrollments (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_code VARCHAR(10),
FOREIGN KEY (student_id) REFERENCES students(student_id)
);

说明：

• 第一个student_id ：从表的字段，设置外键的字段。
• students：主表的表名
• 第二个student_id:主表的字段，插入数据时选择主表student_id的内容

外键约束的行为：

1. 插入数据：在插入 enrollments 表的数据时，从表的student_id 必须在 students 表的 student_id 列中存在，或者为 NULL（如果允许）。
2. 更新数据：在更新 enrollments 表的 student_id 列时，同样的规则适用。
3. 删除数据：在删除 students 表中的某条记录时，如果 enrollments 表中有引用该 student_id 的记录，除非设置了级联删除（ON DELETE CASCADE），否则会报错。

级联操作：

外键约束支持级联操作，可以在主表数据发生变化时自动更新或删除从表中的相关数据。

1. 级联删除：当主表中的记录被删除时，从表中引用该记录的记录也会被自动删除。
2. 级联更新：当主表中的主键值被更新时，从表中引用该主键值的记录也会被自动更新。

9.2 举例

正确示例：

1. 创建一个主表hobby字段有id int primary key , 从表student有字段 hobby_id int，设置从表的外键。
   创建主表命令： create talbe hobby (id int primary key, hobby varchar(20));
   

   创建从表命令：create table student (name varchar(10), hobby_id int,foreign key (hobby_id) references hobby(id) );
   

2. 插入数据，主表要先有数据，从表才能插入数据
   在主表插入数据：
   命令：insert into hobby (hobby) values(1, '代码'), (2, '篮球'), (3, '羽毛球');
   
   查看表：
   

   在从表插入数据
   命令：insert into student values('黎明', 1), ('刘德华', 1), ('王菲', 3);
   
   查看表
   

3. 关系
   hobby_id存入的数值是hobby表中字段id的值，代表hobby兴趣爱好。
   

4. 在从表的字段hobby_id插入 null
   命令：insert into student values('李白', null);
   
   在创建表的时候没有设置 not null，可以插入null。

错误示例：

1. 没有key的字段作为从表的外键
   创建一个没有unique和primary key键的主表
   创建一个表t2，外键连接t1(id)
   命令：create table t2(name varchar(10), hobby_id int);
   
   为什么错误了呢？
   答：t1的hobby字段没有设置key，只有key的字段才能被从表设置为外键。

2. 修改t1表的id字段为为唯一键，再次把id设置为从表的外键
   修改字段id命令：alter table t1 modify id int unique;
   
   创建外键的命令：

   create table t2(
   name varchar(10),
   hobby_id int ,
   foreign key(hobby_id) reference t1(id)
   );

   

3. 直接删除主表
   命令：drop table t1;
   
   为什么会发生错误呢？
   答：当你在主表和从表之间建立了外键约束后，直接删除主表会受到限制，原因在于外键约束确保了数据的完整性和一致性。具体来说，外键约束要求从表中的外键列数据必须在主表的主键或唯一键列中存在。如果直接删除主表，会导致从表中的外键列数据失去参照对象，从而破坏数据的完整性。

9.3 删除主表

法1：
先删除从表，再删除主表。

法2：
先删除从表中引用主表的记录，然后再删除主表。

– 删除从表中的相关记录
DELETE FROM 从表 WHERE 从表的外键字段 IN (SELECT 主表被引用的字段 FROM 主表);
– 删除主表
DROP TABLE 主表;

示例：
删除t2表中引用t1表的记录(用9.3的创建的表)
命令：delete from t2 where hobby_id in (select id from t2);

法3：
使用级联删除
在创建从表时设置级联删除，这样当主表中的记录被删除时，从表中引用该记录的记录也会被自动删除。

CREATE TABLE enrollments (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT,
course_code VARCHAR(10),
FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE
);

ON DELETE CASCADE 是设置级联删除