更加深入Mysql-04-MySQL 多表查询与事务的操作

多表查询

有时我们不仅需要一个表的数据，数据可能关联到俩个表或者三个表，这时我们就要进行夺标查询了。

1. 数据准备：

创建一个部门表并且插入数据：

create table dept(
	id int primary key auto_increment,
	`name` varchar(20)
)	
insert into dept (name) values ('开发部'),('市场部'),('财务部'); 

创建一个员工表并且插入数据：

create table emp (
	id int primary key auto_increment,
	name varchar(10),
	gender char(1),		-- 性别
	salary double,	-- 工资
	join_date date,	-- 入职日期
	dept_id int,
	foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
)

2. 查看数据信息。

select * from emp;
select * from dept;

3. 查询员工以及其部门的全部信息

select * from emp,dept where emp.dept_id = dept.id;

这里我们可以看到我们查询出emp表以及dept表俩个表中的数据了，但是其中存在冗余数据，因此我们要查询出我们所需要的具体数据来提高我们的查询数据。

4. 查询员工的姓名及其部门名称

select emp.name 员工姓名,dept.name 部门名称 from emp,dept where emp.dept_id = dept.id;

5. 通过起别名的方式来使sql代码简单

select e.name 员工姓名,d.name 部门名称 from emp e , dept d where e.dept_id = d.id;

内连接

用左边表的记录去匹配右边表的记录

隐式内连接

看不到 JOIN 关键字，条件使用 WHERE 指定
语法：

SELECT 字段名 FROM 左表, 右表 WHERE 条件

示例：

select e.name 员工姓名,d.name 部门名称 from emp e , dept d where e.dept_id = d.id;

显示内连接

使用 INNER JOIN ... ON 语句, 可以省略 INNER
语法：

SELECT 字段名  FROM 左表  [INNER] JOIN 右表  ON 条件

示例：

1. 查询员工的全部信息

select * from emp inner join dept on emp.dept_id = dept.id;

2. 查询员工姓名为猪八戒的全部信息，只要员工姓名和部门名称信息

select e.name as 员工名称,d.name as 部门名称 from emp e inner join dept d on e.dept_id = d.id and e.name = '猪八戒'

外连接

左外连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
语法：

 SELECT 字段名  FROM 左表  LEFT [OUTER] JOIN 右表  ON 条件

1. 数据准备
   在部门表中增加一个销售部

insert into dept (name) values ('销售部');

2. 使用左外连接查询（保证左表的数据全部显示）

select * from dept left outer join emp on dept.id = emp.dept_id;

这里我们可以看到部门表的信息全部显示了，但是右表中没有匹配的数据则全部为null。

右外连接

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
语法：

SELECT 字段名  FROM 左表 RIGHT [OUTER ]JOIN 右表  ON 条件

1. 数据准备

insert into emp values (null, '沙僧','男',6666,'2013-12-05',null); 

2. 使用右外连接查询（在内连接的基础上，保证右表的数据全部显示）

select * from dept right outer join emp on dept.id = emp.dept_id;

子查询

• 一个查询的结果做为另一个查询的条件
• 有查询的嵌套，内部的查询称为子查询
• 子查询要使用括号

子查询结果：

• 单行单列：子查询结果只要是单行单列，肯定在 WHERE 后面作为条件
• 多行单列: 结果集类似于一个数组，父查询使用 IN 运算符
• 多行多列: 子查询结果只要是多列，肯定在 FROM 后面作为表

示例：
**单行单列：**查询最低工资的员工信息

select * from emp where salary = (select min(salary) from emp)

**多行单列：**查询员工工资低于6000的部门名称

select name from dept where id in (select id from emp where salary < 6000)

**多行多列：**查询出 2011 年以后入职的员工信息，包括部门名称

select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

事务

要么成功都成功，要么失败都失败

功能	SQL 语句
开启事务	start transaction;
提交事务	commit;
回滚事务	rollback;

1. 数据准备

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);

INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);

2. 手动事务提交
   过程图解：
   
   示例（成功）：

start transaction;

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

commit;

当我们开启事务之后，对张三和李四账户进行金额修改的时候，我们可以
看到金额都为1000，并没有发生改变，但是当我们进行提交的时候，账户的金额发生了改变。

示例（失败）： 恢复数据张三和李四分别拥有1000元

-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

rollback;

当我们开启事务之后，对张三和李四账户进行金额修改，我们可以看到金额都为1000，并没有发生改变，我们使用事务的回滚，账户金额依然没有变。

3. 自动提交事务
   MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务

事务原理（图解）：
事务在开启之后，所有操作都会被保存到日志文件当中，事务日志只有在得到commit命令才会同步到数据表中，其他任何情况都会清空事务日志(rollback，断开连接)

事务隔离级别

事务特性	含义
原子性（Atomicity）	每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。
一致性（Consistency）	事务在执行前数据库的状态与执行后数据库的状态保持一致。
隔离性（Isolation）	事务与事务之间不应该相互影响，执行时保持隔离的状态。
持久性（Durability）	一旦事务执行成功，对数据库的修改是持久的。就算关机，也是保存下来的。

• MySQL 数据库有四种隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle 和 SQL Server
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

欢迎java热爱者了解文章，作者将会持续更新中，期待各位友友的关注和收藏。。。