【事务、视图、触发器、存储过程】

一、事务

1.1事务的概念

1.1.1 什么是事务

如果一个业务操作中多次访问到了数据库，必须保证每条SQL语句都执行成功。如果有一条执行失败，那么所有已经执行过的代码必须回滚（也就是撤销）。回到没有执行前的状态。称为事务。简单来说就是要么所有的SQL语句全部执行成功，要么全部失败。

1.1.2事务的四大特性

事务	含义
原子性（Atomicity）	事务是工作的最小单元，整个工作单元要么全部执行成功，要么全部执行失败
一致性（Consistency）	事务执行前与执行后，数据库中数据应该保持相同的状态。如：转账前总金额与转账后总金额相同。
隔离性（Isolation）	事务与事务之间不能互相影响，必须保持隔离性。
持久性（Durability）	如果事务执行成功，对数据库的操作是持久的。

1.2事务的应用场景说明

drop database if exists db04;

create database db04;

use db04;
CREATE TABLE account (  
    id INT PRIMARY KEY AUTO_INCREMENT,  
    name VARCHAR(10),  
    money DOUBLE  
); 

INSERT INTO account (name, money) VALUES ('a', 1000), ('b', 1000);

-- 转账操作

-- 有两条UPDATE的更新操作

-- a转500给b
update account set money=money-500 where name='a';

update account set money=money+500 where name='b';

-- 还原
update account set money=1000;

假设当a账号上-500元，服务器崩溃了。b的账号并没有+500元，数据就出现问题了。我们需要保证其中一条SQL语句出现问题，整个转账就算失败。只有两条SQL都成功了转账才算成功。这个时候就需要用到事务。

1.3 手动提交事务

MYSQL中可以有两种方式进行事务的操作：

• 手动提交事务
• 自动提交事务，默认是自动提交事务。

1.3.1 手动提交事务的SQL语句

功能

功能	SQL语句
开启事务	start transaction/begin
提交事务	commit
回滚事务	rollback

1.4自动提交事务

MySQL默认每一条DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，执行完毕自动提交事务，MySQL默认开始自动提交事务

1.5 事务原理

事务开启之后, 所有的操作都会临时保存到事务日志中，事务日志只有在得到commit命令才会同步到数据表中，执行完commit或rollback都会清空事务日志(rollback，断开连接)

• 如果没有开启事务，用户不使用日志文件，而是直接写到数据库
• 如果查询，数据从表中查询出来以后，经过日志文件加工以后返回。
• 如果回滚，清除日志文件，不会写到数据库中。
  

1.6回滚点

1.6.1什么是回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

1.6.2 回滚点的操作语句

回滚点的操作语句	语句
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字

二、事务的隔离级别

2.1并发访问的三个问题

并发访问下事务产生的问题：
当同时有多个用户在访问同一张表中的记录，每个用户在访问的时候都是一个单独的事务。
事务在操作时的理想状态是：事务之间不应该相互影响，实际应用的时候会引发下面三种问题。应该尽量避免这些问题的发生。通过数据库本身的功能去避免，设置不同的隔离级别。

• 脏读： 一个事务（用户）读取到了另一个事务没有提交的数据
• 不可重复读：一个事务多次读取同一条记录，出现读取数据不一致的情况。一般因为另一个事务更新了这条记录而引发的。
• 幻读：在一次事务中，多次读取到的条数不一致

2.2 设置隔离级别

2.2.1 四种隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle和SQL Server
3	可重复读	repeatable read	否	否	可能会有	MySQL
4	串行化	serializable	否	否	否

2.2.2 四种隔离级别起的作用：

• 1）Read uncommitted (读未提交)： 简称RU隔离级别，所有事务中的并发访问问题都会发生，可以读取到其他事务没有提交的数据
• 2）Read committed (读已提交)：简称RC隔离级别，会引发不可重复读和幻读的问题，读取的永远是其他事务提交的数据
• 3）Repeatable read (可重复读)：简称RR隔离级别，会引发幻读的问题，一次事务读取到的同一行数据，永远是一样
• 4）Serializable (串行化)： 可以避免所有事务产生的并发访问的问题 效率及其低下

2.3 安全和性能对比

• 隔离级别越高，安全性就越高，性能越低
• 隔离级别越第，安全性就越低，性能越高。

2.4 MySQL相关的命令：

查询全局事务隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql>

设置全局事务隔离级别

set global transaction isolation level 四种隔离;		-- 服务器只要不关闭一直有效

修改隔离级别后需要重启会话

2.5脏读

在并发情况下，一个事务读取到另一个事务没有提交的数据，这个数据称之为脏数据，此次读取也称为脏读。
只有read uncommitted（读未提交）的隔离级别才会引发脏读。

将MySQL的事务隔离级别设置为read committed（读已提交）：

mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

2.5.1 解决脏读

将全局的隔离级别进行提升

set global transaction isolation level read committed;

2.6 不可重复读

概念： 在同一个事务中的多次查询应该出现相同的结果，两次读取不能出现不同的结果。

2.6.1 解决不可重复读

将数据进行恢复

update account set money=1000;

-- 设置隔离级别为repeatable read
set global transaction isolation level repeatable read;

这个时候要重启一下窗口

2.7 幻读

概念：一次事务多次读取到的条数不一致而引发的问题；
虽然解决了一些地方的幻读，但在一些特殊的情况下，还是会引发幻读问
一次事务多次读取到的条数不一致会导致有很多情况发生

2.8串行（xing）化

2.8.1概念

想要彻底的解决幻读，那么我们必须再把隔离级别调高，数据库的最高隔离级别为串行化（serializable）
串行化相当于锁表操作，即一个事务如果操作了某张表(增加、删除、修改)，那么就不允许其他任何事务操作此表，也不允许查询，等第一个事务提交或者回滚之后才可以操作，这样做效率及其低下，因此一般不会采用serializable隔离级别

三、触发器与视图

3.1触发器

3.1.1介绍

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
MySQL中触发器一共有三种：

触发器类型	NEW和OLD的使用
insert触发器	NEW 表示将要或者已经新增的数据
update触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
delete触发器	OLD 表示将要或者已经删除的数据

3.1.2 触发器语法

create trigger trigger_name [after/before] [insert/update/delete] 
on table_name
for each row
begin
	......
end;
--after：在插入表之后执行
--before：在插入表之前执行

3.2 视图

视图（View）是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图其实就是一个select返回的结果集，用于方便我们查询而创建的"临时表"，简化我们的查询语句。
视图的优势：

• 方便：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'产品');


DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`id`,`name`,`class_id`) values 
(1,'张三',1),
(2,'李四',1),
(3,'王五',2),
(4,'赵六',1),
(5,'孙七',3),
(6,'王八',3);

3.2.1 创建视图

视图创建的语法

CREATE [OR REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)] 
AS select_statement  
　　[WITH [CASCADED | LOCAL] CHECK OPTION]
　　--OR REPLACE：如图视图名重复，则表示替换原有视图
　　--ALGORITHM：表示创建视图时所使用的算法
　　      --  UNDEFINED：默认值，没有指定算法时一般采用merge算法
　　      --  MERGE：合并算法，在基于视图创建新的视图时，将创建视图时所使用的select语句与将要创建新的视图的select语句进行合并处理，效率较高。
　　      --  TEMPTABLE：临时表算法，在基于视图创建新的视图时，先执行原有视图的select语句，然后再执行新视图的select语句，查询两次，效率较低。

方括号里的东西都可以选择省略不写
需要注意的是：对视图进行任何的新增、修改、删除将会对直接作用到源表

3.2.2 修改视图

创建时修改
语法：

CREATE [OR REPLACE]   
　　[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]  
　　[DEFINER = { user | CURRENT_USER }]  
　　[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]  
AS select_statement  
　　[WITH [CASCADED | LOCAL] CHECK OPTION]

使用alter语句更改视图
语法：

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { USER | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

更新视图
语法：

update viewName set column=newValue

3.2.3 删除视图

删除视图时，只能删除视图的定义，不会删除数据，也就是说不动源表
语法：

DROP VIEW [IF EXISTS]   
view_name [, view_name] ...

四、存储过程和存储函数

MySQL中提供存储过程与存储函数机制，我们先将其统称为存储程序，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

4.1 存储程序优缺点

优点：
1）性能：存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。
2）复用：存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
3）安全：存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。
缺点：
1）如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加。
2）很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
3）我们在开发中，性能的瓶颈往往就是数据库层，我们应该想尽办法来优化数据库的性能，而不是徒增数据库的压力。
4）业务耦合，如果编写存储程序，那么业务逻辑滞后到了数据库端

4.2 存储过程的使用

语法：

CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL语句
end ;

调用存储过程

call test1();

查看存储过程

-- 查看db01数据库中的所有存储过程
select name from mysql.proc where db='db04';

-- 查看存储过程的状态信息
show procedure status;

-- 查看存储过程的创建语句
show create procedure test1;

删除存储过程

drop procedure test1;

4.3 存储过程的语法

4.3.1变量

declare：声明变量

CREATE PROCEDURE test2 ()
begin
	
	declare num int default 0;		-- 声明变量,赋默认值为0
	select num+10;
	
end ;

call test2();			-- 调用存储过程

set:赋值

CREATE PROCEDURE test3 ()
begin
	
	declare num int default 0;
	set num =20;			-- 给num变量赋值
	select num;
	
end ;

call test3();

into：赋值

CREATE PROCEDURE test4 ()
begin
	
	declare num int default 0;			
	select count(1) into num from student;			-- 从student表中查询count(1)的结果,然后将结果赋值给num变量
	select num;
	
end ;

call test4();

4.3.2 if语句

CREATE PROCEDURE test5 ()
begin
	
	declare id int default 1;			
	declare class_name varchar(30);
	
	if id=1 then
		set class_name='哇塞，Java大佬！';
	elseif id=2 then
		set class_name='原来是UI的啊';
	else
		set class_name='不用想了，肯定是产品小样';
	end if;
	
	select class_name;
	
end ;

call test5();

4.3.3 传递参数

语法：

create procedure procedure_name([in/out/inout] 参数名  参数类型)

• in：该参数可以作为输入，也就是需要调用方传入值 , 默认
• out：该参数作为输出，也就是该参数可以作为返回值
• inout：既可以作为输入参数，也可以作为输出参数

4.3.4 case语句

CREATE PROCEDURE test8 (in month int,out season varchar(10))
begin
	
	case 
		when month >=1 and month<=3 then
			set season='spring';
		when month >=4 and month<=6 then
			set season='summer';
		when month >=7 and month<=9 then
			set season='autumn';
		when month >=10 and month<=12 then
			set season='winter';
	end case;
end ;

call test8(9,@season);			-- 定义会话变量来接收test8存储过程返回的值

select @season;

4.3.5 while循环

CREATE PROCEDURE test10 (in count int)
begin
	declare total int default 0;
	declare i int default 1;
	
	while i<=count do
		set total=total+i;
		set i=i+1;
	end while;
	select total;
end ;

call test10(10);

4.3.6 repeat循环

CREATE PROCEDURE test11 (count int)		-- 默认是输入(in)参数
begin
	declare total int default 0;
	repeat 
		set total=total+count;
		set count=count-1;
		until count=0				-- 结束条件,注意不要打分号
	end repeat;
	select total;
end ;

call test11(10);

4.3.7 loop循环

CREATE PROCEDURE test12 (count int)		-- 默认是输入(in)参数
begin
	declare total int default 0;	
	sum:loop							-- 定义循环标识
		set total=total+count;
		set count=count-1;
		
		if count < 1 then
			leave sum;					-- 跳出循环
		end if;
	end loop sum;						-- 标识循环结束
	select total;
	
end ;

call test12(10);

4.4 存储过程和存储函数的区别

存储函数的限制：不能用临时表、不能执行查询语句、只能用表变量等，存储函数必须有返回值
而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强，可以没有返回值,且能返回结果集

五、数据库的三大范式

5.1 第一范式

第一范式强调每一列的原子性，每列的数据必须保证其原子性，即每列的数据必须细化到不可再拆分

学号	姓名	学科	班级
001	张三	java	1班
002	李四	Java	2班
003	王五	产品	3班

5.2 第二范式

在满足第一范式的条件下，每一列的数据都完全依赖于主键，不产生局部依赖，每张表都只描述一件事物，每一列都和主键相关联
也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中

借阅ID	书籍ID	书籍名称	出版社	数量	学号	学生姓名	手机号
001	1	高性能MySQL	清华大学出版社	1	zs-001	张三	110
001	2	MySQL技术内幕	北京大学出版社	2	ls-002	李四	120

根据第二范式细化：拆分成学生表、书籍表、借阅表
学生表：

学号	姓名	年龄	手机号
zs-001	张三	21	110
ls-002	李四	22	120

书籍表：

书籍id	书籍名称	出版社
1	高性能MySQL	清华大学出版社
2	MySQL技术内幕	北京大学出版社

借阅表：

借阅ID	借阅书籍ID	借阅人学号	借阅数量
001	1	zs-001	1
002	2	zs-002	2

5.3第三范式

在满足第二范式的条件下，表中的每一列不存在传递依赖，每列都直接依赖于主键

ID	姓名	年龄	所属部门	部门地点
001	张三	21	研发部	石家庄
002	李四	22	销售部	郑州
003	王五	25	研发部	济南

根据第三范式，每一列应该直接依赖于主键
我们应该拆分成一张用户表和一张部门表，通过建立外键来建立两表之间的关系
部门表：

部门id	部门名称	部门地点	部门简码	部门等级
001	研发部	石家庄	dev	1
002	行政部	郑州	admin	2
003	销售部	济南	sale	2

员工表：

ID	姓名	年龄	部门ID
001	张三	21	001
002	李四	22	002
003	王五	25	001

5.4反范式化

一般我们设计表都会按照数据库的三大范式，但是在某些情况下我们查询的数据在多张表中，例如我们需要查询员工的信息并且希望带出员工的部门名称，这个时候我们必须使用join关联表查询，如果这些数据是查询非常频繁的，那么无疑会降低数据库的读性能

5.5过分范式化带来的弊端

过分的满足第一范式设计：即保证每一列的原子性，会给表带来非常多的列；