mysql 多表事务,【数据库_03】MySQL-多表操作与事务

一、多表查询

1. 内连接

① 隐式内连接

* 语法

select * from 表1，表2 where 连接条件;

* 示例

select * from emp,dept where emp.dept_id = dept.id;

② 显示内连接

* 语法

select * from 表1 [inner] join 表2 on 连接条件;

* 示例

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

③ 注意

* 内连接查询的数据不包含连接条件字段为 null 的数据

2. 外连接

① 左外连接

* 语法

select * from 表1 left [outer] join 表2 on 连接条件;

* 示例

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

② 右外连接

* 语法

select * from 表1 right [outer] join 表2 on 连接条件;

* 示例

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

③ 注意

* 外连接查询的是前面表的所有字段加上后面表与前面表的交集

* 左右外连接结果要看谁在前面谁在后面

④ 自连接

* select * from emp A jion emp B on A.id = B.u_id;

左连接和右连接看似可以互换，但是要考虑大小表问题，如：(小表 左连 大表) (大表 右连 小表) 效率天差地别

二、子查询

1. 子查询的位置

① 什么是子查询

* select 嵌套查询

* 示例

select

ename,(select ename from emp m where t.mgr = m.id)

from emp t;

② 子查询可以在的位置

* select (子查询)

* where (子查询)

* from (子查询)

三、事务

1. 概述

① 什么是事务

* 被事务管理的操作要么都成功要么都失败

② 事务的提交方式

* 自动提交(MySQL)

* 执行完一条SQL语句后自动提交

* 手动提交(Oracle)

* 执行完一条SQL语句后需要手动提交，否则操作不持久化

③ 事务的四大特征

* 原子性

是不可分割的最小单位，要么都成功，要么都失败

* 持久化

事务结束后，数据库会持久化保存

* 隔离性

多个事务之间相互独立

* 一致性

事务操作前后数据量不变(类似能量守恒)

2. 事务执行的步骤

① 开启事务

start transaction

② 执行操作

delete ···

insert ···

update ···

③ 提交事务 -- 不提交不会持久化

commit

④ 回滚事务

rollback -- 事务从哪里开始回滚到哪里

3. 事务的隔离级别

① read uncommitted(读未提交)

* 脏读：读到了其他事务没有提交的数据

* 不可重复读：同一个事务中，读到了另一个事务 update 的数据

* 幻读(虚读)：同一个事务中，读到了另一个事务 insert 的数据

② read committed(读已提交) -- Oracle 默认

* 不可重复度、幻读

③ repeatable read(可重复读) -- MySQL 默认

* 幻读

④ serializable(串行化)

* 可以解决所有问题，但是效率极低

4. 设置事务隔离级别

① 查看事务隔离级别

* select @@tx_isolation;

② 设置事务隔离级别

* set global transaction isolation level 隔离级别;

三、DCL　-- 由DBA(database　admin)操作

1. 用户管理

① 创建用户

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

* 示例

create user 'work'@'%' identified by '123'; -- % 代表任意主机

② 删除用户

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

* 示例

drop user 'work'@'localhost'; -- 删除本地的 work 用户

③ 修改用户密码

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

* 示例

update user set password = password('work') where user = 'work';

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

示例

-- 修改主机为 192.168.62.55 的work用户密码

set password for 'work'@'192.168.63.55' = password('root');

④ 查询用户

* 在 mysql 数据库中有 user 表用来存储用户

* 步骤

use mysql --> select * from user;

2. 用户授权

① 查询权限

* show grants for '用户名'@'主机';

## information_schema 是一个虚拟数据库，是视图，

它默认所有用户都有权限访问，就算是刚创建和收回所有权限的用户也可以访问，但是里面的数据只有已被授权的部分。

② 授予权限

* grant 权限 on 数据库.表名 to '用户名'@'主机';

* 示例

-- 授予本地用户 work 查询和修改 数据库 db 中 user 表的权限

grant select,update on db.user to 'work'@'localhost';

* all ---> 所有权限

*.* ---> 所有库所有表

③ 权限收回

* revoke 权限 on 数据库.表名 from '用户名'@'主机';

* 示例

-- 收回本地用户 work 的所有权限

revoke all on *.* to 'work'@'localhost';

SQL的执行顺序

from：将数据从硬盘加载到数据缓冲区，方便对接下来的数据进行操作

where：从基表或视图中选择满足条件的元组(不能使用聚合函数)

join：多表查询连接查询

on：多表查询连接查询条件

group by：分组，一般和聚合函数一起使用

having：在元组的基础上进行筛选，选出符合条件的元组(一般与GROUP BY进行连用)

select：查询到得所有元组需要罗列的哪些列

distinct：去重的功能(Oracle特有)

union：将多个查询结果合并

order by：进行相应的排序

limit：分页查询(MySQL特有)