多表查询
分类
- 连接查询
- 内连接:相当于查询表A和表B的交集部分数据
- 外连接:
– 左外连接:查询左表所有数据,以及两张表交集部分数据
– 右外连接:查询右表所有数据,以及两张表交集部分数据 - 自连接:当前表与自身的连接查询,自连接必须使用表别名
- 子查询:查询表A和表B的交集部分数据
内连接
- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
--注意事项:给表起了别名之后,只能通过别名.字段,不可使用表名.字段
外连接
左外连接
--查询表1(左表)的所有数据,包含表1和表2的交集数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
右外连接
--查询表2(右表)的所有数据,包含表1和表2的交集数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
自连接
--自连接查询,可以是内连接查询,也可以是外连接查询
SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 条件...;
联合查询-union,union all
--将多次查询的结果合并起来,形成一个新的查询结果集
SELECT 字段列表 FROM 表1
union [all]
SELECT 字段列表 FROM 表2;
--注意事项:多张表的列数要保持一致, 字段类型也要保持一致
--union和union all的区别:union all会将全部数据直接合并在一起,union会对合并之后的数据去重
子查询
- 概念
- SQL语句中嵌套SELECT语句,成为嵌套查询,又称子查询
- 示例
SELECT * FROM t1 WHERE column = (SELECT column1 FROM t2);
--注意事项:子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT中的任何一个
- 分类:
- 根据子查询结果不同进行分类
=》 标量子查询(子查询结果为单个值)
=》 列子查询(子查询结果为一列,可以为多行)
操作符 | 描述 |
---|---|
IN | 在指定的集合范围内,多选一 |
NOT IN | 不在指定的集合范围内 |
ANY | 子查询返回列表中,有任意一个满足即可 |
SOME | 与ANY等同,使用SOME的地方都可以使用ANY |
ALL | 子查询返回列表的所有值都必须满足 |
=》 行子查询(子查询结果为一行)
=》 表子查询(子查询结果为多行多列)
- 根据子查询位置不同进行分类,分为:WHERE之后、FROM之后、SELECT之后
事务
- 事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
事务操作
方式一:在这里插入代码片
- 查看/设置事务提交方式
SELECT @@autocommit;
--@@autocommit = 0:禁用自动提交模式,意味着必须手动开始和结束事务来控制数据的提交行为
--@@autocommit = 1:启动自动提交模式(默认值),意味着每个SQL语句都将自动提交到数据库,而无需手动开始和结束事务
SET @@autocommit = 0;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
方式二:
- 查看/设置事务提交方式
SELECT @@autocommit;
--@@autocommit = 0:禁用自动提交模式,意味着必须手动开始和结束事务来控制数据的提交行为
--@@autocommit = 1:启动自动提交模式(默认值),意味着每个SQL语句都将自动提交到数据库,而无需手动开始和结束事务
SET @@autocommit = 1;
- 开启事务,手动控制事务
START TRANSACTION 或BEGIN;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
事务四大特性ACID
- 原子性(Automicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态
- 隔离性(Isolation):数据库西戎提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,它对数据库的数据改变是永久性的
并发事务问题
问题 | 描述 |
---|---|
脏读 | 一个事务读到另外一个事务还没有提交的数据 |
不可重复读 | 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读 |
幻读 | 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影” |
事务隔离级别
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
Read uncommitted读未提交数据 | O | O | O |
Read committed读已提交数据 | X | O | O |
Repeatable read可重复读(默认) | X | X | O |
Serializable序列化 | X | X | X |
--查看事务隔离级别
SELECT @@TRANSACTION ISOLATION;
--设置事务隔离级别
--SESSION只对当前会话窗口有效
--GLOBAL对所有会话窗口都有效
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ| SERIALIZABLE}