10第三章SQL语言_视图,授权,习题课

视图（view）视图是从一个或几个基本表或视图导出的表，它与基本表不同，是一个虚表。数据库中只存放视图的定义，不存放视图对应的数据，这些数据仍存放在原来的基本表中。基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。SQL语言支持的三级模式结构SQL用户视图视图基本表基本表基本表存储文件存储文件存储文件内模式模式外模式DBMSCreateView视图名[(列名[,列名,…)]As子查询[WithCheckOption];其中：WithCheckOption允许数据变更数据更新时应满足子查询中的条件视图一经定义，就可以和基本表一样被查询、被删除，也可以在一个视图之上再定义新的视图，但对视图的更新(增、删、改)操作则有一定的限制。1．建立视图说明1、子查询中不允许含OrderBy和Distinct2、下列三种情况列名不能省略某个目标列是聚集函数或列表达式(派生属性)；多表连接时选出了几个同名列作为视图的列；某个列启用新的名字(列名)；例1建立华东地区代理商的视图CreateViewAgent_eAsSelect*FromAgentWhereA_area=‘华东’;视图的列名省略，其列名与子查询一致。例2建立信控系学生的视图，且视图可以变更。CreateViewStudent_vAsSelectSno,Sname,SageFromStudentWhereSdept=’信控系’WithCheckOption;DBMS执行Createview语句,是把视图的定义存到数据字典中，并不执行其中的select语句。例3建立自动化院1024课程成绩的视图。CreateViewDB_v(sno,sname,grade)AsSelectStudent.Sno,sname,gradeFromStudent,ScWhereSdept=’自动化’andStudent.Sno=sc.SnoandSc.Cno=’1024’例3建立信息系选修了1024号课程且成绩在90分以上的学生的视图CreateViewDB_v_90AsSelectSno,Sname,GradeFromDB_vWhereGrade=90;视图DB_v_90建立在视图DB_v上。例4建立一个学生出生年份的视图CreateViewStud_b_v(sno,sname,birth)AsSelectSno,sname,2003-sageFromStudent例5将学生学号及其平均成绩定义一个视图。CreateViewS_avg(Sno,Savg)AsSelectSno,avg(grade)FromScGroupBySno;说明如果子查询中的原表改变了结构，则导出的视图将不能正常工作,应删除后重建。3．查询视图视图查询与表的形式一样。例:在信息系视图中查找‘肖小燕’学生的信息Select*FromStudent_vWhereSname=‘肖小燕’；2．删除视图格式：DropView视图名；例6DropViewS_avg;检查表、视图是否存在取出视图定义子查询与对视图的查询结合起来转化成对基本表的查询－－视图的消解即：将视图查询转换为对基本表的查询消解时:视图名换成表名条件中补充视图中子查询的条件查询过程：曾经创建视图Student_v：CreateViewStudent_vAsSelectSno,Sname,SageFromStudentWhereSdept=’信息系’在信息系视图中查找‘肖小燕’学生的信息Select*FromStudent_vWhereSname=‘肖小燕’；消解为Select*FromStudentWhereSname=‘肖小燕’andSdept=‘信息系’例在视图S_avg查询平均成绩在90分以上的信息Select*FromS_avgWhereSavg=90;消解为(形式上)：Select*FromScWhereAvg(Grade)=90GroupBySno;由于Where条件表达式不能用聚集函数,应消解Select*FromScGroupBySnoHavingAvg(Grade)=90;有问题？*：Sno,Avg(Grade)4.更新视图更新操作：InsertUpdateDelete更新条件：视图为行列子集视图－单表行列子集视图视图由单表导出,只去掉某些行或列,但保留了主码。定义时有WithCheckOption子句例将信息系视图增加一个新生InsertIntoStudent_v(Sno,Sname,Ssex,Sage)Valuse(‘2000330’,‘赵敏’,‘女’,20);InsertIntoStudent(Sno,Sname,Ssex,Sage,Sdept)Valuse(‘2000330’,‘赵敏’,‘女’,20,‘信息系’)转换后的语句为：例：将信息系学生视图Student_v中学号为2000034姓名改为‘刘辰’。UpdateStudent_vSetSname=‘刘辰’WhereSno=‘2000034’;UpdateStudentSetSname=‘刘辰’WhereSno=‘2000034’andSdept=‘信息系’将其转化后的更新语句为例删除信息系视图Student_v中学号为2000329的记录DeleteFromStudent_vWhereSno=‘2009329’;转化为对基本表student的更新DeleteFromStudentWhereSno=‘2000329’andSdept=‘信息系’5．视图的用途(1)视图能够简化用户的操作；(2)视图可以任意选定属性列，灵活、方便，使用户能以多种角度看待同一数据；(3)视图对重构数据库提供了的逻辑独立性，新增关系或属性列不会影响到应用程序；(4)视图能够对机密数据提供安全保护，过虑保密数据，产生不能更新的视图；(5)简化查询的表达条件。SQL语言提供了数据控制功能，数据控制也称数据库保护，包括数据的安全性控制、完整性控制，并发控制和恢复。完整性控制是指数据库中数据的正确性与相容性。并发控制是指当多个用户并发地对数据库操作时，对他们加以控制、协调，数据库的一致性。恢复是指当发生各种类型的故障，使数据库处于不一致状态时，将数据库恢复到一致状态的功能。数据控制对库的安全性、完整性控制数据库的安全性是指保护数据库，防止不合法的使用所造成的数据泄露和破坏。安全性的管理是对数据存取进行控制，即规定不同用户对于不同数据对象所允许执行的操作。不同的用户对不同的数据应具有何种操作权力，是由DBA和表的建立者(即表的属主)，根据具体情况决定的，SQL语言则为DBA和表的属主授予与回收这种权力提供了手段。1．授权SQL语言用Grant语句向用户授予操作权限格式Grant权限[，权限]…[On对象类型对象名]To用户[，用户]…[WithGrantOption]；含义：将指定操作对象、操作权限授予指定的用户对象名视图、表、数据库对象类型Table、Datebase操作权限Select、Insert、Update、DeleteAlter、Index、AllPrivilegesCreatetab不同对象类型允许的操作权限Select,insert,update,delete,allprivilegesSelect,insert,update,delete,alter,index,allprivilegesCreatetab对象名对象类型操作权限属性列视图基本表数据库TableDatabaseTableDBMS执行：把授权的决定输入系统把授权结果存入数据字典用户进行操作时检查授权,决定是否允许操作说明：WithGrantOption：权限能再授予别的用户对象名＝属性列时，应明确对应的列名On对象类型,对象名缺省,表示整个数据库用户可以是一个、多个或全体用户Public例1把查询student表权限授给用户U1GrantSelectOnTableStudentToU1;例2把对student表和course表的全部权限授予用户U2和U3GrantAllPrivilegesOnTableStudent,CourseToU2,U3;例3把对表Sc的查询权限授予所有用户GrantSelectOnTableScToPublic;例4把查询视图student_v和修改学生学号的权限授给用户U4。GrantUpdate(sno),SelectOnTableStudent_vToU4;例5把对表Sc的insert权限授予U5用户，并允许他再将此权限授予其他用户。GrantInsertOnTableScToU5WithGrantOption;例6将数据库Sc中建立表的权限授予用户U8GrantCreatetabOnDatabaseScToU8;例7权限授予的区别GrantUpdate(sno)OnTableStudentToU1;用户U1有修改学生学号的权限GrantUpdateOnTableStudentToU2;用户U2有修改整个学生表的权限2．收回权限授予的权限可以由管理员或其他授权者用Revoke语句收回。格式：Revoke权限[，权限]…[On对象类型对象名]From用户[，用户]…;例8把用户U4修改学生学号的权限收回。RevokeUpdate(sno)OnTableStudentFromU4;如果授权时后面附有WithGrantOption，则授权者可将此特权转授给其他用户。由此可以把特权授给一连串用户。在收回特权时，不但收回指明的用户的特权，而且同时收回由这个用户转授出去的特权。例10收回所有用户对表sc的查询权限。RevokeSelectOnTableScFromPublic;例9把用户U5对Sc表的Insert权限收回。RevokeInsertOnTableScFromU5;注：举例说明设用户Ul~U4,U1U2、U2U3、U3U4:U1:GrantSelectToU2WithGrantOption；U2:GrantSelectToU3WithGrantOption；U3:GrantSelectToU4WithGrantOption；U1收回授给U2的特权:U1:RevokeSelectFromU2；此语句的作用不仅收回U1授予U2的特权(Select0nTables和授权传播权),而且还收回U2授予U3、U3授予U4的这种特权。小结：安全性控制，即授权与回收权限授权：Grant权限[，权限…][on对象类型对象名]to用户[，用户…][withgrantoption]回收：Revoke权限[，权限…][on对象类型对象名]from用户[，用户…]具有DBA特权的用户对数据库拥有最大的特权，因而也对数据库负有特别的责任。DBA特权不得任意扩散！数据完整性控制一、完整性定义：实体完整性(列级、表级完整性)参照完整性(列级完整性)用户完整性(列级完整性)1、实体完整性列级完整性(单属性列)设置主码primarykey列的值不允许取空值notnull表级完整性(多属性列)设置主码primarykey(列1,列2)2、参照完整性参照外码foreignkey列名references表名(列名)3、用户完整性(自行定义)取值范围Check()二、数据完整性保护在数据输入、变更时会破坏完整性的约定，系统可以实施一致性保护。主要有：1.拒绝不允许执行该操作(默认)2.瀑布删除3.设置空值空值的处理空值的含义：应该有值，但目前不知道不应该有值用法:赋值：列名＝null判断：列名is[not]null注意：列为char型，null不一定等于‘’列为数值型，null不一定等于0在统计时尤其要当心！