MySQL的多表查询与事务

多表查询

多表查询就是指从多张表中查询数据。

原来查询单表数据，执行的SQL形式为：select * from emp;
那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ;

多张表容易出现重叠现象，这种现象称之为笛卡尔积

在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。

连接查询

• 内连接： 相当于查询A、B交集部分数据

内连接的语法分为两种: 隐式内连接、显式内连接。

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

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

表的别名:

1. table as 别名1 , tableb as 别名2 ;
2. table 别名1 , tableb 别名2 ;

注意事项: 一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

• 外连接：

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

1. 左外连接： 查询左表所有数据，以及两张表交集部分数据
   SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
   左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

2. 右外连接： 查询右表所有数据，以及两张表交集部分数据
   SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
   右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺
序就可以了。而我们在日常开发使用时，更偏向于左外连接。

• 自连接： 当前表与自身的连接查询，自连接必须使用表别名

自连接查询，顾名思义，就是自己连接自己.
对于自连接查询，可以是内连接查询，也可以是外连接查询。

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

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

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

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重处理。

注意：
如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报
错。如：

子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。
select * from t1 where column1 = ( select column1 from t2 );
子查询外部的语句可以是insert / update / delete / select 的任何一个。

分类

根据子查询结果不同，分为：

• 标量子查询（子查询结果为单个值）
• 列子查询(子查询结果为一列)
• 行子查询(子查询结果为一行)
• 表子查询(子查询结果为多行多列)

根据子查询位置，分为：

• where之后
• from之后
• select之后

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

• 列子查询
  子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
  常用的操作符：in 、not in 、 any 、some 、 all

• 行子查询
  子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
  常用的操作符：= 、<> 、in、not in

• 表子查询
  子查询返回的结果是多行多列，这种子查询称为表子查询。
  常用的操作符：in

事务

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

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。

事务操作

• 查看事务提交方式
  select @@autocommit ;

• 设置事务提交方式
  set @@autocommit = 0 ;

• 提交事务
  commit;

• 回滚事务
  rollback;

注意： 上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提
交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

• 开启事务
  start transaction 或 begin ;

• 提交事务
  commit;

• 回滚事务
  rollback;

事务四大特性

上述就是事务的四大特性，简称ACID。

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

并发事务问题

赃读： 一个事务读到另外一个事务还没有提交的数据。

B读取到了A未提交的数据。

不可重复读： 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
事务A两次读取同一条记录，但是读取到的数据却是不一样的。

幻读： 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据
已经存在，好像出现了 “幻影”。

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

• 查看事务隔离级别
  select @@transaction_isolation;

• 设置事务隔离级别
  set [ session | global ] transaction isolation level { read uncommitted |read committed | repeatable read | serializable }

注意： 事务隔离级别越高，数据越安全，但是性能越低。

》》观看黑马程序员总结笔记