SQL实验三数据的更新操作和视图实验报告

1、实验三数据的更新操作和视图本实验需要 2 学时。一、实验目的 1、掌握视图的创建、修改和删除操作。2、掌握数据库数据更新（增加、修改、删除）操作。二、实验内容和步骤1、视图（1）建立名为 uv_sc 的视图。该视图可以让我们看到每个学生的姓名、选修课程名和成绩。（2）利用 uv_sc 视图，查看平均成绩在80 分以上的学生姓名。（3）建立名为 uv_course的视图，该视图显示每门课的课程名和选修该课程的学生人数。（4）修改视图 uv_course ，在视图中增加一列，显示选修该课程所有学生的平均成绩。（5）建立名为 uv_is_depart 的视图。该视图可以用来查看“信息系”的学生的学号

2、、姓名、年龄和系名。并保证对该视图的修改满足系名为信息系的条件。（6）建立名为 uv_student 的加密视图。该视图可以用来查看每个学生选修的课程门数和平均成绩。（7）利用 uv_student 视图，查询平均成绩在80 分以上的学生学号。（8）能否利用视图uv_student，修改某个学生的平均成绩（9）删除视图 uv_student。 2、数据更新（1）向 student 表增加如下记录：sno sname ssex sage saddress sdept 95011 王一鸣男20 福州市计算机系（2）将学号 95011，成绩80，以及课程表中的所有课程号插入到sc表中。（3）建立与表

3、 student 同结构的空表 stud 。然后利用 insert命令把表student 中年龄大于 19 的学生记录添加到表stud 。 （注意：可以使用 select into语句实现同结构空表的创建，再使用insert命令插入记录；或者使用单条 select into语句实现等同的功能）（4）为“95002”的学生增加选修“操作系统”和“数据结构”这两门课的选课记录。（5）把课程名为“操作系统”的成绩从成绩表sc中删除。 （试用三种方式完成）（6）将女生的成绩提高10% 。 （试用三种方式完成）（7）如果学生“张三”的数据结构的成绩低于65，则加上 10 分。（8）将成绩最低的学生的成绩

4、加上10 分。（9）将前 3 名成绩最高的学生的成绩减去10 分。（10）为没有选课的学生自动增加选修“01”课程的选课记录。（11）把选修“数学”不及格的成绩全部改成空值。（12）删除成绩低于总平均成绩的女生的选课记录。三、实验环境四、调试过程五、实验结果六、总结通过此次实验：1. 我知道了视图不是一张真实的表格，是一个虚拟的存在形式。2. 掌握了对视图的创建（ create or replace） ：当视图不存在时可使用create进行创建；而如果不确定视图是否已存在，但此时又需要建立一张此名称的视图时，可使用replace 创建，如果视图名已存在也不会出现冲突，而是进行替换。3. 在创建

5、视图时如果使用了with check option表示在视图上执行update insert或delete 操作时, 要保证所修改的行满足视图定义中的谓词条件, 即 select 查询子句中的限定条件 : 如 where 条件 , 这样可以确保数据修改后仍可通过视图看到修改的数据。4. 分清楚了对视图结构的修改（alter ） 、对视图数据的修改（update） 。同时对视图上的计算列、内置函数列和行集合函数列不能修改。5. 知道了对视图数据的删除操作（delete ） 。6. 通过第二题的训练也使我对数据库数据的更新语句有了更多的体会和认识。7. 第二题第五小题，要求三种方法，只写出了两种，

6、第三种有点没辙。而其他题目有的没要求，因为在思考过程有别的思路，也有多写了相关方法。附录：-1、视图- （1）建立名为 uv_sc的视图。该视图可以让我们看到每个学生的姓名、选修课程名和成绩。createview uv_sc as select sname, cname , grade from student s, course c, sc where=and=select*from uv_sc - （2）利用 uv_sc视图，查看平均成绩在分以上的学生姓名。select sname from uv_sc groupby sname havingavg ( grade) 80 - （3）建立

7、名为 uv_course的视图，该视图显示每门课的课程名和选修该课程的学生人数。createview uv_course as select cname, count(*)as 选修人数 from course c, sc where=groupby cname select*from uv_course - （4）修改视图 uv_course，在视图中增加一列，显示选修该课程所有学生的平均成绩。alterview uv_course as select cname, count(*)as 选修人数 , avg ( grade)as 平均成绩 from course c, sc where=g

8、roupby cname select*from uv_course - （5）建立名为 uv_is_depart的视图。该视图可以用来查看“ 信息系 ” 的学生的学号、姓名、年龄和系名。并保证对该视图的修改满足系名为信息系的条件。createview uv_is_depart as select sno, sname , sage , sdept from student s where sdept= 信息系 withcheckoptionselect*from uv_is_depart - （6）建立名为 uv_student的加密视图。该视图可以用来查看每个学生选修的课程门数和平均成绩。

9、createview uv_student( sno , course_no, avg_grade) with encryption as select sno, count(*),avg ( grade) from sc groupby sno select*from uv_student - （7）利用 uv_student视图，查询平均成绩在分以上的学生学号。select sno from uv_student where avg_grade=80 - （8）能否利用视图uv_student，修改某个学生的平均成绩update uv_student set avg_grade=70 wh

10、ere sno=95001 - （9）删除视图 uv_student。dropview uv_student -2、数据更新- （1）向 student表增加如下记录：-sno sname ssex sage saddress sdept -95011 王一鸣男20 福州市计算机系insertinto student( sno , sname , ssex , sage , saddress, sdept) values( 95011, 王一鸣 , 男 , 20 , 福州市 , 计算机系 ) altertable student add saddress varchar( 50 )null-

11、修改表结构，增加saddress列select* from student where sno=95011 - （2）将学号 ，成绩，以及课程表中的所有课程号插入到sc表中。insertinto sc ( sno , cno , grade) select95011, cno , 80 from course select*from sc - （3）建立与表 student同结构的空表stud。然后利用 insert 命令把表 student中年龄大于的学生记录添加到表 stud。（注意：可以使用select into语句实现同结构空表的创建，再使用insert命令插入记录；或者使用单条sel

12、ect into语句实现等同的功能）- 法一select*into stud from student where sage19 - 法二select*into stud from student where 1 =0 insertinto stud ( sno , sname , sage , ssex , sdept, saddress) select sno, sname , sage , ssex , sdept, saddress from student where sage19 select*from stud - （4）为 “” 的学生增加选修“ 操作系统 ” 和“ 数据结构

13、” 这两门课的选课记录。insertinto sc ( sno , cno , grade) select95002, cno ,nullfrom course where cname = 操作系统 or cname= 数据结构 select*from sc - （5）把课程名为“ 操作系统 ” 的成绩从成绩表sc中删除。（试用三种方式完成）- 法一deletefrom sc where cno in ( select cno from course where cname= 操作系统 ) - 法二deletefrom sc whereexists ( select cno from cour

14、se c where cname= 操作系统 and=) - 法三- （6）将女生的成绩提高%。（试用三种方式完成）- 法一update sc set grade=grade*where sno in ( select sno from student where ssex= 女 ) - 法二update sc set grade=grade*whereexists ( select sno from student s where ssex= 女 and=) - 法三update sc set grade=grade*where ( selectcount(*) from student s

15、 where= and ssex= 女 )=(selectcount(*)from course where cno= - （7）如果学生 “ 张三 ” 的数据结构的成绩低于，则加上分。update sc set grade=grade+10 whereexists ( select sno from student s where= and sname= 张三 and cno in ( select cno from course where cname= 数据结构 ) ) - 查看结果select sname, cname , grade from student s, course c,

16、 sc where=and= and sname= 张三 and cname= 数据结构 - （8）将成绩最低的学生的成绩加上分。update sc set grade=grade+10 where grade in ( selectmin ( grade) from sc ) - （9）将前名成绩最高的学生的成绩减去分。update sc set grade=grade- 10 where grade in ( selecttop 3 grade from sc orderby grade desc ) - （10 ）为没有选课的学生自动增加选修“” 课程的选课记录。insertinto s

17、c ( sno , cno , grade) select sno, 01,nullfrom student where sno notin ( selectdistinct( sno ) - 不显示重复值from sc ) select*from sc - （11 ）把选修 “ 数学 ” 不及格的成绩全部改成空值。update sc set grade=null where sno in ( select sno from course c where= and cname= 数学 and grade60 ) - （12 ）删除成绩低于总平均成绩的女生的选课记录。- 法一deletefrom sc -