【数据库】ch3-SQL-2

3.4数据更新一、插入数据(1)插入单个元组－－新元组插入指定表中。语句格式：INSERTINTO表名[(属性列1[，属性列2…)]VALUES(常量1[，常量2]…);注：INTO子句指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值VALUES子句提供的值的个数和值的类型必须与INTO子句匹配3.4数据更新[例48]将一个新学生记录(学号:03830144；姓名：韩冬；性别：男;所在系:IS;年龄:18岁)插入到Student表中。INSERTINTOStudentVALUES('03830144','韩冬','男',18,'IS');INSERTINTOStudent(Sno,Sname,Ssex,Sdept,SageVALUES('03830144','韩冬','男','IS',18);3.4数据更新(2)插入子查询结果语句格式：INSERTINTO表名[(属性列1[，属性列2…)]子查询；注：INTO子句(与插入单条元组类似)指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值子查询SELECT子句目标列属性的个数和类型必须与INTO子句匹配。3.4数据更新对每个系，求学生平均年龄，把结果存入数据库CreateTableDept_age(SdeptVARCHAR(15),Avg_ageSMALLINT);InsertIntoDept_age(Sdept,Avg_age)SelectSdept,AVG(Sage)FromStudentGroupBySdept;3.4数据更新二、修改数据语句格式：UPDATE表名SET列名=表达式[,列名=表达式]…[WHERE条件]；功能：修改指定表中满足WHERE子句条件的元组。注：SET子句－－指定修改方式，要修改的列和修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元组3.4数据更新DBMS在执行修改语句时会检查欲修改的元组是否会破坏表上已定义的完整性规则。如果破坏，系统会提示，且该记录不会被修改。实体完整性对于修改的记录主码不为空且不可以与表中已有记录主码相同。参照完整性外码必须取空值或被参照关系中对应属性的值。用户定义的完整性对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内3.4数据更新三、删除数据语句格式：DELETEFROM表名[WHERE条件]；功能删除指定表中满足WHERE子句条件的元组注：–WHERE子句指定要删除的元组缺省表示要修改表中的所有元组3.4数据更新DBMS在执行删除语句时会检查所删除的元组是否会破坏表上已定义的完整性规则。如果破坏，系统会提示，且该记录不会被删除。3.5视图什么是视图(View)？视图是从一个或几个基本表（或视图）导出的表，它与基本表不同，是一个虚表。在数据字典中只存放视图的定义，不会出现数据冗余。基表中的数据发生变化，从视图中查询出的数据也随之改变。视图一经定义，就可以和基本表一样被查询和删除，并且可以在视图之上再定义新的视图。视图的更新(增加、删除、修改)操作会受到一定的限制。视图对应三级模式体系结构中的外模式。3.5视图一、视图定义(1)建立视图语句格式：CREATEVIEW视图名[(列名[，列名]…)]AS子查询[WITHCHECKOPTION]；注：CREATEVIEW子句中的列名可以省略，此时视图的属性由子查询中SELECT目标列中的诸字段组成。但在下列情况下明确指定视图的所有列名：(1)某个目标列是集函数或列表达式(2)多表连接时选出了几个同名列作为视图的字段(3)需要在视图中为某个列启用新的更合适的名字子查询中的属性列不允许定义别名，不允许含有ORDERBY子句和DISTINCT短语。WITHCHECKOPTION表示对视图进行更新操作的数据必须满足视图定义的谓词条件(子查询的条件表达式)。3.5视图DBMS执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的SELECT语句。在对视图查询时，按视图的定义从基本表中将数据查出。[例53]建立数学系男生的视图。CREATEVIEWMA_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=‘MA‘ANDSsex=’男’;若一个视图是从单个基本表导出的，并且只是去掉了基本表的某些行和某些列，但保留了码，这类视图称为行列子集视图。上例所建立的视图即为行列子集视图。3.5视图[例54]建立数学系学生的视图，并要求透过该视图进行的更新操作只涉及数学系学生。CREATEVIEWMA_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=‘MA'WITHCHECKOPTION;针对此视图，当进行以下操作时，修改操作：DBMS自动加上Sdept=‘MA'的条件删除操作：DBMS自动加上Sdept='MA'的条件插入操作：DBMS自动检查Sdept属性值是否为'MA'(1)如果不是，则拒绝该插入操作(2)如果没有提供Sdept属性值，则自动定义Sdept为'MA'3.5视图[例55]建立数学系选修了C2号课程的学生视图。CREATEVIEWMA_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=‘MA'ANDStudent.Sno=SC.SnoANDSC.Cno=‘C2';[例56]建立信息系选修了C2号课程且成绩在60分以上的学生的视图。CREATEVIEWMA_S2ASSELECTSno,Sname,GradeFROMMA_S1WHEREGrade6=90；3.5视图[例57]定义一个反映学生出生年份的视图。CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,Year(GetDate())-SageFROMStudent;当子查询中有由表达式构成的派生属性列(也称为虚拟列)时，必须明确定义组成视图的各个属性列名。[例58]将学生的学号及他的平均成绩定义为一个视图CREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;3.5视图(2)删除视图语句格式：DROPVIEW视图名；注：该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除例如删除视图MA_S1DROPVIEWMA_S1;由于有在视图IS_S1之上建立的视图IS_S2，在IS_S1被删除后IS_S2已经无法使用，这时应该使用下列语句删除IS_S1：DROPVIEWMA_S2;3.5视图二、查询视图从用户角度：查询视图与查询基本表相同DBMS实现视图查询的方法实体化视图（ViewMaterialization）(1)有效性检查：检查所查询的视图是否存在(2)执行视图定义，将视图临时实体化，生成临时表(3)查询视图转换为查询临时表(4)查询完毕删除被实体化的视图(临时表)视图消解法（ViewResolution）(1)进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义(2)把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询(3)执行修正后的查询3.5视图[例59]在数学系学生的视图中找出年龄小于18岁的学生。查询语句：SELECTSno,SageFROMMA_StudentWHERESage18;视图实体化法：通过视图定义建立视图结构下的临时表并对临时表进行查询，在查询结束后删除临时表。视图消解法：根据视图定义将对视图的查询转换为对基本表的查询，转换后的查询语句如下：SELECTSno,SageFROMStudentWHERESdept='MA'ANDSage18；3.5视图[例60]在Stu_G视图中查询平均成绩在90分以上的学生学号和平均成绩。S_G视图定义：CREATEVIEWStu_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；对视图的查询语句：SELECT*FROMStu_GWHEREGavg=90；使用视图消解转换的查询：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)=90GROUPBYSno；正确转换的查询：SELECTSno;AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)=90；注：由于对定义中含有集函数的视图不能完全正确地转换为对基本表的查询，这类查询应当直接对基本表进行。3.5视图三、更新视图(1)用户角度：更新视图与更新基本表相同(2)DBMS实现视图更新的方法视图实体化法（ViewMaterialization）视图消解法（ViewResolution）(3)指定WITHCHECKOPTION子句后，DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新。3.5视图实际系统对视图更新的限制从理论上讲，对其更新能够唯一转换为对应基本表更新的视图是可更新的。实际使用中的商品化RDBMS系统只允许对可更新视图的更新操作。通常都只允许对行列子集视图进行更新。3.5视图四、视图的作用(1)视图能够简化用户的操作当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作。如基于多张表连接形成的视图，基于复杂嵌套查询的视图和含导出属性的视图。(2)视图使用户能以多种角度看待同一数据不同的用户和应用程序会以不同的角度看待同一数据。如学生信息中的生源所在地，在招生管理中表达该学生是从哪个地方录取的，而在户籍管理中则表达该学生的户口是从哪个地方迁入的。这两种应用所使用的是数据库中存储的同一数据。因此，视图可以避免数据存储的冗余性，适应数据库共享的需要。3.5视图(3)视图对重构数据库提供了一定程度的逻辑独立性例：现有学生关系Student(Sno,Sname,Ssex,Sage,Sdept)，并在此关系上开发了应用程序，当对学生关系进行如下重构(改变关系的结构)，将其“垂直”地分成两个基本表：SX(Sno,Sname,Sage)SY(Sno,Ssex,Sdept)这时原关系Student已经不存在，应用程序也就无法使用。可以通过建立以下视图：CREATEVIEWStudent(Sno,Sname,Ssex,Sage,Sdept)ASSELECTSX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.SdeptFROMSX,SYWHERESX.Sno=SY.Sno;使用户的外模式保持不变，从而对原Student表的查询程序不必修改，但更新程序必须修改。(即一定程度)3.5视图(4)视图机制提高了系统的安全性建立面向不同用户的视图，并将对该视图的操作权限赋予不同的用户，则这些用户只能对视图所见的数据进行权限内的操作，而视图之外的数据对这些用户是不可见的，从而也就对视图之外的数据进行了保护。同样，针对不同的应用程序建立视图，使用该应用程序也只能操作视图内的数据，对视图外的数据也进行了保护。例如Student表涉及三个系的学生数据，可以在其上定义三个视图，每个视图只包含一个系的学生数据，并只允许每个系的系主任查询