實(shí)驗(yàn)三：交互式SQL語句的使用1、實(shí)驗(yàn)?zāi)康模?）掌握數(shù)據(jù)庫對象的操作過程，包括創(chuàng)建、修改、刪除（2）熟悉表的各種操作，包括插入、修改、刪除、查詢（3）熟練掌握常用SQL語句的基本語法2、實(shí)驗(yàn)平臺使用SQLServer提供的MicrosoftSQLServerManagementStudio工具，交互式使用SQL語句。3實(shí)驗(yàn)容及要求選擇如下一個(gè)應(yīng)用背景之一：學(xué)生選課系統(tǒng)習(xí)題3、4、和5中使用的數(shù)據(jù)庫其它你熟悉的應(yīng)用（1）建立一個(gè)數(shù)據(jù)庫和相關(guān)的表、索引、視圖等數(shù)據(jù)庫對象，練習(xí)對表、索引和視圖的各種操作。（2）要求認(rèn)真進(jìn)行實(shí)驗(yàn)，記錄各實(shí)驗(yàn)用例及執(zhí)行結(jié)果。（3）深入了解各個(gè)操作的功能。實(shí)驗(yàn)要求包括如下方面的容：數(shù)據(jù)定義基本表的創(chuàng)建、修改及刪除索引的創(chuàng)建視圖的創(chuàng)建數(shù)據(jù)操作完成各類更新操作包括:修改數(shù)據(jù)刪除數(shù)據(jù)數(shù)據(jù)查詢操作完成各類查詢操作單表查詢分組統(tǒng)計(jì)連接查詢嵌套查詢集合查詢數(shù)據(jù)操作創(chuàng)建視圖視圖查詢參考示例：建立一個(gè)學(xué)生選課數(shù)據(jù)庫，練習(xí)對表、視圖和索引等數(shù)據(jù)庫對象的各種操作。一、數(shù)據(jù)定義創(chuàng)建學(xué)生選課數(shù)據(jù)庫ST，包括三個(gè)基本表，其中Student表保存學(xué)生基本信息，Course表保存課程信息，SC表保存學(xué)生選課信息，其結(jié)構(gòu)如下表：表1.Student表結(jié)構(gòu)列名稱用途類型長度約束備注Sno學(xué)號字符8主鍵Sname字符8Ssex性別字符2Sage年齡整型Sdept所在系字符20Sclass班級字符4列名稱用途類型長度約束備注Cno課程號字符4主鍵Cname課程名字符40Cpno先修課程號字符4Ccredit學(xué)分整型表3.SC表結(jié)構(gòu)列名稱用途類型長度約束備注Sno學(xué)號字符8外鍵Cno課程號字符4Grade成績整型創(chuàng)建、修改及刪除基本表創(chuàng)建Student表CREATETABLEStudent(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8),SsexCHAR(2)NOTNULL,SageINT,SdeptCHAR(20));創(chuàng)建Course表CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40)NOTNULL,CpnoCHAR(4),Ccredit);SMALLINT,創(chuàng)建SC表CREATETABLESC(SnoCHAR(8)FOREIGNKEY(Sno)REFERENCESStudent(Sno),CnoCHAR(4),GradeSMALLINT,);創(chuàng)建員工表EmployeeCREATETABLEEmployee(編號CHAR(8)PRIMARYKEY,VARCHAR(8)notnull部門CHR(40),工資numeric(8,2),生日datetime職稱char(20),);指出該語句中的錯(cuò)誤并改正后執(zhí)行。檢查表是否創(chuàng)建成功SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSCSELECT*FROMEmployee修改表結(jié)構(gòu)及約束?增加班級列ALTERTABLEStudentADDSclasschar(4)?修改年齡列ALTERTABLEStudentALTERCOLUMNSagesmallint?增加約束ALTERTABLECourseADDUNIQUE(Cname)刪除表DROPTABLEEmployee創(chuàng)建索引為Course表按課程名稱創(chuàng)建索引CREATEINDEXiCnameOnCourse(Cname)為Student表按學(xué)生創(chuàng)建唯一索引CREATEUNIQUEINDEXiSnameONStudent(Sname)為SC表按學(xué)號和課程號創(chuàng)建聚集索引CREATECLUSTEREDINDEXiSnoCnoOnSC(Sno,Cnodesc)為Course表按課程號創(chuàng)建唯一索引請自己完成該操作創(chuàng)建視圖

建立信息系學(xué)生的視圖：CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='IS';3.2數(shù)據(jù)操作插入數(shù)據(jù)將如下表格中的數(shù)據(jù)分別插入到數(shù)據(jù)庫相應(yīng)的表中:表6.學(xué)生選課信息表學(xué)號課程號表6.學(xué)生選課信息表學(xué)號課程號成績學(xué)號課程號成績學(xué)號性別年齡所在系班級20100001勇男20CS100120100002晨女19CS100120100021王敏女18MA100220100031立男19IS100320100003洋女100120100010斌男19IS100520100022明明男19CS1002表5.課程信息表課程號課程名先修課程號學(xué)分1數(shù)據(jù)庫系統(tǒng)原理5642高等數(shù)學(xué)23管理信息系統(tǒng)144操作系統(tǒng)原理635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語言42010000119220100001285201000013882010000219020100002280201000031201000103插入到Student表INSERTINTOStudentVALUES('20100001','勇'，'男'，20,'CS','1001')INSERTINTOStudentVALUES('20100002','晨'，'女'，19,'CS','1001')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,Sclass)VALUES('20100021','王敏'，'女,18,'MA','1002')INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,sclass)VALUES('20100031','立'，'男,19,'IS','1003')INSERTINTOStudent(Sno,Sname,Ssex,sclass)VALUES('20100003','洋'，'女'，'1001')檢查下列語句中的錯(cuò)誤，并改正：INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,sclass)VALUES('20100010',斌，'男,'19','IS','1005')INSERTINTOStudentVALUES('20100022','明明'，19,'男'，'CS','1002')插入到Course表INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('1','數(shù)據(jù)庫系統(tǒng)原理'，'5',4)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('2','高等數(shù)學(xué)'，null,2)INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('3','管理信息系統(tǒng)','1',4)請寫出插入其余行的插入語句，并插入數(shù)據(jù)。插入到SC表INSERTINTOSCVALUES('20100001','1',92)INSERTINTOSCVALUES('20100002','2',80)INSERTINTOSC(Sno,Cno)VALUES('20100003','1')INSERTINTOSC(Sno,Cno,Grade)VALUES('20100010','3',null)請寫出插入其余行的插入語句，并運(yùn)行。多行插入到表中創(chuàng)建存一個(gè)表，保存學(xué)生的學(xué)號、和年齡：CREATETABLEcs_Student(學(xué)號char(8),char(8),年齡smallint);插入數(shù)據(jù)行：INSERTINTOcs_StudentSELECTSno,Sname,SageFROMstudentWhereSdept='CS';檢查插入到表中的數(shù)據(jù)SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSC修改數(shù)據(jù)將學(xué)生20100001的年齡改為22歲。UPDATEstudentSETSage=22WHERESno='20100001'；將所有學(xué)生的年齡增加一歲。UPDATEStudentSETSage=Sage+1填寫斌同學(xué)的管理信息系統(tǒng)課程的成績UPDATESCSETGrade=85WHERESno='20100010'ANDCno='3'將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績加5分UPDATEscSETGrade=Grade+5WHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=sc.Sno)；請自己完成如下操作?將晨同學(xué)的2號課程成績修改為80?將“20100021”同學(xué)的學(xué)號修改為“20100025”檢查數(shù)據(jù)是否修改刪除數(shù)據(jù)刪除學(xué)號為201000022的學(xué)生記錄DELETEFROMStudentWHERESno='20100022'刪除學(xué)號20100001學(xué)生的1號課程選課記錄將選課信息復(fù)制到一個(gè)臨時(shí)表tmpSC中：在tmpSC中執(zhí)行刪除操作：DELETEFROMtmpSCWHERESno='20100001'andCno='1刪除臨時(shí)表中20100002學(xué)生的全部選課記錄請自己完成該操作。刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄DELETEFROMtmpSCWHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno);刪除全部選課記錄DELETEFROMtmpSC檢查數(shù)據(jù)是否刪除3.3數(shù)據(jù)查詢操作完成如下查詢操作：單表查詢按指定目標(biāo)列查詢?查詢學(xué)生的詳細(xì)記錄：SELECT*FROMStudent；?查詢學(xué)生的學(xué)號、和年齡SELECTSno,Sname,SageFROMStudent；目標(biāo)列包含表達(dá)式的查詢?查詢?nèi)w學(xué)生的、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname,'YearofBirth：',2004-Sage,LOWER(Sdept)FROMStudent；查詢結(jié)果集中修改列名稱?查詢?nèi)w學(xué)生的、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname,'YearofBirth：'asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept)FROMStudent；取消重復(fù)行?查詢選修了課程的學(xué)生學(xué)號：比較ALL和DISTINCT的區(qū)別SELECTSnoFROMSC；SELECTDISTINCTSnoFROMSC；（5）簡單條件查詢查詢計(jì)算機(jī)科學(xué)系全體學(xué)生的SELECTSnameFROMStudentWHERESdept='CS'；（6）按圍查詢查詢年齡在20?23歲之間的學(xué)生的、系別和年齡SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23（7）查詢屬性值屬于指定集合的行查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的和性別SELECTSname,SsexFROMStudentWHERESdeptIN（'IS','MA','CS'）;（8）模糊查詢查詢所有姓學(xué)生的、學(xué)號和性別SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'%'（9）查詢空值查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號SELECTSno,CnoFROMscWHEREGradeisnull;（10）多重條件查詢查詢計(jì)算機(jī)科學(xué)系年齡在歲以下的學(xué)生SELECTSnameFROMstudentWHERESdept='CS'andSage<20;（11）結(jié)果集排序查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列SELECT*FROMStudentORDERBYSdept,SageDESC;（12）完成下列查詢查詢學(xué)生基本信息，結(jié)果集屬性名使用漢字查詢信息系且年齡大于23歲同學(xué)的學(xué)號和查詢年齡是17、18、20、23歲同學(xué)的學(xué)號、、年齡和所在系查詢年齡不在21?24歲之間的學(xué)生的、系別和年齡分組統(tǒng)計(jì)（1）聚集函數(shù)的使用查詢學(xué)生總?cè)藬?shù)SELECTCOUNT(*)FROMStudent;查詢選修了課程的學(xué)生人數(shù)SELECTCOUNT(DISTINCTSno)FROMSC查詢最高分SELECTMAX(Grade)FROMSC聚集函數(shù)作用于部分行統(tǒng)計(jì)2號課程的總分、均分和最高分SELECTSUM(grade)總分,AVG(grade)均分,MAX(grade)最高分FROMscWHERECno='2'分組統(tǒng)計(jì)統(tǒng)計(jì)各門課程的選課人數(shù)、均分和最高分selecto課程號，count(*)人數(shù),AVG(grade)均分,MAX(grade)最高分fromscgroupbyCno統(tǒng)計(jì)均分大于90的課程selecto課程號，count(*)人數(shù),AVG(grade)均分,MAX(grade)最高分fromscgroupbyCnohavingAVG(grade)>90完成下面的查詢統(tǒng)計(jì)每個(gè)同學(xué)的學(xué)號、選課數(shù)、平均成績和最高成績統(tǒng)計(jì)每個(gè)班的每門課的選課人數(shù)、平均成績和最高成績連接查詢在WHERE中指定連接條件查詢每個(gè)參加選課的學(xué)生信息及其選修課程的情況SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno查詢每一門課的間接先修課SELECT*FROMcoursefirst,coursesecondWHEREfirst.Cpno=second.Cno;SELECTfirst.Cno,second.CpnoFROMcoursefirst,coursesecondWHEREfirst.Cpno=second.Cno;在FROM中指定連接條件查詢每個(gè)參加選課的學(xué)生信息及其選修課程的情況SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCON(Student.Sno=SC.Sno)使用外連接查詢?查詢每個(gè)學(xué)生信息及其選修課程的情況SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno)復(fù)合條件連接查詢查詢選修號課程且成績在分以上的所有學(xué)生SELECTStudent.Sno,SnameFROMStudentjoinSCON(Student.Sno=SC.Sno)/*連接條件*/WHERESC.Cno='2'ANDSC.Grade>90;/*過濾條件*/多表查詢查詢每個(gè)學(xué)生的學(xué)號、、選修的課程名及成績SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;完成下列查詢查詢選修了2號課程的同學(xué)的學(xué)號和查詢各門課程的課程號、課程名稱以及選課學(xué)生的學(xué)號查詢選修了數(shù)據(jù)庫系統(tǒng)原理課程的同學(xué)的學(xué)號和和成績嵌套查詢由In引出的子查詢查詢與“晨''在同一個(gè)系學(xué)習(xí)的學(xué)生SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='晨');由比較運(yùn)算符引出的子查詢找出每個(gè)學(xué)生超過他選修課程平均成績的課程號。SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno)；帶修飾符的比較運(yùn)算符引出的子查詢查詢其他系中比計(jì)算機(jī)科學(xué)系所有學(xué)生年齡都小的學(xué)生及年齡。SELECTSname,SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS'；由EXISTS引出的子查詢查詢所有選修了1號課程的學(xué)生SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')集合查詢集合并查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生SELECT*FROMStudentWHERESdept='CS'UNIONS