mysql多表查询以及事务

最新推荐文章于 2023-08-26 15:17:18 发布
最新推荐文章于 2023-08-26 15:17:18 发布
阅读量233 收藏
点赞数
分类专栏： java学习 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42137874/article/details/118973311
版权

表连接查询

什么是多表查询

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

多表查询的分类：

在这里插入图片描述

笛卡尔积现象

在这里插入图片描述

内连接

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

隐式内连接

  • 隐式内连接：看不到 JOIN 关键字，条件使用 WHERE 指定
    在这里插入图片描述

显式内连接

在这里插入图片描述

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

insert into dept (name)
values ('开发部'),
       ('市场部'),
       ('财务部'); # 创建员工表
drop table emp;
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;
# 设置过滤条件  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`;
# 	查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称，我们发现需要联合 2 张表同时才能查询出需要的数据，使用内连接
select emp.id as 编号, emp.name as 姓名, emp.gender as 性别, emp.salary as 薪水, dept.name as 部门
from emp
         inner join dept
                    on dept.id = emp.dept_id
where emp.name = '唐僧';
# 在部门表中增加一个销售部
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`;
# 在员工表中增加一个员工
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`;
## 子查询的结果是一个值的时候
# 需求：查询开发部中有哪些员工
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='市场部');
# 查询工资最高的员工是谁？
# 1) 查询最高工资是多少
select max(salary) from emp;
# 2) 根据最高工资到员工表查询到对应的员工信息
select * from emp where salary = (select max(salary) from emp);
# 查询工资小于平均工资的员工有哪些
-- 1) 查询平均工资是多少
select avg(salary) from emp;
-- 2) 到员工表查询小于平均的员工信息
select * from emp where salary < (select avg(salary) from emp);
# 查询工资大于 5000 的员工，来自于哪些部门的名字4.4.1	查询工资大于 5000 的员工，来自于哪些部门的名字
# 先查询大于 5000 的员工所在的部门 id
select dept_id from emp where salary > 5000;
# 再查询在这些部门 id 中部门的名字
select name from dept where id = (select dept_id from emp where salary > 5000); # Subquery returns more than 1 row  报错
select name from dept where id in (select dept_id from emp where salary > 5000);
## 子查询的结果是多行多列
# 查询出 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';
# 子查询小结
# 子查询结果只要是单列，则在 WHERE 后面作为条件
# 子查询结果只要是多列，则在 FROM 后面作为表进行二次查询

子查询

什么是子查询

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

子查询结果的三种情况：

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

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

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

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

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

事务

事务的应用场景说明

  • 什么是事务： 在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。
    事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。
  • 转账的操作
    模拟张三给李四转 500 元钱，一个转账的业务操作最少要执行下面的 2 条语句：
    张三账号-500
    李四账号+500
    假设当张三账号上-500 元,服务器崩溃了。李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。

手动提交事务

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

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

手动提交事务的 SQL 语句

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

手动提交事务使用过程：

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

案例演示 1：事务提交

模拟张三给李四转 500 元钱（成功） 目前数据库数据如下：

  1. 使用 DOS 控制台进入 MySQL
  2. 执行以下 SQL 语句：
    1.开启事务，
    2.张三账号-500，
    3.李四账号+500
  3. 使用 SQLYog 查看数据库：发现数据并没有改变
  4. 在控制台执行 commit 提交事务：
  5. 使用 SQLYog 查看数据库：发现数据改变
    在这里插入图片描述

案例演示 2：事务回滚

模拟张三给李四转 500 元钱（失败） 目前数据库数据如下：

  1. 在控制台执行以下 SQL 语句：1.开启事务， 2.张三账号-500
  2. 使用 SQLYog 查看数据库：发现数据并没有改变
  3. 在控制台执行 rollback 回滚事务：
  4. 使用 SQLYog 查看数据库：发现数据没有改变
    在这里插入图片描述
    总结: 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL
    语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

自动提交事务

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

案例演示 3：自动提交事务

  1. 将金额重置为 1000
  2. 更新其中某一个账户
  3. 使用 SQLYog 查看数据库：发现数据已经改变
    在这里插入图片描述
    在这里插入图片描述

取消自动提交

  • 查看 MySQL 是否开启自动提交事务
    @@表示全局变量，1 表示开启，0 表示关闭
  • 取消自动提交事务
  • 执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变
  • 在控制台执行 commit 提交任务
    在这里插入图片描述

事务原理

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

原理图：

在这里插入图片描述

事务的步骤：

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

回滚点

什么是回滚点

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

回滚点的操作语句

在这里插入图片描述

具体操作：

  1. 将数据还原到 1000
  2. 开启事务
  3. 让张三账号减 3 次钱，每次 10 块
  4. 设置回滚点：savepoint three_times;
  5. 让张三账号减 4 次钱，每次 10 块
  6. 回到回滚点：rollback to three_times;
  7. 分析执行过程
     总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。
    5.6 事务的隔离级别
    5.6.1 事务的四大特性 ACID
    在这里插入图片描述
    在这里插入图片描述
    5.6.4 MySQL 事务隔离级别相关的命令
     查询全局事务隔离级别

 设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

5.6.5 脏读的演示
将数据进行恢复：UPDATE account SET balance = 1000;

  1. 打开 A 窗口登录 MySQL，设置全局的隔离级别为最低

  2. 打开 B 窗口,AB 窗口都开启事务

  3. A 窗口更新 2 个人的账户数据，未提交

  4. B 窗口查询账户

  5. A 窗口回滚

  6. B 窗口查询账户，钱没了

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决脏读的问题：将全局的隔离级别进行提升
将数据进行恢复：

  1. 在 A 窗口设置全局的隔离级别为 read committed

B 窗口退出 MySQL，B 窗口再进入 MySQL
AB 窗口同时开启事务

  1. A 更新 2 个人的账户，未提交

  2. B 窗口查询账户

A 窗口 commit 提交事务

  1. B 窗口查看账户

结论：read committed 的方式可以避免脏读的发生
5.6.6 不可重复读的演示
将数据进行恢复：

  1. 开启 A 窗口

  2. 开启 B 窗口，在 B 窗口开启事务

  3. 在 A 窗口开启事务，并更新数据

  4. B 窗口查询

两次查询输出的结果不同，到底哪次是对的？不知道以哪次为准。 很多人认为这种情况就对了，无须困惑， 当然是后面的为准。我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。
解决不可重复读的问题：
将全局的隔离级别进行提升为：repeatable read
将数据进行恢复：

  1. A 窗口设置隔离级别为：repeatable read

  2. B 窗口退出 MySQL，B 窗口再进入 MySQL

  3. A 窗口更新数据

  4. B 窗口查询

结论：同一个事务中为了保证多次查询数据一致，必须使用 repeatable read 隔离级别
5.6.7 幻读的演示
在 MySQL 中无法看到幻读的效果。
但我们可以将事务隔离级别设置到最高，以挡住幻读的发生 将数据进行恢复：

  1. 开启 A 窗口

  2. A 窗口退出 MySQL，A 窗口重新登录 MySQL

  3. 再开启 B 窗口，登录 MySQL

  4. 在 B 窗口中开启事务，添加一条记录

  5. 在 A 窗口中 commit 提交事务，B 窗口中 insert 语句会在 A 窗口事务提交后立马运行

  6. 在 A 窗口中接着查询，发现数据不变

  7. B 窗口中 commit 提交当前事务

  8. A 窗口就能看到最新的数据

结论：使用 serializable 隔离级别，一个事务没有执行完，其他事务的 SQL 执行不了，可以挡住幻读

第6节 DCL (Data Control Language)
 DDL： create / alter / drop
 DML：insert /update/delete
 DQL ：select /show
 DCL ：grant /revoke
我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。
注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

6.1 创建用户
6.1.1 语法：
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
6.1.2 关键字说明：

关键字 说明
‘用户名’ 将创建的用户名
‘主机名’ 指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以
从任意远程主机登陆，可以使用通配符%
‘密码’ 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
6.1.3 具体操作：
 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123
 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123

 注：创建的用户名都在 mysql 数据库中的 user 表中可以查看到，密码经过了加密。
6.2 给用户授权
用户创建之后，没什么权限！需要给用户授权
6.2.1 语法：
GRANT 权限 1, 权限 2… ON 数据库名.表名 TO ‘用户名’@‘主机名’;

6.2.2 关键字说明：

关键字 说明
GRANT…ON…TO 授权关键字
权限 授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授
予所有的权限则使用 ALL
数据库名.表名 该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作
权限则可用表示，如.*
‘用户名’@‘主机名’ 给哪个用户授权，注：有 2 对单引号
6.2.3 具体操作：

  1. 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询

 注：用户名和主机名要与上面创建的相同，要加单引号。
2. 给 user2 用户分配所有权限，对所有数据库的所有表

6.3 撤销授权
6.3.1 语法：

关键字 说明
REVOKE…ON…FROM 撤销授权的关键字
权限 用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权
限则使用 ALL
数据库名.表名 对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则可用表
示，如.*
‘用户名’@‘主机名’ 给哪个用户撤销

6.3.2 具体操作：
 撤销 user1 用户对 test 数据库所有表的操作的权限
 注：用户名和主机名要与创建时相同，各自要加上单引号
6.4 查看权限
6.4.1 语法：
SHOW GRANTS FOR ‘用户名’@‘主机名’;

6.4.2 具体操作：
 查看 user1 用户的权限
 注：usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。
6.5 删除用户
6.5.1 语法
DROP USER ‘用户名’@‘主机名’;
6.5.2 具体操作：
 删除 user2

6.6 修改管理员密码
6.6.1 语法
mysqladmin -uroot -p password 新密码

 注意：需要在未登陆 MySQL 的情况下操作，新密码不需要加上引号。
6.6.2 具体操作：

  1. 将 root 管理员的新密码改成 123456
  2. 要求输入旧密码
  3. 使用新密码登录

6.7 修改普通用户密码
6.7.1 语法：
set password for ‘用户名’@‘主机名’ = password(‘新密码’);

 注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。
6.7.2 具体操作：

  1. 将’user1’@‘localhost’的密码改成’666666’
  2. 使用新密码登录，老密码登录不了
hu的小金库
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MySQL多表查询
Owen Guo
08-28 1万+
MySQL的多表查询
MySQL --- 多表查询 & 驱动表
weixin_53622554的博客
05-12 7348
指从多张表中查询数据，就是从多张表当中进行查询单表查询的SQL语句：select 字段列表 from 表名;那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2;-- 多表查询:直接在from之后跟上多张表就可以了,多张表之间使用逗号分隔可以看到，在多表查询的结果当中，每个员工都展示了5次，将每个员工分别和五个部门进行了一次匹配， 也就是将17个员工和5个部门挨个组合了一次，就是17 * 5 = 85。
MySQL多表查询 （超详细）
weixin_55772633的博客
08-05 4632
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）（开发中一般取消外键的设定，即在多的一方建立字段指向一的一方的主键即可）（开发中一般取消外键的设定，即在任意一方加入字段关联另外一方的主键即可）（开发中一般取消外键的设定，即在中间表建立两个字段指向两方的主键即可）关系：一个部门对应多个员工，一个员工对应一个部门。实现：在多的一方建立外键，指向一的一方的主键。
MySQL之多表查询
小月月的博客
05-26 5717
一、介绍 二、多表关系 三、多表查询简述 四、多表查询操作 ：1.连接查询--内连接 2.连接查询--外连接 3.连接查询--自连接
MySQL 如何查询当前最新事务ID
09-08
主要介绍了MySQL 如何查询当前最新事务ID，帮助大家更好的理解和学习MySQL，感兴趣的朋友可以了解下
MySQL多表查询、事务和管理用户及授权
05-09
MySQL多表查询、事务和管理用户及授权
MySQL多表查询与事务的操作.pdf
05-01
通过连接运算符可以实现多个表查询。连接是关系数据库模型的主要特点，也是它区别于其它类型数据库管理系统的一个标志。 在关系数据库管理系统中，表建立时各数据之间的关系不必确定，常把一个实体的所有信息存放在...
多表mysql相关事务等.emmx
07-18
使用mindmaster打开
MySQL-小白日志:多表查询和事务
12-15
多表查询的分类： 两个表数据 CREATE TABLE dept( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20) ); CREATE TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), gender CHAR(1), ...
MySQL中的多表查询详解
qq_57492774的博客
07-09 3013
在实际的项目开发中，在进行数据表结构设计的时候，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间很多都是相关联的，所以各个表结构之间也存在着一定的关系。id INT PRIMARY KEY COMMENT '部门编号',name VARCHAR(20) UNIQUE COMMENT '部门名称'
【MySQL】多表查询策略（多表联查&子查询）
热门推荐
尹煜的博客
05-01 1万+
MySQL语句学习的难点和重点就在于多表查询，同时MySQL也有诸多方法供大家选择，不论是多表联查（联结表、左连接、右连接……）还是子查询（SELECT子查询、WHERE子查询、FROM子查询），掌握一种方法达到目的即可，当然其他方法也需要理解，本文将阐述完整的多表查询方法。 首先分享下学习链接，大家可以在看完本文后选择以下链接巩固学习成果，我相信如果都给掌握的话，MySQL语句中90%的问题都将迎刃而解！！ 视频学习链接：SQL学习之道_哔哩哔哩_bilibili
mysql多表查询
zcffight的博客
10-04 2239
mysql的多表查询
MySQL多表查询详解
qq_51010919的博客
07-18 688
在MySQL中，多表查询是指同时从多个表中检索数据并根据表之间的关联关系进行联接（Join）。通过多表查询，可以获取跨多个表的相关数据，实现复杂的查询需求。全连接（Full Join）：全连接用于返回左表和右表中的所有行，无论是否存在匹配的行。如果某个表中没有匹配的行，则对应的列将显示为NULL。MySQL不直接支持全连接，但可以使用左连接和右连接的组合来实现全连接效果。交叉连接（Cross Join）(笛卡尔积)：交叉连接用于返回两个表的，即将左表的每一行与右表的每一行进行组合。
MySql学习4：多表查询
最新发布
weixin_41897680的博客
08-26 947
MySql学习4：多表查询
MySQL - 多表查询
老街头的猫。
03-10 1249
多表查询示例、多表查询分类、等/非等值连接、自然/非自然连接、内/外连接、UNION的使用、7种JOIN操作
MySQL的多表查询
weixin_55734444的博客
07-05 3834
MySQL的多表查询
MySQL数据库总结 之 多表查询
Flying Bulldog 的博客
07-07 873
一万两千字的博客，值的细细品味！ 1. 多表关系、1.1 一对多、1.2 多对多、1.3 一对一、2 多表查询、2.1 数据准备、2.2 概述、2.3 分类—内连接、外连接和自连接、3. 内连接、4. 外连接、5. 自连接、5.1 自连接查询、5.2 联合查询、6. 子查询、6.1 概述、6.2 标量子查询、6.3 列子查询、6.4 行子查询、6.5 表子查询
mysql查询锁表 以及解表
05-28
要查询MySQL中的锁表情况，可以使用以下语句： ``` SHOW OPEN TABLES WHERE In_use > 0; ``` 这条语句将返回当前正在使用的表的列表，其中 `In_use` 表示当前正在使用该表的连接数。 如果要查询行级锁的情况，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值