日期间隔表达式
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/