MySQL多表查询与事务操作的概述

最新推荐文章于 2024-08-28 18:34:29 发布

轩鸢无敌

最新推荐文章于 2024-08-28 18:34:29 发布

阅读量235

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/kentlhxy/article/details/110409155

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL多表查询与事务的操作

多表查询

什么是多表查询？

顾名思义，就是通过多张表进行查询想要的信息

创建员工表，部门表

-- 部门表
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('小罗','男',8200,'2020-10-24',1);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小月','女',9200,'2020-5-04',1);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小黑','男',9000,'2019-09-08',2);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小轩','男',6000,'2018-10-07',3);
	INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('小红帽','女',5500,'2021-03-12',2);

在这里插入图片描述

笛卡尔积

在查询过程中，可能会遇到笛卡尔积现象，即A，B两个集合所组成的所有情况，要完成多表查询，我们需要消除笛卡尔积现象，怎么消除？可以通过条件来过滤掉无用的数据

多表查询的分类

在这里插入图片描述

内连接

使用where条件来过滤无用数据

内连接查询步骤

1.确定查询哪些表
2.条件是什么
3.查询什么字段

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

-- 查询所有员工信息和对应部门的信息
select * from emp ,dept where emp.id = dept.id;

显示内连接：使用inner join…on语句，省略inner

select * from emp t1 join dept t2 on t1.dept_id = t2.id;

在这里插入图片描述

外连接

左外连接：用左边表的数据去匹配右边表的数据，左边表显示所有数据

查询的是左表所有数据以及其交集部分。

语法：

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

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称 ,新添加一个员工111没有部门
select t1.* ,t2.name from emp t1 left join dept t2 on t1.dept_id = t2.id;

在这里插入图片描述

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

右外连接：查询的是右表所有数据以及其交集部分。

语法：

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

同左外连接，只是左右边表位置不一样

子查询

查询中嵌套查询，其中嵌套的查询为子查询

子查询有三种情况

1.单行单列

2.多行单列

3.多行多列

单行单列：

-- 查询工资最高的员工信息
1.查询最高工资的员工
2.查询该员工的信息
select max(salary) from emp;  -- 最高工资为9200
select * from emp where emp.salary = 9200; --查询到该员工的信息
-- 使用子查询
select * from emp where emp.salary = (select max(salary) from emp);  
-- 括号的内部查询就是子查询

多行单列：

-- 查询开发部和市场部所有员工的信息
1.查询开发部和市场部对应的id  -- 多行单列
select id from dept where name ='市场部' or name = '开发部';
select id from dept where name in('市场部','开发部');

2.查询对应部门id所对应的员工信息
select * from emp where dept_id = 1 or dept_id = 2;
select * from emp where dept_id in(1,2);

-- 子查询
select * from emp where dept_id in(select id from dept where name in('市场部','开发部'));

多行多列：

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

-- 查询员工在2020年入职的员工信息和部门信息
select * from dept t1 ,(select * from emp where emp.join_date>='2020-01-01') t2
where t1.id = t2.dept_id; 

-- 普通内连接同样可以查询
select * from emp ,dept where emp.dept_id = dept.id and emp.join_date >='2020-01-01';

-- 美化格式
SELECT
	*
FROM
	emp,
	dept
WHERE
	emp.dept_id = dept.id
AND emp.join_date >= '2020-01-01';

事务

概念

一个包含了多个步骤的业务操作，被事务管理，如果操作成功则同时成功，操作失败，则同时失败，是一个整体。

在这里插入图片描述

操作

1.开启事务 ： start transaction;
事务开启之后, 所有的操作都会临时保存到事务日志中, 事务日志只有在得到 commit 命令才会同步到数据表
中，其他任何情况都会清空事务日志(rollback，断开连接) 
2.事务回滚 :rollback;
当sql语句出现错误，就会进行事务回滚，回滚到刚开始的地方
-- 回滚点
我们可以设置回滚点，让事务不用返回所有操作
3.提交： commit;

回滚点

回滚点操作语句	语句
设置回滚点	savepoint 名字
返回回滚点	rollback to 名字

事务提交的两种方式

1.自动提交

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

2.手动提交

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

修改事务的默认提交方式

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

事务的四大特征

事务特性	含义
原子性(Atomicity)	不可分割的最小操作单位，事务中的所有的SQL语句要么同时成功，要么同时失败
持久性(Durability)	当事务提交或者回滚后，数据库会持久化保存，关机也可以保存下来
隔离性(lsolation)	事务与事务之间，是相互独立的，执行时保持隔离状态
一致性(Consistency)	事务操作前后，数据总量不变，数据库状态前后保持一致

事务的隔离级别

多个事务间是相互独立的，但是如果多个事务操作同一批数据(并发操作)，可能会产生问题，可以设置隔离级别来解决

并发访问引起的问题	意义
脏读	一个事务读取到另一个事务中没有提交的数据
不可重复读(虚读)	一个事务中，两次读取的数据内容不一致。事务 update 时引发的问题
幻读	一个事务中两次读取的数据的数量不一致。 insert 或 delete 时引发的问题。

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

隔离级别

级别	隔离级别	产生问题	数据库默认隔离级别
1	read uncommitted(读未提交)	脏读、不可重复读、幻读
2	read committed(读已提交)	不可重复读、幻读	Oracle、SQL Server
3	repeatable read(可重复读)	幻读	MySQL
4	serializable(串行化)	可以解决所有问题

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

MySQL隔离级别命令

查询隔离级别

select @@tx_isolation;

设置隔离级别

set global transaction isolation level  级别字符串;

DCL (Data Control Language)

SQL分类：

1.DDL：操作数据库和表
2.DML：增删改表中数据
3.DQL：查询表中数据
4.DCL：管理用户，授权

添加用户

create user '用户名'@'主机名' identified by '密码';

删除用户

drop user '用户名'@'主机名';

修改用户密码

update user set password = password('新密码') where user = '用户名';

set password for '用户名'@'主机名' = password('新密码');

mysql忘记root用户密码

1.管理员运行cmd  
net stop mysql -- 停止mysql服务
2.使用无验证方式启动mysql服务mysqld --
 skip-grant-table
3.打开新cmd窗口，输入mysql命令回车，登录
4.use mysql
5.update user set password = password('你的新密码') where user = 'root';
6.关闭两个窗口，打开任务管理器，手动结束mysqld.exe 的进程
7.启动mysql服务，使用新密码登录

查询用户

-- 切换到mysql数据库
use mysql;
--查询user表
select * from user；

-- 通配符： 
% 表示可以在任意主机使用用户登录数据库

权限管理

-- 权限
用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权
限则使用 ALL
-- 查询权限
show grants for '用户名'@'主机名';

-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

--撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';