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/