mysql多表查询&事务

最新推荐文章于 2023-06-06 15:18:53 发布
最新推荐文章于 2023-06-06 15:18:53 发布
阅读量251 收藏 1
点赞数
分类专栏： mysql表查询 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_53347907/article/details/114001160
版权
本文详细介绍了MySQL中的多表查询，包括笛卡尔积、内连接、外连接和子查询，以及如何处理多表查询的案例。此外，还讲解了事务的基本概念、四大特性、隔离级别，并通过实例展示了事务在并发操作中的重要性。最后，提到了用户权限的DCL语句，如创建、查看、授权、撤销和删除用户。
摘要由CSDN通过智能技术生成

mysql多表查询&事务

一、课程目标

1. 【了解】什么是多表查询
2. 【掌握】多表查询的语法
3. 【掌握】多表查询的案例
4. 【了解】DCL语句
5. 【理解】什么是事务
6. 【掌握】事务的四大特征
7. 【理解】事务的隔离级别

二、多表查询

2.1 多表查询概述

同时查询多张表获取到需要的数据组成完整的信息返回给用户。

2.2 多表查询语法

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组之后的条件
order by
	排序
limit
	分页限定

2.3 数据准备

-- 多表查询
create database db3;
use db3;
-- 创建部门表（主表）
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),   -- 性别（sex）
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT -- 外键字段
);

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);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('沙僧','男',6666,'2017-03-04',null);

2.4 多表查询分类

2.4.1 笛卡尔积

多张表的记录进行组合，这种现象称为笛卡尔积(交叉连接)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hOjJjais-1614078868193)(assets/image-20210109223002161.png)]

  • 语法
    select ... from 左表,右表;
  • sql演示
    -- 查询二张表
SELECT * FROM emp,dept;
SELECT COUNT(*) FROM emp,dept;

2.4.2 内连接

拿左表的记录去匹配右表的记录，若符合条件显示（二张表的交集）

  • 语法
    -- 隐式内连接
select ... from 左表,右表 where 连接条件;

    -- 显示内连接【推荐】
select ... from 左表 [inner] join 右表 on 连接条件;
  • sql演示
    -- 隐式内连接
SELECT * FROM emp e,dept d WHERE e.dept_id = d.id;

-- 显示内连接
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
  • 案例

    查询唐僧的 id，姓名，性别，工资和所在部门名称

    -- 1.确定查询表
SELECT * FROM emp e INNER JOIN dept d;
-- 2.确定连接条件
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
-- 3.确定显示字段
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
-- 4.确定业务条件
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 = '唐僧';

2.4.3 外连接

  • 左外连接

    展示左表全部，再去匹配右表记录，若条件符合显示，若条件不符合显示NULL

    语法

    select ... from 左表 left [outer] join 右表 on 连接条件;
  • 右外连接

    展示右表全部，再去匹配左表记录，若条件符合显示，若条件不符合显示NULL

    语法

    select ...from 左表 right [outer] join 右表 on 连接条件;
  • sql演示
    # 左外连接（推荐）
-- 查询所有员工信息及对应的部门名称
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.dept_id = d.id;
-- 查询所有部门及对应的员工信息
SELECT * FROM dept d LEFT JOIN emp e ON e.dept_id = d.id;

# 右外连接（了解）
-- 查询所有部门及对应的员工信息
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.dept_id = d.id;

2.4.4 子查询

一条select语句执行结果，作为另一条select语法的一部分。其实就是select语句的嵌套！

  • 语法
    -- 【1】查询结果单值
SELECT MAX(salary) FROM emp;
-- 【2】查询结果单列多行
SELECT salary FROM emp;
-- 【3】查询结果多行多列
SELECT * FROM emp;

    规律

    -- 【1】子查询结果为单列，肯定作为条件在where后面使用
		select ... from 表名 where 字段 in (子查询);
-- 【2】子查询结果为多列，一般作为虚拟表在from后面使用
		select ... from (子查询) as 表别名;
  • sql演示
    • 子查询结果为单行单列
      -- 【1】查询工资最高的员工是谁？ 
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

      -- 【2】查询工资小于平均工资的员工有哪些？
-- 2.1 先求出平均工资
SELECT AVG(salary) FROM emp;
-- 2.2 查询低于平均工资的员工
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
    • 子查询结果为单列多行
      -- 【1】查询工资大于5000的员工，来自于哪些部门的名字  
-- 1.1 查询工资大于5000的员工
SELECT dept_id FROM emp WHERE salary >5000;
-- 1.2 来自于哪些部门的名字  
SELECT * FROM dept WHERE id IN(SELECT dept_id FROM emp WHERE salary >5000);

      -- 【2】查询开发部与财务部所有的员工信息
-- 2.1 根据部门名称，查询部门主键
SELECT id FROM dept WHERE `name` IN('开发部','财务部');
-- 2.2 根据部门id查询员工信息
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE `name` IN('开发部','财务部'));
    • 子查询结果为多列多行
      -- 【1】 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
-- 1.1 根据 join_date 查询 2011-11-11 之后的员工信息
SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11';

-- 1.2 根据1步骤的结果，跟部门表关联
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

三、多表查询综合案例

3.1 数据准备

-- 多表案例
create database db3_pro;
use db3_pro;
-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY auto_increment, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门位置
);

-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20), -- 职务名称
  description VARCHAR(50) -- 职务描述
);

-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id  外键
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资 99999.99
  bonus DECIMAL(7,2), -- 奖金 99999.99
  dept_id INT, -- 所在部门编号  外键
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,  -- 等级
  losalary INT, -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

3.2 表之间的关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ojl1XVO5-1614078868197)(assets/image-20210109230301998.png)]

3.3 案例一

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述

/*
 1.员工编号，员工姓名，工资，需要查询emp表  职务名称，职务描述 需要查询job表
 2.查询条件 emp.job_id = job.id
*/
SELECT 
 t1.`id`, -- 员工编号
 t1.`ename`, -- 员工姓名
 t1.`salary`,-- 工资
 t2.`jname`, -- 职务名称
 t2.`description` -- 职务描述
FROM 
 emp t1, job t2
WHERE 
 t1.`job_id` = t2.`id`;

3.4 案例二

查询所有员工信息。显示员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

/*
    1. 员工编号，员工姓名，工资 emp  职务名称，职务描述 job  部门名称，部门位置 dept
    2. 条件： emp.job_id = job.id and emp.dept_id = dept.id
*/
SELECT 
	t1.`id`, -- 员工编号
	t1.`ename`, -- 员工姓名
	t1.`salary`,-- 工资
	t2.`jname`, -- 职务名称
	t2.`description`, -- 职务描述
	t3.`dname`, -- 部门名称
	t3.`loc` -- 部门位置
FROM 
	emp t1, job t2,dept t3
WHERE 
	t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;

3.5 案例三

查询员工姓名，工资，工资等级

/*
    1.员工姓名，工资 emp  工资等级 salarygrade
    2.条件 emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
          emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/

SELECT 
	t1.`ename` ,
	t1.`salary`,
	t2.*
FROM 
	emp t1, 
	salarygrade t2
WHERE 
	t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;

3.6 案例四

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

/*
    1. 员工姓名，工资 emp ， 职务名称，职务描述 job 部门名称，部门位置，dept 工资等级 salarygrade
    2. 条件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary between salarygrade.losalary 			  and salarygrade.hisalary
*/

SELECT 
    t1.`ename`,
    t1.`salary`,
    t2.`jname`,
    t2.`description`,
    t3.`dname`,
    t3.`loc`,
    t4.`grade`
FROM 
	emp t1,job t2,dept t3,salarygrade t4
WHERE 
	t1.`job_id` = t2.`id` AND 
	t1.`dept_id` = t3.`id` AND 
	t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;

3.7 案例五

查询出部门编号、部门名称、部门位置、部门人数

/*
    1.部门编号、部门名称、部门位置 dept 表。 部门人数 emp表
    2.使用分组查询。按照emp.dept_id完成分组，查询count(id)
    3.使用子查询将第2步的查询结果和dept表进行关联查询
*/
SELECT 
	t1.`id`,
	t1.`dname`,
	t1.`loc` , 
	t2.total
FROM 
	dept t1,
    (
        SELECT
        	dept_id,COUNT(id) total
        FROM 
            emp
        GROUP BY dept_id
    ) t2
WHERE t1.`id` = t2.dept_id;

3.8 案例六

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-weHnY1Y1-1614078868199)(assets/image-20210109231707532.png)]

查询每个员工的名称及其上级领导的名称（自关联）

/*
	1.姓名 emp， 直接上级的姓名 emp
	 emp表的id 和 mgr 是自关联
    2.条件 emp.id = emp.mgr
    3.查询左表的所有数据，和 交集数据
         使用左外连接查询
*/
SELECT 
  yuangong.id,
  yuangong.ename,
  lingdao.id,
  lingdao.ename 
FROM
  emp yuangong 
LEFT OUTER JOIN 
  emp lingdao 
ON yuangong.mgr = lingdao.id ;

3.9 总结

​ 我们在企业开发时，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。其实不管是几张表的查询，都是有规律可循的。

* 多表查询会产生笛卡尔积

* 消除笛卡尔积
		连接条件 = 表个数-1
		
* 步骤
		1）确定几张表
		2）确定连接条件
		3）确定显示字段
		4）确定业务条件

四、用户权限DCL

我们进入公司后，有专门的DBA（数据库管理员），为你创建一个新用户，授予一定的权限…

我们只做为了解即可！

4.1 创建用户

  • 语法
    create user '用户名'@'主机名' identified by '密码';
  • 注意事项
    主机名：限定客户端登录ip
指定ip：127.0.0.1 （localhost）
任意ip：%
  • 演示
    CREATE USER 'tom'@'%' IDENTIFIED BY '123';

4.2 查看权限

  • 语法
    show grants for '用户名'@'主机名';
  • 演示
    SHOW GRANTS FOR 'tom'@'%' ;

4.3 用户授权

  • 语法
    grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';
  • 注意
    权限：
	select、insert、delete、update、create...
	ll 所有权限
数据库名.*  指定库下面所有的表
  • 演示
    GRANT SELECT ON day20.dept TO 'tom'@'%' ;

4.4 撤销授权

  • 语法
    revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';
  • 注意
    权限：
	select、insert、delete、update、create...
	ll 所有权限
数据库名.*  指定库下面所有的表
  • 演示
    REVOKE SELECT ON day20.dept FROM 'tom'@'%' ;

4.5 删除用户

  • 语法
    drop user '用户名'@'主机名';
  • 演示
    DROP USER 'tom'@'%' ;

4.6 密码管理

  • 超级管理员
    set password for '用户名'@'主机名'=password('新密码');
  • 普通用户
    set password=password('新密码');
  • 演示
    -- 加密函数
SELECT PASSWORD('123');

-- 超级管理帮你找回密码
SET PASSWORD FOR 'tom'@'%'= PASSWORD('999');

五、事务

5.1 事务概述

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

经典使用场景: 用户转账操作

5.2 数据准备

-- 创建数据表
CREATE TABLE account (  -- 账户表
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(32),
	money DOUBLE 
);

-- 添加数据
INSERT INTO account (`name`, money) VALUES ('蝴蝶姐', 1000), ('罗志祥', 1000);

模拟转账

-- 罗志祥扣钱（转出）
UPDATE account SET money = money -100 WHERE  id = 2;

-- 机器故障了


-- 蝴蝶姐加钱（转入）
UPDATE account SET money = money + 100 WHERE id = 1;

5.3 事务的操作步骤

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

5.3.1 事务案例演示

#########################################转账成功###########################################

-- 1. 开启事务
	begin;
-- 2. 罗志祥扣钱
	UPDATE account SET money = money -100 WHERE  id = 2;
-- 3. 蝴蝶姐加钱
	UPDATE account SET money = money + 100 WHERE id = 1;
-- 4. 提交事务
	commit;
	
	
#########################################转账失败###########################################
-- 1.开启事务
	begin;
-- 2.罗志祥扣钱
	UPDATE account SET money = money -100 WHERE  id = 2;
-- 3.机器故障
   出现故障了........
-- 4. 蝴蝶姐加钱
	UPDATE account SET money = money + 100 WHERE id = 1;
-- 5.回滚事务
	rollback;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oVGXgEpE-1614078868204)(assets/1588563849352.png)]

5.4 事务提交方式

  • 自动提交

    mysql就是自动提交的
    一条DML(增删改)语句会自动提交一次事务。

  • 手动提交

    Oracle 数据库默认是手动提交事务
    需要先开启事务，再提交

5.5 修改事务的默认提交方式

  • 查看事务的默认提交方式
    SELECT @@autocommit; -- 1 代表自动提交  0 代表手动提交
或
show variables like 'autocommit'; -- NO 代表自动提交 OFF 代表关闭自动提交，改为手动提交
  • 修改默认提交方式
    set @@autocommit = 0;
或
set autocommit=off;

5.6 事务的四大特征(ACID)

1. 原子性(Atomicity)	：是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 一致性(Consistency)	：事务操作前后，保证数据的一致性
3. 隔离性(Isolation)	：多个事务之间,相互独立,互不干扰
4. 持久性(Durability)  ：当事务提交或回滚后，数据库会持久化的保存数据。   掉电/关机  内存  硬盘上

5.7 事务的隔离性

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

5.7.1 存在的问题

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

5.7.2 隔离级别

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

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

5.7.3 查询和设置隔离级别

  • 查看当前数据库隔离级别
    show variables like '%isolation%';
  • 临时修改隔离级别
    set session transaction isolation level 级别字符串;

5.8 隔离级别演示问题【上课演示，看一遍有印象】

5.8.1 演示脏读

一个事务，读取到另一个事务中没有提交的数据

  • 模拟两个窗口,代表两个事物

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nJYvDUd7-1614078868206)(assets/image-20210115170741127.png)]

  • 两个窗口并登录mysql,查看当前事务的默认的隔离级别

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dyJjrODj-1614078868208)(assets/image-20210115171025592.png)]

show variables like ‘%isolation%’;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0PmvHzLS-1614078868209)(assets/image-20210115171500950.png)]

  • 设置隔离级别: read uncommitted

    set session transaction isolation level read uncommitted;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gVZs7TCe-1614078868210)(assets/image-20210115172741591.png)]

  • 脏读演示,给id为1的用户转账200
    -- A窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 转账
update account set money = money + 200 where id = 1;

###################################################################
-- B窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 查询account表
select * from account;

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rMtOcjXN-1614078868211)(assets/image-20210115175829369.png)]

    A窗口回滚事务

    rolback;

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B7IhUlmF-1614078868212)(assets/image-20210115180055214.png)]

    在一个事务中可以随随便便读取到其他事务未提交的数据,这种情况我们叫脏读.

    简单理解

    ​ B窗口这个事务好轻浮啊，饥渴到连别人没提交的东西都等不及，真脏，呸！

    总结

    ​ 我们将事务隔离级别设置为 read uncommitted，即便是事务没有 commit，但是我们仍然能读到未提交的数据，这是所有隔离级别中最低的一种 read uncommitted

5.8.2 演示不可重复读

在同一个事务中，两次读取到的数据不一样。

  • 设置事务的隔离级别 read committed

    set session transaction isolation level read committed;

    – 查询事务的隔离级别

    show variables like ‘%isolation%’;

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YyvMDHeM-1614078868213)(assets/image-20210116094401140.png)]

  • 不可重复读演示,给id为1的用户转账200
    -- A窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 转账
update account set money = money + 200 where id = 1;

###################################################################
-- B窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 查询account表
select * from account;

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RO3X0a8w-1614078868214)(assets/image-20210116095433332.png)]

总结：

当我们将当前会话的隔离级别设置为 read committed 的时候，当前会话只能读取到其他事务提交的数据，未提交的数据读不到。读取到两次不同的结果。这就造成了不可重复读，就是两次读取的结果不同。这种现象叫不可重复读。

5.8.3 解决不可重复读问题

  • 设置事务的隔离级别 repeatable read

    set session transaction isolation level repeatable read;

-- 查询事务的隔离级别

show variables like '%isolation%';

  • 给id为1的用户转账200

    -- A窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 转账
update account set money = money + 200 where id = 1;

###################################################################
-- B窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 查询account表
select * from account;

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mIuGzdEE-1614078868215)(assets/image-20210116101342575.png)]

5.8.4 演示幻读

一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

  • 设置事务的隔离级别 repeatable read

    set session transaction isolation level repeatable read;

-- 查询事务的隔离级别

show variables like '%isolation%';

  • 在各自的事务中，添加一条记录

    -- A窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 添加一条记录
insert into account values(3,'张三',1000);


###################################################################
-- B窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 添加一条记录
insert into account values(3,'张三',1000);

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cKaSK5oT-1614078868216)(assets/image-20210116105141998.png)]

总结：

数据已经发生改变，但是我还是要保持一致。但是，出现了用户 B 面对的问题，这种现象叫幻读.

当我们将当前会话的隔离级别设置为 repeatable read 的时候，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。

5.8.5 解决幻读

  • 设置事务的隔离级别 serializable

    set session transaction isolation level serializable;

-- 查询事务的隔离级别
show variables like '%isolation%';

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w5GZaXLZ-1614078868217)(assets/image-20210116105704973.png)]

  • 在A窗口事务，添加记录，B窗口查询

    -- A窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 添加一条记录
insert into account values(4,'aa',1000);


###################################################################
-- B窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 查询account
select * from account;

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KlhVfwUe-1614078868218)(assets/image-20210116110349750.png)]

  • A窗口提交事务

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JiXca2Yz-1614078868219)(assets/image-20210116110518409.png)]

总结:

############################################
– B窗口

– 切换数据库
use db2;
– 开启事务
begin;

– 添加一条记录
insert into account values(3,‘张三’,1000);


[外链图片转存中...(img-cKaSK5oT-1614078868216)]



==总结：==

**数据已经发生改变，但是我还是要保持一致。但是，出现了用户 B 面对的问题，这种现象叫幻读.**

**当我们将当前会话的隔离级别设置为 repeatable read 的时候，当前会话可以重复读，就是每次读取的结果集都相同，而不管其他事务有没有提交。**

### 5.8.5 解决幻读

* ==设置事务的隔离级别   serializable==

```sql
set session transaction isolation level serializable;

-- 查询事务的隔离级别
show variables like '%isolation%';

[外链图片转存中…(img-w5GZaXLZ-1614078868217)]

  • 在A窗口事务，添加记录，B窗口查询

    -- A窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 添加一条记录
insert into account values(4,'aa',1000);


###################################################################
-- B窗口

-- 切换数据库
use db2;
-- 开启事务
begin;

-- 查询account
select * from account;

    [外链图片转存中…(img-KlhVfwUe-1614078868218)]

  • A窗口提交事务

    [外链图片转存中…(img-JiXca2Yz-1614078868219)]

总结:

当我们将当前会话的隔离级别设置为 serializable 的时候，其他会话对该表的写操作将被挂起。可以看到，这是隔离级别中最严格的，但是这样做势必对性能造成影响。所以在实际的选用上，我们要根据当前具体的情况选用合适的。

樱木哥哥
关注
专栏目录
mysql五表联合查询_mysql
weixin_36413247的博客
02-18 2937
join语句的基本使用SQL(MySQL) JOIN 用于根据两个或多个表中的字段之间的关系，从这些表中得到数据。JOIN 通常与 ON 关键字搭配使用，基本语法如下：... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditionat...本篇文章是多表联合查询的介绍，希望对大家起到参考的作用。推荐课程：MySQL教程。多表连接类型1. 笛卡尔积...
MySQL数据库设计概念（多表查询&事务操作）
来了点个赞再走...
05-22 4094
数据库设计概念以及表与表之间的关系，连接查询和嵌套查询，MySQL数据库事务操作。。。
多表及事务
陈福国的博客
11-02 322
目录： 多表查询 事务 DCL 多表查询： * 查询语法： select 列名列表 from 表名列表 where.... * 准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) V...
多表查询，事务
buyanf的博客
04-03 824
数据库多表查询，事务
SQL-多表查询-事务
最新发布
weixin_50206068的博客
06-06 475
SQL-多表查询和事务是数据库中常用的两个概念。多表查询可以通过JOIN语句将多个表连接起来，从而实现数据的联合查询。事务是一组操作的集合，可以通过BEGIN 、COMMIT 和ROLLBACK等语句来实现。事务的目的是确保一组操作的原子性，保证数据的一致性和可靠性。通过SQL-多表查询和事务，可以方便地实现复杂的数据查询和操作，提高数据处理的效率和可靠性。
Java初学者笔记17—数据库多表查询，事务
weixin_48889334的博客
09-15 309
一 多表查询： 当两张表的数据有所关联的时候。我们希望能从一张表中的字段去查询到另一张表中的数据，这个操作就叫做多表查询。 我们可以用3种方法去简化多表查询的步骤 正常 select 列名 from 表名1 ，表名2，这是查询表1和表2每条数据全部一一对应的所有情况（笛卡尔乘积），而我们的多表查询一定要消除这种情况。 1 内连接 隐式内连接：在正常情况的基础上加上条件 Select ...from 表1，表2 where 表1.列=表2.列（条件） ...
day_04MySQL多表&事务1
08-08
在"Day_04MySQL 多表&事务"的学习中，我们主要关注两个核心概念：多表查询和事务处理。这些知识对于进行复杂的数据操作和确保数据一致性至关重要。 首先，多表查询是当数据分布在多个相关表中时，为了获取完整信息...
MySQL多表查询、事务和管理用户及授权
05-09
本文将深入探讨MySQL中的多表查询、事务处理以及用户管理和权限授权。 **一、多表查询** 在数据库设计中，往往需要处理多个相关表的数据。MySQL提供了多种多表查询方法，包括： 1. **JOIN操作**：JOIN允许你将两...
MySQL多表&事务课堂笔记.md
08-10
MySQL多表&事务课堂笔记.md
MySQL 如何查询当前最新事务ID
09-08
总的来说，MySQL中查询当前最新事务ID是事务管理和性能优化中不可或缺的技能，其主要通过执行SHOW ENGINE INNODB STATUS命令和查询INFORMATION_SCHEMA.INNODB_TRX表来实现。掌握这些方法将有助于高效管理和维护MySQL...
数据库多表查询和事务
九门宁生
08-15 194
多表查询： * 查询语法： select 列名列表 from 表名列表 where.... * 准备sql # 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
mysql：多表查询与事务
qq_38353993的博客
09-29 429
1、三范式 1.1 什么是范式 ​ 范式就是设计数据库的表的规则 ​ 通过科学规范的设计规则，可以优化数据库的存储性能 1.2 范式的基本分类 ​ 目前关系型数据库有六种范式：我们程序员一般只需要掌握前三种 1.3 一二三范式 总结： 1）、第一范式：每一个字段不可进行再次拆分 2）、第二范式：一张表只描述一件事情；每个表中的每个字段都依赖于主键 3）、第三范式：从表的外键必须使用在主表的主键...
事务与多表查询
大哥的阔落
11-17 499
多表查询 1、创建部门表 create table dept( id int PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); inser into dept（name）values('开发部'),('市场部'),('财务部'); 2、创建员工表 create table emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), gender CHAR(1), -- 性别
MySQL基础（多表查询、事务）
Jonny_Wang的博客
07-16 287
内容来自黑马课件总结，侵删
MySQL多表、事务
weixin_42214237的博客
08-12 507
多表查询
MySQL笔记5 多表查询
灵渡的博客
04-01 885
多表查询：也称为关联查询，指两个或更多个表一起完成查询操作。 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。 1. 案例引出 1.1 案例说明 需求：查询员工的姓名及其部门名称 若执行以下语句 SELECT last_name, department_name FROM employees, departments; 结果为： +-----------+---
【Mysql】第4篇--多表查询和事务
检哥说IT的博客
06-26 379
范式: 设计数据库表的规则。 第一范式:每一列不能再拆分,查询出的结果不需要进一步处理,可以直接使用。 第二范式:每张数据库表必须要有主键，可以解决数据冗余(重复)问题 (一张表只能描述一件事情)。 第三范式：从表的外键必须使用主表的主键。.........
MYSQL（多表和事务）
weixin_50648720的博客
12-02 2247
MYSQL多表和事务DCL创建用户，给用户授权，撤销授权创建用户授权用户撤销授权查看权限DCL删除用户，修改用户密码删除用户修改管理员密码修改普通用户密码数据库备份(重要)DOS命令行方式备份与还原图形化界面备份与还原数据库的三大范式什么是范式范式的基本分类第一范式第二范式第三范式外键约束(重要)什么是外键创建外键约束删除外键约束外键的级联有了外键约束后能直接修改和删除数据吗?什么是级联操作表关系的概念一对多多对多一对一多表查询介绍什么是多表查询多表查询的分类笛卡尔积现象什么是笛卡尔积现象如何清除笛卡尔积现
MySQL基础篇（3）—多表查询、事务
weixin_61843013的博客
10-28 1189
概述：指从多张表中查询数据笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)多表查询分类。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值