mysql编程语法_mysql编程语法

mysql编程语法

触发器

功能：当article被插入一条记录a后，自动往operationlog表时记录a的插入行为，内容有谁插入的，插入的文章的标题以及时间。

create trigger insert_article_log AFTER insert on articlefor each row

BEGIN

DECLARE des varchar(50);

DECLARE mytitle varchar(100);

DECLARE nname varchar(20);

set mytitle=new.title;

select NICKNAME into nname from user where id=new.AUTHOR;

set des=CONCAT(nname,"发表了一稿文章,文章标题为--",mytitle);

insert into operationlog (AUTHOR,DESCRIPTION,createTime)values (new.AUTHOR,des,NOW());

end;

函数

功能：验证用户，参数传来帐号和密码，用Sql函数认证

create function authenUser(zh varchar(20),mm varchar(64))returns INT

BEGIN

DECLARE acount VARCHAR(20);

declare psw varchar(64);

select PASSWORD into psw from user where ACOUNTNAME=zh;

if ISNULL(psw)=1 THEN

return 0;#表示帐号不存在

ELSEIF psw=mm THEN

return 1;#表示帐号认证成功

else

return 2;#表示密码错误

end if;

end;

调用：select authenUser('admin','e10adc3949ba59abbe56e057f20f883e');

存储过程

功能：删除一个用户时，把其它表与该用户相关的记录都删除

create PROCEDURE delUser(uid int)

BEGIN

DELETE from article where author=uid;

delete from uploadpicture where author=uid;

delete from operationlog where author=uid;

delete from user where id=uid;

end;

调用：call authenUser(3);

视图：

功能：从两个表里选一些列出来

create view articleList as selectart.title,art.createTime,usr.nickName,art.pageview from article art join userusr on usr.id=art.AUTHOR ;

调用：select * from articlelist limit 0,10;