MySQL - 数据库进阶

数据库进阶

一、事务

事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。

1.事务的语法：

开始事务：start transaction; / begin;
提交事务：commit;
撤销事务：rollback;

2.事务的ACID特性：

原子性（Atomicity）

事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作在一次执行过程中，只允许出现两种状态之一。要么全部执行成功，要么全部执行失败。事务开始后的所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

一致性（Consistency）

事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。例如：如果从A账户转账到B账户，不可能因为A账户扣了钱，而B账户没有加钱。

隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。

持久性（Duration）

事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

3.事务的并发问题：

脏读：读取到了没有提交的数据，事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
不可重复读：同一条命令返回不同的结果集（更新）。事务 A 多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：重复查询的过程中，数据就发生了量的变化（insert、delete）。

4.事务隔离级别：

读未提交（READ_UNCOMMITTED）：会出现脏读、不可重复读、幻读。
读已提交（READ_COMMITTED）：会出现不可重复读、幻读。
可重复读（REPEATABLE_READ） ：可能会出现幻读。
顺序读（SERIALIZABLE）：不支持并发，不会出现任何并发问题。
4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。 一般数据默认级别是读已提交或可重复读。
查看当前会话中事务的隔离级别：
select @@tx_isolation;
设置当前会话中的事务隔离级别：
set session transaction isolation level read uncommitted;

读未提交（READ_UNCOMMITTED）

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理某一数据，并对其进行了更新，即使尚未提交事务，另一个事务也能够访问更新后的该数据，若此时原事务被撤销，则出现脏读。

读已提交（READ_COMMITTED）

读已提交是不同的事务执行的时候只能获取到已经提交的数据，这样就不会出现上面脏读的情况了。但是如果一个事务访问数据之后，另一个事务更新了数据并提交事务，原事务再次访问数据，就会出现两次访问的数据不一致的情况，这就叫不可重复读。

可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的情况。现有一个事务访问了数据，另外一个事务插入了一条数据并提交事务，此时由于可重复读原事务再次访问数据和第一次访问一致，但如果此时原事务对数据进行范围修改后再次访问数据，就会发现另外一个事务插入的数据混了进来，这就叫幻读，也称为诡异的更新事件。

顺序读（SERIALIZABLE）

顺序读是最严格的事务隔离级别，它要求所有的事务排队顺序执行，即事务只能一个接一个的处理，不能并发。

5.不同隔离级别锁的情况：

读未提交（RU）：有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的。
序列列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了。

6.隐式提交：

执行DDL定义语句（建库、建表、修改表、索引操作、存储过程、视图）时，相当于执行了事务提交。

二、MySQL存储过程

1.存储过程介绍：

目前使用的大多数SQL语句都是针对一个或多个表的单条语句，但是并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成，此时可以创建存储过程。
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。存储过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调用。存储过程就像脚本语言中函数的定义一样。

2.存储过程的优缺点：

优点：
可以把一些复杂的SQL进行封装，简化复杂操作；
保证了数据的完整性，防止错误；
简单的变动只需要更改存储过程的代码即可；
可以提高性能，因为使用存储过程比使用单独的SQL语句要快（预先编译）。
缺点：
存储过程的编写SQL语句复杂；
一般可能并没有创建存储过程的权限，只能调用。

3.创建存储过程：

\d // 用于修改MySQL默认的语句结尾符 ; 为 // 。
create procedure是创建语句，圆括号内可传递参数。
begin和end语句用来限定存储过程体。

\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @i=@i+1;
end while;
end;
//

执行存储：call p1();
查看存储过程：show create procedure p1\G;
删除存储过程：drop procedure p1;

三、MySQL触发器

MySQL语句在需要时被执行，存储过程也是如此。
但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，该怎么办呢？

1.触发器介绍：

触发器是MySQL响应写操作（增、删、改）而自动执行的一条或一组定义在begin和end之间的MySQL语句。也可理解为：提前定义好一个或一组操作，在指定的SQL操作前或后来触发指定的SQL自动执行。触发器就像是JavaScript中的事件一样。

2.触发器语法：

trigger_name：触发器名称；
trigger_time：触发时间，可取值：BEFORE或AFTER；
trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE；
tb1_name：指定在哪个表上；
trigger_stmt：触发处理SQL语句。

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

查看所有的触发器：show triggers\G;
删除触发器：drop trigger trigger_name;

3.创建触发器：

例如：创建一个删除的触发器，在users表中删除数据之前，往del_users表中添加一个数据。

\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id,old.name,old.age,old.account);
end;
//

在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，访问被插入的行。
在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行。OLD中的值全都是只读的，不能更新，在AFTER DELETE的触发器中无法获取OLD虚拟表。
在UPDATE触发器代码内，可以引用一个名为OLD的虚拟表访问更新以前的值，也可以引用一个名为NEW的虚拟表访问新更新的值。

四、MySQL视图

1.视图介绍：

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
视图仅仅是用来查看存储在别处的数据的一种设施或方法。
视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。
在添加或更改这些表中的数据时，视图将返回改变过的数据。
因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。
如果用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

2.视图的作用：

1.重用SQL语句。
2.简化复杂的SQL操作，在编写查询后，可以方便的重用它而不必知道它的基本查询细节。
3.使用表的组成部分而不是整个表。
4.保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5.更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。
6.注意：视图不能索引，也不能有关联的触发器或默认值。

3.视图的基本语法：

创建视图：
create view v_users as select id,name,age from users where age >= 25 and age <= 35;
查看所有的表和视图：show tables;
查看所有视图：show table status where comment=‘view’;
删除视图：drop view v_t1;
view视图的帮助信息：? view;