MySQL：表的约束

       没有约束，就像我们平时往文件里写入，想怎么写就怎么写，完全取决于你的意志，但是我们为了让用户在数据库中安全的插入数据，无论你是编程小白的误操作还是编程大佬的恶意操作，我们都要想办法通过约束进行拦截（比如给年龄的一列插入了性别，身高的一列插入了体重，qq号那列插入了邮箱），来保证我们数据库内数据的合法性。（编译器其实也算是一种约束，你如果语法写错了会给你报错，所以经过编译器的代码虽然逻辑可能有问题，但是至少在语法是没有问题的）

       所以表的约束，就是通过表中的各种约束，来确保我们未来插入数据库的数据是符合预期的。约束的本质是通过技术手段倒逼程序员插入正确的数据。反过来，站在mysql的视角，凡是插入进来的数据，都是符合数据约束的。

      约束的最终目标：保证数据的完整性和可预期性 

      所以为了更好地达到这个目标，我们就需要有尽可能多的约束条件！！ 

        真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。 表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key 。

     为什么数据库要这么严格呢？？？——>因为数据库是我们维护用户数据的最后一道防线！！所以我们必须要保证数据库被插入的数据一定是具备完整性和可预期性的！ 

一、非空约束（强制用户设置）

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

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

 

案例： 创建一个班级表，包含班级名和班级所在的教室。 站在正常的业务逻辑中：

（1）如果班级没有名字，你不知道你在哪个班级

（2）如果教室名字可以为空，就不知道在哪上课  

        所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“约束”。  

     class_name和claas_room 不能为空，也就是说插入的时候如果你没插入的话就会被拦截，而other可以为空，你插入或者不插入都可以，不会拦截 

       对于不能为空的数据，你没插入的话他会提示你没有合适和缺省值，如果你插入空，他会告诉你不能为空。这就是非空约束！！

二、default约束（允许用户忽略）

   默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，用户将来插入，有具体的数据，就用用户的，没有就用默认的

      使用案例：假设这是一个程序员相亲网站，姓名必须有非空约束，然后年龄和性别我们可以设置缺省默认值

 因为年龄和性别并不存在非空约束，且设置了默认值，所以我们不需要指明也可以插入

问题1：如果有了default了，按理说就一定有数据了，那设置not null还有意义吗？？？

 

         以上我们会发现如果给gender设置默认值和非空约束的列  设置null的时候，非空约束会起作用，而当我们忽略这个列的时候，默认值会起作用，因此我们可以得出下面这个结论： 

       所以上面name和gender同样都是非空约束，但是前者因为没有默认值所以用户一旦忽略就会报错，也就是用户必须得插入，而后者因为有默认值所以允许用户忽略！！

       语法上default约束和非空约束同时存在在语法上是支持的，但是实际应用场景中较少出现 ，因为defalut本身有默认值，不会为空，而如果我们设置了not null本身的目的就是希望用户显示地输入

问题2：如果我们建表的时候没有带约束，那么他会自动带上defalute null  

 

三、列描述（描述字段  软约束）

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

 

通过show可以看到：  

通过desc查看不到注释信息：

四、zerofill（格式化显示  软约束）

刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。

 

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

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

案例：

一开始没有这个zerofill

我们加上zerofill后发现这次可以看到b的值由原来的2变成0000000002，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是10），自动填充0。

这一般用于什么场景呢？比方说我们有100的班级我希望从001 002开始，也就是每个编号都是三位，那么我们就可以使用zerofill。 

       要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是2。为什么是这样呢？我们可以 用hex函数来证明。 

注意：如果你不够了，他会给你前面补0，但是如果你够了，你该怎样就是怎样，所以zerofill补0是一种至少的行为。  

当然实际该怎么存还是怎么存，zerofill并不影响实际存储，只是影响后续的格式化输出而已。

 问题：为什么有符号int默认是11，无符号int默认是10？？？

 ——>因为整形是4个字节，int最大就是2^31次方-1 大概是21亿多，unsigned int最大是2^32次方-1  大概是42亿多，最多就是10位数，只不过int可能是负数所以多了一位表示符号位！！

五、主键（强唯一性+非空约束）

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键(1、确保可以根据主键可以拿到唯一的一条记录 即唯一性的甄别2、并不意味着一个表中的主键只能添加给一列（多列的情况我们叫他复合主键）！)；主键所在的列通常是整数类型。  

案例：

创建表的时候直接在字段上指定主键  

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

 当表创建好以后但是没有主键的时候，可以再次追加主键 alter table 表名 add primary key(字段列表)   （你要把该列设置为主键之前必须确保该列没有重复，否则mysql会拦截  所以一般来讲我们都是建议在建表的时候就把主键考虑好 不要等用了一段时间后才加主键）

 

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

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

primary key(id, course) -- id和course为复合主键 

       我们允许不同的一个同学选同一门课，但是不允许一个同学多次选同一门课（两个数据不可同时冲突）

六、auto_increment（自增长+搭配主键+允许表外设置）

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

自增长的特点:

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

（2）自增长字段必须是整数

（3）一张表最多只能有一个自增长

 使用案例：

如果我们没设置id的话，id会自动帮我们插入，然后自增。 一开始默认是1

 如果我们新插入一个id，就会以我们新插入的为主

在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）

 我怎么知道到底下一次该从哪一个数字开始自增呢？？——>在auto_increment不仅可以在表内是设置，也是可以在表外被设置的,他会表示下次插入时的起始值。如果没设置就是从1开始

应用场景：qq账号，我们申请qq账号的时候用手机号申请他会通过后台给我们返回一个账号。 在数据库后台他是自增的。

获取最后一次插入的值：select last_insert_id(); 

 

索引（类似目录，一种加速查找的策略    往往和主键有关系）：

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

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

       数据表很大的时候，利用一部分空间来存索引（内容和数据页码），加速我们对表数据的查找，达到空间换时间的目的 

七、唯一键（确保非主键的唯一性）

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

我们会发现唯一键的本质和主键差不多，但是技术上可以被设置为null，且多个null值不会冲突，空字段不做唯一性比较！！

问题:说说对主键和唯一键的理解？？

1、我们身上是有很多属性值的，比如姓名、性别、学号、邮箱……任何一种人事物都有自身特定的一些属性，而我们建表的本质就是通过这个表来描述这个对象，表有一个个列，一个个列就是一个个属性，列的内容就是属性的值，所以表就相当于我们C语言的结构体、C++的类用来描述一个现实的人事物。

2、主键为了保证记录的唯一性，而我们通常选择主键和设置主键一般有两种做法，第一种是从我身上众多拥有唯一值的属性当中选择一部分列来充当，第二种做法就是选择一个和业务无关的列来充当主键，比如auto_increment这样的值。

3、以第一种为例 ，我们众多的属性里有相当一部分虽然没有被选择为主键，但是依旧需要唯一性保证，你这列成为主键仅仅只是因为你被选择了主键仅此而已。所以我们并不能因为有主键了就不需要唯一键。而是因为你选择了这一列为主键，而其他一部分列可能也是需要维护唯一性的，所以主键和唯一键并不是互相冲突的，反而是相互补充的，双方一起对表做出更严格的约束，防止用户在上层做出一些误操作！

举例：学号作为主键，但是电话号码也应该有唯一性，如果粗心的话可能就导致不同的人共用一个相同的手机号码，这是不符合业务逻辑的！！

 4、主键和唯一键区别是，从技术角度是一个不能为空，一个可以为空，但是更大的区别在业务逻辑角度，主键通常用来标明某一行记录本身在整表的唯一性，而唯一键的侧重点为了让自己插入的这个列值和其他的列值不要有冲突，保证在业务上该字段的唯一性。

5、唯一键并不能保证一定不犯错（比如我号码写错了，但是这个号码原先并没有人使用，或者已经被注销了，就比如我们生活当中的打过去是空号，这是不可避免的），但是至少从技术角度可以确保插入到表中的数据是不一样的 

6、如果我们给唯一键设置了非空约束，那么他和主键的功能可以说是一样的了，但是从使用逻辑上和主键的侧重点是不一样的，他是为了保证表内列信息不要发生冲突

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

八、外键约束（利用一个列属性和其他表产生关联）

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

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

     学生表叫做从表，班级表叫做主表，因为学生表要依附于班级表，他需要通过class_id和班级表产生关联，此时class_id就称作为外键  主表需要给其他从表提供可以和自身产生关联的外键约束（一般都是主键或者唯一键）

插入2个班级

不断有学生来报到

 

情况1：但是此时在登记信息的时候粗心了，不小心插入了一个3号（在班级表并不存在的id）   

情况2：有三个同学都是1号班级的，但是班级表不小心把1号班级给删了

        根据上面两种不合理的情况，虽然上层程序员可以做到比方说新来一个学生要先查一下班级表是否有对应的班级，想删除一个班级的时候要先查一下学生表是否没有学生属于1号班级了，但是这样就很麻烦了，因为我们每次插入还得查表，出现这种问题的根本原因是上述两个表看起来是有外键之名，但是没有外键之实，实际上两个表各自维护各自的信息，两张表在业务上是有相关性的，但是在业务上没有建立约束关系！所以我们的解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关 系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。  

这次我们重新建立一个从表 

      这时候就建立了外键约束，如果学生表插入了不存在的班级id，或者班级表删除了有关联学生的班级id，mysql都会拦截这种行为。

外键总结：

1、从表和主表的关联关系

2、产生外键约束

目标——>确保表和表之间的完整性（比如有学生（从表）对应班级（主表），不会在学生表插入一个不存在班级，也不会在班级表删除一个还有学生的班级）

九、综合案例的练习

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

1、商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)

2、客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)

3、购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

要求：

1、每个表的主外键

2、客户的姓名不能为空值

3、邮箱不能重复

4、客户的性别(男，女)

-- 创建数据库

create database if not exists mall default character set utf8 ;

-- 选择数据库

use mall;

-- 创建数据库表

-- 商品

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)

);