SQL Server 索引和视图

在SQL Server中，设计有效的索引(Index)是影响数据库性能的重要因素之一，合理的索引可以显著提高数据库的查询性能。
视图是一个虚拟表，视图中数据来源于由定义视图所引用的表，并且能够实现动态引用，即表中数据发生变化，视图中的数据随之变化。
统计信息是查询优化器进行查询优化的依据，及时更新统计信息对优化的效果至关重要。SQL Server提供了自动和手动两种方式实现对统计信息的创建及更新功能。

规划索引

用途

SQL Server的索引是为了加速对表中数据检索而创建的一种分散的、物理的数据结构。
数据库中的索引的形式与图书的目录相似，键值就像目录中的标题，指针相当于页码。

索引是一个逻辑文件（无法打开查看），包含从表或视图中一个或多个列生成的键，以及映射到指定数据行的存储位置指针。

当SQL Server执行查询时，查询优化器会对可用的多种数据检索方法的成本进行估计，从中选用最有效的查询计划。

使用索引的优点：

加速数据检索：索引能够以一列或多列值为基础实现快速查找数据行。
优化查询：查询优化器是依赖于索引起作用的，索引能够加速连接、排序和分组等操作。
强制实施行的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。

创建索引

创建时机：
在CREATE TABLE 或 ALTER TABLE语句中定义或修改表结构时 都可以 创建索引。

创建索引之前还需要考虑两个问题：

权限问题，只有表的拥有者才能在表上创建索引。
每个表最多可以创建249个非聚集索引。
在创建聚集索引时还要考虑到数据库剩余空间的问题。
创建聚集索引时所需要的可用空间是数据库表中数据量的120%。如果空间不足会降低性能，甚至导致索引操作失败。

利用SSMS创建

使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：

启动SQL Server Management Studio，展开“资源管理器”窗口中teaching数据库“表”子目录。
选择student表并展开，右击“索引”项，如图8-1所示。在快捷菜单中选择“新建索引”“聚集索引”命令。

使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：

在弹出的“新建索引”对话框中，选择“常规”选项卡，输入所引名称Idx_student，取代默认名称。如图8-2所示。其中各项说明如下。
① 表名：指出创建索引的表的名称，用户不可更改。
② 索引名称：输入所创建索引的名称，由用户设定。
③ 索引类型：本例在索引类型组合框中选择“聚集”。
④ 唯一：选中表示创建唯一性索引。本例唯一复选框为选中状态。
设置完成后，按照提示，单击索引键列的“添加”按钮，出现如图8-3所示的从Student表中选择列对话框。
在“表列”列表中选中要建立索引的一列或多列，如选择studentno列。
索引键列设置完毕，单击“确定”按钮，返回到“新建索引”对话框，在“索引键列”中的“排序顺序”组合框中可以选择的“升序”或“降序”。如图8-4所示。

进行必要的设置后，单击“脚本”，可以查看创建本索引Idx_student的代码。代码如下：

单击“确定”按钮，即完成了创建聚集索引的操作。此时，就可以在当前索引子目录中查看创建的索引文件。

利用CREATE INDEX命令创建

SQL Server 2016提供的创建索引的Transact-SQL语句是CREATE INDEX，其基本语法格式如下：

CREATE  [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name
ON    { table_or_view_name }
(column [ ASC|DESC ][ ,...n ] )
  [ INCLUDE (column_name[ ,...n])]
  [ ON { filegroup_name |default  } ][ ; ]

例8.1】在 teaching 数据库中的 student 表的Email列上创建唯一索引IDX_Email。

      CREATE UNIQUE  INDEX  IDX_Email
           ON  student(Email)

注意：
唯一性约束确保索引列不包含重复的值，重复数据插入操作出现重复键值时会发出错误消息。

【例8.2】在 teaching 数据库中 student 表的 studentno 和 classno 列上创建组合索引 IDX_sc。

本例首先在系统表sysindexes中查找是否存在名称为IDX_sc的索引，如存在则将其删除;
然后在student表上创建非聚集非唯一索引。
本索引键值由列studentno和classno的值组合而成。

IF EXISTS(SELECT name FROM sysindexes WHERE name='IDX_sc')
   DROP INDEX student.sc
GO
CREATE INDEX IDX_sc ON student(studentno,classno)

维护索引

在SSMS中修改索引

使用SSMS修改索引的参考操作步骤如下：
（1）启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
（2）选择并展开“索引”项，右击Idx_student索引，在快捷菜单中选择“属性”命令。
（3）出现“索引属性”对话框，在各选项卡中可以修改索引的设置。在“常规”选项卡中可以添加或删除索引键列、改变键列排序。
（4）在“选项”选项卡中可实现对于在访问索引时是否使用行锁和页锁、填充因子等索引选项的修改。如选中“设置填充因子”和“填充索引”复选框，并设置填充因子为80%。
（5）在“存储”选项卡中可实现对于索引的文件组和分区属性的修改。切换至“扩展属性”选项卡，可以修改与索引相关的扩展信息。
（6）切换至“碎片”选项卡，该选项卡用于查看索引碎片数据以确定是否需要重新组织索引，如图8-9所示。
（7）修改完毕，单击“脚本”按钮，可以查看修改索引的代码。单击“确定”按钮，即可完成操作。

利用ALTER INDEX命令修改索引

SQL Server 2016提供的创建索引的Transact-SQL语句是ALTER INDEX，其语法基本格式如下：

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD
    [ [ WITH ( <rebuild_index_option> [ ,...n ] ) ] ]
    | DISABLE
    | REORGANIZE   }[ ; ]

重新生成和重新组织不同：
重新生成：删除现在的索引，重新建立
重新组织：优化之前的索引

禁用索引应用场景：

导入巨量数据（需要几小时的导入时间）时，可以临时禁用索引，全部导入，然后删除重复，再启动索引。
如果不禁用索引，效率低而且遇到重复数据就停止导入，无法完成任务。

索引碎片

碎片
SQL Server 2016的索引数据是随着表数据的插入、更新或删除操作而自动维护的。这些修改可能会导致索引中的信息分散在数据库中，本来可以存储在一个页中的索引却不得不存储在两个或更多的页上，这样的情况称为索引中存在碎片。

影响：
当索引包含的页中基于键值的逻辑排序与数据文件中的物理排序不匹配时，就会存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。
解决方案：
SQL Server可以通过重新组织索引或重新生成索引来修复索引碎片。

索引碎片检测

决定使用哪种碎片整理方法的前提是检测索引碎片并分析以确定碎片程度。
SQL Server 2016提供了查看和检测有关索引碎片信息的方法，并且可以通过对检测结果的分析，确定处理碎片的最佳方法。在检测结果中，逻辑碎片的百分比属性中的取值可用来决定下一步的处理方法。

一般情况下，如该属性值<=30%，推荐采用索引重组，如果该属性值>30%，推荐采用索引重建。也可以使用sys.dm_db_index_physical_stats()函数获取索引平均碎片。

【例8.3】使用sys.dm_db_index_physical_stats()函数获取score表中所有索引的平均碎片。

SELECT   avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_id(‘score’),null,null,null)

索引重组

索引重组是通过对索引的叶级页进行物理重新排序，使其与叶结点的逻辑顺序相匹配，从而对表或视图的聚集索引和非聚集索引的叶级别进行碎片整理。

实现方式：

索引在分配给它的现有页内重新组织。如果索引跨越多个文件，则将一次重新组织一个文件。
重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有空页。压缩基于设置的填充因子值。

和索引重建对比

重新组织进程使用最少的系统资源，且是自动联机执行的。
索引碎片不太多时，可以重新组织索引。如果索引碎片非常多，重新生成索引则可以获得更好的结果。

SQL语句：
使用ALTER INDEX REORGANIZE语句可实现对索引的重新组织。

【例8.4】重新组织 teaching 数据库中 student 表上的 IDX_sc 索引。

ALTER INDEX IDX_sc ON dbo.student
REORGANIZE

索引重建

索引重建将删除已存在的索引并创建一个新索引。此过程中将删除碎片，通过使用指定的或现有的填充因子设置压缩页来回收磁盘空间，并在连续页中对索引行重新排序。这样可以减少获取所请求数据所需的页读取数，从而提高磁盘性能。
ALTER INDEX REBUILD语句基本语法格式：
使用ALTER INDEX REBUILD语句重建索引

ALTER INDEX { index_name | ALL } ON <object>
    REBUILD 
    [ WITH ( <rebuild_index_option> [ ,...n ] ) ]

【例8.5】重新生成 teaching 数据库中 student 表上的 IDX_Email 索引，设置填充索引，将填充因子设置为80%，设置将中间排序结果存储在tempdb中。

ALTER INDEX IDX_sname ON dbo.student 
REBUILD 
WITH(PAD_INDEX = ON,FILLFACTOR = 80,SORT_IN_TEMPDB = ON)

使用带DROP_EXISTING子句的CREATE INDEX语句重建索引
ALTER INDEX语句不能通过添加或删除键列、更改索引类型、更改列顺序或更改列排序顺序来更改索引定义，如需完成此类操作，可通过带DROP_EXISTING子句的CREATE INDEX语句实现。

【例8.6】重新生成 teaching 数据库中 student 表上的 Idx_student 索引，指定该索引的填充因子为70%。

CREATE UNIQUE CLUSTERED INDEX Idx_student
ON dbo.student(studentno)
WITH(PAD_INDEX = ON,FILLFACTOR = 70,DROP_EXISTING = ON)

索引的分析

数据库表创建索引之后，由于数据的添加、删除和修改会导致索引中的信息分散到不同的数据页，形成索引碎片，需要对索引进行分析和维护。
SHOWPLAN_ALL 命令
SHOWPLAN_ALL 命令可用于在SQL Server中显示查询计划。查询计划包括显示在执行查询的过程中、连接表时所采取的步骤，以及是否选择、选择了哪个索引，从而帮助用户分析有哪些索引被系统采用。通常在查询语句中设置SHOWPLAN_ALL选项，可以选择是否让SQL Server显示查询计划。SHOWPLAN_ALL 命令的使用格式如下：
SET SHOWPLAN_ALL ON ︳OFF

【例8.7】使用SHOWPLAN_ALL命令对Transact_SQL语句进行分析。

SET SHOWPLAN_ALL ON
GO
SELECT studentno, sname, birthdate, phone FROM student
WHERE YEAR(birthdate)>=2000
GO
SET SHOWPLAN_ALL OFF
GO
STATISTICS IO命令
STATISTICS IO命令用于数据检索语句所花费的磁盘活动量，这也是用户比较关心的性能之一。通过设置STATISTICS IO选项，可以是SQL Server显示磁盘IO信息。
设置是否显示磁盘IO统计的命令格式如下：
SET STATISTICS IO ON| OFF
【例8.8】利用STATISTICS IO分析Transact_SQL语句执行过程中的磁盘使用情况。

SET STATISTICS IO ON
GO
SELECT studentno, sname, birthdate, phone
FROM student
WHERE birthdate BETWEEN ’1999-01-01’ AND ’2000-09-30’
GO
SET STATISTICS IO OFF
GO

删除索引

当一个索引不再需要时，可将其从数据库中删除，以回收它当前使用的磁盘空间。删除索引之前，必须先删除PRIMARY KEY或UNIQUE约束，才能删除约束使用的索引。
如果数据已经排序，则重新生成索引的过程无需按索引列对数据排序，重新生成索引有助于重新创建聚集索引。
另外，删除视图或表时，系统将自动删除为永久性和临时性视图或表创建的索引。

使用SQL Server Management Studio删除索引

使用SSMS删除索引的操作步骤如下：
（1）启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
（2）选择并展开“索引”项，右击索引IDX_sc，在快捷菜单中选择“删除”命令。
（3）在弹出的“删除对象”对话框中，其中显示要删除的索引，单击“确定”按钮即可完成删除操作。

使用Transact-SQL语句删除索引

使用DROP INDEX语句可从当前数据库中删除一个或多个索引。
【例 8.9】删除 teaching 数据库中 student 表上的聚集索引 Idx_student 和非聚集索引IDX_Email。
程序代码如下：
DROP INDEX student. Idx_student,student. IDX_Email

统计信息及应用（了解）

SQL Server 能够收集、存储在数据库中索引和列数据的统计信息，查询优化器使用这些统计信息来选择用于数据检索和更新操作的最有效执行计划。

当系统执行查询语句时，查询优化器将根据统计信息决定在执行时是否使用索引，能够以最小的执行成本来完成操作获得结果。

1 . 统计信息的收集

统计信息自动创建和更新功能

SQL Server 2016在维护统计信息方面具有许多特性，最为重要的一点就是能够自动创建和更新统计信息，这项功能有助于查询优化器生成一致且有效的查询计划。

启动SSMS，展开“资源管理器”|“数据库”，右击teaching数据库，在快捷菜单中选择“属性”命令，出现“数据库属性”对话框，切换至“选项”选项卡，可以看到“自动创建统计信息”和“自动更新统计信息”组合框的默认设置均为True。
自动创建的统计信息分两种情况。
（1）在数据表的某个列或列组合上创建索引后，系统自动创建一个同名的统计信息。如PK_student和IDX_sc。
（2）对于数据表中未曾创建索引的单个列，当使用该列执行 SELECT、INSERT、UPDATE和 DELETE 语句时，系统会在评估最佳查询计划前，创建一个该列的统计信息，名称以“_WA_Sys”开头。

SQL Server 2016收集的信息

SQL Server 2016在表级别维护以下信息。这些信息并不属于统计信息对象，而是SQL Server 2016在某些情况下用来进行查询成本估算的。

SQL Server 2016收集关于表中列的下述统计信息，并存储在一个统计信息对象中（statblob）.
（1）表或索引的行数（sys.sysindexes表的rows列）
（2）表或索引占用的页面数（sys.sysindexes表的dpages列）
（3）统计信息收集的时间。
（4）用于生成直方图和密度信息的行数。
（5）平均键的长度和包含了步数的单列直方图。
（6）字符串摘要（如果某一列含有字符串信息）。

统计信息存储在sysindexes系统表的statblob列中，存储在statblob列中的每一个值称为一个分类步长。
分类步长
指的是数据抽样之间的距离，或下一个样本被抽样和存储前跨越了多少行。
索引的第1个和最后1个键值通常被包含到统计信息中。

Tatblob列本身存储在一张内部目录表中。Statblo用于存储二进制大对象统计信息。该对象存储在一个内部目录视图sys.sysobjvalues中。

2. 统计信息的创建

SQL Server 2016提供的创建统计信息的Transact-SQL语句是CREATE STATISTICS，其语法格式如下：

       CREATE STATISTICS statistics_name 
       ON { table |view} (column [,...n ] ) 
       [ WITH 
        [[ FULLSCAN | SAMPLE  number 
             { PERCENT | ROWS }] 
        [ NORECOMPUTE ]   ]

statistics

【例8.10】在 student 表的 studentno 和 classno 上创建一个统计组 studentclass，要求对所有记录计算统计信息。

CREATE STATISTICS studentclass
    ON teaching.dbo.student (studentno,classno)
    WITH FULLSCAN

3 .查看统计信息

使用SSMS查看统计信息

使用SQL Server Management Studio查看统计信息的操作步骤如下：
（1）启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “表”student 子目录。
（2）选中并展开“统计信息”项，右击IDX_sc，在快捷菜单中选择“属性”命令。
（3）弹出如图8-13所示的“统计信息属性”对话框。在“常规”选项卡中显示如下信息。

① “表名”：显示统计信息中所涉及表的名称。
② “统计信息名称”：显示存储的统计信息的名称。
③ “统计信息列”：显示统计信息中所涉及的数据列及相关信息。
④ “上次更新了这些列的统计信息”：显示上一次更新统计信息的日期和时间。
⑤ “更新这些列的统计信息”：选中此项后将在关闭时完成对统计信息的更新操作。

（4）在“统计信息属性”对话框中切换至“详细信息”选项卡，如图8-14所示。这些统计信息包括以下3部分内容：
① 标题信息主要包括表中的行数、统计的抽样行数、所有索引列的平均长度等信息。
② 密度信息主要包括索引列前缀集的选择性、平均长度等信息。
③ 直方图信息则制定显示直方图时的信息。

使用DBCC SHOW STATISTICS命令查看

SQL Server 2016提供了DBCC SHOW STATISTICS命令用于显示指定表上的指定目标的当前分发统计信息，其基本语法格式为：

DBCC SHOW_STATISTICS ( 'table_name' | 'view_name' , target ) 
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM

【例8.11】通过 DBCC SHOW STATISTICS 命令显示 student 表 u_Email 索引的统计信息。

DBCC SHOW_STATISTICS (student, u_Email)

视图

视图概念

视图是从一个或者多个表及其他视图中通过SELECT语句导出的虚拟表，视图所对应数据的行和列数据来自定义视图查询所引用的表，并且在引用视图时动态生成。通过视图可以实现对基表数据的查询与修改。
视图为数据库用户提供了很多的便利，主要包括以下几个方面。
（1）简化数据查询和处理。视图可以为用户集中多个表中的数据，简化用户对数据的查询和处理。
（2）屏蔽数据库的复杂性。数据库表的更改不影响用户对数据库的使用，用户也不必了解复杂的数据库中的表结构（3）安全性。如果想要使用户只能查询或修改用户有权限访问的数据，也可以只授予用户访问视图的权限，就提高了数据库的安全性。

创建视图

视图是作为一个独立的数据库对象进行存储的。创建视图通常有使用SQL Server Management Studio图形工具和Transact-SQL语句两种方法。

使用SSMS创建

（1）在“对象资源管理器”中展开“数据库”子目录。
（2）右击“视图”选项，在快捷菜单中选择“新建视图”命令，进入视图设计界
。在该图中窗口右侧的“视图设计器”中包括以下4个窗格：

关系图窗格：以图形方式显示正在查询的表和其他表结构化对象，同时也显示它们之间的关联关系。
网格窗格：是一个类似电子表格的网格，用户可以在其中指定视图的选项。
SQL 窗格：显示视图所要存储的查询语句。
结果窗格：显示最近执行的选择查询的结果。

注意：
为视图选择包含的数据列。可通过“关系图窗格”、“网格窗格”和“SQL窗格”3种方式实现，一个窗格中做出修改，另外两个窗格将会同步保持一致。具体方法如下：
① 关系图窗格：单击数据列左边的复选框即可将该列添加到查询结果集内。
② 网格窗格：通过“列”和“表”组合框可选择需添加到查询结果集中的数据列及所属的数据表。
③ SQL 窗格：通过SELECT子句选择需添加到查询结果集中的数据列。

指定查询条件。
可通过“网格窗格”和“SQL窗格”两种方式实现，具体方法如下：
① 网格窗格：通过“筛选器”可为关联数据列指定搜索条件。
② SQL 窗格：通过WHERE子句指定查询条件。
本例在网格窗格中的“表”组合框和“列”组合框中分别选择student表的classno列和score表的final列。如果所选数据列只作为搜索子句，而不需在结果集内显示，可以将“输出”复选框设置为未选中状态。在“筛选器”中输入查询条件“= '180501'”和“IS NOT NULL”，如图8-18所示。

指定分组依据和条件。
可通过“网格窗格”和“SQL窗格”两种方式实现，具体方法如下：
① 网格窗格：打开“查询设计器”菜单，选择“添加分组依据”命令，“分组依据”网格列将显示在网格窗格中。默认情况下，在查询结果集内出现的列，将成为GROUP BY子句的一部分。
② SQL 窗格：通过添加GROUP BY和HAVING子句指定分组依据和分组条件。
本例在网格窗格中实现。student.sname 和 student.classno 默认分组依据分别为Group By和Where。如图8-19所示。

（3）设置完成后，单击“保存”按钮。在弹出的对话框中输入视图的名称View_avg后，单击“确定”按钮，即完成了创建视图的操作。

使用Transact-SQL语句创建

CREATE VIEW语句创建视图的语法格式如下：

CREATE VIEW [database_name.] [schema_name.] view_name [(column [,…n])]
  [WITH view_attribute [,…n ]]
  AS
  select_statement
  [WITH CHECK OPTION]

【例8.14】在 teaching 数据库中创建一个名为 V_course 的视图，包含所有类别为“必修”的课程信息。

CREATE VIEW V_course
AS
SELECT *
FROM course
WHERE type = '必修'

【例8.15】编程在 teaching 数据库中创建一个名为V_final 的视图，包含学生学号、姓名、课程号、课程名和期末成绩，按学号升序排序，学号相同的行按课程号升序排序。

CREATE VIEW V_final
AS
SELECT TOP(100) PERCENT student.studentno,student.sname,course.courseno, course.cname,score.final
FROM student,course,score
WHERE student.studentno = score.studentno AND course.courseno = score.courseno
ORDER BY student.studentno,course.courseno

【例8.16】在 teaching 数据库中创建一个名为V_max 的视图，查询每个班最高分的课程名和分数，按班级号升序排序。

CREATE VIEW V_max
AS
SELECT top 10 classno,cname,MAX(final) AS max
FROM student s,score sc,course c
where sc.courseno = c.courseno and s.studentno = sc.studentno and final IS NOT NULL
GROUP BY classno,cname
ORDER BY classno

查看视图数据

通过视图查看数据：查询视图数据既可以使用SSMS方式，也可以使用SELECT语句。
使用SSMS查看
（1）启动SQL Server Management Studio，展开“资源管理器” “数据库”|teaching数据库 “视图”子目录。
（2右击V_course视图，在快捷菜单中选择“编辑前200行”命令。然后进入数据浏览窗口，可和查看数据表一样查看V_course视图中的数据。

使用SELECT语句：和正常表一样

SELECT * FROM V_course

查看视图信息（了解）

使用SSMS查看视图信息
（1）启动SSMS，展开“资源管理器”窗口中的“teaching数据库” “视图”子目录。
（2）查看视图的列信息。选中并展开View_avg视图|“列”子目录，在其下面显示视图的列信息，包括列名称、数据类型、长度精度和是否为空的约束信息。
（3）查看视图的依赖关系。右击View_avg视图，在快捷菜单中选择“查看依赖关系”命令。出现如图8-21所示的“对象依赖关系”对话框。
（4）查看视图定义信息。右击View_avg视图，在快捷菜单中选择“编写视图脚本为”|“CREATE到”|“新查询编辑器窗口”命令，可查看View_avg视图的定义信息。

使用系统表查看视图信息
当用户创建的一个视图被存储到SQL Server 2016系统中后，视图的名称等基本信息存储在sysobjects系统表中，对应的存储对象类型type为“V”。
有关视图中所定义的列的相关信息存储在syscolumns系统表中，有关视图与其他数据库对象之间的依赖关系信息存储在sysdepends系统表中，创建视图的Transact-SQL定义语句的文本存储在syscomments系统表中。

视图的修改

视图定义之后，用户可以更改视图的名称或视图的定义而无需删除并重新创建视图。删除并重新创建视图会造成与该视图关联的权限丢失。

1.在SSMS中修改视图：操作步骤如下：
（1）启动SQL Server Management Studio，展开“资源管理器”窗口中的“teaching数据库” “视图”子目录。
（2）右击V_final视图，在快捷菜单中选择“设计”命令。
（3）打开“视图设计器”窗口，可在其中对视图进行修改，其中的操作与创建视图类似。本例在网格窗格中添加student表的classno列，不指定别名和排序类型，“输出”复选框设置为未选中状态，在“筛选器”中输入查询条件“= '180502'”，如图8-23所示。

（4）修改完成后，可以单击工具栏的“！”按钮执行新的V_final视图的SELECT查询，其查询的结果集显示在结果窗格中。最后单击工具栏上的“保存”按钮，完成修改视图的操作。
2. 利用ALTER VIEW命令修改视图
使用ALTER VIEW命令可修改先前创建的视图，其中包括索引视图。
ALTER VIEW的语法结构如下：
ALTER VIEW [database_name.] [schema_name.] view_name
[(column [,...n])]
[WITH view_attribute [,…n ]]
AS
select_statement
[WITH CHECK OPTION]

利用ALTER VIEW命令修改视图
【例8.19】使用ALTER VIEW语句修改 V_final 视图，使其包含所有学生姓名、课程名和期末成绩，按姓名升序排序。
ALTER VIEW V_final
AS
SELECT TOP(100) PERCENT student.sname,cname,final
FROM student,course,score
WHERE student.studentno = score.studentno
AND course.courseno = score.courseno
ORDER BY student.sname
利用ALTER VIEW命令修改视图
【例8.20】使用ALTER VIEW语句修改View_avg视图，将其改为加密方式，以确保视图的安全性。
--在“查询编辑器”中输入以下程序，修改View_avg视图为加密方式。
ALTER VIEW View_avg
WITH ENCRYPTION
AS
SELECT TOP (100) PERCENT student.sname, AVG(score.final) AS average
FROM score INNER JOIN student
ON score.studentno = student.studentno
WHERE student.classno = '180501' AND score.final IS NOT NULL
GROUP BY student.sname
HAVING AVG(score.final) > 60
ORDER BY average DESC
--使用系统存储过程sp_helptext查看已加密视图的定义信息，执行如下程序：
EXEC sp_helptext View_avg

程序执行结果是在输出窗口中显示“对象'View_avg' 的文本已加密”的提示。由此例可以看出，加密过的视图无法查看其定义文本信息，从而起到保护源程序的作用。
3. 视图重命名
重命名视图可以在SQL Server Management Studio中，可以像在Windows资源管理器中更改文件夹或文件名一样，右击需要重命名的视图，在快捷菜单中选择“重命名”命令，然后输入新的视图名称即可
系统存储过程sp_rename更改当前数据库中用户创建对象的名称，此对象可以是表、列、索引、视图或用户定义数据类型等。
例如使用存储过程将数据库teaching中的视图V_成绩重命名为V_final。
EXEC sp_rename 'V_成绩','V_final1'
3. 视图重命名
重命名视图可以在SQL Server Management Studio中，可以像在Windows资源管理器中更改文件夹或文件名一样，右击需要重命名的视图，在快捷菜单中选择“重命名”命令，然后输入新的视图名称即可
系统存储过程sp_rename更改当前数据库中用户创建对象的名称，此对象可以是表、列、索引、视图或用户定义数据类型等。
例如使用存储过程将数据库teaching中的视图V_成绩重命名为V_final。
EXEC sp_rename 'V_成绩','V_final1'
4. 删除视图
删除视图时，定义在系统表sysobjects，syscolumns，syscomments，sysdepends和sysprotects中的视图信息也会被删除，而且视图的所有权限也一并被删除。
使用SSMS删除视图：操作步骤如下：
（1）在SQL Server Management Studio中，展开 “视图”子目录。
（2）选择并右击V_max视图，在快捷菜单中选择“删除”命令。
（3）在弹出的“删除对象”对话框，其中显示要删除的视图，单击“确定”按钮即可完成删除操作。

使用Transact-SQL删除视图
使用DROP VIEW语句可从当前数据库中删除一个或多个视图。

例如使用Transact-SQL语句删除teaching数据库中V_max视图的命令如下：
DROP VIEW V_max

通过视图修改数据

视图是一种虚拟表，通过视图可以修改与视图相关的、符合一定条件的基表数据，包括插入、更新和删除等基本操作。

通过视图向基表中插入数据

在视图上使用INSERT语句添加数据时，要符合以下规则。

用户使用INSERT语句向数据表中插入数据时必须具有相关权限。
插入操作只能使用同一个基表的列，且不能是通过计算或聚合函数等方式派生出的列。
在基表中插入的数据必须符合在相关列上定义的约束条件，如是否为空、约束及默认值定义等。
视图中不能包含DISTINCT、GROUP BY或HAVING子句。
如果在视图定义中使用了WITH CHECK OPTION子句，则该子句将检查插入的数据是否符合视图定义中SELECT语句所设置的条件，如果插入的数据不符合该条件，SQL Server会拒绝插入数据，并显示错误。

【例8.21】通过视图V_course 向基表 course 中插入数据 ('c05129', '数据库编程', '必修', 64, 4)。
分析：该程序通过单表生成的视图V_course向基表course中插入一条记录，并通过查询语句显示基表中的所有数据。

INSERT INTO V_course
VALUES('c05129','数据库编程','必修',64,16,5)
GO
SELECT * FROM course

【例8.22】编程在teaching数据中创建一个名称为V_sex的视图，包含所有性别为“女”的学生的学号、姓名、性别、出生日期和班级编号，需限制插入数据中性别必须为“女”。
分析：该程序通过单表生成的视图V_sex向基表student中插入一条记录，并通过查询语句显示基表中的所有数据。

--在“查询编辑器”中输入以下程序，创建V_sex视图。

CREATE VIEW V_sex
AS
  SELECT studentno,sname,sex,birthdate,classno
  FROM student
  WHERE sex='女'
  WITH CHECK OPTION

--通过视图V_sex向基表student中插入数据。
INSERT INTO V_sex 
   VALUES('18138211038','李静','女','2000-6-3','180802')
GO
SELECT * FROM student

该记录符合相关要求其执行结果就是向student插入一条记录行。
本例由于创建了WITH CHECK OPTION条件约束，当插入记录时所有“性别”不符合条件的记录无法插入和修改，并显示错误提示信息。

--通过视图V_sex向基表student中插入数据行('18122221548', '张晓明', '男', '2000-11-20', '180501')。
INSERT INTO V_sex
VALUES('18122221548','张晓明','男','2000-11-20','180501')
GO
SELECT * FROM student

【例8.23】通过视图V_final向其多个基表中插入数据。

INSERT INTO V_final
VALUES('18122221324','何平','数据库应用技术',64)

由于视图引用了多个表的数据列，因此插入操作无法实现，错误提示如下

消息 4405,级别16,状态1、第3行
视图或函数‘V_final”不可更新,因为修改会影响多个基表。

2.通过视图更新基表中的数据

使用UPDATE语句可以实现视图对于基表中相关记录的修改，但不能同时修改两个或者多个基表中的数据，不能对主属性进行修改操作。视图中被修改的列不能通过计算或聚合函数等方式派生。

3. 通过视图删除基表中的数据

在视图上可以使用DELETE语句实现对于基表中相关记录的删除。但如果在视图中删除数据，该视图只能引用一个基表的列，且删除操作必须满足基表中定义的约束条件。

posted @ 2022-11-16 09:22 kingwzun 阅读(664) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

kingwzun