综合实验-水果商店进阶二

一、实验目的与要求

综合运用SQL语言相关知识如变量、游标、函数、触发器等解决实际问题。

二、实验内容

设计并完成以下实验，要求附上源码（非截图），测试效果截图
1.在客户表customers中添加“VIP”字段，默认值0，根据已购买的累计金额自动更新等级，如累计100_{199，一星VIP折扣9.9，200}499二星VIP折扣9.7，500_{999三星VIP折扣9.5，1000}1999，四星VIP折扣9，后8.5折。
①　根据已购买情况自动修改客户表中所有客户的VIP信息
源码：
a.在客户表中新增VIP字段

 ALTER TABLE customers ADD VIP INT DEFAULT(0);

b.通过游标实现更新customers表格的VIP等级信息以及orders表格中的折扣信息

CREATE DEFINER=`root`@`localhost` PROCEDURE `c_vip`()
BEGIN
    DECLARE vip_price DECIMAL(6,2);
    DECLARE vip_id INT(10);
    DECLARE done TINYINT DEFAULT 0;
    DECLARE c_vip CURSOR FOR SELECT c_id,sum(original_price) FROM orders GROUP BY c_id ;
    DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
OPEN c_vip;
    WHILE(done=0)DO
    FETCH c_vip INTO vip_id,vip_price;
UPDATE customers SET VIP=CASE
        WHEN vip_price BETWEEN 0 AND 100 
            THEN 0
        WHEN vip_price BETWEEN 100 AND 199 
            THEN 1
        WHEN vip_price BETWEEN 200 AND 499 
            THEN 2
        WHEN vip_price BETWEEN 500 AND 999 
            THEN 3
        WHEN vip_price BETWEEN 1000 AND 1999 
            THEN 4
        WHEN vip_price > 1999 
            THEN 5
        END
        WHERE c_id=vip_id;
        UPDATE orders SET discount=CASE 
            WHEN vip_price BETWEEN 0 AND 100 
            THEN 1
        WHEN vip_price BETWEEN 100 AND 199 
            THEN 0.99
        WHEN vip_price BETWEEN 200 AND 499 
            THEN 0.97
        WHEN vip_price BETWEEN 500 AND 999 
            THEN 0.95
        WHEN vip_price BETWEEN 1000 AND 1999 
            THEN 0.90
        WHEN vip_price > 1999 
            THEN 0.85
        END
        WHERE c_id=vip_id;
        END WHILE;
CLOSE c_vip;
END

c.测试代码

 CALL c_vip();

运行测试结果截图:
查看customers表格

②　在订单表orders中插入新订单时自动计算并插入当前客户的折扣信息（不修改原订单的折扣） 。
源码：
a.创建触发器，当插入新订单时自动计算折扣信息

CREATE TRIGGER discount_vip BEFORE INSERT ON orders FOR EACH ROW 
    BEGIN
        DECLARE re_vip INT(10);
        SET re_vip=(SELECT VIP FROM customers WHERE c_id=new.c_id);
        SET new.discount=(CASE 
            WHEN re_vip=0 THEN 1
            WHEN re_vip=0.99 THEN 0.99
            WHEN re_vip=0.97 THEN 0.97
            WHEN re_vip=0.95 THEN 0.95
            WHEN re_vip=0.90 THEN 0.90
            WHEN re_vip=0.85 THEN 0.85
     END);
END;

b.测试代码

 INSERT INTO orderitems(o_num,o_item,f_id,quantity,item_price)VALUES(50010,5,'b1',100,10.20);

运行测试结果截图:

2.查询指定客户的“总优惠金额”，即累计每次购买时为客户优惠金额。
源码：

CREATE DEFINER=`root`@`localhost` FUNCTION `cheap`(s int) RETURNS decimal(10,2)
    READS SQL DATA
BEGIN
return (
    SELECT SUM(original_price-pay) "总优惠金额" FROM orders where c_id=s
);
    
END

测试代码：

SELECT cheap(10001);

运行测试结果截图:客户10001的总优惠金额

3.数据库设计
本数据库可以实现对fruitshop数据库进行基本的增删改查操作。例如，可以在基本表中插入数据、更新数据、删除数据、查找数据等实现对商品信息、订单信息、客户信息的管理。
可以保证数据库的安全性。通过视图以及对角色和用户的授权实现，确保只有授权的用户才能执行特定的操作，保证数据安全。例如，不同的客户和供货商只能查询到各自的信息。
可以实现对数据库的备份和恢复。备份数据库可以防止数据丢失和损坏，保护数据库信息安全。
后台管理员可以通过数据库后台管理系统管理商品、客户等信息，进行进一步的数据分析。
4.使用PowerDesigner软件将备份后的数据库fruitshop反向生成概念数据模型（CDM）：conceptual data model和物理数据模型(PDM):pyshical data model
A.使用Navicat Premiun生成逻辑结构模型如下图所示：
B.使用PowerDesigner生成逻辑数据模型LDM如下图所示：

C.使用PowerDesigner生成概念数据模型CDM如下图所示：

D.使用PowerDesigner生成物理数据模型PDM（通过LDM）如下图所示：

5.针对已给定的数据库及应用需求，提出优化或改进措施
购物车功能：客户将需要购买的商品添加到购物车中，并结算总价格
支付功能：客户支付订单前，订单状态为“未支付”；客户支付订单后，订单状态改为“已支付”。
索引优化：为经常查询的字段创建索引提高查询速度。

三、实验小结

1．实验中遇到的问题及解决过程

问题一及解决过程：创建触发器时，调用触发器的条件应该是BEFOR insert ，而不是AFTER insert。

问题二及解决过程：在orders中插入新订单时不能自动计算当前客户折扣信息，因为在创建新订单时，直接在orders表格中添加，orderitems表格中没有具体的订单项，本实验中通过orderitems具体的订单项同步到orders表格中，因此，需要在orderitems表格中添加订单后才能有具体的折扣信息和价格。

2．实验中产生的错误及原因分析
错误及原因分析：创建触发器时出现语法错误