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

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

本实验需要2学时。

一、实验目的

1、掌握视图的创建、修改和删除操作。

2、掌握数据库数据更新（增加、修改、删除）操作。

二、实验内容和步骤

1、视图

（1）建立名为UV_SC的视图。该视图可以让我们看到每个学生的姓名、选修课程名和成绩。

（2）利用UV_SC视图，查看平均成绩在80分以上的学生姓名。

（3）建立名为UV_Course的视图，该视图显示每门课的课程名和选修该课程的学生人数。

（4）修改视图UV_Course，在视图中增加一列，显示选修该课程所有学生的平均成绩。

（5）建立名为UV_IS_Depart的视图。该视图可以用来查看“信息系”的学生的学号、姓名、年龄和系名。并保证对该视图的修改满足系名为信息系的条件。

（6）建立名为UV_Student的加密视图。该视图可以用来查看每个学生选修的课程门数和平均成绩。

（7）利用UV_Student视图，查询平均成绩在80分以上的学生学号。

（8）能否利用视图UV_Student，修改某个学生的平均成绩？

（9）删除视图UV_Student。

2、数据更新

（3）建立与表STUDENT同结构的空表STUD。然后利用INSERT命令把表STUDENT中年龄大于19的学生记录添加到表STUD。（注意：可以使用select into语句实现同结构空表的创建，再使用insert命令插入记录；或者使用单条select into语句实现等同的功能）（4）为“95002”的学生增加选修“操作系统”和“数据结构”这两门课的选课记录。

（5）把课程名为“操作系统”的成绩从成绩表SC中删除。（试用三种方式完成）

（6）将女生的成绩提高10%。（试用三种方式完成）

（7）如果学生“张三”的数据结构的成绩低于65，则加上10分。

（8）将成绩最低的学生的成绩加上10分。

（9）将前3名成绩最高的学生的成绩减去10分。

（10）为没有选课的学生自动增加选修“01”课程的选课记录。

（11）把选修“数学”不及格的成绩全部改成空值。

（12）删除成绩低于总平均成绩的女生的选课记录。

三、实验环境

四、调试过程

五、实验结果

六、总结

通过此次实验：

1.我知道了视图不是一张真实的表格，是一个虚拟的存在形式。

2.掌握了对视图的创建（create or replace）：当视图不存在时可使用create

进行创建；而如果不确定视图是否已存在，但此时又需要建立一张此名称的视图时，可使用replace创建，如果视图名已存在也不会出现冲突，而是进行替换。

3.在创建视图时如果使用了with check option表示在视图上执行

或DELETE操作时,要保证所修改的行满足视图定义中的谓词条件,即SELECT查询子句中的限定条件:如Where条件,这样可以确保数据修改后仍可通过视图看到修改的数据。

4.分清楚了对视图结构的修改（alter）、对视图数据的修改（update）。同时对

视图上的计算列、内置函数列和行集合函数列不能修改。

5.知道了对视图数据的删除操作（delete）。

6.通过第二题的训练也使我对数据库数据的更新语句有了更多的体会和认识。

7.第二题第五小题，要求三种方法，只写出了两种，第三种有点没辙。而其他

题目有的没要求，因为在思考过程有别的思路，也有多写了相关方法。

附录：

--1、视图

--（1）建立名为UV_SC的视图。该视图可以让我们看到每个学生的姓名、选修课程名和成绩。create view UV_SC

as

select sname,cname,grade

from student s,course c,sc

where s.sno=sc.sno and https://www.sodocs.net/doc/d712815127.html,o=https://www.sodocs.net/doc/d712815127.html,o

select*from UV_SC

--（2）利用UV_SC视图，查看平均成绩在分以上的学生姓名。

select sname

from UV_SC

group by sname

having avg(grade)>80

--（3）建立名为UV_Course的视图，该视图显示每门课的课程名和选修该课程的学生人数。create view UV_Course

as

select cname,count(*)as'选修人数'

from course c,sc

where https://www.sodocs.net/doc/d712815127.html,o=https://www.sodocs.net/doc/d712815127.html,o

group by cname

select*from UV_Course

--（4）修改视图UV_Course，在视图中增加一列，显示选修该课程所有学生的平均成绩。

alter view UV_Course

as

select cname,count(*)as' 选修人数',avg(grade)as'平均成绩'

from course c,sc

where https://www.sodocs.net/doc/d712815127.html,o=https://www.sodocs.net/doc/d712815127.html,o

group by cname

select*from UV_Course

--（5）建立名为UV_IS_Depart的视图。该视图可以用来查看“信息系”的学生的学号、姓名、年龄和系名。并保证对该视图的修改满足系名为信息系的条件。

create view UV_IS_Depart

as

select sno,sname,sage,sdept

from student s

where sdept='信息系'

with check option

select*from UV_IS_Depart

--（6）建立名为UV_Student的加密视图。该视图可以用来查看每个学生选修的课程门数和平均成绩。

create view UV_Student(sno,course_no,avg_grade)

with encryption

as

select sno,count(*),avg(grade)

from sc

group by 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

where sno=95001

--（9）删除视图UV_Student。

drop view UV_Student

--2、数据更新

--（1）向STUDENT表增加如下记录：

--Sno Sname Ssex Sage Saddress Sdept

--95011 王一鸣男20 福州市计算机系

insert into student(sno,sname,ssex,sage,saddress,sdept)

values('95011','王一鸣','男',20,'福州市','计算机系')

alter table student

add saddress varchar(50)null--修改表结构，增加saddress列

select*

from student

where sno='95011'

--（2）将学号’’，成绩，以及课程表中的所有课程号插入到SC表中。

insert into sc(sno,cno,grade)

select'95011',cno,80

from course

select*from sc

--（3）建立与表STUDENT同结构的空表STUD。然后利用INSERT命令把表STUDENT中年龄大于的学生记录添加到表STUD。（注意：可以使用select into语句实现同结构空表的创建，再使用insert命令插入记录；或者使用单条select into语句实现等同的功能）

--法一

select*into STUD

from student

where sage>19

--法二

select*into STUD

from student

where 1=0

insert into STUD(sno,sname,sage,ssex,sdept,saddress)

select sno,sname,sage,ssex,sdept,saddress

from student

where sage>19

select*from STUD

--（4）为“”的学生增加选修“操作系统”和“数据结构”这两门课的选课记录。

insert into sc(sno,cno,grade)

select'95002',cno,null

from course

where cname='操作系统'or cname='数据结构'

select*from sc

--（5）把课程名为“操作系统”的成绩从成绩表SC中删除。（试用三种方式完成）

--法一

delete from sc

where cno in

(

select cno

from course

where cname='操作系统'

)

--法二

delete from sc

where exists

(

select cno

from course c

where cname='操作系统'and https://www.sodocs.net/doc/d712815127.html,o=https://www.sodocs.net/doc/d712815127.html,o

)

--法三

--（6）将女生的成绩提高%。（试用三种方式完成）

--法一

update sc

set grade=grade*1.1

where sno in

(

select sno

from student

where ssex='女'

)

--法二

update sc

set grade=grade*1.1

where exists

(

select sno

from student s

where ssex='女'and s.sno=sc.sno

)

--法三

update sc

set grade=grade*1.1

where

(

select count(*)

from student s

where s.sno=sc.sno and ssex='女'

)=(select count(*)from course where cno=https://www.sodocs.net/doc/d712815127.html,o) --（7）如果学生“张三”的数据结构的成绩低于，则加上分。update sc

set grade=grade+10

where exists

(

select sno

from student s

where s.sno=sc.sno and sname='张三'

and cno in

(

select cno

from course

where cname='数据结构'

)

--查看结果

select sname,cname,grade

from student s,course c,sc

where s.sno=sc.sno and https://www.sodocs.net/doc/d712815127.html,o=https://www.sodocs.net/doc/d712815127.html,o and sname='张三'and cname='数据结构'

--（8）将成绩最低的学生的成绩加上分。

update sc

set grade=grade+10

where grade in

(

select min(grade)

from sc

)

--（9）将前名成绩最高的学生的成绩减去分。

update sc

set grade=grade-10

where grade in

(

select top 3 grade

from sc

order by grade desc

)

--（10）为没有选课的学生自动增加选修“”课程的选课记录。

insert into sc(sno,cno,grade)

select sno,'01',null

from student

where sno not in

(

select distinct(sno)--不显示重复值

from sc

)

select*from sc

--（11）把选修“数学”不及格的成绩全部改成空值。

update sc

set grade=null

where sno in

(

select sno

from course c

where https://www.sodocs.net/doc/d712815127.html,o=https://www.sodocs.net/doc/d712815127.html,o and cname='数学'and grade<60

)

--（12）删除成绩低于总平均成绩的女生的选课记录。

delete from sc--删除该女生成绩低于总平均成绩的该选修记录

where sno in

(

select s.sno

from student s

where sc.sno=s.sno and ssex='女'

group by s.sno,grade

having grade <(select avg(grade)from sc)

)

--法二

delete from sc--删除该女生成绩低于总平均成绩的该选修记录

where exists

(

select s.sno

from student s

where ssex='女'and grade<(select avg(grade)from sc)and sc.sno=s.sno )