【MySQL】表的约束

掘根

已于 2025-05-13 20:59:44 修改

阅读量912

点赞数 11

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-10-13 11:38:28 首次发布

本文链接：https://blog.youkuaiyun.com/2301_80224556/article/details/142874591

版权

MySQL 专栏收录该内容

23 篇文章

订阅专栏

8.自增长（auto_increment）

1. DESC

我们之前在使用desc的时候有没有想过下面这个事情

Field和Type我们知道是我们创建表时设置的名字和类型，那后面的NULL，Key，Default，Extra是什么东西？

这些都是表的属性，事实上，在创建数据库表时，除了Field（字段名）和Type（数据类型）之外，NULL、Key、Default和Extra也是常见的列属性，它们各自承担着不同的功能和约束：

NULL：
- 表示该字段是否可以为空。
- 如果设置为“NULL”，则表示该字段可以接受空值。
- 如果设置为“NOT NULL”，则表示该字段必须有一个非空的值。
Key：
- 表示该字段在数据库中的键属性。
- 常见的键属性包括“PRI”（主键）、“UNI”（唯一键）、“MUL”（多值索引，通常表示该字段是某个外键的一部分）等。
- 主键（PRI）是表中每条记录的唯一标识，具有唯一性和非空性。
- 唯一键（UNI）确保字段中的所有值都是唯一的，但允许有空值（如果存在多个空值，它们被视为不同的值）。
Default：
- 表示该字段的默认值。
- 当插入新记录而没有为该字段指定值时，将使用此默认值。
- 如果不设置默认值，并且字段被定义为“NOT NULL”，则在插入新记录时必须为该字段指定一个值。
Extra：
- 提供了关于字段的额外信息。
- 常见的值包括“auto_increment”（自动递增）、“on update CURRENT_TIMESTAMP”（在更新记录时自动设置为当前时间戳）等。
- 自动递增字段通常用于主键，以确保每条记录都有一个唯一的标识符。

这些列属性在创建和修改表结构时非常重要，它们有助于确保数据的完整性、一致性和准确性。通过合理使用这些属性，可以更有效地管理数据库中的数据。

2. 什么是表的约束？

所谓约束，就是一种限制。这个限制就是对我们要插入表中的数据的限制。比如下面

查看表中的内容，发现合法的数据被成功的插入了，非法的数据直接被拒绝插入，说明MySQL不会为我们的整形进行截断转换。

约束本质就是通过技术手段，倒逼程序员插入正确的数据，反过来站在mysql的视角，凡是插进来的数据都是符合数据约束的！
约束的最终目标都是保证数据的完整性和可预期性

表的约束是数据库中用于确保数据完整性和一致性的规则。

这些约束定义了可以存储在表中的数据类型，以及数据之间的关系和格式。

通过应用约束，数据库管理系统（DBMS）能够在数据插入、更新或删除时自动检查数据的有效性，从而避免数据错误和异常。

真正约束字段的是数据类型，如果插入的数据超出了对应数据类型的取值范围，那么数据将会插入失败。
但是数据类型的约束很单一，为了更好的保证数据的合法性，从业务逻辑角度保证数据的正确性，MySQL中出现了表的约束，目的就是为了尽可能保证数据安全，减少用户的误操作可能性。
表的约束有很多，本篇博客主要介绍如下几个：null/not null、default、comment、zerofill、primary key、auto_increment、unique key、foreign key。

3.空属性（NULL）

我们上面在讲DESC的时候就提过这个空属性

这个NULL里面的YES的意思就是插入数据时可以不插入这个chart (缺省）

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

我们可以看看NULL的运算

在SQL中，NULL表示一个未知或未定义的值。处理NULL时，有一些特殊的规则和行为需要了解。

SELECT NULL;

这条语句会返回一个结果集，其中包含一个列，这个列的唯一值是NULL。它不会抛出错误，只是简单地返回NULL作为结果。

SELECT NULL + 1;

这条语句尝试将NULL与数字1相加。在SQL中，任何与NULL进行的算术运算都会返回NULL。这是因为NULL代表未知，而未知值与任何已知值进行运算，其结果仍然是未知的。因此，这条语句的结果也是一个NULL值。

总结来说，SELECT NULL;会返回NULL，而SELECT NULL + 1;也会返回NULL，因为在SQL中，与NULL进行的任何算术运算的结果都是NULL。

下面我们去构建一个班级成员表，每一个学生都要有一个班级：

create table class( name varchar(20), room_id tinyint );
desc class;

下面我们对这个表进行插入一些数据：张飞，2，刘备，2，曹操，1，孙权，3。

进行插入：

insert into class(name, room_id)  values ('张飞',2);
insert into class(name ,room_id)  values ('刘备',2);
insert into class(name, room_id)  values ('曹操',1);
insert into class(name)  values ('孙权');

插入完毕，我们查看一下表内容：

select * from class;

咦，孙权同学怎么会没有班级呢？

仔细看一看我们的插入SQL，我们发现是我们在插入时，少写了room_id的相关信息，于是我们就要重新进行录入信息，可是这种出错是人为不小心出错，如果我们没有及时发现，那可能就会产生不好的后果，为了避免这种低级失误，我们就可以对name和room_id这两个字段都加上not null约束，这样，当我们有信息没有录入完全时就会报错，我们就能够及时发现并修改。

我们重新创建这个表，并进行信息录入：

create table class2( name varchar(20) not null, room_id tinyint not null);
desc class2;

可以看到Null那一列都变成了NO

方便对比，我把class的拿下来

进行插入：

insert into class2(name, room_id)  values ('张飞',2);
insert into class2(name ,room_id)  values ('刘备',2);
insert into class2(name, room_id)  values ('曹操',1);
insert into class2(name)  values ('孙权');

插入完毕，我们查看一下表内容：

select * from class2;

我们发现孙权的插不进去，因为他的room_id没有给出来

表中的信息，都是正确的！在我们希望插入数据时有些字段不能为NULL时，我们就可以使用not null约束。

事实上，我们插入NULL进去也会被拦截
insert into class2(name, room_id)  values ('刘烨',NULL);

4.默认值（Default）

和上面的NULL一样，这个字段也是DESC的一部分哦。

可以看到它默认情况下就是NULL。代表没有默认值

Default：

表示该字段的默认值。
当插入新记录而没有为该字段指定值时，将使用此默认值。
如果不设置默认值，并且字段被定义为“NOT NULL”，则在插入新记录时必须为该字段指定一个值。

某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

设置列的默认值

create table 表名（列名 数据类型 defaule 默认值,……);

例如：我们去银行开户，当我们开户时不存钱，那么我们的银行卡余额就应该默认是0元。

我们看到这个money的Default一栏默认就是0.00。我们插入数据进去看看。

insert into account (name,money) values ('张三',100);
insert into account (name) values ('李四');
select * from account;

大家看看啊，李四的money的默认值就是0.00。

事实上，我们可以同时设置NULL和Default两个属性，但是有人就好奇了，我设置not null是为了插入数据时禁止插入空的进来或者禁止不插入，我设置Default是为了我不插入时可以有个默认值，这么说它们两个组合在一起的话，那我岂不是插入数据时禁止插入空的进来或者禁止不插入，那我这个默认值有什么用啊？

但事实上MySQL并没有像上面那样子做

在 MySQL 数据库中，同时设置 NOT NULL 和 DEFAULT 属性并不会导致冲突。这两个属性在功能上是互补的，它们共同作用于数据库表的列（字段），以确保数据的完整性和一致性。

NOT NULL 属性的作用：NOT NULL 是一个约束条件，用于确保列中不会存储 NULL 值。当尝试将 NULL 值插入到 NOT NULL 列时，数据库将抛出一个错误。
DEFAULT 属性的作用：DEFAULT 用于为列指定一个默认值。如果在插入新记录时未为该列提供值，则数据库将自动使用指定的默认值。

当这两个属性同时设置在同一列上时，它们的行为如下：

如果在插入新记录时未为该列提供值，数据库将使用为该列指定的默认值，因为该列是 NOT NULL 的，所以不允许插入 NULL 值。
这意味着，即使未明确提供值，该列也将始终有一个有效的值（即默认值），从而满足 NOT NULL 的约束。

因此，NOT NULL 和 DEFAULT 属性的组合是合理的，并且在实际应用中非常常见。它们共同确保了数据库表中数据的完整性和一致性，同时减少了因数据缺失或不一致而导致的错误和异常。
总的来说，这两个属性在功能上是协同工作的，而不是相互冲突的。它们为数据库表的设计提供了强大的灵活性和控制力，有助于维护高质量的数据。

当同时设置 NOT NULL 和 DEFAULT 属性时，它们的行为如下：

如果在插入新记录时未为该列提供值，那么数据库将使用为该列指定的默认值。
由于该列被标记为 NOT NULL，因此无法插入 NULL 值。如果尝试这样做，数据库将抛出一个错误。
create table person (name varchar(10),id int not null default 1);
desc person;
此时向表中插入数据时

可以不指明id进行插入，此时会使用id的默认值。
指明id为NULL会被禁止

如下：
insert into person (name,id) values('张三',99);
insert into person (name) values('李四');
insert into person (name,id) values('王五',NULL);
select * from person;
很好！

5.列描述（comment）

列描述是在创建表的时候用来对各个字段进行描述的，列描述会根据表创建语句保存，一般是用来给程序员或DBA了解表的相关信息的，相当于一种注释。

比如创建一个用户表，表当中包含用户名、用户的年龄和用户的性别，在每一个字段后面添加上对应的列描述。如下：

create table user( name varchar(20) comment '用户姓名', age  tinyint unsigned comment '用户年龄', sex enum('男','女') comment '用户性别' );

创建表完毕后，通过show create table 表名 \G就可以看到创建表时的相关细节，包括列描述。如下：

show create table user \G

6.zerofill

6.1.显示宽度

刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。通过show看看tt3表的建表语句：

可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？

在 MySQL 中，INT(10) 的语法中的 (10) 并不表示整型数据在存储时所占用的字节数，而是表示当使用 ZEROFILL 属性时，该数字在显示时的最小宽度。然而，如果不使用 ZEROFILL，这个显示宽度实际上是没有效果的。

整型（INT）在 MySQL 中通常占用 4 个字节的存储空间，不论你是否指定了显示宽度。这个 4 字节的存储空间可以存储的整数范围是 -2,147,483,648 到 2,147,483,647（对于有符号整数）或 0 到 4,294,967,295（对于无符号整数）。

当你看到 INT(10) 这样的声明时，如果没有伴随 ZEROFILL，这意味着：

存储大小仍然是 4 个字节。
显示宽度 (10) 在没有 ZEROFILL 的情况下不会影响存储或显示。

如果使用了 ZEROFILL，比如 INT(10) ZEROFILL，那么：

数字在显示时会被填充到至少 10 位宽度。
如果数字本身位数不足 10 位，前面会用零来填充。
例如，存储的数字 123 在显示时会变成 0000000123。

然而，请注意，即使使用了 ZEROFILL，存储大小仍然是 4 个字节，不会因为显示宽度的增加而增加。

总的来说，INT(10) 中的 (10) 在没有 ZEROFILL 的情况下是一个没有实际影响的参数，它不会改变数据的存储大小或存储方式。在使用 ZEROFILL 时，它才会影响数字的显示方式。

事实上，上面这个10代表的就是显示宽度，如果没有zerofill这个属性，括号内的数字是毫无意义的。

a和b列就是前面插入的数据，如下：

6.2.zerofill

所谓zerofill，就是填充0
对应数值类型设置zerofill属性后，如果数据的宽度小于设定的宽度则自动填充0，当然zerofill只是影响了显示的效果，并不会影响数据的具体值。

在 MySQL 中，当为数值类型设置 ZEROFILL 属性后，如果数据的宽度小于设定的显示宽度，则会自动在数据的前面填充零。这里有几个关键点需要注意：

显示宽度：如前所述，对于整数类型，显示宽度在 MySQL 8.0.17 及更高版本中已被废弃，并且对于存储和计算没有影响。然而，即使在这个版本之后，ZEROFILL 仍然会基于一个“假设”的显示宽度来填充零，这个宽度通常是你在创建表时指定的宽度（如果指定了的话）。对于 DECIMAL 类型，显示宽度仍然有效，并且会影响 ZEROFILL 的行为。
填充零：当为字段设置了 ZEROFILL 属性后，如果插入的数据的字符表示形式（即，如果将其视为字符串）的长度小于字段的显示宽度，MySQL 会在数据的前面填充零，直到达到显示宽度。这确实只影响显示效果，不会改变数据的实际值或存储方式。
数据类型：ZEROFILL 只能与数值类型一起使用，包括整数类型和定点数类型（DECIMAL 和 NUMERIC）。它不能与浮点类型（FLOAT, DOUBLE）一起使用。
存储和计算：尽管 ZEROFILL 会影响数据的显示方式，但它不会影响数据的存储大小或计算方式。数据的实际值仍然是一个普通的数值。
字符集和排序规则：ZEROFILL 实际上是将数值类型视为字符类型来处理，以便进行零填充。这意味着，在比较和排序时，ZEROFILL 字段可能会按照字符的字典顺序来比较，而不是按照数值大小来比较。这可能会导致一些不直观的结果，特别是在涉及前导零的情况下。
客户端工具：不同的数据库客户端工具可能会以不同的方式处理 ZEROFILL 和显示宽度。因此，在不同的工具中查看相同的数据时，可能会看到不同的格式。

总的来说，ZEROFILL 是一个有用的属性，可以用来确保数值在显示时具有一致的宽度和格式。然而，它也有一些潜在的陷阱和限制，特别是在涉及比较和排序时。因此，在使用 ZEROFILL 时应该谨慎，并确保了解它的行为以及它如何与你的数据库客户端工具交互。

接下来我们来看看这个zerofill怎么使用

比如创建一个表，表当中包含num1和num2，num3，num4四列整型数据：

num1不设置zerofill属性（实际上系统默认显示宽度是10）
num2设置zerofill属性（实际上系统默认显示宽度是10）
num3不设置zerofill属性，但是num3的显示宽度设置为5
num4设置zerofill属性，但是num4的显示宽度设置为5

(对于int unsigned由于没有符号位，所以最大的数字就是42亿多，所以默认的显示宽带为10，即10位数字宽度，如果有符号位则为11)

我们先预测一波，只有2和4既设置了zerofill属性和显示宽度，所以只有这两个的显示情况会受一点影响！

create table test( num1 int unsigned, num2 int unsigned zerofill, num3 int(5) unsigned, num4 int(5) unsigned zerofill );
desc test;

见见zerofill的正常显示效果

向表中插入一条记录，指明所有的值均为1：
insert into test values(1,1,1,1);
select * from test;
我们上面的预测是对的，只有2和4既设置了zerofill属性和显示宽度，所以只有这两个的显示情况会受一点影响！

这次可以看到num4的值由原来的1变成00001，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是 5），自动填充0。num2同理。

由于我们没有给num1和num3字段设置zerofill属性，因此查看表中数据时显示出来的都是1，并没有显示宽度的概念。
对于num2和num4由于我们设置了zerofill属性，因此查看表中数据时位数不够会给我们进行补0操作，有显示宽度的概念。

见见超出设定的宽度后zerofill的显示效果
insert into test values(11111111,11111111,11111111,11111111);
select * from test;
这里我们特别关注num4，11111111显然超过了5位数，当超出设定的宽度后zerofill的约束效果就"消失"了。

我们给num3指定的显示宽度为5，但是我们没有设置zerofill，所以这个显示宽度没有任何意义，所以显示的和num1一样。
这里我们给num4插入11111111，我们设置的num4的显示宽度设置的是5位，但是这里一共显示了8位。这个很正常。

我们来解释一下

显示宽度的限制：对于整数类型，从 MySQL 8.0.17 开始，显示宽度已经被废弃，并且不会对存储或显示有任何实际影响。但是，即使在这个版本之前，显示宽度也只是一个建议，它不会阻止您存储超出该宽度的数值。
ZEROFILL 的行为：当您为字段设置了 ZEROFILL 属性时，如果插入的数值的字符表示形式的长度小于显示宽度，MySQL 会在前面填充零。但是，如果插入的数值的字符表示形式的长度大于显示宽度，MySQL 不会截断它或抛出错误。相反，它会完整地显示该数值。
我们有一个 INT UNSIGNED ZEROFILL 字段（并且我们设置显示宽度为 5，尽管这个设置在 MySQL 8.0.17 及更高版本中不会有任何效果），并且您插入了数值 11111111，那么无论显示宽度是多少，该数值都会以其完整的 8 位形式显示，因为 11111111 的字符表示形式就是 8 位长。
显示宽度和 ZEROFILL 只会影响数值的显示方式，不会改变其实际值或存储方式。因此，即使您为字段设置了显示宽度为 5，您仍然可以插入和存储一个 10 位的数值，并且它会以其完整的形式显示。

7.主键（PRI）

7.1.key的介绍

我们在desc里面就谈过key这一列

在“key”一栏中，你可能会看到以下几种键类型：

PRI：这表示该列是表的主键。主键用于唯一标识表中的每一行记录。一个表只能有一个主键，但主键可以由多列组成（这种情况叫复合主键）。主键列通常具有NOT NULL约束，且会自动创建一个唯一索引。
UNI：这表示该列是一个唯一键。唯一键用于确保列中的所有值都是唯一的，但允许有空值（尽管多个空值并不违反唯一性约束，但在某些数据库系统中，空值也被视为“相等”的，这取决于具体的数据库实现）。一个表可以有多个唯一键。唯一键也会自动创建一个唯一索引。
MUL：这表示该列是某个索引（非唯一索引）的一部分，并且该索引允许在列中有重复的值。MUL标记通常出现在作为复合索引一部分的列上，或者当列是外键时（尽管外键本身不直接创建MUL标记，但外键通常与索引相关联，以提高查询性能）。需要注意的是，MUL并不直接表示该列是外键；它只是表示该列参与了某个非唯一索引。
（空）：如果这一栏是空的，那么该列不是主键、唯一键或索引的一部分。

因此，当你查看DESC命令的输出时，你可以通过查找“PRI”标记来识别主键列。主键对于数据库表来说非常重要，因为它不仅确保了表中记录的唯一性，还常用于与其他表建立关系（例如，通过外键）。
此外，主键通常会自动创建一个唯一索引，以加快基于主键的查询速度。在大多数情况下，主键列还应该是不可为空的（即，它们应该具有NOT NULL约束），以确保能够始终唯一地标识表中的每一行。

7.1.普通主键

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

以下是对主键特性的进一步解释和澄清：

唯一性约束：
- 主键用于唯一地标识表中的每一行记录。
- 因此，主键列中的每个值都必须是唯一的，不能重复。
非空约束：
- 主键列中的值不能为空（NULL）。
- 空值无法唯一地标识一条记录，因此主键列必须具有NOT NULL约束。
单表唯一性：
- 一张表中最多只能有一个主键。
- 但这个主键可以由一个或多个列组成（复合主键）。
数据类型：
- 主键所在的列通常是整数类型，如INT、BIGINT等。
- 整数类型的数据占用存储空间较少，且索引效率较高。
- 然而，在某些情况下，也可以使用其他数据类型作为主键，如字符串（但通常不推荐，因为字符串比较相对较慢）。
自动索引：
- 数据库系统会自动为主键创建一个唯一索引。
- 这个索引用于加快基于主键的查询速度。
外键关联：
- 主键通常还用于与其他表建立关系（外键关联）。
- 通过外键，可以维护表之间的数据一致性和完整性。

普通主键

创建表的时候直接在字段上指定主键，比如创建一个学生表，表当中包含学生的学号和姓名，由于学生的学号是不会重复的，因此可以将其设置成主键。如下：

create table student(id int unsigned primary key comment '学号', name varchar(20) not null comment '姓名');

desc student;

我们看到id被设置为主键。

有主键约束时，插入表中的记录的主键字段不能重复，如果插入记录的主键与表中已有记录的主键重复，这时就会因为主键冲突而插入失败。

我们看到id被设置为主键，id也就不能出现重复的情况。

如下：

insert into student values (1, '张三');
insert into student values (1, '李四');
insert into student values (2, '李四');
select * from student;

主键有唯一性约束，所以id不能出现重复的！

我们可以接着测试非空约束
insert into student (name) values ('李四');
select * from student;
确实，主键就是有非空约束

删除主键

使用下面的SQL即可删除指定表的主键，因为一个表只有一个主键，因此删除主键时只用指明要删除哪张表的主键即可。

alter table 表名 drop primary key

我们可以来删除主键——id

alter table student drop primary key;
desc student;

我们看到id的key那一栏没有PRI字样了，说明他不是主键了

增加主键

对于已经创建好了的表，使用下面的SQL

alter table 表名 add primary key (列名)

我们使用这个来增加主键——id

alter table student add primary key (id);
desc student;

很好啊！！

注意：虽然我们可以在中途给某一列添加主键，但是最好创建表时就指定好主键，不然在表的使用过程中，当我们想要为某一列设置主键时但是这一列已经出现了重复数据了，这时我们设置主键就会失败，除非将重复的数据给删除掉然后再添加主键。

7.2.复合主键

复合主键

一张表中最多只能有一个主键，但是一个主键是可以由多个字段（多列）一起构成来执行主键的功能，这种主键叫做复合主键。
复合主键用来唯一约束多个字段里面的数据，表当中每条记录的这多个字段不能同时重复也不能为空。

语法：

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建复合主键。

比如创建一个进程表，表当中包含进程的IP地址、端口号和进程的相关信息，并将IP地址和端口号组合起来形成一个复合主键。

create table process (ip varchar(15) comment 'ip地址', port smallint unsigned comment '端口号',info varchar(128) comment '相关信息',primary key(ip, port));

desc process;

在向进程表中插入数据时，只有插入进程的IP和端口均出现冲突时才会产生主键冲突，否则就允许插入。如下：

查看表中插入的数据可以看到，表当中有重复的IP地址，也有重复的端口号，但是不会出现IP和端口均重复的，这就是复合主键的作用！

删除复合主键

与普通主键一样，使用下面的SQL，因为一个表只有一个主键，因此删除主键时只用指明要删除哪张表的主键即可。

alter table 表名 drop primary key;

比如这里删除进程表的复合主键后再查看表结构，可以看到ip和port对应的Key列的PRI都没有了：

alter table process drop primary key;
desc process;

我们看到复合主键不见了

增加复合主键

与普通主键一样，使用下面的SQL，对于已经创建好了的表，使用下面的SQL来增加复合主键

alter table 表名 add primary key (列名)

比如这里重新将ip和port设置成进程表的复合主键后再查看表结构，可以看到ip和port对应的Key列的PRI又回来了。如下：

alter table process add primary key (ip,port);
desc process;

8.自增长（auto_increment）

自增长就是自动增长，设置了自增长属性的字段，插入数据时如果不给该字段值，那么系统会自动找出当前字段当中已有的最大值，将最大值进行+ 1后的值插入该字段。
任何一个字段要做自增长，前提是其本身必须是一个索引（Key一栏有值），并且自增长字段必须是整数类型，一张表最多只能有一个自增长字段。
自增长通常和主键搭配使用，作为逻辑主键。一般而言，建议将主键设计成与当前业务无关的字段，避免因为业务逻辑的调整而需要修改主键。

比如创建一个学生表，表当中包含id和name，将id同时设置成主键和自增长字段。如下：

create table auto_student( id int primary key auto_increment,  name varchar(20) not null);

desc auto_student;

向表中插入第一条记录时如果没有指明自增长字段的值，那么自增长字段的值默认将会从1开始，后续向表中插入记录时如果也不指明自增长字段的值，那么自增长字段的值就会依次递增。如下：

insert into auto_student (name) values ('张三');
insert into auto_student (name) values ('李四');
select * from auto_student;

当然，插入记录的时候也可以指明自增长字段的值，此时将会使用该值进行插入，但注意指明的值不能和表中已有的id值重复。如下

insert into auto_student (id,name) values (6,'王五');
select * from auto_student;

此后向表中插入记录时如果又不指明自增长字段的值，那么自增长字段的值将会从id列中找出最大值，将最大值加一后得到的值作为自增长字段的值进行插入。如下：

insert into auto_student (name) values ('赵六');
insert into auto_student (name) values ('刘八');
select * from auto_student;

为什么MySQL知道我们当前的自增值的最大值是多少呢？

其实我们可以通过查看表创建时的相关信息得到答案
show create table auto_student \G
可以看到其实MySQL记录了了当前自增值的值为多少，当然，我们也可以在创建表时，在表外设置这个值。

其实我们可以手动设置这个AUTO_INCREMENT的值
create table test1(id int primary key auto_increment,name varchar(20) not null)auto_increment=200;

show create table test1 \G
我们插入数据看看
insert into test1 (name) values ('张三');
insert into test1 (name) values ('李四');
select * from test1;
如果我们想要知道上次的自增值是多少，我们可以输入下面这个指令
select last_insert_id();

事实上，我们删除了表中的所有记录，然后重新输入值进去，编号是不会从1开始重新分配，而是从既有最大值开始分配。

如果说我们想要修改这个值的话，我们可以像下面这样子做

alter table 表名 auto_increment=要设定的初始值;

9.唯一键（UNI）

唯一键（Unique Key）在数据库设计中是一种重要的约束，用于确保表中某个字段或字段组合的值在整个表中是唯一的。这种约束有助于维护数据的完整性和一致性，防止数据重复。

唯一键的特性

唯一性：
- 唯一键约束的字段或字段组合在表中的值必须是唯一的。这意味着，在表中不能有两行具有相同的唯一键值。
空值处理：
- 大多数数据库系统允许唯一键字段包含一个或多个空值（NULL）。然而，具体行为可能因数据库系统而异。有些数据库可能限制唯一键列只能有一个NULL值，而有些则可能允许多个。
- 需要注意的是，空值在唯一性比较中被视为“未知”，因此多个空值不会违反唯一性约束。
自动索引：
- 当在表中创建唯一键时，数据库系统会自动为该字段或字段组合创建唯一索引。这有助于加速查询操作，因为数据库可以利用索引来快速定位数据。
多个唯一键：
- 一张表中可以有多个唯一键。这允许设计者在需要时，为表中的多个字段或字段组合提供唯一性约束。

主键和唯一键的区别：

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键，为了解决这个问题唯一键就出现了。
唯一键和主键都能保证字段中数据的唯一性，但唯一键允许字段为空，并且可以多个字段为空，空字段不做唯一性比较。
需要注意的是，不是主键具有唯一性，而是某个具有唯一性的字段被选择成为了主键，而那些不是主键但是同样需要唯一性约束的字段就应该设置成唯一键。

准确的来说

唯一性保证：
主键和唯一键都能确保字段（或字段组合）中的值是唯一的。
主键是表中数据的唯一标识符，用于唯一地标识表中的每一行。
唯一键则用于确保特定字段（或字段组合）的值在整个表中是唯一的，但不一定要作为标识符。

空值处理：
主键字段不允许有空值（NULL）。
唯一键字段则允许有一个或多个空值，但通常数据库系统会限制为只能有一个NULL值（这取决于具体的数据库实现）。
需要注意的是，空值在唯一性比较中被视为“未知”，因此多个空值不会违反唯一性约束。

数量限制：
一张表中只能有一个主键。
一张表中可以有多个唯一键。

索引创建：
主键和唯一键都会自动创建相应的唯一索引，以加速查询和确保数据的唯一性。

使用场景：
主键通常用于与其他表建立关联关系，实现数据的一致性和完整性。
唯一键则用于确保特定字段（或字段组合）的值在表中是唯一的，而不一定用于关联。

关于唯一键和主键的区别：

我们可以简单理解成，主键更多的是标识唯一性的方便我们进行索引。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

乍一听好像没啥区别，我们举一个例子：

假设一个场景(当然，具体可能并不是这样，仅仅为了帮助大家理解)

比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。

而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。这个员工号代表的就是在业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。

一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。

例如创建一个学生表，表当中包含学生的学号、姓名和电话号码，将我们选择学号作为主键，但同时每个学生的电话号码也应该具有唯一性约束，因此应该将电话号码设置成唯一键。如下：

create table uni_student(id int primary key auto_increment comment '学号',name varchar(20) not null comment '姓名',tel char(11) unique comment '电话号码' );

desc uni_student;

向表中插入记录时，如果插入记录中的电话号码与表中已有记录的电话号码出现重复，那么就会因为唯一键冲突而插入失败。如下：

insert into uni_student (id,name,tel) values (1,'张三',12345);
insert into uni_student (name,tel) values ('李四',12345);
select * from uni_student;

此外，向表中插入的记录可以不指明唯一键字段的值，此时该字段默认为空，不做唯一性比较。如下：

insert into uni_student (name) values ('A');
insert into uni_student (name) values ('B');
select * from uni_student;

10.外键

一、定义

外键（Foreign Key）也称为外码或外部关键字，是用于定义两个表之间的引用关系的一种约束。
在关系型数据库管理系统（RDBMS）中，外键指定了一个表的一个或多个列与另一个表的主键或唯一键之间的关联。
具有外键的表通常称为“引用表”或“从表”，而被引用的表则称为“参照表”或“主表”。

二、作用

建立数据表之间的关联：外键可以用来建立两个数据表之间的关联关系。通过在一个表中定义外键，可以指定该外键与另一个表的主键相对应，从而连接两个表，实现数据的关联查询和操作。
维护数据的完整性：外键可以确保数据的完整性，避免数据的不一致和冗余。当在引用表中插入或更新数据时，外键约束会确保这些数据在被引用表中存在，从而防止无效数据的插入。
支持数据的一致性：外键还可以确保数据的一致性。当在主表中更新或删除某个值时，外键可以自动更新或删除与之关联的引用表中的数据，从而保持数据的一致性。
提高数据查询的效率：通过外键建立的关系，可以使查询操作更加高效。可以使用关联查询（JOIN）来获取多个表中的相关数据，而不需要手动编写复杂的查询语句。

三、使用原则

为关联字段创建外键：在创建外键时，应确保外键列与被引用表的主键列或唯一键列相关联。
所有的键都必须唯一：外键列中的值必须唯一，以确保引用关系的准确性。
避免使用复合键：复合键是由多个列组成的键，使用复合键作为外键可能会增加数据库设计的复杂性。
外键总是关联唯一的键字段：外键应始终引用被引用表中的唯一键字段，以确保引用关系的唯一性。

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

10.1.创建外键

语法：

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

例如我们要设置两张表，一张学生表，一张班级表，如下：

可以看出上面的两张表出现了数据冗余，我们实际需要一个班级id就行了，于是我们就可以设计成让stu->class_id和myclass->id形成关联的关系，这就需要使用到了外键约束了。

下面我们就对上面的示意图进行创建：

先创建一个班级表作为主表，表当中包含班级的id和班级名，并将班级id设置为自增主键。

create table class(id int primary key auto_increment comment '班级编号',name varchar(20) not null comment '班级名称');

desc class;

再创建一个学生表作为从表，表当中包含学生的id、姓名以及学生所在班级对应的id，并将学生表中的班级id列设置成外键，关联到班级表中的班级id列。如下：

create table stu(id int unsigned primary key comment '学号',name varchar(20) not null comment '姓名',class_id int comment '学生所对应的班级',foreign key (class_id) references class(id));

表创建完毕后查看学生表的表结构，可以看到学生表中的班级id对应的Key列出现了MUL标志，这表明class_id已经成功被设置成了外键。如下：

desc stu;

为了演示外键约束的功能和特性，我们先向班级表中插入两条记录。如下：

insert into class values (1, '机器人工程');
insert into class values (2, '通信工程');
select * from class;

我们现在就有两个class啦。现在就要学生呢

这时向学生表中插入记录时，如果插入的记录对应的班级id是班级表中存在的，或者插入的班级id为null，那么此时是允许进行插入的。如下：

insert into stu values(1,'张',1);
insert into stu values(2,'刘',2);
insert into stu values(3,'李',NULL);
select * from stu;

但如果插入学生表的记录对应的班级id是3 ，相当于插入学生表的这条记录对应的班级并不存在，此时将会插入失败，这就是外键约束。如下：

insert into stu values(4,'张',3);
insert into stu values(5,'刘',4);
select * from stu;

这就是刚才我们说的：当定义外键后，要求外键列数据 必须在主表的主键列存在或为null。

10.2.删除外键

在这个学生和班级的示例中，我们的从表stu的外键字段class_id是依赖于主表class的id。

当我们删除主表中id对应的数据时，必须要确保这个id字段不能有数据还在关联。

select * from stu;
select * from class;
delete from class where id=2;

所以这里当我们想要删除2号班级时，必须先将确保这个班里面没有学生。

delete from stu where class_id=2;
delete from class where id=2;
select * from class;
select * from stu;

10.3.如何理解外键约束

外键约束

强制引用完整性：
外键约束确保子表中的外键列的值必须与父表中的主键或唯一键列的值匹配，或者为NULL（如果允许）。
这可以防止在子表中插入无效的引用或孤立的数据，从而维护了数据的引用完整性。

维护数据一致性：
当父表中的数据发生变化时，外键约束可以自动更新或删除子表中对应的数据，以保持数据的一致性。
例如，如果父表中的某个记录被删除，那么子表中引用该记录的外键列的值也会被相应地删除或更新（取决于数据库的配置）。

防止意外删除：
外键约束可以防止意外删除关联表中的数据。
如果某个表被其他表引用，那么在删除该表的数据之前，数据库会检查是否存在相关的外键约束。
如果存在关联数据，数据库会拒绝删除操作，从而避免数据丢失。

提升查询性能：
虽然外键约束本身不会直接提升查询性能，但它可以帮助数据库优化器更好地理解和利用表之间的关系。
这有助于数据库在查询时选择合适的执行计划，从而提高查询效率。

如何理解外键约束首先我们承认，这个世界是数据很多都是相关性的。

理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。此时，在实际使用的时候，可能会出现什么问题？有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？

比如比特只开了比特100班，比特101班，但是在上课的学生里面竟然有比特102班的学生(这个班目前并不存在)，这很明显是有问题的。因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

解决方案就是通过外键完成的。

建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

11. 综合案例 - 阅读

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

要求：

每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(男，女)

-- 创建数据库
create database if not exists bit32mall
default character set utf8 ;

-- 选择数据库
use bit32mall;

-- 创建数据库表

-- 商品
create table if not exists goods
(
goods_id int primary key auto_increment comment '商品编号',
goods_name varchar(32) not null comment '商品名称',
unitprice int not null default 0 comment '单价，单位分',
category varchar(12) comment '商品分类',
provider varchar(64) not null comment '供应商名称'
);

-- 客户
create table if not exists customer
(
customer_id int primary key auto_increment comment '客户编号',
name varchar(32) not null comment '客户姓名',
address varchar(256) comment '客户地址',
email varchar(64) unique key comment '电子邮箱',
sex enum('男','女') not null comment '性别',
card_id char(18) unique key comment '身份证'
);

-- 购买
create table if not exists purchase
(
order_id int primary key auto_increment comment '订单号',
customer_id int comment '客户编号',
goods_id int comment '商品编号',
nums int default 0 comment '购买数量',
foreign key (customer_id) references customer(customer_id),
foreign key (goods_id) references goods(goods_id)
);