mysql第五次作业

一、触发器

1、建表

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);

create table goods(gid char(8) primary key, name varchar(10), price decimal(8,2)
, num int);

 insert into goods values('A0001','橡皮', 2.5, 100);

 insert into goods values
    -> ('B0001', '小楷本', 2.8, 210),
    -> ('C0001', '铅笔', 1.2, 120),
    -> ('D0001', '计算器', 28, 20);

2、建立触发器，订单表中增加订单数量后，商品表商品数量同步减少对应的商品订单出数量,并测试

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

测试：

insert into orders (gid,name,price,onum,otime) value('C0001','铅笔', 1.2, 80, now());

建立触发器，实现功能:客户取消订单，恢复商品表对应商品的数量

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

测试：

delete from orders where gid='C0001';

建立触发器，实现功能:客户修改订单，商品表对应商品数量同步更新

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

测试：

insert into orders value(1001,'A0001','橡皮',2.5,20, now());

二、存储过程

创建提取emp_new表所有员工姓名和工资的存储过程s1

 delimiter $$
 mysql> create procedure s1()
    -> begin
    ->    select name,incoming from emp_new;
    -> end $$

创建存储过程s2，实现输入员工姓名后返回员工的年龄

delimiter $$

mysql>  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 $$

创建一个存储过程s3，有2个参数，传入部门号，返回该部门的平均工资

 delimiter $$
mysql> create procedure s3(in in_dept int, out avg_sal float)
    -> begin
    ->  select avg(incoming) into avg_sal from emp_new where dept2=in_dept;
    -> end $$