day3-mysql-多表查询和事务操作

最新推荐文章于 2021-01-18 21:00:50 发布

今天,我和你拼了

最新推荐文章于 2021-01-18 21:00:50 发布

阅读量151

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/weixin_38255444/article/details/99879444

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

day3:多表查询和事务操作

一. 多表连接查询

分类:

内连接:隐式内连接 , 显示内连接

外连接:左外连接 , 右外连接

1.1. 笛卡尔积:左表数据和右表数据进行组合,这种效果叫做笛卡尔积

消除笛卡尔积现象的影响

可以使用where进行条件限制

-- 查询员工和部门的名字
SELECT emp.`name`, dept.`name` FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

1.2. 内连接:用左边的表记录取匹配右边的表记录,如果符合条件的则显示

1.2.1 隐式内连接:没有join关键字,使用where进行条件限制.

SELECT 字段名 FROM 左表, 右表 WHERE 条件
示例：
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

1.2.2 显示内连接:使用join … on 语句

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
示例：
-- 查询唐僧的信息，显示员工 id，姓名，性别，工资和所在的部门名称
SELECT * FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id` WHERE e.`name`='唐僧' ;

1.3. 外连接

1.3.1 左外连接:使用left join …on

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.3.2 右外连接:使用right join …on

SELECT 字段名 FROM 左表 RIGHT OUTER JOIN 右表 ON 条件
用右边表的记录去匹配左边表的记录，如果符合条件的则显示，否则，显示 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）一个查询的结果做为另一个查询的条件

2）有查询的嵌套，内部的查询称为子查询

3）子查询要使用括号

-- 需求：查询开发部中有哪些员工
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='开发部' );

子查询结果三种情况

单行单列

结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、
<>、 = 等
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
示例：
-- 查询工资最高的员工是谁？
SELECT MAX(salary) FROM emp;
-- 使用子查询 过滤条件 工资= SELECT MAX(salary) FROM emp;
SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);
-- 查询工资小于平均工资的员工有哪些?
SELECT AVG(salary) FROM emp;
SELECT * FROM emp WHERE salary<(SELECT AVG(salary) FROM emp);

多行单列

结果集类似一个数组，条件需要使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
示例：
-- 查询工资大于 5000 的员工，来自于哪些部门的名字 ?
SELECT dept_id FROM emp WHERE emp.`salary`>5000;
SELECT dept.`name` FROM dept WHERE dept.`id` IN ( SELECT dept_id FROM emp
WHERE emp.`salary`>5000);
-- 查询开发部与财务部所有的员工信息
SELECT id FROM dept WHERE dept.`name` IN ('开发部','财务部');
SELECT * FROM emp WHERE emp.`dept_id` IN (SELECT id FROM dept WHERE dept.`name`
IN ('开发部','财务部'));

多行多列

子查询结果只要是多列，肯定是在 FROM 关键字后作为表使用。
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没有名称则无法访问表中的字段
示例：
-- 查询出 2011 年以后入职的员工信息，包括部门名称
# 1) 查询 2011 年以后的员工
# 2) 使用第一步的结果做为表 与 部门表 关联查询
SELECT * FROM emp WHERE emp.`join_date`>'2011-1-1';
SELECT * FROM (SELECT * FROM emp WHERE emp.`join_date`>'2011-1-1') e , dept d
WHERE e.dept_id=d.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';

三. 事务

事务的应用场景说明
概念：在实际的开发过程中，一个业务操作如:转账，往往是要多次访问数据库才能完成的。转账的过程是一个用户扣钱，另一个用户加钱。如果其中有一条SQL 语句出现异常，这条 SQL 就可能执行失败。事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的 SQL 语句都要回滚，整个业务执行失败。

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

自动提交事务
查看当前是否开启自动提交事务
select @@autocommit; – @@表示全局变量 0 代表关闭 1 代表开启

取消自动事务
查看是否开启自动提交事务
set @@autocommit=0; – @@表示全局变量 0 代表关闭 1 代表开启

原理:

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

事务的步骤

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

回滚点:

概念：在某些成功的操作完成之后，后续的操作有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。
操作 SQL 语句:
savepoint 名字; – 设置回滚点
rollback to 名字; – 回到回滚点

四:事物的隔离级别

1. 事务的四大特性 ACID

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

2. 隔离级别:

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

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

3. MySQL 四种隔离级别

表格的级别由低到高，“是”表示会出现这种问题，“否”表示不会出现这种问题。
级别名字隔离级别脏读不可重复读幻读数据库默认隔离级别
1 读未提交 read uncommitted 是是是
2 读已提交 read committed 否是是 Oracle 和 SQL Server
3 可重复读 repeatable read 否否是 MySQL
4 串行化 serializable 否否否
隔离级别越高，性能越差，安全性越高。

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

1）查询全局事务隔离级别

SELECT @@tx_isolation;
2）设置隔离级别, 需要退出再重新登录后才能看到隔离级别的变化

SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

五: DCL(Data Control Language)

5.1 创建用户
语法： CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
用户名：将创建的用户名
主机名：指定该用户在哪个主机上可以登录，如果是本地用户可使用 localhost，如果想让该用户
可以从任意主机登录，可以使用通配符 %
密码：该用户的登录密码，密码可以为空，如果为空该用户可以不需要密码登录服务器
具体操作：

-- 创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123 
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- 创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123
CREATE USER 'user2'@'%' IDENTIFIED BY '123';
USE mysql;
SELECT * FROM USER;

5.2 给用户授权
用户创建之后,没什么权限，需要给用户授权。
语法：GRANT 权限 1,权限 2… ON 数据库名.表名 TO ‘用户名’@‘主机名’;
权限：指授予用户的权限，如 create alter select insert update 等。如果授予全部权限使用 ALL
数据库名.表名：该用户可以操作的哪个数据库的哪些表。如果要授予该用户对所有数据库和表的
相应操作权限则可以使用* 表示，如果
‘用户名’@’主机名’: 给哪个用户授权

-- 给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记
录，查询
GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON test.* TO 'user1'@'localhost';
-- 给 user2 用户分配所有权限，对所有数据库的所有表
GRANT ALL ON *.* TO 'user2'@'%'

5.3 撤销授权
语法： REVOKE 权限 1, 权限 2… ON 数据库.表名 FROM ‘用户名’@‘主机名’;

具体示例：
-- 撤销 user1 用户对 test 数据库所有表的操作的权限
REVOKE ALL ON test.* FROM 'user1'@'localhost';

5.4 查看权限
语法：SHOW GRANTS FOR ‘用户名’@‘主机名’;

具体示例：
-- 查看 user1 用户的权限
SHOW GRANTS FOR 'user1'@'localhost';

5.6 修改管理员密码
语法：mysqladmin -uroot -p password 新密码
注意：在未登录 MySQL 的情况下操作，新密码不加引号，需要输入原来密码
具体操作：

将 root 管理员的新密码改成 123456
要求输入旧密码
使用新密码登录
5.7 修改普通用户密码
语法：SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
注意：需要在登陆 MySQL 的情况下操作，新密码要加单引号。

具体操作：
1) 将'user1'@'localhost'的密码改成 123456
2) 使用新密码登录，老密码登录不了
-- 将'user1'@'localhost'的密码改成 123456
SET PASSWORD FOR 'user1'@'localhost'=PASSWORD('123456');

今天,我和你拼了

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
day3-mysql-多表查询和事务操作

day3:多表查询和事务操作一. 多表连接查询分类: 内连接:隐式内连接 , 显示内连接外连接:左外连接 , 右外连接1.1. 笛卡尔积:左表数据和右表数据进行组合,这种效果叫做笛卡尔积消除笛卡尔积现象的影响可以使用where进行条件限制-- 查询员工和部门的名字SELECT emp.`name`, dept.`name` FROM emp...
复制链接

扫一扫