数据库-实验八 综合实验

---

一、实验目的与要求：

利用本学期所学知识，综合运用

二、实验内容：

1.在订单详情表orderitems插入新订单时自动获得水果数量，如果水果数量小于插入的新订单的水果数量，则插入失败。

源码:

CREATE TRIGGER or_insert before INSERT on orderitems FOR EACH ROW
BEGIN 
   DECLARE qquantity int;
   SELECT quantity INTO qquantity FROM fruits
   where fruits.f_id=new.f_id;
   IF qquantity<new.quantity THEN
    signal sqlstate 'HY000' set message_text = '水果数量不足';
   end IF;
END

测试截图:

2.统计每个月份销量最高的三个水果，方便下次进货的时候多进货。

源码:

SELECT*
FROM
(
select f_id,sum(quantity) 
from orderitems
group by f_id
ORDER BY sum(quantity) DESC)s LIMIT 3

测试效果截图:

3.随着用户消费增多，修改VIP等级

源码:

CREATE TRIGGER up_VIP AFTER UPDATE on orders FOR EACH ROW
BEGIN 
     DECLARE s_pay DECIMAL(10,2);
     SELECT SUM(original_price) INTO s_pay FROM orders
     WHERE orders.c_id=new.c_id;
     UPDATE customers SET VIP=
     CASE WHEN s_pay BETWEEN 0 AND 100 THEN 0
          WHEN s_pay BETWEEN 100 AND 199 THEN 1
          WHEN s_pay BETWEEN 200 AND 499 THEN 2
          WHEN s_pay BETWEEN 500 AND 999 THEN 3
          WHEN s_pay BETWEEN 1000 AND 1999 THEN 4
          WHEN s_pay>1999 THEN 5
         END
     WHERE customers.c_id=new.c_id;
END

测试结果:

三 、实验小结

1．实验中遇到的问题及解决过程
一开始第一题的插入失败这个知识点之前没用过，就不知道要怎么取用。
后来上网查了查发现可以手动抛出异常。后来采用了发现这个方法可以。
在修改vip等级的时候和同学交流，有出现这样一个疑惑，就是在你原来的订单然后你vip升级了，你下第二单订单的时候可以享受优惠，但是你原来的订单取消了，那你的第二单优惠是否也应该取消。我觉得这个问题我就参考了淘宝，淘宝有些津贴活动，比如300-30，尽管你整单购买才能享受这个优惠，但是有人通过在这300元的单子中进行拆单，也可以以少量的价钱获得到这个津贴。所以我这边也只写了在orders的更新操作后进行VIP等级的修改。删除这个就没有写。
2.实验中产生的错误及原因分析
一开始第二题的测试查询，我出现了一个这样的错误

这个错误是由于

即 sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，不然会抛出上面的错误。
所以我就把原来的quantity改成聚合函数的形式即sum(quantity)就可以解决这个问题了。
3．实验体会和收获。
在之前的实验中都是配合着老师的问题来进行实验的，这次实验是自己去发现问题，解决问题，有点不一样。
或许在实际的生活应用中随着业务需求的增加，我们的功能也需要不断地完善。