存儲過程PROCEDURE
DROP PROCEDURE IF EXISTS usp_test;
DELIMITER //
CREATE PROCEDURE `usp_test`()
COMMENT ‘存儲過程注釋’
BEGIN
-- 需要執(zhí)行的SQL
SELECT * FROM tb_a;
SELECT * FROM tb_b;
END//
DELIMITER ;
函數(shù)FUNCTION
DROP FUNCTION IF EXISTS `fn_test`;
DELIMITER //
CREATE FUNCTION `fn_test`(`in_name` VARCHAR(50)) RETURNS varchar(50) CHARSET utf8
BEGIN
-- 函數(shù)體
set @a=’Name=’;
return concat(@a,in_name);
END//
DELIMITER ;
事件EVENT
DROP EVENT IF EXISTS `evt_test`;
DELIMITER //
CREATE EVENT `evt_task_member_statistics` ON SCHEDULE EVERY 1 DAY STARTS ‘2016-07-07 00:00:00’
ON COMPLETION PRESERVE ENABLE COMMENT ‘注釋’ DO BEGIN
-- 需要執(zhí)行的SQL
SELECT * FROM tb_a;
END//
DELIMITER ;
備注：MYSQL默認(rèn)是關(guān)閉事件機(jī)制的，需要自行開啟
SET GLOBAL event_scheduler = 1; — 開啟event
show variables like ‘event_%’; — 查看event開啟狀態(tài)
SQL語句性能分析
explain
select * from wp_posts a where a .ID= 5 and a .post_type= ‘post’;
創(chuàng)建臨時(shí)表
DROP TEMPORARY TABLE IF EXISTS tmp_tb;
CREATE TEMPORARY TABLE tmp_tb (id int AUTO_INCREMENT PRIMARY KEY not null,service_id int not null);
無鎖模式（Unlock）
MS SQL Server使用無鎖查詢模式只需要在表名后面加上(NOLOCK）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
-- SQL查詢語句
SELECT * FROM tb_a;
COMMIT ;
或者
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
-- SQL查詢語句
SELECT * FROM tb_a;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
觸發(fā)器（TRIGGER）
插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
-- sql
END
插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
-- sql
END
刪除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
-- sql
END
刪除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
-- sql
END
更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
-- sql
END
更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
-- sql
END
本文會持續(xù)更新…
