《精通SQL》04

SQL 第 4 章 索引与视图的创建 和表一样，索引和视图也是数据库中的重要对象。本章将介绍索引、视图的概念及其创 建与管理。 4.1 索引的基础知识 用户对数据库最频繁的操作是数据查询。一般情况下，数据库在进行查询操作时，需要 对整个表进行搜索。当表中的数据很多时，搜索数据就需要很长的时间，这就造成了服务器 的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。 4.1.1 索引的概念 索引是一个单独的、物理的数据库结构，是数据库的一个表中所包含的值的列表，其中 注明了表的各个值所在的存储位置。索引是依赖于表建立的，提供了编排表中数据的方法。 实际上，一个表的存储是由两部分组成的，一部分用来存放表的数据页面，另一部分存 放索引页面，索引就存放在索引页面上。通常，索引页面相对于数据页面来说小得多。当进 行数据检索时，系统先搜索索引页面，从中找到所需数据的指针，再通过指针从数据页面中 读取数据。 从某种程度上，可以把数据库看作一本 书 关于书的成语关于读书的排比句社区图书漂流公约怎么写关于读书的小报汉书pdf ，把索引看作书的目录，通过目录查找书中的 信息。显然，与没有目录的书相比，显得方便和快捷。 4.1.2 索引的结构 一般的数据库如 SQL Server、Oracle 等，按存储结构的不同将索引分为两类，簇索引 （ClusteredIndex）和非簇索引（Nonclustered Index）。 1．簇索引 簇索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即簇索引 62 第 4 章 索引与视图的创建 SQL 与数据是混为一体的，它的叶节点中存储的是实际的数据。 由于簇索引对表中的数据一一进行了排序，因此用簇索引查找数据很快。但由于簇索引 将表的所有数据完全重新排列了，所需要的空间也就特别大，大概相当于表中数据所占空间 的 120%。 注意 表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。 例如，在学生信息表中，为学号字段建立簇索引，索引的典型结构如图 4.1 所示。 学生信息表 001 021 051 学号 索引 001 020 …… 学号 021 050 …… 051 058 …… 图 4.1 簇索引的结构 2．非簇索引 非簇索引具有与表的数据完全分离的结构，使用非簇索引不必将物理数据页中的数据按 列排序。非簇索引的叶节点中存储了关键字的值和行定位器。行定位器的结构和存储内容取 决于数据的存储方式。如果数据是以簇索引方式存储的，则行定位器中存储的是簇索引的索 引键；如果数据不是以簇索引方式存储的，则行定位器存储的是指向数据行的指针，这种方 式又称为堆存储方式（Heap Structure）。非簇索引将行定位器按关键字进行排序，这个顺序 与表的行在数据页中的排序是不匹配的。 由于非簇索引使用索引页存储，因此比簇索引需要更多的存储空间，且检索效率较低。 但一个表只能建一个簇索引，当用户需要建立多个索引时，就需要使用非簇索引了。从理论 上讲，一个表最多可以建 249 个非簇索引。 同样，在学生信息表中，为学号字段建立非簇索引，索引的典型结构如图 4.2 所示。 学生信息表 001 002 004 学号 索引 003 001 003 002 004 …… …… …… …… …… …… …… …… 图 4.2 非簇索引的结构 63 4.2 索引的创建与销毁 SQL 4.2 索引的创建与销毁 在 SQL 中，创建索引是由 CREATE INDEX 关键字实现的。但在不同的数据库管理系统中， CREATE INDEX 语句有不同形式的扩展。索引的销毁则是由 DROP INDEX 关键字实现的。 4.2.1 基本创建语法 创建素引的基本关键字为 CREATE INDEX，在其后要指明创建的索引的名称，并需要指 明表的名字及创建索引的列。语法如下。 CREATE INDEX index_name ON table_name(column_name1, [column_name2], ...); 每个索引必须有惟一的名字。ON 关键字后面为创建索引的表的名字，在括号内列出索 引包含的列（当然，可以为多列）。 在不同的数据库管理系统中，创建索引语句有着不同形式的扩展。如在 SQL Server 中， CREATE INDEX 语句创建索引可以有如下的形式。 CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH { PAD_INDEX | FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING | STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB} [ ,...n] ] [ ON filegroup ] 在 Oracle 中，CREATE INDEX 语句创建索引可以有如下的形式。 CREATE INDEX [schema.]index ON { [schema.]table (column [!!under!!ASC|DESC] [, column [!!under!!ASC|DESC]] ...) | CLUSTER [schema.]cluster } [INITRANS integer] [MAXTRANS integer] [TABLESPACE tablespace] [STORAGE storage_clause] [PCTFREE integer] [NOSORT] 在 Informix 中，CREATE INDEX 语句创建索引可以有如下的形式。 CREATE [UNIQUE | DISTINCT] [CLUSTER] INDEX index_name ON table_name (column_name [ASC|DESC],column_name [ASC|DESC]...) 其中用到的几个主要关键字含义如下。 UNIQUE （DISTINCT）：惟一性索引，不允许表中不同的行在索引列上取相同值。若 已有相同值存在，则系统给出相关信息，不建此索引。 CLUSTERED/ NONCLUSTERED：聚集和非聚集索引，若为 CLUSTERED，则为聚集 索引，即表中元组按索引项的值排序，并聚集在一起。一个基本表上只能建一个聚集索 引。NONCLUSTERED表示创建的索引为非聚集索引。缺省时，创建的为非聚集索引。 64 第 4 章 索引与视图的创建 SQL ASC/DESC：索引表中索引值的排序次序，缺省为ASC（正序排列）。 关于其他一些关键字和语句的含义，读者可参阅各数据库系统的参考手册。所有这些创 建形式有一些共同点，即都包含了基本的创建语句： CREATE INDEX index_name ON table_name (column_name, ...) 本章介绍的索引操作就是围绕这个基本创建语句展开的。 4.2.2 本章实例用到的实例表 本章介绍索引和视图操作实例时，共用到了3个实例表，分别为学生信息表（StudentInfo）、 招生信息表（RecruitInfo）和院系信息表（Department）。分别如下。 1．学生信息表（StudentInfo） StudentInfo 表包含学号（sno）、姓名（sname）、性别（sex）、生源（address）、系号（dno） 几个字段信息。该表的结构及数据如表 4-1 所示。 表 4-1 StudentInfo 表的结构及数据 学号（sno） 姓名（sname） 性别（sex） 生源（address） 系号（dno） 001 张平 女 湖南 2 002 李山 男 北京 4 003 王彤 女 湖北 2 004 张伟 男 浙江 1 005 高守传 男 山东 3 006 刘小静 女 山东 4 007 张勇 男 河北 6 008 刘红 女 河南 5 009 吴军 男 山西 4 010 张大山 男 陕西 7 该表的 SQL 生成代码如下。 CREATE TABLE StudentInfo ( sno char (3) NOT NULL , sname char (8) NOT NULL , sex char(2) NOT NULL , address char(8) NOT NULL , dno int NOT NULL ) INSERT INTO StudentInfo VALUES('001','张平','女', '湖南', 2) INSERT INTO StudentInfo VALUES('002','李山','男', '北京', 4) …… 2．招生信息表（RecruitInfo） RecruitInfo 表包含生源（address）、录取分数（score）和招收人数（snum）3 个字段，其 65 4.2 索引的创建与销毁 SQL 结构及数据如表 4-2 所示。 表 4-2 RecruitInfo 表的结构及数据 生源（address） 录取分数（score） 招收人数（snum） 北京 560 220 湖南 648.5 65 湖北 654 85 山东 650 80 浙江 638 76 河南 629.5 72 河北 625 58 山西 631 55 陕西 635 62 该表的 SQL 生成代码如下。 CREATE TABLE RecruitInfo ( address char(10) NOT NULL, score foat NOT NULL, snum int NOT NULL ) INSERT INTO RecruitInfo VALUES(‘北京’,560, 220) INSERT INTO RecruitInfo VALUES(‘湖南’,648.5, 65) …… 3．院系信息表（Department） Department 表包含系号（dno）、系名（dname）和招收人数（dnum）3 个字段，其结构 及数据如表 4-3 所示。 表 4-3 Department 表的结构及数据 系号（dno） 系名（dname） 招收人数（dnum） 1 计算机工程系 220 2 汽车系 80 3 机械工程系 120 4 电子工程系 180 5 工程物理系 40 6 应用数学系 50 7 材料工程系 60 该表的 SQL 生成代码如下。 CREATE TABLE Department ( dno int NOT NULL, 66 第 4 章 索引与视图的创建 SQL dname char(20) NOT NULL, dnum int NOT NULL ) INSERT INTO Department VALUES(1,'计算机工程系', 220) INSERT INTO Department VALUES(2,'汽车系', 80) …… 4.2.3 创建简单的非簇索引 当一个表的记录数很大时，为查询符合条件的记录，扫描整个表要花费很长时间。如在 StudentInfo 表中查询学生“吴军”的记录，查询语句如下。 SELECT * FROM StudentInfo WHERE sname=’吴军’ 假如 StudentInfo 表中有 100000 条记录，为查询“吴军”学生的记录，就需要用 WHERE 条件对 100000 条记录逐一进行核对，显然效率低下。而此时如果对 sname 字段建立了索引， 该索引如同对 sname 字段内的所有记录进行了某种排序，通过分析姓名“吴军”，很快就会 定位到它在表中的记录位置，从而提高了检索效率。 下面通过一个实例介绍一下非簇索引的创建和使用过程。该实例将为 StudentInfo 表中的 姓名（sname）字段创建非簇索引 Name_Index。 1．索引的创建 索引创建前，首先查看学生信息表（StudentInfo）中的数据。 实例 1 查询创建索引前 StudentInfo 表中的数据 实例代码： SELECT * FROM StudentInfo 运行结果如图 4.3 所示。 图 4.3 学生信息表 实例 2 为 StudentInfo 表中的 sname 字段创建非簇索引 实例代码： CREATE INDEX Name_Index 67 4.2 索引的创建与销毁 SQL ON StudentInfo (sname ) 执行该代码，系统会提示索引创建成功。在非簇索引中，DBMS 只对创建索引的列的键 值进行排序（升序），而索引的表行不排序。 实例 3 查询索引的键字值 实例代码： SELECT sname FROM studentinfo 运行结果如图 4.4 所示。 图 4.4 索引的键字值 可见，索引的键值按照升序进行了排序。 实例 4 查询创建索引后 StudentInfo 表中的所有数据 实例代码： SELECT * FROM StudentInfo 运行结果如图 4.5 所示。 图 4.5 创建索引后的学生信息表 与创建索引前的查询结果没有什么变化。这是因为这里创建是非簇索引。它并没有改变 表中数据存放的物理位置。 2．非簇索引的使用 由于大多数数据库系统具有使用多个索引的能力，如 SQL Server，当在表上创建一个或 68 第 4 章 索引与视图的创建 SQL 多个索引后，SQL Server 的查询优化器会自动决定在查询执行期间使用哪个索引。 为了在 SQL Server 中使用索引，WHERE 子句中的列之一必须是索引所在列，对于本例 就是 sname 列。 当然也可以不让 SQL Server 的查询优化器自动决定索引，而是强制使用某种索引。语法 如下。 SELECT column1,column2,…… FROM table_name WITH (INDEX (index_name)) WHERE condition 其中 index_name 指明了要使用的索引名字。 实例 5 强制使用非簇索引查询表 强制使用 Name_Index 索引，查询学生信息表（StudentInfo）中的数据，代码如下。 SELECT * FROM StudentInfo WITH (INDEX (Name_Index)) 运行结果如图 4.6 所示。 图 4.6 使用强制索引的检索结果图 对比使用索引前后数据库表中的数据，可以发现，数据按照 sname 字段升序的方向重新 排列。因为这里强制使用了 Name_Index 索引，而该索引中，sname 字段是按照记录升序的方 向排列的。 说明 对于查询表中的所有记录信息，使用索引是毫无意义的。这里之所以这么做，是为了演示索引的作用效果，下同。 在 SQL 的 SELECT 语句中，使用 ORDER BY 关键字同样可以对查找结果进行排序。 实例 6 使用 ORDER BY 关键字对查找结果排序 实例代码： SELECT * FROM StudentInfo ORDER BY sname 运行结果如图 4.7 所示。 69 4.2 索引的创建与销毁 SQL 图 4.7 按照姓名排序的结果 说明 有关 Select语句，在后面的章节中会有详细的介绍。 虽然从表面上看，使用 SELECT……ORDER BY 语句得到了相同的结果，但是，二者从 本质上有很大的区别。ORDER BY 关键词在每次查询数据时，都要对数据进行排序；而创建 索引后，数据库系统实际上创建了一个索引结构体，用户每次使用查询数据时，都使用相同 的索引结构，从而节约了时间。 注意 当数据库表被删除时，和它相关的所有索引都将被删除。 4.2.4 多字段非簇索引的创建 SQL 允许用户在一个表中，在两个或多个字段上创建多字段索引，这种索引又被称为复 合索引。有时，建立这类索引在实际应用中也是必要的。 例如，在学生选课表中，经常要查询某某同学（如张三）选修的某门课程（如数学）的 成绩。这时，如果只在一个字段上建立索引，则查询效率要低些。比如，只在学生姓名字段 上建立索引，执行查询时，系统将利用索引找出张三同学的所有选课记录，然后再对课程逐 一扫描，找到课程为数学的记录。而如果只在课程字段上建立索引，执行查询时，系统将利 用索引找出所有选修数学课程的学生记录，再对这些记录的姓名字段进行逐一扫描，找到姓 名为张三的记录。这时，为了提高查询效率可以为同时为姓名和课程两个字段建立一个索引。 说明 由于学生数一般远远大于课程数，所以如果只在一个字段上建立索引，以建姓名字段为好。 实例 7 创建并强制使用多字段索引检索表 为 StudentInfo 表中的性别（sex）和姓名（sname）字段创建索引 SexName_Index。 实例代码： CREATE INDEX SexName_Index ON StudentInfo (sex , sname ) 强制使用 SexName_Index 索引，查询 StudentInfo 表中的所有记录。 SELECT * 70 第 4 章 索引与视图的创建 SQL FROM StudentInfo WITH (INDEX (SexName_Index)) 运行结果如图 4.8 所示。 图 4.8 使用强制索引检索结果图 可以发现，在创建的索引中，sex 字段的优先级要高于 sname 字段。在创建多字段索引 时，各字段的排列顺序决定了其优先级，排列越靠前，具有越高的优先级。 4.2.5 使用 UNIQUE 关键字创建惟一索引 惟一索引是指不允许在两行中存在相同的索引值。惟一索引可以拥有一行或者多行。如 果用户试图使用 INSERT 或 UPDATE 语句，在拥有惟一索引的数据中生成一个重复的值，那 么 INSERT 或者 UPDATE 就会被终止，SQL 服务器会生成一个错误信息。 实例 8 创建并强制使用惟一索引检索表 为 StudentInfo 表中的学号（sno）字段创建惟一索引 Sno_Index，且指定降序排序。 CREATE UNIQUE INDEX Sno_Index ON StudentInfo (sno DESC ) 强制使用 Sno_Index 索引，查询 StudentInfo 表中的所有记录。 SELECT * FROM StudentInfo WITH (INDEX (Sno_Index)) 运行结果如图 4.9 所示。 图 4.9 使用强制索引检索所有的记录 71 4.2 索引的创建与销毁 SQL 如果用户要向 StudentInfo 表中插入具有相同学号的学生信息，如： insert StudentInfo (sno, sname, sex, address, dno) values ('006','刘备','男','河南',1) SQL Server 会报错，在查询分析器的 Result 窗格将显示如下信息。 Cannot insert duplicate key row in object 'StudentInfo' with unique index 'Sno_Index'. The statement has been terminated. 注意 当创建惟一索引时，应确保被索引的列不允许 NULL值。例如，SQL Server将 NULL 也看作是一个值，因而如果创建惟一索引的列允许 NULL 值，假定原先该列不存在 NULL值，那么向其中插入 NULL值时，第一个是合法的，第二个就会失败，因为两 个 NULL值被视为违背惟一性原则。 当然，创建惟一性索引的前提是，创建索引的列中已有的记录本身没有重复的值。否则， 系统会报错，创建失败。 实例 9 具有重复值字段的惟一性索引的创建 为 StudentInfo 表中的 address 字段创建惟一索引 Address_Index。 CREATE UNIQUE INDEX address_Index ON StudentInfo (address ) 运行结果：SQL Server 数据库系统会报错，在查询分析器的 Result 窗格将显示如下信息。 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 4. Most significant primary key is '山东 � '. The statement has been terminated. 这是因为在 StudentInfo 表的 address 列中，其值不是惟一的（包含两个“山东”记录）， 因此对其创建惟一性索引将导致创建失败。 4.2.6 使用 CLUSTERDE 关键字创建簇索引 与非簇索引不同，簇索引改变了表中数据存放的物理位置。在带有簇索引的表中，行是 以索引顺序存放的。即簇索引不仅对索引中的键字值进行排序，而且对表中的行排序，以便 使其与索引的排序相匹配。使用簇集索引主要有下面几点优势： 使用簇集索引的表将占用最小的磁盘空间。因为 DBMS 在插入新行时，会自动地重用 以前分配给删除行的空间。 对基于簇集索引的列值进行查询时，会有更快的执行速度，因为所有值在物理磁盘上相 互靠近。 基于簇集索引的列以升序显示数据查询，不再需要ORDER BY子句，因为表的数据本 身已经以所要求的输出顺序排列。 当检索带有连续键值的多行时，如查询姓王的所有学生时，簇索引就显示出很多优势。一 旦找到了第一个键值，后续索引值的行必定物理地排在后面，这样就无需进一步访问磁盘了。 72 第 4 章 索引与视图的创建 SQL 实例 10 创建单字段簇索引并查询创建索引后的表 为 StudentInfo 表中的姓名（sname）字段创建簇索引 Snamecluseter_Index。 CREATE CLUSTERED INDEX Snamecluseter_Index ON StudentInfo (sname ) 此时查询学生信息表（StudentInfo）中的数据： SELECT * FROM StudentInfo 运行结果如图 4.10 所示。 图 4.10 创建单字段簇索引后的表记录 可见，创建簇索引后，表中数据（行）存储的物理位置发生了变化。与非簇索引一样， 也可以建立多字段簇集索引。 实例 11 创建多字段簇索引 为 StudentInfo 表中的 sex 和 address 字段创建簇索引 SexAddressCluseter_Index。当然， 建立以前要删除已有的簇集索引 Snamecluseter_Index。 DROP INDEX studentinfo.Snamecluseter_Index CREATE CLUSTERED INDEX SexAddressCluseter_Index ON StudentInfo (sex , address ) 此时，查询 StudentInfo 表中的所有记录： SELECT * FROM StudentInfo 运行结果如图 4.11 所示。 图 4.11 创建多字段簇索引后的表记录 73 4.2 索引的创建与销毁 SQL 可以发现，在创建的多字段簇集索引中，sex 字段的优先级要高于 address 字段。 注意 一个表中只能创建一个簇索引。因为表行必须以簇集索引的顺序排列，而单个表在磁盘上只能有一个物理记录排列方式。 在对表创建了一个簇集索引之后，在插入新行或是更新簇集索引一部分的列值时，DBMS 将自动地重新排序。因此，对那些经常大量插入行或更新索引列值的表，尽量不要建立簇集 索引。 4.2.7 索引的销毁 在 SQL 中，无论是簇集索引还是非簇索引，都可以通过 DROP INDEX 关键字销毁。语 法如下。 DROP INDEX index_name 销毁索引，只需在 DROP INDEX 关键字后写入索引的名称即可。 实例 12 销 毁 索 引 将为 StudentInfo 表中的 sname 字段创建的索引 Name_Index 销毁。 DROP INDEX StudentInfo.Name_Index 注意 在 SQL Server中，用 DROP INDEX语句销毁索引时，一定要指明索引所在表的名字，如实例代码中的 StudentInfo.Name_Index。 4.2.8 使用索引的几点原则 前面介绍了索引的创建和销毁。在一个表中，什么时候需要创建索引，创建索引又需要 注意些什么呢？下面是创建索引时，需要注意的几点。 对小的数据表，使用索引并不能提高任何检索性能，因此不需对其创建索引。 当用户要检索的字段的数据包含有很多数值或很多空值（NULL）时，为该字段创建索 引，会大大提高检索效率。 当用户查询表中的数据时，如果查询结果包含的数据（行）较少，一般少于数据总数的 25%时，使用索引会显著提高查询效率。反之，如果用户的查询操作，返回结果总是包 含大量数据，那么索引的用处不大。 索引列在WHERE子句中应频繁使用。例如，在学生姓名字段上建了索引，但实际查询 中并不是经常用姓名作为查询条件，该索引就没有发生作用。 先装数据，后建索引。对于大多数的表，总有一批初始数据需要装入。该原则是说，建 立表后，先将这些初始数据装入表，然后再建索引，这样可以加快初始数据的录入。如 果建表后就建索引，那么在输入初始数据时，每插入一个记录都要维护一次索引。当然， 对于索引来说，早建和晚建都是允许的。 索引提高了数据检索的速度，但也降低了数据更新的速度。如果要对表中的数据进行大 量更新时，最好先销毁索引，等数据更新完毕再创建索引，这样会提高效率。 74 第 4 章 索引与视图的创建 SQL 索引要占用数据库空间。在设计数据库时，要把需要的索引空间考虑在内。 尽量要把表和它的索引存放在不同的磁盘上，这样会提高查询速度。 4.3 视图的基础知识 视图是从一个或多个表中查询数据的另外一种方式。利用视图，用户可以集中、简化、 定制数据库，同时还可以提供安全保证。 4.3.1 视图简介 视图是从一个或多个表中导出的表，其结构和数据是建立在对表的查询基础上的。和表 一样，视图也是包括几个被定义的数据列和多个数据行，但就本质而言，这些数据列和数据 行来源于其所引用的表。所以视图不是真实存在的基础表，而是一张虚表。 视图所对应的数据并不以视图结构存储在数据库中，而是存储在视图所引用的表中。关 于视图的概念如图 4.12 所示。 图 4.12 视图的基本示意图 通过视图看到的数据只是存放在基本表中的数据。对视图的操作与对表的操作一样，可 以对其进行查询、修改（有一定的限制）和删除。 当对视图中的数据进行修改时，相应的基本表数据也要发生变化；同时，若基本表的数 据发生变化，则这种变化也可以自动地反映到视图中。 4.3.2 视图的优缺点 视图有很多优点，主要表现在简化操作、定制数据、合并分隔数据、安全性等方面。 简化操作。视图大大简化了用户对数据的操作。因为在定义视图时，视图本身就可以是 一个复杂查询的结果集。这样，在每一次执行相同的查询时，不必重新写这些复杂的查 询语句，只要一条简单的查询视图语句即可。 定制数据。视图能够让不同的用户，以不同的方式看到不同或相同的数据集。因此，当 75 4.4 视图的创建与销毁 SQL 有许多不同水平的用户共用同一数据库时，这显得极为重要。比如，想让公司的用户访 问某些职员记录，但不想让这些用户获得诸如医疗卡号或工资之类的信息，那么就可以 创建一个视图，只提供他们应该看到的信息。 合并分隔数据。在有些情况下，由于表中数据量太大，故在表的设计时，常将表进行水 平分割或垂直分割，但表的结构的变化却对应用程序产生不良的影响。如果使用视图就 可以重新保持原有的结构关系，从而使外模式保持不变，原有的应用程序仍可以通过视 图来重载数据。 安全性。视图可以作为一种安全机制。通过视图，用户只能查看和修改他们所能看到的 数据。其他数据库或表既不可见，也不可以访问。如果某一用户想要访问视图的结果集， 必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。 使用视图有两个主要缺点。 性能。由于视图是虚拟的表，在使用包括视图引用的 SQL 语句时，除了执行所键入的 SQL语句中的查询或更新之外，还要告诉 DBMS执行定义视图的查询，这就影响了查 询效率。 更新限制。不是所有的视图都是可更新的。目前，SQL将可更新的视图限制为基于对单 个表的，并且没有GROUP BY或者HAVING子句的查询。除此之外，为了使视图是可 更新的，视图不能使用聚集函数、计算的列或 SELECT DISTINCT子句。 由于 SQL 对更新视图的限制，用户不能总是用视图来代替表。另外，在使用视图的情况 下，要综合考虑使用视图的优势和 DBMS 每次执行创建视图的 SQL 语句引起的性能损失。 4.4 视图的创建与销毁 与前面介绍的创建表、创建索引相似，视图的创建主要是由 CREATE VIEW 关键字实现 的，视图的销毁则是由 DROP VIEW 关键字实现的。 4.4.1 基本创建语法 视图的创建主要由 CREATE VIEW 关键字实现，而其数据则由 SELECT 语句定义。语法 如下。 CREATE VIEW [(column1, column2...)] AS SELECT FROM [(column1, column2...)]为可选项，缺省时，为子查询结果中的字段名。SELECT 语句指 明了视图中的字段及其数据。关于视图还要强调两点。 视图创建后，只在数据字典中存放视图的定义，而其中的 SELECT语句并不执行。 只有当用户对视图进行操作时，才按照视图的定义将数据从基本表中取出。 4.4.2 创建简单的视图 本节将给出几个实例，说明几种简单视图的创建。 76 第 4 章 索引与视图的创建 SQL 1．创建与表具有相同信息的视图 实例 13 为整表创建视图并查看其信息 创建一个与 StudentInfo 表具有相同信息的视图 StudentInfo_View。 CREATE VIEW StudentInfo_View AS SELECT * FROM StudentInfo 查看视图的数据： SELECT * FROM StudentInfo_View 运行结果如图 4.13 所示。 图 4.13 视图的数据信息 可以发现，视图 StudentInfo_View 中的信息与 StudentInfo 表完全相同（因为前面为 StudentInfo 表创建了簇索引，所以物理存储位置发生了变化）。 2．为视图创建视图 实际上，可以把视图也看成是一个表，也可以为视图创建视图。 实例 14 为视图创建视图并查看其信息 为视图 StudentInfo_View 包含的所有男同学的信息创建一个视图 Boy_View。 实例代码： CREATE VIEW Boy_View AS SELECT * FROM StudentInfo_View WHERE sex = '男' 查看视图的数据。 SELECT * FROM Boy_View 运行结果如图 4.14 所示。 77 4.4 视图的创建与销毁 SQL 图 4.14 视图的视图数据信息 注意 由于视图是一个“虚表”，当表被删除时，由该表创建的视图，或视图的视图都不可用。 3．为表中的一列、或者几列信息创建视图 实例 15 为列创建视图并查看其信息 为StudentInfo表中的姓名（sname）、生源（address）信息创建一个视图NameAddress_View。 实例代码： CREATE VIEW NameAddress_View AS SELECT sname, address FROM StudentInfo 查看视图的数据。 SELECT * FROM NameAddress_View 运行结果如图 4.15 所示： 图 4.15 列视图的信息 注意 用户可以通过创建视图进行数据查询。假如，一个表有 50列，有成千上万行，而用户 只需要使用表中的两列数据。这时，可以为这两列创建一个视图，在视图中查询需要 的数据，这样会大大提高查询效率。 4．创建与表具有不同字段名的视图 前面实例创建的视图，并没有特别指明视图的字段名，系统就默认为与表相同的字段名。 实际上，创建视图时，也可以为表中的数据定义新的字段名。 78 第 4 章 索引与视图的创建 SQL 实例 16 为表创建具有不同字段名的视图并查看其信息 为 StudentInfo 表中所有男同学的 sname、address 字段创建一个视图 NewColumnName_ View，这两个字段的字段名定义为 Boy_Name 和 Boy_Address。实例代码： CREATE VIEW NewColumnName_View(Boy_Name, Boy_Address,sex) AS SELECT sname, address,sex FROM StudentInfo WHERE sex = '男' 查看视图的数据。 SELECT * FROM NewColumnName_View 运行结果如图 4.16 所示。 图 4.16 与表具有不同字段名的视图信息 实际应用时，要注意新定义的字段名与表中数据的对应关系。 4.4.3 利用视图简化表的复杂连接 视图的一个重要用途就是进行复杂的 SQL 数据处理。通过创建视图，可以实现多表之间 的复杂连接。将频繁使用的连接定义成视图后，用户就不必在每次使用时都要指定复杂的连 接条件了。 说明 本小节和下一小节用到了一些前面并没有介绍的语句，如果读者没有一定的 SQL基础，可先略过。在本书的后面章节中，对这些语句会有详细的讲解。 在 4.2.2 节，介绍了 3 个相互关联的表——学生信息表（StudentInfo）、招生信息表 （RecruitInfo）和院系信息表（Department）。在实际应用中，用户一次查询的记录往往分别存 储在这 3 个不同的表中，如查询“李山”同学所在的系以及高考时所在地区的录取分数线信 息。这时，需要的 3 个字段 sname、dname 和 dnum 分别在这 3 个不同的表中。如果采用连接 表的方式查询，每次查询不同的同学都需要通过复杂的连接条件连接一次表，显然效率低下。 而如果为 sname、dname 和 dnum 3 个字段的记录创建一个视图，只需要进行一次表的连接， 而后相关的查询只要访问视图就可以了，明显提高了查询效率。 实例 17 利用视图简化表的复杂连接 创建一视图 Join_View，包含 StudentInfo 表中的姓名（sname）字段、RecruitInfo 表中的 79 4.4 视图的创建与销毁 SQL 系名（dname）以及 Department 表中的录取分数（dnum）的信息。实例代码： CREATE VIEW Join_View AS SELECT sname, dname, score FROM StudentInfo, Department, RecruitInfo WHERE StudentInfo.address = RecruitInfo.address AND StudentInfo.dno = Department.dno 查看视图的数据。 SELECT * FROM Join_View 运行结果如图 4.17 所示。 图 4.17 查询视图 创建了视图 Join_View 后，用户以后需要对姓名（sname）、所在系名（dname）以及所 在地录取分数（dnum）的信息查询，只要对视图 Join_View 进行操作就可以了，而不必每次 都对表进行连接。 4.4.4 利用视图简化复杂查询 在多表之间进行一系列复杂查询时，利用视图可以将查询条件层层分解，简化查询过程。 下面通过一个具体实例说明利用视图将复杂查询的层层分解过程。 实例 18 利用视图简化复杂查询 从 StudentInfo 表、RecruitInfo 表和 Department 表中查找录取分数高于 630 分的男同学的 姓名及其所在的系名。 （1）基于 StudentInfo 表，创建视图 Boys_View，包含所有男同学的信息。代码： CREATE VIEW Boys_View AS SELECT * FROM StudentInfo WHERE sex = '男' 查看视图 Boys_View 的数据。 SELECT * FROM Boys_View 80 第 4 章 索引与视图的创建 SQL 运行结果如图 4.18 所示。 图 4.18 创建基于表的视图 （2）基于 RecruitInfo 表，创建视图 Score_View，包含录取分数高于 630 的所有学生信息。 代码： CREATE VIEW Score_View(sno ,sname, sex, address,dno) AS SELECT StudentInfo.* FROM StudentInfo, RecruitInfo WHERE StudentInfo. address = RecruitInfo. address AND RecruitInfo. Score>630 查看视图 Score_View 的数据。 SELECT * FROM Score_View 运行结果如图 4.19 所示。 图 4.19 创建基于表的带有条件的视图 （3）基于 Score_View 视图和 Boys_View 视图，创建视图 BoyScore_View，包含录取分数 高于 630 的所有男学生信息。代码： CREATE VIEW BoyScore_View AS SELECT * FROM Score_View WHERE sno IN (SELECT sno FROM Boys_View) 查看视图 BoyScore_View 的数据。 SELECT * FROM BoyScore_View 运行结果如图 4.20 所示。 （4）基于视图 BoyScore_View 和表 Department，创建视图 Result_View，包含录取分数高 于 630 分的男同学的姓名及其所在的系信息。代码： 81 4.4 视图的创建与销毁 SQL 图 4.20 创建基于多个视图的视图 CREATE VIEW Result_View (sname, dname) AS SELECT BoyScore_View. Sname, Department.dname FROM BoyScore_View, Department WHERE BoyScore_View. dno = Department. dno 查看视图 Result_View 的数据。 SELECT * FROM Result_View 运行结果如图 4.21 所示。 图 4.21 查看视图的数据 可见，通过创建视图，层层分解，多表的复杂查询变得简洁、清楚。 4.4.5 视图的销毁 在 SQL 中，可以通过 DROP VIEW 关键字销毁创建的视图。语法如下。 DROP VIEW view_name 销毁视图，只需在 DROP VIEW 关键字后写入视图的名称即可。 实例 19 销毁视图并查询销毁视图后表的信息 销毁 4.4.2 节为 StudentInfo 表创建的视图 StudentInfo_View。代码如下。 DROP VIEW StudentInfo_View 此时，如果查看视图 StudentInfo_View 数据： SELECT * FROM StudentInfo_View 运行结果如下。 Invalid object name 'StudentInfo_View'. 表明该视图对象在数据库中已经不存在。 强调一点，视图在物理上是不存在的，它实际上是不存在的，只是一个查询结果，是一 个被存储的查询。与创建表 CREATE TABLE 语句不同，CREATE TABLE 语句在系统目录中 保存表，而 CREATE VIEW 语句只保存视图的定义。所以 DROP VIEW 语句删除视图时，删 82 第 4 章 索引与视图的创建 SQL 除的也只是视图的定义，对实际表中的数据并没有任何影响。 此时查看 StudentInfo 表中的数据如图 4.22 所示。 图 4.22 StudentInfo 表中的数据 可以发现，虽然视图被销毁了，但是表中的数据没有任何变化。 注意 视图被销毁后，实际的表并不会发生任何改变。 4.4.6 使用视图的几点原则 一般情况下，创建和使用视图应遵循以下几点原则。 和表一样，视图必须有惟一的名字。不仅视图之间不允许有相同的名字，并且视图与表 也不允许拥有相同的名字。 视图的创建个数不受限制，用户可以创建任意多个视图。 用户要创建视图，必须从数据库管理员得到创建权限。 视图可以嵌套，即可以创建视图的视图。 一些数据库管理系统（如 SQL Server）禁止用户在查询语句中使用ORDER BY子句。 << /ASCII85EncodePages false /AllowTransparency false /AutoPositionEPSFiles true /AutoRotatePages /All /Binding /Left /CalGrayProfile (Dot Gain 20%) /CalRGBProfile (sRGB IEC61966-2.1) /CalCMYKProfile (U.S. Web Coated \050SWOP\051 v2) /sRGBProfile (sRGB IEC61966-2.1) /CannotEmbedFontPolicy /Warning /CompatibilityLevel 1.4 /CompressObjects /Tags /CompressPages true /ConvertImagesToIndexed true /PassThroughJPEGImages true /CreateJDFFile false /CreateJobTicket false /DefaultRenderingIntent /Default /DetectBlends true /DetectCurves 0.0000 /ColorConversionStrategy /LeaveColorUnchanged /DoThumbnails false /EmbedAllFonts true /EmbedOpenType false /ParseICCProfilesInComments true /EmbedJobOptions true /DSCReportingLevel 0 /EmitDSCWarnings false /EndPage -1 /ImageMemory 1048576 /LockDistillerParams false /MaxSubsetPct 100 /Optimize true /OPM 1 /ParseDSCComments true /ParseDSCCommentsForDocInfo true /PreserveCopyPage true /PreserveDICMYKValues true /PreserveEPSInfo true /PreserveFlatness true /PreserveHalftoneInfo false /PreserveOPIComments false /PreserveOverprintSettings true /StartPage 1 /SubsetFonts true /TransferFunctionInfo /Apply /UCRandBGInfo /Preserve /UsePrologue false /ColorSettings