Mysql中的视图, 存储过程, 存储函数,触发器

视图

介绍

视图是一种虚构的表, 视图中的数据实际并不存在于视图之中, 而是存在于视图创建所基于的表之中,
而我们进行增改时实际所操作的也是基表中的数据(注意 : 视图中实际存储的是查询语句, 我们并不能执行删除操作! )
	而视图实际所存储的其实是一条sql的逻辑, 不保存sql的查询结果, 当我们调用视图时, 视图就执行此sql, 从而得到视图的结果
	视图是可以有继承关系的, b视图可以是基于a视图去创建的, 主要体现在创建视图所引用的select语句中所from的并不是表格,
而是视图 	视图是可以看成是一个数据表进行查询或更新操作的, 但假如视图创建时加入了检查选项(check with option),
查询或更新操作 需要对视图创建时所引用的sql语句中的where条件进行检查, 满足条件才可以进行操作

特点

1, 简单, 可以将一段复杂的sql查询简化成一个视图, 这样在调用时可以直接针对视图进行操作, 更为简化用户的操作, 也能简化用户对数据的了解
2, 安全, 数据库权限授权最多到表, 通过视图操作, 我们可以指定用户可以查看的行和列
3. 数据独立, 视图的直观体现是独立在基表之外的查询结果, 可以有效屏蔽基表中除视图所查询的数据以外其余数据或列的变化

--语法
	--创建/更新语法
	create [or replace] view 视图名称[列名列表] as select语句 [with [cascaded | local] check option]
	--解析
		--or replace 重构视图 替换(更新)原来的视图
		--with [cascaded | local] check option 默认cascaded, 插入或更新数据时检查创建视图时的条件
			--cascaded 向上获取并检查继承关系中所有的基础视图中的创建条件, 无论父级表格中是否指定检查选项(with check option), 都会进行检查
			--local  向上获取并检查继承关系中所有的基础视图中的创建条件, 根据父级表格中是否指定检查选项(with check option), 而去决定是否检查父级视图的创建条件
	--删除语法
		drop view 视图名称
	--查询视图创建时的定义
	show create view 视图名称

存储过程

介绍

存储过程是事先经过编译的一段sql语句, 实际上则是把一段sql代码封装, 在需要的时候进行调用, 和开发中的函数类似
好处是可以简化开发人员的业务操作, 通过视图的调用简略一部分sql查询, 并且由于是数据库层面的过程操作, 所以可以减少与数据库的链接, 提高数据处理的性能 

特点

1. 封装, 复用, 把一段sql代码封装起来, 在需要的时候进行调用
2. 可以接收参数, 也可以返回参数
3. 减少与数据库的交互, 减少链接次数, 提高数据处理的性能

--创建
	create procedure 存储过程名称([参数列表])
	begin
		--sql语句
	end;
--调用
	call 视图名称([参数])
--查看
	--查看指定数据库的存储过程(实际上是查询routines表中的数据, 里面存储的是各数据库中的存储过程)
	select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 数据库名称
	--查看存储过程的定义
	show create procedure 存储过程名称
--删除
	drop procedure [if exists] 存储过程名称
--注意:
	--在命令行中，执行创建存储过程的SQL时，需要通过关键字 delimiter 指定SQL语句的结束符。

存储过程的参数

参数的类型，主要分为以下三种：IN、OUT、INOUT
in 该类参数作为输入，也就是需要调用时传入值 	默认	
out 该类参数作为输出，也就是该参数可以作为返回值 	
inout 既可以作为输入参数，也可以作为输出参数

--用法
	create procedure 存储过程名称([in/out/inout 参数名 类型])
	begin
		--sql语句
	end;
--如
	create procedure p4(in score int,out result varchar(10))
	begin 
			if score < 60 then
					set result := '不及格';
			elseif score < 85 then
					set result := '及格';
			else
					set result := '优秀';
			end if;
	end;
	
	-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
	call p4(76,@result);
	-- 查询出局部变量的值
	select @result;

存储函数

存储函数实际上则是有返回值的存储过程, 与存储过程的区别在于
1, 存储函数只能存入in类型的参数, 不支持out 或者 outin
2, 存储函数可以有return, 用于返回值
所以, 在业务中, 我们一般使用业务过程, 能用函数解决的, 存储过程一样能解决, 并且对传入的参数没有类型的限制

--语法
	create function 存储函数名称((in)[参数 类型])
	return type [characteristic ...]
	begin
		--sql语句
		return ...;
	end;
--characteristic
  --DETERMINISTIC：相同的输入参数总是产生相同的结果
  --NO SQL ：不包含 SQL 语句。
  --READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。
--案例
	create function fun1(n int)
	--指定返回值类型为int, characteristic类型为DETERMINISTIC, 相同输入参数得到相同结果
	returns int deterministic
	begin
		declare total int default 0;
		while n>0 do
			set total := total + n;
			set n := n - 1;
		end while;
		return total;
	end;
	--查询值
	select fun1(50);

注意: 在mysql8.0版本中binlog默认是开启的，一旦开启了，mysql就要求在定义存储过程时，需要指定characteristic特性，否则就会报如下错误：

触发器

触发器是与表有关的数据库对象, 指在增删改操作后(after)或前(before)执行的sql语句集合, 触发器可以起到确保数据的完整性并且减少业务代码的作用, 也提高了性能效率

使用别名old或者new, 可以得到旧数据和新数据, 分别对应在insert, update, 以及delete中的意义是
1, INSERT 型触发器 	NEW 表示将要或者已经新增的数据
2, UPDATE 型触发器 	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
3, DELETE 型触发器 	OLD 表示将要或者已经删除的数据

--创建
	create trigger 触发器名称
	before/after insert/update/delete
	on 表名称 for each row -- 行级触发器
	begin
		--sql语句
	end;
--查看所有的触发器
	show triggers;
--删除
	drop trigger [数据库名称.]触发器名称; -- 没有指定数据库名称则默认为当前数据库
--案例
	--修改数据的触发器
		create trigger tb_user_update_trigger
				--更新后执行的针对user表中的行级触发器
				after update on tb_user for each row
		begin
				--把数据插入到日志表中
				insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null,'update',now(),new.id,concat('更新之前的数据内容为:id=',old.id,',name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession, '| 更新之后的数据内容为:id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email,',profession=',new.profession));
		end
--注意:mysql中仅支持行级触发器. 所以当你批量执行语句时会多次触发触发器