内容来源: http://www.cnblogs.com/cac2020/p/6026511.html
表:里面存储的都是真实的数据 视图:就是一个表或者多个表的一个映射,一般只做查询使用。比如你想要的数据存在两个表里,但你查询时不想每次都写关联,那么你创建一个视图,以后只查询这个视图就可以(查询时视图与查询表语法一样)。 触发器:做数据库操作的时候,还希望相关的数据同步操作就用触发器,比如想要向A表插入数据的时候,同时向B表插入,这样写过触发器每次向A表插入数据之后就会自动向B表插入。 存储过程:这个一般是沉淀数据使用的,当你需要查询的数据不能用一个sql语句查出或者sql语句查询速度特别慢,想要提高效率,就会用到存储过程,先把需要的数据沉淀到结果表里然后直接查询结果表就能提高效率。
一、自定义函数
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]