MySQL02_约束、多表查询、事务

Lemonade22
80 阅读5分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第21天，点击查看活动详情

⭐️前面的话⭐️

✉️坚持和努力一定能换来诗与远方！
💭推荐书籍：📚《王道408》，📚《深入理解 Java 虚拟机-周志明》，📚《Java 核心技术卷》
💬算法刷题：✅力扣🌐牛客网
🎈Github
🎈码云Gitee

4. 约束（保证数据的完整性和正确性）

概述

  1. 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
  2. 目的：保证数据库中数据的正确、有效性和完整性。
  3. 分类：
约束描述关键字例如
非空约束限制该字段的数据不能为nullnot null不为空，并且唯一
唯一约束保证该字段的所有数据都是唯一、不重复的unique不为空，并且唯一
主键约束主键是一行数据的唯一标识，要求非空且唯一primary key主键，并且自动增长
默认约束保存数据时，如果未指定该字段的值，则采用默认值default如果没有指定该值，默认为1
检查约束（8.0.16版本之后）保证字段值满足某一个条件check大于0并且小于等于120
外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性foreign key

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

案例

字段名字段含义字段类型约束条件
idID唯一标识int主键，并且自动增长
name姓名varchar(10)不为空，并且唯一
age年龄int大于0并且小于等于120
status状态char如果没有指定该值，默认为1
gender性别char无
 create table tb_user(
   id int auto_increment primary key comment 'ID唯一标识',
   name varchar(10) not null unique comment '姓名',
   age int check(age>0&&age<=120) comment '年龄',
   status char(1) default('1') comment '状态',
   gender char(1) comment '性别'
 );

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

 -- 添加外键
 create table 表名(
     字段名  数据类型
   ...
   
   [constraint][外键名称] foreign key (外键字段名) references 主表(主表列名);
 )
 alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
 ​
 -- 删除外键
 alter table 表名 drop foreign key 外键名称;
  • 删除/更新行为
行为说明
no action当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与restrict 一致)
restrict当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 no action一致)
cascade当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
set null当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。
set default父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)
 alter table 表名 add constraint 外键名称 foreign key（外键字段） references 主表名( 主表字段名)  on update cascade on default cascade;

5. 多表查询（结合具体的案例）

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所

以各个表结构之间也存在着各种联系，基本上分为三种：

  • 一对多（多对一）

案例: 部门 与 员工的关系

关系: 一个部门对应多个员工，一个员工对应一个部门

实现: 在多的一方建立外键，指向一的一方的主键

image-20220331134259260

  • 多对多

案例: 学生 与 课程的关系

关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

image-20220331134402725

  • 一对一

案例: 用户 与 用户详情的关系

关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

image-20220331134527199

多表查询概述

概述: 指从多张表中查询数据

笛卡尔积: 笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)

连接查询

内连接：相当于查询A、B交集部分数据

左外连接：查询左表所有数据，以及两张表交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

交集

内连接

 -- 隐式内连接
   select 字段列表 from 表1,表2 where 条件...;
   -- 隐式内连接
   select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
 // 内连接查询的是两张表交集的部分

外连接（重点）

 -- 左外连接: 相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据
 select 字段列表 from 表1 left[outer] join 表2 on 条件...;
 -- 左外连接
 select 字段列表 from 表1 right[outer] join 表2 on 条件...;

自连接

 -- 自连接查询，可以是内连接查询，也可以是外连接查询。
 select 字段列表 from 表A 别名A join 表B 别名B on 条件...;
 ​
 -- 联合查询-union , union all：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
 select 字段列表 from 表A...
 union[all]
 select 字段列表 from 表B...
 // 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
 // union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

 select * from t1 where column1 = (select column1 from t2);
 // 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

根据子查询结果不同，分为：

  • 标量子查询（子查询结果为单个值：数字、字符串、日期等）（ 常用的操作符：= <> > >= < <= ）
  • 列子查询(子查询结果为一列) （常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL）
  • 行子查询(子查询结果为一行) （常用的操作符：= 、<> 、IN 、NOT IN）
  • 表子查询(子查询结果为多行多列)）（常用的操作符：IN）

根据子查询位置，分为：WHERE之后 、FROM之后、SELECT 之后。

多表查询案例

 1. 查询员工的姓名、年龄、职位、部门信息。
 2. 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
 3. 查询拥有员工的部门ID、部门名称。
 4. 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来。
 5. 查询所有员工的工资等级。
 6. 查询 "研发部" 所有员工的信息及工资等级。
 7. 查询 "研发部" 员工的平均工资。
 8. 查询工资比 "灭绝" 高的员工信息。
 9. 查询比平均薪资高的员工信息。
 10. 查询低于本部门平均工资的员工信息。
 11. 查询所有的部门信息, 并统计部门的员工人数。
 12. 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称

总结

多表关系

  • 一对多：在多的一方设置外键，关联一的一方的主键
  • 多对多：建立中间表，中间表包含两个外键，关联两张表的主键
  • 一对一：用于表结构拆分，在其中任何一方设置外键unique ，关联另一方的主键

多表查询

  • 内连接
 select 字段列表 from 表1,表2 where 条件...;
 ​
 select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
  • 外连接
 select 字段列表 from 表1 left[outer] join 表2 on 条件...;
  • 自连接
 select 字段列表 from 表A 别名A join 表B 别名B on 条件...;
  • 子查询
 标量子查询、列子查询、行子查询、表子查询

6. 事务（保证数据的安全性）

事务简介

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作

请求，即这些操作要么同时成功，要么同时失败。

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务操作

 -- 查看/设置事务提交方式
 select @@autocommit;
 set @@autocommit=0;
 ​
 -- 开启事务
 start transaction 或 begin;
 -- 提交事务
 commit;
 -- 回滚事务
 rollback;

事务四大特性AIDC

  • 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
  • 一致性（Consistency）：执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的。
  • 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
  • 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

问题描述
脏读一个事务读到另外一个事务还没有提交的数据。
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了

事务隔离级别

隔离级别脏读不可重复读幻读
Read uncommitted√√√
Read committed×√√
Repeatable Read（默认）××√
Serializable×××
 -- 查看事务隔离级别
 select @@transaction_isolation;
 ​
 -- 设置事务隔离级别
 set [session | global] transaction isolation level [Read uncommitted | Read committed | Repeatable Read | Serializable]

注意：事务隔离级别越高，数据越安全，但是性能越低。

总结

  1. 事务简介

事务是一组操作的集合，这组操作，要么全部执行成功，要么全部执行失败。

  1. 事务操作
 -- 开启事务
 start transaction 或 begin;
 -- 提交事务
 commit;
 -- 回滚事务
 rollback;
  1. 事务四大特性

原子性 Atomicity 、一致性 Consistency 、隔离性 Isolation 、持久性 Durability

  1. 并发事务问题

脏读、不可重复读、幻读

  1. 事务隔离级别

Read uncommitted | Read committed | Repeatable Read | Serializable

avatar
文章
阅读
粉丝