数据库原理及应用视图及授权课件2

数据库原理与应用数据库原理与应用南方医科大学生物医学工程学院信息技术系数据库原理与应用南方医科大学生物医学工程学院信息技术系1 视 图视图的特点虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数据也随之改变 视 图视图的特点2视 图1、建立视图2、删除视图3、查询视图4、更新视图视 图1、建立视图31、建立视图语句格式 CREATE VIEW (，)AS WITH CHECK OPTION；子查询不允许含有ORDER BY子句和DISTINCT短语。WITH CHECK OPTION表示对视图进行增删改操作时，不得破坏视图定义中的谓词条件（即子查询中的条件表达式）。1、建立视图语句格式4常见的视图形式行列子集视图WITH CHECK OPTION的视图基于多个基表的视图基于视图的视图带表达式的视图分组视图常见的视图形式行列子集视图5行列子集视图从单个基本表导出，只是去掉了基本表的某些行和某些列，而保留了码。例1 建立销售部员工的视图。CREATE VIEW Sales_Stuff AS SELECT Sno,Sname,Sage FROM Stuff WHERE Sdept=Sales;行列子集视图从单个基本表导出，只是去掉了基本表的某些行和某些6WITH CHECK OPTION的视图例2 建立销售部员工的视图，并要求透过该视图进行的更新操作只涉及销售部员工。CREATE VIEW Sales_Stuff AS SELECT Sno,Sname,Sage FROM Stuff WHERE Sdept=Sales WITH CHECK OPTION;WITH CHECK OPTION的视图例2 建立销售7基于多个基表的视图例3 建立销售部售卖了1号商品的员工视图。CREATE VIEW Sales_S1(Sno,Sname,Grade)AS SELECT Stuff.Sno,Sname,Grade FROM Student,SG WHERE Sdept=Sales AND Stuff.Sno=SG.Sno AND SG.Gno=1;基于多个基表的视图例3 建立销售部售卖了1号商品的员工8基于视图的视图例4 建立信息系售卖了1号商品且业绩绩在90以上的员工的视图。CREATE VIEW Sales_S2 AS SELECT Sno,Sname,Grade FROM Sales_S1 WHERE Grade=90；基于视图的视图例4 建立信息系售卖了1号商品且业绩绩在9带表达式的视图定义视图时可以设置一些派生属性列,也称为虚拟列。例5 定义一个反映员工出生年份的视图。CREATE VIEW BT_S(Sno,Sname,Sbirth)AS SELECT Sno,Sname,2015-Sage FROM Stuff带表达式的视图必须明确定义组成视图的各个属性列名。带表达式的视图定义视图时可以设置一些派生属性列,也称为虚拟10建立分组视图可以用带有集函数和GROUP BY子句的查询来定义视图。例6 将员工的工号及他的平均业绩定义为一个视图。（假设SG表中“成绩”列Grade为数字型）CREATE VIEW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SG GROUP BY Sno;建立分组视图可以用带有集函数和GROUP BY子句的查询来定11视 图1、建立视图2、删除视图3、查询视图4、更新视图5、视图的作用视 图1、建立视图122、删除视图DROP VIEW ；该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须显式删除例9 删除视图Sales_S1。DROP VIEW Sales_S1;2、删除视图DROP VIEW ；13视 图1、建立视图2、删除视图3、查询视图4、更新视图5、视图的作用视 图1、建立视图143、查询视图从用户角度：查询视图与查询基本表相同DBMS实现视图查询的方法视图实体化法（View Materialization）有效性检查：检查所查询的视图是否存在执行视图定义，将视图临时实体化，生成临时表查询视图转换为查询临时表查询完毕删除被实体化的视图(临时表)3、查询视图从用户角度：查询视图与查询基本表相同15查询视图例1 在销售部员工的视图中找出年龄小于30岁的员工。SELECT Sno,Sage FROM Sales_Stuff WHERE Sage=90;S_G视图定义：CREATE VIEW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SG GROUP BY Sno;查询视图例2在S_G视图中查询平均成绩在90分以上的学生17视 图1、建立视图2、删除视图3、查询视图4、更新视图5、视图的作用视 图1、建立视图184、更新视图用户角度：更新视图与更新基本表相同DBMS实现视图更新的方法视图实体化法（View Materialization）视图消解法（View Resolution）指定WITH CHECK OPTION子句后DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新4、更新视图用户角度：更新视图与更新基本表相同19更新视图例1 将销售部员工视图IS_Stuff中学号95002的员工姓名改为“刘辰”。UPDATE Sales_Stuff SET Sname=刘辰 WHERE Sno=95002;转换后的语句：UPDATE StuffSET Sname=刘辰WHERE Sno=95002 AND Sdept=Sales;更新视图例1 将销售部员工视图IS_Stuff中学号920更新视图例2 向销售部员工视图Sales_Stuff中插入一个新的员工记录：95029，赵新，25岁 INSERT INTO Sales_Stuff VALUES(95029,赵新,25);转换为对基本表的更新：INSERT INTO Stuff(Sno,Sname,Sage,Sdept)VALUES(95029,赵新,20,Sales);更新视图例2 向销售部员工视图Sales_Stuff中21更新视图例3 删除视图IS_Stuff中工号为95029的记录。DELETE FROM Sales_Stuff WHERE Sno=95029;转换为对基本表的更新：DELETE FROM Stuff WHERE Sno=95029 AND Sdept=Sales;更新视图例3 删除视图IS_Stuff中工号为950222更新视图的限制一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的更新(对两类方法均如此)例：视图S_G为不可更新视图。CREATE VIEW S_G(Sno,Gavg)AS SELECT Sno,AVG(Grade)FROM SG GROUP BY Sno;更新视图的限制一些视图是不可更新的，因为对这些视图的更新不能23更新视图对于如下更新语句：UPDATE S_GSET Gavg=90WHERE Sno=95001;无论实体化法还是消解法都无法将其转换成对基本表SG的更新。更新视图对于如下更新语句：24实际系统对视图更新的限制允许对行列子集视图进行更新对其他类型视图的更新，不同系统有不同限制实际系统对视图更新的限制允许对行列子集视图进行更新25课堂练习1建立销售部售卖了编号为92001商品的员工视图。在销售部员工的视图中找出年龄大于30岁的员工。向销售部员工视图Sales_Stuff中插入一个新的员工记录：95028，刘晨，27岁 课堂练习1建立销售部售卖了编号为92001商品的员工视图。26授 权将对指定操作对象的指定操作权限授予指定的用户。授 权将对指定操作对象的指定操作权限授予指定的用户。27 授 权GRANT语句的一般格式：GRANT,.ON TO,.WITH GRANT OPTION;授 权GRANT语句的一般格式：28(1)操作权限(1)操作权限29(2)用户的权限建表（CREATETAB）的权限:属于DBADBA授予-普通用户基本表或视图的属主拥有对该表或视图的一切操作权限接受权限的用户:一个或多个具体用户PUBLIC（全体用户）(2)用户的权限建表（CREATETAB）的权限:属于DB30(4)WITH GRANT OPTION子句指定了WITH GRANT OPTION子句:获得某种权限的用户还可以把这种权限再授予别的用户。未指定WITH GRANT OPTION子句:获得某种权限的用户只能使用该权限，不能传播该权限。(4)WITH GRANT OPTION子句指定了WITH31例题例1 把查询Stuff表权限授给用户U1。GRANT SELECT ON TABLE Stuff TO U1;SQL Server中取消了中取消了TABLE关键字。关键字。例题例1 把查询Stuff表权限授给用户U1。SQL Ser32例题例2 把对Stuff表和Goods表的全部权限授予用户U2和U3。GRANT ALL PRIVILEGES ON TABLE Stuff,Goods TO U2,U3;SQL Server中一条中一条GRANT语句只能对一张语句只能对一张表或视图进行授权。表或视图进行授权。例题例2 把对Stuff表和Goods表的全部权限授予用户U33例题例3 把对表SG的查询权限授予所有用户。GRANT SELECT ON TABLE SG TO PUBLIC;例题例3 把对表SG的查询权限授予所有用户。34例题例4 把查询Stuff表和修改员工号的权限授给用户U4。GRANT UPDATE(Sno),SELECT ON TABLE Stuff TO U4;例题例4 把查询Stuff表和修改员工号的权限授给用户U4。35例题例5 把对表SG的INSERT权限授予U5用户，并允许他再将此权限授予其他用户。GRANT INSERT ON TABLE SG TO U5 WITH GRANT OPTION;例题例5 把对表SG的INSERT权限授予U5用户，并允许他36收回权限REVOKE语句的一般格式为：REVOKE,.ON FROM,.;功能：从指定用户那里收回对指定对象的指定权限收回权限REVOKE语句的一般格式为：37例题例7 把用户U4修改员工号的权限收回。REVOKE UPDATE(Sno)ON TABLE Stuff FROM U4;例题例7 把用户U4修改员工号的权限收回。38例题例8 收回所有用户对表SG的查询权限。REVOKE SELECT ON TABLE SG FROM PUBLIC;例题例8 收回所有用户对表SG的查询权限。39例题例9 把用户U5对SG表的INSERT权限收回。REVOKE INSERT ON TABLE SG FROM U5;例题例9 把用户U5对SG表的INSERT权限收回。40SQL灵活的授权机制DBA拥有对数据库中所有对象的所有权限，并可以根据应用的需要将不同的权限授予不同的用户。用户对自己建立的基本表和视图拥有全部的操作权限，并且可以用GRANT语句把其中某些权限授予其他用户。被授权的用户如果有“继续授权”的许可，还可以把获得的权限再授予其他用户。所有授予出去的权力在必要时又都可以用REVOKE语句收回。SQL灵活的授权机制DBA拥有对数据库中所有对象的所有权限，41课堂练习2把查询Stuff表和修改员工号的权限授给用户U4把对表stuff的INSERT权限授予U5用户，并允许他再将此权限授予其他用户收回所有用户对表SG的查询权限课堂练习2把查询Stuff表和修改员工号的权限授给用户U442*嵌 入 式 SQLSQL语言提供了两种不同的使用方式：交互式嵌入式为什么要引入嵌入式SQLSQL语言是非过程性语言事务处理应用需要高级语言这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充*嵌 入 式 SQLSQL语言提供了两种不同的使用方式：43DBMS处理宿主型数据库语言SQL 的方法修改和扩充主语言使之能处理SQL语句（少用）预编译（多用）由DBMS的预处理程序对源程序进行扫描，识别出SQL语句；把它们转换成主语言调用语句，以使主语言编译程序能识别它；最后由主语言的编译程序将整个源程序编译成目标码。DBMS处理宿主型数据库语言SQL 的方法修改和扩充主语言使44嵌入式SQL的预编译处理源程序源程序主主语言言+SQL预编译系系统主主语言言源程序源程序主主语言言编译系系统目目标代代码嵌入式SQL的预编译处理源程序预编译主语言主语言目标45嵌入式SQL需要解决的问题区分SQL语句和主语言语句数据库工作单元和程序工作单元之间的通讯协调SQL语句的面向集合处理方式和主语言的面向记录处理方式嵌入式SQL需要解决的问题区分SQL语句和主语言语句46作业1，落实到每位同学，课下上机操作，完成课堂练习1,2的练习题。2，小组合作，汇总现有的基于SQL server构建的所有基本表及巩固相关的操作语句3，小组合作，基于SQL server建立超市管理系统。作业1，落实到每位同学，课下上机操作，完成课堂练习1,2的练47