JAVAWEB学习总结，DAY3(MySql多表查询、事务、权限)

数据库范式

范式(NF)是指：设计数据库表的规则(Normal Form) : 如果要构造一个比较科学的规范的数据库，所需要遵循的规则和规范。

范式的基本分类：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF， 完美范式)。

如果在创建数据库的时候，要遵循的最基本的要求，就是1NF。在1NF基础上满足更多要求，就是2NF。以此类推。但是在实际创建数据库的时候，只要到3NF即可

三大范式
1NF：要求表里所有字段是不可分割的
2NF：在1NF基础上，要求所有的列都要完全依赖于主属性（主要指：主属性是由多字段组合成的情况）
3NF：在2NF基础上，要求所有的列都要直接依赖于主属性 依赖传递：A->B->C 就存在依赖传递关系 A->C

多表查询（重点）

什么是多表查询
在建表的时候，存在拆分表的情况。把数据拆分到多张表里保存，数据之间是有关系的。
拆分成多表之后，要查询数据，就需要从这多张表中查询有关联的数据了。

 多表查询的关键，在于：把多表关联起来，关联的时候一定要有关联条件。

 迪卡尔积：多表关联的时候，没有写关联条件，就得到了无意义的多表数据的排列组合。
 在多表查询中一定要避免迪卡尔积的出现：一定要有关联条件（多张表的乘积）

内连接查询
Ø 从多表中查询一定有关联的数据
Ø 隐式内连接查询：
语法：select * from 表1, 表2 where 关联条件
Ø 显式内连接查询
语法：select * from 表1 inner join 表2 on 关联条件

外连接查询
Ø 查询一张表所有数据，以及另外一张表有关联的数据。左外连接和右外连接本质完全相同，仅仅是方向相反
Ø 左外连接：查询左表所有数据，以及右表相关联的数据
语法：select * from 左表 left [outer] join 右表 on 关联条件
Ø 右外连接：查询右表所有数据，以及左表相关联的数据
语法：select * from 左表 right [outer] join 右表 on 关联条件

子查询
概述：一条select语句结果作为另一条select语法一部分 SELECT 查询字段 FROM 表 WHERE 查询条件;子查询需要放在（）中

单行单列子查询----子查询的结果是一个值
概述：子查询结果只要是 单列 ，肯定在 WHERE 后面作为 条件 SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

查询 oid为1的用户信息
#第一步：查询oid为1的uid的值
SELECT uid FROM orders WHERE oid = 1;
#第二步：查询user表里uid为上一步查询结果值的用户信息
SELECT * FROM USER WHERE uid = 1;
#合成一条SQL：
SELECT * FROM USER WHERE uid = (SELECT uid FROM orders WHERE oid = 1);

多行单列子查询----子查询结果是一个集合
概述：子查询结果只要是 单列 ，肯定在 WHERE 后面作为 条件 子查询结果是单例多行，结果集类似于一个数组，父查询 使用 IN 运算符 SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

1. 先查询大于5000的员工所在的部门id
SELECT dept_id FROM emp WHERE salary > 5000;
2.再查询在这些部门id中部门的名字
SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary >  5000);

多行多列子查询----子查询结果是一张虚拟表，拿虚拟表和其它表关联查询，子查询作为表
概述：子查询结果只要是 多列 ，肯定在 FROM 后面作为 表 SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件; 子查 询作为表需要取别名，否则这张表没用名称无法访问表中的字段

1. 在员工表中查询2011-1-1以后入职的员工
SELECT * FROM emp WHERE join_date > '2011‐1‐1';
1. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011‐1‐1') e WHERE e.dept_id =  d.id;
使用表连接：
SELECT d.*, e.* FROM dept d INNER JOIN emp e ON d.id = e.dept_id WHERE e.join_date > '2011‐1‐1';

MySql 的事务

事务
概述：是数据库的概念，逻辑上的一个操作需要由多个步骤共同完成，组成一个事务的多个操作单元，要么全部成功，要么全部失败
事务的作用：保证组成事务的多个SQL操作，要么全部成功，要么全部失败
使用事务的场景：多条数据变更SQL，要求一起成功的时候，才需要使用到事务
事务的经典场景：银行转账业务，需要保证两步要么全部成功，要么全部失败，如果成功一半，要能够撤销已经执行的SQL
事务的使用步骤：

开启事务：
第一步：转账人扣钱操作，执行一条SQL -----数据变更不会立即保存数据库，而是被MySql暂时保存起来
第二步：收款人加钱操作，执行一条SQL -----数据变更不会立即保存数据库，而是被MySql暂时保存起来
关闭事务:
提交事务：开启事务之后执行的所有SQL立即全部生效，真正的保存到数据库里去
回滚事务：开启事务之后执行的所有SQL全部撤消，所有的数据变更不会生效，回到事务开启之前的状态

事务的管理
1.默认管理方式
MySql默认是自动提交的。 有一个变量”autocommit”值默认是1，是开启状态的。
如果把这个开关关闭掉，再执行多条SQL的数据变更就不会立即生效了，而是被暂时保存起来了。
Ø 查看自动提交的状态：select @@autocommit; ----1：开启状态；0：关闭状态
Ø 关闭自动提交：set autocommit = 0;
Ø 默认管理方式的步骤：

执行SQL1 -----数据变更不会立即生效
执行SQL2 -----数据变更不会立即生效
提交事务commit/回滚事务rollback ------数据立即生效/全部回滚撤消
注意：关闭自动提交，仅仅对当前本次连接有效。

2.手动管理方式
在默认自动提交开启的状态下，可以通过SQL命令，单独手动开启一个事务。然后执行多条SQL语句，之后再关闭事务。
Ø 开启事务：start transaction
Ø 执行n条SQL-----数据变更不会立即生效，而是被暂时保存起来了
Ø 关闭事务：

提交事务：commit; -----刚刚执行的所有SQL全部同时生效
回滚事务：rollback; ----刚刚执行的所有SQL全部撤消变更，回到事务开启之前的状态

事务的回滚点
默认状态下，回滚直接到事务开启之前的状态
设置回滚点，可以回滚到回滚点的位置，而不是事务开启之前的状态

开启事务：start transaction
执行n条SQL
设置一个回滚点：savepoint 回滚点名称
执行n条SQL
回滚：
可以回滚到事务开启之前：rollback
也可双回滚到回滚点的位置：rollback to 回滚点名称

事务的特性ACID
1.事务的ACID四个特性（面试题）
A：Atomicity，原子性。表示事务里的操作是不可分割的，要么全部成功，要么全部失败。不存在成功一半的情况
C：Consistency，一致性。表示事务执行的前后，数据应该是完整的一致的。
I：Isolation，隔离性。表示多个事务同时并发执行时，事务之间应该是相互独立、互不干扰的。
D：Durability，持久性。表示事务执行之后，数据会被持久化保存到数据库里。保存之后不管出现什么问题，数据都在数据库里保存着。

2.事务并发的问题
在不考虑隔离性，或者隔离性不够高的时候，事务并发出现的问题。
Ø 脏读：一个事务，读取到了另外一个事务未提交的数据。是最严重的事务并发问题，必须要避免的问题
Ø 不可重复读：在一个事务里，多次读取的数据不一致。主要是受到其它事务的update操作影响
Ø 虚读/幻读：在一个事务里，多次读取的数据不一致。主要是受到其它事务的insert、delete影响

3.事务的隔离级别（了解）
事务并发有问题的原因是：隔离级别不够高，隔离级别有四种：
read uncommitted : 读未提交，是最低的隔离级别，存在脏读、不可重复读、虚读/幻读
read committed : 读已提交，解决了：脏读 存在：不可重复读、虚读/幻读
repeatable read : 重复读，解决：脏读、不可重复读 存在：虚读/幻读
serializable: 串行化，解决了所有并发问题

安全性：serializable > repeatable read > read committed > read uncommitted
性能： serializable < repeatable read < read committed < read uncommitted

隔离级别的操作：
查看隔离级别：select @@tx_isolation
设置隔离级别：set session transaction isolation level 隔离级别

不同隔离级别的演示
假定：A客户端主要演示用，B客户端作为干扰事务
read uncommitted

设置A的隔离级别为read uncommitted
A和B同时开启事务
A查询一下数据—主要是看原始数据是什么样的
B执行update操作，但是不提交事务
A再查询数据----如果查到的数据变化了，说明A读取到了B未提交的数据，存在：脏读

read committed

设置A的隔离级别为read committed
A和B同时开启事务
A查询一下数据—主要是看原始数据是什么样的
B执行update操作，但是不提交事务
A再查询数据----如果查到的数据不变，说明解决了脏读问题
B提交事务
A再查询数据----看数据是否有变化，说明在A一个事务里多次查询数据不一致，存在：不可重复读问题

repcatable read

设置A的隔离级别为 repeatable read
A和B同时开启事务
A查询一下数据—主要是看原始数据是什么样的
B执行update操作，但是不提交事务
A再查询数据----如果查到的数据不变，说明解决了脏读问题
B提交事务
A再查询数据----看数据是否有变化，如果数据没有变化，说明解决了：不可重复读问题

serializable

设置A的隔离级别为serializable
A和B开启事务
A执行一个查询
B执行一个update操作----处于等待状态。
A事务结束，B会立即执行
A事务一直不结题，B等待超时

DCL语句

概述：DataBase Control Language, 数据库控制语言，主要是DBA用来管理数据库的用户、权限等。需要在登录管理员帐号才能操作

管理用户
1.创建用户
Ø 语法：create user ‘用户名’@’主机名’ indentified by ‘密码’
用户名：创建的帐号名称
主机名：创建的帐号，可以在哪台电脑上登录这个数据库。localhost：表示帐号只能在MySql本机登录；%：表示可以在任意电脑上登录MySql
密码：创建的帐号的密码
Ø 示例：
创建用户名:liuyp，可以在任意电脑上登录，密码是:liuyp
create user ‘liuyp’@’%’ identified by ‘liuyp’;

2.修改用户密码
Ø 语法：set password for ‘用户名’@’主机名’ = password(‘新密码’);
Ø 示例：SET PASSWORD FOR ‘liuyp’@’%’ = PASSWORD(‘12345’);

3.删除用户
Ø 语法：drop user ‘用户名’@’主机名’
Ø 示例：DROP USER ‘liuyp’@’%’;

管理权限
1.增加授权
Ø 语法：grant 权限1, 权限2,… on 数据库名.表名 to ‘用户名’@’主机名’
权限：select， update， delete， alter, drop等等。all表示所有权限
数据库名：这些权限可以操作哪个数据库
表名：这些权限可以操作哪张表
‘用户名’@’主机名’：把权限授给哪个用户
Ø 示例：给liuyp用户增加授权：可以任意操作heima41的所有表
Ø Grant all on heima42.* to ‘liuyp’@’%’;

2.查看授权
Ø 语法：show grants for ‘用户名’@’主机名’
Ø 示例：SHOW GRANTS FOR ‘liuyp’@’%’;

3.取消授权
Ø 语法：revoke 权限1, 权限2,… on 数据库名.表名 from ‘用户名’@’主机名’
Ø 示例：取消liuyp用户的update权限