MySQL多表查询、事务、DCL-03-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_45007945/article/details/103161914

本文深入讲解SQL多表查询技巧，包括内连接、左连接、右连接及子查询应用，配合事务管理和权限控制详解，助您掌握数据库操作核心技能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一节多表查询

1.1.什么是多表查询

准备sql

-- 创建部门表
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);

多表查询的作用：
比如：我们想查询孙悟空的名字和他所在的部门的名字，则需要使用多表查询。
如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

1.1.1.多表查询的分类

在这里插入图片描述

1.2.笛卡尔积现象

什么是笛卡尔积现象

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

如何清除笛卡尔积现象的影响
我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据。

1.3.内连接

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

1.3.1.隐式内连接

隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定

– 查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

1.3.2.显示内连接

显示内连接：使用 INNER JOIN … ON 语句, 可以省略 INNER
语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
例如：

– 查询所有员工信息和对应的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
– 确定查询条件，我们查询的是唐僧的信息，员工表.name=‘唐僧’
SELECT * FROM emp e JOIN dept d ON e.dept_id = d.id WHERE e.name=‘唐僧’ ;

1.4.左连接

语法： select 字段列表 from 表1 left [outer] join 表2 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 可以省略
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL 可以理解为：在内连接的基础上保证右表的数据全部显示

– 在员工表中增加一个员工
insert into emp values (null, ‘沙僧’,‘男’,6666,‘2013-12-05’,null);
– 使用内连接查询
select * from dept inner join emp on dept.id = emp.dept_id;
– 使用右外连接查询
select * from dept right join emp on dept.id = emp.dept_id;

在这里插入图片描述

1.6.子查询

1.6.1.概念：查询中嵌套查询，称嵌套查询为子查询。

– 查询工资最高的员工信息
– 1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
– 2 查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.salary = 9000;
– 一条sql就完成这个操作。子查询
SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);

1.6.2子查询不同情况

1. 子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断。运算符： > >= < <= =

– 查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查询的结果是多行单列的：

子查询可以作为条件，使用运算符in来判断

– 查询’财务部’和’市场部’所有的员工信息
SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
– 子查询
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);
3. 子查询的结果是多行多列的：

子查询可以作为一张虚拟表参与查询

– 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
– 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.join_date > ‘2011-11-11’) t2 WHERE t1.id = t2.dept_id;
– 普通内连接
SELECT * FROM emp t1,dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > ‘2011-11-11’

第二节事务

2.1. 事务的基本介绍

概念：

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

2.2. 操作：

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

2.3. 例子：

CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
-- 添加数据
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 account SET balance = balance + 500 WHERE NAME = 'lisi';
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;

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

2.4.1事务提交的两种方式：

自动提交：
mysql就是自动提交的
一条DML(增删改)语句会自动提交一次事务。
手动提交：
Oracle 数据库默认是手动提交事务
需要先开启事务，再提交

修改事务的默认提交方式：
查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
修改默认提交方式： set @@autocommit = 0;

2.5 回滚点

2.5.1 什么是回滚点

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

2.5.2 回滚点的操作语句回滚点的操作语句语句设置回滚点 savepoint 名字回到回滚点 rollback to 名字

2.6 事务的隔离级别

2.6.1 事务的四大特性ACID

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

2.6.2 事务的隔离级别

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

并发访问的问题	含义
脏读	一个事务读取到了另一个事务中尚未提交的数据
不可重复读	一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update 时引发的问题
幻读	一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题