【MySQL】表的约束

【MySQL】表的约束

一、表的约束字段

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的完整性和可预期性。比如有一个字段是name，要求是唯一的。表的约束很多，这里主要介绍如下几个：

null/not null,default, comment, zerofill，primarykey，auto_increment，unique key

二、空属性—null

null（默认的）和not null(不为空)
数据库默认字段基本都是字段为空，但是实际开发时，可能字段不为空

这里以一个班级信息表为例：班级名称和班级位置必须不为空

mysql>createtableifnotexists class(-> class_name varchar(32)notnull,-> class_add varchar(32)notnull,-> other varchar(32)->);

通过查看表结构和查看优化后的建表信息发现，name，addNull字段被设置为No;other没有指明not null,所以Null字段还是yes

插入数据再次分析：not null约束限制了该列插入信息不能为空

三、default—默认值

默认值：如果指明了默认值，数据在插入的时候不给该字段赋值，就使用默认值或者指明数据使用此数据插入

建立表t1；name字段约束不能为空，age和gender字段都设置默认值

mysql>createtableifnotexists t1(-> name varchar(32)notnull,-> age tinyintunsigneddefault18,-> gender char(1)default'男'->);

查看表结构和建表信息：default字段，没有指明就是NULL，指明了就是默认值


插入部分信息测试

3.1 null与default联系

创建表t2,与t1相似，但是gender字段即设置了not null也设置了default

mysql>createtableifnotexists t2(-> name varchar(32)notnull,-> age tinyintunsigneddefault18,-> gender char(1)notnulldefault'男'->);

查看表结构及建表信息

插入数据测试：

3.2 总结

1. not null和default是不冲突的，而是互补的
2. 当用户不使用默认值使用自己的数据插入时，not null起作用（null，合法数据）
3. default：用户使用了默认值（忽略此列，并且该列设置了默认值）；没有设置此列默认值，并且忽略了，直接报错
4. 当某一列没有设置not null，MySQL会自动将此列默认值设置为空DEFAULT NULL

四、列描述—comment

列描述：comment，没有实际含义，专门用来描述字段，即注释作用

创建表t3并添加约束comment

mysql>createtableifnotexists t3(-> name varchar(20)notnullcomment'姓名',-> age char(1)notnullcomment'年龄'->);

查看表结构看不到注释信息

查看建表信息可以看到：comment越是是帮助数据库管理人员管理数据库的约束

五、zerofill

zerofill：对表中数据进行格式化显示

创建表t4并查看表结构和建表信息

修改表中字段属性，增加zerofill约束，可以发现num2字段下数据被格式化显示，以int(10)为例
将对应数据占10个位置，不够的补0【没有zerofill这个属性，括号内的数字是毫无意义的】

数据库中实际存储的值还是1，显示给我们看的是格式化后的数据

再次修改字段num2属性，将int改为int(3)

结论：当插入的数据小于填充的位数的时候，会在前面补0；当插入的数据位数大于填充位数，就是该数据，不会被修改

修改表结构，num1为有符号整数，num2为无符号整数；通过查看建表信息发现，num1 int(11),num2 int(10)

这是因为无符号整数0~42亿多，有符号整数是-21亿多到21亿多，那么他们最多占十位；而有符号数要多一个符号位，所以是11位

六、主键—primary key

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

创建表primary_t将其id设置为主键

mysql>createtableifnotexists primary_t(-> id intprimarykeycomment'学号',-> name varchar(20)notnull->);

查看表结构发现，Key字段有PRI主键约束

插入与主键相同的的值，报错

删除表中主键与增加表中主键

altertable 表名 dropprimarykey;altertable 表名 addprimarykey(字段列表)

6.1 复合主键

当一个字段无法满足唯一性要求的时候，可以采用多个字段组成一个主键（复合主键）

mysql>createtableifnotexists stu(-> id intunsigned,-> course intunsignedcomment'课程号',-> score tinyintunsignedcomment'成绩',->primarykey(id,course)->);

存在任意不相同字段可以插入

七、自增长—auto_increment

auto_increment：通常和主键搭配使用，作为逻辑主键。当表中某个字段被设置为自增长，如果插入时忽略此字段，默认从1开始每次+1；如果中途设置了，那就从最大值+1开始
自增长的特点:

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

创建表atimt，id字段设置为主键并添加自增属性

mysql>createtableifnotexists atimt(-> id intunsignedprimarykeyauto_increment,-> name varchar(10)notnull->);

查看建表信息和表结构

插入数据观察特性
忽略自增字段的时候，默认从1开始自增

指明插入的id值，判断是否是最大值，以后都会从最大值+1开始自增

建表的时候设置自增值

createtableifnotexists tt( id intunsignedprimarykeyauto_increment, name varchar(10)notnull)auto_increment=100；

八、唯一键----unique

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

关于唯一键和主键的区别
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。

创建表student并设置id为主键，tel为唯一键

mysql>createtableifnotexists student(-> id varchar(10)notnullprimarykeycomment'主键id',-> name varchar(20)notnull,-> tel varchar(11)uniquecomment'唯一键'->);

上图可以发现，对于唯一键，NULL时可以插入。

引入唯一键是因为，我们选择某一列作为主键，但是并不排除其他属性不需要维护，可能需要用到唯一键来限定，选择其他列作为唯一键，与主键配合起来，能够保证一些数据冲突。

九、外键----foreign key…reference

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

使用语法

foreignkey(字段名)references 主表(列)

创建User表

mysql>createtableifnotexistsUser(-> id intunsignedprimarykeyauto_increment,-> name varchar(10)notnull,-> telphone varchar(11)notnull,-> class_id int->);

创建表Class

mysql>createtableifnotexists Class(-> id intprimarykey,-> name varchar(32)notnull->);

这种情况下如果User表插入的class_id在Class表id中不存在，不会导致报错，因此需要为User->class_id和Class->id形成关联关系即外键约束，实现插入学生信息的班级必须存在。不存在就不能插入

User表为从表，Class表是主表，class_id就是外键

输入添加外键约束语句

mysql>altertableUseraddconstraintforeignkey(class_id)references Class(id);

查看表结构及建表信息

上述红框内第一个字符串CONSTRAINT代表约束名字，可有可无

十、综合案例

有一个商店的数据，记录客户及购物情况，有以下三个表组成：
商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)
要求：
每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(男，女)

-- 创建数据库createdatabaseifnotexists bit32mall
defaultcharacterset utf8 ;-- 选择数据库use bit32mall;-- 创建数据库表-- 商品createtableifnotexists goods
(
goods_id intprimarykeyauto_incrementcomment'商品编号',
goods_name varchar(32)notnullcomment'商品名称',
unitprice decimal(10,4)notnulldefault0.0comment'单价，单位分',
category varchar(12)comment'商品分类',
provider varchar(64)notnullcomment'供应商名称');-- 客户createtableifnotexists customer
(
customer_id intprimarykeyauto_incrementcomment'客户编号',
name varchar(32)notnullcomment'客户姓名',
address varchar(256)comment'客户地址',
email varchar(64)uniquekeycomment'电子邮箱',
sex enum('男','女')notnullcomment'性别',
card_id char(18)uniquekeycomment'身份证');-- 购买createtableifnotexists purchase
(
order_id intprimarykeyauto_incrementcomment'订单号',
customer_id intcomment'客户编号',
goods_id intcomment'商品编号',
nums intdefault0comment'购买数量',foreignkey(customer_id)references customer(customer_id),foreignkey(goods_id)references goods(goods_id));