实践第7章-实践幻灯片.pptxVIP

实践7 视图、索引和事务实践目标使用视图简化查询使用索引提高查询效率使用事务保证数据一致性任务一：优化旅游线路信息查询和统计使用视图简化线路信息查询和统计。使用索引提高线路信息查询效率。（1）创建一个视图，用于获取所有线路的预订次数。（2）创建一个视图，用于获取所有顾客的线路预订信息。（3）客户在预订线路时，经常需要根据线路名进行查询，并按价格排序显 示。如何使用索引技术提高检索。任务一：优化旅游线路信息查询和统计需求说明（1）解决方案：线路表Line提供了线路基本信息，订单线路表OL_Detail提供了线路预订情况。使用这两张表创建一个视图，获取所有线路的预订次数。CREATE VIEW v_Nums_Line ASSELECT L.lineName , COUNT(OLD.lineID) nums FROM Line L, OL_Detail OLDWHERE L.lineID=OLD.lineID GROUP BY L.lineName 任务一：优化旅游线路信息查询和统计需求说明（2）解决方案：为获取每个客户预订线路的详细信息，需联接客户表Customer、订单客户表OC_Detail、订单线路表OL_Detail和线路表Line。使用这四张表创建一个视图，即可查询所有客户的预订信息。CREATE VIEW v_Customer_OrderLine_Detail ASSELECT C.name, L.lineName, L.days, L.price FROM Customer C, OC_Detail OCD, OL_Detail OLD ,Line LWHERE 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）系统需要经常获取每个客户预订线路的数量，如何使用索引技术提升查询效率?任务二：删除游线路信息删除“出境游”线路类型信息。在不违背外键约束的前提下删除“出境游”线路类型信息。DECLARE @lineTypeID int --线路类型编号变量SELECT @lineTypeID=lineTypeID FROM LineType WHERE typeName=出境游BEGIN TRANSACTION--将“出境游”类型的线路编号置为NULLUPDATE Line SET lineTypeID=NULL WHERE lineTypeID=@lineTypeIDIF @@ERROR=0BEGIN PRINT 线路表修改成功‘ -- 删除“出境游”类型记录 DELETE FROM LineType WHERE lineTypeID=@lineTypeID IF @@ERROR=0 BEGIN PRINT 线路类型表删除成功 COMMIT TRANSACTION -- 提交事务 END ELSE BEGIN PRINT 线路类型表删除失败 ROLLBACK TRANSACTION – 回滚事务 ENDENDELSEBEGIN PRINT 线路表修改失败 ROLLBACK TRANSACTION -- 回滚事务END任务二：删除游线路信息需求说明解决方案：线路类型表LineType与线路表Line是一对主从表，线路类型表是主表，线路表是从表，两者之间存在外键约束。为删除“出境游”线路类型信息，只能先将线路表中类型为“出境游”的类型编号赋值为NULL，然后再删除线路类型表的“出境游”记录。这两个操作是一个整体，或者全部成功，或者全部撤销。可使用数据库事务技术实现该业务。任务二：查询、统计和维护客户所订旅游线路信息需求说明（2）解决方案：定义一个变量，用于存储“凤凰古城”的线路编号，再通过联接客户表、订单客户表和订单线路表，使用子查询技术获取预订线路“凤凰古城”的客户姓名。DECLARE @lineName nvarchar(20)=凤凰古城DECLARE @lineID int --定义线路编号SELECT @lineID=lineID FROM Line WHERE lineName=@lineNameSELECT name 客户姓名 FROM Customer