第07章：存储过程、事务、视图、索引

事务的使用
示例：客户“雷亚波”在美淘网购买了两份“宝岛牛排”和一双“花 花公子登山鞋”，请使用事务技术维护相应的数据表以反映该购买业 务。
1.
客户购买商品，除在订单表（Orders）上生成一条记录外，还应在订单明 细表（OrdersDetail）上生成两条记录。
2. 因而需要在上述这两张表上分别进行“INSERT”操作。两个操作是一个业
完全独立于数据行的结构。
非聚集索引中的数据排列顺序并非表中数据的排列顺序。
一个表可以拥有多个非聚集索引，每个非聚集索引提供访问数据
的不同排序顺序。
在默认情况下，SQL Server所建立的索引是非聚集索引。
聚集索引VS非聚集索引
插入数据 速度
查询数据 速度 快
索引的数量 一表一个
所需空间 少
索引的使用场合
索引使用的代价 维护索引，在对数据进行插入、更新和删除操作时会耗费系统时 间。
在建立索引时，由于需要复制数据，也会耗费系统的时间和空间 。 可在以下场合创建索引 主键、外键。 需要在指定范围快速或频繁查询的列。 需要排序的列。 在聚合函数中使用的列。
索引分类
当遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保 证数据库中数据的一致性和可恢复性。 转账交易过程中的转出和转入可设计为一个数据库事务。
事务的特性
原子性（atomic、atomicity)：事务必须是一个原子工作单元。对于 其数据修改，或者全都执行，或者全都不执行。
一致性（consistent、consistency)：事务在完成时，必须使所有的 数据都保持一致的状态，即事务执行的结果必须是使数据库从一个一 致性状态转换为另一个一致性状态。 隔离性（isolation)：由并发事务所作的修改必须与任何其他并发事 务所作的修改隔离。 持久性（duration、durability）：事务完成后，其对于系统的影响 是永久性的。
• 通常在主外键、频繁查询的列、排序列和聚合函数列的字段上建立索引。 依据需求，可在线路表的线路名和价格上建立索引。
系统需要经常获取每个客户预订线路的数量，如何使用索引技术 提升查询效率?
视图：内容Βιβλιοθήκη Baidu览
理论 实践
• 视图
讲解时间： 20分钟
• 练习使用视图
实践时间： 40分钟
视图
视图(View)是一种数据库对象，是一个从一张表、多张表或视图中导 出的虚表。视图的结构和数据是对数据表进行查询的结果。
隐式事物
隐式事务 在当前事务提交或回滚后，SQL Server自动开始下一个 事务。 隐式事务不需要使用BEGIN TRANSACTION语句启动事务 ，仅需要用户使用COMMIT TRANSACTION或ROLLBACK TRANSACTION语句提交或回滚事务。 在提交或回滚后，SQL Server自动开始下一个事务。 执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。
聚集索引
聚集索引特点：
将数据行的值在表内排序，并存储对应的数据记录，使数据表物理顺序 与索引顺序相一致。 当以某字段作为关键字建立聚集索引时，表中数据以该字段作为排序依 据。
一个表仅能建立一个聚集索引，但该索引可以包含多个列（组合索引）
主键默认为聚集索引 。
非聚集索引
非聚集索引特点：
第 7章
存储过程、事务、视图、索引
本章内容
1 2 3 3
索引 视图 事物 存储过程
索引：内容预览
理论 实践
• 索引
讲解时间： 15分钟
• 练习创建索引
实践时间： 30分钟
索引
索引
SQL Server数据访问方法 表扫描法：从表的起始处逐行查找，直至找到符合查询条件的记 录为止。
索引法：系统会通过遍历索引结构来查找行的存储位置，相比表 扫描法，效率大为提高。 索引的作用 增强数据记录的唯一性。 可以大大加快数据检索的速度。 加速表与表之间的连接，这在实现数据参照完整性方面有特殊的 意义。 使用ORDER BY和GROUP BY子句进行检索数据时，可以显著减少在 查询中排序和分组所占用的时间。
务整体，仅允许全部成功，如果其中有一个失败，则撤销所有的操作。 3. 使用数据库事务可以很好地实现该业务。
事务·参考代码·(1)变量声明
DECLARE @ordersID int,@productID int DECLARE @customerID int = 999 DECLARE @ordersTime varchar(10)='2016-01-01' DECLARE @deliveryTime varchar(10)='2016-01-03'
利用T-SQL语句CREATE INDEX。
使用SSMS创建索引
现使用对象资源管理器在商品表的“团购价”字段上建立非聚集索引
使用SSMS创建索引
使用SSMS创建索引
使用SSMS创建索引
使用T-SQL创建索引
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name„)
CREATE VIEW v_Product_Category_Area_Shop AS SELECT p.title 商品标题, c.categoryName 类型, p.currentPrice
团购价, a.areaName 地区, s.shopName 商店
FROM Product p,Category c,Area a,Shop s WHERE p.categoryID=c.categoryID AND p.areaID=a.areaID AND p.shopID=s.shopID AND p.currentPrice>400
自动提交事务
自动提交事务 将每个T-SQL语句都视为一个事务，如果成功执行，则自动提交； 如果出现错误，则自动回滚。 它是SQL Server默认的事务模式。
在数据库实际开发过程中，显式事务用到得最多。 因为自动提交事务无法将多条语句作为一个独立的逻辑单元 来处理， 而隐式事务无法明确控制事务的开始位置。
使用T-SQL语句创建视图
创建视图的语法 CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] AS SELECT_statement
使用T-SQL平台创建视图
使用T-SQL语句创建团购价高于400元的商品信息视图，用于查看商品 类型名、商品标题、团购价、地区名和商店名。
创建复杂查询视图
视图中的列不仅可以是基表的数据列，还可以是计算列或聚合函数列
。 例：创建一个视图，用于生成每个订单的金额，并利用该视图更新订
单表中相应订单的金额
1. 生成每个订单的金额，需联接订单表、订单详细表和商品
表，并根据订单分组、汇总每个订购明细的金额。
2. 在子查询中使用视图用于更新订单表金额。
显式事务
显式事务： 显式地定义事务的开始和事务的结束。 BEGIN TRANSACTION：标识一个事务的开始，即启动事 务。 COMMIT TRANSACTION：提交事务。标识一个事务的结 束，事务内所修改的数据被永久保存到数据库中。 ROLLBACK TRANSACTION：回滚事务，即事务内所修改 的数据被回滚到事务执行前的状态。
视图仅存放视图的定义，不存放视图所对应的数据。 视图如果基表中的数据发生变化，则从视图中查询出的数据也随之改 变。
视图的特点： 关注点聚焦 简化操作 定制数据 合并分割数据 提供安全机制
使用SQLServer平台创建视图
使用SSMS管理平台，创建团购价高于400元的商品信息视图，用于查 看商品类型名、商品标题和团购价。
事务·参考代码·(2)主体代码
BEGIN TRANSACTION -- 启动事务 INSERT INTO Orders(customerID,ordersDate,deliveryDate) -- 添加订单表记录 VALUES(@customerID,CONVERT(date,@ordersTime),CONVERT(date,@deliveryTime)) IF @@ERROR=0 -- 添加订单表记录成功 BEGIN SELECT @ordersID=MAX(ordersID) FROM Orders-- 获取新增订单的订单编号 SELECT @productID=productID FROM Product WHERE title='宝岛牛排' INSERT INTO OrdersDetail(ordersID,productID,quantity) VALUES(@ordersID,@productID,2) -- 添加2份“宝岛牛排”到订单明细表 SELECT @productID=productID FROM Product WHERE title='花花公子登山鞋' INSERT INTO OrdersDetail(ordersID,productID,quantity) VALUES(@ordersID,@productID,1) -- 添加1双“花花公子登山鞋”到订单明细表 IF @@ERROR=0 -- 添加订单表和订单明细表记录成功 BEGIN PRINT ‘添加订单明细成功' COMMIT TRANSACTION -- 提交事务 END ELSE BEGIN PRINT '添加订单明细失败' ROLLBACK TRANSACTION -- 回滚事务 END END ELSE BEGIN PRINT '添加订单失败' ROLLBACK TRANSACTION -- 回滚事务 END
在商品表的“商品编号”和“标题”字段上建立 非聚集索引 CREATE NONCLUSTERED INDEX idx_Product_productID_title ON Product(productID, title)
实践练习
练习巩固上述语法
实践时间： 30分钟
客户在预订线路时，经常需要根据线路名进行查询，并按价格排 序显 示。如何使用索引技术提高检索。
以存储结构区别，有“聚集索引”（Clustered Index，也称聚类索 引、簇集索引）和“非聚集索引”（NonClustered Index，也称非聚 类索引、非簇集索引）。 以数据的唯一性区别，有“唯一索引”（Unique Index）和“非唯一 索引”（NonUnique Index）。
以索引列的个数区分，则有“单列索引”与“多列索引”。 索引 索引 索引 聚集索引 非聚集索引 唯一索引 非唯一索引 单列索引 多列索引
视图创建完成后，点击“保存”并命名为“v_Product_Category”。 通常约定视图名以“v”作为前缀，并用下划线连接基表名。 SELECT * FROM v_Product_Category。
使用SQLServer平台创建视图
使用SQLServer平台创建视图
使用SQLServer平台创建视图
聚集索引
非聚集索引
慢
快
慢
一表可以 多个
多
创建索引
系统自动创建索引
系统在创建表中的其他对象时，可以附带创建新索引。通常情况 下，在创建UNIQUE约束或PRIMARY KEY约束时，SQL Server会自
动为这些约束列创建聚集索引。
用户创建索引
使用SQL Server Management Studio 的对象资源管理器。
UPDATE Orders SET amount=A.calAmount FROM( SELECT ordersID, calAmount FROM v_OrdersAmount
)A WHERE Orders.ordersID=A.ordersID
实践练习
练习巩固上述语法 创建一个视图，用于获取所有线路的预订次数。 创建一个视图，用于获取所有顾客的线路预订信息。
• 为获取每个客户预订线路的详细信息，需联接客户表Customer、订单客户表 OC_Detail、订单线路表OL_Detail和线路表Line。使用这四张表创建一个视图， 即可查询所有客户的预订信息。
实践时间： 40分钟
• 线路表Line提供了线路基本信息，订单线路表OL_Detail提供了线路预订情况。 使用这两张表创建一个视图，获取所有线路的预订次数。
创建复杂查询视图
CREATE VIEW v_OrdersAmount AS SELECT od.ordersID, SUM(p.currentPrice*od.quantity)
calAmount
FROM Orders o, OrdersDetail od, Product p WHERE o.ordersID=od.ordersID AND p.productID=od.productID GROUP BY od.ordersID
创建一个视图，用于获取每一种线路类型的预订次数。 创建一个视图，用于获取所有预订“国内短线游”客户的基本信息。
事物：内容预览
理论 实践
• 事务
讲解时间： 20分钟
• 练习使用事务
实践时间： 40分钟
为什么需要事物
什么是事物
事务是一个由用户所定义的完整的工作单元，一个事务内的所有语句 作为一个整体来执行，即或者全部执行，或者全部不执行。