【Java EE】Day04 MySQL多表、事务、事务隔离级别、DCL

一、多表查询

1、概述

• 笛卡尔积：两集合的所有组成情况
• 多表查询：消除笛卡尔积得到的无用数据

2、分类

• 内连接查询(满足指定条件无空值，只显示有关联的数据)
  • 隐式内连接：使用where限制消除无用数据
  • 显式内连接：SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
• 外连接查询
  • 左外连接：left [outer] join，查询左表所有数据及其交集(以左表为参照)
  • 右外连接：right[outer] join，查询右表所有数据及其交集
• 子查询
  • 概念：查询中嵌套查询 
  • 不同结果：
    • 单行单列
    • 多行单列
    • 多行多列

二、事务

1、概念

• 多个步骤的业务操作，要么同时成功要么同时失败(如转账)
• 操作的过程
  • 开启事务：start transaction 
  • 回滚：rollback
  • 提交：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);
-- 0 开启事务
START TRANSACTION;
-- 1张三给李四转账500元
-- 2查询张三账户余额是否大于500
-- 3张三账户-500
UPDATE account SET balance = balance - 500 WHERE NAME='zhangsan';
-- 李四账户+500
-- 出错了
UPDATE account SET balance =balance + 500 WHERE NAME='lisi';
SELECT * FROM account;-- 临时数据的变化，不是持久的变化
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，应该 回滚事务
ROLLBACK;-- 回滚，保证账户的安全性

2、事务提交方式

• 自动提交(默认，-1)，每条DML语句都会自动提交一次事务
• 手动提交(0)：先开启事务再提交
• 修改默认方式：set @@autocommit=0;

3、事务的四大特征：

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

4、事务的隔离级别

• 概念：多个事务操作同一批数据，会引发一些问题，通过设置不同隔离级别进行解决
• 存在的问题：
  • 脏读：读取到另一个事务中没有提交的数据
  • 不可重复读(虚读)：同一事务读取到的数据不一样
  • 幻读：事务查询不到另一事务添加到的数据(读取不到另一个事务中提交的数据)
• 不同的事务隔离级别
  • read uncommitted
    • 读未提交
    • 产生问题：脏读、不可重复读、幻读
  • read committed(Oracle默认)
    • 产生问题：不可重复读、幻读
  • repeatable read(MySQL默认)
    • 产生问题：幻读
    • 可重复读
  • serializable：
    • 串行hang化，锁表，效率低
    • 可以解决所有问题
• 设置事务的隔离级别：SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

三、DCL

1、概述

DDL、DML、DQL、DCL

2、对用户的管理操作

• 添加用户：
  • CREATE USER '用户名@主机名' identify 密码;
  • 在任意电脑上登录：CREATE USER '用户名@%' identify 密码;-- 表示远程登录
• 删除用户：
  • DROP USER '用户名'@'主机名';
• 修改用户密码
  • UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
  • DCL特有简化方式：SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('密码');
• 忘记密码解决方案：　　　　　　　

 3、权限管理

• 查询权限
  • SHOW GRANTS FOR '用户名'@'主机名';
• 授予权限
  • GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  • 授予所有权限给所有表：GRANT ALL ON *.* TO 'zhangsan'@'localhost';
• 撤销权限
  • REVOKE 权限列表 ON 数据库.表名 FROM '用户名'@'主机名';