MySQL資料庫的常用命令語句記錄——預存程序語句

標籤：

CREATE EVENT 建立執行事件

CREATE

    [DEFINER = { user | CURRENT_USER }]

    EVENT

    [IF NOT EXISTS]

    event_name

    ON SCHEDULE schedule

    [ON COMPLETION [NOT] PRESERVE]--當完成事件時，伺服器是否保留時間.預設為不保留

    [ENABLE | DISABLE | 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}

exmple:

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資料庫的常用命令語句記錄——預存程序語句