mysql视图/存储过程/函数/事件/触发器

--语法参考&＃xff1a;https://dev.mysql.com/doc/   &＃xff08;当前用的是5.6&＃xff09;

https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-data-manipulation.html

--视图

CREATE VIEW test.v AS SELECT * FROM t;

 --存储过程

CREATE
    [DEFINER &＃61; { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type    

type:
    Any valid MySQL data type

characteristic:
    COMMENT &＃39;string&＃39;
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

--新建存储过程

delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END//
delimiter ;

--调用存储过程
CALL simpleproc(&＃64;a);    

--函数
CREATE
    [DEFINER &＃61; { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT &＃39;string&＃39;
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

--新建函数
CREATE FUNCTION hello (s CHAR(20))
    RETURNS CHAR(50) DETERMINISTIC
    RETURN CONCAT(&＃39;Hello, &＃39;,s,&＃39;!&＃39;);
--使用函数
SELECT hello(&＃39;world&＃39;);

 

--事件
CREATE
    [DEFINER &＃61; { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT &＃39;string&＃39;]
    DO event_body;

schedule:
    AT timestamp [&＃43; INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [&＃43; INTERVAL interval] ...]
    [ENDS timestamp [&＃43; 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}

用法一&＃xff1a;
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP &＃43; INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol &＃61; mycol &＃43; 1;

用法二&＃xff1a;
CREATE EVENT e_totals
    ON SCHEDULE AT &＃39;2006-02-10 23:59:00&＃39;
    DO INSERT INTO test.totals VALUES (NOW());      

用法三&＃xff1a;
delimiter |

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT &＃39;Saves total number of sessions then clears the table each day&＃39;
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

delimiter ;

用法四&＃xff1a;
CREATE EVENT e_call_myproc
    ON SCHEDULE
      AT CURRENT_TIMESTAMP &＃43; INTERVAL 1 DAY
    DO CALL myproc(5, 27);

 

--触发器
    
CREATE
    [DEFINER &＃61; { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }  #BEFORE&＃xff1a;先触发触发器&＃xff0c;再执行操作 | AFTER&＃xff1a;执行完操作再触发触发器

trigger_event: { INSERT | UPDATE | DELETE }
    
    

CREATE TABLE classification_log
(
    id BIGINT(20),
    note varchar(128),
    utime datetime default NOW()
)
--插入时&＃xff0c;触发器记录日志记录日志
CREATE TRIGGER tri_classification
AFTER INSERT ON document_classification
FOR EACH ROW
BEGIN

INSERT classification_log(id , note)
VALUES(new.id,&＃39;新插入id&＃39;);
END;
    
--修改时&＃xff0c;触发器记录日志记录日志
CREATE TRIGGER tri_classificationx3
AFTER UPDATE ON classification_log2
FOR EACH ROW
BEGIN

INSERT classification_log(id , note)
VALUES(old.id,&＃39;修改前&＃39;);

INSERT classification_log(id , note)
VALUES(new.id,&＃39;修改后&＃39;);
END;
    
--删除时&＃xff0c;触发器记录日志记录日志
CREATE TRIGGER tri_classificationx4
AFTER DELETE ON classification_log2
FOR EACH ROW
BEGIN

INSERT classification_log(id , note)
VALUES(old.id,&＃39;删除前&＃39;);

END;