mysql 编程 for,MySQL编程

通俗描述：执行一条sql语句，这条sql语句的执行会自动去触发执行其他的sql语句

监视地点：表

监视行为：增，删，改

触发时间：after/before

触发事件：增，删，改

创建触发器

create trigger [triggerName]

after/before insert/delete/update

on [tableName]

for each row

[sql];    //触发的sql语句

例子：

(●)

create trigger t1

after insert on bill

for each row

update goods set goods_amount=goods_amount-new.goods_amount where goods_id=new.goods_id;

(●)

create trigger t2

after delete on bill

for each row

update goods set goods_amount=goods_amount+old.goods_amount where goods_id=old.goods_id;

(●)

create trigger t3

after update on bill

for each row

update goods set goods_amount=goods_amount+old.goods_amount-new.goods_amount where goods_id=old.goods_id;

删除触发器

drop trigger [triggerName];

查看所有的触发器

show triggers;

***************************************************************************

通俗描述：一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能。存储过程与触发器类似，但存储过程是主动调用，触发器是触发条件调用

创建存储过程

create procedure [procedureName]()

begin

[业务逻辑]

end;

例子：

create procedure p1()

begin

declare age int default 27;

declare height int default 170;

select concat("年龄",age,"身高",height);

end;

调用存储过程

call [procedureName]();

例子：call p1();

删除存储过程

drop procedure [procedureName];

例子：drop procedure p1;

-------------------------------------------------------------------------------------

简单计算

create procedure p2()

begin

declare age int default 18;

set age:=age+10;

select concat("10年后的年龄是", age);

end;

调用：

call p2();

if...else判断的

create procedure p3()

begin

declare age int default 27;

if age > 18

then select "成年人";

else

select "未成年";

end if;

end;

调用：

call p3();

带有参数的

create procedure p4(height int, weight int)

begin

if weight > height then

select "你比较胖";

elseif weight < height then

select "你比较高";

else

select "你身材刚刚好";

end if;

end;

调用：

call p4(14, 16);

while循环结构的

create procedure p5()

begin

declare total int default 0;

declare num int default 0;

while num < 100 do

set num := num + 1;

set total := total + num;

end while;

select total;

end;

调用：

call p5();

带有参数的循环结构

create procedure p6(n int)

begin

declare total int default 0;

declare num int default 0;

while num < n do

set num : = num + 1;

set total := total + num;

end while;

select total;

end;

调用：

call p6(10);

out类型的参数

create procedure p7(in n int, out total int)

begin

declare num int default 0;

set total := 0;

while num < n do

set num : = num + 1;

set total : = total + num;

end while;

end;

调用：

call p7(100, @sum); //声明一个变量去接受out类型的参数

select @sum;

inout类型的参数

create procedure p8(inout age int)

begin

set age := age + 20;

end;

调用：

set @current_age = 6; //声明一个变量并赋值

call p8(@current_age);

select @current_age;

switch循环的使用

create procedure p9()

begin

declare score int default 0;

set score := floor(5 * rand());

case score

when 1 then select "我是老大";

when 2 then select "我是老二";

when 3 then select "我是老三";

else select "我是无名氏";

end case;

end;

调用：

call p9();

repeat循环的使用(本质上为do...while循环)

create procedure p10()

begin

declare total int default 0;

declare num int default 0;

repeat

set num := num+1;

set total := total+num;

until num >= 100 end repeat;

select total;

end;

调用：

call p10();

***************************************************************************

通俗描述：对查询数据库所返回的记录进行遍历，以便进行相应的操作

游标查询一行

create procedure p11()

begin

declare row_gid int;

declare row_num int;

declare get_goods cursor for select goods_id, goods_amount from goods;

open get_goods;

fetch get_goods into row_gid, row_num;

select row_gid, row_num;

close get_goods;

end;

调用：

call p11();

游标循环的正确逻辑(用repeat循环)

create procedure p14()

begin

declare row_id int;

declare row_num int;

declare row_name varchar(20);

declare has int default 1;

declare get_goods cursor for select goods_id, goods_amount, goods_name from goods;

declare continue handler for not found set has:=0;

open get_goods;

fetch get_goods into row_id, row_num, row_name;  //先手动fetch一次这样的逻辑处理才合理

repeat

select row_id, row_num, row_name;

fetch get_goods into row_id, row_num, row_name;

until has = 0 end repeat;

close get_goods;

end;

调用：

call p14();

游标循环的正确逻辑(用while循环)

create procedure p15()

begin

declare row_id int;

declare row_num int;

declare row_name varchar(20);

declare has int default 1;

declare get_goods cursor for select goods_id, goods_amount, goods_name from goods;

declare continue handler for not found set has := 0;

open get_goods;

fetch get_goods into row_id, row_num, row_name;

while has = 1 do

select row_id, row_num, row_name;

fetch get_goods into row_id, row_num, row_name;

end while;

close get_goods;

end;

调用：

call p15();