MySQL最基础（三）：多表查询、事务管理和部分DCL

by konley

mysql最基础第三弹（完结篇），主要涉及多表查询、事务管理和部分DCL知识

十、多表查询

10.1笛卡尔积

如果直接这样子查两个表

select * from emp,dept;

将会产生表1字段数*表2字段数条记录

我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要
通过条件过滤掉没用的数据

笛卡尔积

有两个集合AB，取这两个集合的所有组成情况（相乘）

要完成多表查询，首先应该消除笛卡尔积中无用、冗余的数据。

10.2 内连接查询

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

1.隐式内连接

语法

...where 多表条件
一般为 表1.外键 = 表2.主键
where 学生表.选课代码 = 课程表.课程代码

使用 where 条件消除无用数据

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

--查询员工表的名称、性别和部门
select 
	emp.name,
	emp.gender,
	dept.name
from
	emp,
	dept
where
	emp.dept_id = dept.id;

推荐的内连接写法，起别名

--查询员工表的名称、性别和部门
select 
	t1.name as 姓名,
	t1.gender as 年级,
	t2.name as 课程名
from
	--先写from行，再写select行有代码提示
	emp as t1,
	dept as t2
where
	--where也要使用别名
	t1.dept_id = t2.id;

2.显式内连接

语法

select 字段列表 from 表名1 [inner] join 表名2 on 条件

inner可以省略

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

推荐写法：

select 
	emp.name as 姓名,
	emp.gender as 年级,
	dept.name as 课程名
from 
	emp join dept 
on 
	emp.dept_id = dept.id

--可以起别名
select 
	t1.name as 姓名,
	t1.gender as 年级,
	t2.name as 课程名
from 
	emp as t1 join dept as t2
on 
	t1.dept_id = t2.id

10.3 外连接查询

1.左外连接

语法

select 字段列表 from 表1 left join 表2 on 条件

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

--查询所有员工信息，如果员工有部门，则查询部门名称，没有部分，则不显示部门名称（null）
select
	t1.*,
	t2.name
from
	emp as t1 left join dept as t2
on
	t1.dept_id = t2.id

2.右外连接

语法

select 字段列表 from 表1 right [outer] join 表2 on 条件

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

--查询所有部门下的员工信息
select
	* 
from
	dept as t2
	right join emp as t1
on 
	t1.`dept_id` = t2.`id`

3. 如何理解内外连接

内连接：连接字段的值在2个表都完全相同
左外连接：连接字段的值在2个表都相同的，或者连接字段的值在左表有而右表没有
右外连接：连接字段的值在2个表都相同的，或者连接字段的值在右表有而左表没有

左连接就是以左为主，右边的记录可能为空；右连接就是以右为主，左边的记录可能为空。

10.4 子查询

查询中嵌套查询，称嵌套查询为子查询

例子：查工资最高的员工信息

--1.先查出工资最高的
select max(salary) from emp;
--2.根据工资查员工信息
select * from emp where salary=9000;

多条查询可以嵌套查询，即是子查询，记得加括号

--嵌套查询
SELECT
	* 
FROM
	emp 
WHERE
	salary = ( SELECT MAX( salary ) FROM emp );

1.子查询的结果为单行单列

子查询可以做为条件，使用运算符去判断，= > >= < <=等

--查询员工平均工资
SELECT
	* 
FROM
	emp 
WHERE
	salary < ( SELECT avg( salary ) FROM emp );

2.子查询的结果为多行单列

子查询可以作为条件，使用运算符in来判断，适用于多种结果的集合

--查询'财务部'和'市场部'所有的员工信息

--1.查市场部和财务部的id，此时会查出两行id，此时应该用in来获取集合
select id from dept where name = '财务部' or name = '市场部'

--2.嵌套为一条
SELECT
	* 
FROM
	emp 
WHERE
	dept_id IN ( SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部' );

3.子查询的结果为多行多列

将子查询的结果看成一张虚拟的表，参与其他查询

查询在’2011-11-11’后入职的员工信息和部门信息

--用普通内连接
SELECT
	* 
FROM
	emp AS t1,
	dept AS t2 
WHERE
	t1.dept_id = t2.id 
	AND t1.join_date > "2011-11-11"

--用虚拟表

--1.查'2011-11-11'后入职的员工信息
select * from emp where join_date > '2011-11-11'

--2.将1查到的结果当成一张表嵌套进去再次参与查询
select 
	*
from
	(select * from emp where join_date > '2011-11-11') as t1, --这里看成一张表
	dept as t2
where
	t1.dept_id = t2.id;

10.5 查询练习

-- 需求：
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT
	emp.id 员工编号,
	emp.ename 员工姓名,
	emp.salary 工资,
	job.jname 职务名称,
	job.description 职务描述 
FROM
	emp
	JOIN job ON emp.job_id = job.id;
	
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
	emp.id 员工编号,
	emp.ename 员工姓名,
	emp.salary 工资,
	job.jname 职务名称,
	job.description 职务描述,
	dept.dname 部门名称,
	dept.loc 部门位置
FROM
	emp JOIN job ON emp.job_id = job.id
	JOIN dept ON emp.dept_id = dept.id;
	
-- 3.查询员工姓名，工资，工资等级
SELECT
	emp.ename 员工姓名,
	emp.salary 工资,
	salarygrade.grade 工资等级
FROM
	emp JOIN salarygrade ON emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary;
	
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
	emp.ename 员工姓名,
	emp.salary 工资,
	job.jname 职务名称,
	job.description 职务描述,
	dept.dname 部门名称,
	dept.loc 部门位置,
	salarygrade.grade 工资等级
FROM
	emp JOIN salarygrade ON emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary 
	JOIN dept ON emp.dept_id = dept.id
	JOIN job ON emp.job_id = job.id;
	
-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT
	dept.dname 部门编号,
	dept.dname 部门名称,
	dept.loc 部门位置,
	COUNT(emp.id) 部门人数
FROM
	emp JOIN dept ON emp.dept_id = dept.id
GROUP BY
	dept.id
	
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
	-- 即是在查出的交集情况下 再追加现有结果上所没有的表2的补集
SELECT
	t1.ename 员工,
	t1.mgr 领导编号,
	t2.ename 领导
FROM
	emp t1
LEFT JOIN
	emp t2
ON
	t1.mgr = t2.id;

十一、事务

11.1 事务概念

概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作：
1. 开启事务： start transaction;
2. 回滚：rollback；
3. 提交：commit;

例子：

CREATE TABLE account (
			id INT PRIMARY KEY AUTO_INCREMENT,
			NAME VARCHAR(10),
			balance DOUBLE
		);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 张三给李四转账 500 元
		-- 0. 开启事务
		START TRANSACTION;
		-- 1. 张三账户 -500
		
		UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
		-- 2. 李四账户 +500
		-- 出错了...
		UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
		
		-- 发现执行没有问题，提交事务
		COMMIT;
		
		-- 发现出问题了，回滚事务
		ROLLBACK;

MySQL数据库中事务默认自动提交
- 事务提交的两种方式：
  * 自动提交：
  * mysql就是自动提交的
  * 一条DML(增删改)语句会自动提交一次事务。
  * 手动提交：
  * Oracle 数据库默认是手动提交事务
  * 需要先开启事务，再提交
- 修改事务的默认提交方式：
  - 查看事务的默认提交方式：SELECT @@autocommit; – 1 代表自动提交 0 代表手动提交
  - 修改默认提交方式： set @@autocommit = 0;

11.2 事务的四大特征

原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间。相互独立。
一致性：事务操作前后，数据总量不变

11.3 事务的隔离级别

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
- 存在问题：
  1. 脏读：一个事务，读取到另一个事务中没有提交的数据
  2. 不可重复读(虚读)：在同一个事务中，两次读取到的数据不一样。
  3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
- 隔离级别：
  1. read uncommitted：读未提交
    - 产生的问题：脏读、不可重复读、幻读
  2. read committed：读已提交（Oracle）
    - 产生的问题：不可重复读、幻读
  3. repeatable read：可重复读（MySQL默认）
    - 产生的问题：幻读
  4. serializable：串行化
  - 可以解决所有的问题
- 注意：隔离级别从小到大安全性越来越高，但是效率越来越低
- 数据库查询隔离级别：
- select @@tx_isolation;
  - 数据库设置隔离级别：
    - set global transaction isolation level 级别字符串;
  - 演示：
```
set global transaction isolation level read uncommitted;
start transaction;
-- 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
```

十二、DCL

12.1 SQL分类

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

12.2 DBA：数据库管理员

12.3 DCL：管理用户，授权

12.3.1 管理用户

添加用户：
* 语法：CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
删除用户：

语法：DROP USER ‘用户名’@‘主机名’;

修改用户密码：

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. 使用新密码登录。

查询用户：

-- 1. 切换到mysql数据库
USE myql;
-- 2. 查询user表
SELECT * FROM USER;
通配符： % 表示可以在任意主机使用用户登录数据库

12.3.2 权限管理

1.查询权限：
	-- 查询权限
	SHOW GRANTS FOR '用户名'@'主机名';
	SHOW GRANTS FOR 'lisi'@'%';
	
2. 授予权限：
	-- 授予权限
	grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
	-- 给张三用户授予所有权限，在任意数据库任意表上
	GRANT ALL ON *.* TO 'zhangsan'@'localhost';
	
3. 撤销权限：
	-- 撤销权限：
	revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
	REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';

posted @ 2022-10-22 12:16 konley 阅读(50) 评论(0) 收藏举报

刷新页面返回顶部

Konley

逆风如解意，容易莫摧残。