【Mysql】表的约束

为什么需要表的约束

1)真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的保证数据的合法性,从业务逻辑角度保证数据的正确性

• 比如有一个字段是身份证,要求是唯一的.

2)定义字段的时候,需要有一个类型,这样一个类型,有时候并不能满足我们对一个表的约束.下面的这些都不是数据的类型能够约束的.比如：

• 表字段是否可以为NULL,有没有默认值,表字段的解释能不能加上
• 对于数字类型的字段可不可以指定默认表示的位数,可不可以将这个字段设置称为唯一标识该行的数据,

约束是什么

本质上是Mysql通过限制用户操作的方式,来达到维护数据本身安全,完整性的一套方案

为什么要有约束

本质上Mysql是一套数据存储解决方案,除了解决基本的数据存储功能之外,还要保证数据尽可能的安全,减少用户的误操作可能性, 约束的体现,不仅仅可以体现在数据库层面,在我们进行用户业务逻辑编码的时候,我们其实也可以做判断(约束),约束其实不仅仅只能出现在数据库层而上,编码层面也是可以体现的

---

空属性

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

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

• MySQL中的NULL表示不存在 ,用户没有填写该字段, 和0,空串是不同的
• C/C++中, NULL,0,'\0’数字上是一样的

数据为空不参与运算:

---

案例：创建一个班级表,包含班级名和班级所在的教室

站在正常的业务逻辑中：如果班级没有名字,你不知道你在哪个班级,如果教室名字可以为空,就不知道在哪上课,所以我们在设计数据库表的时候,一定要在表中进行限制,满足上面条件的数据就不能插入到表中,这就是“约束”

插入数据时,没有缺省了就会报错

---

默认值

default：可以在创建表字段的时候,指定一个默认值,当我们插入数据的时候,没有插入该字段,就采用默认属性.

• 设置default可以避免该字段为空,不填自动设置为默认值.

注意:如果一个字段即设置 not null 又设置 default,那么 not null 就失去作用了.

not null和defalut一般不需要同时出现,因为default本身有默认值,不会为空

默认值的生效：数据在插入的时候不给该字段赋值,就使用默认值,只有设置了default的列,才可以在插入值的时候对列进行省略

列描述

列描述：comment,没有实际含义,专门用来描述字段,会根据表创建语句保存,供操作者来查看该列的含义.

• 相当于C/C++的注释

---

zerofill

刚开始学习数据库时,很多人对数字类型后面的长度很迷茫,int(11),这个代表什么意思呢,整型不是4字节吗?这个11又代表什么呢？

其实没有zerofill这个属性,括号内的数字是毫无意义的

---

如果指定了zerofill,则默认会加上无符号属性(unsigned),在数据库中存储的还是原来的值,只不过在查询的时候,会按照宽度进行输出,宽度不够则用0填充, 相当于格式化输出,如果没有zerofill则类型后面的数字没有任何意义

• 例如：当我们创建一个int类型的字段时,如果我们没有指定括号内部的值,MySQL会自动为我们添加一个(11).如果我们没有设置zerofill属性,这个值是无意义的.

• zerofile约束了数字的宽度,如果宽度不够,则用0进行填充

• 比如我们增加了zerofill属性,int(5)则表示宽度为5,如果一个数为6,则其显示为00006.

• 如果插入的数据低于指定的位数,就在前面补0,如果插入的数据超过了指定的位数,就正常显示,这就是zerofill的作用.只对显示结果调整,不影响存储结果

这次可以看到a的值由原来的10变成00010,这就是zerofill属性的作用,这只是最后显示的结果,在MySQL中实际存储的还是10,我们可以用hex函数来证明

可以看出数据库内部存储的还是10和30,00010和00030只是设置了zerofill属性后的一种格式化输出而已

---

主键

数据库存储数据不是唯一的目的,还要考虑到提取数据.一般,要求数据库表中的记录有一个特定的唯一标识,来表示唯一性,这个唯一的特定标识就是主键,类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录

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

---

创建主键

• 在创建表的时候,直接在字段的后面加上关键字 “primary key” ,就可以设置该字段为主键

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

• 在创建表的时候,将所有的字段指定完毕后,在所有的字段后面加上关键字,并且加上指定段的名称

• 在创建表的时候可以不指定,创建完成之后,使用alter关键字来增加主键----为已存在字段追加主键

alter table 表名字 add primary key('字段'); # 追加主键
1

注意:如果该字段内有重复值,则不能以该字段新增为主键,必须先把重复的数据去掉然后才能添加该列为主键

删除主键

alter table	表名字 drop primary key; # 删除主键
1

复合主键

在创建表的时候,在所有字段之后,使用primary key(主键字段列表)来创建主键,如果有多个字段作为主键,可以使用复合主键,这些字段合在一起是一个主键.

例如：ip+port标识全网中的唯一网络进程,他们要构成复合主键

create table `tb_name` (
	`field` datatype,
    primary key (`要设置程复合主键的字段`) # 设置复合主键
); 
1
2
3
4

如果想让多个字段合起来作为主键,就可以使用复合主键.也就是让多个字段联合在一起作为唯一标识,单个字段主键重复是没有问题的,只要不是成为复合主键的字段一起冲突就行.

上述中: 设置成复合主键的两个列信息同时和表当中的数据冲突了才叫主键冲突,如果其中一个重复不称为主键冲突,并且一旦设置为复合主键,他们两个字段都不能为空

---

主键索引

通过主键构建一颗B+树,在用户进行检索的时候,如果约束条件(查找条件)为主键,则会大大的提高搜索效率,因为B+树的搜索效率高,可以找到主键对应的一行数据.

---

自增长

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

自增长的特点:

• 自增长字段必须是整数,自增长字段可以不设置初始值,默认从1开始递增.

• 被自增长的字段必须作为主键或者其他具有唯一性的键使用,(必须保证这一列字段具有唯一性的字段)

• 自增长字段也可以插入数据,只要不与已有数据重复即可,之后新增数据会从最大值开始递增.

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

• 一张表当中最多只能有一个自增长的列

---

注意:自增长的初始值可以在建表的时候设置

---

索引

• 一般在设置主键的时候,建议设置成与业务无关的数据字段,来充当主键! (万一业务逻辑调整,还需要更改主键,更重要的是很多 sql 都与主键有关)
• 书=目录+内容 , 读书的时候,需要查找的大部分都是"内容",目录不是我们读书所关心的,那么目录存在的意义? 加速查找的 !
• 目录要不要占据一些纸张呢? 一定要占用纸张!
• 数据本身就是书的内容,直接查找效率太低了,为了提高查找的效率,MySQL就设计了一个“索引”概念（目录)
  索引要不要占用MySQL的资源呢? 肯定的,索引一定要占用内存或者磁盘空间
• 目录或者索引的本质是什么? 使用空间来换取时间!
• 在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中 一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单,索引的作用相当于图书的目录, 可以根据目录中的页码快速找到所需的内容,
• 索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序,数据库使用索引以找到特定值,然后顺指针找到包含该值的行,这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息,

---

唯一建

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

---

唯一键的本质和主键差不多,唯一键允许为空,而且可以多个为空,空字段不做唯一性比较, 如果将唯一键设置为不能为空则自动转换成主键. not null unique ==> 相当于PRI

唯一键和主键的关系：主键更多的是标识唯一性的.而唯一键更多的是保证在业务上,不要和别的信息出现重复,主键只能有一个,唯一键能设置多个

1）可以说,主键和唯一键并不冲突,在一张表当中,可以有一个主键,也可以同时具有唯一键, 他们不是用来对比的,只是负责数据在不同层面的唯一性,既要保证选择成为主键的属性字段不能重复不能为空,同时也要保证本身具有唯一性的列的字段不能出现冲突

• 主键和唯一键不是对立关系而是补充关系,目的是让数据库创建出一个表具有非常强的约束,彻底杜绝用户的误插入
• 主键用来查找,唯一键用来保证数据的完整性

比如学生信息中有身份证号和学号两个字段,

• 我们可以把身份证号设置成为主键,而所有学生的学号也不能重复,此时就可以把学号设置成唯一键

---

此时id为唯一键,但是即使我们插入了3个id为NULL的字段也不算重复,不会导致唯一键冲突！因为NULL代表没有填写该字段,也就没有比较,也就没有冲突

---

外键

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

• 外键用来将主表和从表之间建立关系,让从表和主表产生关联的字段就叫做外键.外键定义在从表上,一般外键就对应着主表的主键.

---

1. 主表：存放基础属性的表,一般用来描述当前表中的基础属性.
2. 从表：存放具体的用户生成的信息,主表当中的信息与从表是一对多的.
3. 定义主表当中可能成为其它表外键的字段,一定需要将该字段设置成为主键或者唯一建,因为主键不能重复,不能为空,唯一标识一行数据
4. 定义从表的时候,设置外键其实就是设置了一个关系,在从表当中插入数据的时候,会对插入数据进行校验,校验插入的数据是否存在于主表字段当中,外键就是增加了表和表之间的约束关系.

foreign key (从表的字段名称) references 主表名字(主表的字段名称)  #建立外键关联
1

---

通过外键产生关系的表,除了在逻辑上有关联,MySQL也会维护该逻辑对用户操作产生约束

例子1:

例如2:

学生表通过class_id和班级表产生关联,在语义上,class_id称为外键,

此时的这个学生表就相当于从表,班级表相当于是主表, 比如我们新增一个class_id为30的学生,但是班级表中得先有id为30的班级,才能在学生表中添加, 要删除班级表中id为20的班级,首先必须保证学生表中没有对应班级为class_id =20的学生.,当然我们也可以新增一个class_id为NULL的学生,表示该学生还没有分配班级, 外键可以为空！

1.创建主表和从表:

2.插入班级

3.插入学生

4.如果此时想要删除id为10的班级:如果学生表当中有存在这个班级的学生,就会报错, 我们需要先把这个班级的学生删掉,然后才能删除这个班级

---

区分外键和外键约束:

1)外键是用来实现表和表之间关系的字段, 只要是人去操作,就可能犯错误,为了强约束表之间的关系,需要添加外键约束,添加了外键约束,mysql才会帮我们去维护表之间的逻辑关系

2)对于用户来说,外键是为了方便业务关联,对于MySQL来说,外键是为了产生对应的外键约束,保证数据的安全.

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

---