實驗6 視圖的應(yīng)用.doc

實驗6 視圖的應(yīng)用實驗?zāi)康膶W(xué)會使用MS SQL SERVER 2000企業(yè)管理器和SQL語句創(chuàng)建、修改和刪除視圖，掌握通過視圖向基本表插入、刪除、更新數(shù)據(jù)的方法和步驟。知識點撥：1. 視圖是一張?zhí)摫?. 視圖的作用3. 視圖的種類4. 創(chuàng)建視圖的SQL語句5. 更新視圖的SQL語句6. 刪除視圖的SQL語句7. 通過視圖修改數(shù)據(jù)庫中數(shù)據(jù)的限制實驗內(nèi)容1. 行列子視圖的創(chuàng)建2. 基于多個基本表的視圖的創(chuàng)建3. 基于基本表和視圖的視圖的創(chuàng)建4. 帶表達式的視圖的創(chuàng)建5. 分組視圖的創(chuàng)建6. 刪除視圖7. 修改視圖8. 通過視圖修改數(shù)據(jù)庫數(shù)據(jù)實驗步驟1. 選擇數(shù)據(jù)庫JX；注意：以下操作均在查詢分析器定義，在企業(yè)管理器中查看和修改，并請驗證結(jié)果！2. 創(chuàng)建行列子視圖1） 創(chuàng)建視圖WY_Student，用于查詢外語系學(xué)生的基本信息；CREATE VIEW WY_StudentASSELECT *FROM StudentWHERE Sdept=外國語2） 創(chuàng)建視圖SS_Student，用于查詢學(xué)生的姓名、學(xué)號和總學(xué)分信息，要求屬性列中文顯示；CREATE VIEW SS_StudentASSELECT Sname 姓名,SC.Sno 學(xué)號,Total 總學(xué)分FROM Student3. 創(chuàng)建基于多個基本表的視圖1） 創(chuàng)建視圖XSXK，用于查詢每個同學(xué)的選課信息，包括學(xué)號、姓名、課程號、課程名和成績；CREATE VIEW XSXKASSELECT SC.Sno,Sname,SC.Cno,Cname,GradeFROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.CnoGROUP BY SC.Sno,Sname,SC.Cno,Cname,Grade2） 創(chuàng)建視圖JSSK，用于查詢每個教師的任課情況，包括教師號、教師名、職稱、所講授課程的課程號信息，并用中文列出各屬性名；CREATE VIEW JSSKASSELECT Teacher.Tno 教師號,Tname 教師名,Tprof 職稱,SC.Cno 課程號,Cname 課程名FROM Teacher,SC,CourseWHERE Teacher.Tno=SC.Tno AND SC.Cno=Course.CnoGROUP BY Teacher.Tno ,Tname ,Tprof ,SC.Cno ,Cname 3） 基于視圖WY_Student和SC，創(chuàng)建視圖SSS，用于查詢外語系學(xué)生的選課情況；CREATE VIEW SSSASSELECT WY_Student.Sno,Sname,CnoFROM WY_Student,SCWHERE SC.Sno=WY_Student.Sno4. 帶表達式的視圖的創(chuàng)建1） 創(chuàng)建視圖XSNL，用于查詢學(xué)生的學(xué)號、姓名和年齡；CREATE VIEW XSNL(Sno,Sname,Sage)ASSELECT Sno,Sname,year(getdate()-year(Sbirth)FROM Student2） 創(chuàng)建視圖XSZCJ，用于查詢選修了課程的學(xué)生的總評成績，包括學(xué)號、姓名、課程號，每門課程的總評成績計算公式為：成績*70%+（成績-10）*20%+10；CREATE VIEW XSZCJ(Sno,Sname,Cno,總評成績)ASSELECT Student.Sno,Sname,Cno,Grade*0.7+(Grade-10)*0.2+10FROM Student,SCWHERE Student.Sno=SC.Sno3） 創(chuàng)建視圖JSGZ，用于顯示教師的預(yù)發(fā)工資，包括教工號、姓名、職稱和按95%預(yù)發(fā)的工資額；（含有%的列名字可以用【】）CREATE VIEW JSGZ(Tno,Tname,Tprof,預(yù)發(fā)95%工資)ASSELECT Tno,Tname,Tprof,Tpay*0.95 FROM Teacher5. 分組視圖的創(chuàng)建1） 創(chuàng)建視圖XSPJCJ，用于查詢選修了課程的學(xué)生的成績，包括學(xué)號、姓名和所選課程的平均成績；CREATE VIEW XSPJCJ(Sno,Sname,平均成績)ASSELECT SC.Sno,Sname,AVG(Grade)FROM SC,StudentWHERE SC.Sno=Student.SnoGROUP BY SC.Sno,Sname2） 創(chuàng)建視圖JSRKMS，用于查詢教師的任課門數(shù)，包括教工號、教師名和任課門數(shù)；CREATE VIEW JSRKMS(Tno,Tname,任課門數(shù))ASSELECT Teacher.Tno,Tname,COUNT(DISTINCT(Cno)FROM Teacher,SCWHERE Teacher.Tno=SC.TnoGROUP BY Teacher.Tno,Tname3） 創(chuàng)建視圖KCPJCJ，用于查詢各門課程的平均成績，包括課程號、課程名、選課人數(shù)、平均成績；CREATE VIEW KCPJCJ(Cno,Cname,選課人數(shù),平均成績)ASSELECT Course.Cno,Cname,COUNT(*),AVG(Grade)FROM Course,SCWHERE Course.Cno=SC.CnoGROUP BY Course.Cno,Cname4） 創(chuàng)建視圖YXXS，用于查詢各門成績和平均成績都在85分以上的學(xué)生的學(xué)號、姓名及平均成績；CREATE VIEW YXXSASSELECT DISTINCT SC.Sno,Sname,AVG(Grade) 平均成績FROM Student,SCWHERE Student.Sno=SC.Sno AND SC.Sno NOT IN(SELECT SnoFROM SCWHERE Grade85)GROUP BY SC.Sno,Sname6. 刪除視圖1） 刪除視圖XSNL；DROP VIEW XSNL7. 通過視圖修改數(shù)據(jù)庫數(shù)據(jù)1） 查詢視圖WY_Student，然后使用INSERTINTO語句更新WY_Student視圖，再次查詢該視圖以及相關(guān)基表，檢查執(zhí)行結(jié)果；SELECT *FROM WY_StudentINSERTINTO WY_Student(Sno,Sname)VALUES (06122,王五)SELECT *FROM WY_StudentSELECT *FROM Student再次查詢的結(jié)果為，在基本表Student中插入了該記錄，但是在視圖WY_Student中并沒有插入該記錄，因為插入的記錄不滿足視圖WY_Student子查詢的條件。若是改為INSERT INTO WY_Student(Sno,Sname,Sdept)VALUES (06122,王五,外國語)則視圖WY_Student和基本表Student都插入了該記錄。2） 使用UPDATE 更新視圖WY_Student中的某個或某些屬性值，然后再次查詢該視圖以并查詢相關(guān)基表檢查更新結(jié)果。UPDATE WY_StudentSET Sname=王士武WHERE Sno=06003SELECT *FROM WY_StudentSELECT *FROM Student再次查詢的結(jié)果為，視圖WY_Student和基本表Student中的記錄都被修改了，即如果修改視圖中的記錄，那么基本表中的記錄相應(yīng)的也被修改。同樣的，若是修改了基本表中的記錄，那么由基本表導(dǎo)出的視圖中的記錄相應(yīng)也會被修改。3） 使用DELETE刪除視圖WY_Student中剛插入的記錄，然后查詢該視圖和相關(guān)基本表數(shù)據(jù)；DELETE FROM WY_StudentWHERE Sname=王五SELECT * FROM WY_StudentSELECT * FROM Student基本表和視圖中的記錄都被刪掉4） 修改視圖JSGZ，把職稱為副教授的預(yù)發(fā)工資改為該視圖預(yù)發(fā)工資的90%，查看執(zhí)行結(jié)果；UPDATE