MySql高级之触发器、存储过程、游标

触发器的使用：

  什么是触发器？

     是一类特殊的事务，可以监测某种数据操作--update insert delete  并触发相关操作

     比如我们在商城购买一件东西的时候，购买后，要让相应的商品库存自动减一，此时就可以使用触发器。当然，也可以使用   update去使得库存数量发生变化

  trigger枪机  触发  引起的意思

存储触发器的文件TRG

  四个要点： 

      监视地点  监视事件  触发时间  触发事件

  实例：

delimiter $ 表示最外层分隔符 因为mysql默认；为结束

  create trigger 触发器名称

  after/before

 要发生的动作 on 触发的表

  for each row

  begin

  update 要发生改动的表 xxx;

  end $

 

create trigger t1

  after

  insert on table1

  for each row

  begin

  update table2 set num = num-1  where id = 1;

  end $

触发器引发行变量

引用动作监视的数据

增

create trigger t2

  after

  insert on table1

  for each row

  begin

  update table2 set num = num-new.much  where id = new.gid;

  end $

查看已有triggers：show triggers

将存在相同监测（事件触发时间 事件触发条件 事件触发源都一样 ）的触发器器删除，否则会引起冲突

drop trigger t1 $

删

create trigger t3

  after

  delete on table1

  for each row

  begin

  update table2 set num = num-old.much  where id = old.gid;

  end $

改

create trigger t3

  after

  delete on table1

  for each row

  begin

  update table2 set num = num+old.much-new.much  where id = old.gid;

  end $

 

create trigger t3

  before

  update on table1

  for each row

  begin

  update table2 set num = num+old.much-new.much  where id = old.gid;

  end $

sql编程

完成much和num的判断

create trigger t21

  after

  insert on table1

  for each row

  begin

  declare //声明新的变量

  rnum int ;

 //判断

 select num into rnum from table2 where gid=new.gid;

 if new.much > rnum then 

            set new.much = rnum;

 end if 

 update table2 set num = num-new.much  where id = new.gid;

 end $

for each row 的作用？

触发器分为：语句级触发器 行级触发器

for each row每一行受影响，触发器都执行，叫做行级触发器

oracle中如果不写 for each row，无论update语句一次影响多少行，都只执行一次

遗憾的是，Mysql目前不支持语句级触发器，for each row不写会报错

 

存储过程

过程：封装若干条语句，调用时封装体执行。

函数是一个有返回值的过程

过程是一个没有返回值的函数

把若干条sql封装起来，起个名字就是过程，把此过程存储在数据库中就是所谓的存储过程

语法：

create procedure provedurename()

begin

  ------sql语句

end$

查看已有的procedure

show procedure status;

调用存储过程：

 call procedure名称（）$

存储过程引入变量编程：

create procedure p2()

begin

declare age int default 18;

declare height int default 180;

select concat('年龄是'，age,'身高是'，height)

end$

 

、、变量运算

、、注意的是运算的结果如何赋值

create procedure p3()

begin

declare age int default 18;

declare height int default 180;

set age:=age+20;

select concat('20年后年龄是'，age);

end$

、、if else控制语句

create procedure p4()

begin

    declare age int default 18;

    if age>=18 then

      select '已成年';

    else 

      select '未成年';

    end if；

end$

、、给存储过程传入参数

create procedure p5(width int ,height int)

begin

   select concat("你的面积是",width*height) as area;

    if width>height then

          select '你挺胖';

    else if  width<height then

          select '你挺瘦';

    else 

          select '你挺方'；

    end if；

end$

call p5(123,313)$

、、顺序、选择、循环

create procedure p6()

begin

    declare total int default 0;

    declare num int default 0;

    while num<=100 do

        set total : = total+num;

        set num : = num +1;

    end while;

    select total;

end$

、、How to get the summary of 1 to N?

in

create procedure p7(in n int )

begin

    declare total int default 0;

    declare num int default 0;

    while num<=n do

        set total : = total+num;

        set num : = num +1;

    end while;

    select total;

end$

out

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

begin

    declare num int default 0;

    set total ： = 0 ；//此处要给出参初始值，否则会是null,因为在sql中   null+3=null

    while num<=n do

        set total : = total+num;

        set num : = num +1;

    end while;

    select total;

end$

调用：call p8(131,@total)$

、、case结构的用法

create procedure p9()

begin

 declare num int default 0；

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

    case num

        when 1 then select '人在飞'；

        when 2 then select '人在游泳'；

        else select ‘不是人’；

    end case；

end$

、、repeat循环

create procedure p10()

begin 

  declare num int default 0；

  declare i int default 0；

  repeat 

     set i := i+1; 

     set total := total+1;

  util i>100 end repeat;

  select total; 

end$

 

游标

cursor

 1条sql，对应的N条资源，取出资源的接口\句柄，就是游标

create procedure p12（）

 begin

   declare row_gid int;

   declare row_num int;

   declare row_name varchar(20);

   declare getgoods cursor  for select gid,num,name  from goods;

   open getgoods;

   fetch getgoods into row_gid , row_num , row_name'

   select row_gid , row_num , row_name;

   close  getgoods;

 end$

 

 

create procedure p13（）

 begin

   declare row_gid int;

   declare row_num int;

   declare row_name varchar(20);

   declare cnt int default 0;

   declare i int default 0;

   declare getgoods cursor  for select gid,num,name  from goods;

   open getgoods;

   repeat

         set i ：=i+1;

         fetch getgoods into row_gid , row_num , row_name'

         select  row_num , row_name;

   until i>=cnt end repeat;

   close  getgoods;

 end$

游标取值越界时，有没有什么标志？利用标识来结束

在mysql中使用declare continue handler来操作一个越界标识

declare continue handler for not found statement；

create procedure p15（）

 begin

   declare row_gid int;

   declare row_num int;

   declare row_name varchar(20);

   declare you int default 1;

   declare getgoods cursor  for select gid,num,name  from goods;

   declare continue handler for not found  set you ：=0；

   open getgoods;

   repeat

         set i ：=i+1;

         fetch getgoods into row_gid , row_num , row_name'

         select  row_num , row_name;

   until you=0 end repeat;

   close  getgoods;

 end$

 

continue和exit的区别--------------

            与continue相比，exit触发后，后面的语句不再执行

            undo handler 是触发后，前面的语句撤销，但是mysql还不支持

create procedure p16（）

 begin

   declare row_gid int;

   declare row_num int;

   declare row_name varchar(20);

   declare you int default 1;

   declare getgoods cursor  for select gid,num,name  from goods;

   declare exit handler for not found  set you ：=0；

   open getgoods;

   repeat

         set i ：=i+1;

         fetch getgoods into row_gid , row_num , row_name'

         select  row_num , row_name;

   until you=0 end repeat;

   close  getgoods;

 end$

游标循环的正确读取逻辑

  

create procedure p16（）

 begin

   declare row_gid int;

   declare row_num int;

   declare row_name varchar(20);

   declare you int default 1;

   declare getgoods cursor  for select gid,num,name  from goods;

   declare exit handler for not found  set you ：=0；

   open getgoods;

//此处需要首先手动取出第一条，防止一条数据也没有

   fetch getgoods into row_gid , row_num , row_name'

   repeat

         set i ：=i+1;

         fetch getgoods into row_gid , row_num , row_name'

         select  row_num , row_name;

   until you=0 end repeat;

   close  getgoods;

 end$

 

 

create procedure p17（）

 begin

   declare row_gid int;

   declare row_num int;

   declare row_name varchar(20);

   declare you int default 1;

   declare getgoods cursor  for select gid,num,name  from goods;

   declare exit handler for not found  set you ：=0；

   open getgoods;

//此处需要首先手动取出第一条，防止一条数据也没有

   fetch getgoods into row_gid , row_num , row_name'

   while you =1 do

         fetch getgoods into row_gid , row_num , row_name'

         select  row_num , row_name;

   end while;

   close  getgoods;

 end$

 

mysql权限检查原理

用户连接mysql，并做各种查询

分为两个阶段：

    1.你有没有权限连接上来

    2.你有没有权利执行此操作

对于1：服务器如何判断用户有没有权利连接上来

   依据三个参数：

     你是谁 user

     你从哪来  host

     你的密码是多少 password

用户的这三个信息存储在mysql.user的表下