《实践第7章实践》由会员分享，可在线阅读，更多相关《实践第7章实践（14页珍藏版）》请在金锄头文库上搜索。
1、实践7 视图、索引和事务,实践目标,使用视图简化查询 使用索引提高查询效率 使用事务保证数据一致性,使用视图简化线路信息查询和统计。 使用索引提高线路信息查询效率。,任务一：优化旅游线路信息查询和统计,（1）创建一个视图，用于获取所有线路的预订次数。 （2）创建一个视图，用于获取所有顾客的线路预订信息。 （3）客户在预订线路时，经常需要根据线路名进行查询，并按价格排序显 示。如何使用索引技术提高检索。,需求说明（1）解决方案： 线路表Line提供了线路基本信息，订单线路表OL_Detail提供了线路预订情况。使用这两张表创建一个视图，获取所有线路的预订次数。 CREATE VIEW v_Num
2、s_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 SE
3、LECT 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
4、ON Line(lineName, price),任务一：优化旅游线路信息查询和统计,（1）创建一个视图，用于获取每一种线路类型的预订次数。 （2）创建一个视图，用于获取所有预订“国内短线游”客户的基本信息。 （3）系统需要经常获取每个客户预订线路的数量，如何使用索引技术提升查询效率?,任务一：优化旅游线路信息查询和统计,任务二：删除游线路信息,删除“出境游”线路类型信息。,在不违背外键约束的前提下删除“出境游”线路类型信息。,需求说明解决方案： 线路类型表LineType与线路表Line是一对主从表，线路类型表是主表，线路表是从表，两者之间存在外键约束。为删除“出境游”线路类型信息，只能先将
5、线路表中类型为“出境游”的类型编号赋值为NULL，然后再删除线路类型表的“出境游”记录。这两个操作是一个整体，或者全部成功，或者全部撤销。可使用数据库事务技术实现该业务。,任务二：删除游线路信息,DECLARE lineTypeID int -线路类型编号变量 SELECT lineTypeID=lineTypeID FROM LineType WHERE typeName=出境游 BEGIN TRANSACTION -将“出境游”类型的线路编号置为NULL UPDATE Line SET lineTypeID=NULL WHERE lineTypeID=lineTypeID IF ERROR
6、=0 BEGIN PRINT 线路表修改成功 - 删除“出境游”类型记录 DELETE FROM LineType WHERE lineTypeID=lineTypeID IF ERROR=0 BEGIN PRINT 线路类型表删除成功 COMMIT TRANSACTION - 提交事务 END ELSE BEGIN PRINT 线路类型表删除失败 ROLLBACK TRANSACTION 回滚事务 END END ELSE BEGIN PRINT 线路表修改失败 ROLLBACK TRANSACTION - 回滚事务 END,需求说明（2）解决方案： 定义一个变量，用于存储“凤凰古城”的线路
7、编号，再通过联接客户表、订单客户表和订单线路表，使用子查询技术获取预订线路“凤凰古城”的客户姓名。 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
8、_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, L
9、ine 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语句即可实现此业务。,任务二：查询、统计和维护客户所订旅游线路信息,End,谢谢观看,
