触发器和存储过程

触发器和存储过程
  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;