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 设置结束符