多表查询和事务

1、概述

1.1 介绍

多表查询就是指从多张表中查询数据。 执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ; 查询结果中包含了大量的结果集，这其实就是两张表所有记录的所有组合情况，这种现象称之为笛卡尔积。 在多表查询中，需要消除无效的笛卡尔积的，只保留两张表关联部分的数据，给多表查询加上连接查询的条件即可：where

1.2 分类

1. 连接查询

   1. 内连接：相当于查询交集部分数据

   2. 外连接

      1. 左外连接：查询左表数据以及交集

      2. 右外连接：查询右表数据以及交集

2. 子查询

2、内连接

内连接查询的是两张表交集部分的数据。(也就是 A∩B 部分的数据)

2.1 语法

隐式内连接：select 字段列表 from 表1, 表2 where 条件...;

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

表的别名 table A as 别名1, tableB as 别名2; tableA 别名1, tableB 别名2; 注意：一旦为表起了别名，就不能再使用表名来指定对应字段，只能使用别名

2.2 案例：

-- 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
    select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
    select e.name,d.name from emp e , dept d where e.dept_id = d.id; -- 别名
​
-- 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)
    select e.name, d.name from emp e inner join dept d  on e.dept_id = d.id;
    select e.name, d.name from emp e join dept d  on e.dept_id = d.id; -- 简化

3、外连接

外连接分为左外连接和右外连接

3.1 语法

左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件...; 左外连接包含左表所有数据和表1表2交集的数据

右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件...; 右外连接相当于查询右表所有数据和表1表2交集的数据

注意： 左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。日常开发使用时，更偏向于左外连接。 on就是过滤笛卡尔积，其他条件用where

3.2 案例

-- 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)（要查询emp的所有数据）
    select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
    select e.*, d.name from emp e left join dept d on e.dept_id = d.id;
​
-- 查询员工表 所有 部门的名称, 和对应的员工名称 (右外连接)
    select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
    select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

4、子查询

4.1 介绍

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

分类： 根据子查询结果不同，分为： 标量子查询（子查询结果为单个值） 列子查询（子查询结果为一列） 行子查询（子查询结果为一行） 表子查询（子查询结果为多行多列）

根据子查询位置，分为： WHERE之后 FROM之后 SELECT之后

4.2 标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。 常用的操作符：=  <>  >   >=   <  <=

4.3 列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。 常用的操作符：IN 、NOT IN

4.4 行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询（(...,...)=(...,...)多个值比较可以用括号）。 常用的操作符：= 、<> 、IN 、NOT IN 

4.5 表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询（如果子查询要当作表用，一定要给个别名）。 常用的操作符：IN

二、事务

1、问题

删除部门和删除部门员工要一起成功，但是删除员工失败了，造成了数据的不一致

2、介绍

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。 在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。 默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句时，MySQL会立即隐式的提交事务。

3、操作

1. 开启事务：START TRANSACTION 或 BEGIN;

2. 提交事务：COMMIT;

3. 回滚事务：ROBACK;

如果执行成功，则提交事务，执行如下语句：commit; 如果执行失败，则回滚事务，执行如下语句：rollback;

4、四大特性：

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。