MySQL基础7-约束

MySQL,基础 · 浏览次数 : 271

小编点评

#生成内容的排版步骤 1.首先,需要将数据分为多个表并进行数据一致性验证。 2.然后,将数据进行排版,生成内容。 3.最后,对数据进行验证,确保内容的一致性和完整性。 #示例代码 ```python #数据分成多个表并进行数据一致性验证 table1 = Table('teacher', ['id', 'name']) table2 = Table('student', ['id', 'name']) table3 = Table('subject', ['id', 'teacher_id', 'subject_id']) #数据进行排版 data = Data.load() data.sort() #数据进行验证 validate = Data.validate() validate.check() ```

正文

一、约束的基本概念

  1、概念:约束是作用于表中字段上的规则,用于限制储存在表中的数据

  2、目的:保证数据库中的数据的正确性,有效性和完整性

  3、分类

    • 非空约束(not null):限制该字段的数据不能为null
    • 唯一约束(unique):保证该字段的所有数据都是唯一,不重复的
    • 主键约束(primary key):主键是一行数据的唯一标识,要求非空且唯一
    • 默认约束(default):保存数据时,如果未指定该字段的值,则采用默认值
    • 检查约束(check 8.0以后的新约束):保证字段满足某一个条件
    • 外键约束(foreign key):用来让两张变的数据建立连接,保证数据的一致性和完整性

二、约束的案例实践

  需求1:创建一个表id、name、age、address、stu_num五个字段。

  需求2:id字段为主键,且设置为自动递增。

  需求3:name字段长度为10个字符并且不能为空。

  需求4:age字段要大于0并且小于150.

  需求5:address字段如果不设,默认为广州。

  需求6:stu_num唯一且不能为空。

mysql> create table stu_table(
    -> id int primary key auto_increment comment "id主键",
    -> name varchar(10) not null comment "姓名",
    -> age int check(age>0 && age<150) comment "年龄",
    -> address varchar(10) default "广州" comment "地址",
    -> stu_num int not null unique comment "学号"
    -> ) comment "学生表";
Query OK, 0 rows affected, 1 warning (0.03 sec)

  stu_table的表结构如下

mysql> desc stu_table;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| age     | int         | YES  |     | NULL    |                |
| address | varchar(10) | YES  |     | 广州    |                |
| stu_num | int         | NO   | UNI | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

  说明:

    • Type 是字段的类型
    • Null 是是否允许为空
    • key 是标记主键,外键和唯一的
    • Default 是该字段的默认值
    • Extra 是一些额外信息的展示

  验证1:添加一组正常数据

mysql> insert into stu_table (name, age, address, stu_num) values ("张三", 18, "深圳",10001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu_table;
+----+--------+------+---------+---------+
| id | name   | age  | address | stu_num |
+----+--------+------+---------+---------+
|  1 | 张三   |   18 | 深圳    |   10001 |
+----+--------+------+---------+---------+
1 row in set (0.00 sec)

  说明1:各个字段都复合各个字段的要求,添加正常没有问题

  验证2:添加一个name 超过10位的异常数据

mysql> insert into stu_table (name, age, address, stu_num) values ("ABCDEFGHIJK", 18, "深圳",10002);
ERROR 1406 (22001): Data too long for column 'name' at row 1

  说明2:直接报错,提示 name  data too long 太长了

  验证3:验证age 大于150的异常情况

mysql> insert into stu_table (name, age, address, stu_num) values ("李四", 152, "深圳",10003);
ERROR 3819 (HY000): Check constraint 'stu_table_chk_1' is violated.

  说明3:这里提示了一个验证错误

  验证4:验证address不填写,默认值的设置

mysql> insert into stu_table (name, age, stu_num) values ("李四", 19,10002);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_table;
+----+--------+------+---------+---------+
| id | name   | age  | address | stu_num |
+----+--------+------+---------+---------+
|  1 | 张三   |   18 | 深圳    |   10001 |
|  2 | 李四   |   19 | 广州    |   10002 |
+----+--------+------+---------+---------+
2 rows in set (0.00 sec)

  说明4:在上面的insert 语句中只设置了name,age,stu_num三个字段,所以adderss就自动设置了默认值广州

  验证5:验证stu_num字段的唯一性

mysql> insert into stu_table (name, age, address, stu_num) values ("王五", 21, "上海",10002);
ERROR 1062 (23000): Duplicate entry '10002' for key 'stu_table.stu_num'

  说明5:提示10002已经重复了

 三、外键约束介绍

  1、什么是外键

    • 首先外键是表中一个字段
    • 外键是两张表之间的纽带
    • 设置外键的表称之为子表,外键对应的表称之为父表

  2、外键的介绍

    

    说明1:《学生表》和《辅导员》表示两张相互独立的表。

    说明2:在《学生表》中的辅导员编号,和《辅导员表》中的辅导员编号是一一对应的

    说明3:这种情况下就可以通过辅导员编号这个字段将《学生表》和《辅导员表》联系起来了

    说明4:这是辅导员编号字段,就符合设置为外键的条件

    说明5:如果将《学生表》中的辅导员编号字段设置为外键,则《学生表》为子表,《辅导员表》为父表

    说明6:外键在父表中是唯一,不可重复的。

  3、多外键展示

    

    说明1:通过上图发现《学生表》中的班级id和《班级表》中的班级id也存在一一对应的关系

    说明2:班级id也符合设置外键的标准。

    说明3:例如:辅导员编号,班级id都符合外键的设置标准,所以一个表中可以有多个外键,但是每个外键对应不同的表

  4、不符合外键的展示

    

    说明1:在《学生表》班级评级字段和《班级考核与平级对照表》中的班级平级字段也存在着关系。

    说明2:但是这个班级评级字段就不存在外键的特征,因为班级评级在《班级考核与评级对照表》中不是惟一的。

    说明3:在子表中的四星,对应父表中有三种情况这样就会出现子表中的四星到底对应父表的哪一个四星的情况。

四、外键约束展示

  1、原始数据:student表结构及其数据

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

  2、原始数据:teacher表结构及其数据

mysql> select * from teacher;
+------------+--------------+
|         id | teacher_name |
+------------+--------------+
|          1 | 张三         |
|          2 | 李四         |
|          3 | 王五         |
+------------+--------------+
3 rows in set (0.00 sec)

  3、添加外键的语法

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 父表 (父表字段)on update 更新行为 on delete 删除行为

    说明1:alter table 是DML语法,修改表的意思,在之前的文章中已经介绍过

    说明2:add constraint 是添加约束的意思

    说明3:foreign key 是外键约束的关键字

    说明4:references 后面跟上父表和父表中字段

  4、需求:给student表中的teacher_id设置为teacher表的外键,并且对应id字段的数据

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher (id);
Query OK, 6 rows affected (0.22 sec)
Records: 6  Duplicates: 0  Warnings: 0

    说明1:外键一旦设置成功,将会保持子表和父表的数据一致性和完整性。

    说明2:这个时候,如果我删除《teacher》表中的id=1的张三老师,就会出错,因为,如果张三在《teacher》表中删除了,则在《student》中的辅导员编号这列数据就找不到对应的值

    说明3:从而这样就破坏了数据的完整性和一致性

mysql> delete from teacher where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mysql_test`.`student`, CONSTRAINT `fk_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`id`))

    说明1:这个时候就会提示,不能删除或者修改父表中的数据,因为有外键存在

  5、外键数据的更新和删除行为

    • no action:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有则不允许删除/更新(与restrict一致)
    • restrict:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有则不允许删除/更新(与 no action一致)
    • cascade:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有,则也删除/更新外键在子表中的记录
    • set null:当在父表中删除/更新对应记录时,首先检查该记录是否有对应的外键,如果有则设置子表中该外键的值为null,这就要求该外键记录允许null
    • set default:父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)

  6、重新创建《student》和《teacher》表并添加外键

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  1 | 张三         |
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher(id) on update cascade on delete cascade;
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

  7、验证cascade级联行为

    验证1:我修改《teacher》表中id=1的数据改为id=4

mysql> update teacher set id=4 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
|  4 | 张三         |
+----+--------------+
3 rows in set (0.01 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          4 |    98 |
|  2 | stu2     |          4 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

    说明1:这个时候我们会发现,当我修改了《teacher》表中辅导员编号的id字段是,在《student》表中teacher_id 原本等于1的也都改为了4,这就是cascade的作用

    验证2:cascade的删除行为

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
4 rows in set (0.00 sec)

    说明2:和更新一样,cascade的删除也是级联的。

  8、验证set null的更新和删除行为

    需求1:同样先删除《student》和《teacher》表然后重新建立新的表,重新建立外键约束测试

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  1 | 张三         |
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |          1 |    98 |
|  2 | stu2     |          1 |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

mysql> alter table student add constraint fk_teacher foreign key (teacher_id) references teacher(id) on update set null on delete set null;
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

    验证1:更新《teacher》表中id=1的数据,改为id=4

mysql> update teacher set id=4 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
|  4 | 张三         |
+----+--------------+
3 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |       NULL |    98 |
|  2 | stu2     |       NULL |    88 |
|  3 | stu3     |          2 |    79 |
|  4 | stu4     |          2 |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.01 sec)

    说明1:对应更新的数据都改为了null

    验证2:删除《teacher》表中id=2的数据

mysql> delete from teacher where id = 2;
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  3 | 王五         |
|  4 | 张三         |
+----+--------------+
2 rows in set (0.00 sec)

mysql> select * from student;
+----+----------+------------+-------+
| id | stu_name | teacher_id | score |
+----+----------+------------+-------+
|  1 | stu1     |       NULL |    98 |
|  2 | stu2     |       NULL |    88 |
|  3 | stu3     |       NULL |    79 |
|  4 | stu4     |       NULL |    97 |
|  5 | stu5     |          3 |    93 |
|  6 | stu6     |          3 |    86 |
+----+----------+------------+-------+
6 rows in set (0.00 sec)

    说明1:当删除数据的时候,子表中对应的外键数据也会变成了null

五、删除外键约束

  1、删除外键的语法

alter table 表名 drop foreign key 外键名称;
mysql> alter table student drop foreign key fk_teacher;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

  2、删除外键后,数据一致性和完整性的验证

mysql> delete from teacher where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from teacher;
+----+--------------+
| id | teacher_name |
+----+--------------+
|  2 | 李四         |
|  3 | 王五         |
+----+--------------+
2 rows in set (0.00 sec)

    说明1:这个时候就可以在《teacher》表中删除id=1的张三老师了,但是这样《student》表和《teacher》表的数据的一致性就破坏了

 

    

与MySQL基础7-约束相似的内容:

MySQL基础7-约束

一、约束的基本概念 1、概念:约束是作用于表中字段上的规则,用于限制储存在表中的数据 2、目的:保证数据库中的数据的正确性,有效性和完整性 3、分类 非空约束(not null):限制该字段的数据不能为null 唯一约束(unique):保证该字段的所有数据都是唯一,不重复的 主键约束(primar

【冷启动#1】实用的MySQL基础

简单安装一下MySQL Windows下(5.7.x) 本体安装 1、首先先下载安装包,名字如下: mysql-5.7.19-winx64.zip 2、配置环境变量,将解压之后的bin目录添加一下 3、在解压目录下创建my.ini文件,内容如下: [ client ] port=3306 defau

基于Canal实现MySQL 8.0 数据库数据同步

前言 服务器说明 | 主机名称 | 操作系统 | 说明 | |--|--| | | 192.168.11.82 | Ubuntu 22.04 | 主库所在服务器 | | 192.168.11.28 | Oracle Linux Server 8.7 | 从库所在服务器 | 版本说明 MySQL版本:

MySQL基础8-多表查询

一、多表关系 一对多或者多对一 案例:部门与员工的关系 关系:一个部门对应多个员工,一个员工对应一个部门(不考虑跨部门的特殊情况) 实现:在多的一方建立外键,指向一的一方的主键,这里员工表是多的的一方,部门表是一的一方 多对多 案例:学生与课程的关系 关系:一个学生可以选修多门课程,一门课程也可以供

MySQL基础9-事务基础

一、事务简介 事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有操作作为一个整体一起向系统提交或者撤销操作请求,即这些操作要么同时成功,要么同时失败。mysql的事务默认是自动提交的,也就是说,当执行一条DML语句,Mysql会立即隐式的提交事务 二、事务操作 2.1 查看事务提交方式

MySQL基础4-数据查询

一、DQL介绍 DQL全称:Data Query Language(数据查询语言),用来查询数据库中表的记录。 关键字:select 二、DQL语法 select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序

MySQL基础5-用户及权限管理

一、介绍 DCL:Data Control Language(数据控制语言),用来管理数据库用户,控制数据库的访问,权限。 二、用户管理 1、查询用户 语法: 1、use MySQL; 2、select * from user; 默认只有四个账户。 2、创建用户 语法:create user '用户

MySQL基础6-常用数据库函数

一、字符串函数 1、常见MySQL内置字符串函数 concat(s1,s2,s3,...):字符串拼接,将s1,s2,s3...等拼接成一个字符串 lower(str):将字符串str全部转为小写 upper(str):将字符串str全部转为大写 lpad(str,n,pad):左填充,将字符串pa

[转帖]第二章 MySQL的体系结构与基础管理

第二章 MySQL的体系结构与基础管理 https://www.jianshu.com/p/6d017ac5e685 本课程,适合具备一定Linux运维或者开发基础的朋友,课程定级中、高级DBA。只要掌握80%,轻松助力薪资15k-25K。课程内容均来自与MySQL官网+MySQL源码。配套精品视频

我在前端写Java SpringBoot项目

本篇文章主要是使用 NestJs + Sequelize + MySQL 完成基础运行, 带大家了解 Node 服务端的基础搭建,也可以顺便看看 Java SpringBoot 项目的基础结构,它俩真的非常相似,不信你去问服务端开发同学。