MySQL练习05

题目

步骤 

触发器

use mydb16_trigger;    #使用数据库  create table goods( gid char(8) primary key, name varchar(10), price decimal(8,2), num int);  create table orders( oid int primary key auto_increment, gid char(10) not null, name varchar(10), price decimal(8,2), onum int, otime date);

添加数据 

insert into goods values ('A0001','橡皮',2.5,100), ('B0001','小楷本',2.8,210), ('C0001','铅笔',1.2,120), ('D0001','计算器',28,20);

输入触发器 

create trigger insert_after_orders_trigger after insert on orders for each row update goods set num=num-new.onum where gid=new.gid;

删除触发器

create trigger delete_after_orders_trigger after delete on orders for each row update goods set num=num+old.onum where gid=old.gid;

更新触发器 

create trigger update_after_orders_trigger after update on orders for each row update goods set num=num+(old.onum-new.onum) where gid=new.gid;

  存储过程

存储过程s1 

delimiter //    #切换命令分隔符 create procedure s1() begin           select name '姓名',incoming '工资' from emp_new; end // delimiter ;

存储过程s2 

delimiter //    #更改命令结束符 create procedure s2(in in_name varchar(11), out out_age int) begin          select age into out_age from emp_new where name=in_name; end // delimiter ;

存储过程s3 

delimiter // create procedure s3(in in_dept2 int, out avg_sal double(10,2)) begin          select avg(incoming) into avg_sal from emp_new where dept2=in_dept2; end // delimiter ;