多表查询、事务(MySQL笔记第三期)

p.s.这是萌新自己自学总结的笔记，如果想学习得更透彻的话还是请去看大佬的讲解

多表关系

在项目开发中，进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在各种联系，基本上分为三种：一对一、一对多(多对一)、多对多
.
一对多(多对一)：比如部门和员工的的关系，一个部门对应多个员工，一个员工对应一个部门；
实现：在 多(员工) 的一方建立外键，指向 一(部门) 的一方的主键

.
多对多：比如学生与课程的关系，一个学生可以学多门课程，一门课程也可被多个学生选择；
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

.
一对一：比如用户和用户详情的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率；
实现：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的(UNIQUE)

多表查询

多表查询，即从多张表中查询数据

-- 表结构参照
create table worker(id int comment '编号',workername varchar(15)      comment '工人名字',gender     char             comment '性别',age        tinyint unsigned comment '年龄',idcard     char(18)         comment '身份证号',entrydate  date             comment '入职时间',dep_id     int              comment '部门编号',constraint fk_worker_dep_id foreign key (dep_id) references department(dep_id)
)comment '员工表';create table department(dep_id int primary key auto_increment comment '部门编号',dep char(3) comment '部门'
)comment '部门表';-- 数据参考
insert into department values (1,'销售部'),(2,'采购部'),(3,'管理部');insert into worker values (01,'张三','男',18,1234567,'2024-06-03',1),(02,'李四','女',26,1145411,'2015-11-21',2),(03,'王五','男',30,1152612,'2024-08-26',3),(04,'赵六','女',19,1223653,'2006-01-03',2),(05,'张二狗','男',32,1598764,'2020-11-19',1),(06,'李二','男',17,1965312,'2004-05-18',3),(07,'王三','女',35,2265498,'2024-06-03',null);

如果我们单纯的同时查询两张表，如：select * from worker,department;这种情况会显示(表A数据条数*表B数据条数)条数据，且无法看出员工对应的是哪个部门;
这种情况称为笛卡尔积
笛卡尔积：指在数学中，两个集合 A集合和B集合的所有组合情况。
所以在多表查询的时候，我们需要消除无效的笛卡尔积
比如这种：select * from worker,department where worker.dep_id = department.dep_id;

多表查询分类：连接查询(内连接、外连接[左外连接、右外连接]、自链接)、子查询

内连接

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

隐式内连接：SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

显式内连接：SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

举例

-- 查询每一个员工的姓名，及关联部门的名字(隐式内连接)
select workername,dep from worker,department where worker.dep_id = department.dep_id;
-- 使用别名简化(使用别名后就不能使用表名调用字段了)
select workername,dep from worker w,department d where w.dep_id = d.dep_id;
-- 此时不会显示王二的信息-- 查询每一个员工的姓名，及关联部门的名字(显式内连接)
select workername,dep from worker w inner join department d on w.dep_id = d.dep_id;
-- 此时不会显示王二的信息

外连接

左外连接

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

语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件...;

右外连接

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

语法：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件...;

举例

-- 查询员工表的信息，及关联部门的名字(左外连接)
select w.*,dep from worker w left outer join department d on w.dep_id = d.dep_id;
-- 此时能查询到王二的信息-- 查询部门表的信息，及关联员工的名字(右外连接)
select workername,d.* from worker w right outer join department d on w.dep_id = d.dep_id;
-- 此时不能查询到王二的信息

自连接

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

语法： SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

自连接查询，可以是内连接查询，也可以是外连接查询

举例

-- 查询员工及所属领导的名字
select a.workername,b.workername from worker a,worker b where a.managerid = b.id;-- 查询员工及所属领导的名字,如果员工没有领导，也需要查询出来
select a.workername,b.workername from worker a left join worker b on a.managerid = b.id;

联合查询

联合查询，即把多次查询的结果合并起来，形成一个新的查询结果集
语法

SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;

举例

-- 将年龄大于20，idcard为2开头的员工全部查询出来
select workername from worker where age>20
union all
select workername from worker where id like '2%';-- 将年龄大于20，idcard为2开头的员工全部查询出来(去重)
select workername from worker where age>20
union
select workername from worker where id like '2%';

注意：联合查询多张表的字段列表数量和类型必须一致;union all会把所有的数据合并在一起，而union会对合并后的数据进行去重

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，即子查询
语法：SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询外部的语句可以是增删改查的任意一种

根据查询结果不同，子查询分为：
标量子查询(子查询结果为单个值)
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询子查询结果为多行多列)

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

标量子查询

标量子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式
常用操作符:= <> > >= < <=

举例

-- 查询销售部的员工信息-- a.查询销售部的ID(子查询)select dep_id from department where dep='销售部';-- b.根据销售部ID查询员工信息select * from worker where dep_id=1;-- 整合
select * from worker where dep_id = (select dep_id from department where dep='销售部');-- ------------------------------------------------------------------------------------------ 查找在李四入职后的员工信息-- a.查询 李四 的入职日期(子查询)select entrydate from worker where workername='李四';-- b.查询指定入职日期之后入职的员工信息select * from worker where entrydate>'2015-11-21';-- 整合
select * from worker where entrydate>(select entrydate from worker where workername='李四');

上面的两条子查询语句返回的都是单个值，所以为标量子查询

列子查询

列子查询返回的结果是一列(也可以是多行)
常用操作符：IN 、NOT IN、ANY、SOME、ALL

举例

-- 查询销售部和管理部的员工信息-- a.查询销售部和管理部的ID(子查询)select dep_id from department where dep='销售部'or dep='管理部';-- b.根据部门ID查询员工信息select * from worker where dep_id in (1,3);-- 整合
select * from worker where dep_id in (select dep_id from department where dep='销售部'or dep='管理部');-- --------------------------------------------------------------------------------------------------- 查询比采购部所有员工年龄都高的员工信息-- a.查询采购部所有员工的年龄(子查询)select dep_id from department where dep='采购部';select age from worker where dep_id=2;-- 整合(子查询)select age from worker where dep_id=(select dep_id from department where dep='采购部');-- b.查询比采购部所有员工年龄都高的员工信息select * from worker where age > all (select age from worker where dep_id = (select dep_id from department where dep='采购部') );-- --------------------------------------------------------------------------------------------------- 查询比管理部任一员工年龄都高的员工信息-- a.查询管理部所有员工年龄(子查询)select age from worker where dep_id=(select dep_id from department where dep='管理部');-- b.查询比管理部任一员工年龄都高的员工信息select * from worker where age > any (select age from worker where dep_id = (select dep_id from department where dep='管理部') );

行子查询

行子查询返回的结果是一行(也可以是多列)
常用符号：=、<>、IN、NOT IN

举例

-- 查询和张三年龄及部门相同的员工信息-- a.查询张三的年龄与部门select age,dep_id from worker where workername='张三';-- b.查询和张三年龄及部门相同的员工信息select * from worker where (age,dep_id) = (select age,dep_id from worker where workername='张三');

表子查询

表子查询返回的结果是多行多列
常用符号：IN

举例

-- 查询和张三、李四性别及部门相同的员工信息-- a.查询张三、李四的性别与部门select gender,dep_id from worker where workername='张三' or workername='李四';-- b.查询和张三、李四性别及部门相同的员工信息select * from worker where (gender,dep_id) in (select gender,dep_id from worker where workername='张三' or workername='李四');-- ------------------------------------------------------------------------------------------------------------- 查询入职日期是'2007-01-01'之后的员工信息，及其部门信息-- a.查询入职日期是'2007-01-01'之后的员工信息select * from worker where entrydate>'2007-01-01';-- b.查询这部分员工对应的部门信息(将这部分员工信息作为一张临时表)select e.*,d.* from (select * from worker where entrydate>'2007-01-01') as e left join department d on e.dep_id=d.dep_id;

例题

-- 查询员工的姓名、年龄、部门信息(隐式内连接)
select w.workername,w.age,d.dep from worker w,department d where w.dep_id = d.dep_id;-- 查询年龄小于30的员工的姓名、年龄、部门信息(显式内连接)
select  w.workername,w.age,d.dep from worker w inner join department d on w.dep_id = d.dep_id where age<30;-- 查询拥有员工的部门ID、部门名称
select distinct d.dep_id,d.dep from worker w,department d where w.dep_id = d.dep_id;-- 查询年龄大于25的员工及其部门名称，如果员工没有部门也要展示出来
select w.*,d.dep from worker w left join department d on d.dep_id = w.dep_id where age > 25;-- 查询管理部的平均年龄
select avg(w.age) from worker w,department d where w.dep_id=d.dep_id and d.dep='管理部';-- 查询比平均年龄高的员工信息
select * from worker where age>(select avg(age) from worker);-- 查询低于本部门平均年龄的员工信息-- a.查询指定部平均年龄select avg(w1.age) from worker w1 where w1.dep_id=1;select avg(w1.age) from worker w1 where w1.dep_id=2;-- b.查询低于本部门平均年龄的员工信息select *,(select avg(w1.age) from worker w1 where w1.dep_id=w2.dep_id) '平均年龄' from worker w2 where age < (select avg(w1.age) from worker w1 where w1.dep_id=w2.dep_id);-- 查询所有的部门信息，并统计部门人数
select * from department;select count(*) from worker where dep_id=1;select d.*,(select count(*) from worker w where w.dep_id=d.dep_id) '人数' from department d;

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
举例说明
好处，当想执行一系列操作时，如果中间出现了异常，由于所有操作均不会被自动执行，从而保证了数据的准确

方式一

查看/设置事务提交方式

SELECT @@autocommit;
set @@autocommit=0;

提交事务:COMMIT;

回滚事务:ROLLBACK;

举例

create table account(name varchar(15) comment '姓名',money int unsigned comment '存款'
) comment '账户表';insert into account values ('张三',2000),('李四',1000);-- ------------------------------------------------------------- 查询当前事务的提交方式
select @@autocommit;-- 设置为手动提交(0为手动，1为自动)
set @@autocommit = 0;-- 转账操作(张三给李四转账1000)-- 1.查询张三账户余额select money from account where name='张三';-- 2.将张三账户余额-1000update account set money = money-1000 where name='张三';-- 3.将李四账户余额+1000update account set money = money+1000 where name='李四';-- 提交事务
commit;-- 回滚事务(撤销事务)
rollback;

方式二

开启事务:START TRANSACTION或BEGIN

提交事务:COMMIT;

回滚事务:ROLLBACK;

举例

create table account(name varchar(15) comment '姓名',money int unsigned comment '存款'
) comment '账户表';insert into account values ('张三',2000),('李四',1000);-- ------------------------------------------------------------- 查询当前事务的提交方式
select @@autocommit;-- 设置为手动提交(0为手动，1为自动)
set @@autocommit = 1;-- 开启事务
begin;-- 转账操作(张三给李四转账1000)-- 1.查询张三账户余额select money from account where name='张三';-- 2.将张三账户余额-1000update account set money = money-1000 where name='张三';-- 3.将李四账户余额+1000update account set money = money+1000 where name='李四';-- 提交事务
commit;-- 回滚事务(抛出异常时)
rollback;

事务四大特性(ACID)

原子性（Atomicity）：原子性意味着事务中的所有操作要么全部完成，要么全部不完成，不会出现只执行了部分操作的情况。如果事务在执行过程中遇到错误，所有已经执行的操作将被回滚，数据库状态回到事务执行前的状态
一致性（Consistency）：一致性确保事务从一个一致的状态转换到另一个一致的状态。在事务开始和完成时，数据库的完整性约束没有被破坏。例如，在银行转账的场景中，无论转账是否成功，账户的总金额应保持不变
隔离性（Isolation）：隔离性保证了当多个用户并发访问数据库时，数据库为每个用户的事务提供独立的工作区域。每个事务对其他事务的操作不可见，直到该事务提交。这个特性可以防止多个事务同时操作同一数据时可能产生的问题，如脏读、不可重复读和幻读
持久性（Durability）：持久性意味着一旦事务被提交，它对数据库的修改将是永久性的。即使发生系统故障，如数据库崩溃或电源故障，已提交的事务所做的修改也不会丢失

并发事务问题

隔离事务级别

为了解决并发事务问题有以下的几种隔离事务级别

(√ 表示可能出现),事务隔离级别越高，安全性越好，性能越低

查看事务隔离级别：SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别：SET [SESSION|GLOBAL] @@TRANSACTION ISOLATION LEVEL [READ UNCOMMITED | READ COMMIT | REPEATABLE READ | SERIALIZABLE];