第二十一天：MYSQL视图和存储过程
一、视图VIEW
视图：虚拟表，保存有实表的查询结果，相当于别名
利用视图,可以隐藏表的真实结构,在程序中利用视图进行查询,可以避免表结构的变化,而修改程序,降低程序和数据库之间的耦合度
创建方法：
CREATE VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图定义：
SHOW CREATE VIEW view_name #只能看视图定义 SHOW CREATE TABLE view_name # 可以查看表和视图
删除视图：
DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]
二、FUNCTION 函数
1、函数：分为系统内置函数和自定义函数
系统内置函数参考：
https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html
自定义函数：user-defined function UDF，保存在mysql.proc (MySQL8.0 中已经取消此表)表中
创建UDF语法
CREATE [AGGREGATE] FUNCTION function_name(parameter_name type,[parameter_name
type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body
说明：
参数可以有多个,也可以没有参数
无论有无参数，小括号（）是必须的
必须有且只有一个返回值
查看函数列表：
SHOW FUNCTION STATUS;
查看函数定义
SHOW CREATE FUNCTION function_name
删除UDF
DROP FUNCTION function_name
调用自定义函数语法
SELECT function_name(parameter_value,...)
2、MySQL中的变量
两种变量：系统内置变量和用户自定义变量
系统变量：MySQL数据库中内置的变量，可用@@var_name引用
用户自定义变量分为以下两种
普通变量：在当前会话中有效，可用@var_name引用
局部变量：在函数或存储过程内才有效，需要用DECLARE 声明，之后直接用 var_name引用
自定义函数中定义局部变量语法
DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]
说明：局部变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义
为变量赋值语法
SET parameter_name = value[,parameter_name = value...] SELECT INTO parameter_name
三、PROCEDURE 存储过程
存储过程：多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中
创建存储过程
CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]]) routime_body proc_parameter : [IN|OUT|INOUT] parameter_name type
说明：其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表
示参数名称；type表示参数的类型
查看存储过程列表
SHOW PROCEDURE STATUS;
查看存储过程定义
SHOW CREATE PROCEDURE sp_name
调用存储过程
CALL sp_name ([ proc_parameter [,proc_parameter ...]])
存储过程修改
ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改
存储过程,方法就是删除重建
删除存储过程
DROP PROCEDURE [IF EXISTS] sp_name
流程控制
存储过程和函数中可以使用流程控制来控制语句的执行
IF：用来进行条件判断。根据是否满足条件，执行不同语句
CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断
LOOP：重复执行特定的语句，实现一个简单的循环
LEAVE：用于跳出循环控制，相当于SHELL中break
ITERATE：跳出本次循环，然后直接进入下一次循环，相当于SHELL中continue
REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句
WHILE：有条件控制的循环语句
四、TRIGGER 触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
创建触发器
CREATE [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
说明：
trigger_name：触发器的名称
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发
trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件
tbl_name：该触发器作用在表名
查看触发器
#在当前数据库对应的目录下，可以查看到新生成的相关文件：trigger_name.TRN,table_name.TRG SHOW TRIGGERS; #查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。 USE information_schema; SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert';
删除触发器
DROP TRIGGER trigger_name;
事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的
启动，它由一个特定的线程来管理的，也就是所谓的"事件调度器"。
1、相关变量和服务器选项
MySQL事件调度器event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件
是否要调用， 要创建事件，必须打开调度器
服务器系统变量和服务器选项：
event_scheduler：在MySQL8.0默认是ON，在MariaDB默认值为OFF，设置为ON才支持Event，并且
系统自动打开专用的线程
2、管理事件
create event 语句创建一个事件。每个事件由两个主要部分组成，第一部分是事件调度（event
schedule），表示事件何时启动以及按什么频率启动，第二部分是事件动作（event action ），这是事
件启动时执行的代码，事件的动作包含一条SQL语句，它可能是一个简单地insert或者update语句，也
可以使一个存储过程或者 benin...end语句块，这两种情况允许我们执行多条SQL
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
event_name ：创建的event名字，必须是唯一确定的
ON SCHEDULE：计划任务
schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种
形式 AT和EVERY
[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任
务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉
[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节
[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关
闭该事情，可以用alter修改
DO event_body: 需要执行的sql语句，可以是复合语句
提示：event事件是存放在mysql.event表中
查看Event
SHOW EVENTS [{FROM | IN} schema_name]
[LIKE 'pattern' | WHERE expr]
修改Event
ALTER
[DEFINER = { user | CURRENT_USER }]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
[DO event_body]
浙公网安备 33010602011771号