GDOU-B-11-112（）實驗名稱實驗2數(shù)據(jù)的查詢、更新 課程名稱 數(shù)據(jù)庫系統(tǒng)原

課程號1620072學(xué)院(系)學(xué)生姓名

信息學(xué)院學(xué)號

專業(yè) 計算機科學(xué)與技術(shù)實驗地點科技樓

班級 1113實驗日期 04-12實驗二數(shù)據(jù)的查詢、更新一、實驗?zāi)康?、掌握用戶自定義數(shù)據(jù)類型的方法2、掌握用T-SQL語句進行數(shù)據(jù)的插入、修改、刪除的方法3SELECT二、實驗要求1實驗內(nèi)容的預(yù)習(xí)準(zhǔn)備工作；2、能認真獨立完成實訓(xùn)內(nèi)容；3、實驗后做好實驗總結(jié)，根據(jù)實驗情況完成總結(jié)報告。三、實驗內(nèi)容1、用T-SQLchar2varcha，長度為2，允許為空。sp_addtypechar20,'varchar(20)',null2、用T-SQL語句，建立一個“學(xué)生課程數(shù)據(jù)庫”，在此基礎(chǔ)上建立該數(shù)據(jù)庫包含的學(xué)生表，課程表，學(xué)生選修表，并向各表插入如下相應(yīng)的數(shù)據(jù)。建立“學(xué)生課程數(shù)據(jù)庫”數(shù)劇庫：CREATEDATABASE學(xué)生課程數(shù)據(jù)庫ONPRIMARY(NAME=Student_dat,.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)LOGON(NAME=Student_log,FILENAME='D:\.ldf',SIZE=5MB,FILEGROWTH=10%)GO創(chuàng)建學(xué)生表CREATETABLEStudent(SnoINTPRIMARYKEY,SnameCHAR(10),SsexCHAR(2CHECK(Ssex='男ORSsex='女'),SageSMALLINTCHECK(SageBETWEEN15AND30),SdeptCHAR(20)); 插入數(shù)據(jù)：INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUESINSERTINTO('95001','李敏勇', '男',20,'CS')Student(Sno,Sname,Ssex,Sage,Sdept)VALUESINSERT('95002', '劉晨','女',19'IS')INTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUESINSERT('95003', '王敏','女',18'MA')INTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES('95004','張立','男', 18,'IS');(2)創(chuàng)建課程表CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(10)NOTNULL,CpnoCHAR(4),CreditINTCHECK(Credit>=0ANDCredit<=100),Teacherchar20);插入數(shù)據(jù)：INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES (1,'數(shù)據(jù)', 5,4,'王芳')INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES(2 ,'數(shù)學(xué)',NULL,2,'')INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES(3 信息系統(tǒng)',1,4,'劉新')INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES(4,'操作系統(tǒng)'3,'高升')INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES(5,'數(shù)據(jù)結(jié)構(gòu)',74,'宋明')INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES(6,'數(shù)據(jù)處理',NULL,2,'張彬')INSERTINTOCourse(Cno,Cname,Cpno,Credit,Teacher)VALUES(7,'Pascal語言',6,4,'李磊');創(chuàng)建學(xué)生選修表CREATETABLESC(SnoINTFOREIGNKEYREFERENCESStudent(Sno),CnoCHAR(4)FOREIGNKEYREFERENCESCourse(Cno),GradeINTCHECK(Grade>=0ANDGrade<=100),PRIMARYKEY(Sno,Cno));插入數(shù)據(jù)：INSERTINTOSC(Sno,Cno,Grade)VALUES('95001','1',92)INSERTINTOSC(Sno,Cno,Grade)VALUESINSERT('95001','2',85)INTOSC(Sno,Cno,Grade)INSERT('95001','3',88)INTOSC(Sno,Cno,('95002','2',90)INTOSC(Sno,Cno,('95003','2',55)INTOSC(Sno,Cno,Grade)Grade)Grade)VALUES('95004','2',70)3、用T-SQL語句，修改上面所建學(xué)生課程數(shù)據(jù)庫中數(shù)據(jù):向?qū)W生:Student中加入一條記錄謝,男,22,CS)并保INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept)VALUES ('95030','謝非','男',22,'CS')將李敏勇的數(shù)據(jù)庫的成績改為98UPDATESCSETGrade=98WHERESC.SnoIN(SELECTSC.SnoFROMStudent,SCWHEREStudent.Sno=SC.SnoANDStudent.Sname='李敏勇')ANDSC.CnoIN(SELECTSC.CnoFROMWHERECourse.Cno=SC.CnoANDCourse.Cname='數(shù)據(jù)庫')刪除學(xué)生表StudentDELETEStudentWHEREStudent.Sname='謝非'能不能從StudentDELETEStudentWHEREStudent.Sname='李敏勇'不能刪除李敏勇學(xué)生的記錄語句與REFERENCE約束"FK SC Sno 沖突。該沖突發(fā)生于數(shù)據(jù)"學(xué)生課程數(shù)據(jù)"，"dbo.SC",column。”；也不能刪除王敏,張立兩個學(xué)生的記錄。4、用T-SQL語句，完成下面簡單的查詢SELECTSno,Sname,SageFROMStudent 查詢?nèi)w學(xué)生的姓名,年齡及所在系（列名）。SELECTSname,Sage,LOWER(Sdept)'系名'FROM Student查詢選修了課程的學(xué)生學(xué)號SELECTFROM SC查詢信息系全體學(xué)生的姓名5）查詢所有年齡在20歲以下的學(xué)生姓名及其年齡SELECTFROMWHEREStudentSage<20Sage5）查詢所有年齡在20歲以下的學(xué)生姓名及其年齡SELECTFROMWHEREStudentSage<20Sage6）2018SELECT Sdept,SageFROM Student7）2319SELECT 7）2319SELECT Sdept,SageFROM StudentWHERE ORSage>238）查詢不是信息系(IS)和計算機系(CS)學(xué)生的姓名和性別SELECTSname,Ssex,SdeptFROMStudentWHERENOTSdept='IS'ANDNOTSdept='CS'9）查詢所有姓劉的學(xué)生的姓名,學(xué)號和性別SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'劉%' 10）查詢姓“張”且名為一個漢字的學(xué)生的姓名SELECTSnameFROMStudentWHERESnameLIKE'張_'11）查詢名字中第2個字為”敏”字的學(xué)生姓名和學(xué)號SELECTSname11）查詢名字中第2個字為”敏”字的學(xué)生姓名和學(xué)號SELECTSname,SnoWHERESnameLIKE'_敏%' 查詢所有不姓劉的學(xué)生姓名SELECTSnameFROMStudentWHERESnameNOTLIKE'劉%'T ,e,x,e,FROM StudentORDERBYSdept,SageDESC 查詢學(xué)生表中所有學(xué)生信息，要求只顯示前SELECT TOP10PERCENT*15）按成績降序查詢輸出SC表中成績大于等于70分的所有學(xué)生的學(xué)號，要求只顯示前2行數(shù)據(jù),若第3行后的數(shù)據(jù)也等于70分也要顯示。SELECTTOP215）按成績降序查詢輸出SC表中成績大于等于70分的所有學(xué)生的學(xué)號，要求只顯示前2行數(shù)據(jù),若第3行后的數(shù)據(jù)也等于70分也要顯示。SELECTTOP2WITHTIESSno,GradeFROM SCWHEREGrade>=70ORDERBYGradeDESC查詢每個學(xué)生的學(xué)號、課程號及分數(shù)，同時統(tǒng)計每個學(xué)生的總分T ,o,FROM SCORDERBYSnoCOMPUTESUM(Grade)BYSno 查詢每個學(xué)生的各科分數(shù)、最高分、最低分、總分、平均分T ,o,FROM SCORDERBYSnoCOMPUTEMAX(Grade),MIN(Grade),SUM(Grade),AVG(Grade) BYSno5、用T-SQL語句完成下面的查詢查詢學(xué)生的總?cè)藬?shù)SELECT COUNT(*)'總?cè)藬?shù)'FROM Student查詢選修了課程的學(xué)生人數(shù)SELECTCOUNT(*)FROMStudent,SCWHEREStudent.Sno=SC.Sno3)3)2SELECT AVG(Grade)FROM SCWHERECno=2 4)查詢選修2號課程的學(xué)生最高分數(shù)SELECT MAX(Grade)FROM SCWHERECno=25)求各個課程號及相應(yīng)的選課人數(shù)5)求各個課程號及相應(yīng)的選課人數(shù)SELECTCno,COUNT(Sno)'選課人數(shù)'FROM SCGROUPBYCno2SELECTSnoFROM SCGROUPBYSnoHAVINGCOUNT(*)>2查詢每個學(xué)生及其選修課程的情況SELECTStudent.*,Course.*FROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno查詢每一門課的間接先修即先修課的先修SELECTCourse.Cname,Course_2.Cname'間接先修課'FROM CourseINNERJOINCourseASCourse_1ONCourse.Cno=Course_1.CpnoINNERJOINCourseASCourse_2ONCourse_1.Cno=Course_2.Cpno290（90）的所有學(xué)生。SELECTSnameFROM StudentWHERESnoIN(SELECTSnoFROM SCWHERE Cno=2ANDGrade>=90)用T-SQL語句完成下面的查詢查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生SELECTSno,Sname,Ssex,Sage,SdeptFROM StudentWHERESdeptIN(SELECTSdeptFROM WHERESname='劉晨')ANDSname!='劉晨'查詢選修了課程名為“數(shù)學(xué)”的學(xué)生學(xué)號和姓名SELECTStudent.Sno,Student.SnameFROM Student,SC,CourseWHERECourse.Cname='數(shù)學(xué)'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno查詢其它系中比信息系中某一學(xué)生年齡小的學(xué)生姓名和年齡SELECT Sname,SageFROM StudentWHERESdept!='IS'ANDSage<ANY(SELECT SageFROM StudentWHERESdept='IS')查詢其它系中比計算機系所有學(xué)生年齡都小的學(xué)生姓名及年齡SELECT Sname,SageFROM StudentWHERESdept!='IS'ANDSage<ALL(SELECT SageFROM StudentWHERESdept='IS')2SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=26)6)3SELECT FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHEREStudent.Sno=SC.SnoANDCno=1)7、用T-SQL語句完成下面的復(fù)雜查詢1)至少選修劉老師所授課程中一門課程的女學(xué)生姓名SELECT SnameFROM Student,SC,CourseWHERESsex='女'ANDCourse.TeacherLIKE'劉%'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno2)檢索王同學(xué)不學(xué)的課程的課程號SELECT Cno2)檢索王同學(xué)不學(xué)的課程的課程號SELECT CnoSELECTCourse.CnoFROM Student,SC,CourseWHEREStudent.SnameLIKE'王%'ANDStudent.Sno=SC.SnoANDSC.Cno=Course.Cno3)檢索全部學(xué)生都選修的課程的課程號與課程名。SELECT FROMCourseWHERENOTEXISTS(SELECT*FROM StudentWHERENOTEXISTS(SELECT*FROM SCWHERE Student.Sno=SC.SnoANDSC.Cno=Course.Cno))4)4)檢索選修課程包含劉老師所授課的學(xué)生學(xué)號。SELECTDISTINCTStudent.SnoFROM Student,SC,CourseWHERE Student.SnoSC.SnoANDSC.CnoCourse.CnoANDCourse.TeacherLIKE'劉%'5)5)求選修課程號為2的學(xué)生的平均年齡。SELECTAVG(Sage)'平均年齡'FROM Student,SCWHERE Student.Sno=SC.SnoANDSC.Cno=2求劉老師所授課程的每門課程的學(xué)生平均成績。SELECTCname,Teacher,AVG(Grade)'平均成績'FROM Student,SC,CourseWHERE Student.SnoSC.SnoANDSC.CnoCourse.CnoANDCourse.TeacherLIKE'劉%'GROUPBYCname,Teacher檢索學(xué)號比劉同學(xué)大，而年齡比他小的學(xué)生姓名。SELECT FROM StudentWHERESno>(SELECTSnoFROM StudentWHERESname='劉%')ANDSage<(SELECTSageFROM StudentWHERESname='劉%')求年齡大于女同學(xué)平均年齡的男同學(xué)姓名和年齡。SELECT Sname, FROM StudentWHERESage>(SELECTAVG(Sage)FROMStudentWHERESsex='女')ANDSsex='男'求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。SELECT Sname, FROM StudentWHERESage>(SELECTMAX(Sage)FROMStudentWHERESsex='女')ANDSsex='男'檢索每一門課程成績都大于等于80的基本表（。CREATETABLES(SNOINTPRIMARYKEY,SNAMECHAR(10),SEX CHAR(2CHECK(SEX='男ORSEX='女'))INSERTINTOS(SNO,SNAME,SEX)SELECTDISTINCTStudent.Sno,Sname,SsexFROM Student,SCWHERE Student.Sno=SC.SnoAND Grade>80把選課數(shù)學(xué)課不及格的成績?nèi)臑榭罩?。UPDATESCSETGrade=NULLWHERE'數(shù)學(xué)'IN(SELECT Cname FROM Course,SCWHERESC.Cno=Course.Cno ANDGrade<=60)把王同學(xué)的選課信息全部刪去。DELETESCWHERESnoIN(SELECTSC.SnoFROMStudent,SCWHERE SnameLIKE'王%'ANDStudent.Sno=SC.Sno)把低于總平均成績的男同學(xué)成績提高UPDATESCSETGrade=Grade*1.05WHEREGrade<(SELECTAVG(Grade)FROM SC)ANDSnoIN(SELECTSnoFROMStudentWHERESsex='男')課程的學(xué)生學(xué)