MySQL数据库的常用命令语句记录——存储过程语句

CREATE EVENT myevent

    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

    DO

      UPDATE myschema.mytable SET mycol = mycol + 1;

ALTER 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]

DROP EVENT [IF EXISTS] event;

CREATE PROCEDURE/FUNCTION 创建存储过程/函数

CREATE

    [DEFINER = { user | CURRENT_USER }]

    PROCEDURE sp_name ([proc_parameter[,...]])

    [characteristic ...] routine_body

CREATE

    [DEFINER = { user | CURRENT_USER }]

    FUNCTION sp_name ([func_parameter[,...]])

    RETURNS type

    [characteristic ...] routine_body

proc_parameter:

    [ IN | OUT | INOUT ] param_name type

func_parameter:

    param_name type

type:

    Any valid MySQL data type

characteristic:

    COMMENT ‘string‘

  | LANGUAGE SQL

  | [NOT] DETERMINISTIC

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }  

exmple 

 delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)

     BEGIN

       SELECT COUNT(*) INTO param1 FROM t;

     END//

CREATE FUNCTION hello (s CHAR(20))

     RETURNS CHAR(50) DETERMINISTIC

     RETURN CONCAT(‘Hello, ‘,s,‘!‘);

PROCEDURE,FUNCTION但需要更新执行内容时，需要先DROP后CREATE

ALTER PROCEDURE

ALTER PROCEDURE proc_name [characteristic ...]

characteristic:

    COMMENT ‘string‘

  | LANGUAGE SQL

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

ALTER FUNCTION

ALTER FUNCTION func_name [characteristic ...]

characteristic:

    COMMENT ‘string‘

  | LANGUAGE SQL

  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

DROP FUNCTION/FUNCTION [IF EXISTS] function/procedure

CREATE TRIGGER 触发器

CREATE

    [DEFINER = { user | CURRENT_USER }]

    TRIGGER trigger_name

    trigger_time trigger_event

    ON tbl_name FOR EACH ROW

    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

DECLARE 定义变量

    DECLARE CONDITION

    DECLARE condition_name CONDITION FOR condition_value

condition_value:

    mysql_error_code

  | SQLSTATE [VALUE] sqlstate_value

DECLARE CURSOR 定义游标

DECLARE cursor_name CURSOR FOR select_statement

    OPEN CURSOR;

    CLOSE CURSOR;

    FETCH cursor INTO variable[,...]

DECLARE VARIABLE 

DECLARE var_name [, var_name] ... type [DEFAULT value]

SHOW CREATE EVENT  event;

SHOW CREATE FUNCTION function;

SHOW CREATE PROCEDURE procedure;

SHOW EVENTS [FROM database] [LIKE ‘pattern‘ | WHERE expression];

SHOW FUNCTION CODE function;

SHOW FUNCTION STATUS [LIKE ‘pattern‘ | WHERE expression];

SHOW PROCEDURE CODE stroe_procedure;

SHOW PROCEDURE STATUS [LIKE ‘pattern‘ | WHERE expression];

SHOW TRIGGERS  [FROM database] [LIKE ‘pattern‘ | WHERE expression];

delimiter 设置结束符

MySQL数据库的常用命令语句记录——存储过程语句

标签：