MYSQL 第五次作业

任务要求：

一， 触发器：

mysql> create  database mydb16_trigger;
Query OK, 1 row affected (0.01 sec)

mysql> use mydb16_trigger;
Database changed

mysql>  create table goods(
    -> gid char(8) primary key,
    -> name varchar(10),
    -> price decimal(8,2),
    -> num int
    -> );
Query OK, 0 rows affected (0.03 sec)

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
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into goods values
    -> ('A0001', '橡皮', 2.5, 100),
    -> ('B0001', '小楷本', 2.8, 210),
    -> ('C0001', '铅笔', 1.2, 120),
    -> ('D0001', '计算器', 28, 20);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>  create trigger orders_after_insert_trigger
    -> after insert on orders for each row
    -> update goods set num=num-new.onum
    -> where gid=new.gid;
Query OK, 0 rows affected (0.01 sec)

mysql>  insert into orders(gid, name, price, onum, otime) value('C0001', '铅笔', 1.2, 80, now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> create trigger orders_after_delete_trigger
    -> after delete on orders for each row
    -> update goods set num=num+old.onum
    -> where gid=old.gid;
Query OK, 0 rows affected (0.01 sec)

mysql> delete from orders where gid='C0001';
Query OK, 1 row affected (0.00 sec)

mysql> select * from orders;
Empty set (0.00 sec)

mysql>  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;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into orders(gid, name, price, onum, otime) value('A0001', '橡皮', 2.5, 20, now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> update orders set onum=35 where gid='A0001';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

二， 存储过程

mysql>  use mydb7_openlab;
Database changed

mysql> delimiter $$
mysql> create procedure s1()
    -> begin
    ->   select name,incoming from emp_new;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> 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 //
Query OK, 0 rows affected (0.01 sec)

mysql>  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 $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call s3(101, @avg_sal)
    -> ;
Query OK, 1 row affected (0.00 sec)