新SQL-SERVER實(shí)驗(yàn)練習(xí)答案

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

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

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

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

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