视图用索引列会导致索引失效吗 索引和视图

第8章 索引与视图

•     8.1 索引概述 

   8.1.1 什么是索引  

 
    索引是依赖于数据表或视图的一种数据库对象，它保存了针对指定数据表或视图的键值或指针。索引有自己的文件名（即索引文件名），也需要占用磁盘空间。创建索引的目的为了提高对数据表或视图的搜索效率。
    对于数据表来说，索引可以理解为对一个或多个字段值进行排序的结构，本质上它是指向存储在表中指定列的数据值的指针。在SQL Server中，索引是用B_树这种数据结构来构造的，通过索引访问数据实际上是寻找一条从根节点到叶子节点的过程，这个过程一般比直接按顺序访问数据表要高效得多。这是因为通过索引只需少数几个I/O操作可以在较短的时间内定位到表中相应的行，而顺序访问则需要从头到尾逐行比较，在平均意义下使用的时间要多得多。
    索引的作用就是提高对数据表的查询效率，但实际情况并不总是这样。如果对数据表创建过多的索引，反而可能使得对数据的查询效率下降。原因在于，不但搜索庞大的B_树需要时间，而且SQL Server对这些B_树进行维护也可能需要付出巨大的代价和开销。因为B_树作为一种数据结构是存放在数据表以外的地方，需要额外的系统资源，而且当对数据表执行UPDATE、DELETE和INSERT等操作时因需要更改这些B_树而付出大量的时间代价。因此，索引并不是创建得越多就越好。
    总之，索引是独立于数据表的一种数据库对象，它保存了针对于指定数据表的键值和指针。索引文件也需要占用磁盘空间。创建索引的目的是为了提高查询效率。

8.1.2 何种情况下创建索引

    
    过多地创建索引反而降低查询效率，所以如何适当地创建索引，这是问题的关键。一般来说，当数据表很大的时候，对于一些用于查询操作比较频繁的字段，应该对其创建索引，而对于其他字段则少创建索引。
    设计良好的索引可以减少磁盘的I/O操作，降低索引对系统资源的消耗，提高SELECT语句执行效率。但由于执行UPDATE、DELETE或INSERT语句时，需要维护索引，因此可能会降低这些语句的执行效率。
    索引的创建是由用户来完成，而索引的使用则是由SQL Server的查询优化器来自动实现。需要注意的是，并不是所有已创建的索引都会在查询操作中自动被使用。一个索引被使用与否是由SQL Server的查询优化器来决定。

8.1.3 索引的原理——B_树

    索引采用的数据结构是B_树，即在逻辑上索引是一棵B_树，因此了解B_树有助于对索引原理的理解。

    B_树即平衡树（Balanced Tree），一棵m阶的平衡树是满足下列性质的树（在此不考虑空树）：

（1）树中每个节点最多有m棵子树。

（2）根节点除外，所有非叶子节点至少都包含m/2棵子树。

（3）若根节点不是叶子节点，则根节点至少两棵子树。

（4）所有非叶子节点都包含相应的关键信息，一个包含k+1棵子树的非叶节点恰好包含k个关键字。

      

k表示关键字的个数。Ki（i=1,2,…,k）为关键字，且Ki < Ki+1。Ai（i=0,2,…,k）为指向相应子树根节点的指针，且指针Ai-1所指子树中所有节点的关键字均小于Ki（i=1,2,…,k），而Ai+1所指子树中所有节点的关键字均大于Ki（i=1,2,…,k-1）

（5）所有的叶子节点都出现在同一层次上，并且叶子节点不包含任何关键字信息。

        例如，图8.1表示了一棵四阶的B_树。

 

  •     8.2 索引的类型

8.2.1 聚集索引和非聚集索引

1. 聚集索引（Clustered Index）

    聚集索引的主要特点是，索引顺序与数据表中记录的物理顺序相同，每一张数据表只允许拥有一个聚集索引。

    聚集索引与数据是“一体”的，其存在是以表中的记录顺序来体现。这是因为在B_树的叶子节点中存储的是实际的数据。

    为了形象地介绍聚集索引，考虑表student中对s_no字段创建的聚集索引。其典型的聚集索引结果如图8.2所示。

    由图8.2可以看出，聚集索引的索引指针是“不相交”的（这是聚集索引的主要特点），这是因为索引顺序与数据记录的物理顺序是一致的。

    当对一个表定义主键时，聚集索引将自动、隐式被创建。聚集索引一般是在字段值唯一的字段上创建，特别是在主键上创建。如果在字段值非唯一的字段上创建聚集索引，那么SQL Server将对包含此重复字段值的记录添加4个字节的标识符，以完成对这些包含重复字段值的记录进行唯一性标识。

    聚集索引确定了表中记录的物理顺序，它适用于使用频率比较频繁的查询、唯一性查询和范围查询等。从SQL语句的角度看，这些查询主要包括：

　　　　使用BETWEEN、>=、>、<=、<等运算符的查询

　　　　使用JOIN子句的查询

　　　　使用GROUP BY子句的查询

　　　　返回大结果集的查询

    在创建聚集索引时，应考虑在以下的列上创建：

　　　　字段值唯一的字段（特别是标识字段），或绝大部分字段值都不重复的字段，如90%字段值都不重复的字段

　　　　按顺序被访问的字段

　　　　 结果集中经常被查询的字段

    对于以下的字段，尽量避免在其上创建聚集索引：

　　　　更新频繁的字段。因为在数据更新时，为保持与聚集索引的一致性必须移动表中的记录。对数据量大的数据表而言这种移动过程是耗时的，因而是不可取的。

　　　　宽度比较长的字段。因为非聚集索引键值都包含聚集索引键，这会导致所有非聚集索引的“膨胀”，增加非聚集索引的长度，降低查询效率。

    由于聚集索引对表中的数据记录的存放位置一一进行了排序，因此使用聚集索引搜索数据很快。

2. 非聚集索引（Non-Clustered Index）

    非聚集索引也是基于B_树来构造的，但它与聚集索引不同，这主要体现在以下两点：

•    非聚集索引允许表中记录的物理顺序与索引顺序不相同，即非聚集索引不改变表中记录的物理顺序，它只是保存着指向相应记录的指针。一个数据表可以同时拥有一个或多个非聚集索引。

•    非聚集索引的叶子节点包含索引键和指向索引键对应记录的指针，而不包含数据页（不保存实际数据，更多是保存指向记录的指针）。

 【例子】 我们考虑表student中对s_no字段创建的非聚集索引。其可能的非聚集索引结果如图8.3所示。

       由图8.3所示的特点表明，非聚集索引的索引指针是允许“相交”的（这是非聚集索引的主要特点），这是因为在非聚集索引中B_树的叶子结点保存的是记录的指针，索引顺序与数据记录的物理顺序不需要保持一致，只要索引指针正确指向相应的记录即可。

    非聚集索引与数据表是分开的，非聚集索引的改动不会导致数据表的变动。我们可以基于一个或多个字段创建一种或多种不同类型的非聚集索引。但是，正如前面指出，非聚集索引不是创建得越多越好，一般在创建这类索引的时候应该从以下几方面考虑：

     （1）在对数据表创建非聚集索引的时候，应注意以下情况。

    宜对数据量大、更新操作少的表，特别是专门用于查询的数据表创建非聚集索引。例如，面向决策支持系统应用程序的只读数据表。

    不宜对更新操作频繁的数据表创建非聚集索引，否则会降低系统的性能。

    尽量少对OLTP（联机事务处理）类应用程序频繁涉及的数据表创建非聚集索引，因为OLTP应用程序对这类表的更新操作可能很频繁。

    在创建非聚集索引时，尽量避免涉及多字段的索引，即涉及的字段越少越好。

    

    （2）当确定要对一个表创建非聚集索引的时候，要选择哪一字段或哪些字段来创建，这也是需要进一步考虑的问题。

    可考虑对包含大量非重复值的字段创建非聚集索引。如果只有很少的非重复值，如只有0和1，则查询优化器将不使用索引，所以对这类字段不宜创建索引。

    所基于字段的查询不返回大结果集，对此字段可考虑创建非聚集索引。

    对于WHERE子句中用于精确匹配查询的字段，可考虑创建非聚集索引。

    可考虑对覆盖查询的字段创建非聚集索引。这有利于消除对聚集索引的访问，提高查询效率。

8.2.2唯一索引与非唯一索引

    在创建和使用索引时经常看到“唯一索引（Unique Index）”这个术语。那么，什么是唯一索引？所谓唯一索引，它是指索引值唯一（没有重复索引值）的一类索引，如果索引值不唯一，则为非唯一索引。当对某一字段创建了唯一索引后，就不能对该字段输入有重复的字段值。在创建表时，如果设置了主键，那么SQL Server就会自动建立一个唯一索引。用户也可以在表创建以后再对字段创建唯一索引。
    在创建唯一索引以后，SQL Server会在每次执行更新操作时都会自动检查是否有重复的索引值，如果有则插入操作将被回滚，并同时由数据库引擎显示错误消息。
    唯一索引与聚集索引和非聚集索引有什么联系呢？
•    答案是“没有”。它们只是从不同的角度对索引进行分类罢了，就像人可以分为男人和女人、也可以分为中国人和非中国人的道理一样。从索引数据存储的角度来看，索引可以分为聚集索引和非聚集索引；从索引值是否可以重复的角度看，索引又可以分为唯一索引和非唯一索引。唯一索引既可以是聚集索引，也可以是非聚集索引。

8.2.3 组合索引

    组合索引是指使用两个或两个以上的字段来创建的索引。它与聚集索引等也没有必然的联系，只是分类的根据不同罢了。
    前面已经指出，在创建索引时涉及的字段越少越好，那么为什么还要允许创建组合索引呢？
     原因：有时候需要唯一索引，但利用一个字段不能创建唯一索引，这就需要增加字段的方法实现唯一索引。
    在表SC中没有哪一字段可以对其创建唯一索引，但把字段s_no和字段c_name组合起来就可以创建唯一索引，因为不会出现同一个学生选修两门或两门以上相同课程的情况。

•     8.3 创建索引

8.3.1 聚集索引

    聚集索引的最大优点是，当对带有聚集索引的字段进行查询时，会产生很高的查询效率。这是因为，索引值相近的字段值在物理磁盘上也相互靠近，这样就可以大大减少磁盘转动所需要的读盘时间。
    注意，对一个表或视图只能创建一个聚集索引。
    带CLUSTERED选项的CREATE INDEX语句用于创建聚集索引，其语法格式如下：

CREATE CLUSTERED INDEX index_name
ON table_name(col_list [DESC | ASC]);

 

  index_name表示要设定的索引名，table_name表示表名，col_list为字段列表；如果选择了DESC则表示创建降序索引，如果选择了ASC（默认选项）则表示创建升序索引。


【例8.1】 创建一个聚集索引。
       本例中，先创建一个空的数据表——表student2，此表与表student的区别是没有为它创建主健，创建代码如下：

CREATE TABLE student2(
    s_no char(8)         ,
    s_name char(8)         NOT NULL,
    s_sex char(2)         CHECK(s_sex = '男' OR s_sex = '女’),
    s_birthday smalldatetime    CHECK(s_birthday>='1970-1-1' AND s_birthday<='2000-1-1’),
    s_speciality varchar(50)     DEFAULT  '计算机软件与理论’,
    s_avgrade numeric(3,1)     CHECK(s_avgrade >= 0 AND s_avgrade <= 100),
    s_dept varchar(50)                       DEFAULT  '计算机科学系'
);

 

    表student2的特点是没有任何索引。用下列INSERT语句插入数据：

INSERT student2 VALUES('20170205','贾簿','男','1994-09-03','计算机软件与理论',43.0,'计算机系');
INSERT student2 VALUES('20170206','李思思','女','1996-05-05','计算机应用技术',67.3,'计算机系');
INSERT student2 VALUES('20170207','蒙恬','男','1995-12-02','大数据技术',78.8,'大数据技术系');
INSERT student2 VALUES('20170208','张宇','女','1997-03-08','大数据技术',59.3,'大数据技术系');
INSERT student2 VALUES('20170201','刘洋','女','1997-02-03','计算机应用技术',98.5,'计算机系');
INSERT student2 VALUES('20170202','王晓珂','女','1997-09-20','计算机软件与理论',88.1,'计算机系');
INSERT student2 VALUES('20170203','王伟志','男','1996-12-12','智能科学与技术',89.8,'智能技术系');
INSERT student2 VALUES('20170204','岳志强','男','1998-06-01','智能科学与技术',75.8,'智能技术系');

 

    这些数据与表student中的数据一样，只是插入顺序不同。当用下列SELECT语句查看表student2时，可以看到如图8.4所示的内容。

SELECT * FROM student2;

 

 
 从图8.4中可以看到，表student2中记录的物理顺序是数据实际插入的先后顺序。

    接着对表student2的s_no字段创建降序聚集索引，索引名为myIndex1：

CREATE CLUSTERED INDEX myIndex1        -- 创建聚集索引myIndex1
ON student2(s_no DESC);

 

    执行上述代码，然后利用SELECT语句查看这时表student2的内容，结果如图8.5所示。

    可以看到，这时表student2中的记录已经按s_no降序排列，这种顺序也是表student2中记录在磁盘上的物理顺序。今后在对表student2插入数据时，将按照索引myIndex1在字段s_no上定义的顺序把数据记录插入到相应的位置（而不一定位于表的最后位置）；或者说，在创建聚集索引后，每一次插入数据，系统都会对数据重新进行排序（这个过程需要时间）。因此，对那些经常插入或更新索引字段值的数据表，尽量不要创建聚集索引。

 

8.3.2 非聚集索引

    与聚集索引不同，对一个数据表可以创建多个非聚集索引。理论上，我们可以对任何一列或若干列的组合创建非聚集索引，只要总数不超过249个。但对于索引视图，只能为已定义唯一聚集索引的视图创建非聚集索引。
    带NONCLUSTERED选项的CREATE INDEX语句可用于创建非聚集索引，但NONCLUSTERED选项可以省略。也就说，在默认情况下CREATE INDEX语句将创建非聚集索引。其语法格式如下：

CREATE [CLUSTERED] INDEX index_name
ON table_name(col_list [DESC | ASC]);

 

【例8.2】 创建一个非聚集索引。
        一般情况下，对于表student，按姓名（s_name）查询学生信息是常常使用的查询方式，因此对列s_name创建一个非聚集索引，这对提高应用系统的查询效率有着重要的作用。下列代码则用于对表student中的列s_name创建一个名为myIndex2的非聚集索引：

CREATE NONCLUSTERED INDEX myIndex2  -- 创建非聚集索引myIndex2
ON student(s_name);

 

上述代码中，关键字NONCLUSTERED可以省略。
        默认情况下，在查询时一个索引是否被运用是由查询优化器决定的，但我们可以强制引用指定的索引来辅助查询。




【例8.3】 强制引用指定的非聚集索引。
    在对列s_name创建了索引myIndex2以后，可以通过WITH子句强制查询优化器引用索引myIndex2：

SELECT *
FROM student
WITH (INDEX (myIndex2))   -- 强制引用索引myIndex2
WHERE s_name='刘洋';

 

8.3.3 唯一索引

    唯一索引的创建是使用带UNIQUE选项的CREATE INDEX语句来实现，其语法格式如下：

CREATE UNIQUE INDEX index_name
ON table_name(col_list [DESC | ASC]);

 

【例8.4】 创建一个唯一非聚集索引。
在本例中，对表student的s_avgrade列创建一个唯一非聚集索引，使索引列降序排序。相应代码如下：

CREATE UNIQUE INDEX myIndex3      -- 创建唯一非聚集索引myIndex3
ON student(s_avgrade DESC)

 

    最理想的情况是对空表创建唯一索引。但是也可能出现对非空表创建唯一索引的情况。如果表非空且待创建索引的列存在重复列值，则不能创建此唯一索引。
    当对某一列创建唯一索引后，在插入新数据时就不允许在此列上出现重复列值，否则将产生异常，导致插入操作失败。
    有时候基于一个字段创建的索引难以满足实际需要，而需要基于多个字段的组合才能创建符合要求的索引。也就是说，在某些情况下需要利用多个字段来创建一个索引，这就是组合索引。
  【例8.5】 创建唯一组合索引的实例。
   表SC包含三个字段：s_no,c_name,c_grade，其中任意一个字段都不能唯一标识表中的记录。但s_no和c_name的组合则可以唯一标识表中的每一条记录。因此，可以基于这两个字段创建一个名为myIndex5的唯一组合索引。代码如下：

CREATE UNIQUE INDEX myIndex5
ON SC(s_no DESC, c_name ASC);

 

    该唯一组合索引是一种非聚集索引，因为选项NONCLUSTERED是默认的。我们也可以创建属于聚集索引的组合索引，只需在 CREATE INDEX语句中使用CLUSTERED选项即可。

•     8.4 查看和删除索引 

 

8.4.1 查看索引

    利用系统存储过程sp_helpindex可以获得一张数据表或视图上的所有索引。其语法如下：

sp_helpindex [ @objname = ] 'name‘;

 

       其中，参数[ @objname =] 'name'用于指定当前数据表或视图的名称。该存储过程结果集的形式输出指定数据表或视图上的所有索引。结果集包含三个列：
index_name：返回索引名；
index_description：返回索引说明，如是否是聚集索引、唯一索引等信息，其中包括索引所在的文件组；
index_keys：返回对其生成索引的列。

     【例8.6】  查看数据表上所有索引的实例。
     本例是查看数据表student上的所有索引，代码如下：

sp_helpindex 'student';

 

     执行此存储过程，结果如图8.6所示。

    图8.6中，被降序索引的列在结果集中用减号(-)标识，即如果一个列名后缀减号(-)，则表示该列被降序索引；当列出被升序索引的列（这是默认情况）时，只带有该列的名称。

    当前数据库中的所有索引都保存在目录视图sys.indexes中，因此通过查询该表可以获得当前数据库中所有索引的详细信息。

   【例8.7】 查看当前数据库的所有索引。

      本例用于查看当前数据库的所有索引，相应的SELECT语句如下：

USE MyDatabase;
    GO
    SELECT * FROM sys.indexes;

 

8.4.2 删除索引

    当一个索引不再需要的时候，可用DROP INDEX语句将之删除。该语句最简单的语法格式可以表示为：

DROP INDEX index_name ON table_name;

 

    也可以将表名前缀，写成：

DROP INDEX table_namet.index_name;

 

  【例8.8】 删除索引实例。
    本例是将表student中的索引myIndex2删除，相应代码如下：

DROP INDEX myIndex2 ON student;
    --也可以写成：
DROP INDEX student.myIndex2

 

    另外，在创建表的时候，可能设置了PRIMARY KEY或UNIQUE约束，这时会自动生成与约束名同名的索引。这种索引的删除不能使用DROP INDEX语句来完成，但可以使用ALTER TABLE DROP CONSTRAINT语句将其删除。


【例8.9】 删除定义PRIMARY KEY约束时创建的索引。
    删除表student中定义PRIMARY KEY约束时创建的索引    PK__student__2F36BC5B24DD5622，实现代码如下：

ALTER TABLE student
DROP CONSTRAINT PK__student__2F36BC5B91406173

 

    •     8.5 视图概述 

8.5.1 视图的概念

    视图在视觉上也是一张由行和列构成“数据表”，但它不是真正的数据表，而是一张虚拟的数据表（虚表）。实际上，视图在本质上是一个命令集，当“打开”它时将由这些命令从一张或多张数据表中抽取数据，这些数据便在视觉上构成了一张“数据表”，而这些被从中抽取数据的数据表通常称为视图的基本表或基础表（简称基表）。所以，视图也可以看成是一张或多张数据表的一个数据窗口，它是动态生成的。

    视图离不开它的基表，它是按照某种条件和要求对基表进行筛选的结果；离开了基表，视图是没有意义的。基表中数据的变化将实时反映到视图当中，针对视图的操作实际上是对基表进行操作。

    图8.7给出了由一张表生成视图的示意图。

 

8.5.2 视图的优缺点

    在视觉上，视图和数据表几乎是一模一样，具有字段、记录和数据项，也可以进行查询、更新等操作。但视图毕竟不是数据表，其所包含的数据并不以视图结构存储在数据库中，而是存储在视图的基表中。因此，在对视图进行操作时会受到许多限制。正是这些限制为数据库的安全提供了一种保障机制。再加上其他的相关机制，使得视图较数据表具有独特的优势。这些优势主要体现在以下几个方面：

 （1）提供个性化的数据显示功能
       数据表的创建一般是出于对系统的设计与实现来考虑的，主要是面向系统设计人员和程序编写人员，而不是面向用户。但是，对一个用户而言，他感兴趣的可能是一张或多张数据表中的部分数据。视图则为用户能够看到他们感兴趣的特定数据提供了一种有效的窗口观察机制。


 （2）简化数据的操作
        用户感兴趣的数据可能分散在多张数据表中，将这些用户感兴趣的数据检索出来，可能需要多种操作。即使这些数据在同一张表中，把它们检索出来也可能需要一些复杂而繁琐的操作。如果将这些经常使用的操作（如连接、投影、联合查询和选择查询等）定义为视图，那么用户每次对特定的数据执行进一步操作时，不必重复指定所有条件和限定。
    假如出于某种实际应用要求，需要多次、重复执行某一个复合查询，则最好将该复合查询定义为一个视图，以后只需对该视图查询即可，从而简化对数据的访问方式。此外，也简化了用户权限的管理操作，因为只需授予用户使用某些视图的权限，而不必指定用户只能使用表的特定列。


（3）自组织数据
        视图允许用户以不同的方式查看数据，即使他们同时使用相同的数据时也是如此。

（4）组合分区数据
        用户可以将来自不同数据表的两个或多个查询结果集定义为一个视图，称为分区视图。通过使用分区视图，对用户来说他操作的是一张“表”，而不是多张表，可以对它像一张表一样进行查询等操作，而无需对基表进行操作。

（5）便于数据共享
        对一个基表可以定义多个用户视图。用户可以通过使用自己的视图来实现对基表的操作，从而可以轻而易举地实现同一张表为多个用户服务的目的。

（6）提高安全性
        用户只能看到视图中所定义的数据，而看不到基表中的其他数据以及表的其他信息。这种机制可以增强数据的安全性。
       

       
 但视图也有其自身的缺点，这主要体现在：
（1）相对低效
        视图在本质上是一些命令的集合。在对视图进行操作的时候，除了执行键入的SQL语句中的查询或更新外，还要需要执行视图本身所包含命令。因此，这在一定程度上降低了查询效率。

（2）有限的更新操作
        视图主要是用于查询，对更新操作受到很多的限制。目前，可更新的视图要求其基表是单表（准确地说，一次更新操作不能同时涉及到两个或两个以上的基表），并且用于定义视图的SELECT语句不能包含GROUP BY或HAVING子句。另外，如果SELECT语句中包含了聚集函数、计算列或DISTINCT子句，相应的视图也不能更新。

  •     8.6 视图的创建与删除

8.6.1 创建视图

    视图是用CREATE VIEW语句来创建，其简要的语法如下：

CREATE VIEW view_name [(column [,...n])]
AS select_statement;

 

     其中，view_name表示要创建的视图的名称；select_statement为SELECT查询语句；column [,...n]表示视图中字段的名称，如果未指定 column，则视图的字段名将与 SELECT查询中的字段名相同。

【例8.10】 创建与基表完全相同的视图。
       本例中，将创建与基表student“拥有”完全相同内容的视图myView1。当然这种视图没有什么实际意义，但它是我们认识视图的起点。代码如下：

CREATE VIEW myView1
AS
SELECT * FROM student;

 

     执行上述代码，将在当前数据库中创建名为myView1的视图。使用下列SELECT语句可以查询该视图中的数据，如图8.8所示：

SELECT * FROM myView1

 

可以看到，视图myView1中的数据与表student中的数据是完全一样的

 

 

【例8.11】 创建包含基表中部分数据的视图，并给视图设定新的中文字段名。
    实际上，视图的重要应用之一是充当基表的数据窗口，透过这个窗口我们可以看到我们感兴趣的数据。本例中，对表student中平均成绩（s_avgrade）在60或60分以上的学生，将其学号（s_no）、姓名（s_name）、专业（s_speciality）、平均成绩（s_avgrade）和系别（s_dept）定义为视图myView2。相应代码如下：

CREATE VIEW myView2(学号,姓名,专业,平均成绩,系别)
AS
SELECT s_no, s_name, s_speciality, s_avgrade, s_dept
FROM student
WHERE s_avgrade>=60;

 

    执行上述语句后，通过执行下列SELECT语句来查询myView2中的数据，结果如图8.9所示。

SELECT * FROM myView2

 

--注意，视图myView2包含的字段名为“学号”、“姓名”、“专业”、“平均成绩”和“系别”，
--而s_no, s_name, s_speciality, s_avgrade, s_dept不是该视图的字段名了，
--因此下列的SELECT语句都是错误的：
SELECT s_no, s_name, s_speciality, s_avgrade, s_dept FROM myView2
SELECT s_no, s_name FROM myView2
--而下列的SELECT则是正确的：
SELECT 学号,姓名,专业,平均成绩,系别 FROM myView2

 

【例8.12】 创建带有两个基表的视图。
    如果一个视图带有两个基表，这说明在其CREATE VIEW语句中运用了连接查询，其中涉及到两张数据表。下面是创建这种视图的SQL代码：

CREATE VIEW myView3
AS
SELECT student.s_no 学号, s_name 姓名, s_sex 性别, s_speciality 专业, s_dept 系别, c_name 课程名称, c_grade 课程成绩
FROM student, SC
WHERE student.s_no = SC.s_no

 

       实际上，在很多情况下，视图可以像数据表一样使用。比如，可以用视图去更新另外一个数据表，实现数据初始化等功能。


【例8.13】 用视图更新数据表。
    在例5.30中，为了用表SC中的课程成绩（c_grade）去更新（初始化）表student中的平均成绩s_avgrade字段，我们定义了一个临时数据表tmp_table。实际上，可以用视图来代替这个数据表，较少存储空间的消耗。方法如下：
    首先，通过按学号分组的方法求各个学生的平均成绩，并将其学号和平均成绩定义称为视图tmp_view

CREATE VIEW tmp_view
AS
SELECT s_no, AVG(c_grade) as s_avgrade
FROM SC
GROUP BY s_no

 

其次，表student和视图tmp_view的联系是基于字段s_no的（1:1）联系，因此我们可以用视图tmp_view中的平均成绩字段s_avgrade去更新表student中的平均成绩字段s_avgrade：

UPDATE student
SET s_avgrade = b.s_avgrade
FROM  student as a
JOIN tmp_view as b
ON a.s_no = b.s_no

 

 【例8.14】 创建带参数的“视图”。
    创建一个“视图”，要求其能够显示平均成绩在指定分数段内的学生信息。分数段由两个参数来定义，这样我们可以利用函数来实现这个功能，函数的定义代码如下：

CREATE FUNCTION myFunView(@a float, @b float) RETURNS TABLE
AS
  RETURN
  (
    SELECT *
    FROM student
        WHERE s_avgrade >= @a and s_avgrade <= @b
  );

 

    执行上述代码，创建函数myFunView。此后，可以像视图一样查询该函数，但要带两个参数。比如，查询平均成绩在60到90之间的学生，相应的SELECT代码如下：

SELECT * FROM myFunView(60,90)

 

    也可以像视图一样，对该函数进行更新操作。比例，将平均成绩在60到90之间的学生的系别（s_dept）全部改为“智能技术系”，相应代码如下：

UPDATE myFunView(60,90) SET s_dept= '智能技术系‘

 

   注意，这种带参数的“视图”并不是视图，而是一种函数，但它具有视图的大部分特性。

8.6.2 更新视图

     更新视图是指通过视图更新其基表中的数据，这跟更新数据表的方法一样，只需将视图名当作数据表名即可。

 【例8.15】 将视图myView2中的平均成绩全部设置为80分。
      相应代码如下：

UPDATE myView2
SET 平均成绩 = 80;

 

     执行上述语句后，视图myView2中的平均成绩全部变为80。实际上，这个修改操作是修改表student中字段s_avgrade的字段值，将其全部改为80分。
      但由于视图不是真正的数据表，对其进行的更新操作会受到诸多的限制。最明显的一个限制是：任何针对视图的修改操作不能同时影响到两个或两个以上的基表。

 【例子】 对于视图myView3，下列的UPDATE语句是错误的：
      

UPDATE myView3
              SET 性别 = '女', 课程成绩 = 99

 

        这是因为视图myView3中的“性别”和“课程成绩”分别来自表student中的字段s_sex和表SC中的字段c_grade，即该UPDATE语句会影响到表student和SC，因此该UPDATE语句是错误的。
      但下面两条UPDATE语句都是可执行的，因为它们各自都只影响到一个数据表：
     

UPDATE myView3 SET 性别 = '女’
        UPDATE myView3 SET 课程成绩 = 99

 

       对视图的数据插入和删除操作与对数据表的数据插入和删除操作也分别一样，但前提是不能违反表的完整性约束、一个操作不能影响到两个或两个以上的基表。

8.6.3 删除视图

    视图的删除可用DROP VIEW语句来实现，其语法如下：

DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]

 

     其中，view_name为要删除的视图的名称，schema_name为视图所属架构的名称。
 
【例8.16】  删除视图的实例。
     删除视图myView1和myView2，可用下面的DROP VIEW语句来完成：

DROP VIEW myView1, myView2;

 

       需要说明的是，使用DROP VIEW语句删除视图时，并没有删除其基表中的数据，而只是删除视图的定义代码及其与其他对象的关系。所以，删除视图时不必担心会删除数据表中的数据。

•     8.7 查看视图

8.7.1 视图的定义代码

如果视图的定义代码没有被加密，我们可以查看它的定义代码。

【例8.17】  查看视图的定义代码。
查看视图myView1的定义代码，可以利用系统存储过程sp_helptext来实现。其实现代码如下：

sp_helptext myView1;

 

执行上述存储过程，结果如图8.10所示。

 

8.7.2 视图的结构信息

    视图的结构信息等可以利用系统存储过程sp_help来查看。

 【例8.18】 查看系统的结构信息。
本例将查看视图myView1，使用系统存储过程sp_help的代码如下：

sp_help myView1;

 

执行上述代码，结果如图8.11所示。

 

8.7.3 数据库中的视图

    当前数据库中所有视图的信息都包含在系统目录视图sys.views中，因此通过查询该视图可以获得当前数据库中所有视图的信息。


【例8.19】 查看当前数据库中所有用户定义的视图。
      将数据库MyDatabase设置为当前数据库，然后查询其中包含的所有用户定义的视图的名称。实现代码如下：

USE MyDatabase;
GO
SELECT name '视图名称（当前数据库）' FROM sys.views;
GO

 

执行上述代码，结果如图8.12所示。

    如果要查看所有数据库中的视图，则可利用下列的语句来完成：

SELECT name '视图名称（所有数据库）' FROM sys.all_views;