MySQL编程提示与样例

日期间隔表达式
date_add(now(),interval expr type)

on duplicate key update no_id='xxx'

sql语句中流程函数
IF(value,t,f) 如果value是真,返回t;否则返回f
IFNULL(value1,value2) 如果value1不为空,返回value1;否则返回value2
case when [value1] then [result1]...else [default] end
case [expr] when [value1] then [result1]...else [default] end

found_row():上一条sql语句返回的条目
变量赋值
set last_month_start=date_add(current_date(),interval 1 month)

delimiter $$
create procedure p_name()
begin
   declare continue handler for sqlstate '23000' set @x=1
end;
$$
delimiter ;

handler_type现在还只支持continue和exit两种,continue表示继续执行下面的语句,exit则表示执行停止,undo现在还不支持
condition_value的值可以是通过Declare定义的condition_name,可以是sqlstate的值或者mysql-error-code的值或者sqlwarning、not found、sqlexception,这3个值是3种定义好的类别

sqlwarning是对所有以01开头的sqlstate代码速记
not found是对所有以02开头的sqlstate代码速记
sqlexception是对所有没有被sqlwarning或not found捕获的sqlstate的代码速记

mysql-error-code:sql报错后error后面紧跟的数值
sqlstate:sql报错后括号中的数值

leave:相当于shell中的break
iterate:相当于shell中的continue

上面的代码可以改为
first:  declare continue handler for 1062 set @x=1
second: declare c_name condition for sqlstate '23000'
        declare continue handler for duplicatekey set @x=1
third:  declare continue handler for sqlexception set @x=1

delimiter $$
create procedure p_name()
begin
  declare i_staff_id int;
  declare d_amount decimal(5,2);
  declare cur_payment cursor for select staff_id,amount from payment;
  declare exit handler for not found close cur_payment;
 
  set @x1=0
  set @x2=0

  open cur_payment;
  repeat
    fetch cur_payment into i_staff_id,d_amount;
      if i_staff_id=2 then
          set @x1=@x1+d_amount;
      else
         set @x2=@x2+d_amount;
      end if;
  until 0 end repeat;
close cur_payment;
end;
$$
delimiter ;

自动回滚
create procedure sp_auto_rollback_demo()
begin
declare exit handler for sqlexception rollback;
start transaction
insert into b select 1;
insert into b select 2;
commit;
end;

自动回滚的加强型
create procedure sp_auto_rollback_demo()
begin
declare exit handler for sqlexception begin rollback; select -1;end;
start transaction
insert into b select 1;
insert into b select 2;
commit;
select 1;
end;


注:mysql没有打印语句,只能在循环中加入insert语句,将游标遍历的值保存下来;

delimiter $$
create procedure actor_insert()
begin
  set @x=0
  ins:LOOP
     set @x=@x+1;
     if @x=10 then
       leave ins;
     elseif mod(@x,2)=0 then
       iterate ins;
     end if;
     insert into actor(actor_id,first_name,last_name) values(@x+200,'Test',@x);
  end loop ins;
end;
$$
delimiter ;

while循环和repeat循环的区别在于,while是满足条件才执行循环,repeat是满足条件退出;
delimiter $$
create procedure loop_demo()
begin
  set @x=1,@x1=1;
  repeat
   set @x=@x+1;
  until @x>0 end repeat;
 
  while @x1<1 do
    set @x1=@x1+1;
  end while;
end;
$$

事件调度
create event test_event_2
on schedule at current_timestamp + interval 1 hour
do insert into test.test(id1,create_time) values('test',now());

create event test_event_1 on schedule every 5 second do insert into test.test(id1,create_time) values('test',now());
create event test_event_1 on schedule every 5 minute do insert into test.test(id1,create_time) values('test',now());
CREATE  EVENT `create5dayvol` ON SCHEDULE EVERY 1 DAY STARTS '2015-01-14 09:00:00' ON COMPLETION PRESERVE ENABLE DO CALL create_5vol()

show event\G
show variables like '%sche%';
+-----------------+---------------+
| Variable_name   | Value  |
+-----------------+---------------+
| event_scheduler | OFF   |
+-----------------+---------------+
set global event_scheduler=1;

alter event e_name disable;

触发器
create trigger t_name
after(before) insert on t1
for each row
begin

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29620572/viewspace-1804013/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/29620572/viewspace-1804013/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值