MySQL表的约束

目录

一.空属性

二.默认值

三.列描述

四.zerofill

五.主键

六.自增长

七.唯一键

八.外键

---

        前言：这一篇介绍MySQL表的约束语句，对于主键和唯一键的不同点进行了区分，并且进行了充分的测试。

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

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

一.空属性

not null

        两个值：null（默认的）和not null(不为空)

        数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

        下面创建一个班级表，包含班级名和班级所在教室：

        正常情况下，我们必须得知道班级名和班级在哪，因此设置表的时候，就要在表中进行限制。

        不满足上面条件的数据就不能插入到表中，者就是“约束”。

        下面创建的班级表中要确保班级名和班级所在教室不为空，就添加not null

        创建时两个属性都写，正常创建成功。

        如果有一个写NULL，或者直接不写其中一个，那就直接报错。

        总结：当我们显示的向一列插入，如果插入的是正常值，就正常工作；如果插入NULL，not null的约束就不让你插入。

二.默认值

default

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

        创建表，使用默认值：

        这里显示的表的信息中Default下就是创建表时的默认值。

        这里插入时只给了名字信息，显示时其它两项就是默认值。 

        如果该属性没有信息就会用默认值，如果有就用有的。

        not null和default也可以一起用：

        这里就契合了两种约束。

 

        总结：在我们不显示的向指定列中插入，default会自动起效果。有了default才可以不插入值。

三.列描述

comment

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

        这个列描述显示在这里，虽然它属于约束，但是实际上就类似于代码规范一样，就像注释一样，可以不写，但是写了会看起来更加清晰。 

四.zerofill

zerofill

        之前创建表的数据类型中，浮点数后面可以加长度，表示精度，而整型其实也是可以加长度的。

        这里我们创建的表中的整型并没有在后面加长度，但是desc和show看到的结构中，int后面是有(长度)的。 

        实际上，即使我们不写这个长度，mysql也会给我们写上。

那么int(11)和int(10) unsigned的这个10和11都是什么呢？

         这里修改了zerofill这个属性，将其修改为5，发现a的值由1变成00001，这就是zerofill属性的作用。

        如果宽度小于设定的宽度（这里设置的是5），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。

        这里使用hex转换为十六进制，显示的还是1，因此实际存储的就是1。

        一个整数的取值范围到2^32 - 1，如果是无符号int没有符号位，10位就可以表示2^32 - 1了，有符号int的就是正负2^32 - 1，多了一位1位是符号位。因此一个是10，另一个是11。

五.主键

primary key

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

        让学号是主键，创完表可以发现主键自带not null。

        这里因为name不是主键，所以可以重复，都可以插入。

        id这里是主键，上面有3了，主键不能重复，所以不能再插入了。 

        主键约束：主键对应的字段中不能重复，一旦重复，操作失败。

        删除主键：

alter table 表名 drop primary key;

        成功删除主键。

 

        当表创建好以后但是没有主键的时候，可以再次追加主键：

alter table 表名 add primary key(字段列表);

        这里我们想要加主键，但是无论是id还是name，这个表中都有重复的，因此无法添加主键，所以想要添加主键，必须删掉重复的：

        不过，在实际项目中，一定要在创建表的时候就选择好主键，如果创建表了之后，已经插入了很多信息，那么我们删哪个信息，我们怎么知道这个信息没用呢。这样我们就很难去选择一个主键了。

        复合主键：

        在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

        测试：

        这里因为是复合主键，只有这两个完全一样的时候，才无法插入，剩下即使有一个是一样的也是可以插入的（这里的实际场景是学生A选择了课程Z，学生B当然也可以选择课程Z，这不冲突；学生A除了选择了课程Z，学生A还可以选择课程X）。

        再次选择该课程，即使分数变了，也不能算在内了，因此报错。

如何设计主键呢？

        ① 结合业务，选择一个唯一的列属性作为主键

        ② 选择与业务无关的唯一值(特定设计的)【这样设计，主键值和业务无关，业务调整，不影响整体主键的表的结构】

六.自增长

auto_increment

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

        自增长的特点：

① 任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）

② 自增长字段必须是整数

③ 一张表最多只能有一个自增长

                           

         这里直接用自增长auto_increment无法使用，因为key一栏没有值。

        这里让id变成主键（加primary key），这时key一栏就有值了，可以加上自增长auto_increment了。

         到此为止，desc所看到的表结构的列名都学过了，从左到右分别是Field列名、Type数据类型、Null是否为空(not null约束)、Key主键约束、Default默认值约束、Extra指明当前的列是否为自增长的。

 

         这里没有指明id，但是插入时，id会自动增长，这就是自增长auto_increment的作用。

        看这里的AUTO_INCREMENT=6，这就相当于一个计数器，也就是表维护了一个计数器，每次插入不指明id就会用该数。

        当然，这里可以自己指明id，只要不冲突，可以不使用auto_increment。

        这时因为之前插入了一个id为1000的，再次让它自增长，插入的信息不写入id，就变成了1001，变成了刚才的id+1。auto_increment会变为最大值+1。

        如果想让该表从10000自增长当然也是可以的：

        这里就从10000开始了。

索引：

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

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

七.唯一键

unique

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

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

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

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

        数据库中的学生表，本质其实是用来描述一堆学生信息，以一个学生为例：

        表中存的都是学生身上的属性值，这些属性值，有的是可以重复的，有的是不会重复的。数据库为了能够更好的约束，有了唯一键。

        唯一键和主键并不冲突，反而是互相补充的，共同维护表的完整性。

        创建一个学生表：

         插入两个学生的信息，这里假设我们不小心在插入第二个学生信息的时候忘记修改学生的qq号了，然后就导致插入了一个错误的qq号，是上一个学生的qq号：

        这当我们查找该qq号的学生时，就发现有两个学生，我们就无法知道到底这个qq号是谁的了。这里stu_id有主键约束，qq号没有约束，导致了这个情况。所以在保证stu_id是主键的情况下，可以添加qq号的唯一键约束。就可以避免出现这种情况了。

        现实生活中，有非常多的具有唯一性的值，一般而言，主键只是在众多具有唯一性的属性列中被选择成为主键而已。其它字段的唯一性，在建表时也要保证他们的唯一性。但是因为主键只能有一个，所以mysql提供了另一种保证数据列信息唯一性的方案 ---- 唯一键。

        主键：更多的是为了保证我们在查找的时候，找到唯一的一条记录

        唯一键：更多的是为了保证在表中，各个不同的值，一定要在mysql层面也保证他的唯一性。

        下面创建带有唯一键的学生表：

         给qq和telphone添加了唯一键之后，这里qq号和telphone如果有相同的就会直接报错了。

         主键和唯一键的语法有一定区别：

 

          这里可以看到主键自带not null约束，而唯一键是不带的

         当然，不论主键还是唯一键，如果重复就会报错。

 

  

          这里id是主键不能为空，而number是唯一键可以为空。

         不过，我们可以手动设置唯一键为not null。这样主键和唯一键在语法上基本没有差别了。

八.外键

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

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

一个学生，最终一定要隶属于一个班级。

一个班级，内部一定要有多名学生。

学生： 班级 = n : 1

         创建学生表和班级表：

         插入班级和学生信息：

 

         查找：

          一查找，就可以找到对应的班级的学生信息。

          也可以更改某个学生的信息。

         这里没有班级160，是插入时不小心写错的，那这怎么办？

         应该不允许插入。

         又或者，我们删除了该104班级，但是再去查看学生信息，发现104的学生信息都还在，这怎么办？ 

         如果想要删除班级，在这个班级还有学生就不能删除这个班级。

         在上面的两个表中，在语义上，只有外键的关系，但是，默认依旧是两张独立的表。即表和表之前没有约束。（有关系但没约束）

         有关系 && 有约束：外键约束

         下面建立带有外键约束的学生表和班级表：

         把原表删除。

          重新创建表：

         这里在创建学生表时，它是从表，上一个创建完成的班级表是主表，想要使用外键约束就是在创建从表时，写上 foreign key (字段名) references 主表(列) 即可。

         插入信息。

          这里因为学生表里有学生的班级是110，且有外键约束，所以无法删除。

         这里把学生班级是110的学生删除了之后，就可以把110班级删除了。 

         外键不仅仅是产生表和表之间的关联的，还有一个重要属性往往被人忽略，外键在MySQL中还具有特定的约束规则，保证表和表的数据完整性和一致性。存在约束的关联字段，才叫做外键。