實(shí)驗(yàn)6 視圖的應(yīng)用.doc

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