MySQL_08约束

本文主要是介绍MySQL_08约束，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

MySQL_08约束

1.什么是约束？

约束对应的英语单词：constraint

在创建表的时候，我们可以给表中的字段加一些约束，来保证这个表中数据的完整性、有效性。

约束的作用就是为了保证表中的数据有效。

2.常用的约束包括哪些？

约束名	关键字
非空约束	not null
唯一性约束	unique
主键约束	primary key(简称PK)
外键约束	foreign key(简称FK)
检查约束	check(MySQL不支持,Oracle支持)

3.非空约束：not null

3.1创建表，使用not null

非空约束not null约束的字段不能为null。

现创建一个t_vip表，其中有id和name两个字段，使用not null约束name字段，同时插入两条记录。

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null
);
insert into t_vip(id,name) values(1,'Zhangsan');
insert into t_vip(id,name) values(2,'Lisi');

3.2使用sql脚本文件批量执行sql语句

我们可以创建一个后缀为.sql的sql脚本文件，将以上命令语句一并粘贴进去。

使用时，使用source命令导入该sql脚本文件，即可执行里面的所有命令。

输入source，然后直接将sql脚本文件拖进窗口，回车。

mysql> source D:\code\MySQL\MySQL_08约束\test01.sql

这种方式适用于执行大量sql语句。

3.3测试

插入如下数据：

insert into t_vip(id) values(3);

报错：

mysql> insert into t_vip(id) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

可见，name的值已经不可为null。

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

4.唯一性约束：unique

4.1创建表，使用unique

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique
);
insert into t_vip(id,name) values(1,'Zhangsan');
insert into t_vip(id,name) values(2,'Lisi');

4.2测试

查看表结构：

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

1）插入一条name重复的记录：

insert into t_vip(id,name) values(3,'Lisi');

插入失败：

mysql> insert into t_vip(id,name) values(3,'Lisi');
ERROR 1062 (23000): Duplicate entry 'Lisi' for key 'name'

2）插入两条只设定id的记录：

insert into t_vip(id) values(3);
insert into t_vip(id) values(4);

插入成功：

mysql> select * from t_vip;
+------+----------+
| id   | name     |
+------+----------+
|    1 | Zhangsan |
|    2 | Lisi     |
|    3 | NULL     |
|    4 | NULL     |
+------+----------+
4 rows in set (0.00 sec)

name字段虽然被unique约束了，但是可以都为null。

4.3两个字段联合唯一

现新建一个表t_vip，有id、name、email三个字段，要求name和email两个字段联合唯一。

也就是说可以插入的记录：

insert into t_vip(id,name,email) values(1,'Zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@qq.com');

不可以插入的记录：

insert into t_vip(id,name,email) values(1,'Zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@123.com');

创建表：

使用unique(字段名1,字段名2)来实现两个字段联合唯一。

约束没有添加在列的后面，这种约束被称为表级约束。

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)
);
insert into t_vip(id,name,email) values(1,'Zhangsan','zhangsan@123.com');

测试：

1)插入与第一条记录name、email相同的记录。

报错

mysql> insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@123.com');
ERROR 1062 (23000): Duplicate entry 'Zhangsan-zhangsan@123.com' for key 'name'

2)插入与第一条记录name相同，email不相同的记录。

插入成功

mysql> insert into t_vip(id,name,email) values(2,'Zhangsan','zhangsan@qq.com');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | Zhangsan | zhangsan@123.com |
|    2 | Zhangsan | zhangsan@qq.com  |
+------+----------+------------------+
2 rows in set (0.00 sec)

结论：当需要给多个字段联合起来添加某一个约束时，需要使用表级约束。

5.not null和unique联合

非空约束和唯一性约束可以联合起来使用。

新建表t_vip，使用not null 和unique联合约束name字段：

drop table if exists t_vip;
create table t_vip(
    id int, 
    name varchar(255) not null unique
);

查看表结构：

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

在mysql中，如果一个字段同时被not null 和 unique联合约束，就自动变成了主键字段。(注意：oracle中不一样)

测试：

insert into t_vip(id,name) values(1,'ZhangSan');
insert into t_vip(id,name) values(2,'ZhangSan');//报错，name不可重复
insert into t_vip(id) values(2);//也报错，name不能为null

6.主键约束(primary key，简称PK)【重要】

6.1主键概述

主键约束的相关术语：

主键字段：在一个字段上添加了主键约束，这样的字段就叫：主键字段。
主键值：主键字段中每一个值都叫：主键值。

主键有什么用？

主键值是每一行记录的唯一标识，相当于人的身份证号。

主键建议使用什么类型？

int
bigint
char
等类型

注意：

任何一张表都有主键，没有主键，表无效。

主键的特征：唯一且不为null(not null + unique)

如何给一张表添加主键约束？

drop table if exists t_vip;
create table t_vip(
    id int primary key,
    name varchar(255)
);

插入数据：

insert into t_vip(id,name) values(1,'ZhangSan');
insert into t_vip(id,name) values(2,'ZhangSan');
insert into t_vip(id,name) values(1,'Lisi');//报错，主键不可重复
insert into t_vip(name) values('Lisi');//报错，主键不可为null

查询表中数据：

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | ZhangSan |
+----+----------+
2 rows in set (0.00 sec)

此外，还可以通过表级约束来添加主键：

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    primary key(id)
);

6.2复合主键

一个字段做主键叫单一主键，两个字段联合起来做主键叫复合主键。

通过表级约束来添加联合主键：

drop table if exists t_vip;
#id和name联合起来做主键
create table t_vip(
    id int,
    name varchar(255),
    primary key(id,name)
);

插入数据：

insert into t_vip(id,name) values(1,'ZhangSan');
insert into t_vip(id,name) values(1,'LiSi');//可以插入相同的id
insert into t_vip(id,name) values(1,'ZhangSan');//错误，不可插入相同的id和name

查询结果：

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | LiSi     |
|  1 | ZhangSan |
+----+----------+
2 rows in set (0.00 sec)

在实际开发中，不建议使用复合主键。建议使用单一主键。

理由：

主键值存在的意义就是这行记录的身份证号，只要意义达到即可。单一主键就可以实现这一意义，复合主键比较复杂，不建议使用。

另外，在一张表中，只能有一个主键，不可重复定义。

drop table if exists t_vip;
mysql> create table t_vip(
    -> 	   id int primary key,
    ->     name varchar(255) primary key
    -> );
#报错，主键定义重复
ERROR 1068 (42000): Multiple primary key defined

6.3自然主键和业务主键

主键除了单一主键和复合主建外，还有如下分类：

自然主键：主键值是一个自然数，和业务无关。
业务主键：主键值和业务紧密关联，例如拿银行卡号来做主键值。

在实际开发中，使用自然主键较多一些，因为主键只需要做到不重复即可，不需要有意义。

业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动时，可能会影响到主键值，所以业务主键不建议使用。

6.4自动维护主键值机制

在mysql中，有一种机制，可以帮助我们自动维护主键值。

在主键后添加auto_increment：

drop table if exists t_vip;
create table t_vip(
    id int primary key auto_increment,
    name varchar(255)
);

插入记录，只设定name字段的值：

insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');
insert into t_vip(name) values('Zhangsan');

查看表中数据：

mysql> select * from t_vip;
+----+----------+
| id | name     |
+----+----------+
|  1 | Zhangsan |
|  2 | Zhangsan |
|  3 | Zhangsan |
|  4 | Zhangsan |
|  5 | Zhangsan |
|  6 | Zhangsan |
+----+----------+
6 rows in set (0.00 sec)

可见，如果未设定主键的值，主键会从1开始，以1递增地自动赋值。

7.外键约束(foreign key，简称FK)【重要】

外键约束涉及到的相关术语：

外键约束：一种约束(foreign key)
外键字段：添加了外键约束的字段
外键值：外键字段中的每一个值

7.1设计数据库表描述“班级和学生”信息

业务背景:

请设计数据库表，来描述“班级和学生”的信息。

7.1.1第一种方案

班级和学生信息都存于一张表中

drop table if exists t_school;
create table t_school(
    no int primary key auto_increment,
    name varchar(255),
    classno int,
    classname varchar(255)
);
insert into t_school(no,name,classno,classname) values
(1,'Zhangsan',100,'高三1班'),
(2,'Lisi',100,'高三1班'),
(3,'WangWu',101,'高三2班'),
(4,'ZhaoLiu',101,'高三2班'),
(5,'Tom',100,'高三1班'),
(6,'Jerry',101,'高三2班'),
(7,'Lili',101,'高三2班'),
(8,'Lorry',100,'高三1班');

查看表：

+----+----------+---------+------------+
| no | name     | classno | classname  |
+----+----------+---------+------------+
|  1 | Zhangsan |     100 |  高三1班    |
|  2 | Lisi     |     100 |  高三1班    |
|  3 | WangWu   |     101 |  高三2班    |
|  4 | ZhaoLiu  |     101 |  高三2班    |
|  5 | Tom      |     100 |  高三1班    |
|  6 | Jerry    |     101 |  高三2班    |
|  7 | Lili     |     101 |  高三2班    |
|  8 | Lorry    |     100 |  高三1班    |
+----+----------+---------+------------+

分析以上方案的缺点：有多项数据重复，数据冗余，空间浪费，是比较失败的设计。

7.1.2第二种方案

班级一张表，学生一张表。

班级表：t_class

+---------+------------+
| classno | classname  |
+---------+------------+
|     100 |  高三1班    |
|     101 |  高三2班    |
+---------+------------+

学生表：t_student

+----+----------+------+
| no | name     |  cno |#(cno班级编号)
+----+----------+------+
|  1 | Zhangsan |  100 |
|  2 | Lisi     |  100 |
|  3 | WangWu   |  101 |
|  4 | ZhaoLiu  |  101 |
|  5 | Tom      |  100 |
|  6 | Jerry    |  101 |
|  7 | Lili     |  101 |
|  8 | Lorry    |  100 |
+----+----------+------+

当cno字段没有任何约束时，可能会导致数据无效。比如可能出现一个102，但是102班级不存在。

所以为了保证cno字段中的值都是100和101，需要给cno字段添加一个外键约束。

即使用FK引用t_class这张表的classno。

添加后，cno字段就是外键字段，cno字段中的每一个值都是外键值。

注意：

被引用的t_class是父表，t_student是字表。
创建表的顺序是：
- 先创建父表，再创建子表。
删除表的顺序是：
- 先删子表，再删父表。
插入数据的顺序是：
- 先插入父，再插入子。
删除数据的顺序是：
- 先删除子，再删除父。

开始创建表：

#先删子，再删父
drop table if exists t_student;
drop table if exists t_class;
#先创父，再创子
create table t_class(
    classno int primary key,
    classname varchar(255)
);
create table t_student(
    no int primary key auto_increment,#自增
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)#添加外键约束，引用t_class表中的classno数据
);

#先插父，再插子
insert into t_class(classno,classname) values(100,'高三1班');
insert into t_class(classno,classname) values(101,'高三2班');

insert into t_student(no,name,cno) values
(1,'Zhangsan',100),
(2,'Lisi',100),
(3,'WangWu',101),
(4,'ZhaoLiu',101),
(5,'Tom',100),
(6,'Jerry',101),
(7,'Lili',101),
(8,'Lorry',100);

思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？

所以被引用的这个字段不一定是主键。但被引用的这个字段必须是唯一的，因为如果t_class表中的classno字段可以重复，那么在t_student表中，cno所引用的classno字段也就不确定了。

那么外键值可以为null吗？

插入一条cno为null的记录：

insert into t_student(name) values('Alice');

查看表：

mysql> select * from t_student;
+----+----------+------+
| no | name     | cno  |
+----+----------+------+
|  1 | Zhangsan |  100 |
|  2 | Lisi     |  100 |
|  3 | WangWu   |  101 |
|  4 | ZhaoLiu  |  101 |
|  5 | Tom      |  100 |
|  6 | Jerry    |  101 |
|  7 | Lili     |  101 |
|  8 | Lorry    |  100 |
|  9 | Alice    | NULL |
+----+----------+------+
9 rows in set (0.00 sec)

可见，外键值可以为null。

7.2总结

在设计表时，要考虑空间问题
实现父表和子表：
- 创建表：先创父，再创子
- 删除表：先删子，再删父
- 插入数据：先插父，再插子
- 删除数据：先删子，再删父
添加外键约束：foreign key(字段名) references 被引用表(被引用字段)。
外键值可以为null。
父表中被引用的字段不一定是主键，但必须唯一。(用unique字段约束)

这篇关于MySQL_08约束的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

MySQL_08约束

MySQL_08约束

1.什么是约束？

2.常用的约束包括哪些？

3.非空约束：not null

3.1创建表，使用not null

3.2使用sql脚本文件批量执行sql语句

3.3测试

4.唯一性约束：unique

4.1创建表，使用unique

4.2测试

4.3两个字段联合唯一

5.not null和unique联合

6.主键约束(primary key，简称PK)【重要】

6.1主键概述

6.2复合主键

6.3自然主键和业务主键

6.4自动维护主键值机制

7.外键约束(foreign key，简称FK)【重要】

7.1设计数据库表描述“班级和学生”信息

7.1.1第一种方案

7.1.2第二种方案

7.2总结

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯