MySQL--基础回顾 (五) 多表查询、事务和DCL

最新推荐文章于 2024-11-01 23:37:32 发布

卖BUG的小男孩

最新推荐文章于 2024-11-01 23:37:32 发布

阅读量42

点赞数

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_42844241/article/details/133006580

版权

表连接查询

多表查询概念

查询数据涉及多个表

作用

如果一条 SQL 语句查询多张表，因为查询结果在多张不同的表中。每张表取 1 列或多列。

多表查询的分类

引入笛卡尔积现象

　　笛卡尔积现象是，表的数据与另一个表数组形成了组合，但是当我们需要查询数据时，我们不需要这种数据组合，我们需要的表与表之间连接 ID 相等，在开发过程中我们需要过滤掉这种没有用的数据。

分类详解

内连接

　　用外表的外键等于主表的主键，简单将就是左边表有记录去匹配右边表，如果条件符合则可以显示。

隐式内连接

看不到 JOIN 关键字，条件使用 WHERE 指定

　　SELECT 字段名 FROM 左表, 右表 WHERE 条件
　　select * from emp,dept where emp.`dept_id` = dept.`id`;

显式内连接

　　使用 INNER JOIN ... ON 语句, 可以省略 INNER
　　SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件

　　select * from emp e inner join dept d on e.`dept_id` = d.`id` where e.`name`='小三';

连接查询步骤

确定查询哪些表
确定表连接的条件
确定查询的条件
确定查询的字段

左外连接

　　使用 LEFT OUTER JOIN ... ON，OUTER 可以省略
　　SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

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

右外连接

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

　　使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略
　　SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

子查询

概念：

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

子查询结果的三种情况

子查询是单列
子查询是多行单列
子查询是多行多列

结果是一个值

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

示例

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

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

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

示例

-- 先查询大于 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);

子查询的结果是多行多列

　　子查询结果只要是多列，肯定在 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 后面作为表进行二次查询

事务

应用场景

　　什么是事务：在实际的开发过程中，一个业务操作如：转账，往往是要多次访问数据库才能完成的。转账是一个用户扣钱，另一个用户加钱。如果其中有一条 SQL 语句出现异常，这条 SQL 就可能执行失败。

　　事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。

两种方式事务操作

手动提交事务
自动提交事务

手动提交事务

手动提交事务SQL语句

手动提交事务使用过程

执行成功的情况：开启事务→执行多条 SQL 语句 →成功提交事务

执行失败的情况：开启事务→执行多条 SQL 语句→事务的回滚

如图所示：

案例演示 1：事务提交

案例演示 2：事务回滚

　　如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。如果事务中 SQL语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

自动提交事务

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

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

取消自动提交事务

查看MySQL是否开启自动提交事务

@@表示全局变量，1 表示开启，0 表示关闭

取消自动提交事务

执行更新语句，使用 SQLYog 查看数据库，发现数据并没有改变

在控制台执行 commit 提交任务

事务原理

原理图

事务的步骤：

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

回滚点

什么是回滚点？

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

回滚操作语句

具体操作

将数据还原到 1000
开启事务
让张三账号减 3 次钱，每次 10 块
设置回滚点：savepoint three_times;
让张三账号减 4 次钱，每次 10 块
回到回滚点：rollback to three_times;
分析执行过程

设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

事务的隔离级别

事务的四大特性 ACID

事务的隔离级别

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

MySQL 数据库有四种隔离级别

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

隔离级别越高，性能越差，安全性越高。

MySQL设置事务隔离级别

查询全局事务隔离级别：

查询隔离级别　　　　select @@tx_isolation;

设置事务隔离级别

　　set global transaction isolation level 级别字符串;

示例：

1.打开A窗口，登录MySQL，设置全局的隔离级别为最低　　set global transaction isolation level read uncommitted; 创建数据库文件，创建表，有两列，添加数据。

2.打开B窗口，登录MySQL，A和B都开启事务，在A窗口更新数据，不进行提交事务commit。

3.切换到B窗口，查看表，结果读到了未提交的数据，

4.切换到A，输入rollback，数据回滚，

5.切换到B，查询表，数据发生变化，

这就是脏读，在业务中例如，买东西与卖东西，我开启事务，支付费用，向你支付了500元，你查看收款情况，接收到500元，我进行事务回滚，恢复到为开启事务的时候，你再次查看你的余额，钱没了。

如何解决脏读？

使用更高级的事务隔离，将全局的隔离级别进行提升

在A窗口重新设置全局事务隔离，set global transaction isolation level read committed;
B窗口退出MySQL----exit，重新登录MySQL，AB重新开启事务
A窗口对数据进行更新，不进行事务提交commit
B窗口查询，A窗口提交事务，B窗口再次查询，数据与上次发生了变化，脏读问题解决

不可重复读

上面的查询的结果，两次查询不一样也，在日常生活中

　　我们可以考虑这样一种情况，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

如何解决不可重复的问题？

将全局的隔离级别进行提升为：repeatable read

　　在A窗口开始事务到提交事务过程后，在B窗口开启事务后，第一次查询和第二次查询结果一样，在B窗口提交事务后，再次查询数据，发生变化，业务操作正常。

幻读：

　　在MySQL中无法看到幻读，但我们可以将事务隔离级别设置到最高，以挡住幻读的发生将数据进行恢复：

　　重新设置A窗口全局事务隔离级别，退出MYSQL，重新登录MySQL，开启事务，再次打开B窗口重新登陆MySQL，在B中添加记录，B窗口不会提示添加成功，光标会一直闪烁。A窗口提交事务，B窗口添加记录会执行，再提交事务，再A窗口查询，数据发生变化。

　　使用最高事务隔离级别，一个事务没有执行完，其他事务SQL执行不了。这样就可以挡住幻读。

　　事务操作数据过程中，会将数据操作先添加到一个日志文件中，当提交事务后，数据才会发生变化。

DCL(Data Control Language)

　　我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

　　注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。

管理用户

添加用户：

　　* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

　　创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123create user 'user1'@'localhost' identified by '123';
　　创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123create user 'user2'@'%' identified by '123';

再MySQL数据库中，有一张表专门存放用户--user表，查看表我们会看到用户密码是一串字符，这是因为MySQL对密码进行了加密

删除用户：

　　* 语法：DROP USER '用户名'@'主机名';

修改管理员密码

　　mysqladmin -uroot -p password 新密码

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

修改普通用户密码：

　　UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
　　UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
　　SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
　　SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

mysql中忘记了root用户的密码？

1. cmd -- > net stop mysql 停止mysql服务

　　* 需要管理员运行该cmd

2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。