当前位置：首页 > news >正文

MYSQL 笔记3

news 2025/7/25 11:32:12

表的约束
1.6.1. 概念
为什么需要约束？
真正约束字段的是数据类型,但是数据类型约束很单一,需要有一些额外的约束,更好的保证数据的合法性,从业务逻辑角度保证数据的正确性，如：有一个字段是身份证,要求是唯一的
定义字段的类型无法满足对表的约束：
表字段是否可以为NULL,有没有默认值,表字段的解释能不能加上
对于数字类型的字段可不可以指定默认表示的位数,可不可以将这个字段设置称为唯一标识该行的数据
本质上Mysql是一套数据存储解决方案,除了解决基本的数据存储功能之外,还要保证数据尽可能的安全,减少用户的误操作可能性, 约束的体现,不仅仅可以体现在数据库层面,在我们进行用户业务逻辑编码的时候,我们其实也可以做判断(约束),约束其实不仅仅只能出现在数据库层而上,编码层面也是可以体现

约束是什么：约束是在表上强制执行的数据校验规则，本质上是Mysql通过限制用户操作的方式,来达到维护数据本身安全及数据完整性的一套方案

数据的完整性要从以下四个方面考虑：
实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。
约束作为数据库对象，存放在系统表中，也有自己的名字

常见约束：
非空约束
默认值约束
主键约束
外键约束
唯一约束
检查约束
创建约束的时机：
在建表的同时创建
建表后创建（修改表）
约束可定义列级或表级约束

语法：
列级约束：在定义列的同时定义约束
create table 表名 (列名类型约束 )
表级约束：在定义了所有列之后定义的约束
create table 表名 constraint 约束名约束类型 (列信息)
创建表之后添加约束：
alter table 表名 add constraint 约束名约束类型(要约束的列名)
约束名推荐采用：表名_列名_约束类型简介

非空约束
作用：限定某个字段/某列的值不允许为空
两个值：null（默认的）和not null(不为空)
列字段默认一般都是空,但是实际开发时,尽可能保证字段不为空,因为数据为空没办法参与运算
所有数据类型的值都可以是NULL，空字符串不等于NULL，0也不等于NULL
示例：

mysql> use mydb2_stuinfo;

Database changed

mysql> desc student1;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| sex | char(2) | YES | | NULL | |

| age | int unsigned | YES | | NULL | |

+----------+--------------+------+-----+---------+-------+

8 rows in set (0.00 sec)

# 插入数据

mysql> insert into student1(id,name) values('1002','张三');

Query OK, 1 row affected (0.00 sec)

mysql> insert into student1(id,name) values('1003','李四');

Query OK, 1 row affected (0.00 sec)

mysql> select * from student1;
+------+----------+------+------+-------+-------+----------+------------+

# 显示sex之后的列都为NULL，表示内容没有填

若需要某些字段必须写入，应该怎么处理，如：创建一个班级表，包含班级名和班级所在的教室，站在正常的业务逻辑中：如果班级没有名字，你不知道你在哪个班级，如果教室名字可以为空，就不知道在哪上课，所以我们在设计数据库表的时候，一定要在表中进行限制，满足上面条件的数据就不能插入到表中，这就是“约束

mysql> create table myclass1(class_name varchar(20) not null, class_room

varchar(20) not null, class_id int); # 无约束

Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass1;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| class_id | int | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

# 插入数据时：

mysql> insert into myclass1 (class_room) values('计科1');

ERROR 1364 (HY000): Field 'class_name' doesn't have a default value
# 失败，因为 name有约束不能为空

mysql> insert into myclass1 (class_name,class_room) values('A104','计科1'); #

同时插入成功

Query OK, 1 row affected (0.00 sec)

mysql> select * from myclass1;

+------------+------------+----------+

| class_name | class_room | class_id |

+------------+------------+----------+

| A104 | 计科1 | NULL |

+------------+------------+----------+

1 row in set (0.00 sec)

# id字段无约束，可以为空

以上为创建表时的设置，也可以创建之后修改：
# 语法：
alter table <数据表名> change column <字段名> <字段名> <数据类型> not null;

mysql> alter table student1 change column id id int not null;

# 也可以通过modify修改

mysql> alter table student1 modify name varchar(30) not null;

# modify字句中省略not null 相当于设置为可以为空
比如说：
删除not null

mysql> alter table student1 modify id int null;

Query OK, 0 rows affected (0.04 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 或者

mysql> alter table student1 modify name varchar(30);

mysql> desc student1;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| sex | char(2) | YES | | NULL | |

| age | int unsigned | YES | | NULL | |

+----------+--------------+------+-----+---------+-------+

8 rows in set (0.00 sec)

默认值约束：
作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

mysql> create table teacher1(id int not null, name varchar(30) not null, sex
char(2) default '女');
Query OK, 0 rows affected (0.03 sec)

mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| sex | char(2) | YES | | 女 | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> insert into teacher1(id,name) values('1001','林雪');
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher1;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1001 | 林雪 | 女 |
+------+--------+------+
1 row in set (0.00 sec)

mysql> insert into teacher1(id,name,sex) values('1002','石磊','男'); # 也可以全字段插入
Query OK, 1 row affected (0.01 sec)
mysql> select * from teacher1;
+------+--------+------+
| id | name | sex |
+------+--------+------+
| 1001 | 林雪 | 女 |
| 1002 | 石磊 | 男 |
+------+--------+------+
2 rows in set (0.00 sec)

# 创建表之后增加默认值：
mysql> alter table student1 modify sex char(2) default '女';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student1;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
| sex | char(2) | YES | | 女 | |
| age | int unsigned | YES | | NULL | |
| score | float | YES | | NULL | |
| addr | varchar(50) | YES | | NULL | |
| passwd | char(8) | YES | | NULL | |
| birthday | date | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
注意：若某字段都设置了not null和default约束，则按照不插入时会选择默认值，插入时选择插入值，所以此时not null毫无意义
mysql> alter table teacher1 add age int not null default '18';
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc teacher1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(30) | NO | | NULL | |
| sex | char(2) | YES | | 女 | |
| age | int | NO | | 18 | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
# not null和defalut一般不需要同时出现,因为default本身有默认值,不会为空

列描述---comment：
列描述没有实际含义,专门用来描述字段,会根据表创建语句保存,供操作者来查看该列的含义，相当于C/C++的注释

mysql> create table myclass2( class_name varchar(20) not null comment '教

室',class_room varchar(20) default '计科2');

Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass2;

+------------+-------------+------+-----+---------+-------+

+------------+-------------+------+-----+---------+-------+

| class_room | varchar(20) | YES | | 计科2 | |

+------------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> show create table myclass2\G

*************************** 1. row ***************************

Table: myclass2

Create Table: CREATE TABLE `myclass2` (

`class_name` varchar(20) NOT NULL COMMENT '教室',

`class_room` varchar(20) DEFAULT '计科2' COMMENT '班级'

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1 row in set (0.00 sec)

主键约束

作用：数据库存储数据不是唯一的目的,还要考虑到提取数据，一般要求数据库表中的记录要有一个特定的唯一标识，来表示唯一性,这个唯一的特定标识就是主键,类似于序号学号这样的唯一标识,可以根据主键来唯一地筛选出一条记录

主键：primary key，用来唯一的约束该字段里面的数据

特点：

主键字段不能为空，不能重复

一张表中最多只能有一个主键

主键所在的列通常是整数类型

主键约束是最频繁的约束

注意：当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了，需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

mysql> create table t1 ( id int unsigned primary key comment '学号不能为空', name

varchar(20) not null ,sex char(2) default '男');

Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int unsigned | NO | PRI | NULL | |

| sex | char(2) | YES | | 男 | |

+-------+--------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

mysql> insert into t1 values(1,'孙文','女');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;

+----+--------+------+

| id | name | sex |

+----+--------+------+

| 1 | 孙文 | 女 |

+----+--------+------+

1 row in set (0.00 sec)

mysql> insert into t1(id,name) values(1,'黎明');

ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'

# 再次插入出错，因为需要保持唯一性

示例：修改表，追加主键

mysql> alter table teacher1 add primary key(id);

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc teacher1;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| sex | char(2) | YES | | 女 | |

| age | int | NO | | 18 | |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

注意:如果该字段内有重复值,则不能以该字段新增为主键,必须先把重复的数据去掉然后才能添加该列为主键

删除主键：
alter table 表名 drop primary key;

复合主键：在创建表的时候,在所有字段之后,使用primary key(主键字段列表)来创建主键,如果有多个字段作为主键,可以使用复合主键,这些字段合在一起是一个主键，也就是让多个字段联合在一起作为唯一标识,单个字段主键重复是没有问题的,只要不是成为复合主键的字段一起冲突就行

mysql> create table t2 (id int, hostname varchar(10), ip varchar(20), port int unsigned, primary key (ip,port));

Query OK, 0 rows affected (0.02 sec)

mysql> desc t2;

+----------+--------------+------+-----+---------+-------+

+----------+--------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

| ip | varchar(20) | NO | PRI | NULL | |

+----------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> insert into t2 values(1,'node1','192,168,48,2',120);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;

+------+----------+--------------+------+

| id | hostname | ip | port |

+------+----------+--------------+------+

| 1 | node1 | 192,168,48,2 | 120 |

+------+----------+--------------+------+

1 row in set (0.00 sec)

mysql> insert into t2 values(2,'node2','192,168,48,3',120);

Query OK, 1 row affected (0.00 sec) # 可以单个记录重复

mysql> insert into t2 values(3,'node2','192,168,48,2',120);

ERROR 1062 (23000): Duplicate entry '192,168,48,2-120' for key 't2.PRIMARY' # 复合

主键一起冲突，报错

主键自增长

作用：给主键添加自增长的数值

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

注意：

自增长字段必须是整数,自增长字段可以不设置初始值,默认从1开始递增.

被自增长的字段必须作为主键或者其他具有唯一性的键使用,(必须保证这一列字段具有唯一性的字段)

自增长字段也可以插入数据,只要不与已有数据重复即可,之后新增数据会从最大值开始递增.

任何一个字段要做自增长,前提是本身是一个索引（key一栏有值）.

一张表当中最多只能有一个自增长的列

约束的字段必须具备 NOT NULL 属性

# 先增加主键

mysql> use mydb2_stuinfo;

Database changed

mysql> alter table student1 modify id int primary key;

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

# 设置自增长

mysql> alter table student1 modify id int auto_increment;

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

# 查看

mysql> desc student1;

+----------+--------------+------+-----+---------+----------------+

+----------+--------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

| sex | char(2) | YES | | 女 | |

| age | int unsigned | YES | | NULL | |

+----------+--------------+------+-----+---------+----------------+

8 rows in set (0.00 sec)

id自动增加了一位，变成1004

指定自增长的起始值：如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5，那么再插入记录时，id 值就会从 5 开始往上增加

# 指定自增长的起始值为100

mysql> create table t3 ( id int primary key auto_increment, systemname varchar(10)

not null )auto_increment=100;

Query OK, 0 rows affected (0.02 sec)

# 插入一条记录

mysql> insert into t3(systemname) values('Linux');

Query OK, 1 row affected (0.00 sec)

# 插入一条记录

mysql> insert into t3(systemname) values('windows');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;

+-----+------------+

| id | systemname |

+-----+------------+

| 100 | Linux |

| 101 | windows |

+-----+------------+

2 rows in set (0.01 sec)

唯一性约束
产生原因：一张表中有往往有很多字段需要唯一性,数据不能重复,但是一张表中只能有一个主键,唯一键就可以解决表中有多个字段需要唯一性约束的问题

作用：限制某个字段/某列的值不能重复

唯一键和主键的关系：
主键更多的是标识唯一性
唯一键更多的是保证在业务上,不要和别的信息出现重复
主键只能有一个,唯一键能设置多个
主键和唯一键不是对立关系而是补充关系,目的是让数据库创建出一个表具有非常强的约束,彻底杜绝用户的误插入，在一张表当中,可以有一个主键,也可以同时具有唯一键, 它们不是用来对比的,只是负责数据在不同层面的唯一性,既要保证选择成为主键的属性字段不能重复不能为空,同时也要保证本身具有唯一性的列的字段不能出现冲突
主键用来查找,唯一键用来保证数据的完整性
如：学生信息中有身份证号和学号两个字段，可以把身份证号设置成为主键,而所有学生的学号也不能重复,此时就可以把学号设置成唯一键

mysql> create table t4( id int primary key, name varchar(20) unique comment '名子不

能重名，可以为空' );

Query OK, 0 rows affected (0.02 sec)

mysql> desc t4;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

# 插入数据

mysql> insert into t4 values(1,'刘文');

Query OK, 1 row affected (0.00 sec)

# 唯一键数据重复，插入失败

mysql> insert into t4 values(1,'刘文');

ERROR 1062 (23000): Duplicate entry '1' for key 't4.PRIMARY'

mysql> insert into t4 values(2,'张磊');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;

+----+--------+

| id | name |

+----+--------+

| 1 | 刘文 |

| 2 | 张磊 |

+----+--------+

2 rows in set (0.00 sec)

唯一键冲突，自增涨字段值不连续

# 设置id列为自增长

mysql> alter table t4 modify id int auto_increment;

Query OK, 3 rows affected (0.03 sec)

Records: 3 Duplicates: 0 Warnings: 0

# 插入一条记录

mysql> insert into t4(name) values('张磊磊');

ysql> select * from t4;

+----+-----------+

| id | name |

+----+-----------+

| 1 | 刘文 |

| 2 | 张磊 |

| 3 | 张磊磊 |

+----+-----------+

3 rows in set (0.00 sec)

# 插入一条相同姓名记录

mysql> insert into t4(name) values('张磊磊');

ERROR 1062 (23000): Duplicate entry '张磊磊' for key 't4.name'

# 在插入一条记录，由于上述唯一键冲突，自增长会出现不连续

mysql> insert into t4(name) values('钱明');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;

+----+-----------+

| id | name |

+----+-----------+

| 1 | 刘文 |

| 2 | 张磊 |

| 3 | 张磊磊 |

| 5 | 钱明 |

+----+-----------+

4 rows in set (0.00 sec)

外键约束
作用：限定某个表的某个字段的引用完整性
概念：
主表（父表）：被引用的表、被参考的表
从表（子表）：引用别人的表，参考别人的表
外键：从表中的某个字段引用自主表的某个字段或多个字段
引用键：主表被引用的字段
外键约束主要定义在从表上,主表则必须是有主键约束或唯一键约束.当定义外键后,要求外键列数据必须在主表的主键列存在或为null

格式：foreign key (从表的字段名称) references 主表名字(主表的字段名称) # 建立外键关联

注意：
主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为主表定义主键。
主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。
定义从表的时候,设置外键其实就是设置了一个关系,在从表当中插入数据的时候,会对插入数据进行校验,校验插入的数据是否存在于主表字段当中,外键就是增加了表和表之间的约束关系。

示例：

# 定义主表

mysql> create table myclass3(id int primary key, name varchar(20) not null comment

'班级名');

Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass3;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

# 定义从表：

mysql> create table myclass3_stu( id int primary key, name varchar(30) not null

comment '学生名', class_id int, foreign key (class_id) references myclass3(id) );

Query OK, 0 rows affected (0.02 sec)

mysql> desc myclass3_stu;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

| class_id | int | YES | MUL | NULL | |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

# 主表中插入数据

mysql> insert into myclass3 values(10,'C++'),(20,'Java');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from myclass3;

+----+------+

| id | name |

+----+------+

| 10 | C++ |

| 20 | Java |

+----+------+

2 rows in set (0.00 sec)

# 从表中正常插入数据：

mysql> insert into myclass3_stu values(1,'Li',10),(2,'Sun',20);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from myclass3_stu;

+----+------+----------+

| id | name | class_id |

+----+------+----------+

| 1 | Li | 10 |

| 2 | Sun | 20 |

+----+------+----------+

2 rows in set (0.00 sec)

# 从表中插入没有班级的记录，受外键控制，报错

mysql> insert into myclass3_stu values(3,'wang',30);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint

fails (`stu_info`.`myclass3_stu`, CONSTRAINT `myclass3_stu_ibfk_1` FOREIGN KEY

(`class_id`) REFERENCES `myclass3` (`id`))

# 从表中，收到外键控制，可以使用null替代，表示没有分配班级

mysql> insert into myclass3_stu values(3,'wang',null);

Query OK, 1 row affected (0.00 sec)

mysql> select * from myclass3_stu;

+----+------+----------+

| id | name | class_id |

+----+------+----------+

| 1 | Li | 10 |

| 2 | Sun | 20 |

| 3 | wang | NULL |

+----+------+----------+

3 rows in set (0.00 sec)

检查约束：
作用：检查约束（check）是用来检查数据表中字段值有效性的一种手段，一般用于设置值的范围
注意：
设置检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入
在默认值和非空约束可看作是特殊的检查约束
注意检查约束在8.0.16之前，MySQL默认但不会强制的遵循check约束（写不报错，但是不生效，需要通触发器完成），8之后就开始正式支持这个约束了

示例1：创建表时设置检查约束

mysql> create table t5(id int primary key, name varchar(20), salary float,

check(salary>0 and salary<10000) );

Query OK, 0 rows affected (0.02 sec)

mysql> desc t5;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

+--------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

mysql> insert into t5 values(1,'Li',5984);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(2,'wang',12000);