MySql多表查询与事务

多表查询

写多表查询之前先让我们回忆一下之前写SQL中DQL查询与约束
1、排序语句：

排序语句：order by
升序：asc
降序：desc

2、聚合函数

聚合函数	作用
count	统计个数
max	最大值
min	最小值
sum	求和
avg	求平均

3、分页查询

limit 起始值从0开始，长度

4、分组查询

group by 分组列  having 过滤条件

5、约束的关键字

约束名	约束关键字
主键约束	primary key
唯一约束	unique
非空约束	not null
默认	default
外键	foreign key

自增长的关键字：

auto_increment

6、级联操作

级联操作语法	描述
on update cascade	级联更新
on delete cascade	级联删除

在数据业务当中，我们经常会遇到多表的查询，而且有时候多表查询会消除大量的冗余数据，下面我们举个小的栗子来开始多表的学习：

创建部门表
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) -- 外键，关联部门表(部门表的主键)
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

需求：查询所有的员工和所有的部门

select * from emp,dept;

这样会产生笛卡尔积，造成数据的冗余

在这里插入图片描述
这就造成了笛卡尔积：

有两个集合A,B .取这两个集合的所有组成情况，所以要完成多表查询，需要消除无用的数据

为解决这样的问题，我们可以设置过滤条件：

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

这也就是接下来我们为什么要学习内外连接，这样就会方便很多
1. 内连接查询：

隐式内连接：使用where条件消除无用数据
例子：
查询所有员工信息和对应的部门信息

 SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
 -- 查询员工表的名称，性别。部门表的名称
 SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;		
 SELECT 
 			t1.name, -- 员工表的姓名
 			t1.gender,-- 员工表的性别
 			t2.name -- 部门表的名称
 FROM
 		   emp t1,
 		   dept t2
 WHERE 
 			t1.`dept_id` = t2.`id`;

显式内连接：
语法：

 select 字段列表 from 表名1 [inner] join 表名2 on 条件

例如：

 SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
 SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

内连接查询需要注意：

 从哪些表中查询数据
 条件是什么
 查询哪些字段

2. 外连接查询：

左外连接：

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL，取左表的交集，可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工)

语法：

select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。

例子：

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

右外连接：

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL，可以理解为：在内连接的基础上保证右表的数据全部显示

语法：

select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。

例子：

 SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

3. 子查询：

子查询的概念：

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

子查询结果的三种情况：

子查询的结果是单行单列

概念：

 子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、=等
 SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

栗子：查询工资小于平均工资的员工有哪些？

 1) 查询平均工资是多少
 select avg(salary) from emp;
 2) 到员工表查询小于平均的员工信息
 select * from emp where salary < (select avg(salary) from emp);

子查询的结果是多行单列

概念：

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

栗子：查询工资大于 5000 的员工，来自于哪些部门的名字

 先查询开发部与财务部的 id
 select id from dept where name in('开发部','财务部');
 再查询在这些部门 id 中有哪些员工
 select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));

子查询的结果是多行多列

概念：

 子查询结果只要是多列，肯定在 FROM 后面作为表
 SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
 子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

栗子：查询出 2011 年以后入职的员工信息，包括部门名称

在员工表中查询 2011-1-1 以后入职的员工
select * from emp where join_date >='2011-1-1';
查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门 id 等于的 dept_id
select * from dept d, (select * from emp where join_date >='2011-1-1') e where d.`id`= e.dept_id ;

注：子查询结果只要是单列，则在 WHERE 后面作为条件
子查询结果只要是多列，则在 FROM 后面作为表进行二次查询

事务

1. 事务的基本介绍

概念：

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作：

1. 开启事务： start transaction;
2. 回滚：rollback;
3. 提交：commit;

例子借钱转账：

CREATE TABLE account (
 	id INT PRIMARY KEY AUTO_INCREMENT,
 	NAME VARCHAR(10),
 	balance DOUBLE
 );
 	-- 添加数据，初始化张三李四都是1000元	
 INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
 SELECT * FROM account;
 UPDATE account SET balance = 1000;
 
 -- 张三给李四转账 500 元
 	
 -- 0. 开启事务
       START TRANSACTION;
 -- 1. 张三账户 -500
 	
 UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
 -- 2. 李四账户 +500
 -- 在此处出错了，不会执行下一条更新语句update...
 UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
 	
 -- 发现执行没有问题，提交事务
 	COMMIT;
 	
 -- 发现出问题了，回滚事务
 	ROLLBACK;

回滚点：在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成
功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称
之为回滚点。

MySQL数据库中事务默认自动提交

事务提交的两种方式：
 		自动提交：mysql就是自动提交的，一条DML(增删改)语句会自动提交一次事务。
 		手动提交：Oracle 数据库默认是手动提交事务，需要先开启事务，再提交
修改事务的默认提交方式：
 		查看事务的默认提交方式：SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
 		修改默认提交方式： set @@autocommit = 0;

2. 事务的四大特征(ACID)：

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

3. 事务的隔离级别（了解）
概念：

多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

1. read uncommitted：读未提交                      
     产生的问题：脏读、不可重复读、幻读
2. read committed：读已提交 （Oracle）       
     产生的问题：不可重复读、幻读
3. repeatable read：可重复读 （MySQL默认）
     产生的问题：幻读
4. serializable：串行化                                     
     可以解决所有的问题

注意：

 隔离级别从小到大安全性越来越高，但是效率越来越低
 数据库查询隔离级别：select @@tx_isolation;
 数据库设置隔离级别： set global transaction isolation level  级别字符串;

演示：

set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

posted @ 2019-07-28 10:13 _SpringCloud 阅读(3) 评论(0) 编辑收藏举报来源

刷新页面返回顶部

MySql多表查询与事务

多表查询

事务

公告