当前位置：首页 > news >正文

MySQL第五次作业

news 2025/6/29 7:30:22

根据图片内容完成作业

1.建表

（1）建立两个表:goods(商品表)、orders(订单表)

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

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

（2）在商品表中导入商品记录

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

2.建立触发器

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

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

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

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

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

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

3.存储过程

（1）使用mydb7 openlab，创建提取emp_new表所有员工姓名和工资的存储过程proc01

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

mysql> delimiter ;
mysql> call proc01();

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

mysql> delimiter //
mysql> create procedure proc02(in in_name varchar(10), out out_age int)
-> begin
-> select age into out_age from emp_new where name=in_name;
-> end //

mysql> delimiter ;
mysql> call proc02('张三',@out_age);

mysql> select @out_age;

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

mysql> delimiter //
mysql> create procedure proc03(in in_dept2 int, out out_avg float)
-> begin
-> select avg(incoming) into out_avg from emp_new where in_dept2=dept2;
-> end //

mysql> delimiter ;
mysql> call proc03(101,@out_avg);

mysql> select @out_avg;