实践第7章实践.pptxVIP

实践7 视图、索引和事务 ;实践目标;使用视图简化线路信息查询和统计。 使用索引提高线路信息查询效率。;需求说明（1）解决方案： 线路表Line提供了线路基本信息，订单线路表OL_Detail提供了线路预订情况。使用这两张表创建一个视图，获取所有线路的预订次数。 CREATE VIEW v_Nums_Line AS SELECT L.lineName , COUNT(OLD.lineID) nums FROM Line L, OL_Detail OLD WHERE L.lineID=OLD.lineID GROUP BY L.lineName ;需求说明（2）解决方案： 为获取每个客户预订线路的详细信息，需联接客户表Customer、订单客户表OC_Detail、订单线路表OL_Detail和线路表Line。使用这四张表创建一个视图，即可查询所有客户的预订信息。 CREATE VIEW v_Customer_OrderLine_Detail AS SELECT C.name, L.lineName, L.days, L.price FROM Customer C, OC_Detail OCD, OL_Detail OLD ,Line L WHERE C.customerID=OCD.travelCustomerID AND , OCD.ordersID=OLD.ordersID AND OLD.lineID=L.lineID ;需求说明（3）解决方案： 通常在主外键、频繁查询的列、排序列和聚合函数列的字段上建立索引。依据需求，可在线路表的线路名和价格上建立索引。 CREATE INDEX idx_Line ON Line(lineName, price) ;（1）创建一个视图，用于获取每一种线路类型的预订次数。 （2）创建一个视图，用于获取所有预订“国内短线游”客户的基本信息。 （3）系统需要经常获取每个客户预订线路的数量，如何使用索引技术提升查询效率? ;任务二：删除游线路信息;需求说明解决方案： 线路类型表LineType与线路表Line是一对主从表，线路类型表是主表，线路表是从表，两者之间存在外键约束。为删除“出境游”线路类型信息，只能先将线路表中类型为“出境游”的类型编号赋值为NULL，然后再删除线路类型表的“出境游”记录。这两个操作是一个整体，或者全部成功，或者全部撤销。可使用数据库事务技术实现该业务。;需求说明（2）解决方案： 定义一个变量，用于存储“凤凰古城”的线路编号，再通过联接客户表、订单客户表和订单线路表，使用子查询技术获取预订线路“凤凰古城”的客户姓名。 DECLARE @lineName nvarchar(20)='凤凰古城' DECLARE @lineID int --定义线路编号 SELECT @lineID=lineID FROM Line WHERE lineName=@lineName SELECT name 客户姓名 FROM Customer WHERE customerID IN( SELECT travelCustomerID FROM OC_Detail WHERE ordersID IN( SELECT ordersID FROM OL_Detail WHERE lineID=@lineID)) ;需求说明（3）解决方案： 联接订单线路表、线路表和订单客户表，根据订单编号分组统计每个订单的订购人次和订单金额。 将订购人次和订单金额，分别赋值给订单表中相应记录的“订购人次”（man_times）和“订单金额”（amount）。 UPDATE Orders SET man_times=A.订购人次, amount=A.订单金 FROM (SELECT OLD.ordersID 订单号, COUNT(*) 订购人次, SUM(price) 订单金额 FROM OL_Detail OLD, Line L ,OC_Detail OCD WHERE OLD.lineID=L.lineID AND OLD.ordersID=OCD.ordersID GROUP BY OLD.ordersID) A WHERE ordersID=A.订单号 ;（1）编写T-SQL，计算每个订单的折扣系数。 （2）计算每个订单的实际订单金额。 ;练习部分需求说明（1）解决方案： 根据每个订单中订购人次的不同，决定每个订单的折扣系数。规则是：一人次不享受折扣，两人次享受九八折，三人次享受九六折，四人次以上（含）享受九四折。使用T-SQL的CASE语句即可实现此业务。 ;