MySQL表的约束
真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的
保证数据的合
法性
，从业务逻辑角度保证数据的正确性。比如有一个字段是
email
，要求是唯一的。
表的约束很多，这里主要介绍如下几个：
null/not null,default, comment, zerofill
，
primary
key
，
auto_increment
，
unique key
。
1.空属性
两个值：
null
（默认的）和
not null(
不为空
)
数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办
法参与运算。
mysql>
select
null
;+——+
|
NULL
|+——+
|
NULL
|+——+
1
row
in set
(
0.00
sec)mysql>
select
1
+
null
;+——–+
|
1
+
null
|+——–+
|
NULL
|+——–+
1
row
in set
(
0.00
sec)
案例：
创建一个班级表，包含班级名和班级所在的教室。
站在正常的业务逻辑中：
如果班级没有名字，你不知道你在哪个班级
如果教室名字可以为空，就不知道在哪上课
所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中。这就是“
约束
”
。
mysql>
create table
myclass(-> class_name
varchar
(
20
)
not
null
,-> class_room
varchar
(
10
)
not
null
);Query OK,
0
rows affected (
0.02
sec)mysql>
desc
myclass;+————+————-+——+—–+———+——-+
| Field | Type |
Null
| Key | Default | Extra |+————+————-+——+—–+———+——-+
| class_name |
varchar
(
20
) | NO | |
NULL
| || class_room |
varchar
(
10
) | NO | |
NULL
| |+————+————-+——+—–+———+——-+
//
插入数据时
,
没有给教室数据插入失败：mysql>
insert into
myclass(class_name)
values
(
‘class1’
);ERROR
1364
(HY000): Field
‘class_room’
doesn
‘t have a default value
2.默认值
默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。
mysql>
create table
tt10 (-> name
varchar
(
20
)
not
null
,-> age
tinyint unsigned
default
0
,-> sex
char
(
2
) default
‘
男
‘-> );
Query OK,
0
rows affected (
0.00
sec)mysql>
desc
tt10;+——-+———————+——+—–+———+——-+
| Field | Type |
Null
| Key | Default | Extra |+——-+———————+——+—–+———+——-+
| name |
varchar
(
20
) | NO | |
NULL
| || age |
tinyint
(
3
)
unsigned
| YES | |
0
| || sex |
char
(
2
) |YES | | 男 | |+——-+———————+——+—–+———+——-+
默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
mysql>
insert into
tt10(name)
values
(
‘zhangsan’
);Query OK,
1
row affected (
0.00
sec)mysql>
select
*
from
tt10;+———-+——+——+
| name | age | sex |
+———-+——+——+
| zhangsan |
0
|
男
|+———-+——+——+
–注意：只有设置了default的列，才可以在插入值的时候，对列进行省略
3.列描述
列描述：
comment
，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或
DBA来进行了解。
mysql>
create table
tt12 (-> name
varchar
(
20
)
not
null
comment
‘
姓名
‘
,-> age
tinyint unsigned
default
0
comment
‘
年龄
‘
,-> sex
char
(
2
) default
‘
男
‘
comment
‘
性别
‘-> );
—
注意：
not null
和
defalut
一般不需要同时出现，因为
default
本身有默认值，不会为空
通过desc查看不到注释信息：
mysql>
desc
tt12;+——-+———————+——+—–+———+——-+
| Field | Type |
Null
| Key |Default|Extra|+——-+———————+——+—–+———+——-+
| name|
varchar
(
20
) | NO | |
NULL
| || age |
tinyint
(
3
)
unsigned
| YES | |
0
| || sex |
char
(
2
) | YES | | 男 | |+——-+———————+——+—–+———+——-+
通过show可以看到：
mysql> show
create table
tt12\G***************************
1
. row ***************************Table
: tt12Create Table
:
CREATE TABLE
`tt12` (`name`
varchar
(
20
)
NOT
NULL
COMMENT
‘
姓名
‘
,`age`
tinyint
(
3
)
unsigned
DEFAULT
‘0’
COMMENT
‘
年龄
‘
,`sex`
char
(
2
) DEFAULT
‘
男
‘
COMMENT
‘
性别
‘) ENGINE=MyISAM DEFAULT CHARSET=gbk
1
row
in set
(
0.00
sec)
4. zerofill
刚开始学习数据库时，很多人对数字类型后面的长度很迷茫。通过show看看tt3表的建表语句：
mysql> show
create table
tt3\G*****************
1
. row *****************Table
: tt3Create Table
:
CREATE TABLE
`tt3` (`a`
int
(
10
)
unsigned
DEFAULT
NULL
,`b`
int
(
10
)
unsigned
DEFAULT
NULL) ENGINE=MyISAM DEFAULT CHARSET=gbk
1
row
in set
(
0.00
sec)
可以看到
int(10),
这个代表什么意思呢？整型不是
4
字节码？这个
10
又代表什么呢？其实没有
zerofill
这个属性，括号内的数字是毫无意义的。a
和
b
列就是前面插入的数据，如下：
mysql>
insert into
tt3
values
(
1
,
2
);Query OK,
1
row affected (
0.00
sec)mysql>
select
*
from
tt3;+——+——+
| a | b |
+——+——+
|
1
|
2
|+——+——+
但是对列添加了zerofill属性后，显示的结果就有所不同了。修改tt3表的属性：
mysql>
alter table
tt3 change a a
int
(
5
)
unsigned
zerofill;mysql> show
create table
tt3\G***************************
1
. row ***************************Table
: tt3Create Table
:
CREATE TABLE
`tt3` (`a`
int
(
5
)
unsigned
zerofill DEFAULT
NULL
,
—
具有了
zerofill`b`
int
(
10
)
unsigned
DEFAULT
NULL) ENGINE=MyISAM DEFAULT CHARSET=gbk
1
row
in set
(
0.00
sec)
对a列添加了zerofill属性，再进行查找，返回如下结果：
mysql>
select
*
from
tt3;+——-+——+
| a | b |
+——-+——+
|
00001
|
2
|+——-+——+
这次可以看到
a
的值由原来的
1
变成
00001
，这就是
zerofill
属性的作用，如果宽度小于设定的宽度（这里设置的是5
），自动填充
0
。要注意的是，这只是最后显示的结果，在
MySQL
中实际存储的还是
1
。为什么是这样呢？我们可以用hex
函数来证明。
mysql>
select
a, hex(a)
from
tt3;+——-+——–+
| a |hex(a) |
+——-+——–+
|
00001
|
1
|+——-+——–+
可以看出数据库内部存储的还是1,00001只是设置了zerofill属性后的一种格式化输出而已。
5.主键
主键：
primary key
用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。
案例：
创建表的时候直接在字段上指定主键
主键约束：主键对应的字段中不能重复，一旦重复，操作失败。
mysql>
insert into
tt13
values
(
1
,
‘aaa’
);Query OK,
1
row affected (
0.00
sec)
mysql>
insert into
tt13
values
(
1
,
‘aaa’
);ERROR
1062
(
23000
): Duplicate entry
‘1’
for key
‘PRIMARY’
当表创建好以后但是没有主键的时候，可以再次追加主键
alter table 表名 add primary key(字段列表)
删除主键
alter table 表名 drop primary key;
mysql>
alter table
tt13
drop
primary key;mysql>
desc
tt13;+——-+——————+——+—–+———+——-+
| Field | Type |
Null
| Key | Default | Extra |+——-+——————+——+—–+———+——-+
| id |
int
(
10
)
unsigned
| NO | |
NULL
| || name |
varchar
(
20
) | NO | |
NULL
| |+——-+——————+——+—–+———+——-+
复合主键
在创建表的时候，在所有字段之后，使用
primary key(
主键字段列表
)
来创建主键，如果有多个字段
作为主键，可以使用复合主键。
mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment ‘
课程代码
‘,-> score tinyint unsigned default 60 comment ‘
成绩
‘,-> primary key(id, course) — id
和
course
为复合主键-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc tt14;
+——–+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——–+———————+——+—–+———+——-+
| id | int(10) unsigned | NO | PRI | 0| | insert into tt14 (id,course)values(1, ‘123’);
Query OK, 1 row affected (0.02 sec)
mysql> insert into tt14 (id,course)values(1, ‘123’);
ERROR 1062 (23000): Duplicate entry ‘1-123’ for key ‘PRIMARY’ —
主键冲突
6.自增长
auto_increment
：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
自增长的特点
:
任何一个字段要做自增长，前提是本身是一个索引（
key
一栏有值）自增长字段必须是整数
一张表最多只能有一个自增长
案例：
mysql>
create table
tt21(-> id
int unsigned
primary key auto_increment,-> name
varchar
(
10
)
not
null
default
”-> );
mysql>
insert into
tt21(name)
values
(
‘a’
);mysql>
insert into
tt21(name)
values
(
‘b’
);mysql>
select
*
from
tt21;+—-+——+
| id |name |
+—-+——+
|
1
| a ||
2
| b |+—-+——+
在插入后获取上次插入的 AUTO_INCREMENT 的值（批量插入获取的是第一个值）
mysql >
select
last_insert_id();+——————+
| last_insert_id() |
+——————+
|
1
|+——————+
索引：
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL
语句执行得更快，可快速访问数据库表中的特定信息。
7.唯一键
一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
关于唯一键和主键的区别：
我们可以简单理解成，主键更多的是标识唯一性的。而唯一键更多的是保证在业务上，不要和别的信息出现重复。乍一听好像没啥区别，我们举一个例子
假设一个场景
(
当然，具体可能并不是这样，仅仅为了帮助大家理解
)比如在公司，我们需要一个员工管理系统，系统中有一个员工表，员工表中有两列信息，一个身份证号码，一个是员工工号，我们可以选择身份号码作为主键。
而我们设计员工工号的时候，需要一种约束：而所有的员工工号都不能重复。
具体指的是在公司的业务上不能重复，我们设计表的时候，需要这个约束，那么就可以将员工工号设计成为唯一键。
一般而言，我们建议将主键设计成为和当前业务无关的字段，这样，当业务调整的时候，我们可以尽量不会对主键做过大的调整。
案例：
mysql>
create table
student (-> id
char
(
10
) unique comment
‘
学号，不能重复，但可以为空
‘
,-> name
varchar
(
10
)-> );
Query OK,
0
rows affected (
0.01
sec)mysql>
insert into
student(id, name)
values
(
’01’
,
‘aaa’
);Query OK,
1
row affected (
0.00
sec)mysql>
insert into
student(id, name)
values
(
’01’
,
‘bbb’
);
—
唯一约束不能重复ERROR
1062
(
23000
): Duplicate entry
’01’
for key
‘id’mysql>
insert into
student(id, name)
values
(
null
,
‘bbb’
);
—
但可以为空Query OK,
1
row affected (
0.00
sec)mysql>
select
*
from
student;+——+——+
| id |name|
+——+——+
|
01
| aaa ||
NULL
|bbb |+——+——+
8.外键
外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或
unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null
。
语法：
foreign key (
字段名
) references
主表
(
列
)
对上面的示意图进行设计：
先创建主键表
create table
myclass (id
int
primary key,name
varchar
(
30
)
not
null
comment
‘
班级名
‘);
再创建从表
create table
stu (id
int
primary key,name
varchar
(
30
)
not
null
comment
‘
学生名
‘
,class_id
int
,foreign key (class_id) references myclass(id)
);
正常插入数据
mysql>
insert into
myclass
values
(
10
,
‘C++
大牛班
‘
),(
20
,
‘java
大神班
‘
);Query OK,
2
rows affected (
0.03
sec)Records:
2
Duplicates:
0
Warnings:
0
mysql>
insert into
stu
values
(
100
,
‘
张三
‘
,
10
),(
101
,
‘
李四
‘
,
20
);Query OK,
2
rows affected (
0.01
sec)Records:
2
Duplicates:
0
Warnings:
0
插入一个班级号为30的学生，因为没有这个班级，所以插入不成功
mysql>
insert into
stu
values
(
102
,
‘wangwu’
,
30
);ERROR
1452
(
23000
): Cannot add
or update
a child row:a foreign key constraint fails (mytest
.stu
, CONSTRAINT stu_ibfk_1FOREIGN KEY (class_id) REFERENCES myclass (id))
插入班级id为null，比如来了一个学生，目前还没有分配班级
mysql> insert into stu values(102, ‘wangwu’, null);
9.综合案例
有一个商店的数据，记录客户及购物情况，有以下三个表组成：
商品
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)
要求：
每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(
男，女
)
SQL:
-- 创建数据库
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 int not null default 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)
);
本文来自网络，不代表协通编程立场，如若转载，请注明出处：https://www.net2asp.com/4ab54c21d0.html
相关推荐
-
C语言 strlen、length、size、sizeof的区别
strlen: strlen 是 C 语言中的函数，用于计算字符串的长度（不包括字符串末尾的’\0’）。 仅适用于以 null 字符结尾的字符串，即 C-s…
-
Unity UI、图片(Sprite)的显示层级(遮挡关系)控制
UI之间如何控制互相的遮挡关系： 一个方法是给每个UI都添加一个Canvas组件，选中 Override Sorting之后就可以通过调整Sort Order的数值来控制遮挡关系。…
-
【vue + element ui】ColorPicker 颜色选择器的使用
项目场景： 组件提供的ColorPicker 颜色选择器不能满足实际需求，所以在此基础上使用了自定义的方式进行使用 我这边的使用场景是ColorPicker与el-input输入框…
-
关于element ui 中使用表单校验字段，校验一直不消失的问题
element ui 中form表单，当遇到表单项为上传文件或者图片等等，上传完成之后，点击确定按钮，该表单项校验规则仍然不消失，如下 解决方法为，在上传成功的方法中手动移除校…
-
如何将数据显示到UI上？
UE5 插件开发指南 前言 前言 前面介绍的知识点需要串联起来，从而达到我们将数据显示到UI上的目标。现在重新回到源头，理清思路：策划交给程序员一个Excel表格，这个表格就是物品…
-
蓝凌OA sysUiExtend.do 任意文件上传漏洞复现
0x01 产品简介 蓝凌核心产品EKP平台定位为新一代数字化生态OA平台，数字化向纵深发展，正加速构建产业互联网，对企业协作能力提出更高要求，蓝凌新一代生态型OA平台能够支撑办公数…
-
浅谈kubernetes部署：UI部署
UI部署 镜像制作 登录私服 以阿里云docker私服举例 sudodockerlogin—username=registry.cn-beijing.aliyuncs.co…
-
stable-diffusion-webui的逐步部署教程
大家好，我是爱编程的喵喵。双985硕士毕业，现担任全栈工程师一职，热衷于将数据思维应用到工作与生活中。从事机器学习以及相关的前后端开发工作。曾在阿里云、科大讯飞、CCF等比赛获…
-
案例 | 3D可视化工具HOOPS助力SolidWorks edrawings成功引入AR/VR技术
HOOPS中文网慧都科技是HOOPS全套产品中国地区指定授权经销商，提供3D软件开发工具HOOPS售卖、试用、中文试用指导服务、中文技术支持。http://techsoft3d.e…
-
3D Gaussian Splatting的使用
3D Gaussian Splatting的使用 1 下载与安装 2 准备场景样本 2.1 准备场景照片 2.1.1 采集图片 2.1.2 生成相机位姿 3 训练 4 展示 1 下…
