SQL實驗三--數(shù)據的更新操作和視圖-實驗報告(共16頁)

1、實驗三 數(shù)據的更新操作和視圖 本實驗需要2學時。一、實驗目的 1、掌握視圖的創(chuàng)建、修改和刪除操作。2、掌握數(shù)據庫數(shù)據更新（增加、修改、刪除）操作。二、實驗內容和步驟1、視圖（1）建立名為UV_SC的視圖。該視圖可以讓我們看到每個學生的姓名、選修課程名和成績。（2）利用UV_SC視圖，查看平均成績在80分以上的學生姓名。（3）建立名為UV_Course的視圖，該視圖顯示每門課的課程名和選修該課程的學生人數(shù)。（4）修改視圖UV_Course，在視圖中增加一列，顯示選修該課程所有學生的平均成績。（5）建立名為UV_IS_Depart的視圖。該視圖可以用來查看“信息系”的學生的學號、姓名、年齡和系名。

2、并保證對該視圖的修改滿足系名為信息系的條件。（6）建立名為UV_Student的加密視圖。該視圖可以用來查看每個學生選修的課程門數(shù)和平均成績。（7）利用UV_Student視圖，查詢平均成績在80分以上的學生學號。（8）能否利用視圖UV_Student，修改某個學生的平均成績？（9）刪除視圖UV_Student。 2、數(shù)據更新 （1）向STUDENT表增加如下記錄：SnoSnameSsexSageSaddressSdept95011王一鳴男20福州市計算機系（2）將學號95011，成績80，以及課程表中的所有課程號插入到SC表中。（3）建立與表STUDENT同結構的空表STUD。然后利用INS

3、ERT命令把表STUDENT中年齡大于19的學生記錄添加到表STUD。（注意：可以使用select into語句實現(xiàn)同結構空表的創(chuàng)建，再使用insert命令插入記錄；或者使用單條select into語句實現(xiàn)等同的功能）（4）為“95002”的學生增加選修“操作系統(tǒng)”和“數(shù)據結構”這兩門課的選課記錄。（5）把課程名為“操作系統(tǒng)”的成績從成績表SC中刪除。（試用三種方式完成） （6）將女生的成績提高10%。（試用三種方式完成）（7）如果學生“張三”的數(shù)據結構的成績低于65，則加上10分。（8）將成績最低的學生的成績加上10分。（9）將前3名成績最高的學生的成績減去10分。（10）為沒有選課的學生

4、自動增加選修“01”課程的選課記錄。（11）把選修“數(shù)學”不及格的成績全部改成空值。（12）刪除成績低于總平均成績的女生的選課記錄。三、實驗環(huán)境四、調試過程五、實驗結果六、總結通過此次實驗：1. 我知道了視圖不是一張真實的表格，是一個虛擬的存在形式。2. 掌握了對視圖的創(chuàng)建（create or replace）：當視圖不存在時可使用create進行創(chuàng)建；而如果不確定視圖是否已存在，但此時又需要建立一張此名稱的視圖時，可使用replace創(chuàng)建，如果視圖名已存在也不會出現(xiàn)沖突，而是進行替換。3. 在創(chuàng)建視圖時如果使用了with check option表示在視圖上執(zhí)行UPDATEINSERT或DE

5、LETE操作時,要保證所修改的行滿足視圖定義中的謂詞條件,即SELECT查詢子句中的限定條件:如Where條件,這樣可以確保數(shù)據修改后仍可通過視圖看到修改的數(shù)據。4. 分清楚了對視圖結構的修改（alter）、對視圖數(shù)據的修改（update）。同時對視圖上的計算列、內置函數(shù)列和行集合函數(shù)列不能修改。5. 知道了對視圖數(shù)據的刪除操作（delete）。6. 通過第二題的訓練也使我對數(shù)據庫數(shù)據的更新語句有了更多的體會和認識。7. 第二題第五小題，要求三種方法，只寫出了兩種，第三種有點沒轍。而其他題目有的沒要求，因為在思考過程有別的思路，也有多寫了相關方法。附錄： -1、視圖-（1）建立名為UV_SC的

6、視圖。該視圖可以讓我們看到每個學生的姓名、選修課程名和成績。create view UV_SCasselect sname,cname,gradefrom student s,course c,scwhere s.sno=sc.sno and o=oselect * from UV_SC-（2）利用UV_SC視圖，查看平均成績在分以上的學生姓名。select snamefrom UV_SCgroup by snamehaving avg(grade)80-（3）建立名為UV_Course的視圖，該視圖顯示每門課的課程名和選修該課程的學生人數(shù)。create view UV_Courseassel

7、ect cname,count(*) as選修人數(shù)from course c,scwhere o=ogroup by cnameselect * from UV_Course-（4）修改視圖UV_Course，在視圖中增加一列，顯示選修該課程所有學生的平均成績。alter view UV_Courseasselect cname,count(*) as 選修人數(shù),avg(grade) as 平均成績from course c,scwhere o=ogroup by cnameselect * from UV_Course-（5）建立名為UV_IS_Depart的視圖。該視圖可以用來查看“信息系

8、”的學生的學號、姓名、年齡和系名。并保證對該視圖的修改滿足系名為信息系的條件。create view UV_IS_Departasselect sno,sname,sage,sdeptfrom student swhere sdept=信息系with check option select * from UV_IS_Depart-（6）建立名為UV_Student的加密視圖。該視圖可以用來查看每個學生選修的課程門數(shù)和平均成績。create view UV_Student(sno,course_no,avg_grade)with encryptionasselect sno,count(*),a

9、vg(grade)from scgroup by snoselect * from UV_Student-（7）利用UV_Student視圖，查詢平均成績在分以上的學生學號。select snofrom UV_Studentwhere avg_grade=80-（8）能否利用視圖UV_Student，修改某個學生的平均成績？update UV_Studentset avg_grade=70where sno=95001-（9）刪除視圖UV_Student。drop view UV_Student-2、數(shù)據更新-（1）向STUDENT表增加如下記錄：-SnoSnameSsexSageSaddre

10、ssSdept-95011王一鳴男20福州市計算機系insert into student(sno,sname,ssex,sage,saddress,sdept)values(95011,王一鳴,男,20,福州市,計算機系)alter table studentadd saddress varchar(50) null-修改表結構，增加saddress列select *from studentwhere sno=95011-（2）將學號，成績，以及課程表中的所有課程號插入到SC表中。insert into sc(sno,cno,grade)select 95011,cno,80from cou

11、rseselect * from sc-（3）建立與表STUDENT同結構的空表STUD。然后利用INSERT命令把表STUDENT中年齡大于的學生記錄添加到表STUD。（注意：可以使用select into語句實現(xiàn)同結構空表的創(chuàng)建，再使用insert命令插入記錄；或者使用單條select into語句實現(xiàn)等同的功能）-法一select * into STUDfrom studentwhere sage19-法二select * into STUDfrom studentwhere 1=0insert into STUD(sno,sname,sage,ssex,sdept,saddress)s

12、elect sno,sname,sage,ssex,sdept,saddress from studentwhere sage19select * from STUD-（4）為“”的學生增加選修“操作系統(tǒng)”和“數(shù)據結構”這兩門課的選課記錄。insert into sc(sno,cno,grade)select 95002,cno,null from coursewhere cname=操作系統(tǒng) or cname=數(shù)據結構select * from sc-（5）把課程名為“操作系統(tǒng)”的成績從成績表SC中刪除。（試用三種方式完成）-法一delete from sc where cno in(sel

13、ect cno from course where cname=操作系統(tǒng) )-法二delete from sc where exists(select cno from course cwhere cname=操作系統(tǒng) and o=o)-法三-（6）將女生的成績提高%。（試用三種方式完成）-法一update scset grade=grade*1.1where sno in(select snofrom studentwhere ssex=女)-法二update scset grade=grade*1.1where exists(select snofrom student swhere ss

14、ex=女 and s.sno=sc.sno)-法三update scset grade=grade*1.1where(select count(*)from student swhere s.sno=sc.sno and ssex=女)=(select count(*) from course where cno=o)-（7）如果學生“張三”的數(shù)據結構的成績低于，則加上分。update scset grade=grade+10where exists(select snofrom student swhere s.sno=sc.sno and sname=張三and cno in(select

15、 cnofrom coursewhere cname=數(shù)據結構)-查看結果select sname,cname,gradefrom student s,course c,scwhere s.sno=sc.sno and o=o and sname=張三 and cname=數(shù)據結構-（8）將成績最低的學生的成績加上分。update scset grade=grade+10where grade in(select min(grade)from sc)-（9）將前名成績最高的學生的成績減去分。update scset grade=grade-10where grade in(select top

16、 3 gradefrom scorder by grade desc)-（10）為沒有選課的學生自動增加選修“”課程的選課記錄。insert into sc(sno,cno,grade)select sno,01,null from studentwhere sno not in(select distinct(sno)-不顯示重復值from sc)select * from sc-（11）把選修“數(shù)學”不及格的成績全部改成空值。update scset grade=nullwhere sno in(select snofrom course cwhere o=o and cname=數(shù)學 and grade60)-（12）刪除成績低于總平均成績的女生的選課記錄。-法一delete from sc-刪除該女生成績低于總平均成績的該選修記錄where sno in(sel