《数据库原理及应用》实验报告题目:SQL语言(视图、数据控制、空值和空集的处理)1.4视图一实验目的熟悉SQL语言支持的有关视图的操作,能够熟练使用SQL语句来创建需要的视图,对视图进行查询和取消视图。
二实验内容(1)定义常见的视图形式,包括:·行列子集视图。
· WITH CHECK OPTION的视图。
·基于多个基表的视图。
·带表达式的视图。
·分组视图。
(2)通过实验考察WITH CHECK OPTION 这一语句在视图定义后产生的影响,包括对修改操作、删除操作、插入操作的影响。
(3)讨论视图的数据更新情况,对子行列视图进行数据更新。
(4)使用DROP语句删除一个视图,由该视图导出的其它视图定义仍在数据词典中,但已经不能使用,必须显式删除。
同样原因,删除基表时,由该基表导出的所有视图定义都必须显式删除。
三实验题目(1)定义选课信息和课程名称的视图VIEWCcreate view VIEWc asselect CHOICES.cid,ame,sid,tid,scorefrom CHOICES,COURSES where CHOICES.cid=COURSES.cid(2)定义学生姓名与选课信息的视图VIEWScreate view VIEWS asselectSTUDENTS.sname,CHOICES.cid,CHOICES.sid,CHOICES.tid,choices.scorefrom CHOICES,STUDENTS where CHOICES.sid=STUDENTS.sid(3)定义年级低于1998的学生视图S1(SID,SNAME,GRADE)drop view s1create view S1 asselect STUDENTS.sid ,sname,grade from STUDENTSwhere grade>1998(4)查询学生为”uxjof”的学生的选课信息select*from VIEWSwhere sname='uxjof'(5)查询选修课程”UML”的学生的编号和成绩select sid,score from VIEWC where cname='UML'(6)向视图S1插入记录(”60000001,Lily,2001”)insert into s1values('60000001','Lily',2001)(7)定义包括更新和插入约束的视图S1,尝试向视图插入记录(“60000001,Lily,1997”),删除所有年级为1999的学生记录,讨论更新和插入带来的影响。
drop view s1create view S1asselect STUDENTS.sid,sname,grade from STUDENTSwhere grade>1998with check option插入记录:insert into s1values('60000001','Lily',1997)出现错误,原因是目标视图指定了WITH CHECK OPTION,而该操作的一个或多个结果行又不符合CHECK OPTION 约束。
delete from s1where grade=1999命令成功执行。
(8)在视图VIEWS中将姓名为“uxjof”的学生的选课成绩都加上5分update VIEWS set score=score+5where sname='uxjof'and score<95(9)取消以上建立的所有视图drop view VIEWCdrop view VIEWSdrop view S11.5数据控制一实验目的熟悉SQL的数据控制功能,能够使用SQL语句来向用户授予和收回权限。
二实验内容(1)使用GRANT语句对用户授权,对单个用户和多个用户授权,或使用保留字PUBLIC对所有用户授权。
对不同的操作对象包括数据库、视图、基本表等进行不同权限的授权。
(2)使用WITH GRANT OPTION子句授予用户传播该权限的权利。
(3)在授权时发生循环授权,考察DBS能否发现这个错误。
如果不能,结合取消权限操作,查看DBS对循环授权的控制。
(4)使用REVOKE子句收回授权,取消授权的级联反应。
三实验题目(1)授予所有用户对表STUDENTS的查询权限。
grant select on STUDENTS to public(2)授予所有用户对表COURSES的查询和更新权限。
grant select,update on courses to public(3)授予USER1对表TEACHERS的查询,更新工资的权限,且允许USER1可以传播这些权限。
grant select,update(salary)on teachers to USER1with grant option(4)授予USER2对表CHOICES的查询,更新成绩的权限。
grant select,update(score)on choices to USER2(5)授予USER2对表TEACHERS的除了工资之外的所有信息的查询。
create view TV asselect tid,tname,email from TEACHERSgrant select on TV to USER2(6)由USER1授予USER2对表TEACHERS的查询权限,和传播此项权限的权利。
以USER1的身份登录数据库grant select on teachers to user2with grant option (7)由USER2授予USER3对表TEACHERS的查询权限,和传播此项权限的权利。
再由USER3授予USER2上述权限,这样的SQL语句能否得到执行?以USER2的身份登录数据库grant select on teachers to user3with grant option以USER3的身份登录数据库grant select on teachers to user2with grant option命令成功执行。
(8)取消USER1对表STUDENTS的查询权限,考虑由USER2的身份对表STUDENTS进行查询,操作能否成功,为什么?revoke select on teachers from user1操作不成功。
消息提示为:若要撤消或拒绝可授予的特权,请指定CASCADE 选项(9)取消USER1和USER2的关于表COOURSES的权限revoke select,update on COURSES from USER1,USER21.6空值和空集的处理一实验目的认识NULL值在数据库中的特殊含义,了解空值和空值对于数据库的数据查询操作,特别是空值在条件表达式中与其他的算术运算符或逻辑运算符的运算中,空集作为嵌套查询的子查询的返回结果时候的特殊性,能够熟练使用SQL语句来进行与空值、空集相关的操作。
二实验内容通过实验验证在原理解析中分析过的SQL SERVER对NULL的处理包括:·在查询的目标表达式中包含空值的运算·查询条件中空值与比较运算符的运算结果·使用“IS NULL"或"IS NOT NULL”来判断元组该列是否为空值·对存在取空值的列按值进行ORDER BY排序.·使用保留字DISTINCT对空值的处理,区分数据库的多种取值与现实中的多种取值的不同。
·使用GROUP BY对存在取空值的属性值进行分组·结合分组考察空值对各个集合函数的影响.特别注惫对COUNT(*)和COUNT(列名)的不同影响·考察结果集是空集时,各个集函数的处理情况。
·验证嵌套查询中返回空集的悄况下与各个谓词的运算结果·进行与空值有关的等值连接运算.三实验习题1、查询所有课程记录的上课学时〔数据库中为每星期学时),以一学期十八个星期计算每个课程的总学时,注意HOUR取NULL值的情况。
select cid,hour*18 from COURSES对NULL值做算术运算结果为NULL2、通过查询选修课程C++的学生的人数,其中成绩合格的学生人数,不合格的学生人数,讨论NULL值的特殊含义。
选修课程C++的学生的人数为187人成绩合格的人数: 151select choices.cid,COUNT(*)from CHOICES,COURSESwhere ame='C++'and COURSES.cid=CHOICES.cid and score>=60 group by choices.cid成绩不合格的人数:19select choices.cid,COUNT(*)from CHOICES,COURSES where ame='C++'and COURSES.cid=CHOICES.cidand score<60 group by choices.cid成绩为NULL的人数:17select choices.cid,COUNT(*)from CHOICES,COURSES where ame='C++'and COURSES.cid=CHOICES.cidand score is null group by choices.cid3、查询选修课程C++的学生的编号和成绩使用ORDER BY按成统进行排序时,取NULL的项是否出现在结果中?如果有在什么位置?select sid,score from CHOICES,COURSESwhere ame='C++'and COURSES.cid=CHOICES.cidorder by score取NULL的项出现在结果的最前面。
NULL值被当作最小值。
4、在上面的查询的过程中,如果加上保留字DISTINCT会有什么效果呢?select distinct sid,score from CHOICES,COURSESwhere ame='C++'and COURSES.cid=CHOICES.cidorder by score加上DISTINCT,执行结果没有区别5、按年级对所有的学生进行分组,能得到多少个组?与现实的情况有什么不同?select grade from STUDENTS group by grade得到15个组,有一条记录中grade为NULL。