MySql : 三、 多表查询和事务
MySql
前言
本篇主要介绍了数据库中多表查询以及事务相关的知识。
目录
一、多表查询
二、子查询
三、事务
一、多表查询
1.1 多表查询的分类
多表查询
- 内连接
- 隐式内连接
- 显示内连接
- 外连接
- 左外连接
- 右外连接
1.2 内连接
内连接的概念：
用左边表的记录去匹配右边表的记录，如果符合条件的则显示
1.2.1 隐式内连接
看不到 JOIN 关键字，条件使用 WHERE 指定
SELECT 字段名 FROM 左表, 右表 WHERE 条件
例如：
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id`= t2.`id`;
1.2.2 显式内连接
使用 INNER JOIN ... ON 语句, 可以省略 INNER
SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
例如：
SELECT * FROM emp t1 JOIN dept t2 ON t1.`dept_id`=t2.`id`;
1.3 外连接
1.3.1 左外连接
用左边表的记录去匹配右边表的记录(左表的记录会全部显示出来)，如果符合条件的则显示；
否则，显示 NULL
可以理解为：
在内连接的基础上保证左表的数据全部显示。
查询的是左表所有数据以及其交集部分。
使用 LEFT OUTER JOIN ... ON，OUTER 可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
例如：
-- 使用左外连接查询
-- 左外连接查询能保证左表(dept )中的数据全部显示出来
SELECT * FROM dept t1 LEFT JOIN emp t2 ON t1.`id`=t2.`dept_id`;
1.3.2 右外连接
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；
否则，显示 NULL
可以理解为：
在内连接的基础上保证右表的数据全部显示
使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
二、子查询
2.1 子查询的概念
- 一个查询的结果做为另一个查询的条件
- 有查询的嵌套，内部的查询称为子查询
- 子查询要使用括号
例如：
-- 查询市场部中有哪些员
SELECT * FROM emp WHERE emp.dept_id=(SELECT id FROM dept WHERE dept.`name`='市场部');
2.2 子查询结果的三种情况
2.2.1 子查询的结果是单行单列
子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，
父查询使用：比较运算符，如：> 、<、<>、= 等
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
例如：
-- 查询工资最高的员工是谁
SELECT t1.`name`,t1.`salary` FROM emp t1 WHERE t1.`salary`=(SELECT MAX(salary) FROM emp);
--查询工资小于平均工资的员工有哪些
SELECT t1.name ,t1.`salary` FROM emp t1 WHERE t1.`salary`<(SELECT AVG(salary) FROM emp);
2.2.2 子查询的结果是多行单列
子查询结果是单例多行，结果集类似于一个数组
父查询使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
例如：
--查询工资大于 4000 的员工，来自于哪些部门的名字
SELECT t1.name FROM dept t1 WHERE t1.`id` IN (SELECT t2.dept_id FROM emp t2 WHERE t2.`salary`>4000);
-- 查询开发部与财务部所有的员工信息
SELECT * FROM emp t1 WHERE t1.`dept_id` IN (SELECT t2.id FROM dept t2 WHERE t2.`name` IN ('开发部','财务部'));
2.2.3 子查询的结果是多行多列
子查询结果只要是多列，肯定在 FROM 后面作为表
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
例如:
-- 查询出 2011 年以后入职的员工信息，包括部门名称
SELECT * FROM dept t1, (SELECT * FROM emp WHERE join_date > '2011-1-1') t2 WHERE t2.dept_id = t1.id;
-- 也可以使用表连接
SELECT * FROM emp t1 JOIN dept t2 ON t1.`dept_id`=t2.`id` WHERE t1.`join_date`>'2011-1-1';
SELECT * FROM emp t1 JOIN dept t2 ON t1.`dept_id`=t2.`id` AND t1.`join_date`>'2011-1-1';
2.2.4 子查询小结
- 子查询结果只要是单列，则在 WHERE 后面作为条件
- 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询
三、事务
3.1 什么是事务
在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。
转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。
事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。
MYSQL 中可以有两种方式进行事务的操作：
- 手动提交事务
- 自动提交事务
3.2 手动提交事务
手动提交事务的 SQL 语句：
- start transaction ：开启事务
- commit ：提交事务
- rollback ：回滚事务
手动提交事务使用过程：
- 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
- 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。
如果事务中 SQL语句有问题，rollback 回滚事务，会回退到开启事务时的状态。
3.3 自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕
自动提交事务，MySQL 默认开始自动提交事务
3.4 事务原理
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表
中，其他任何情况都会清空事务日志(rollback，断开连接)
3.5 事务的四大特征
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
- 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
- 隔离性：多个事务之间。相互独立。
- 一致性：事务操作前后，数据总量不变。
注：由于本人水平有限，所以难免会有理解偏差或者使用不正确的问题。如果小伙伴们有更好的理解或者发现有什么问题，欢迎留言批评指正~
