觸發器和存儲過程

觸發器和存儲過程
  1.觸發器
  在進行dml操作的時候(insert,update,delete),可以對事件進行監聽和響應,這種機制在數據庫中叫做觸發器。
  觸發器與永久性表關聯，只能建在永久性表上面,不能是temporary表或view,且只有insert,update,delete三種事件。
  mysql -u root -paixocm
  show triggers;
  show triggers\G;
mysql> delimiter //

====改結束符
 
自動在內存中生成臨時的new表和old表，觸發器執行完成后自動銷毀
insert 操作進來的數據放在new表中
delete操作刪除的數據放在old表中
update操作跟新前的數據放在old表中，更新后的數據放在new表中
old表是只讀的，而new表在觸發器程序中可以重新賦值

觸發器的觸發時機：
before:在執行操作之前要運行的語句
after:在執行操作之后要運行的語句

例：創建一個觸發器，當輸入的學生成績小于0時設置為0，當大于100時設置為100

  mysql> create trigger tr_stu_bf
   -> before insert on stu
   -> for each row
   -> begin
   -> if new.score < 0 then
   -> set new.score=0;
   -> elseif new.score > 100 then
   -> set new.score=100;
   -> end if;
   -> end
   -> //

 例:當學生的score大于等于90時，將學生的id和名字寫入yxstu表中;
 mysql> create trigger tr_ins_af
   -> after insert on stu
   -> for each row
   -> begin
   -> if new.score >= 90 then
   -> insert into yxstu values(new.id,new.name);
   -> end if;
   -> end
   -> //
    練習:新建銷售表sales,包含商品id,商品名,商品類型(代號),銷售數量四個字段;新建庫存表store,包含商品id,
     商品名,庫存數量四個字段,要求在庫存表中加入4條數據,庫存量都為100;然后每銷售一件商品則相應商品的
     庫存量就減少商品的銷售量;當刪除銷售表里的記錄時則將庫存表相應的記錄也刪除
     庫存表
     1  華為手機  p001  100
     2  小米手機  p002  100
     3  中興手機  p003  100
     4  vivi手機  p004  100

after delete on sales
  for each row
  begin
  delete from store where store_number=old.sales_number
  end
  //
 
 
  練習:創建學生成績表score,包括記錄ID,學生編號(s001),學生姓名,數學、語文、英語三門課程的成績;新建學生
      情況表scond,包含包括記錄ID,學生編號(s001),學生姓名,學生表現四個字段,每插入一條成績表記錄時,
      則在學生情況表里插入相應的信息,如果學生三門課的平均成績大于等于90分,則學生表現字段為"優秀",如
      果三門課的平均成績為>=70并<90則為良好,大于等于60小于70為及格,小于60為不及格。
      mysql> create table score(id int primary key,sno varchar(6),sname varchar(20),math float(4,1),chinese float(4,1),english float(4,1));
   
 mysql> create table scond(id int primary key,sno varchar(6),sname varchar(20),perform varchar(10));
      mysql> create trigger tr_sc_ins_af
   -> after insert on score
   -> for each row
   -> begin
   -> declare ascore float(4,1);
   -> set ascore=(select (new.math+new.chinese+new.english)/3 from score limit 1);
   -> if ascore >= 90 then
   -> insert into scond values(new.id,new.sno,new.sname,'優秀');
   -> elseif ascore>=70 && ascore < 90 then
   -> insert into scond values(new.id,new.sno,new.sname,'良好');
   -> elseif ascore>=60 && ascore < 70 then
   -> insert into scond values(new.id,new.sno,new.sname,'及格');
   -> elseif ascore<60 then
   -> insert into scond values(new.id,new.sno,new.sname,'不合格');
   -> end if;
   -> end
   -> //

 練習:如果在score表中刪除學生記錄,則將scond表中相應的學生記錄也一起刪除。
 
  after delete on score
  for each row
  begin
  delete from store where id=old.id
  end
  //
 
 練習:當更新score表中數學、語文和英語成績時,檢查是否合理,如果低于0分則為0,高于100分則為100。
   
  mysql> create trigger haha
   -> before update on score
   -> for each row
   -> begin
   -> if new.chinese < 0 then
   -> set new.chinese=0;
   -> elseif new.chinese > 100 then
   -> set new.chinese=100;
   
   -> elseif new.math < 0 then
   -> set new.math=0;
   
   -> elseif new.math > 100 then
   -> set new.math=100;
   
   -> elseif new.english < 0 then
   -> set new.englisn=0;
   
   -> elseif new.english > 100 then
   -> set new.english=100;
   -> end if;
   -> end
   -> //
 
  查看觸發器信息
 mysql> show triggers\G
 mysql> select * from information_schema.triggers\G
 
 刪除觸發器
 mysql> drop trigger tg_sc_up_bf;
   
2.存儲過程procedure
mysql> show procedure status;
變量：
全局變量：以@開頭,如@var1,設置方法為set @var1 = 1000;select @var1 := 'hello,test!'；

系統變量;
系統變量使用@@引用
 
  局部變量:
 局部變量用在begin...end語句中,聲明的是局部變量
 delare var1 int;
 set var1=100;

例:存儲過程示例
 mysql> create procedure sp1()
   -> begin
   -> declare var1 int;
   -> declare var2 int default 0;  ======default==賦初值
   -> declare var3 varchar(20) charset utf8 default '湖南工業大學';
   -> set var1=10000;
   -> set var2=111;
   -> select var1,var2,var3;
   -> end
   -> //

 1、使用存儲過程傳遞參數
   
   in 傳入參數
   out 傳出參數
   inout 傳入傳出參數 

傳出參數：
   
   例:使用存儲過程統計指定表的記錄數,并且記錄數能在外面使用
   
   mysql> create procedure sp2(out num int)
   -> begin
   -> select count(*) into num from employees;
   -> end
   -> //

call sp2(@var1)======變量名可以不一樣


傳入參數：  
   例:使用存儲過程創建users表,包含id,name和sex三個字段
   
    mysql> create procedure sp3(id int,name varchar(20),sex enum('man','woman'))======in  可以省略
   -> begin
   -> create table if not exists users
   -> (
   -> id int primary key,
   -> name varchar(20),
   -> sex enum('man','woman')
   -> )engine=innodb charset=utf8;
   -> insert into users values(id,name,sex);
   -> select * from users;
   -> end
   -> //
   
mysql> call sp3(1,'張三','男');
 傳入傳出參數:
   mysql> create procedure sp3(inout va int)
   -> begin
   -> set va := va + 10;
   -> set va=va+10;
   -> select va+10 into va;
   -> end
   -> //
   
   mysql> set @var1=100;
   mysql> call sp4(@var1);
   mysql> select @var1;
   
  
     存值方法:
   
   
   select .... into var|@var
   select @var := 100
   select emp_no,first_name into var1,var2 from employees \\必須使用declare
                                                                                            進行聲明
   select emp_no,first_name into @var1,@var2 from employees
   select @var1 := emp_no,@var2 := first_name from employees
   
   2、mysql編程
  (1)loop循環(無限循環)
     label:loop
        循環體
        if 退出條件
           leave label;
        end if;
       
     end loop;
     
 例:使用存儲過程計算指定數字從1開始的和值
 mysql> create procedure sp4(in snum int)
   -> begin
   -> declare sum,i int;
   -> set sum=0,i=0;
   -> sxjy:loop
   -> if i <= snum then
   -> set sum := sum + i;
   -> set i := i + 1;
   -> else
   -> leave sxjy;
   -> end if;
   -> end loop;
   -> select sum
   -> end
   -> //
   
   
    (2)while循環
   leave    跳出循環
   iterate  跳過本次循環
   
   label:while 條件 do
     循環體
   end while
   
    mysql> create procedure sp5(in snum int)
   -> begin
   -> declare sum,i int;
   -> set sum=0,i=0;
   -> while i<= snum do
   -> set sum := sum + i;
   -> set i := i + 1;
   -> end while;
   -> select sum;
   -> end
   -> //
   
 練習:在test數據庫下新建test1表,包括
     id    整型,主鍵
     num1  整型,從1到200的隨機數
     dt    datetime類型,為當前系統日期時間
     然后使用while循環插入200條記錄
   
   
   mysql> create procedure sp6() begin declare i,rand int declare time datetime set i=0,rand=select ceil(200*RAND()+1) limit 1), time=(select NOW() linit 1); while i<=200 do insert into test1(id,num1,dt) values(i,rand,time); set i := i+1; end while; select * from test1 limit 10; end//
 (3)repeat循環
     repeat
       循環體
     until 條件
     end repeat;
 
  mysql> create procedure sp6(in snum int)
   -> begin
   -> declare sum,i int;
   -> set sum=0,i=0;
   -> repeat
   -> set sum := sum + i;
   -> set i := i + 1;
   -> until i > snum
   -> end repeat;
   -> select sum;
   -> end
   -> //
   
   
   (4)case語句
  case 操作數
   when 條件 then 執行語句;
   when 條件 then 執行語句;
   when 條件 then 執行語句;
   else 執行語句
  end case
 
  例:使用存儲過程添加學生,當學生的id除3余0時將學生插入c01班,余1時插入c02班,余2時插入c03
     班,每個班的字段為id,name,age,sex四個字段。
     mysql> create procedure sp6(in id int,in name varchar(20),in age int,in sex enum('man','woman'))
   -> begin
   -> declare num int;
   -> set num=mod(id,3);
   -> case num
   -> when 0 then insert into c01 values(id,name,age,sex);
   -> when 1 then insert into c02 values(id,name,age,sex);
   -> when 2 then insert into c03 values(id,name,age,sex);
   -> else
   -> insert into c01 values(id,name,age,sex);
   -> end case;
   -> end
   -> //
   
   
   
    select case age
   when 20 then 語句;
   when 30 then 語句;
   ....
   else
   語句;
   end case from 表名;
   
   select case
   when age >= 20 && age < 25 then 語句;
   when age >= 25 && age < 30 then 語句;
   ....
   else
   語句;
   end case from 表名;

練習:將employees數據庫中,員工的入職時間在1985年到1990年的加薪15%向上取整,入職時間
     在1990年到1995年的加薪10%向上取整,入職時間在1995年到2000年的加薪5%向上取整,
   
   
   
   3、mysql備份和恢復
  mysqldump備份工具
  備份數據庫下的表
  # mysqldump -u root -paixocm --opt test c01 c02 c03 > /employees.sql
  # mysql -u root -paixocm --database test < employees.sql
 
  備份指定數據庫
  # mysqldump -u root -paixocm --opt --database test > /employees.sql
  # mysql -u root -paixocm < employees.sql
 
  備份所有數據庫
  # mysqldump -u root -paixocm --opt --all-databases > /employees.sql
 
  鎖定和解鎖所有表
  mysql> FLUSH TABLES WITH READ LOCK;
  mysql> unlock tables;