MySQL之多表查询与事务

一、多表查询

连接查询

内连接

查询两张表的交集

• 隐式内连接

select 字段列表 from 表1，表2 where 条件...;

--查询每一个员工的姓名及关联的部门名称
select emp.name,dept.name from emp, dept where emp.dep_id = dept.id;

• 显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

--查询每一个员工的姓名及关联的部门名称
select emp.name,dept.name from emp join dept on emp.dep_id = dept.id;

外连接

• 左外连接：查询左表的所有数据，包含表1、2交集部分的数据

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

--查询emp表所有信息，和对应的部门信息
select e.* from emp e left outer join dept d on e.dep_id = d.id;

• 右外连接：查询右表的所有数据，包含表1、2交集部分的数据

select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

--查询dept表所有信息，和对应的员工信息
select d.* from emp e right outer join dept d on e.dep_id = d.id;

自连接

可以是内连接，也可以是外连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

--查询员工及所属领导编号
select * from emp a join emp b on a.managerid = b.id;
--查询员工及所属领导编号,如果员工没有领导也要查询
select a.name , b.name from emp a left join emp b on a.managerid = b.id;

联合查询

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

select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B...;

子查询

select * from t1 where column1 = (select column1 from t2);

标量子查询

• 列子查询
  常用操作符：in 、not in、any、some、all

--查询销售部和市场部的员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

行子查询

常用操作符: =、<>、in、not in

--查询与张无忌的薪资和领导相同的员工信息
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');

##1# 表子查询
常用操作符：in

--查询与“宋” “鹿”的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name = '宋' or name = '鹿');

二、事务

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

--查看/设置事务提交方式
select @@autocommit;
set @@autocommit=0;

--开启事务
start transaction 或 begin;

--提交事务
commit;

--回滚事务
rollback;

四大特性：

• 原子性：不可分割的最小操作单元
• 一致性：完成时所有数据保持一致状态
• 隔离性：不受外部并发影响
• 持久性：一旦提交或回滚，对数据库中的数据的改变是永久的

并发问题

• 脏读：一个事务读到另一个事务还没提交的数据
• 不可重复读：先后读取同一条记录，但数据不同
• 幻读：查询时不存在，但在插入时又发现这行数据已经存在

隔离级别	脏读	不可重复读	幻读
read uncommitted	是	是	是
read committed	否	是	是
repeatable read (默认)	否	否	是
serializable	否	否	否

--查看事务隔离级别
select @@transaction_isolation;
--设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted |read committed|repeatable read|serializable};