第03SQL语言练习

1、 )1数据库系统概论数据库系统概论an introduction to database system )23.6 3.6 视图视图 3.6.1 3.6.1 定义视图定义视图3.6.2 3.6.2 查询视图查询视图3.6.3 3.6.3 更新视图更新视图3.6.4 3.6.4 视图的作用视图的作用 )3 create view ( ,) as with check option； 说明：组成视图的属性列，或全部省略或说明：组成视图的属性列，或全部省略或全部指定全部指定3.6.1 3.6.1 定义视图定义视图 列表达式（集函数）列表达式（集函数） 同名列、列改名同名列、列改名 )43.6.1 3

2、.6.1 定义视图定义视图 )53.6 3.6 视图视图 3.6.1 3.6.1 定义视图定义视图3.6.2 3.6.2 查询视图查询视图3.6.3 3.6.3 更新视图更新视图3.6.4 3.6.4 视图的作用视图的作用 )6例例9 在信息系学生视图中找出年龄在信息系学生视图中找出年龄20岁的学生。岁的学生。 select sno，sage from is_stu where sage20；3.6.2 3.6.2 查询视图查询视图思考该语句的思考该语句的执行过程执行过程 ？ 从用户角度：查询视图与查询基本表相同从用户角度：查询视图与查询基本表相同 )73.6.2 3.6.2 查询视图查询视图

3、 从用户角度：查询视图与查询基本表相同从用户角度：查询视图与查询基本表相同ndbms视图查询的方法视图查询的方法u方法方法1-1-实体化视图实体化视图4有效性检查有效性检查：检查所查询的视图是否存在检查所查询的视图是否存在4执行视图定义执行视图定义：将视图临时实体化，生成临时表将视图临时实体化，生成临时表4查询临时表查询临时表4删除临时表删除临时表 )8例例9 在信息系学生视图中找出年龄在信息系学生视图中找出年龄20岁的学生。岁的学生。 select sno，sage from is_stu where sage20；u采用实体化视图执行采用实体化视图执行 (1)先执行视图定义先执行视图定义

4、create view is_stu as select sno，sname，sage from student where sdept= is； (2)再执行在视图上的查询再执行在视图上的查询3.6.2 3.6.2 查询视图查询视图 )93.6.2 3.6.2 查询视图查询视图 从用户角度：查询视图与查询基本表相同从用户角度：查询视图与查询基本表相同ndbms视图查询的方法视图查询的方法u方法方法2-2-视图消解法（视图消解法（view resolutionview resolution）4有效性检查有效性检查：检查查询表、视图是否存在。检查查询表、视图是否存在。4将视图定义中的子查询与用户

5、的查询相结合将视图定义中的子查询与用户的查询相结合：转换成等价的对基本表的查询：转换成等价的对基本表的查询4执行修正后的查询执行修正后的查询 )10例例9 在信息系学生的视图中找出年龄在信息系学生的视图中找出年龄20岁的学生。岁的学生。 select sno，sage from is_stu where sage20；4采用视图消解法采用视图消解法，转换后的查询语句为：转换后的查询语句为： select sno，sage from student where sdept= is and sage=90=90分的学生学号分的学生学号和平均成绩。和平均成绩。 select * from s_g w

6、here gavg=90； 3.6.2 3.6.2 查询视图查询视图s_g 视图定义如下：视图定义如下： create view s_g (sno，gavg) as select sno，avg(grade) from sc group by sno思考两种方法思考两种方法的执行过程的执行过程 ？ )13 例例1111在在s_gs_g视图中查询平均成绩视图中查询平均成绩=90=90分的学生学号和分的学生学号和平均成绩。平均成绩。 select sno，avg(grade) from sc where avg(grade)=90 group by sno； select sno，avg(grad

7、e) from sc group by sno having avg(grade)=90；3.6.2 3.6.2 查询视图查询视图 有些情况下，视图消解法不能生成正确查有些情况下，视图消解法不能生成正确查询，采用视图消解法的询，采用视图消解法的dbmsdbms会限制这类查询。会限制这类查询。 )143.6 3.6 视图视图 3.6.1 3.6.1 定义视图定义视图3.6.2 3.6.2 查询视图查询视图3.6.3 3.6.3 更新视图更新视图3.6.4 3.6.4 视图的作用视图的作用 )153.6.3 3.6.3 更新视图更新视图：更新视图与更新基本表相同：更新视图与更新基本表相同u通过视图

8、实现插入、删除和修改数据。通过视图实现插入、删除和修改数据。u由于视图是虚表，因此对视图的更新由于视图是虚表，因此对视图的更新要转要转换为换为对基本表的更新对基本表的更新。 u视图实体化法视图实体化法（view materialization）u视图消解法视图消解法（view resolution） )163.6.3 3.6.3 更新视图更新视图 dbms在更新视图时会进行检查，防止用在更新视图时会进行检查，防止用户通过视图对户通过视图对不属于视图范围内不属于视图范围内的基本表数据的基本表数据进行更新。进行更新。 )17 例例12 将视图将视图is_stu中学号中学号95002的姓名改为刘辰。

9、的姓名改为刘辰。 update is_stu set sname= 刘辰刘辰 where sno= 02151224 update student set sname= 刘辰刘辰 where sno= 0215122 and sdept= is3.6.3 3.6.3 更新视图更新视图 )18例例13 向信息系学生视图向信息系学生视图is_s中中插入插入一个新的学生记录一个新的学生记录 （0215129，赵新，赵新，20岁）岁） insert into is_stu values(0215129，赵新赵新，20)4 转换为对基本表的更新：转换为对基本表的更新： insert into stude

10、nt(sno,sname,sage,sdept) values(0215129, 赵新赵新, 20, )3.6.3 3.6.3 更新视图更新视图sql serversql server拒绝拒绝插入！插入！ )19例例14 删除视图删除视图is_s中学号为中学号为0215129的记录。的记录。 delete from is_student where sno= 02151294 delete from student where sno= 0215129 3.6.3 3.6.3 更新视图更新视图 )203.6.3 3.6.3 更新视图更新视图 一些视图是不可更新的，因为对这些视图的一些视图是不可

11、更新的，因为对这些视图的更新更新不能唯一不能唯一地地有意义有意义地转换成对相应基本表的地转换成对相应基本表的更新更新(对两类方法均如此对两类方法均如此) )213.6.3 3.6.3 更新视图更新视图例：视图例：视图s_g为不可更新视图。为不可更新视图。 create view s_g (sno，gavg) as select sno，avg(grade) from sc group by sno；执行如下更新语句：执行如下更新语句： update s_g set gavg=90 where sno= 0215121； )223.6.3 3.6.3 更新视图更新视图 一些视图是不可更新的，因为

12、对这些视图的一些视图是不可更新的，因为对这些视图的更新更新不能唯一不能唯一地地有意义有意义地转换成对相应基本表的地转换成对相应基本表的更新更新(对两类方法均如此对两类方法均如此)n视图的可更新性：视图的可更新性：u理论上可更新：理论上可更新：u理论上不可更新：理论上不可更新：允许更新允许更新不允许更新不允许更新不允许更新不允许更新 )23实际系统对视图更新的限制实际系统对视图更新的限制n允许对行列子集视图进行更新允许对行列子集视图进行更新n对其他类型视图的更新，不同系统有不同限制对其他类型视图的更新，不同系统有不同限制 一些一些dbms对视图更新的限制对视图更新的限制：(1) 若视图由若视图由

13、导出，则此视图不允许更新导出，则此视图不允许更新(2) 若视图列来自若视图列来自，则不允许执行，则不允许执行insert和和update操作，可执行操作，可执行delete操作。操作。(3) 若视图列来自若视图列来自，则此视图不允许更新。，则此视图不允许更新。 )24实际系统对视图更新的限制实际系统对视图更新的限制n允许对行列子集视图进行更新允许对行列子集视图进行更新n对其他类型视图的更新不同系统有不同限制对其他类型视图的更新不同系统有不同限制 一些一些dbms对视图更新的限制对视图更新的限制（续）：（续）：(4)(4)若视图定义含有若视图定义含有子句，则不允许更新。子句，则不允许更新。(5)

14、(5)若视图定义含有若视图定义含有，则不允许更新，则不允许更新(6)(6)一个一个定义的视图也不允许更新定义的视图也不允许更新(7)(7)若视图定义有嵌套查询，并且内层查询的若视图定义有嵌套查询，并且内层查询的fromfrom子子句中涉及的表也是导出该视图的基本表，则此视图句中涉及的表也是导出该视图的基本表，则此视图不允许更新。不允许更新。 )25实际系统对视图更新的限制实际系统对视图更新的限制例例 将将sc中成绩在平均成绩之上的元组定义成一中成绩在平均成绩之上的元组定义成一个视图个视图good_sc: create view good_sc as select sno，cno，grade f

15、rom sc where grade () 导出视图导出视图good_sc的基表与内层查询中涉及的基表与内层查询中涉及的表相同。的表相同。sql serversql server允许更新允许更新！ )263.6 3.6 视图视图 3.6.1 3.6.1 定义视图定义视图3.6.2 3.6.2 查询视图查询视图3.6.3 3.6.3 更新视图更新视图3.6.4 3.6.4 视图的作用视图的作用 )273.6.4 3.6.4 视图作用视图作用1.1.视图能够视图能够简化简化用户的操作用户的操作 当视图中数据不是直接来自基本表时，定当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作。义视图

16、能够简化用户的操作。4 基于多张表连接形成的视图基于多张表连接形成的视图4 基于复杂嵌套查询的视图基于复杂嵌套查询的视图4 含导出属性的视图含导出属性的视图 )283.6.4 3.6.4 视图作用视图作用1.1.视图能够视图能够简化简化用户的操作用户的操作2.2.视图使用户能以视图使用户能以多种角度多种角度看待同一数据看待同一数据4 视图机制能使不同用户以不同角度看待同视图机制能使不同用户以不同角度看待同一数据，适应数据库共享的需要一数据，适应数据库共享的需要3.3.视图对在视图对在一定程度上一定程度上提高了数据库的提高了数据库的逻辑独立性逻辑独立性 )293.6.4 3.6.4 视图作用视图

17、作用例：数据库逻辑结构发生改变例：数据库逻辑结构发生改变 学生关系学生关系student(sno,sname,ssex,sage,sdept) 4“垂直垂直”地分成两个基本表：地分成两个基本表： sx(sno，sname，sage) sy(sno，ssex，sdept) 通过建立一个视图通过建立一个视图student： create view student(sno,sname,ssex,sage,sdept) as select sx.sno, sname, ssex, sage, sdept from sx，sy where sx.sno=sy.sno； )303.6.4 3.6.4 视图

18、作用视图作用1.1.视图能够视图能够简化简化用户的操作用户的操作2.2.视图使用户能以视图使用户能以多种角度多种角度看待同一数据看待同一数据3.3.视图对在视图对在一定程度上一定程度上提高了数据库的提高了数据库的逻辑独立性逻辑独立性 使用户的外模式保持不变，从而对原使用户的外模式保持不变，从而对原student表的查询程序不必修改表的查询程序不必修改n对不同用户定义不同视图，使每个用户只能看到对不同用户定义不同视图，使每个用户只能看到他有权看到的数据；他有权看到的数据；n通过通过with check option对关键数据定义操作对关键数据定义操作限制限制4.4.能够对数据提供能够对数据提供安

19、全保护安全保护 )31 create view is_sc as select student.sno,sanme,cno,grade from sc where student.sno=sc.sno and cno= 1 and datepart(hh,getdate() between 9 and 17 and datepart(dw,getdate() between 2 and 6 with check option;3.6.4 3.6.4 视图作用视图作用 )323.6.4 3.6.4 视图作用视图作用1.1.视图能够视图能够简化简化用户的操作用户的操作2.2.视图使用户能以视图使用

20、户能以多种角度多种角度看待同一数据看待同一数据3.3.视图对在视图对在一定程度上一定程度上提高了数据库的提高了数据库的逻辑独立性逻辑独立性4.4.能够对数据提供能够对数据提供安全保护安全保护5.5.适当的利用视图可以更适当的利用视图可以更清楚的表达查询清楚的表达查询 )33小结小结sql的视图使用的视图使用u视图的定义视图的定义(create view as)u视图的查询（两种方法）视图的查询（两种方法）u视图的更新（不允许更新的视图）视图的更新（不允许更新的视图）u视图的作用视图的作用作业作业p127第第4、5、7、9、11题。题。 )341.sql语言具有语言具有_的功能的功能 a.关系规

21、范化、数据操纵、数据控制关系规范化、数据操纵、数据控制 b.数据定义、数据操纵、数据控制数据定义、数据操纵、数据控制 c.数据定义、关系规范化、数据控制数据定义、关系规范化、数据控制 d.数据定义、关系规范化、数据操纵数据定义、关系规范化、数据操纵2.sql中，与中，与“not in”等价的操作符是等价的操作符是_ a.=any b.any c.=all d.all )353.检索选修课程检索选修课程“2”的学生中成绩最高的学的学生中成绩最高的学生的学号。正确的生的学号。正确的select语句是语句是_。 select sno from scwhere cno=2 and grade= (se

22、lect grade from sc where cno=2) select sno from scwhere cno=2 and grade in (select grade from sc where cno=2) select sno from scwhere cno=2 and grade not in (select grade from sc where cno=2) select sno from scwhere cno=2 and grade =all (select grade from sc where cno=2) a bc d )364.说明以下代码的含义，并指出该语句

23、的执行顺序说明以下代码的含义，并指出该语句的执行顺序: select sno sum(grade) from sc where grade=60 group by sno having count(*)4 order by 2 desc )375.5.设有下列设有下列4 4个关系模式个关系模式: :书店（书店编号，书店名，地址）书店（书店编号，书店名，地址）图书（书号，书名，定价）图书（书号，书名，定价）出版社（出版社号，出版社名，城市，电话）出版社（出版社号，出版社名，城市，电话）图书发行（出版社号，书号，书店号，数量）图书发行（出版社号，书号，书店号，数量）select 出版社名出版社名

24、from 出版社出版社where 出版社号出版社号 in (select 出版社号出版社号 from 图书发行图书发行 where 书号书号 in (select 书号书号 from 图书图书 where 书名书名=数据库系统概论数据库系统概论)(1)(1)说明下列代码所表达的含义。说明下列代码所表达的含义。 )381.sql语言具有语言具有_的功能的功能 a.关系规范化、数据操纵、数据控制关系规范化、数据操纵、数据控制 b.数据定义、数据操纵、数据控制数据定义、数据操纵、数据控制 c.数据定义、关系规范化、数据控制数据定义、关系规范化、数据控制 d.数据定义、关系规范化、数据操纵数据定义、关

25、系规范化、数据操纵2.sql中，与中，与“not in”等价的操作符是等价的操作符是_ a.=any b.any c.=all d.all )393.检索选修课程检索选修课程“2”的学生中成绩最高的学的学生中成绩最高的学生的学号。正确的生的学号。正确的select语句是语句是_。 select sno from scwhere cno=2 and grade= (select grade from sc where cno=2) select sno from scwhere cno=2 and grade in (select grade from sc where cno=2) selec

26、t sno from scwhere cno=2 and grade not in (select grade from sc where cno=2) select sno from scwhere cno=2 and grade =all (select grade from sc where cno=2) a bc d )404.说明以下代码的含义，并指出该语句的执行顺序说明以下代码的含义，并指出该语句的执行顺序: select sno sum(grade) from sc where grade=60 group by sno having count(*)4 order by 2 d

27、esc 检索在选修课程中及格在检索在选修课程中及格在4门以上的学门以上的学生学号和及格课程的平均成绩，并按平生学号和及格课程的平均成绩，并按平均成绩的降序排列。均成绩的降序排列。 )414.说明以下代码的含义，并指出该语句的执行顺序说明以下代码的含义，并指出该语句的执行顺序: select sno sum(grade) from sc where grade=60 group by sno having count(*)4 order by 2 desc )425.5.设有下列设有下列4 4个关系模式个关系模式: :书店（书店编号，书店名，地址）书店（书店编号，书店名，地址）图书（书号，书名，定价）图书（书号，书名，定价）出版社（出版社号，出版社名，城市，电话）出版社（出版社号，出版社名，城市，电话）图书发行（出版社号，书号，书店号，数量）图书发行（出版社号，书号，书店号，数量）select 出版社名出版社名 from 出版社出版社where 出版社号出版社号 in (select 出