MySQL多表查询和事务

我是蒟蒻你别怕

已于 2022-08-29 18:39:30 修改

阅读量341

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2022-08-29 13:01:49 首次发布

本文链接：https://blog.csdn.net/qq_51591771/article/details/126578167

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

多表查询

多表关系介绍

1.一对多，多对一

可以理解是班级和学生的关系，一个班级可以有多个学生，多个学生可以选择同一个班级，要关联这两张表的话，可以在学生表上建立外键，关联班级的主键，实现一对多

总结就是：多的一方设置外键，关联的一方设置主键

2.多对多

例如：两个表相关联，互相可以多对多，此时就需要建立第三张表，这个表关联其他两个表的主键形成外键，实现多对多

3.一对一

就是把一个表给拆分成两个表，通过建立一个唯一的约束，关联另一个表的主键，形成一对一。注意要加上唯一约束unique

查询分类

1.内外连接

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

外连接

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

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

如果select * from emp, dept就会出现笛卡尔积现象，就是emp数据的条数乘dept数据的条数的数据条数

其实本质上来说，左外连接和右外连接主要就是看哪个是主表，左外是左侧，右外是右侧，本质都是完全显示主表的内容，就是如果有个主表没有查到数据，也会给上null的默认

#内连接 隐式内连接比显示内连接执行效率高

#隐式内连接
select e.name, d.name from emp e, dept d where emp.id = dept.id

#显式内连接
select e.name,d.name from emp e inner join dept d on e.id = d.id

#inner可省略
select e.name, d.name from emp e join dept d on e.id = d.id


#外连接 outer可省略

#左外连接 查询所有emp表的数据，和对应的部门信息
select e.*, d.name from emp e left outer join dept d on e.id = d.id
select e.*, d.name from emp e left join dept d on e.id = d.id

#右外连接 查询所有dept表的数据，和对应的员工信息
select d.*, e.* from emp e right outer join dept d on e.id = d.id
select d.*, e.* from emp e right join dept d on e.id = d.id

2.自连接

#自连接

#查询员工及其所属领导的名字
select a.name, b.name from emp a, emp b where a.managerid = b.id


#查询所有员工emp 及其领导的名字emp,如果员工没有领导也要查出来
select a.*, b.name left join emp a on emp b where a.managerid = b.id

3.联合查询

字段长度和类型要一致，union可以合并两个不同的表，前提是字段和类型一致，or则只能查询一个表

#将薪资低于5k和年龄大于50的员工查询出来

直接or也行，不过是练习

#重复去重
select *  from emp where salary < 5000
union all
select * from emp where age > 50

#重复不去重
select *  from emp where salary < 5000
union
select * from emp where age > 50

4.子查询

标量子查询

注意：不要想着用group by聚合出销售部的组，因为聚合之后，只有聚合函数和你聚合的字段有意义，拿不到想要的员工信

#标量子查询
#查询销售部的所有员工信息

#a
select id from dept where name = "销售部";

#b
select * from emp where id = (select id from dept where name = "销售部");


#查询在东方白入职之后的员工信息

#a
select entryData from emp where name = "东方白";

select * from emp where entryData > (select entryData from emp where name = "东方白");

列子查询

#列子查询
#查询销售部和市场部的员工信息

#a
selct id from dept where name = "销售部" or name = "市场部";

#b
select * from dept where dept_id in (selct id from dept where name = "销售部" or name = "市场部")


#查询比所有财务部人工资都高的员工信息

select max(salary) from dept where name = "财务部";

select * from dept where emp.salary > (select max(salary) from dept where name = "财务部");

#按照知识规则就是
select id from dept where name = "财务部";

select salary from emp where deptid = (select id from dept where name = "财务部");

select * from emp where salary > all(select salary from emp where deptid = (select id from dept where name = "财务部"));

行子查询

#查询宇张无忌薪资和直属领导相同的员工

select salary, managerid from emp where name = "张无忌";

#本来
select * from emp where salary = 125000 and mangerid = 1;

#所以
select * from emp where (salary, mangerid) = (select salary, managerid from emp where name = "张无忌");

表子查询

练习

#1
select e.name, e.age, e.job, d.name  from emp e ,dept d where e.dept_id = d.id; 

#2
select e.name, e.age, e.job, d.name  from emp e join dept d on e.dept_id = d.id where e.age <= 30;

#3
selecct disthinct d.id, d.name from emp e, dept d where dept_id = d.id

#4
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where age > 40;

#5
select e.* s.grade from emp e, salgrade s where e.salary >= s.local and e.salary <= s.lisal;

#6
select id from dept where name = "研发部";
select * from emp e where dept_id = (select id from dept where name = "研发部");
select e.*, s.grade from (select * from emp e where dept_id = (select id from dept where name = "研发部")) e, salgrade s where e.salary >= s.local and e.salary <= s.lisal;

#7
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = “研发部”;

#8
简单。。

#9
简单。。

#10

看一下分析

事务

我理解的事务是一组操作的集合，这组操作集合同时向系统提交，这些操作要么同时成功，要么同时失败。

事务在MySQL中是默认开启的。每执行一条DML语句，就会默认提交事务。

#1默认提交 0手动提交

select @@autocommit


#方式1
set @@autocommit = 0;


#手动提交需要commit
commit

#出错后
rollback(); 回滚事务

#方式2
start transaction/begin 开启事务

commit 提交

rollback 回滚

四大特性

持久性: 事务最终会提交持久化到磁盘中，具体可以去本地数据库Data文件下的数据库文件下查看。

并发事务描述

个人理解：

AB事务并发执行

脏读就是例如A事务先执行了查询操作，然后修改操作，但未提交数据，此时的B事务查询数据库，查询到了A事务未提交的修改操作

不可重复读：可以理解是，A事务中执行了两次查询操作，在此期间，B事务进行了修改操作并提交事务，导致A事务两次查询的结果不同。

幻读：A事务执行了查询和插入操作，查询并没有该数据，但此时B事务，插入了数据并提交事务，此时A执行插入操作报错。

事务隔离级别

需要权衡安全性和并发性

oracal默认是Read ccommitted

select @@transaction_isolation; //高版本

select @@tx_isolation; //低版本


set session TRANSACTION ISOLATION LEVEL read UNCOMMITTED; //设置隔离级别

session/global 当前窗口/全局

serilizable 锁机制解决并发？