WORD格式--可編輯--專業資料完整版學習資料分享SQL-Server實驗答案上海師范大學計算機系

目錄第一部分企業管理器的使用 3試驗一注冊服務器 3試驗二創建數據庫 3試驗三創建表 4實驗四數據輸入 5實驗五登錄到數據庫服務器 6第二部分SQL語言 7第二部分SQL語言 7試驗一數據庫創建 7試驗二創建表 7試驗三創建數據完整性 8試驗四數據完整性試驗 9試驗五索引 12試驗六更新數據 13試驗七Sql查詢語句 13試驗八視圖 15試驗九安全性控制實驗 15試驗十存儲過程 16試驗十二觸發器 17試驗十二恢復技術 19試驗十三事務 20試驗十四鎖 21

第一部分企業管理器的使用第二部分SQL語言試驗一數據庫創建目的：1掌握利用SQL語言進行數據庫的創建、維護。2sp_helpdb命令要求：1創建數據庫2修改數據庫3刪除數據庫一建立school數據庫1使用查詢分析器創建數據庫schoolCreateDataBaseschool2使用SP_helpdb查詢數據庫School的信息3使用SQL-Server的企業管理器查看數據庫school的信息。4記錄：1）school數據庫文件所在的文件夾。2）school數據庫的文件名二刪除School數據庫1使用查詢分析器刪除數據庫schoolDROPDATABASEschool2使用SQL-Server的企業管理器刪除數據庫school。三createDatabase深入研究1建立school數據庫,要求數據庫存儲在c:\data文件夾下，初始大小為5MB,增量為1MB。CREATEDATABASEschoolON(Name=‘school_dat’,Filename='c:\sqldata\school.mdf',SIZE=5,FILEGROWTH=1)2使用SQL-Server的企業管理器，將數據庫的每次增量改為20%。試驗二創建表目的：1掌握利用SQL語言創建表的方法。2sp_help命令要求：1創建表2修改表結構3刪除表一寫出使用CreateTable語句創建表student,sc,course的SQL語句。學生表、課程表、選課表屬于數據庫School，其各自得數據結構如下：學生Student(Sno,Sname,Ssex,Sage,Sdept)序號列名含義數據類型長度1Sno學號字符型(char)62Sname姓名字符型(varchar)83Ssex性別字符型(char)24Sage年齡整數(smallint)5sdept系科字符型(varchar)15課程表course(Cno,Cname,Cpno,Ccredit)序號列名含義數據類型長度1Cno課程號字符型(char)42cname課程名字符型(varchar)203Cpno先修課字符型(char)44Ccredit學分短整數(tinyint)學生選課SC(Sno,Cno,Grade)序號列名含義數據類型長度1Sno學號字符型(char)42Cno課程名字符型(char)63Grade成績小數(decimal)12,1二把創建表的sql語句的腳本存儲到文件school.sql。createtableStudent(Snochar(6),Snamechar(10),Ssexchar(2),Sagesmallint,Sdeptchar(10),)createtablecourse(Cnochar(4),Cnamechar(16),Cpnochar(4),Ccreditint,)createtableSC(Snochar(6),Cnochar(4),Gradeint)三使用SP_HELP查看表student的表結構利用企業管理器查看表sc的表結構四利用sql語句表結構修改1在student表中添加列：家庭地址address長度為60varchar型入學日期inDate日期型ALTERTABLEstudentADDaddressvarchar(60)ALTERTABLEstudentADDinDatedatetime完成后用sp_help查看是否成功。2將家庭地址address長度為50ALTERTABLEstudentALTERCOLUMNvarchar(50)完成后用sp_help查看是否成功。3刪除student表的inDate列ALTERTABLEstudentDROPCOLUMNinDate五刪除表1刪除表sc2刪除表student3刪除表course試驗三創建數據完整性目的：1掌握創建數據完整性約束的命令。2掌握完整性約束的修改、刪除。要求：1能建立完整性約束2修改完整性約束3刪除完整性約束一寫出帶有完整性約束的CreateTable命令建立表student、course、sc。要求：1Student表的主碼：snostudent的約束：姓名不可為空，且唯一性別不能為空且取值范圍為{男，女}年齡大于16歲sdept默認為‘JSJ’系2Course表的主碼：cnocourse的約束：Ccredit取值范圍{0,1,2,3,4,5}課程表的每一行的Cno與cpno不可相同3Sc表的主碼：sno，cno。主碼名為PK_SCSc的外碼：外碼：SC表的sno參照表student的sno外碼：sc表的Cno參照表course的cno4把上述創建表的sql語句的腳本存儲到文件createSchool.sql。createtableStudent(Snochar(6),Snamechar(10)notnullunique,Ssexchar(2)check(ssex='男'orssex='女'),Sagesmallintcheck(sage>16),Sdeptchar(10)notnulldefault'JSJ',primarykey(sno))createtablecourse(Cnochar(4),Cnamechar(16),Cpnochar(4),Ccreditintcheck(Ccredit>=0andCcredit<=5),check(cno<>cpno),--約束primarykey(cno))createtableSC(Snochar(6),Cnochar(4),Gradeintcheck(grade<=100),constraintpk_scprimarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno),)二使用SP_HELP查看表student的主碼名，約束名，并記錄。使用SP_HELP查看表sc的主碼名，外碼名，并記錄。三利用altertable添加、刪除完整性約束1刪除SC的主碼，sc表的主碼名為pk_scALTERTABLEscDROPpk_sc復習在Sql-Server企業管理器中如何完成。2刪除SC表參照course表的外碼。如何知道SC表參照course表的外碼的名字。1)SP_HELPcourse找到外碼名,假設為：fk_sc_cno_029382)ALTERTABLEscDROPfk_sc_cno_029383添加SC表的主碼。主碼名為PK_SCALTERTABLEscADDCONSTRAINTpk_scPRIMARYKEY(sno,cno)

4添加SC表的Cno的外碼，參照表Course的Cno.ALTERTABLEscADDFOREIGNKEY(cno)REFERENCEScourse(cno)5加自定義約束：表SC的成績只能在0–100分之間。ALTERTABLEscADDcheck(grade>=0andgrade<=100)四使用Sql-Server企業管理器完成：1刪除SC表參照course表的外碼。2建立SC表的Cno的外碼，參照表Course的Cno.*使該外碼具有級聯修改的功能。3刪除表SC的成績只能在0–100分之間的約束.3加自定義約束：表SC的成績只能在0–100分之間。五使用select*fromstudent查看信息select*fromcourse查看信息select*fromsc查看信息試驗四數據完整性試驗目的：1理解實體完整性、參照完整性、用戶自定義完整性的作用2特別掌握外碼的作用。要求：記錄試驗中遇到的問題，并寫出原因。實驗前需要利用試驗三完成的腳本文件createSchool.sql，重新建立數據庫school。一實體完整性1student表數據輸入學號姓名性別年齡系科3001趙達男20SX3002楊麗女21JSJ3001李寅女21SX輸入上述數據，記錄出現的問題，說明原因。第三行不能輸入。Student的主碼為sno,因此sno列的值不能重復。select*fromstudent查看你輸入了幾行數據。2course表數據的輸入CnoCnameCpnoCcredit1081電子商務43SC表數據的輸入SnoCnoGrade30011081903001108179輸入上述數據，記錄出現的問題，說明原因。二用戶自定義完整性約束表student有用戶自定義約束：性別不能為空且取值范圍為{男，女}年齡大于16歲表course的自定義約束：Ccredit取值范圍{0,1,2,3,4,5}課程表的每一行的Cno與cpno不可相同1student表數據輸入學號姓名性別年齡系科3005趙達男14SX3006楊麗南21JSJ輸入上述數據，記錄出現的問題，說明原因。3005學生不能輸入，年齡問題，有約束sage>163006學生不能輸入，性別不對。select*fromstudent查看你輸入了那些數據。2course表數據的輸入CnoCnameCpnoCcredit1085C++91086語文10863輸入上述數據，記錄出現的問題，說明原因。1086不能輸入，因為有約束check(cno<>cpno)select*fromstudent查看你輸入了那些數據。3SC表數據的輸入SnoCnoGrade30021081128輸入上述數據，記錄出現的問題，說明原因。3002這條數據不能輸入，因為grade不能大于100分select*fromstudent查看你輸入了那些數據。

三參照完整性約束掌握表之間建立外碼后，對被參照表的如下操作會有何影響：修改主碼、插入新行、刪除新行？對參照表添加新行、刪除行、修改外碼值有何影響？掌握級聯修改、級聯刪除的概念。注意：表SC的Sno是外碼，參照student的sno。表SC的Cno是外碼，參照course的cno。1輸入實驗前的數據學生表StudentSnoSnameSsexSageSdept4001趙尹男20SX4002楊開女20JSJ課程表courseCnoCnameCpnoCcredit1088Java51089數學3學生選課SCSnoCnoGrade400110889040021088862試驗過程1)在SC表中添加新行：SnoCnoGrade4001106676記錄試驗結果.,寫出出現此結果的原因.不能添加，因為在cno是外碼，參照course的cno,但在course中沒有1066課程。2)在student表中添加新行SnoSnameSsexSageSdept4003趙輝男21SX記錄試驗結果.,寫出出現此結果的原因.可以輸入3)刪除student表的4001，4002學生記錄試驗結果.,寫出出現此結果的原因.兩個學生不能被刪除，因為sc的外碼sno參照student的sno,sc中已經有4001，4002學生的數據，因此不能刪除。思考：刪除SC表的記錄有限制嗎？沒有采取什么技術能使不能成功執行的命令變得可以執行，且使數據庫保持數據完整性。級聯刪除4)把student表的學號4003改為4018,4001改為4021。記錄試驗結果.,寫出出現此結果的原因.4003可以改為4018,4001不能改為4021因為sc的外碼sno參照student的sno,sc中已經有4001的數據，但沒有4003的選課數據。思考：采取什么技術能使本題不能執行的命令可以執行，且使數據庫保持數據完整性。級聯修改5)把sc表中的如下記錄的學號從4001改為4011。SnoCnoGrade4001108890記錄試驗結果.,寫出出現此結果的原因.不能修改，因為sc的外碼sno參照student的sno,4011在student中不存在。如不成功，則可以采取什么方法來實現此要求。需要在student表中添加4011學生。如不成功，那么把4001修改為4003，能成功嗎？能成功！思考:參照完整性規則中,外碼可以為空,但SC表中的外碼可以為空嗎?為什么?舉一個外碼可以為空的例子。不可以，因為sc表的主碼為sno+cno,即sno,cno為主屬性，所以不能為空。試驗五索引目的：掌握索引的建立、刪除的方法。一創建索引1建student的索引為姓名建立索引，索引名：Ix_student_sname為系科建立索引，索引名：Ix_student_sdeptcreateindexix_student_snameONstudent(sname)createindexix_student_sdeptONstudent(sdept)2SC的索引為課程號建立索引：ix_sc_cnocreateindexix_sc_cnoONsc(cno)3Course的索引為課程名建立唯一性索引：Ix_course_cnamecreateuniqueindexix_course_cnameONcourse(cname)4如何SP_HELP查看索引剛才建立的索引？如何在企業管理器中查看索引？二刪除索引course表的索引IX_course_cnameDROPINDEXcourse.ix_course_cname三思考：如何把索引IX_student_sname修改為唯一性索引？可以使用企業管理器或先刪除索引，再重新建立。*四思考建立索引的目的1輸入下列存儲過程，該程序生成大量數據供測試：createprocedureusp_makedataasdeclare@nCntint,@sNovarchar(6),@snamevarchar(8)set@nCnt=12000--計數器while@nCnt<999999beginset@nCnt=@nCnt+1set@sNo=convert(varchar(6),@nCnt)set@sName='張'+@snoinsertintostudent(sno,sname,ssex,sage)values(@sno,@sname,'男',20)endreturn2execusp_makedata--生成測試數據3輸入下述測試程序：createprocedureusp_testasdeclare@nCountint,@dataintset@nCount=0while@nCount<100beginselect@data=count(*)fromstudentwheresname<'張3800'orsname>'張8800'set@nCount=@nCount+1end4測試1）建立姓名的索引，查看運行時間(8秒).createindexix_student_snameonstudent(sname)--建立索引execusp_test2)刪除姓名索引，查看運行時間（2分11秒），比較與1）的時間長短。dropindexstudent.ix_student_sname--刪除索引execusp_test試驗六更新數據目的：掌握insert,update,delete語句的使用。一insert1寫出把下述學生的信息添加到student表中的命令。學號姓名性別年齡系科4001趙茵男20SX4002楊華女21Insertintostudent(sno,sname,ssex,sage,sdept)values(‘4001 ‘,’趙茵’,’男’,20,’ SX’)Insertintostudent(sno,sname,ssex,sage)values(‘4002 ‘,’楊華’,’女’,21)2批量插入數據1）建立一個新表sc_name，有屬性sno,sname,ssex,cno,grade。CREATETABLEsc_name(Snochar(6),Snamevarchar(20),Ssexchar(2),cnochar(4),gradeint)2）把SX系學生的sno,sname,ssex,cno,grade插入到表sc_name中。Insertintosc_name(sno,sname,ssex,cno,grade)selectstudent.sno,sname,ssex,cno,gradefromstudent,scwherestudent.sno=sc.snoandsdept=’SX’3）察看sc_name表的數據select*fromsc_name二Update1修改0001學生的系科為:JSJUpdatestudentsetsdept=’JSJ’wheresno=’0001’2把陳小明的年齡加1歲，性別改為女。Updatestudentsetsage=sage+1,ssex=’女’wheresname=’陳小明’3修改李文慶的1001課程的成績為93分updatescsetgrade=93wherecno=’1001’andsnoin(selectsnofromstudentwheresname=’李文慶’)4把“數據庫原理”課的成績減去1分updatescsetgrade=grade-1wherecnoin(selectcnofromcoursewherecname=’數據庫原理’)三Delete1刪除所有JSJ系的男生 deletefromstudentwheresdept=’JSJ’2刪除“數據庫原理”的課的選課紀錄Deletefromscwherecnoin(selectcnofromcoursewherecname=’數據庫原理’)思考：修改數據的命令與修改表結構的命令有何區別？試驗七Sql查詢語句目的:掌握Select查詢語句。一單表1查詢年齡在19至21歲之間的女生的學號,姓名,年齡,按年齡從大到小排列。selectsno,sname,sagefromstudentwheresagebetween19and21andssex=’女’orderbysagedesc2查詢姓名中第戎2個字為“明”字的學生學號、性別。selectsname,ssexfromstudentwheresnamelike‘_明%’3查詢1001課程沒有成績的學生學號、課程號selectsno,cnofromscwheregradeisnullandcno=’1001’4查詢JSJ、SX、WL系的學生學號,姓名，結果按系及學號排列selectsno,snamefromstudentwheresdeptin(‘JSJ’,’SX’,’WL’)orderbysdept,sno5按10分制查詢學生的sno,cno,10分制成績（1-10分為1，11-20分為2，30-39分為3，。。。90-100為10）selectsno,cno,grade/10.0+1aslevelfromsc6查詢student表中的學生共分布在那幾個系中。（distinct）selectdistinctsdeptfromstudent7查詢0001號學生1001，1002課程的成績。Selectcnofromscwheresno=’0001’and(cno=’1001’orcno=’1002’)二統計1查詢姓名中有“明”字的學生人數。selectcount(*)fromstudentwheresnamelike‘%明%’2計算‘JSJ’系的平均年齡及最大年齡。Selectavg(sage),max(sage)fromstudentWheresdept=’JSJ’3計算每一門課的總分、平均分，最高分、最低分，按平均分由高到低排列selectcno,sum(grade),avg(grade),max(grade),min(grade)fromscgroupbycnoorderbyavg(grade)desc4計算1001,1002課程的平均分。Selectcno,avg(grade)fromscwherecnoin(‘1001’,’1002’)Groupbycno5查詢平均分大于80分的學生學號及平均分selectsc.sno,avg(grade)fromscgroupbysc.snohavingavg(grade)>806統計選修課程超過2門的學生學號selectsnofromscgroupbysnohavingcount(*)>27統計有10位成績大于85分以上的課程號。Selectcnofromscwheregrade>85groupbycnohavingcount(*)=108統計平均分不及格的學生學號selectsnofromscgroupbysnohavingavg(grade)<609統計有大于兩門課不及格的學生學號selectsnofromscwheregrade<60groupbysnohavingcount(*)>2三連接1查詢JSJ系的學生選修的課程號selectcnofromstudent,scwherestudent.sno=sc.snoandsdept=’JSJ’2查詢選修1002課程的學生的學生姓名(不用嵌套及嵌套2種方法）a:selectsnamefromstudent,scwherestudent.sno=sc.snoandcno=’1002’b:selectsnamefromstudentwheresnoin(selectsnofromscwherecno=’1002’)3查詢數據庫原理不及格的學生學號及成績selectsno,gradefromsc,coursewhereo=oandcname=’數據庫原理’4查詢選修“數據庫原理”課且成績80以上的學生姓名(不用嵌套及嵌套2種方法）a:selectsnamefromstudent,sc,coursewherestudent.sno=sc.snoando=oandgrade>80andcname=’數據庫原理’b:selectsnamefromstudentwheresnoin(selectsnofromscwheregrade>80andcnoin(selectcnofromcoursewherecname=’數據庫原理’))5查詢平均分不及格的學生的學號，姓名,平均分。selectsno,max(sname),avg(grade)asavggradefromsc,studentwherestudent.sno=sc.snogroupbystudent.snohavingavg(grade)<606查詢女學生平均分高于75分的學生姓名。A:Selectsnamefromstudentwheressex=’女’andsnoin(Selectsnofromscgroupbysnohavingavg(grade)>75)B:Selectmax(sname)fromsc,studentwherestudent.sno=sc.snoandSsex=’女’Groupbystudent.snohavingavg(grade)>757查詢男學生學號、姓名、課程號、成績。(一門課程也沒有選修的男學生也要列出，不能遺漏)selectstudent.sno,sname,cno,gradefromstudentleftjoinscONstudent.sno=sc.snoandssex=’男’四嵌套、相關及其他1查詢平均分不及格的學生人數selectcount(*)fromstudentwheresnoin(selectsnofromscgroupbysnohavingavg(grade)<60)2查詢沒有選修1002課程的學生的學生姓名selectsnamefromstudentwheresnonotin(selectsnofromscwherecno=’1002’)student0001aaX0002bb0003ccXsc00011001000110020002100100031002selectsnamefromstudentwherenotexists(select*fromscwherecno=’1002’andsc.sno=student.sno)3查詢平均分最高的學生學號及平均分（2種方法TOP,any,all）a:selecttop1sno,avg(grade)fromscgroupbysnoorderbyavg(grade)descB:selectsno,avg(grade)fromscgroupbysnohavingavg(grade)=(selecttop1avg(grade)fromscgroupbysnoorderbyavg(grade)desc)c:selectsno,avg(grade)fromscgroupbysnohavingavg(grade)>=all(selectavg(grade)fromscgroupbysno)*4查詢沒有選修1001，1002課程的學生姓名。Selectsnamefromstudentwherenotexists(Select*fromcoursewherecnoin(‘1001’,’1002’)andNotexists(select*fromscwheresno=student.snoandcno=o))5查詢1002課程第一名的學生學號（2種方法）a:selecttop1snofromsccno=’1002’orderbygradedescb:selectsnofromscwherecno=’1002’andgrade>=all(selectgradefromscwherecno=’1002’)6查詢平均分前三名的學生學號selecttop3snofromscgroupbysnoorderbyavg(grade)desc7查詢JSJ系的學生與年齡不大于19歲的學生的差集a:select*fromstudentwheresdept=’JSJ’andsage>19b:select*fromstudentwheresdept=’JSJ’exceptselect*fromstudentwheresage<198查詢1001號課程大于90分的學生學號、姓名及平均分大于85分的學生學號、姓名selectstudent.sno,snamefromstudent,scwherecno=’1001’andgrade>90unionselectsno,snamefromstudentwheresnoin(selectsnofromscgroupbysnohavingavg(grade)>85)9查詢每門課程成績都高于該門課程平均分的學生學號selectsnofromstudentwheresnonotin(selectsnofromscXwheregrade<(selectavg(grade)fromscYwhereY.sno=X.sno))selectsnofromstudentwheresnonotin(selectsnofromscXwheregrade<(selectavg(grade)fromscwherecno=X.cno))10查詢大于本系科平均年齡的學生姓名selectsnamefromstudentXwheresage>(selectavg(sage)fromstudentywheresdept=x.sdept)試驗八視圖目的:掌握視圖的建立、使用。1建立學生學號、姓名、性別、課程號、成績的視圖v_sc查看V_sc中的數據。Createviewv_sc(sno,sname,ssex,cno,grade)asSelectstudent.sno,sname,ssex,cno,gradefromstudent,scWherestudent.sno=sc.snoSelect*fromv_sc1建立學生學號、姓名、出生年月的視圖v_age查看V_age中的數據。Createviewv_age(sno,sname,sbirth)asSelectsno,sname,2008–sagefromstudentSelect*fromv_age2建立‘JSJ’系的學生學號、姓名、性別、年齡的視圖V_JSJCreateviewv_jsj(sno,sname,ssex,sage)asSelectsno,sname,ssex,sagefromstudentwheresdept=’JSJ’3建立每門課程的平均分的視圖V_avggradeCreateviewv_avgGrade(cno,grade1)asSelectcno,avg(grade)fromscgroupbycno4將視圖v_jsj中李文慶的年齡改為21歲Updatev_jsjsetsage=sage+1wheresname=’李文慶’5察看student中李文慶的年齡查看v_age中李文慶的出生年月Select*fromstudentwheresname=’李文慶’Select*fromv_agewheresname=’李文慶’6查詢每門課程的及格率Createviewv1(cno,cnt1)asSelectcno,count(*)fromscgroupbycnoCreateviewv2(cno,cnt1)asSelectcno,count(*)fromscwheregrade>=60groupbycnoSelecto,cnt2*1.0/cnt1fromv1,v2whereo=o思考:1利用V_JSJ視圖,可以更新SX的學生的年齡嗎?寫出理由如:updatev_jsjsetsage=25wheresno=’0004’0004號學生為SX系.試驗九安全性控制實驗目的:掌握Sql-server的授權機制.1）建立新用戶mary,密碼1234Sp_addLogin‘mary’,‘12342)授予mary可以訪問School數據庫的權力選擇school數據庫Sp_grantDBaccessmary3)以mary登錄sql-server,執行select*fromstudent,記錄執行結果，說明原因。無法查到數據，因為mary沒有查詢student的權限。4）將course的查詢、更改權限授予maryGrantselect,updateoncoursetomary5）把查詢student表和修改學生學號的權限授予用戶mary,且他能將此權限轉授他人。Grantselect,update(sno)onstudenttomarywithgrantoption6)把對course表的更改權限從mary收回Revokeupdateoncoursefrommary7)把第5）小題授予mary的權限收回。revokeselect,update(sno)onstudentfrommarycascade8）mary只能查詢‘1001’號課程的學生成績,請問如何授權Createviewv_sc1(sno,cno,grade)asSelectsno,cno,gradefromscwherecno=’0001Grantselectonv_sc1tomary思考:1sp_addlogin,sp_grantdbaccess語句的區別.2如有200個人需要授權，SQL-SERVER如何簡化授權機制。試驗十存儲過程目的:掌握存儲過程的概念、編程及使用1編寫一個存儲過程usp_avgage,向客戶端返回每個系科的學生平均年齡。系科平均年齡JSJ21SX20。。。1)編寫存儲過程的代碼Createprocedureusp_avgageasSelectsdept,avg(sage)fromstudentgroupbysdept2）調試、運行該存儲過程。Usp_avgage2編寫一個存儲過程usp_sdept,傳入一個系科代碼，返回該系的平均年齡，人數Createprocedureusp_sdept@deptchar(10)asSelectavg(sage),count(*)fromstudentwheresdept=@dept3編寫存儲過程usp_updateGrade,傳入參數為課程號,處理邏輯:對傳入的這門課,進行如下處理:如某學生該門課成績>80,則加2分如某學生該門課成績>60,則加1分如某學生該門課成績<=60,則減1分并且返回此門課的每個學生的最新成績:學號成績.Createprocedureusp_updateGrade@cnochar(4)asUpdatescsetgrade=grade+2wherecno=@cnoandgrade>80Updatescsetgrade=grade+1wherecno=@cnoandgradebetween60and80Updatescsetgrade=grade-1wherecno=@cnoandgrade<=80Selectsno,gradefromscwherecno=@cnoreturn5編寫存儲過程usp_comp_age,比較0001，0002學生的年齡的高低，輸出：XXXX學生的年齡大注意：XXXX為學生的姓名Createprocedureusp_comp_ageasdeclare@age1int,@age2intdeclare@name1char(10),@name2char(10)--臨時存儲兩個人的姓名select@age1=sage,@name1=snamefromstudentwheresno=’0001select@age2=sage,@name2=snamefromstudentwheresno=’0002if@age1>@age2print@name1+‘學生的年齡大’elseprint@name2+‘學生的年齡大’return7編寫存儲過程usp_comp_age1,比較兩個學生的年齡的高低，兩個學生的學號有參數輸入，最后輸出：XXXX學生的年齡大。注意：XXXX為學生的姓名Createprocedureusp_comp_age1@no1char(6),@no2char(6)asdeclare@age1int,@age2intdeclare@name1char(10),@name2char(10)--臨時存儲兩個人的姓名select@age1=sage,@name1=snamefromstudentwheresno=@no1select@age2=sage,@name2=snamefromstudentwheresno=@no2if@age1>@age2print@name1+‘學生的年齡大’elseprint@name2+‘學生的年齡大’return10編寫存儲過程usp_comp_age2,比較兩個學生的年齡的高低，兩個學生的學號有參數輸入，最后把年齡大的學生的姓名、性別返回客戶端。Createprocedureusp_comp_age1@no2char(6),@no2char(6)asdeclare@age1int,@age2intdeclare@name1char(10),@name2char(10)--臨時存儲兩個人的姓名select@age1=sage,@name1=snamefromstudentwheresno=@no1select@age2=sage,@name2=snamefromstudentwheresno=@no2if@age1>@age2selectsname,ssexfromstudentwheresno=@no1elseselectsname,ssexfromstudentwheresno=@no2return12編寫存儲過程usp_t1，傳入參數為學號,把該學號的課程1001的成績減到58分。每次只能減1分，用循環完成。createprocedureusp_t1@nochar(6)asdeclare@ageintset@age=100while@age>58BEGINSELECT@age=sagefromstudentwheresno=@noIf@age>58Updatesage=sage-1wheresno=@noENDRETURN--以下不需要4編寫存儲過程usp_disp,傳入參數為課程號,處理邏輯:返回每個學生的成績等級。成績>=90為優,成績>=80為良,成績>=70為中,成績>=60為及格,成績<=60為不及格。返回結果如下：學號課程號成績等第0001100191優0001100278中……………..createprocedureudp_disp@cnochar(4)as--建立臨時表存儲結果createtable#tmp(snochar(4),cnochar(4),gradeint,levelchar(6))--建立某門課程的游標declarecur1cursorforselectsno,gradefromscwherecno=@cnodeclare@snochar(4),@nGradeintdeclare@sLevelchar(6)--臨時存儲某學生的成績等級opencur1fetchnextfromcur1into@sno,@nGrade--讀出游標第一行數據while@@fetch_status=0begin--處理一行數據if@nGrade>=90set@sLevel=‘優’elseif@nGrade>=80set@sLevel=‘良’elseif@nGrade>=70set@sLevel=‘中’elseif@nGrade>=80set@sLevel=‘及格’elseset@sLevel=‘不及格’--把結果寫入臨時表insertinto#tmp(sno,cno,grade,level)values(@sno,@cno,@nGrade,@sLevel)fetchnextfromcur1into@sno,@nGrade--讀出游標下一行數據endclosecur1dealLocatecur1select*from#tmp--返回結果給客戶端droptable#tmp--刪除臨時表return5編寫一個存儲過程，傳入參數為學號，執行后，把該學號的學生按如下格式輸出成績：（注意：只有一行）學號姓名1001課程1002課程1003課程平均分6編寫一個存儲過程，傳入參數為系科，執行后，把該系科的學生按如下格式輸出學生成績：學號姓名1001課程1002課程1003課程平均分createprocedureusp_grade@deptchar(15)ascreatetable#tmp(snochar(4),snamechar(10),g1intnull,g2intnull,g3intnull,pjintnull)declare@nochar(4),@namechar(10),@nG1int,@nG2int,@nG3intdeclarecur1cursorforselectsno,snamefromstudentwheresdept=@dept--游標某一個系的學生opencur1fetchnextfromcur1into@no,@namewhile@@fetch_status=0beginselect@nG1=gradefromscwheresno=@noandcno='1001'select@nG2=gradefromscwheresno=@noandcno='1002'select@nG3=gradefromscwheresno=@noandcno='1003'insertinto#tmp(sno,sname,g1,g2,g3,pj)values(@no,@name,@nG1,@nG2,@nG3,(@nG1+@nG2+@nG3)/3)fetchnextfromcur1into@no,@nameendclosecur1dealLocatecur1select*from#tmpdroptable#tmp--執行usp_grade'JSJ'7編寫存儲過程，統計男女生1001，1002，1003各自的選修人數，輸出格式如下：性別1001人數1002人數1003人數小計男35210女2417合計59317（數據為示意數據）createprocedureusp_tjascreatetable#tmp(ssexchar(2),rs1int,rs2int,rs3int,xjint)declare@nRs1int,@nRs2int,@nRs3intselect@nRs1=count(*)fromstudent,scwherecno=’1001’andssex=’男select@nRs2=count(*)fromstudent,scwherecno=’1002’andssex=’男select@nRs3=count(*)fromstudent,scwherecno=’1003’andssex=’男insertinto#tmp(ssex,rs1,rs2,rs3,xj)values(‘男’,@nRs1,@nRs2,@nRs3,@nRs1+@nRs2+@nRs3)select@nRs1=count(*)fromstudent,scwherecno=’1001’andssex=’女select@nRs2=count(*)fromstudent,scwherecno=’1002’andssex=’女select@nRs3=count(*)fromstudent,scwherecno=’1003’andssex=’女insertinto#tmp(ssex,rs1,rs2,rs3,xj)values(‘女’,@nRs1,@nRs2,@nRs3,@nRs1+@nRs2+@nRs3)select*from#tmpdroptable#tmpreturn8編寫一個存儲過程，利用存儲過程的參數返回數據庫服務器上的日期時間。思考：何時需要存儲過程？試驗十二觸發器目的:了解觸發器的機制及編程設計、使用一建立學生表的觸發器usp_addstudent，當增加學生時，SX系的學生不能超過30歲。1寫出觸發器2執行下列語句塊：begintraninsertintostudent(sno,sname,ssex,sage,sdept)values(‘0701’,’劉歡’,’男’,26,’SX’)if@@error=0commitelserollbackend觀察該學生是否加入到student3執行下列語句塊：begintraninsertintostudent(sno,sname,ssex,sage,sdept)values(‘0702’,’趙歡’,’男’,31,’SX’)if@@error=0commitelserollbackend觀察該學生是否加入到student二實現下列觸發器1不能刪除年齡大于25歲的學生記錄。createtriggerutr_student1onstudentfordeleteasdeclare@nCntint--存儲被刪除的大于25歲的人數select@nCnt=count(*)fromdeletedwheresage>25if@nCnt>0beginraiserror('不能刪除大于25歲的學生',16,10)rollbacktransactionend--測試insertintostudentvalues('8701','aa1','男',27,'JSJ')--不能被刪除insertintostudentvalues('8702','bb1','男',24,'JSJ')--能刪除select*fromstudentwheresnoin('8701','8702')deletefromstudentwheresno='8701'select*fromstudentwheresnoin('8701','8702')deletefromstudentwheresno='8702'2建立觸發器usp_delcourse,使課程表中1001，1002，1003三門課不會被刪除。注意如何調試。createtriggerutr_deleteCourseoncoursefordeleteasdeclare@nCntintselect@nCnt=count(*)fromdeletedwherecnoin(‘1001’,’1002’,’if@nCnt>0beginraiserror('不能刪除',16,10)rollbacktransactionendreturn調試：Deletefromcoursewherecno=’1001’Deletefromcoursewherecno=’1006’3對學生表建立一觸發器，使更改后的年齡只能比原值大createtriggerutr_student_update1onstudentforupdateasifnotupdate(sage)returndeclare@nCntintselect@nCnt=count(*)frominserted,deletedwheredeleted.sno=inserted.snoandinserted.sage<deleted.sageif@nCnt>0beginraiserror('更改后的年齡比原值小了',16,10)rollbacktransactionend4對sc表建立觸發器，使‘JSJ’系的學生不可選擇‘1004’號課程createtriggerutr_chooseonscforinsertasdeclare@nCntint--存儲被刪除的大于25歲的人數select@nCnt=count(*)frominserted,studentwherestudent.sno=inserted.snoandsdept='JSJ'ando='1004'--inserted存儲insert命令添加的數據如0001,1004,90if@nCnt>0beginraiserror('JSJ不可選擇1004',16,10)rollbacktransactionend--測試insertintostudentvalues('8701','aa1','男',27,'JSJ')insertintosc(sno,cno,grade)values('8701','1001',90)--可以insertintosc(sno,cno,grade)values('8701','1004',90)--不可以select*fromscwheresno='8701'5對表course建觸發器，實現級聯刪除的功能，但某課選修人數大于3則不能刪除。(先刪除sc表對course的外碼)*三建立一個觸發器，使對sc表成績的修改自動記錄修改日志。日志文件表(tablog)記錄如下：用戶名學號課程號原成績修改后成績更改日期四在School數據庫中建立一個試驗用的發票表bill，然后為發票bill建立觸發器utr_money,實現當輸入單價和數量后，自動填寫金額，即發票金額不輸入，由單價、數量相乘后自動填寫到金額中。Createtablebill(billIDchar(8),--發票編號datedatetime,--開票日期productchar(10),--產品編號priceint,--單價qtyint,--數量chargeint,--金額primarykey(billid))思考:觸發器中inserted,deleted表的作用?在觸發器中如沒有用到此兩個表中的任何一個,你認為觸發器還有意義嗎?

試驗十二恢復技術目的：1掌握數據庫的備份及恢復的方法。2了解備份方案的設定一完全備份的建立與恢復1建立完全備份USEschoolGOBACKUPDATABASEschoolTODISK=’C:\schooldata.bak’2查看備份文件中的信息RESTOREFILELISTONLYFROMDISK=’c:\schooldata.bak’RESTOREHEADERONLYFROMDISK=’c:\schooldata.bak’3恢復完全備份1)先刪除數據庫SchoolUSEMasterGODROPDATABASEschool2)然后恢復.RESTOREDATABASEschoolfromDISK=’c:\schooldata.bak’3):查看school的student中的數據二建立差異備份1建立備份1）制作數據文件備份schoolDiff.bak2）把學號7001，姓名：王海，性別：男，年齡為23的學生加入student3）制作school的差異備份，存入schoolDiff.bakBACKUPDATABASEschoolTODISK=’schoolDiff.bak’WITHDIFFERENTIAL4)把學號7002，姓名：趙燕，性別：女,年齡為22的學生加入student5)制作school的差異備份，存入schoolDiff.bakBACKUPDATABASEschoolTODISK=’schoolDiff.bak’WITHDIFFERENTIAL2查看備份文件schoolDiff.bak中的信息3刪除school數據庫4恢復數據庫school到第2步狀態RESTOREDATABASEschoolfromDISK=’c:\schoolDiff.bak’WITHfile=1NORECOVERYRESTOREDATABASEschoolfromDISK=’c:\schoolDiff.bak’WITHfile=2Select*fromstudent觀察student數據5恢復數據庫school到最新狀態RESTOREDATABASEschoolfromDISK=’c:\schoolDiff.bak’WITHfile=1NORECOVERYRESTOREDATABASEschoolfromDISK=’c:\schoolDiff.bak’WITHfile=3Select*fromstudent觀察student數據思考:如果僅執行下述恢復語句,能查看student的數據嗎?RESTOREDATABASEschoolfromDISK=’c:\schoolDiff.bak’WITHfile=1NORECOVERYSelect*fromstudent三利用日志備份1設置故障還原模型為：完全2建立備份1)制作數據文件備份schooldata1.bak2）把學號7003，姓名：王江，性別：男，年齡為23的學生加入student3)制作日志備份存入schoollog.bak4)把學號7004，姓名：趙蘭，性別：女,年齡為22的學生加入student5)制作日志備份存入schoollog.bak3觀察schoollog中的信息4刪除school數據庫5利用schooldata1.bak及schoollog.bak恢復數據庫school到最新狀態四使用企業管理器練習備份調度策略1對數據庫school每天上午8時進行一次數據庫完全備份2對數據庫school的每隔1分鐘備份進行一次差異備份。3手工啟動兩個備份作業4刪除school數據庫5利用1，2步的備份進行school的恢復。思考：如何把備份文件備份到另外一臺計算機上。五使用企業管理器練習數據庫的分離及附加六如何清除日志文件。七使用企業管理器練習數據庫的壓縮八把school備份到其他計算機上。試驗十三事務目的:1掌握并理解事務一理解rollback1在查詢分析器輸入下列語句并執行,記錄該學生的年齡。Select*fromstudentwheresno=’0001’202執行下列語句序列A:BEGINTRANsactionUpdatestudentsetsage=sage+1wheresno=’0001’Select*fromstudentwheresno=’0002’此事務結束了嗎？答：沒有執行：Select*fromstudentwheresno=’0001’記錄該學生的年齡。思考：student中的0001的年齡確實被更改了嗎?為什么?21更改了，因為在事務中執行了update語句4執行下列語句。ROLLBACKTRANsaction然后再執行：Select*fromstudentwheresno=