存储过程
- 1语法
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
- 2举例
DROP PROCEDURE IF EXISTS pro_add;
DELIMITER //
CREATE PROCEDURE pro_add( IN p1 INT, IN p2 INT, INOUT p3 INT)
BEGIN
SET p3=p1+p2;
END
//
CALL pro_add(1,2,@abc);
SELECT @abc;
函数 (特殊的存储过程 暂时不支持 out)
- 1语法结构:
CREATE FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body
- 2举例
DROP FUNCTION IF EXISTS fun_add; DELIMITER // CREATE FUNCTION fun_add(p1 INT,p2 INT) RETURNS INT BEGIN RETURN p1+p2; END // SET @abc=fun_add(1,2); SELECT @abc;
视图
1>语法结构
create view 名称 as sql语句
- 视图操作举例
CREATE TABLE `arcticle` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `content` varchar(200) DEFAULT NULL, `createuser` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 CREATE VIEW vi_arc AS SELECT title,content FROM arcticle SELECT * FROM arcticle UPDATE vi_arc SET content='java永不过时' WHERE title='java是否过时' DELETE FROM vi_arc WHERE title='java是否过时' INSERT INTO vi_arc VALUES('abc','ccc')
触发器
- 视图操作举例
1>语法结构
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt
2>举例:
DROP TRIGGER trg_arc DELIMITER // CREATE TRIGGER trg_arc BEFORE INSERT ON arcticle FOR EACH ROW BEGIN IF new.createuser IS NULL THEN SET new.createuser='张三'; END IF; END //
3>注意
delete和update 有 old对象
insert和update 有new对象
update 有old,new对象
after触发器 不能够修改new的值