MySQL进阶（二）多表查询

2. 多表查询

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

• 一对多(多对一)

• 多对多

• 一对一

多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据，那么多表查询都有哪些呢？

• 连接查询

• 内连接查询 ：相当于查询AB交集数据

• 外连接查询

  • 左外连接查询 ：相当于查询A表所有数据和交集部门数据
  • 右外连接查询 ： 相当于查询B表所有数据和交集部分数据

• 子查询

2.1 内连接查询

• 语法

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

内连接相当于查询 A B 交集数据，若A中某条数据与B无关联则不查询

3.2 外连接查询

• 语法

  -- 左外连接
  SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
  
  -- 右外连接
  SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
  

  左外连接：相当于查询A表所有数据和交集部分数据
  右外连接：相当于查询B表所有数据和交集部分数据

3.3 子查询

• 概念

  查询中嵌套查询，称嵌套查询为子查询。

  什么是查询中嵌套查询呢？通过一个例子来看：

  需求：查询工资高于猪八戒的员工信息。

  来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

  select salary from emp where name = '猪八戒'
  

  第二步：查询工资高于猪八戒的员工信息

  select * from emp where salary > 3600;
  

  第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

  select * from emp where salary > (select salary from emp where name = '猪八戒');
  

  这就是查询语句中嵌套查询语句。

• 子查询根据查询结果不同，作用不同

  • 子查询语句结果是单行单列，也叫做标量子查询；
  • 子查询语句结果是多行单列，也叫做列子查询；
  • 子查询语句结果是单行多列，也叫做行子查询；
  • 子查询语句结果是多行多列，也叫做表查询。

3.3.1 标量子查询（子查询语句结果是单行单列）

--标量子查询
-- A.查询“教研部”的所有员工信息
-- a.查询教研部的部门ID - tb_dept
select id from tb_dept where name = '教研部';

-- b.再查询该部门ID下的员工信息 - tb_emp
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部')

3.3.2 列子查询（子查询语句结果是多行单列）

-- 列子查询
-- A.查询"教研部”和"咨询部"的所有员工信息
-- a查询"教研部”和"咨询部"的部门ID - tb_dept
select id from tb_dept where name = '教研部' or name = '咨询部';

-- b.根据部门工D，查询该部门下的员工信息 - tb emp
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

3.3.3 行子查询（子查询语句结果是单行多列）

-- 行子查询
-- A.查询与“韦一笑”的入职日期及职位都相同的员工信息; 
-- a.查询”韦一关”的入职目期及职位
select entrydate, job from tb_emp where name = '韦一笑';

-- b．查询与其入职目期及职位都相同的员工信息;
select from tb_emp where (entrydate , job) = (select entrydate, job from tb_emp where name = '韦一笑);

3.3.4 表子查询（子查询语句结果是多行多列）

-- 表子查润
-- A．查询入职日期是“2006-O1-01”之后的员工信息，及其部门名称 
-- a. 查询入职口期是"2006-01-01”之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';

-- b. 查询这部分员工信息及其部门名称 - tb_dept
select e.*, d.name from (select * from tb_emp where entrydate > '2006-01-01') e, tb_dept d where e.dept_id = d.id;

4，事务

4.1 概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

事务是一个不可分割的工作逻辑单元。

4.2 语法

• 开启事务

  START TRANSACTION;
  或者  
  BEGIN;
  

• 提交事务

  commit;
  

• 回滚事务

  rollback;
  

sql的执行成功并选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

4.4 事务的四大特征

• 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

• 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

• 隔离性（Isolation） :多个事务之间，操作的可见性

• 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;