likes
comments
collection
share

Mysql数据库基础篇 - 多表查询与事务的操作

作者站长头像
站长
· 阅读数 10

一、多表查询

1.1 什么是多表查询

如下表，我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询，即我们需要查询多张表才能得到我们想要的结果。

# 创建部门表 
create table dept( 
id int primary key auto_increment,
name varchar(20) )


insert	into dept (name) values ('开发部'),('市场部'),('财务部');

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);

1.1.1 多表查询的分类：

Mysql数据库基础篇 - 多表查询与事务的操作

1.2 笛卡尔积现象

1.2.1 什么是笛卡尔积

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

需求：
查询所有的员工和所有的部门 
```sql
select * from emp,dept;

结果分析：

Mysql数据库基础篇 - 多表查询与事务的操作

1.2.2 如何清除笛卡尔积现象的影响

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要 通过条件过滤掉没用的数据。 -- 设置过滤条件 Column 'id' in where clause is ambiguous

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

-- 查询员工和部门的名字

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

1.3 内连接

用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键

1.3.1 隐式内连接

隐式内连接： 看不到 JOIN 关键字，条件使用 WHERE 指定 SELECT 字段名 FROM 左表, 右表 WHERE 条件

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

1.3.2 显式内连接

显示内连接： 使用 INNER JOIN ... ON 语句, 可以省略 INNER

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

查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能 查询出需要的数据，使用内连接

select
    e.id, --编号
    e.name,--姓名
    e.gender, --性别
    e.salary, --工资
    d.name,--部门名字 
    from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';

1.4 外连接

外连接可以分为左外连接和右外连接。

1.4.1 左外连接

左外连接：使用 LEFT OUTER JOIN ... ON，OUTER 可以省略

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件 用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证左表的数据全部显示(左表是部门，右表员工) -- 在部门表中增加一个销售部

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

-- 使用内连接查询

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

-- 使用左外连接查询

select * from dept d left join emp e on d.`id` = e.`dept_id`;

1.5 右外连接 

右外连接：使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证右表的数据全部显示 -- 在员工表中增加一个员工 -- 在员工表中增加一个员工

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

select * from emp;

-- 使用内连接查询

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

-- 使用右外连接查询

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

二、子查询

2.1 什么是子查询

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

-- 需求：查询开发部中有哪些员工 
select * from emp; 
-- 通过两条语句查询 
select id from dept where name='开发部' ; 
select * from emp where dept_id = 1; 
-- 使用子查询 
select * from emp where dept_id = (select id from dept where name='市场部');

2.2 子查询结果的三种情况：

  1. 子查询的结果是单行单列
  2. 子查询的结果是多行单列
  3. 子查询的结果是多行多列

2.3 子查询的结果是一个值的时候

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

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

2.3.1 案例：查询工资最高的员工是谁？

-- 1) 查询最高工资是多少 
select max(salary) from emp; 
-- 2) 根据最高工资到员工表查询到对应的员工信息 
select * from emp where salary = (select max(salary) from emp);

2.3.2 查询工资小于平均工资的员工有哪些？

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

2.4 子查询结果是多行单列的时候

子查询结果是单例多行，结果集类似于一个数组，父查询使用 IN 运算符

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

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

-- 先查询大于 5000 的员工所在的部门 id 
select dept_id from emp where salary > 5000;
-- 再查询在这些部门 id 中部门的名字 Subquery returns more than 1 row 
select name from dept where id = (select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

2.4.2 查询开发部与财务部所有的员工信息

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

2.5 子查询的结果是多行多列

子查询结果只要是多列，肯定在 FROM 后面作为表

SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;

子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段

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

-- 查询出 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 ;
-- 也可以使用表连接： 
select * from emp inner join dept on emp.`dept_id` = dept.`id` where join_date >='2011-1-1'; select * from emp inner join dept on emp.`dept_id` = dept.`id` and join_date >='2011-1-1';

三、事务

3.1 事务的应用场景说明

事务指的是在一个业务操作存在多个操作数据库的操作，多个数据库操作同时成功同时失败，失败后数据同时回滚。 例如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条SQL就可能执行失败。 事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

转账的操作 
-- 创建数据表 
CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); --- 添加数据 
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000); 
--模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句： 张三账号-500 李四账号+500 
-- 张三账号-500 
update account set balance = balance - 500 where name='张三'; 
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中 一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

3.2 事务提交

MYSQL 中可以有两种方式进行事务的操作：

  1. 手动提交事务
  2. 自动提交事务

3.2.1 手动提交事务

# 开启事务 
start transaction;
# 提交事务 
commit;
# 回滚事务 
rollback;

3.2.2 手动提交事务使用过程：

  1. 执行成功的情况：
    • 开启事务
    • 执行多条 SQL 语句
    • 成功提交事务
  2. 执行失败的情况：
    • 开启事务
    • 执行多条 SQL 语句
    • 事务的回滚

3.3 自动提交事务

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

3.4 事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表 中，其他任何情况都会清空事务日志(rollback，断开连接)

3.4.1 原理图：

Mysql数据库基础篇 - 多表查询与事务的操作

3.4.2 事务的步骤：

  • 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
  • 开启事务以后，所有的操作都会先写入到临时日志文件中
  • 所有的查询操作从表中查询，但会经过日志文件加工后才返回
  • 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

3.5 回滚点

3.5.1 什么是回滚点

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

3.5.2 回滚点的操作语句

Mysql数据库基础篇 - 多表查询与事务的操作

3.6 事务的隔离级别

3.6.1 事务的四大特性 ACID 事务特性 含义

Mysql数据库基础篇 - 多表查询与事务的操作

3.6.2 事务的隔离级别

事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个 数据。可能引发并发访问的问题：

Mysql数据库基础篇 - 多表查询与事务的操作

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

上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。

Mysql数据库基础篇 - 多表查询与事务的操作 隔离级别越高，性能越差，安全性越高。

3.6.4 MySQL 事务隔离级别相关的命令

  •  查询全局事务隔离级别
select @@tx_isolation;
  • 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化 设置隔离级别
set global transaction isolation level 级别字符串;

后记

喜欢我的文章的朋友点点喜欢、收藏，也欢迎朋友们评论区留下你的意见和建议，恕毅在此拜谢！

转载自:https://juejin.cn/post/7175845097500573752
评论
请登录