《关系数库据标准语言》由会员分享，可在线阅读，更多相关《关系数库据标准语言（72页珍藏版）》请在金锄头文库上搜索。
1、数据库系统数据库系统西安电子科技大学西安电子科技大学Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evaluation only.Evaluation only. Created wit
2、h Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Science 注： CREATE VIEW 子句中的列名可以省略，但在下列情况下 明确指
3、定视图的所有列名： (1) 某个目标列是集函数或列表达式 (2) 多表连接时选出了几个同名列作为视图的字段 (3) 需要在视图中为某个列启用新的更合适的名字子查询中的属性列不允许定义别名，不允许含有ORDER BY子句和DISTINCT短语。WITH CHECK OPTION表示对视图进行更新操作的数据 必须满足视图定义的谓词条件(子查询的条件表达式)。Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides
4、 for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty L
5、td.Copyright 2004-2011 Aspose Pty Ltd.11数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Science 若一个视图是从单个基本表导出的，并且只去掉了基本 表的某些行和某些列，但保留了码，这类视图称为行列 子集视图。上例所建立的视图即为行列子集视图。Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with As
6、pose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 A
7、spose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.12数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Science 针对此视图，当进行以下更新操作时，修改操作：DBMS自动加上 Sdept= IS 的条件；删除操作：DBMS自动加上 Sdept=IS 的条件；插入操作：DBMS自动检查Sdept属性值是否为IS， (1) 如果不是，则拒绝该插入操作 (2) 如果没有提供Sdept属性值，则自动定义Sdept为ISEvaluation only.Evalua
8、tion only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.
9、0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.13数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Science 当子查询中有由表达式构成的派生属性列(也称为虚拟列 )时，必须明确定义组成视图的各个属性列名。 例6 将学生的学号及他的平均成绩定义为一个视图。CREATE VIEW
10、 S_G (Sno, Gavg)AS SELECT Sno, AVG (Grade)FROM SCGROUP BY Sno ; Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evalua
11、tion only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.14数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Scien
12、ce 存在问题：修改基表Student的结构后(在非末尾增加一列)， Student表与F_Student1视图的映象关系会被破坏，导致该视 图不能正确工作。 处理方法：在子查询的SELECT子句中明确指出各属性列的名称，可 以避免对基本表的属性列增加而破坏与视图间的映象关系， 但不能解决修改列名的问题，因此上对基本表修改后采用重 建视图的方法。Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for
13、 .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.C
14、opyright 2004-2011 Aspose Pty Ltd.15数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Science 注：该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能 使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删 除例8 删除视图IS_S1。 DROP VIEW IS_S1 ;如果有在视图IS_S1之上建立的视图IS_S2，在IS_S1被删 除后IS_S2已经无法使用，这时应该使用下列语句删除IS_S1 ：DROP VIE
15、W IS_S2 ;Evaluation only.Evaluation only. Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.Evaluation only.Evaluation only. Created with Aspose.Slides f
16、or .NET 3.5 Client Profile 5.2.0.0.Created with Aspose.Slides for .NET 3.5 Client Profile 5.2.0.0. Copyright 2004-2011 Aspose Pty Ltd.Copyright 2004-2011 Aspose Pty Ltd.16数据库系统 Principle of DataBase System西安电子科技大学计算机学院 - School of Computer Science 查询语句：SELECT Sno, Sage FROM IS_Student WHERE Sage = 90 ;使用视图消解转换的查询： SELECT Sno, AVG(Grade) FROM SC WHERE AVG(Grade)=90 GROUP BY Sno ;正确的查询： SELECT Sno,
