MySQL从入门到入土---MySQL表的约束 (内含实践)---详细版

引入：

null 与not null

default：

comment列描述：

not null 和 default：

zerofill ：

主键：primary key

复合主键：

自增长:auto_increment

唯一键：unique key

外键：

引入：

MySQL本质上是数据的最后一道防线，它为了保证自己内部存储的数据一定是符合预期的，会针对每次插入的数据做严格检查，虽然数据类型已经算是一种约束，但是还不够灵活和全面，所以Mysql也存在其他的约束，如：null/not null,default, comment, zerofill，primary 、key，auto_increment，unique key ...

主要介绍null/not null,default, comment, zerofill，primary 、key，auto_increment，unique key 这几种约束。

null 与not null

在实际工程中，大部分情况下一般默认的数据都不会是null，因为没有意义

#新建一张表t1用于测试null
mysql> create table t1(-> class_name varchar(32),-> class_room vaechar(32)-> );#插入测试数据
insert into t1(class_name) values('软件工程');

不插入class_room字段,查一下表中数据：

#查询
select* from t1;

查询结果：

很显然，在教室的数据库表中，class_room不应该出现null，class_name却存在的情况，如果不使用not null修饰，很又可能会发生上述情况，所以给字段属性设置not null后再查一次：

#重新建立表t1
mysql> create table t1(-> class_name varchar(32) not null,-> class_room varchar(32) not null-> );#插入上次测试的数据

显示MySQL不允许此次插入，即要求必须要插入所有数据，不能使用默认的null值。

default：

语法和上述的not nul类似，当字段设置了default约束时，如果插入数据时，该字段对应的数据为null，则默认使用default的值

测试demo：

#创建表用来测试
mysql> create table t2_default(-> class_room varchar(32) not null,-> age tinyint unsigned default 0-> );

insert时，只插入class_room字段：

insert into t2_default values('软件');

查一下：

发现结果确实符合预期，age使用了建表时的默认值；

测试一下字符串：

mysql> create table t3( name varchar(32) default '张三', age int default 0);

同样的insert一条数据：

mysql> insert into t3 values()-> ;

查一下：

varcahar同样可以使用default，其他类型同理；

comment列描述：

在创建表时，用来描述字段；

demo测试：

mysql> create table t4(-> age int comment '年龄',-> name varchar(32) comment '姓名'-> );

comment的描述无法使用desc语句查询到，可以使用如下语句查询：

mysql> show create table t4 \G

结果：

符合预期；

not null 和 default：

二者可以连用，但是注意：如果我们建立表的时候两种约束一起使用，例如not null default xxx;

如果我们不在该字段插入数据，只在其余字段插入。是允许插入的；但是如果只设置了not null，MySQL是不允许我们忽略该字段，而直接插入表的其他字段的；

zerofill ：

说人话：是MySQL的一种对数据的格式化显示的约束；

当表中字段带有zerofill约束时，数据类型后面的整数才有意义

demo样例，不带zerofill约束：

mysql> create table if not exists t6(-> num int(10) not null-> );

insert一条数据：

mysql> insert into t6 values(5);

查一下：

可以发现不带zerofill约束时，num字段后面带的数字好像并没有体现意义；

加上zerofill约束：

mysql> alter table t6 modify num int(10) zerofill;

再查一次：

得出结论：如果用户插入的数据合法，但是长度不满足创建表时定义int的显示长度，会补充前导0，直到长度 == 定义表时的int显示长度；当然，字段中一定要携带有zerofill约束，若没携带，则无意义；

补充：zerofill不会改变数据本身意义，只改变显示长度！！

主键：primary key

概念：

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

demo测试：

mysql> create table t7(-> id int not null primary key,-> name varchar(32)-> );

desc查一下：

insert几条数据测试结果：

mysql> insert into t7 values(1,'张三');
mysql> insert into t7 values(1,'李四');
mysql> insert into t7 values(1,'王五');
mysql> insert into t7 values(1,'小明');
mysql> insert into t7 values(1,'小美');

第一次成功插入，但是之后的插入全部被拒绝：

再测试不同的id数据插入：

mysql> insert into t7 values(2,'张三');
mysql> insert into t7 values(3,'张三');
mysql> insert into t7 values(4,'张三');

查一下：

符合主键的描述，也符合我们的预期；

删除主键：

mysql> alter table t7 drop primary key;

再查一下表结构：

复合主键：

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

作为主键，可以使用复合主键

直接来个demo测试：

mysql> create table t1(-> id int,-> name varchar(32),-> primary key(id,name)-> );

连续insert2次相同的数据：

mysql> insert into t1 values(1,'张三');

第一次显示成功，第二次拦截；

验证一点：复合主键不允许相同的列组合数据插入；

第二次demo测试：

mysql> insert into t1 values(2,'张三');
mysql> insert into t1 values(3,'张三');
mysql> insert into t1 values(1,'李四');

查一下：

3次均能插入，也就是说，只要组合列中有一列数据与历史已插入数据不同即可；

自增长:auto_increment

auto_increment

来个demo样例：

mysql> create table if not exists t2(-> id int primary key auto_increment comment '学生id',-> name varchar(32)-> );

insert几次数据：

mysql> insert into t2 values(null,'张三');
mysql> insert into t2 values(null,'李四');
mysql> insert into t2 values(null,'王五');

插入成功，并且id全都不重复，如图：

1.auto_increment默认从1开始自增；2.也可以从最近的一次插入的数据（没插入成功的数据一样记录自增）开始自增；3.也可以建表时手动设置初始值

来个demo测试，测试第二点：

mysql> insert into t2 values(100,'王五');
mysql> insert into t2 values(null,'王五');

查一下：

符合预期；

测试第三点：建表时可以指定auto_increment的初始值：

mysql> create table if not exists t3(-> id int primary key auto_increment,-> name varchar(32)-> )auto_increment=1000;

insert数据：

mysql> insert into t3 values(null,'张三');
mysql> insert into t3 values(null,'李四');

查一下：

符合预期；

唯一键：unique key

一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

来个demo测试:创建一个学生信息表，不允许姓名，电话号码和微信号重复;

mysql> create table student(-> id int unsigned primary key auto_increment comment '学生id',-> name varchar(20) not null unique,-> tel char(20) unique,-> wechat_id varchar(32) unique-> );

desc一下：

insert几次数据测试：

mysql> insert into student values(null,'张三','123456789','xxxxxx');
mysql> insert into student values(null,'李四','123456789','xxxxxx');
mysql> insert into student values(null,'李四','12345678910','xxxxxx');
mysql> insert into student values(null,'李四','12345678910','xxxxxxyyy');

第一次insert成功，第2，3次失败，第四次成功，查一下表：符合预期；

外键：

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

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

来个案例：需要一张学生表和一张班级表，其中多名学生可以隶属于同一个班级（或者待定），但是不能隶属于班级表中不存在的班级，要删除班级表中的某个班级时，要确保班级中没有学生存在；

测试：

#创建班级表
mysql> create table class(-> id int unsigned primary key auto_increment,-> name varchar(32)-> );#insert班级表数据
mysql> insert into class values(null,'软件工程001');
mysql> insert into class values(null,'软件工程002');#创建学生表
mysql> create table student(-> id int unsigned primary key auto_increment,-> name varchar(32) not null,-> class_id int unsigned,-> foreign key(class_id) references class(id)-> );#insertstudent的有效数据
mysql> insert into student values(null,'张三',1);
mysql> insert into student values(null,'李四',1);
mysql> insert into student values(null,'王五',1);
mysql> insert into student values(null,'赵六',2);
mysql> insert into student values(null,'小王',null);#测试无效数据
mysql> insert into student values(null,'田七',3);
mysql> insert into student values(null,'小明',0);

查一下表中结果：

当插入的学生class_id不在班级表的id中的，显示插入数据失败！

符合预期；

再测试能否删除主表的数据：

从表数据存在时：

#在主表（班级表）中新增一行信息
mysql> insert into class values(null,'通信工程001');
#此时从表中没有数据是class_id = 3的，我们尝试删除这一行数据
mysql> delete from class where id=3;#测试从表中有数据使用主表的id时能否直接删除
mysql> delete from class where id=1;
mysql> delete from class where id=2;

第一次测试时，可以直接删除，因为主表的id=3在从表中并未有数据使用！

第二次测试时，无法直接删除，因为从表中有数据使用了主表的id：