MYSQL进阶,函数,视图,存储过程,触发器的应用

首先理解delimiter,用于修改分隔符,因为分号“;”在函数里会用到,所以需要进行改动,不可作为结束符号

平时:

mysql> select * from t1;

更改分隔符后:

mysql> delimiter //
mysql> select * from t1//

函数:

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
    -> //

视图:

这里用到的fngetage是上文自定义的函数
mysql> create view viewuserinfo
    -> as select * ,fngetage(userbirthday) as userage from userinfo;
mysql> select * from viewuserinfo;

存储过程:

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;

触发器:

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 ;

参考:http://blog.csdn.net/mr__fang/article/details/6908676

若您觉得我的博文对您有帮助,欢迎点击下方按钮对我打赏
打赏