MYSQL第五次

目录

前提：创建数据库和表并使用

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


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

使用desc查看表结构

代码实现

一、触发器

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

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

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

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

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

mysql> create trigger update_goods_after_update_order 
    -> after update on orders 
    -> for each row
    -> begin
    ->     update goods set num = num + old.onum - new.onum where gid = new.gid;
    -> end; 
    -> //

mysql> delimiter ;

注意： delimiter ;必须要有

测试

插入一条新的订单记录：

insert into orders (gid, name, price, onum, otime) values ('a0001', '橡皮', 2.5, 5, curdate()); 

删除一条订单记录：

insert into orders (gid, name, price, onum, otime) values ('a0001', '橡皮', 2.5, 8, curdate()); 

delete from orders where oid = 2; 

更新一条订单记录：

update orders set onum = 9 where oid = 1;

二、存储过程

库和表的创建

create database mydb7;
use mydb7;

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

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

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

mysql> create procedure s2(in p_name varchar(50), out p_age int)
    -> begin
    ->     select age into p_age from emp_new where name = p_name;
    -> end 
    -> //

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

mysql> create procedure s3(in p_dept int, out p_avg_salary decimal(10, 2))
    -> begin
    ->     select avg(incoming) into p_avg_salary from emp_new where dept2 = p_dept;
    -> end 
    -> //
mysql> delimiter ;

注意： delimiter ;必须要有

测试

s1

call s1();

s2

mysql> call s2('张三',@age);

mysql> select @age;

s3

mysql> call s3 (102,@avg);

mysql> select @avg;

到此为止，感谢您的阅览