＜四＞从0基础到中级DBA（MySQL篇）-----------＞多表查询与事务

@—笨小孩—@

已于 2022-04-02 22:37:45 修改

阅读量692

点赞数 8

分类专栏： 0基础学习DBA 文章标签： mysql dba sql

于 2022-03-25 20:35:27 首次发布

本文链接：https://blog.csdn.net/m0_57588393/article/details/123707826

版权

0基础学习DBA 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、多表查询

1. 多表关系

1.1 一对多（多对一）

在多表关系中的一对多关系中，

实例：部门表与员工表

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

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

1.2 多对多

多对多关系，

实例：学生表与课程表

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

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

1.3 一对一

一对一关系

实例：用户表与用户详情表

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

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

2. 概述

员工表（10条数据） ------------- 部门表（5条数据）

在查询时，如果直接
select * from emp , dept ;
会出现10 * 5 =50 条数据------------> 笛卡尔积 =====> A集合与B集合所有的组合情况

======> 在多表查询时，我们需要消除无效的笛卡尔积，这是就需要添加条件

======>条件：外键（dept_id）“dept_id=dept.id”

-----------> 若 dept_id 为空（null），则不能返回值

3. 多表查询分类

3.1连接查询

         内连接： ---------->交集部分========> 查员工名和其所在的部门名

                        隐式内连接
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 表A 别名B on 条件;
联合查询 ========> 把多次查询的结果合并起来，形成一个新的查询结果集关键词有：union union all
select 字段 from 表A
union (all)
select 字段 from 表B
========>1. 对于联合查询的多张表的列数必须保持一致，字段类型也要保持一致

2. union 会对合并之后的数据去重

union all 会将全部的数据直接合并在一起

3.2 子查询

子查询：SQL语句中嵌套select 语句，成为嵌套查询，又称子查询！
select 字段 from 表1 where column1=(select column1 fro 表2)
标量子查询-------->子查询结果为单个值（数字，字符串，日期等）

常用操作符：=、<>、>、<、>=、<=

列子查询---------->子查询结果为一列，也可以是多行

常用操作符： in，not in ， any，some ， all

any / some 满足任意一个即可 all 均需满足

行子查询---------->子查询结果为一行，也可以是多列

常用操作符： =，<> , in ， not in

表子查询--------->子查询结果为多行多列

常用操作符： in

当查询较复杂时，首先列出来：1. 表结构 2. 连接条件 3. 查询条件

二、事务

1. 事务概述

事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

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

例如：张三向李四转账1000元

步骤：1. 查询张三账户余额

2. 张三账户余额 - 1000

3. 李四账户余额 + 1000

2. 事务操作

2.1 手动提交事务

-- 查询事务状态
select @@autocommit ;
-----> 自动为1   手动为0
-- 修改事务为手动提交
set @@autocommit = 0;
-- 提交事务
commit;
-- 回滚事务
rollback;

2.2 开启事务

-- 开启事务
start transaction;
-- 提交事务
commit;
-- 回滚事务
rollback;

3. 事务四大特性(ACID)

3.1 原子性(Atomicity）

事务是不可分割的最小的操作单元，要么全部成功，要么全部失败

3.2 一致性(Consistency)

如转账，不管转账成功与否，总金额时恒定不变的

3.3 隔离性 (Isolation)

依赖于数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

事务A与事务B同时运行二者不会互相性影响

3.4 持久性(Durability)

事务一旦提交或回滚，它对数据库中的数据的改变是永久的

最终数据是存在磁盘里的，故为持久的

4. 并发事务的问题

4.1 脏读

脏读：一个事务读到另一个事务还未提交的数据

4.2 不可重复读

不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同

4.3 幻读

幻读：一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时又发现这行数据已将崔在，好像出现了“幻影”

5. 事务隔离级别

----------> 解决并发事务问题

---------------------------------------（× 代表不出现 √ 代表出现）---------------------------------------

隔离级别脏读不可重复读幻读
read uncommitted √ √ √
read committed × √ √
repeatable read × × √
serializable × × ×

read uncommitted 读未提交

read committed 读提交

repeatable read 可重复读---------> MySQL 的默认隔离级别

serializable 串行（xing）化

依次往下，性能逐渐变差，安全性逐渐变高

此现象可用命令行来演示

隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

-- 查看事务级别
select @@transaction_isolation;
-- 设置事务隔离级别
set [session / global] transaction isolation level {read uncommitted / read committed / repeatable read / serializable};
session---> 绘画级别------>针对当前客户端窗口有效

global ----> 针对所有客户端绘画窗口有效