数据库SQL ServerSQLite教程课件：视图与索引

视图与索引6.1视图6.2索引

图6-1健康码和行程码

6.1视图6.1.1视图的概念和作用⒈视图的概念视图(View)是保存在数据库中从一个或多个表或视图中导出由查询语句定义生成的一个虚拟表。与真正的数据表类似，视图也是由一组命名的列和数据行构成的，其结构和数据是建立在对表或视图查询的基础上。数据库只存储视图的定义，而不存储对应的数据，这些数据仍然存储在导出该视图的数据表中，当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。

⒉视图的使用及注意事项

通过视图来访问数据，而不必直接去访问对应的数据表，实现数据从分散到集中，简化处理，便于共享。对视图的一般操作与对表的操作一样，可以对其进行查询、修改、删除和更新。当对视图中的数据表进行修改时，其对应数据表的数据也会同步发生变化，同时这种变化也自动地反映到视图中。

在创建视图时，应注意以下几点：

(1)只能在当前数据库中才能创建视图。

(2)视图的命名必须遵守标识符命名规则，不能与表同名，且对每一个用户视图名必须是唯一的。不能把规则、默认值或触发器与视图相关联。

3.视图的作用

视图的作用有以下几点：

(1)视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

(2)视图提供了一个统一访问数据的接口，即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限。

(3)增强了安全性，使用户只能看到视图所显示的数据。

6.1.2视图的创建、修改及删除

1.创建视图

1)使用菜单方式创建视图

例6-1使用表student(s#,sname)、sc(s#,c#,score)、course(c#,cname,credit)创建视图，显示学号、姓名、课程代码、成绩、课程名和学分等信息。

操作步骤如下：

(1)在“对象资源管理器”中展开“数据库”节点，展开要创建视图的数据库，再展开“视图”节点，显示当前数据库的所有视图。右击“视图”节点，在弹出的快捷菜单中选择“新建视图”命令。

(2)在弹出的“添加表”对话框中选择与视图相关联的表、视图或函数，可以按住“Ctrl”键选择相应的多个表。选择完毕后，单击“添加”按钮，然后单击“关闭”按钮，如图6-2所示。

(3)在视图设计器中共有4个区：表区、列区、SQL语句区和查询结果区。在表区中选择创建视图所需要的列，此时SQLServer脚本显示在SQL区，在列区可以指定别名、

排序方式和规则等。

(4)右击创建视图区域，在弹出的快捷菜单中选择“执行SQL”命令，或单击工具栏中的“执行”按钮，在最下面的窗口中显示视图对应的结果集，如图6-3所示。

图6-2添加表对话框图6-3“创建视图”选项卡

(5)右击视图选项卡，在弹出的快捷菜单中选择“保存视图”命令(如图6-4所示)，或单击工具栏中的“保存”按钮，在弹出的“选择名称”窗口中输入新的视图的名称，单击“确定”按钮，完成视图的创建。图6-4“保存视图”快捷菜单

2)使用代码方式创建视图

使用createview语句创建视图，其语法格式如下：

参数说明：

(1)schemaname是视图在数据库中所属架构的名称，如果没有指定，则视图属于默认架构dbo。view_name是新建视图的名称。column是视图中的列名，如果没有指定，则列名由select语句指定。

(2)<view_attribute>={[encryption][,schemabinding]

[,view_metadata]}，其中：encryption表示对视图进行加密。SQLServer为了保护视图的定义，使用withencryption子句可以不让用户查看视图的定义文本。schemabinding表示将视图绑定到底层所应用到的表，在select语句中如果包含表、视图或函数，则表名、视图名或函数名前必须有所有者前缀。

(3)as指定视图要执行的操作。select_statement是定义视图的select语句。

(4)withcheckoption是附加检查选项，从而保证在对视图执行数据修改后，通过视图仍可看到这些数据，否则修改无效。也就是对视图上的数据的修改都必须符合select语句

设置的条件。

例6-2使用表student(s#,sname)、sc(s#,c#,score)、course(c#,cname,credit)创建视图，显示学号、姓名、课程代码、成绩、课程名和学分等信息。代码如下：

3)使用别名创建视图

在默认情况下，视图中的列名和查询语句中的列名相同，也可以通过createview语句中指定列别名。

例6-3使用表student(s#,sname)、sc(s#,c#,score)、course(c#,cname,credit)创建视图，并统计学生的平均分、课程门数，并在createview语句中指定列的别名。要求视图包括学号、姓名、平均分和课程门数。代码如下

结果如图6-5所示。图6-5使用别名创建视图并显示结果

4)使用withcheckoption子句创建视图

视图的使用隔断了用户与表之间的联系，方便用户理解。为了防止用户错误地插入或修改，在视图定义时需要使用到withcheckoption选项。

例6-4使用表student(s#，sname，age，sex，classid)，创建一个只包含20180102班的视图。查询视图显示结果如图6-6所示。图6-6使用withcheckoption子句创建视图并显示结果

2.修改、删除视图

1)视图的修改

(1)使用菜单方式修改视图。在“对象资源管理器”中展开“数据库”节点，展开相应的数据库和视图节点，右击视图，选择“设计”菜单，进入视图设计器进行必要的修改，

修改完成单击“保存”按钮即可。

(2)使用代码方式修改视图。使用alterview语句可以修改视图。修改视图与删除并重新创建视图是不同的，修改视图会保持视图的权限不变，但删除并重新创建视图则意味着

视图的重新定义。

例6-5在当前数据库中，修改在例6-4中创建的视图student_2，添加withencryption选项。代码如下：

2)视图的删除

视图并不是数据库中必需的数据库对象，对于不需要的视图可以使用dropview语句将其删除，删除视图后，其所对应的数据不会受到影响。如果有其他数据库对象使用了该

视图，仍可以删除该视图，只是再使用那些数据库对象时，将会发生错误。

(1)使用代码删除视图。

例6-6删除例6-5中创建的视图student_2。代码如下：

dropviewstudent_2

(2)使用菜单删除视图。展开数据库和视图节点，在要删除的视图上右击鼠标，在弹出的快捷菜单中选择“删除”命令，单击“确定”按钮即可删除视图。

6.1.3视图的使用

视图一经创建，就可以当成表来使用。可以在查询中使用单个视图，也可以使用视图和表或者视图与视图关联查询。

例6-7使用例6-3中创建的视图ssc_view2，查询平均分大于等于75分的学生信息。

代码如下：

select*fromssc_view2where平均分>=75

6.2索引

6.2.1SQLServer的数据存储SQLServer有两种数据存储文件，分别是数据文件和日志文件，其中数据文件是以8KB(8192Byte)的页面(Page)作为存储单元，日志文件是以日志记录作为存储单元。以数据文件为例，从页面类型、数据页面结构、数据页缓存、盘区、数据访问等方面入手，讨论其存储格式与方式。

1.SQLServer定义的页面类型

SQLServer定义的页面类型有8种，如表6-1所示。用户的数据一般存储在数据页面中，在一个数据页面中，要知道数据如何存放，根据什么来定位页面与页面上的数据，就

要先了解数据页面的结构。

2.数据页面结构

在数据页面上，数据行紧接着页首按顺序放置，在页尾有一个行偏移表。在行偏移表中，页上的每一行都有一个条目，每个条目记录那一行的第一个字节与页首的距离。页偏

移表中的条目序列与页中行的序列相反。

数据页面的结构如图6-7所示。数据页面页首96个字节保存着页面的系统信息，如页的类型、页的可用空间量、拥有页的对象的ID及该页面属于哪个物理文件。图6-7SQLServer数据页

3.数据页缓存

SQLServer数据库的主要用途是存储和检索数据，因此密集型磁盘I/O是数据库引擎的一大特点。由于完成磁盘I/O需要消耗许多资源且耗时较长，因此SQLServer侧重于提高I/O效率。缓冲区管理是实现高效I/O的关键环节，一个缓冲区就是一个8KB大小的内存页，其大小与一个数据页或索引页相当，因此缓冲区高速缓存被划分为多个8KB页。缓冲区管理器负责将数据页或索引页从数据库磁盘文件读入缓冲区高速缓存中，并将修改后的页写回磁盘。

4.盘区

SQLServer默认的存储分配单位是盘区。为了避免频繁地读写I/O，在表或其他对象分配存储空间时，不是直接分配一个8KB的页面，而是以一个盘区(Extent)为存储分配

单位，一个盘区为8个页面(8×8KB=64KB)。

SQLServer定义了两种盘区类型：统一盘区和混合盘区。统一盘区只能存放同一对象，该对象拥有这个盘区的所有页面。

5.数据访问

系统访问表中的数据时，可以采用表扫描和索引查找两种方式。如果对数据页上的数据进行访问，一维升序或降序数据序列可以采用两分检索的方法迅速找到需要插入或删除

元素的位置。但当采用顺序存储的方式时，插入一个元素，需要将其下面的数据进行后移，反之删除一个元素，需要将其下面的数据进行前移。为避免大量的数据移动，提高插入或删除的工作效率，研究者提出了多种解决方案，其中B树是较好的一种方案。

B树是由一系列节点所构成，它的每一个节点均由2M个数据域和2M+1个指针域构成，每个节点的数据从左向右升序排列。一般情况下，B树的每个节点中的数据域不一定

存满数据，但基本上每个节点存放的数据个数大于B树M个，如图6-8所示。图6-8B树示例(节点数据的关系)

B树中父节点与子节点中的数据之间具有以下关系：父节点中每一数据域中存放的数据，均大于该数据域左侧指针指向的子节点中的所有数据，也小于该数据域右侧指针指向

子节点中的所有数据。如图6-8所示，为建立一棵B树，需要将一个个的数据插入其中。当查询到插入位置，发现该节点已填满数据时，需要进行节点的分割，如图6-9所示。图6-9B树示例(节点分割)

6.2.2索引及其创建与删除

1.索引的概念

索引(Index)是SQLServer在列上建立的一种数据库对象。它保存着表中排序的索引列，并记录索引列在表中的物理存储位置，从而实现表中数据的逻辑排序。一张表的存储

是由数据页面和索引页面组成的。索引就存放在索引页面上，当进行数据检索时系统先搜索索引页面，从中找到所需数据的指针，再通过指针从数据页面中读取数据。

2.索引的类型

对于索引类型的划分有多种，通常根据索引对表中记录顺序的影响分类，可以分为聚集索引和非聚集索引。此外，还有唯一索引与非唯一索引、单列索引与多列索引等分类。

下面主要介绍聚集索引和非聚集索引。

1)聚集索引

聚集索引(ClusteredIndex)是指表中的数据记录实际存储的次序与索引中相对应的键值的实际存储次序完全相同的索引。也就是说，聚集索引将对表中的物理数据页中的

数据按列进行排序，然后再存储到磁盘上。聚集索引与数据是融为一体的，因此聚集索引查找数据最快。当然，一个表只能有一个聚集索引。

2)非聚集索引

非聚集索引(NonclusteredIndex)是指表中的数据记录实际存储的次序与索引中相对应的键值的实际存储次序不相同的索引。也就是说，表中的数据不是按照索引列排序的，使用索引页存储，比聚集索引占用更多的存储空间，检索效率也较低。一个表中可以同时有聚集索引和非聚集索引，而且一个表可以有多个非聚集索引，但是一个表中最多不超过

250个索引。

3.索引的创建与删除

索引的创建分为直接方式和间接方式两种。直接方式就是使用命令或工具直接创建索引；间接方式就是在创建其他对象时附带创建了索引，例如在设置主键约束或唯一性约束

时，系统将自动创建索引。这里重点介绍直接创建索引的方法。

1)使用菜单方式创建/修改/删除索引

例6-8在books表为bookid列创建聚集索引，索引名为Ix_books_bookid。

操作步骤如下：

(1)在“对象资源管理器”中展开数据库节点和表节点，右击“索引”节点，在弹出的快捷菜单中选择“新建索引”命令，弹出“新建索引”对话框。

(2)在“新建索引”对话框中进行设置。单击“常规”选项，在“索引名称”框中输入名称，在“索引类型”框中选择“聚集”，单击“添加”按钮，在弹出的“从表dbo.books中选择列”对话框中选中bookid列前面的复选框，单击“确定”按钮，返回“新建索引”对话框，如图6-10所示。

图6-10“新建索引”对话框

(3)单击“确定”按钮，完成聚集索引的创建。

例6-9在表sc的s#列已创建聚集索引Ix_sc_s#，要求修改该索引，使索引设置在s#和c#列上，索引名改为Ix_sc_s#c#。

操作步骤如下：

(1)在“对象资源管理器”中展开“数据库”节点和“表”节点，再展开sc表节点，展开“索引”节点，右击“Ix_sc_s#(聚集)”，在弹出的快捷菜单中选择“属性”命令，弹出“索引属性”对话框。

(2)在“索引属性”对话框中进行相应设置。在“常规”选项中单击“添加”按钮，在弹出的“从‘dbo.sc’中选择列”对话框中同时选中s#列和c#列前面的复选框，如图6-11所示。单击“确定”按钮，返回“索引属性”对话框。图6-11“修改索引-添加列”对话框

(3)单击“确定”按钮，完成聚集索引的修改。

注意：该聚集索引是直接创建的，如果是设置主键附加的聚集索引，则不能手动删除索引，在修改主键约束的同时重新创建索引。

例6-10在表studinfo的email列上创建一个唯一的非聚集索引，该列的email值是不重复的，索引名为Ix_studinfo_email，该索引建立在文件组filegroup1上，该索引的中间节点和叶级节点的填满度均为60%。

操作步骤如下：

(1)在“对象资源管理器”中展开“数据库”节点和“表”节点，再展开studinfo表节点，右击“索引”节点，在弹出的快捷菜单中选择“新建索引”命令，弹出“新建索引”对话框。在“新建索引”对话框中进行设置。

(2)单击“常规”选项，在“索引名称”框中输入名称，在“索引类型”框中选择“非聚集”，勾选“唯一”复选框，单击“添加”按钮，在弹出的“从dbo.studinfo中选择列”

对话框中选中email列前面的复选框，单击“确定”按钮，返回“新建索引”对话框。

(3)单击“选项”选项，勾选“设置填充因子”复选框，在后面的列表框中输入60，勾选“填充索引”复选框，如图6-12所示。

图6-12“新建索引”对话框

(4)单击“存储”选项，在“文件组”下拉列表中选择“filegroup1”。单击“确定”按钮，完成唯一的非聚集索引的创建。

例6-11删除在表studinfo的email列上创建的非聚集索引，该列的索引名为Ix_studinfo_email。

操作步骤如下：

(1)在“对象资源管理器”中展开“数据库”节点和“表”节点，再展开studinfo表节点，然后再展开“索引”节点，右击Ix_studinfo_email索引节点，在弹出的快捷菜单中选择“删除”命令，弹出“删除索引”对话框。

(2)单击“确定”按钮，完成索引的删除。

2)使用代码方式创建/重新生成索引/删除索引

(1)索引的创建。使用createindex命令创建索引，语法格式如下：

参数说明：

①unique是指创建唯一索引，clustered是指创建聚集索引，nonclustered是指创建非聚集索引。indexname是索引名称，tablename是指索引所在的表名称，viewname是指索

引所在的视图的名称。

②onfilegroup是指将索引存放在指定的文件组中。

③<indexoption>={pad_index={on|off}|fillfactor=fillfactor|sort_in_tempdb={on|off}|ignore_dup_key={on|off}|statistics_norecompute={on|off}|drop_existing={on|off}}，其中：indexoption是索引属性。pad_index用于指定索引填充，默认值为off。

(2)重新生成索引。重新生成索引将根据指定的或现有的填充因子设置压缩页来删除碎片、回收磁盘空间，然后对连接页中的索引行重新排序。重新生成索引将会删除并重新创建索引。

alterindex命令可重新生成索引或禁用索引，语法格式如下：

参数说明：

①indexname是索引的名称，all是指定与表或视图相关联的所有索引，object是指重建索引的表的名称。

②rebuild[with(<rebuildindexoption>[,...n])]是指定将使用相同的列、索引类型、唯一性属性和排序顺序重新生成索引，其中：

③<rebuildindexoption>和<indexoption>的选项的含义相同。

disable是将索引标记为已禁用，任何索引均可被禁用。

例6-15将表sc的索引文件Ix_sc