欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-06-04 07:50:58
...

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