数据库函数存储过程触发器事件视图

向右看齐 2022-02-03 10:23 337阅读 0赞

# [MYSQL数据库重点：自定义函数、存储过程、触发器、事件、视图][MYSQL] #

一、自定义函数

mysql自定义函数就是实现程序员需要sql逻辑处理，参数是IN参数,含有RETURNS字句用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

语法：

创建：***CREATE FUNCTION**** 函数名称(参数列表)*

***　　RETURNS** 返回值类型*

函数体

修改： ***ALTER*** ***FUNCTION*** *函数名称* \[characteristic ...\]

删除：***DROP**** **FUNCTION** \[IF EXISTS\] 函数名称*

调用：***SELECT** 函数名称(参数列表)*

二、存储过程

一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高，用户通过指定存储过程的名字并给出参数来执行它。参数可以为IN, OUT, 或INOUT

语法：

创建：***CREATE PROCEDURE*** 过程名 (参数列表) \[characteristic ...\]

函数体

修改： ***ALTER PROCEDURE***  过程名 \[characteristic ...\]

删除：***DROP**** **PROCEDURE** \[IF EXISTS\] 过程名*

调用：***CALL** 过程名(参数列表)*

注意：存储过程和自定义函数的区别：  
1.存储过程，功能强大，可以执行包括修改表等一系列数据库操作，也可以创建为数据库启动时自动运行的存储过程。

自定义函数，用户定义函数不能用于执行一组修改全局数据库状态的操作。  
  
2.对于sql server: 存储过程，可以使用非确定函数。自定义函数，不允许在用户定义函数主体中内置非确定函数。

3.存储过程，可返回记录集。

自定义函数，可以返回表变量。  
  
4.存储过程，其返回值不能被直接引用。

自定义函数，其返回值可以被直接引用。  
  
5.存储过程，用 CALL 语句执行。

自定义函数，在查询语句中调用。

* *

3、触发器

与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

语法：

创建：

*CREATE TRIGGER <触发器名称>*  --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.  
*\{ BEFORE | AFTER \}*  --触发器有执行的时间设置：可以设置为事件发生前或后。  
*\{ INSERT | UPDATE | DELETE \}*  --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。  
*ON <表名称>*  --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。  
*FOR EACH **ROW*  --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。  
*<触发器SQL语句>*  --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。  
\--创建触发器（CREATE TRIGGER），需要SUPER权限。

eg:

CREATE TRIGGER testref BEFORE INSERT ON test1
      FOR EACH ROW BEGIN
        INSERT INTO test2 SET a2 = NEW.a1;
        DELETE FROM test3 WHERE a3 = NEW.a1;  
        UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
      END

删除：DROP TRIGGER 方案名称.触发器名称

4、事件

事件调度器是MySQL5.1后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似于linux系统下面的任务调度器crontab，或者类似与window下面的计划任务。值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务，而操作系统的计划任务（如：Linux下的CRON或Windows下的任务计划）只能精确到每分钟执行一次。

语法：

创建：

CREATE
    
        [DEFINER = { user | CURRENT_USER }]   --定义事件执行的时候检查权限的用户。
    
        EVENT
    
        [IF NOT EXISTS]
    
        event_name
    
        ON SCHEDULE schedule              --定义执行的时间和时间间隔。
    
        [ON COMPLETION [NOT] PRESERVE]     --定义事件是一次执行还是永久执行，默认为一次执行，即NOT PRESERVE。
    
        [ENABLE | DISABLE | DISABLE ON SLAVE]   --定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上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}

删除：

DROP EVENT [IF EXISTS] event_name

修改：

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]

查看事件是否开启，使用如下命令查看：

SHOW VARIABLES LIKE 'event\_scheduler';

SELECT @@event\_scheduler;

SHOW PROCESSLIST;

如果看到event\_scheduler为on或者PROCESSLIST中显示有event\_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event\_scheduler的信息，那么就说明事件没有开启，我们需要开启它。

开启事件：

开启mysql的事件，通过如下三种方式开启：

Ø  通过动态参数修改

SET GLOBAL event\_scheduler = ON;    更改完这个参数就立刻生效了

注意：还是要在my.ini中添加event\_scheduler=ON。因为如果没有添加的话，mysql重启事件又会回到原来的状态了。

Ø  更改配置文件然后重启

在my.ini中的\[mysqld\]部分添加如下内容，然后重启mysql。

event\_scheduler=ON

Ø  通过制定事件参数启动

mysqld ... --event\_scheduler=ON

5、视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的SQL 语句，它主要出于两种原因：安全原因， 视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”，从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT \*，而你看到的将是你在视图定义里给出的那些数据列：

语法：

创建：

CREATE \[OR REPLACE\] \[ALGORITHM = \{UNDEFINED | MERGE | TEMPTABLE\}\]  
VIEW view\_name \[(列名列表)\]  
AS 查询语句  
\[WITH \[CASCADED | LOCAL\] CHECK OPTION\]

修改：

ALTER \[ALGORITHM = \{UNDEFINED | MERGE | TEMPTABLE\}\]  
VIEW view\_name \[(column\_list)\]  
AS select\_statement  
\[WITH \[CASCADED | LOCAL\] CHECK OPTION\]

删除：

DROP VIEW \[IF EXISTS\]  
view\_name \[, view\_name\] ...  
\[RESTRICT | CASCADE\]

[MYSQL]: https://www.cnblogs.com/cac2020/p/6026511.html