新SQL-SERVER實驗練習(xí)答案

WORD格式--可編輯--專業(yè)資料完整版學(xué)習(xí)資料分享SQL-Server實驗答案上海師范大學(xué)計算機(jī)系

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

第一部分企業(yè)管理器的使用第二部分SQL語言試驗一數(shù)據(jù)庫創(chuàng)建目的：1掌握利用SQL語言進(jìn)行數(shù)據(jù)庫的創(chuàng)建、維護(hù)。2sp_helpdb命令要求：1創(chuàng)建數(shù)據(jù)庫2修改數(shù)據(jù)庫3刪除數(shù)據(jù)庫一建立school數(shù)據(jù)庫1使用查詢分析器創(chuàng)建數(shù)據(jù)庫schoolCreateDataBaseschool2使用SP_helpdb查詢數(shù)據(jù)庫School的信息3使用SQL-Server的企業(yè)管理器查看數(shù)據(jù)庫school的信息。4記錄：1）school數(shù)據(jù)庫文件所在的文件夾。2）school數(shù)據(jù)庫的文件名二刪除School數(shù)據(jù)庫1使用查詢分析器刪除數(shù)據(jù)庫schoolDROPDATABASEschool2使用SQL-Server的企業(yè)管理器刪除數(shù)據(jù)庫school。三createDatabase深入研究1建立school數(shù)據(jù)庫,要求數(shù)據(jù)庫存儲在c:\data文件夾下，初始大小為5MB,增量為1MB。CREATEDATABASEschoolON(Name=‘school_dat’,Filename='c:\sqldata\school.mdf',SIZE=5,FILEGROWTH=1)2使用SQL-Server的企業(yè)管理器，將數(shù)據(jù)庫的每次增量改為20%。試驗二創(chuàng)建表目的：1掌握利用SQL語言創(chuàng)建表的方法。2sp_help命令要求：1創(chuàng)建表2修改表結(jié)構(gòu)3刪除表一寫出使用CreateTable語句創(chuàng)建表student,sc,course的SQL語句。學(xué)生表、課程表、選課表屬于數(shù)據(jù)庫School，其各自得數(shù)據(jù)結(jié)構(gòu)如下：學(xué)生Student(Sno,Sname,Ssex,Sage,Sdept)序號列名含義數(shù)據(jù)類型長度1Sno學(xué)號字符型(char)62Sname姓名字符型(varchar)83Ssex性別字符型(char)24Sage年齡整數(shù)(smallint)5sdept系科字符型(varchar)15課程表course(Cno,Cname,Cpno,Ccredit)序號列名含義數(shù)據(jù)類型長度1Cno課程號字符型(char)42cname課程名字符型(varchar)203Cpno先修課字符型(char)44Ccredit學(xué)分短整數(shù)(tinyint)學(xué)生選課SC(Sno,Cno,Grade)序號列名含義數(shù)據(jù)類型長度1Sno學(xué)號字符型(char)42Cno課程名字符型(char)63Grade成績小數(shù)(decimal)12,1二把創(chuàng)建表的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的表結(jié)構(gòu)利用企業(yè)管理器查看表sc的表結(jié)構(gòu)四利用sql語句表結(jié)構(gòu)修改1在student表中添加列：家庭地址address長度為60varchar型入學(xué)日期inDate日期型ALTERTABLEstudentADDaddressvarchar(60)ALTERTABLEstudentADDinDatedatetime完成后用sp_help查看是否成功。2將家庭地址address長度為50ALTERTABLEstudentALTERCOLUMNvarchar(50)完成后用sp_help查看是否成功。3刪除student表的inDate列ALTERTABLEstudentDROPCOLUMNinDate五刪除表1刪除表sc2刪除表student3刪除表course試驗三創(chuàng)建數(shù)據(jù)完整性目的：1掌握創(chuàng)建數(shù)據(jù)完整性約束的命令。2掌握完整性約束的修改、刪除。要求：1能建立完整性約束2修改完整性約束3刪除完整性約束一寫出帶有完整性約束的CreateTable命令建立表student、course、sc。要求：1Student表的主碼：snostudent的約束：姓名不可為空，且唯一性別不能為空且取值范圍為{男，女}年齡大于16歲sdept默認(rèn)為‘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把上述創(chuàng)建表的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復(fù)習(xí)在Sql-Server企業(yè)管理器中如何完成。2刪除SC表參照course表的外碼。如何知道SC表參照course表的外碼的名字。1)SP_HELPcourse找到外碼名,假設(shè)為：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企業(yè)管理器完成：1刪除SC表參照course表的外碼。2建立SC表的Cno的外碼，參照表Course的Cno.*使該外碼具有級聯(lián)修改的功能。3刪除表SC的成績只能在0–100分之間的約束.3加自定義約束：表SC的成績只能在0–100分之間。五使用select*fromstudent查看信息select*fromcourse查看信息select*fromsc查看信息試驗四數(shù)據(jù)完整性試驗?zāi)康模?理解實體完整性、參照完整性、用戶自定義完整性的作用2特別掌握外碼的作用。要求：記錄試驗中遇到的問題，并寫出原因。實驗前需要利用試驗三完成的腳本文件createSchool.sql，重新建立數(shù)據(jù)庫school。一實體完整性1student表數(shù)據(jù)輸入學(xué)號姓名性別年齡系科3001趙達(dá)男20SX3002楊麗女21JSJ3001李寅女21SX輸入上述數(shù)據(jù)，記錄出現(xiàn)的問題，說明原因。第三行不能輸入。Student的主碼為sno,因此sno列的值不能重復(fù)。select*fromstudent查看你輸入了幾行數(shù)據(jù)。2course表數(shù)據(jù)的輸入CnoCnameCpnoCcredit1081電子商務(wù)43SC表數(shù)據(jù)的輸入SnoCnoGrade30011081903001108179輸入上述數(shù)據(jù)，記錄出現(xiàn)的問題，說明原因。二用戶自定義完整性約束表student有用戶自定義約束：性別不能為空且取值范圍為{男，女}年齡大于16歲表course的自定義約束：Ccredit取值范圍{0,1,2,3,4,5}課程表的每一行的Cno與cpno不可相同1student表數(shù)據(jù)輸入學(xué)號姓名性別年齡系科3005趙達(dá)男14SX3006楊麗南21JSJ輸入上述數(shù)據(jù)，記錄出現(xiàn)的問題，說明原因。3005學(xué)生不能輸入，年齡問題，有約束sage>163006學(xué)生不能輸入，性別不對。select*fromstudent查看你輸入了那些數(shù)據(jù)。2course表數(shù)據(jù)的輸入CnoCnameCpnoCcredit1085C++91086語文10863輸入上述數(shù)據(jù)，記錄出現(xiàn)的問題，說明原因。1086不能輸入，因為有約束check(cno<>cpno)select*fromstudent查看你輸入了那些數(shù)據(jù)。3SC表數(shù)據(jù)的輸入SnoCnoGrade30021081128輸入上述數(shù)據(jù)，記錄出現(xiàn)的問題，說明原因。3002這條數(shù)據(jù)不能輸入，因為grade不能大于100分select*fromstudent查看你輸入了那些數(shù)據(jù)。

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

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