mysql 存储过程 视图 自定义函数_MySQL触发器、存储过程、自定义函数、视图简单示例...

mysql> use test;mysql> create table userinfo(userid int,username varchar(10),userbirthday date);mysql> create table userinfolog(logtime datetime,loginfo varchar(100));mysql> describe userinfo;1.触发器mysql> delimiter |mysql> create trigger beforeinsertuserinfo

-> before insert on userinfo

-> for each row begin

->

insert into userinfolog values(now(),CONCAT(new.userid,new.username));

-> end;

-> |mysql> delimiter ;mysql> show triggers;2.存储过程mysql> delimiter //mysql> create procedure spinsertuserinfo(

-> puserid int,pusername varchar(10)

-> ,puserbirthday date

-> )

-> begin

->

insert into userinfo values(puserid,pusername,puserbirthday);

-> end;

-> //mysql> show procedure status like 'spinsertuserinfo';mysql> call spinsertuserinfo(1,'zhangsan',current_date);mysql> select * from userinfo;3.自定义函数mysql> update userinfo

-> set userbirthday='2000.01.01'

-> where userid='1';mysql> drop function if exists fngetage;mysql> delimiter //mysql> create function fngetage(pbirthday date)

-> returns integer

-> begin

->

return year(now()) - year(pbirthday);

-> end

-> //4.视图mysql> create view viewuserinfo

-> as select * ,fngetage(userbirthday) as userage from userinfo;mysql> select * from viewuserinfo;清除日志记录mysql> truncate table userinfolog;mysql> delete from userinfolog; 本文转摘自『蓝派网』http://www.lan27.com/Article/200807/7557.htm