4、使用SQL语言创建数据对象.doc

上一章：数据存放第四章 使用SQL语言创建数据对象第四章使用SQL语言创建数据对象14.1 CREATE语句14.2 DROP语句24.3 ALTER语句24.4 创建DB2数据库34.5 视图34.5.1带有Check Option成分的视图54.5.2嵌套视图定义64.5.3修改视图74.5.4可删除的视图74.5.5可更新的视图84.5.6可插入的视图84.6 索引84.6.1 一般的索引指导原则94.6.2 更改索引94.6.3 引用的完整性和索引104.6.4空值和索引10 让我们使用SQL语句创建某些数据对象。在本章的开头，我们曾经把SQL语言分为三部分：DDL数据定义语言DCL数据控制语言DML数据操纵语言允许我们定义和修改数据库对象的SQL语句组叫做DDL或数据定义语言(Data Definintion Language)。数据定义语言包含三类SQL语句：CREATEALTERDROP4.1 CREATE语句 CREATE . CREATE语句用来对数据库对象进行初始定义。被创建的 (数据库对象)可以是以下的任何一种：表窗口别名用户自定义函数(FUNCTION)触发器事件监视器索引用户自定义数据类型(DISTINCT TYPE)表空间使用DDL语言创建任何数据库对象的结果将产生对系统编目表的更新。创建这些数据库对象需要特殊的数据库。数据库对象可以用于各种目的。某些数据库对象用来定义一种条件或联系(索引、监视器、触发器),其它数据库对象只是简单的定义用户数据(表、表空间)的位置。4.2 DROP语句 DROP . DROP(去除)语句用来删除系统编目表内的约束条件或定义。因为系统编目表不能被直接删除，DROP语句用来从这些表中删除数据记录。因为数据库对象可能依赖于其它数据库对象，去除一个对象的动作可能会导致一个相关的对象成为无效。被去除的(数据库对象)可能是以下的一种对象：表窗口别名用户自定义函数(FUNCTION)触发器事件监视器索引用户自定义数据类型(DISTINCT TYPE)表空间4.3 ALTER语句ALTER ALTER(更改)语句允许用户更改某些数据库对象信息(数据库对象必须已经存在于数据库中)。用户可以更改一个表或表空间。被更改的(数据库对象)可以是以下的数据库对象之一：1 表(Table)。2 表空间(Table Space) 注解：用户不能更改窗口或索引。用户必须去除原有的窗口或索引，然后再创建新的窗 口或索引。每次用户发出一条DDL语句，系统编目表将被更新。这种更新包括创建或修改时间印戳和创建者（修改者）的 HP uthid(权限标识)。 注解：以下的做法是有益的：将针对数据库的所有DDL语句存储到命令行处理器（CLP）输入文件中，以便允许更容易地创建数据库对象。4.4 创建DB2数据库在创建任何数据库对象之前，必须存在某个DB2数据库。必须给数据库一个名字。(该数据库不与任何模式相关。)一旦创建了数据库之后，下一个逻辑步骤是要创建表空间。 注解：CREATE DATABASE命令不是一条SQL语句。其中的数据库名的长度是1至8个字符。表空间类型在DB2中，有两种类型的表空间：系统管理存储器(SMS System Managed Storage)表空间数据库管理存储器(DMS Database Managed Storages)表空间系统管理的表空间（SMS）在系统管理存储器（SMS）中，DB2使用它自己的管理机制去存储和定位数据库对象。执行CREATE TABLESPACE命令期间，为这些表空间分配磁盘空间。数据库管理的表空间（DMS）在系统管理的表空间(DMS)的表空间中，DB2使用它自己的存储器机制去存储和定位数据库对象。这类表空间的磁盘空间在执行CREATE TABLESPACE语句期间分配。 注解：如果使用DMS表空间，用户将需要计算表所期望的磁盘空间。默认的表空间当创建数据库时，DB2将按照默认方式创建三种表空间：这些表空间是SMS（系统管理的表空间）。它们是 SYSCATSPACE 包含系统编目 TEMPSPACE1 保存临时表 USERSPACE1 包含所有用户数据4.5 视图视图是使用CREATE VIEW语句创建的逻辑表。一旦视图被定义，就可以使用DML语句(SELECT、INSERT、UPDATE、DELETE)去访问它，就仿佛它是一个基表一样。视图是一个临时表，视图只能在查询期间被访问。现在让我们来为DB2课程授证考试数据库创建基于test_ taken表之间的一个视图。这个表用来存储所有的应考学员标识符、考试编号和考试中心编号，应考学员需要在考试中心参加DB2课程授证考试。出于安全性的考虑，一个考试场所只能安排应考学员在他们所在地区的考试。用户可以不去为每个考试中心创建一个单独的表，而是为每个引用单个表的考试中心创建一个视图。如果我们为每个考试中心创建了一个单独的表，并且用户想要得到有关所有考试中心的报告，这将需要大量的表进行连接。在创建视图之后，可以指定存取特权。这将提供数据的安全性，因为只有基表的受限制的视图可以被访问。窗口可以包含一条WHERE子名去将存取操作限定至针对某些行，或者一个视图只包含一部分列，以将访问限制到某些数据列上。在我们的例子中，视图需要将访问限制在test_ taken表的某些行上。在图4.18中给出了视图db2.test_ taken_ tx01的定义，该视图用来将行限制到只能由考试中心tx01存取的那些考试(行)上。如图4.18所示，我们从test_ taken 表中选择所有的列，但是我们正在限定到tcid列上。这一定义将仅显示已经报名参加在考试中心tx01举行的考试的那些应考学员的信息。CREATE VIEW语句用来定义视图名及其它的列。视图总是通过对一个或多个表使用SELECT语句来定义。 注解：视图总是使用一条SELECT语句来定义。在视图中的列名不需要与基表中的列名相匹配。表名有一个相关的模式，视图名也同样有一个相关的模式。在图4.18中，基表和视图对应的模式名都是db2。 CREATE VIEW db2.test_taken _tx01 ( tcid , cid , number, date_ taken , start_ time , finish_ time , score , seat_ no) AS SELECT tcid , cid , number , date_ taken , start_ time , finsh_ time , score , seat_ no FROM db2.test_ taken WHERE CHAR ( tcid ) = TX01 图4.18定义视图Test_ Taken_ Tx01一旦定义了视图后，视图就可以(带有限制条件)被用于象SELECT、INSERT、 UPDATE和DELETE 这样的DML语句中。数据库管理员可以决定为一组用户提供对于视图而不是基表的更高一级的存取特权。例如，一个考试中心协调员能够更新有关视图 db2.text_ taken_ tx01的特权，而同时不更新表 db2.test_ taken 的特权。如果视图db2.test_ taken_ tx01作为更新操作的目标表，其中的WHERE条件将不进行验证。因此，如图 4.18中所定义的视图不能保证某个INSERT或 UPDATE 操作将不影响其它考试中心的数据。 注解：按照默认方式，视图定义中的谓词（WHERE句子）对 UPDATE不起作用。4.5.1带有Check Option成分的视图 如果视图定义语句包含条件(比如，一条WHERE子句)并且试图保证，任何引用该视图的 INSERT或 UPDATE语句都应用一条 WHERE子句，这个视图必须使用 WITH CHECK OPTION成分来定义，这样的视图也叫做调和视图(symmetric view)。这一选区项可以保证数据库中被修改数据的完整性。如果执行 INSERT或 UPDATE操作期间，违反条件，那么将返回一个 SQL错误。图 4.19是使用WITH CHECK OPTION 成分对视图进行定义的例子。让我们假设，考试中心TX01只有两个座位。想限制表中的值以便确保座位号码是1或2。这时需要用到WITH CHECK OPTION选项去保证不断对条件进行检查。 CREATE VIEW db2.tx01_ seats (tcid , cid , number, date_ taken , start_ time , finish_ time , score, seat_ no) AS SELECT tcid , cid , number , date_ taken , start_ time , finsh_ time , score , seat_ no FROM db2.test_ taken WHERE CHAR ( tcid ) = TX01 AND seat_ no BETWEEN 1 AND 2 WITH CHECK OPTION 图4.19带有WITH CHECK OPTION成分的视图定义 在图4.19中的视图包含WITH CHECK选项。这将是 seat_ no列的输入值。当视图被用来插入新值时， WITH CHECK OPTION部分的功能总会被实施。如果图4.19中的视图被用于INSERT语句中，对于其 seat_ no列值不是1或2的那些行将被拒绝。重要的是要记住，如果不规定 WITH CHECK OPTION，当遇到修改时，将不进行数据的有效性检查。如果图 4.19中的视图被用于SELECT语句中，那么条件 (WHERE子句)将被调用并且结果表将只包含匹配的数据行。对于象在图4.19中所定义的视图，还有一些管理问题需要强调。如果考试中心tx01为其考试设施增加了更多的座位，那么需要改变 BETWEEN子句的内容。为此，我们可以创建一个稍有不同的视图，它可以通过增加一个子查询来避免这个修改问题。这个子查询有单值的结果，如图 4.20中所示。在图 4.20中，视图用表 db2.test_center 的值定义，以便在使用该视图插入新值的时候对座位值的有效性进行核查。第二条SELECT语句是确保不返回1个以上的值，因为 tcid 列是主码。 CREATE VIEW db2.tx01_seats (tcid , cid , number, date_ taken , start_ time , finish_ time , score, seat_ no) AS SELECT tcid , cid , number , date_ taken , start_ time , finsh_ time , score , seat_ no FROM db2.test_ taken WHERE CHAR ( tcid ) = TX01 AND SMALLINT( seat_ no ) BETWEEN 1 AND (SELECT no_ seat FROM db2.test_ center WHERE CHAR(TCID) = TX01 ) WITH CHECK OPTION 图4.20在谓词中带有子查询的视图4.5.2嵌套视图定义如果一个窗口基于另一个视图之上，那么谓词核查的量是基于短语WITH CHECK OOPTION的使用之上。如果视图 V2 是基于视图V1之上，并且有关 V2 的核查选项是WITH CASADED CHECK OPTION，那么在使用 V2视图的 INSERT和 UPDATE活动期间，对两个视图的谓词都需要计算。如果视图 V2 是基于视图V1之上，并且有关 V2 的核查选项是WITH LOCAL CHECK OPTION，那么使用 V2 视图的INSERT 和UPDATE 活动期间，仅对视图V2 的谓词进行计算。 注解：WITH CHECK OPTION并不影响只读视图的结果集。4.5.3修改视图 视图是临时的表定义。视图存储在系统编目表中。因此，如果执行数据库的备份，视图定义便被保存在被备份的影象中。在视图中包含的数据，只能在SQL中引用视图时可以被访问。与其它 DB2对象不同，不能使用 ALTER语句去更改视图。如果需要以某种方式去改变视图，则必须去除原有的视图，再创建视图。如果某个被引用的数据库对象从数据库中被去除，那么该视图将变成不可操作的 (inoperative)。这些视图的依赖关系存储在叫做SYSCAT.VIEWDEP的系统表中。系统编目表 SYSCAT.VIEWS保存着一个叫做 VALID的列，如果视图变成不可操作的，该列将包含字符 X。如果去除表 db2. test_ taken ，那么db2.test_taken 和窗口db2.tx01_seats将变成如图 4.21所示的不可操作视图。 SELECT VIEWNAME,VIEWCHECK,READONLY,VALID FROM SYSCAT.VIEWS WHERE VIEWSCHEMA = DB2 VIEWNAME VIEWCHECK READONL YVALID - - - - - - - - - - - - - - - TEST_TAKEN_TX01 N N X TE01_SEATS C N X 图4.21 不可操作的视图图4.21中显示了两个不可操作的视图。Valid列包含 X 值(Y的值表示视图是有效的)。图4.21中的询问没有显示正文列的内容。这个列包含原始的 CREATE VIEW语句正文。 viewcheck 列与CREATE VIEW语句中的 WITH CHECK OPTION相对应。 N值表示没有规定任何核查选项， L表示规定的是 local(本地)核查选项， C表示指定了 cascaded （级联）核查选项。 注解：视图总是实施基表的约束条件。这些约束条件可能包括主码、外来码、表核查或非空约束条件。 4.5.4可删除的视图根据视图的定义，决定视图能够或不能够用于DELETE语句。在图 4.21中，只读列用来决定该视图是否只能用于SELECT 语句。我们已经定义的视图被认为是可更新的和可删除的，因为read-only 列包含的值是N。如果视图的定义坚持以下的规则，那么该视图可以用于 DELETE语句中。外层完全选择(fullselect)的FROM语句只标识基表、可删除的表、可删除的嵌套表表达式或可删除的常用表表达式。外层完全选择不包含VALUE子句。外层完全选择不包含GROUP BY HAVING子句。外层完全选择不包含选择列表中的列函数。外层完全选择不包含集合操作(UNION、EXCEPT或 INTERSECT)，UNION ALL操作列外。当使用UNION ALL操作时，所有的操作数必须是可删除的，并且不能是同一个表。外层完全选择的选定不包含DISTINCT。 4.5.5可更新的视图如果视图满足一个可删除视图的要求以及所有以下的条件，那么这个视图就被认为是可更新的。被更新的列必须是求解基表中的一列。它不能是一个派生列。基于UNION ALL之上的视图的所有列必须有匹配的数据类型和默认值。4.5.6可插入的视图如果视图的所有列都是可更新的，并且该视图的完全选择不包括UNION ALL，那么这个视图被认为是可插入的视图。4.6 索引索引代表了数据库内数据的复制。当插入、更新或删除数据时，按照指定的次序（升序或降序）维护索引。以保证值的唯一性。以便改变查询性能。如果对于单列或多列已经定义了一个UNIQUE(唯一性)索引，就能够保证数据唯一性。查询选择期间可以使用唯一性的索引，以便更快地检索数据。通过维护数据的一种排序次序， NON-UNIQUE (非唯一性)索引也能改进查询性能。如果对表定义了一个唯一性索引，那么DB2 将不允许有任何重复的键值。索引键值由DB2 基于索引定义(CREATE INDEX语句) 之上产生。索引键可由一个或多个列组成。 注解：存储到数据库中的数据记录没有定义任何次序。相应的索引用CREATE INDEX语句定义 的次序存储。 索引保持的键值与基表中的数据行相对应。对表可以定义多重索引，但是要记住，每个索引都必须由DB2维护(这可能增加了更新、删除、插入的时间)。因此，如果有一个表有三个索引，并且该表含有100行数据，将需要产生300个索引键值。索引包括一个指针，即记录识码(RID-record id)，它指向表中对应表行的物理位置。让我们用列 Iname、 fname、 initial在 db2.candidate表上创建一个唯一性索引。db2.icandidate_by_Iname 将按照键列(Inames、 fname、 initial)组合值的升序排列去存储RIDs (记录标识码)。 注解：默认的索引排序次序是ascending（升序）。让我们发出以下的SQL语句： SELECT Iname , fname , initial FROM db2.candidate LDDER BY Iname db2.icandidate_ by_ Iname将可能用来检索结果，因为所有要求的数据都驻留在索引中，并且索引已经按照适当的次序(升序)排列好。不需要使用临时表去对数据排序。如果 SQL语句已经声明了一个升序结果表，那么图4.22中的索引不大可能用于求解这一查询。 DB2索引不能同时跨越两个方向(沿升序和降序)。因此，用户可以在同一键列上定义两个索引，一个索引按照升序排列，一个索引按照降序排列。要按照降序排列，可以在列名后加上关键字 DESC。 CREATE UNIQUE INDEX db2.icandidate_ by_ Iname ON db2.candidate (Iname, fname, initial) 图4.22 建立索引的例子图4.23中定义的索引表明，指定降序索引列的一个例子。要创建非唯一性的索引，可略去CREATE INDEX语句中的关键字 UNIQUE。在图4.23中，索引可能已经被定义为非唯一性索引，因为可能存在有姓、名和姓名起首字母相同的应考学员。 CREATE UNIQUE INDEX db2.icandidate_ by_ Iname ON db2.candidate (Iname DESC, fname, initial) 图4.23 降序排列DB2用的唯一性索引去实现声明的引用约束条件。当我们为 DB2课程授证考试数据库创建这个表(图4.15)时，我们声明了一个主码约束条件。这个索引保证每个数据记录的唯一性，并且它不能使用DROP INDEX命令去除。4.6.1 一般的索引指导原则索引需要消耗磁盘空间。所消耗磁盘空间的数量与索引键列的长度密切相关。索引占据空间的大小随着被插入基列表的数据日而增加。因此当规划数据库的大小时，要考虑索引所需要的磁盘空间。有关建立索引的一些考虑