2.7VIEW视图
视图:虚拟表,保存有实表的查询结果,相当于别名
利用视图,可以隐藏表的真实结构,在程序中利用视图进行查询,可以避免表结构的变化,而修改程序,降低程序和数据库之间的耦合度
创建方法:
CREATE VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
删除视图:
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
注意:视图中的数据事实上存储于"基表"中,因此,其修改操作也会针对基表实现;其修改操作受基表限制
2.8FUNCTION函数
函数:分为系统内置函数和自定义函数
系统内置函数参考:
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,...)
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
2.9PROCEDURE 存储过程
存储过程:多表SQL的语句的集合,可以独立执行,存储过程保存在mysql.proc表中
存储过程优势
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程,提高了运行速度,同时降低网络数据传输量
存储过程与自定义函数的区别
存储过程实现的过程要复杂一些,而函数的针对性较强
存储过程可以有多个返回值,而自定义函数只有一个返回值
存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用
无参数的存储过程执行过程中可以不加(),函数必须加 ( )
创建存储过程
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:有条件控制的循环语句
2.10TRIGGER触发器
触发器的执行不是由程序调用,也不是由手工启动,而是由事件来触发、激活从而实现执行
触发器创建
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;
2.11Event事件
2.11.1 Event 事件介绍
事件(event)是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次,也可周期性的启动,它由一个特定的线程来管理的,也就是所谓的”事件调度器”。
事件和触发器类似,都是在某些事情发生的时候启动。当数据库上启动一条语句的时候,触发器就启动了,而事件是根据调度事件来启动的。由于它们彼此相似,所以事件也称为临时性触发器。
事件取代了原先只能由操作系统的计划任务来执行的工作,而且MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务(如:Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。
事件的优缺点
优点:一些对数据定时性操作不再依赖外部程序,而直接使用数据库本身提供的功能,可以实现每秒钟执行一个任务,这在一些对实时性要求较高的环境下就非常实用
缺点:定时触发,不可以直接调用
2.11.2 Event 管理
2.11.2.1 相关变量和服务器选项
MySQL事件调度器event_scheduler负责调用事件,它默认是关闭的。这个调度器不断地监视一个事件是否要调用, 要创建事件,必须打开调度器
服务器系统变量和服务器选项:
event_scheduler:默认值为OFF,设置为ON才支持Event,并且系统自动打开专用的线程
2.11.2.2 管理事件
create event 语句创建一个事件。每个事件由两个主要部分组成,第一部分是事件调度(event schedule),表示事件何时启动以及按什么频率启动,第二部分是事件动作(event action ),这是事件启动时执行的代码,事件的动作包含一条SQL语句,它可能是一个简单地insert或者update语句,也可以使一个存储过程或者 benin…end语句块,这两种情况允许我们执行多条SQL一个事件可以是活动(打开)的或停止(关闭)的,活动意味着事件调度器检查事件动作是否必须调用,停止意味着事件的声明存储在目录中,但调度器不会检查它是否应该调用。在一个事件创建之后,它立即变为活动的,一个活动的事件可以执行一次或者多次
创建Event
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]
注意:alter event语句可以修改事件的定义和属性。可以让一个事件成为停止的或者再次让它活动,也可以修改一个事件的名字或者整个调度。然而当一个使用 ON COMPLETION NOT PRESERVE 属性定义的事件最后一次执行后,事件直接就不存在了,不能修改
删除Event
DROP EVENT [IF EXISTS] event_name