準備:
1 建立日志表
DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
`id` int DEFAULT NULL,
`name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
`code` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `demo` VALUES ('2', 'zz');
INSERT INTO `demo` VALUES ('1', 'yy');
INSERT INTO `demo` VALUES ('3', 'sz');
1 視圖
視圖建立
create view v_user as select * from demo;
視圖
select * from v_user;
2 觸發器
建立觸發器
DELIMITER ||
CREATE TRIGGER demo BEFORE INSERT
ON demo FOR EACH ROW
BEGIN
INSERT INTO logs VALUES(NOW());
END
||
DELIMITER ;
測試
INSERT INTO `demo` VALUES ('1', 'a');
結果
`logs` 表中有資料
select * from `logs`
3 存儲過程
建立
DELIMITER ||
CREATE PROCEDURE delete_proc(IN p_playerno INTEGER)
BEGIN
DELETE FROM demo
WHERE id = p_playerno;
END
||
測試
call delete_proc(1);