MySQL之表的约束(下)

自增长

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

自增长的特点：

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

2. 自增长字段必须是整数

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

 建表

mysql> create table if not exists t7(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null
    -> );
查看创表的完整语句：show create table t7\G

查表：desc t7;
可以看到id列是主键和自增

插入 

insert into t7 (name) values ('a');

insert into t7 (name) values ('b');

insert into t7 (name) values ('c');//连续三个插入

发现id会自动加一(默认从1开始)(我们没有对id列进行插入)，就是我们不用管他，自动解决冲突问题

 当我们再次插入insert into t7 (id,name) values (1000,'e');//发现能插入

当我们再次插入一样的，发现不能插入(履行主键职责)
那么我们仅对name列插入时，是从3开始自增还是1000开始自增呢？

insert into t7 (name) values ('e');

发现是从1000开始自增；

又为什么MySQL是怎么知道从1000开始自增呢？

我们可以查创表看到auto_increment=1002,代表下次自增起始值1002(代表创表后自定义起始值了)

 自增长值

当然我们也可以在创表的时候自定义自增起始值 ：
mysql> create table if not exists t8(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null
    -> )auto_increment=500;

进行插入检测：

insert into t8 (name) values ('e');
insert into t8 (name) values ('a');

insert into t8 (name) values ('v');

说明就是从500开始设定的值开始的；

我们也可以用last_insert_id()函数查找上次的自增值：

last_insert_id()函数用于检索上次插入操作生成的自增ID。

查找下次插入的其实自增值：

 带auto_increment必须当前列有primary key否则不成功 ；

 索引：

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

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

唯一键

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

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

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

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

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

而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。

具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯 一键。

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

建表

mysql> create table if not exists stu(
    -> id char(20) unique comment '这是一个学生的唯一键',
    -> name varchar(32) not null
    -> );

//写成unique或者unique key都行

在id列出现唯一键

 插入

insert into stu (id,name) values ('1234','张三');

再次插入：insert into stu (id,name) values ('1234','李四');

报错：ERROR 1062 (23000): Duplicate entry '1234' for key 'id'//唯一键冲突，位置在id这一列

而我们插入：insert into stu (id,name) values (NULL,'李四');//不冲突

我们在多次插入相同的insert into stu (id,name) values (NULL,'李四');//发现多次NULL不冲突，这就是唯一键和主键的区别。

 他俩(主键和唯一键)都表示唯一性，但是他俩不冲突而是相互补充的；主键是默认带not null约束所以不能是NULL；所以MySQL只能技术上防止冲突，不能防止人为错误；唯一键加not null技术上相当于主键；

 外键

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

语法： foreign key (字段名) references 主表(列)

案例：我们用学生表和班级表创建； 

建表 

从表：学生表

mysql> create table if not exists student(
    -> id int unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> tel varchar(32) not null,
    -> class_id int
    -> );

 

主表： 班级表

mysql> create table if not exists class(
    -> id int primary key,
    -> name varchar(20) not null
    -> );

 插入

向班级中插入班级信息：

insert into class values (1,'教学1');

insert into class values (2,'教学2');

向学生表中插入信息：

 insert into student (name,tel,class_id) values ('张三','123456',1);

insert into student (name,tel,class_id) values ('李四','123456',1);

insert into student (name,tel,class_id) values ('王五','123456',1);

可以看到插入了三个学生信息每个人都有自己的class_id

继续插入：insert into student (name,tel,class_id) values ('田七','123456',3);

发现我插入的学生表中没有对应的班级表信息，那么如果我删除班级表的某个信息，导致学生表没有对应的班级信息。这就是只有外键之名(关联关系)并没有外键之实(没有约束)！

 关联建表

 让从表的class_id与主表的id产生关联

删表：drop table student;

重新建表：

mysql> create table if not exists student(

id int unsigned primary key,

name varchar(20) not null,

tel varchar(32) unique key,

class_id int,

foreign key(class_id) references class(id)//先建立主表后从表

);//指明外键列与主表特定列产生外键约束关系

 插入：

insert into student  values (1,'张三','123456',1);

insert into student  values (2,'李四','1234567',2);

//插入成功

错误插入：insert into student  values (3,'王五','12345678',3);

报错：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))//发生外键约束，因为在class中并不存在id=3的值

删除：

删除：delete from class where id=1;

报错：ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))//发生外键约束，不能删除因为他被从表关联着尤其从表中有数据关联着；
删除：那么我们可以先删除从表的数据，再删主表的数据这样就不会报错

 首先我们承认，这个世界是数据很多都是相关性的。

理论上，上面的例子，我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。

此时，在实际使用的时候，可能会出现什么问题？

有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？

比如学校只开了1班，2班，但是在上课的学生里面竟然有3班的学生(但是实际上这个班目前并不存在)，这很明显是有问题的。

但是因为此时两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。

解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql 表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，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 good;

使用数据库：mysql> use good;

创表(商品)：

mysql> 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 '供应商名称'
    -> );
创表(客户):

mysql> 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 comment '电子邮箱',
    ->     sex enum('男', '女') not null comment '性别',
    ->     card_id char(18) unique comment '身份证'
    -> );

创表(购买)：

mysql> 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)
    -> );