《中职 数据库应用基础（SQL Server 2016）项目6教学课件.pptx》由会员分享，可在线阅读，更多相关《中职 数据库应用基础（SQL Server 2016）项目6教学课件.pptx（23页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、中职 数据库应用基础（SQL Server 2016）项目6教学课件工信版p 理解索引的概念和类型p 掌握设计索引的方法p 掌握创建索引的方法p 理解视图的概念、用途和限制p 掌握创建视图的方法p 掌握管理和应用视图的方法 项目目标 6.1.1 索引的基本概念在SQL Server数据库中，数据是以页作为基本单位进行存储的，页的大小为8KB。为数据库中的数据文件分配的磁盘空间可以从逻辑上划分成页（从0到n连续编号）。磁盘I/O操作是在页级执行的，SQL Server将读取或写入所有数据页。当向表中添加行时，数据存储在数据页中，但数据行并不按特定的顺序存放，数据页也没有特定的顺序。当一个数据页放
2、满数据行时，数据将存放到另一个数据页上，这些数据页的集合称为堆。索引是与表或视图关联的磁盘上或内存中结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键，这些键存储在一种称为B树的结构中，使SQL Server可以快速有效地查找与键值关联的行。索引在逻辑上以组织为包含行和列的表存储数据，在物理上则以行存储格式或列存储格式来存储数据。在SQL Server中，行存储是指基础数据存储格式为堆、B树或内存优化表的表，这是存储关系表数据的传统方法。任务6.1 理解索引6.1.2 索引的类型p 聚集索引。聚集索引基于聚集索引键按顺序排序和存储表或视图中的数据行。聚集索引按B树
3、索引结构实现，B树索引结构支持基于聚集索引键值对行进行快速检索。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序排序。p 非聚集索引。既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。p 哈希索引。借助于哈希索引可通过内存中的哈希表来访问数据。哈希索引的内存用量固定不变，是存储桶数量的函数。p 内存优化非聚集索引。对于内存优化的非聚集索引，内存使用量依赖于行计数以及索引键列的大小。p 唯一索引。唯一索引可以确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。p 列存储索引。内存中列存储索引通过使用基于列的数据存储和基于列的查询处理来存储和管理
4、数据。p 包含性列索引。这是一种非聚集索引，它扩展后不仅包含键列，还包含非键列。p 计算列索引。这是从一个或多个其他列的值或某些确定的输入值派生的列上的索引。p 筛选索引。这是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。p 空间索引。利用空间索引可以更高效地对几何数据类型的列中的空间对象（空间数据）执行某些操作。p XML索引。这是xml数据类型列中XML二进制大型对象（BLOB）的已拆分持久表示形式。p 全文索引。这是一种特殊类型的基于标记的功能性索引，由Microsoft SQL Server全文引擎服务创建和维护。任务6.1 理解索引6.2.1 索引设计
5、准则1.设计索引的数据库准则p 如果在一个表中创建大量的索引，将会影响NSERT、UPDATE和DELETE语句的性能，因为在更改表中的数据时，所有索引都要进行适当的调整。应避免对经常更新的表进行过多的索引，并且索引应保持较窄，列要尽可能少。p 使用多个索引可以提高更新少而数据量大的查询的性能。大量索引可以提高不修改数据的查询（例如SELECT语句）的性能，因为查询优化器有更多的索引可供选择，从而可以确定最快的访问方法。p 对小表进行索引可能不会产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长。因此，小表的索引可能从来不用，但仍必须在表中的数据更改时
6、进行维护。p 视图包含聚合、表连接或聚合和连接的组合时，视图的索引可以显著地提升性能。若要使查询优化器使用视图，并不一定非要在查询中显式引用该视图。p 使用数据库引擎优化顾问来分析数据库并生成索引建议。任务6.2 设计索引6.2.1 索引设计准则2.设计索引的查询准则p 为经常用于查询中的谓词和连接条件的所有列创建非聚集索引。p 涵盖索引可以提高查询性能，因为符合查询要求的全部数据都存在于索引本身中。p 将插入或修改尽可能多的行的查询写入单个语句内，而不要使用多个查询更新相同的行。p 评估查询类型以及如何在查询中使用列。例如，在完全匹配查询类型中使用的列就适合用于非聚集索引或聚集索引。3.设计
7、索引的列准则p 对于聚集索引，应保持较短的索引键长度。另外，对唯一列或非空列创建聚集索引可以使聚集索引获益。p 不能将ntext、text、image、varchar(max)、nvarchar(max)和varbinary(max)数据类型的列指定为索引键列。p xml数据类型的列只能在XML索引中用作键列。p 检查列的唯一性。在同一个列组合的唯一索引而不是非唯一索引提供了有关使索引更有用的查询优化器的附加信息。p 在列中检查数据分布。如果为包含很少唯一值的列创建索引或在这样的列上执行连接，则会导致长时间运行的查询。p 如果索引包含多个列，则应考虑列的顺序。p 考虑对计算列进行索引。任务6.
8、2 设计索引6.2.2 设计聚集索引聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。每个表几乎都对列定义聚集索引来实现下列功能：可用于经常使用的查询；提供高度唯一性；可以用于范围查询。创建PRIMARY KEY约束时，将在列上自动创建唯一索引。默认情况下，此索引是聚集索引，但是在创建约束时，也可以指定创建非聚集索引。如果没有使用UNIQUE属性创建聚集索引，则数据库引擎将向表自动添加一个4字节的标识列，必要时数据库引擎还将向行自动添加一个标识值，使每个键唯一。此列和列值供内部使用，用户不能查看或访问。任务6.2 设计索引6.2.3
9、 设计非聚集索引非聚集索引包含索引键值和指向表数据存储位置的行定位器。通常情况下，设计非聚集索引是为了改善经常使用的、没有建立聚集索引的查询的性能。对表或索引视图可以创建多个非聚集索引。与使用书中索引的方式相似，查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使非聚集索引成为完全匹配查询的最佳选择，因为索引包含说明查询所搜索的数据值在表中的精确位置的项。每个索引项都指向表或聚集索引中准确的页和行，其中可以找到相应的数据。在查询优化器在索引中找到所有项之后，它可以直接转到准确的页和行进行数据检索。任务6.2 设计索引6.2.4 设计唯一索引唯一索引
10、能够保证索引键中不包含重复的值，从而使表中的每一行从某种方式上具有唯一性。只有当唯一性是数据本身的特征时，指定唯一索引才有意义。使用多列唯一索引，索引能够保证索引键中值的每个组合都是唯一的。例如，若为成绩表中的学号列和课程编号列的组合创建了唯一索引，则表中的任意两行都不会有这些列值的相同组合。唯一索引具有以下优点：能够确保定义的列的数据完整性；提供了对查询优化器有用的附加信息。聚集索引和非聚集索引都可以是唯一的。只要列中的数据是唯一的，就可以为同一个表创建一个唯一聚集索引和多个唯一非聚集索引。创建PRIMARY KEY或UNIQUE约束会自动为指定的列创建唯一索引。创建UNIQUE约束和创建独
11、立于约束的唯一索引没有明显的区别。数据验证的方式是相同的，而且查询优化器不会区分唯一索引是由约束创建的还是手动创建的。但是，如果要实现数据完整性，则应为列创建UNIQUE或PRIMARY KEY约束，这样做才能使索引的目标明确。如果数据中存在重复的键值，则不能创建唯一索引、UNIQUE 约束或PRIMARY KEY约束。如果数据是唯一的并且希望强制实现唯一性，则为相同的列组合创建唯一索引可以为查询优化器提供附加信息，从而生成更有效的执行计划。在这种情况下，建议创建唯一索引（最好通过创建UNIQUE约束来创建）。唯一非聚集索引可以包括包含性非键列。任务6.2 设计索引6.3.1 使用SSMS创建
12、索引 连接到数据库引擎实例，在对象资源管理器中展开该实例。依次展开“数据库”和“表”，展开要在其中创建索引的表，右键单击“索引”，指向“新建索引”，然后单击“聚集索引”或“非聚集索引”。在“新建索引”对话框的“常规”页中，指定索引名称，选择索引类型，指定索引是否具有唯一性；然后单击“添加”按钮，在“添加列”对话框选择要添加到索引的列并设置其排序顺序。若要设置索引的选项，可以选择“选项”页并对相关选项进行设置。单击“确定”按钮，完成索引的创建。任务6.3 创建索引6.3.2 使用SQL语句创建索引CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX 索引名称 ON
13、 表或视图名称(列 ASC|DESC,.)WITH(IGNORE_DUP_KEY=ON|OFF|DROP_EXISTING=ON|OFF）WHERE.其中UNIQUE指定创建唯一索引，不允许两行具有相同的索引键值。CLUSTERED指定创建聚集索引，即在创建索引时由键值的逻辑顺序决定表中对应行的物理顺序。在创建任何非聚集索引之前创建聚集索引，创建聚集索引时会重新生成表中现有的非聚集索引。NONCLUSTERED指定创建非聚集索引，即创建指定表的逻辑排序的索引。对于非聚集索引，数据行的物理排序独立于索引排序。NONCLUSTERED是默认值。索引名称在表或视图中必须唯一，但在数据库中不必唯一。索
14、引名称必须符合标识符的规则。表或视图名称指定要为其建立索引的表或视图的名称。任务6.3 创建索引6.3.3 查看索引信息1.使用sp_helpindex查看索引信息sp_helpindex 表或视图名称其中表或视图名称指定用户定义的表或视图的限定或非限定名称。仅当指定限定的表或视图名称时，才需要使用引号。如果提供了完全限定名称，包括数据库名称，则该数据库名称必须是当前数据库的名称。sp_helpindex返回的结果集包括以下三个列：index_name（索引名称）、index_description（索引说明）以及index_keys（对其生成索引的表或视图列）。2.使用sp_spaceuse
15、d查看索引使用的空间sp_spaceused 对象名称,更新使用其中对象名称指定请求其空间使用信息的表或索引视图的限定或非限定名称。仅当指定限定对象名称时，才需要使用引号。如果提供完全限定对象名称（包括数据库名称），则数据库名称必须是当前数据库的名称。如果未指定对象名称，则返回整个数据库的结果。任务6.3 创建索引6.3.4 删除索引1.使用SSMS删除索引 连接到数据库引擎实例，在对象资源管理器中展开该实例。展开待删除索引所在的表，展开该表下方的“索引”节点。右键单击要删除的索引，然后选择“删除”命令。在“删除对象”对话框中单击“确定”按钮。2.使用SQL语句删除索引DROP INDEX 表
16、名称.索引名称,.其中表名称指定索引所在表的名称。索引名称指定要从表中删除的索引。执行DROP INDEX语句后，将重新获得以前由索引占用的所有空间。这些空间随后可以用于任何数据库对象。任务6.3 创建索引6.4.1 视图的基本概念视图是一个虚拟表，其内容由选择查询定义。与真实的表一样，视图也包含一系列带有名称的列和行数据，但这些列和行数据来自由定义视图的查询所引用的表，并且是在引用视图时动态生成的，而不是以数据值存储集形式存在于数据库中（索引视图除外）。视图中引用的表称为基础表。对基础表而言，视图的作用类似于筛选。定义视图的筛选可以来自当前或其他数据库的一个或多个表，也可以来自其他视图。分布
17、式查询也可以用于定义使用多个异类源数据的视图。例如，如果有多台不同的服务器分别存储企业在不同地区的数据，而需要将这些服务器上结构相似的数据组合起来，使用这种方式就很方便。视图类型:p 标准视图。p 索引视图。p 分区视图。p 系统视图。任务6.4 理解视图6.4.2 视图的用途和限制视图通常用在以下三种场合：（1）简化数据操作。（2）自定义数据。（3）提高数据库的安全性。创建视图时应注意以下几点：p 创建视图时必须遵循标识符命名规则，在数据库范围内视图名称要具有唯一性。p 一个视图最多可以引用1 024个列，这些列可以来自一个或多上表或视图。p 定义视图的查询不能包含INTO关键字。p 定义视
18、图的查询不能包含ORDER BY，除非在SELECT选择列表中使用TOP子句。p 视图可以在其他视图上创建，SQL Server允许视图最多嵌套32层。p 即使删除了一个视图所依赖的表或视图，这个视图的定义仍然保留在数据库中。p 可以在视图上定义索引。p 不能在视图上绑定规则、默认值和触发器。p 不能创建临时视图，也不能在一个临时表上创建视图。p 只能在当前数据库中创建视图。任务6.4 理解视图6.5.1 使用SSMS创建视图 连接到数据库引擎实例，在对象资源管理器中展开该实例。展开要在其中创建视图的数据库，右键单击“视图”节点并选择“新建视图”。在“添加表”对话框中选择要引用的一个或多个基础
19、表，然后单击“添加”按钮。若要在视图中引用已有的视图，可选择“视图”选项卡。选择并添加表或视图后，单击“关闭”按钮。在视图设计器中通过定义选择列表、设置筛选条件以及指定排序顺序等，生成用于定义视图的SELECT语句。完成视图定义后，单击“保存”按钮。任务6.5 创建视图6.5.2 使用SQL语句创建视图CREATE VIEW 架构名称.视图名称(列,.)WITH,.AS SELECT语句WITH CHECK OPTION;:=ENCRYPTION SCHEMABINDING VIEW_METADATA任务6.5 创建视图6.6.1 修改视图1.使用SSMS修改视图 连接到数据库引擎实例，在对象
20、资源管理器中展开该实例。在视图所属数据库中展开“视图”节点，右键单击要修改的视图，选择“设计”。在视图设计器中，对定义视图的SELECT语句进行修改。保存更改。2.使用SQL语句修改视图ALTER VIEW 架构名称.视图名称(列,.)WITH,.AS SELECT语句 WITH CHECK OPTION;:=ENCRYPTION SCHEMABINDING VIEW_METADATA任务6.6 管理和应用视图6.6.2 重命名视图1.使用SSMS重命名视图 连接到数据库引擎实例，在对象资源管理器中展开该实例。展开“数据库”，展开该视图所在的数据库，展开该数据库下方的“视图”节点。右键单击要重
21、命名的视图并选择“重命名”。为视图输入新的名称并按下Enter键。2.使用SQL语句重命名视图在Transact-SQL中，可以使用系统存储过程sp_rename在当前数据库中更改用户创建对象的名称，此对象可以是表、视图、索引、列以及别名数据类型等。任务6.6 管理和应用视图6.6.3 查看视图相关信息如果视图定义没有加密，则可以获取该视图定义的有关信息。在实际应用中，可能需要查看视图定义以了解数据从源表中的提取方式，或通过SELECT语句来查看视图所定义的数据。如果更改视图所引用对象的名称，则必须更改视图，使其文本反映新的名称。因此，在重命名对象之前，首先显示该对象的依赖关系，以确定即将发生
22、的更改是否会影响任何视图。除了使用SELECT语句查看视图定义的数据外，还可以使用下列目录视图或系统存储过程来获取有关视图的相关信息。p 查看当前数据库中包含哪些视图：查询目录视图sys.views。p 查看指定视图中包含哪些列：查询目录视图sys.columns。p 查看指定视图的定义文本：执行系统存储过程sp_helptext。p 查看视图引用了哪些表以及哪些列：执行系统存储过程sp_depends。任务6.6 管理和应用视图6.6.4 通过视图修改数据通过视图不仅可以从一个或多个基础表中查询数据，还可以修改基础基表的数据，修改方式与通过UPDATE、INSERT和DELETE语句或使用b
23、cp实用工具和BULK INSERT语句修改表中数据的方式一样。但是，通过视图更新数据时有以下限制。p 任何修改（包括UPDATE、INSERT和DELETE语句）都只能引用一个基表的列。p 视图中被修改的列必须直接引用表列中的基础数据，它们不能通过其他方式派生，例如，通过聚合函数计算形成的列得出的计算结果是不可更新的。p 正在修改的列不受GROUP BY、HAVING或DISTINCT子句的影响。p 如果在视图定义中使用了WITH CHECK OPTION子句，则所有在视图上执行的数据修改语句都必须符合定义视图的SELECT语句中所设置的筛选条件。p INSERT语句必须为不允许空值且没有D
24、EFAULT定义的基础表中的所有列指定值。p 在基础表的列中修改的数据必须符合对这些列的约束，如为空性、约束及DEFAULT定义等。p 不能对视图中的text、ntext或image列使用READTEXT语句和WRITETEXT语句。任务6.6 管理和应用视图6.6.5 删除视图使用SSMS删除视图：连接到数据库引擎实例，在对象资源管理器中展开该实例。展开“数据库”，展开视图所属的数据库，展开该数据库下方的“视图”节点。右键单击要删除的视图，选择“删除”。在“删除对象”对话框中，单击“确定”按钮。使用DROP VIEW从当前数据库中删除一个或多个视图：DROP VIEW 架构名称.视图名称,.;其中schema_name指定该视图所属架构的名称。view_name指定要删除的视图的名称。任务6.6 管理和应用视图