mysql学习笔记(三)：数据约束

最新推荐文章于 2022-07-20 23:35:48 发布

秋忆夏伤

最新推荐文章于 2022-07-20 23:35:48 发布

阅读量201

点赞数

分类专栏： mysql学习笔记

本文链接：https://blog.csdn.net/qq_29331365/article/details/94553902

版权

mysql学习笔记专栏收录该内容

7 篇文章 1 订阅

订阅专栏

一、默认值约束

当用户对使用默认值约束的字段不插入值的时候，该字段就会使用默认值；

下面为默认值约束的创建：使用 default 关键字；

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> address varchar(20) default "杭州西湖");
Query OK, 0 rows affected

插入数据的时候不插入 address 数据，数据库会自动使用默认值填充 address 列：

mysql> insert into student(id,name) values(1, "rose");
Query OK, 1 row affected

mysql> select * from student;
+----+------+----------+
| id | name | address  |
+----+------+----------+
|  1 | rose | 杭州西湖 |
+----+------+----------+
1 row in set

注意：默认值的字段允许插入 null 值；

mysql> insert into student values(2, "lily", null);
Query OK, 1 row affected

mysql> select * from student;
+----+------+----------+
| id | name | address  |
+----+------+----------+
|  1 | rose | 杭州西湖 |
|  2 | lily | NULL     |
+----+------+----------+
2 rows in set

二、非空约束

插入数据的时候，使用非空约束的字段必须赋值；

创建非空约束：not null

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> gender varchar(2) not null);
Query OK, 0 rows affected

注意：非空约束的字段必须赋值，且不能插入 null 值，但是可以插入空字符串；

mysql> insert into student(id,name) values(1, "rose");
1364 - Field 'gender' doesn't have a default value
mysql> 
mysql> insert into student(id,name,gender) values(1, "rose", null);
1048 - Column 'gender' cannot be null
mysql> 
mysql> insert into student(id,name,gender) values(1, "rose", "");
Query OK, 1 row affected

三、唯一约束

插入数据的时候，使用唯一约束的字段的值不能重复；

创建唯一约束：unique

mysql> create table student(
    -> id int unique,
    -> name varchar(20));
Query OK, 0 rows affected

使用唯一约束的字段的值不能重复：

mysql> insert into student values(1, "rose");
Query OK, 1 row affected

mysql> insert into student values(1, "lily");
1062 - Duplicate entry '1' for key 'id'

注意：唯一约束可以为 null 值；（只有在有值的时候才会约束不能重复）

mysql> insert into student(name) values("lily");
Query OK, 1 row affected

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
|    1 | rose |
| NULL | lily |
+------+------+
2 rows in set

四、主键约束

主键的作用：非空 + 唯一

通常情况下，每张表都会设置一个主键字段，用于标记表中的每条记录的唯一性；

建议不要选择表中包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的 id 字段作为主键；

创建主键约束：primary key

mysql> create table student(
    -> id int primary key,
    -> name varchar(20));
Query OK, 0 rows affected

主键具有唯一约束：

mysql> insert into student values(1, "rose");
Query OK, 1 row affected

mysql> insert into student values(1, "lily");
1062 - Duplicate entry '1' for key 'PRIMARY'

主键具有非空约束：

mysql> insert into student(name) values("lily");
1364 - Field 'id' doesn't have a default value
mysql> 
mysql> insert into student values(null, "lily");
1048 - Column 'id' cannot be null

五、自增长约束

插入数据的时候，使用自增长约束的字段不用赋值，会自动生成数值，且从 1 开始自动递增；

创建自增长约束：auto_increment

mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20));
Query OK, 0 rows affected

插入数据的时候不用管使用自增长约束的列，数据库会自动管理自增长列；

mysql> insert into student(name) values("rose");
Query OK, 1 row affected

mysql> insert into student(name) values("lily");
Query OK, 1 row affected

mysql> select * from student;
+----+------+
| id | name |
+----+------+
|  1 | rose |
|  2 | lily |
+----+------+
2 rows in set

创建一个长度为 4位的自增长列：int(4) 表示 id 的长度为 4位；zerofill：表示长度不足 4 位时补 0；

mysql> create table student(
    -> id int(4) zerofill primary key auto_increment,
    -> name varchar(20));
Query OK, 0 rows affected

插入数据时，使用自增长约束的字段自动生成的数据如下所示：

mysql> insert into student(name) values("rose");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name) values("lily");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+
| id   | name |
+------+------+
| 0001 | rose |
| 0002 | lily |
+------+------+
2 rows in set (0.00 sec)

注意：delete 删除数据时不会删除自增长约束，重新插入数据时，自增长列的数值会从之前删掉的值继续递增；

truncate 删除数据时可以删除自增长约束；

六、外键约束

约束两张表的数据；

创建一个员工表，插入 4 条数据：

mysql> create table employee(
    -> id int primary key,
    -> empName varchar(20),
    -> deptName varchar(20));
Query OK, 0 rows affected

mysql> insert into employee values(1, "rose", "软件部");
Query OK, 1 row affected

mysql> insert into employee values(2, "lily", "财务部");
Query OK, 1 row affected

mysql> insert into employee values(3, "lucy", "软件部");
Query OK, 1 row affected

mysql> insert into employee values(4, "jack", "软件部");
Query OK, 1 row affected

mysql> select * from employee;
+----+---------+----------+
| id | empName | deptName |
+----+---------+----------+
|  1 | rose    | 软件部   |
|  2 | lily    | 财务部   |
|  3 | lucy    | 软件部   |
|  4 | jack    | 软件部   |
+----+---------+----------+
4 rows in set

如上表所示，员工会有很多人，而部门很少，这样就会导致 deptName 列会有很多重复的代码，显得数据冗余；

解决办法是独立出一张部门表，将冗余的数据放到部门表中；创建部门表如下：

mysql> create table department(
    -> id int primary key,
    -> deptName varchar(20));
Query OK, 0 rows affected

mysql> insert into department values(1, "软件部");
Query OK, 1 row affected

mysql> insert into department values(2, "财务部");
Query OK, 1 row affected

mysql> insert into department values(3, "人事部");
Query OK, 1 row affected

mysql> select * from department;
+----+----------+
| id | deptName |
+----+----------+
|  1 | 软件部   |
|  2 | 财务部   |
|  3 | 人事部   |
+----+----------+
3 rows in set

创建出部门表之后，员工表的 deptName 字段就不需要了，改为部门表的 id；

存储员工数据的时候，部门信息就存储部门表的 id，根据部门 id 可以到部门表中查找具体的部门；

修改之后的员工表如下：

constraint：关键字，表示约束

employee_department_fk：外键约束的名称（employee、department 是两张表的名称，fk 是外键(foreingn key)的缩写）

foreign key：关键字，表示外键

key 里的 deptId：表示当前表（员工表）需要约束的列

references：关键字，表示参考

department(id)：表示参考的是部门表的 id 字段

constraint employee_department_fk foreign key(deptId) references department(id))：整句话的意思是声明一个约束(constraint )，约束的名字为 employee_department_fk（理论上可以随便起，但是尽量起的有意义），约束的类型为外键约束（foreign key），约束的内容为当前表的 deptId 字段的值要参考 department 表的 id 字段；

mysql> create table employee(
    -> id int primary key,
    -> empName varchar(20),
    -> deptId int,
    -> constraint employee_department_fk foreign key(deptId) references department(id));
Query OK, 0 rows affected

向 employee 表中插入数据时，deptId 的取值只能是 department 表中 id 已有的值：

mysql> insert into employee values(1, "rose", 1);
Query OK, 1 row affected

mysql> insert into employee values(2, "lily", 2);
Query OK, 1 row affected

mysql> insert into employee values(3, "lucy", 3);
Query OK, 1 row affected

mysql> insert into employee values(4, "lucy", 4);
1452 - Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `employee_department_fk` FOREIGN KEY (`deptId`) REFERENCES `department` (`id`))

注意：

1）、被约束的表称为副表（员工表），用来约束别人的表称为主表（部门表），外键约束要设置在副表中；

2）、主表的参考字段通用为主键；

3）、添加数据顺序：先添加主表数据，再添加副表数据；

4）、修改数据顺序：如果副表中有数据关联主表中的 id，则不能直接修改主表，需要先修改副表，

使其不再关联主表之后，才能修改主表；

5）、删除数据顺序：和修改的原理一样，需要先删除副表，使其不再关联主表，才能再删除主表；

级联操作

当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或者删除主表；

但是，我们希望可以直接删除或者修改主表数据，删除或修改主表数据时，自动删除或者修改副表中的数据；

可以通过级联操作实现。

级联修改：on update cascade

级联删除：on delete cascade

创建带有级联操作的外键约束：

mysql> create table employee(
    -> id int primary key,
    -> empName varchar(20),
    -> deptId int,
    -> constraint emp_dept_fk foreign key(deptId) references department(id) on update cascade on delete cascade);
Query OK, 0 rows affected

向 employee 表中插入 3条数据：

mysql> insert into employee values(1, "rose", 1);
Query OK, 1 row affected

mysql> insert into employee values(2, "lily", 2);
Query OK, 1 row affected

mysql> insert into employee values(3, "lucy", 3);
Query OK, 1 row affected

mysql> select * from employee;
+----+---------+--------+
| id | empName | deptId |
+----+---------+--------+
|  1 | rose    |      1 |
|  2 | lily    |      2 |
|  3 | lucy    |      3 |
+----+---------+--------+
3 rows in set

department 表中的数据为：

mysql> select * from department;
+----+----------+
| id | deptName |
+----+----------+
|  1 | 软件部   |
|  2 | 财务部   |
|  3 | 人事部   |
+----+----------+
3 rows in set

1）、修改 department 表中财务部的 id 为 5，可以发现，employee 表中 lily 所对应的 deptId 自动改成了 5；

mysql> update department set id=5 where id=2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from department;
+----+----------+
| id | deptName |
+----+----------+
|  1 | 软件部   |
|  3 | 人事部   |
|  5 | 财务部   |
+----+----------+
3 rows in set

mysql> select * from employee;
+----+---------+--------+
| id | empName | deptId |
+----+---------+--------+
|  1 | rose    |      1 |
|  2 | lily    |      5 |
|  3 | lucy    |      3 |
+----+---------+--------+
3 rows in set

2）、删除 department 表中 id 为 1 的数据，可以发现，employee 表中 deptId 为 1 的关联数据也自动删除了；

mysql> delete from department where id=1;
Query OK, 1 row affected

mysql> select * from department;
+----+----------+
| id | deptName |
+----+----------+
|  3 | 人事部   |
|  5 | 财务部   |
+----+----------+
2 rows in set

mysql> select * from employee;
+----+---------+--------+
| id | empName | deptId |
+----+---------+--------+
|  2 | lily    |      5 |
|  3 | lucy    |      3 |
+----+---------+--------+
2 rows in set