第二十一天: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]