【MySQL系列】表约束的学习

一、MySQL表的约束

• 数据类型也可以进行约束插入的值如果插入的数据超出了对应数据类型的取值范围那么数据将会插入失败。
• 但是数据类型的约束很单一为了更好的保证数据的合法性从业务逻辑角度保证数据的正确性MySQL中出现了表的约束。
• 目的就是为了尽可能保证数据合法性数据是符合预期的。表的约束是通过技术手段倒逼插入数据的人插入的数据是合法的还有一个就是减少用户的误操作可能性。
• 反过来站在MySQL的视角对于成功插入数据库的数据一定是合法的。
• 约束的目的保证数据的完整性和可预期性
• 表的约束很多这里主要介绍如下几个 null/not null,default, comment, zerofillprimary keyauto_incrementunique key

注意MySQL的命令对大小写不敏感的即不区分大小写。

1.1 空属性

• 空属性有两个值分别是null空和not null不为空
• 数据库默认字段基本都是字段为空但是实际开发时尽可能保证字段不为空因为数据为空没办 法参与运算

空值是无法参与运算例如

注意null代表的是不存在不是C语言、C++里面的NULL注意区别。

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

mysql> create table if not exists class(
    -> class_name varchar(20),
    -> class_room varchar(10));

查看表的属性这些类型字段默认是null

尝试插入数据是允许插入为空的数据的

站在正常的业务逻辑中

• 如果班级没有名字你不知道你在哪个班级
• 如果教室名字可以为空就不知道在哪上课

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

如果要让某个字段不允许为空在创建表的时候就可以给对应字段设置not null属性。

依旧是创建一个班级表包含班级名和班级所在的教室但是这两个属性不允许为空

mysql> create table if not exists class2(
    -> class_name varchar(20) not null,
    -> class_room varchar(10) not null);

创建表完毕后查看表结构可以看到这两个字段是不允许为空的。

查看表创建时的细节

向表中插入数据时只有这两个字段都不为空时才能插入成功否则将会插入失败。

1.2 默认值default

• 如果某一个字段会经常性的出现某个值那么就可以考虑将这个值设置成该字段的默认值。
• 向表中插入数据时如果不给带有默认值的字段赋值那么就会使用默认值进行插入。

比如创建一个表表当中包含用户的姓名、年龄和性别年龄和性别分别设置为18男

mysql> create table if not exists t1(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> sex char(2) default '男');

创建之后查看表是属性

向表中插入数据时如果不指明用户的年龄或性别那么就会使用对应的默认值缺省值

还有一种就是一个字段同时设置not null和default两个值

• 一旦给某一字段设置了默认值那么该字段将不会出现空值因为就算插入数据时没有指明该字段的值也会使用该字段的默认值进行填充。
• 而给某一字段设置not null属性的目的是约束该字段不能为空因此一个字段设置了default属性后再设置not null属性就没有意义了。

1.3 列描述comment

列描述comment没有实际含义专门用来描述字段会根据表创建语句保存用来给程序员或DBA来进行了解相当于C语言、C++的注释。

比如创建一个表表当中包含用户名、用户的年龄和用户的性别在每一个字段后面添加上对应的列描述。

mysql> create table if not exists t2(
    -> name varchar(20) not null comment '这是用户的名字',
    -> age tinyint unsigned default 18 comment '这是用户的年龄',
    -> sex char(1) default '男' comment '这是用户的性别');

创建之后查看表创建时的详细信息(通过desc查看不到注释信息)

show create table t2;

注意MySQL使用单引号也可以代表字符串与C语言、C++的有区别

1.4 zerofill

数值类型后面的圆括号中的数字代表的是显示宽度对应数值类型设置zerofill属性后如果数据的宽度小于设定的宽度则自动填充0

例如创建一个表表当中包含a和b两列整型数据将它们的显示宽度都设置成5但是没有设置zerofill属性

mysql> create table if not exists t3(
    -> a int(5),
    -> b int(5));

向表中插入一条记录正常显示

修改表结构给a列添加上zerofill属性由于a列数据的显示宽度为5因此查看表中数据可以看到a列数据中宽度不足5位的数据都自动在前面填充0

要注意的是这只是最后显示的结果在MySQL中实际存储的还是100001只是设置了zerofill属性后的一种格式化输出而已。

简单说一下索引后序详谈

• 索引在关系数据库中索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
• 索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。
• 索引提供指向存储在表的指定列中的数据值的指针然后根据指定的排序顺序对这些指针排序。
• 数据库使用索引以找到特定值然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快可快速访问数据库表中的特定信息。

1.5 主键primary key

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

比如创建一个学生表表当中包含学生的学号和姓名由于学生的学号是不会重复的因此可以将其设置成主键

mysql> create table if not exists student(
    -> stu_id int unsigned primary key comment '学生ID',
    -> stu_name varchar(20) not null comment '学生姓名');

创建表成功后查看表结构可以看到id对应的Key列出现了PRIPRI就是主键。

查看表的创建详细信息主键也可以这样设置图中圈起来的

进行插入插入表中的记录的主键字段不能重复如果插入记录的主键与表中已有记录的主键重复这时就会因为主键冲突而插入失败

当表创建好以后但是没有主键的时候可以再次追加主键

alter table 表名 add primary key(字段列表)

需要注意只有列当中的值不为空并且不重复的列才能被设置成主键如果列中已经有了重复的的值则设置主键失败。这也说明要先把表结构设计好不然后面插入有数据很难进行操作

删除主键

alter table 表名 drop primary key;

复合主键

• 一张表里面只能有一个主键但一个主键可以由多个字段来承担这种主键叫做复合主键。
• 复合主键用来唯一约束多个字段里面的数据表当中每条记录的这多个字段不能同时重复也不能为空。

比如创建一个成绩表表当中包含学生的ID课程ID成绩。学生ID和课程ID可以同时设置为主键因为这两个字段不能同时重复也不能为空。

mysql> create table if not exists t5(
    -> stu_id int unsigned,
    -> course char(10) comment '课程ID',
    -> score tinyint unsigned default 0 comment '成绩',
    -> primary key(stu_id, course) -- id和course为复合主键
    -> );

注-- 后面也是注释

查看表信息可以看到两列都是PRI即主键并且它们都是不允许为空的

向表中插入数据时只有插入的学生ID和课程ID同时出现冲突时才会产生主键冲突否则就允许插入。

只有两个同时冲突时才会产生主键冲突有点像C语言的&&条件

1.6 自增长auto_increment

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

自增长的特点

• 任何一个字段要做自增长前提是本身是一个索引key一栏有值
• 自增长字段必须是整数
• 一张表最多只能有一个自增长

比如创建一个表表当中包含id和name将id同时设置成主键和自增长字段

mysql>  create table if not exists t6(
    -> id int unsigned auto_increment,
    -> name varchar(20) not null default '',
    -> primary key(id)
    -> );

创建表完毕后查看表结构可以看到id的Extra列中出现了auto_increment标志

向表中插入第一条记录时如果没有指明自增长字段的值那么自增长字段的值默认将会从1开始。id不给值会自动的被系统触发系统会从当前字段中已经有的最大值+1操作得到一个新的不同的值

后续向表中插入记录时如果也不指明自增长字段的值那么自增长字段的值就会依次递增。如下

插入数据的时候也可以指明自增长字段的值此时将会使用该值进行插入但注意指明的值不能和表中已有的id值重复。

继续插入会i从d列中找出最大值会将最大值加一后得到的值作为自增长字段的值进行插入

这个自增长在表外就有设置查看表创建的详细信息就可以看到这个值就是作为下一次自增长的值。如果想指定自增长的开始值可以在表外面进行修改这个值。

输入select last_insert_id();可以查看当前值的上一个即表中的最大一个值

1.7 唯一键unique

• 一张表中往往有很多字段需要唯一性但一张表中只能有一个主键而唯一键就可以解决表中有多个字段需要唯一性约束的问题。
• 唯一键和主键都能保证字段中数据的唯一性但唯一键允许字段为空并且可以多个字段为空空字段不做唯一性比较。
• 需要注意的是某一字段被选择成为了主键仅仅是因为这个字段被选择成为主键除了主键之外仍有其他需要标识唯一性的字段这些字段就可以被设置成唯一键。
• 主键和唯一键是互相补充的关系

比如创建一个学生表表当中包含学生的id、姓名和电话号码将我们选择id作为主键但同时每个学生的电话号码也应该具有唯一性约束因此应该将电话号码设置成唯一键。

mysql> create table if not exists t7(
    -> stu_id int unsigned primary key auto_increment,
    -> name varchar(20) not null,
    -> tel varchar(20) unique comment '唯一键'
    -> );

表创建完毕后查看表结构可以看到tel的Key列出现了UNI标志这就表明tel已经成功被设置成唯一键了。

查看表创建的详细信息

向表中插入数据时如果插入记录中的电话号码与表中已有记录的电话号码出现重复那么就会因为唯一键冲突而插入失败还有唯一键是可以允许为空。

1.8 外键foreign key

• 外键用来定义主表和从表之间的关系外键约束主要定义在从表上主表必须有主键约束或唯一键约束。
• 外键定义后要求插入外键列的数据必须在主表对应的列存在或为null。
• 外键是用于表和表之间的关联表和表之间的约束。

语法

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

比如先创建一个班级表作为主表表当中包含班级的id和班级名并将班级id设置为主键。

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

创建成功后查看表的详细信息

再创建一个学生表表当中包含学生的id、姓名以及学生所在班级对应的id学生ID设置为主键班级ID设置为外键

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

表创建成功后查看表的详细信息学生表中的班级id对应的Key列出现了MUL这表明class_id已经成功被设置成了外键。

向班级表插入两条数据

向学生表中插入记录时如果插入的记录对应的班级id是班级表中存在的或者插入的班级id为null那么此时是允许进行插入的。

但是如果插入学生表的记录对应的班级id是不存在此时将会插入失败这就是外键约束

这里的班级表就是主表学生表就是从表

在查看学生表创建时的详细信息时图中圈起来的这个字段是给外键约束起名字不过一般都不怎么使用默认情况下外键有自己的名字圈起来引号的内容。

--------------------- END ----------------------

「 作者 」 枫叶先生
「 更新 」 2023.8.6
「 声明 」 余之才疏学浅故所撰文疏漏难免
          或有谬误或不准确之处敬请读者批评指正。