一、表的约束
MySQL数据库是有唯一性约束的,真正对表的字段进行约束的是字段类型,比如我们字段类型规定的取值范围是多少,如果我们插入的数据超出了这个范围,MySQL是不会让我们插入的。这里和高级程序语言不一样,我们以前在使用C/C++的时候,如果定义的整型超出了范围,它会自动帮我们截断,但是MySQL没有截断这一说,只要超出了范围,就是不允许插入,这样可以保证数据是一定正确的。
但是字段类型的约束还是比较单一的,还需要一些额外的约束,从而更好地保证数据的合法性,从业务逻辑保证数据的正确性。比如说我们要插入一个email邮箱号字段,要保证该字段插入的数据是唯一的,就需要使用到数据库的约束了。数据库表的约束有很多,这篇文章主要介绍以下几种约束:空属性、默认值、列描述、zerofill、主键、自增长、唯一键、外键。
二、空属性
数据库的大部分字段在定义的时候默认都是空的,但在实际开发中,尽可能保证字段不为空,因为如果数据为空的话就没办法参与计算了。举一个现实场景的假设,假设现在我们要做一张学校的班级表,表中有班级编号,班级教室,如果班级编号可以为空,难么我们可能会无法得知自己在哪个班级。如果班级教室可以为空,那么我们也可能无法得知自己在哪个教室上课。所以在设置的时候,我们叫将这两个字段设置为not null
,即不允许为空。
此时我们再向表中插入数据,其中班级编号和班级教室这两个字段都不为空,如果我们正常插入就没任何问题,如果我们不插入或者插入空,就无法插入数据:
三、默认值
某些数据可能会经常出现某个具体的值,我们可以在一开始建表的时候就给这类数据一个默认值,如果用户填入了该字段的值就使用用户输入的,如果没有则使用默认值。
比如我们可以建一张表,其中age年龄字段设置默认值为18,name姓名字段不设置默认值,并且年龄字段和姓名字段都不能为空:
此时我们再向表中插入数据,如果指定了age的值,则使用我们指定的值,如果没有指定,则会使用默认值。但是name必须指定,因为name的默认值是NULL,如果没有指定会使用默认的NULL,但是name字段我们设置了是不能为空的,所以使用默认值NULL会出错,因此必须指定name的值。
四、列描述
在定义字段时我们可以使用comment来描述这一列,列描述没有实际含义,它的意义是说明,专门用来描述字段,会根据表的创建语句保存,用来给别人快速了解该字段的含义是什么。
五、zerofill
我们先创建一张表,表中定义两个整数字段,分别是age年龄字段和sum总数字段,这两个字段都使用int类型,创建好之后我们使用desc
指令查看一下表结构:
我们发现定义的两个字段类型是int,但是类型int后面括号的数字11是什么含义呢?其实这个数字就与zerofill属性有关。如果没有设置zerofill属性,这个数字其实并没有什么实际的含义,我们可以向表中随便插入一些数据查看一下表的内容:
但是如果我们修改一下age列属性,输入指令alter table t12 modify age int(5) zerofill;
,再来查看表中的内容,就会发现表中的数据依旧没变,但是age列的数据被自动补0了:
这就是zerofill属性的作用,如果数据的宽度小于设定的宽度,比如上面修改之后age字段设定的宽度是5,如果小于5的话,会将其他位自动补0。并且需要注意的是,这里的补0其实只是最后的显示结果,其实在MySQL中存储的实际还是18或者19(就上表数据而言),也就是说如果我们查找一下age值为18的数据还是可以找得到的,而不是需要通过00018才能找到:
六、主键
1.primary key
主键是用来约束该字段里数据的唯一性的,比如说当前有一张表,表中的id字段被设置为主键,那么在后续向id这一列插入数据时,不可以插入列中已经存在的数据,也就是说该列数据不能出现重复。并且被设置为主键的字段默认不能为空。一张表最多只能有一个主键。
我们可以建一张表演示一下主键的使用,建一张学生表,将表中的id学号字段设置为主键,意思是学生的学号不能为空并且不能出现重复:
建好表之后,我们尝试向表中插入数据,当id列插入不重复的数据时,都可以插入成功。但当id列插入一个当前已经存在的数据时,就会插入失败了,原因是id列目前被设置为了主键:
如果我们想要删除主键,可以使用下面的语句,只需要提出想要删除主键的表名即可,不需要指定到哪一列,因为一张表中只允许有一个主键:
alter table 表名 drop primary key;
此时表中就没有主键了,如果我们又想要给表中的某一列添加主键,可以使用下面的语句:
alter table 表名 add primary key(字段名);
比如我们想给student表中的name列添加主键,输入语句alter table student add primary key(name);
,我们却发现添加失败了,原因是name列中的数据本身就已经存在重复的了,这个时候再添加主键就会失败:
2.复合主键
如果我们需要有多个字段作为主键,也可以使用复合主键。比如假设我们有这样的学生抢课场景,同一个学生可以选择多门课程,多个学生可以选择同一个课程,但是一个学生不能重复选择同一个课程,此时如果我们就可以使用复合主键了。复合主键在创建表的时候,在所有字段定义完之后,使用primary key(主键字段列表)来创建主键。
例如我们创建一张抢课的表,id字段代表学生的学号,course字段代表课程的编号,name字段代表学生的姓名,将id字段和course字段设置为复合主键:
接下来我们可以模拟一下这种场景:同一个学生选多门不同的课程、多个学生选同一门课程、同一个学生选同一个课程,我们会看到前两种情况是可以成功插入数据的,第三种情况就会插入失败:
七、自增长
当字段被设置为自增长时,如果我们在向表插入数据时没有给该字段数据,就会自动触发自增长,系统会将当前字段中已经存在的数据最大值加一,再新插入到该字段中。自增长有以下几个特点:
- 任何一个字段要做自增长,前提是本身就是一个索引。
- 自增长字段必须被设置为主键。
- 自增长字段的数据必须是整数。
- 一张表最多只有一个自增长。
我们可以建一张表演示一下自增长字段的使用,将表中的id字段设置为自增长:
接下来我们向表中插入数据,如果id列不给值,它会自增长:
此时我们可以输入语句show create table t2\G;
查看一下表的创建语句,我们发现在表的创建语句中,MySQL自动帮我们添加上了auto_increment=5这一语句,也就是说这里会记录下一个自增长的数据从哪里开始。
如果我们向表中插入数据时,向id列指定一个比下一个自增长的值还要大的值,结果是可以正常插入,但是当我们查看表的创建语句时,下一次自增长的值就变成了当前最大值加一:
同理可得,当我们插入一个比最大值要小的数据时,是可以成功插入的,但下一次自增长的值是不会变的。因为自增长的值永远都是当前列中数据最大值加一。
八、唯一键
在现实生活中唯一性的属性并不是只有一个,一张表中往往有很多个字段需要唯一性,数据不能出现重复,比如说我们在做一张学生信息的表时,学生的学号是必须唯一的,学生的身份号也必须是唯一的,但是一张表只能有一个主键,所以唯一键就可以解决表中有多个字段需要唯一性约束的问题。
举个例子,我们建一张学生信息表,stu_id字段代表学生的学号,我们将其设置为主键并且是自增长的。name字段代表学生的姓名,该字段可以重复,因为确实有可能存在名字相同的学生。id字段代表学生的身份证号,该字段也是不允许出现重复的,所以我们将其设置为唯一键:
当我们向表中插入数据时,学号字段不可以出现重复,身份证号字段也不可以出现重复,如果出现了重复,就无法插入成功了:
九、外键
外键是用来定义主表和从表之间关系的属性,它一共有两个功能,第一个是关联两张表,第二个是对表中的数据进行约束。举个例子,假设我们要设计一张学生表和班级表,每个学生都对应自己的班级,如果我们把班级名称和学生都放在同一张表,就会出现数据冗余。所以将其分开为学生表和班级表,学生表中有班级编号字段,通过这个班级编号就可以在班级表中找到对应的班级,这样就将两张表关联起来了,其中班级编号就是关联两张表的外键。
但这只是语义上的关联,两张表之间还缺少约束。MySQL的外键不仅为我们提供了关联,还为我们提供了约束。如果没有这种约束的话,在学生表中插入数据时,如果插入了一个不存在的班级编号,也可以插入成功,但这就不符合现实场景了。比如说现实中只有一班和二班,但是在插入学生信息的时候不小心插入了三班,却也可以插入成功,这样就出错了。因此,外键的存在,确保了这种情况不会发生,因为外键会为我们做约束。
外键基本语法如下:
foreign key(字段名) references 主表(字段名)
外键是定义在从表上的,比如上述例子中学生表就是从表,主表必须是有主键约束或唯一键约束。当从表定义外键之后,要求从表中的外键列数据必须在主表中的主键列存在,或者为空。
我们创建这样一张学生表和班级表,演示一下外键的使用:
create table class_tb (
id int primary key,
name varchar(32) not null
);
create table stu_tb (
id bigint primary key auto_increment,
name varchar(20) not null,
class_id int,
foreign key (class_id) references class_tb (id)
);
接下来我们向表中分别插入数据,可以发现的是,如果学生表中插入的班级不存在,就会插入失败: