一、多表查询
连接查询
内连接
查询两张表的交集
- 隐式内连接
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};