【MySQL】表的约束-Toy模板网

这篇具有很好参考价值的文章主要介绍了【MySQL】表的约束。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、表的约束字段

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

二、空属性—null

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

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

mysql> create table if not exists class(
    -> class_name varchar(32) not null,
    -> class_add varchar(32) not null,
    -> other varchar(32)
    -> );

通过查看表结构和查看优化后的建表信息发现，name，addNull字段被设置为No;other没有指明not null,所以Null字段还是yes
【MySQL】表的约束
插入数据再次分析：not null约束限制了该列插入信息不能为空

三、default—默认值

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

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

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

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

插入部分信息测试

3.1 null与default联系

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

mysql> create table if not exists t2(
    -> name varchar(32) not null,
    -> age tinyint unsigned default 18,
    -> gender char(1) not null default '男'
    -> );

查看表结构及建表信息
【MySQL】表的约束
插入数据测试：

3.2 总结

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

四、列描述—comment

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

创建表t3并添加约束comment

mysql> create table if not exists t3(
    -> name varchar(20) not null comment '姓名',
    -> age char(1) not null comment '年龄'
    -> );

查看表结构看不到注释信息
【MySQL】表的约束
查看建表信息可以看到：comment越是是帮助数据库管理人员管理数据库的约束

五、zerofill

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

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

数据库中实际存储的值还是1，显示给我们看的是格式化后的数据
【MySQL】表的约束
再次修改字段num2属性，将int改为int(3)

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

修改表结构，num1为有符号整数，num2为无符号整数；通过查看建表信息发现，num1 int(11),num2 int(10)
【MySQL】表的约束
这是因为无符号整数0~42亿多，有符号整数是-21亿多到21亿多，那么他们最多占十位；而有符号数要多一个符号位，所以是11位

六、主键—primary key

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

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

mysql> create table if not exists primary_t(
    -> id int primary key comment '学号',
    -> name varchar(20) not null
    -> );

查看表结构发现，Key字段有PRI主键约束
【MySQL】表的约束
插入与主键相同的的值，报错

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

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

【MySQL】表的约束

6.1 复合主键

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

mysql> create table if not exists stu(
    -> id int unsigned,
    -> course int unsigned comment '课程号',
    -> score tinyint unsigned comment '成绩',
    -> primary key (id,course)
    -> );

存在任意不相同字段可以插入
【MySQL】表的约束

七、自增长—auto_increment

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

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

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

mysql> create table if not exists atimt(
    -> id int unsigned primary key auto_increment,
    -> name varchar(10) not null
    -> );

查看建表信息和表结构
【MySQL】表的约束
插入数据观察特性
忽略自增字段的时候，默认从1开始自增

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

建表的时候设置自增值

create table if not exists tt( id int unsigned primary key auto_increment, name varchar(10) not null )auto_increment=100；

【MySQL】表的约束

八、唯一键----unique

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

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

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

mysql> create table if not exists student(
    -> id varchar(10) not null primary key comment '主键id',
    -> name varchar(20) not null,
    -> tel varchar(11) unique  comment '唯一键'
    -> );

【MySQL】表的约束
上图可以发现，对于唯一键，NULL时可以插入。

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

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

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

使用语法

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

创建User表

mysql> create table if not exists User(
    -> id int unsigned primary key auto_increment,
    -> name varchar(10) not null,
    -> telphone varchar(11) not null,
    -> class_id int
    -> );

创建表Class

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

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

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

输入添加外键约束语句

mysql> alter table User add constraint foreign key(class_id) references Class(id);

查看表结构及建表信息
【MySQL】表的约束
上述红框内第一个字符串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)
要求：
每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(男，女)文章来源地址https://www.toymoban.com/news/detail-505743.html

-- 创建数据库
create database if not exists bit32mall
default character set utf8 ;
-- 选择数据库
use bit32mall;
-- 创建数据库表
-- 商品
create table if not exists goods
(
goods_id int primary key auto_increment comment '商品编号',
goods_name varchar(32) not null comment '商品名称',
unitprice decimal(10,4) not null default 0.0 comment '单价，单位分',
category varchar(12) comment '商品分类',
provider varchar(64) not null comment '供应商名称'
);
-- 客户
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 key comment '电子邮箱',
sex enum('男','女') not null comment '性别',
card_id char(18) unique key comment '身份证'
);
-- 购买
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)
);