實(shí)驗(yàn)4

SQL語言——SELECT查詢操作實(shí)驗(yàn)?zāi)康?/p>

背景知識

背景知識

背景知識

實(shí)驗(yàn)示例實(shí)驗(yàn)示例2、刷新數(shù)據(jù)庫目錄后，選擇新出現(xiàn)的jxgl數(shù)據(jù)庫，在SQL操作窗口中，創(chuàng)建Student、SC、Course三表及表記錄插入命令如下：USEjxglCreateTableStudent(SnoCHAR(5)NOTNULLPRIMARYKEY(Sno),SnameVARCHAR(20),SageSMALLINTCHECK(Sage>=15ANDSage<=45),SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女'),SdeptCHAR(2));CreateTableCourse(CnoCHAR(2)NOTNULLPRIMARYKEY(Cno),CnameVARCHAR(20),CpnoCHAR(2),CcreditSMALLINT);實(shí)驗(yàn)示例CreateTableSC(SnoCHAR(5)NOTNULLCONSTRAINTS_FFOREIGNKEYREFERENCESStudent(Sno),CnoCHAR(2)NOTNULL,GradeSMALLINTCHECK((GradeISNULL)OR(GradeBETWEEN0AND100)),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Cno)REFERENCESCourse(Cno));INSERTINTOStudentVALUES('98001','錢橫',18,'男','CS');INSERTINTOStudentVALUES('98002','王林',19,'女','CS');INSERTINTOStudentVALUES('98003','李民',20,'男','IS');INSERTINTOStudentVALUES('98004','趙三',16,'女','MA');INSERTINTOCourseVALUES('1','數(shù)據(jù)庫系統(tǒng)','5',4);INSERTINTOCourseVALUES('2','數(shù)學(xué)分析',null,2);實(shí)驗(yàn)示例INSERTINTOCourseVALUES('3','信息系統(tǒng)導(dǎo)論','1',3);INSERTINTOCourseVALUES('4','操作系統(tǒng)原理','6',3);INSERTINTOCourseVALUES('5','數(shù)據(jù)結(jié)構(gòu)','7',4);INSERTINTOCourseVALUES('6','數(shù)據(jù)處理基礎(chǔ)',null,4);INSERTINTOCourseVALUES('7','C語言','6',3);INSERTINTOSCVALUES('98001','1',87);INSERTINTOSCVALUES('98001','2',67);INSERTINTOSCVALUES('98001','3',90);INSERTINTOSCVALUES('98002','2',95);INSERTINTOSCVALUES('98002','3',88);實(shí)驗(yàn)示例實(shí)驗(yàn)示例實(shí)驗(yàn)示例圖4-1在SSMS查詢子窗口中的查詢執(zhí)行情況[例4.2]查年齡大于18，并不是信息系（IS）與數(shù)學(xué)系（MA）的學(xué)生的姓名和性別。SELECTSname,SsexFROMStudentWHERESage>18ANDSdeptNOTIN('IS','MA');在SSMS打開表的顯示SQL窗格中的執(zhí)行情況如圖4-2所示。實(shí)驗(yàn)示例實(shí)驗(yàn)示例圖4-2在SSMS打開表的顯示SQL窗格中的查詢執(zhí)行情況實(shí)驗(yàn)示例[例4.3]查以”MIS_”開頭，且倒數(shù)第二個(gè)漢字為“導(dǎo)”字的課程的詳細(xì)情況。SELECT*FROMCourseWHERECnameLIKE’MIS#_%導(dǎo)_’ESCAPE’#’;

[例4.4]查詢選修了課程的學(xué)生人數(shù)。SELECTCOUNT(DISTINCTSno)/*加DISTINCT去掉重復(fù)值后計(jì)數(shù)*/FROMSC;

實(shí)驗(yàn)示例SELECTStudent.SnoFROMStudent,SCWHERESdept='CS'ANDStudent.Sno=SC.SnoGROUPBYStudent.SnoHAVINGCOUNT(*)>=2;

[例4.6]查詢Student表與SC表的廣義笛卡爾積。SelectStudent.*,SC.*FromStudent,SC;或SelectStudent.*,SC.*FromStudentCrossJoinSC;

實(shí)驗(yàn)示例[例4.9]查詢課程之先修課的先修課(自身連接例)。SELECTFIRST.Cno,SECOND.cpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.cpno=SECOND.Cno;

我們?yōu)镃ourse表取兩個(gè)別名FIRST與SECOND，這樣就可以在SELECT子句和WHERE子句中的屬性名前分別用這兩個(gè)別名加以區(qū)分。

實(shí)驗(yàn)示例[例4.10]查詢學(xué)生及其課程、成績等情況（不管是否選課，均需列出學(xué)生信息）。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLeftOuterJOINSCONStudent.Sno=SC.Sno;

[例4.11]查詢學(xué)生及其課程成績與課程及其學(xué)生選修成績的明細(xì)情況（要求學(xué)生與課程均需全部列出）。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Course.Cno,Grade,cname,cpno,ccreditFROMStudentLeftOuterJOINSCONStudent.Sno=SC.SnoFullOuterjoinCourseonSC.cno=Co;實(shí)驗(yàn)示例[例4.12]查詢性別為男、課程成績及格的學(xué)生信息及課程號、成績。SELECTStudent.*,Cno,GradeFROMSTUDENTINNERJOINSCONStudent.Sno=SC.SnoWHERESSEX=’男’ANDGRADE>=60實(shí)驗(yàn)示例[例4.13]查詢與“錢橫”在同一個(gè)系學(xué)習(xí)的學(xué)生信息。SELECT*FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='錢橫');或SELECT*FROMStudentWHERESdept=實(shí)驗(yàn)示例[例4.13]查詢與“錢橫”在同一個(gè)系學(xué)習(xí)的學(xué)生信息。(SELECTSdeptFROMStudentWHERESname='錢橫');--當(dāng)子查詢?yōu)閱瘟袉涡兄禃r(shí)可以用“=”或SELECTS1.*FROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='錢橫';一般來說，連接查詢可以替換大多數(shù)的嵌套子查詢。

實(shí)驗(yàn)示例SQL-92支持“多列成員”的屬于(IN)條件表達(dá)，例：[例4.14]找出同系、同年齡、同性別的學(xué)生。Select*fromStudentasTWhere(T.sdept,T.sage,T.ssex)IN(Selectsdept,sage,ssexFromstudentasSWhereS.sno<>T.sno);--SQLServer可能不支持實(shí)驗(yàn)示例它等價(jià)于逐個(gè)成員IN的方式表達(dá)，如下（能在SQLServer中執(zhí)行）：Select*fromStudentTWhereT.sdeptIN(SelectsdeptFromstudentSWhereS.sno<>T.snoandT.sageIN(SelectsageFromstudentXWhereS.sno=X.snoandX.sno<>T.snoandT.ssexIN(SelectssexFromstudentYWhereX.sno=Y.snoandY.sno<>T.sno)));實(shí)驗(yàn)示例[例4.15]查詢選修了課程名為“數(shù)據(jù)庫系統(tǒng)”的學(xué)生學(xué)號、姓名和所在系。SELECTSno,Sname,SdeptFROMStudent--IN嵌套查詢方法WHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='數(shù)據(jù)庫系統(tǒng)'));或

SELECTSno,Sname,SdeptFROMStudent--IN、=嵌套查詢方法WHERESnoIN(SELECTSnoFROMSCWHERECno=(SELECTCnoFROMCourseWHERECname='數(shù)據(jù)庫系統(tǒng)'));實(shí)驗(yàn)示例或SELECTStudent.Sno,Sname,Sdept--連接查詢方法FROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='數(shù)據(jù)庫系統(tǒng)';或SelectSno,Sname,SdeptFromStudent--Exists嵌套查詢方法WhereExists(Select*FromSCWhereSC.Sno=Student.SnoAndExists(Select*FromCourseWhereSC.Cno=Course.CnoAndCname='數(shù)據(jù)庫系統(tǒng)'));或SelectSno,Sname,SdeptFromStudent--Exists嵌套查詢方法WhereExists(Select*FromcourseWhereCname='數(shù)據(jù)庫系統(tǒng)'andExists(Select*FromSCWheresc.sno=student.snoandSC.Cno=Course.Cno));實(shí)驗(yàn)示例[例4.16]檢索至少不學(xué)2和4兩門課程的學(xué)生學(xué)號與姓名。SELECTSno,SnameFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECnoIN('2','4'));實(shí)驗(yàn)示例[例4.17]查詢其他系中比信息系IS所有學(xué)生年齡均大的學(xué)生名單,并排序輸出。SELECTSnameFROMStudentWHERESage>All(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS'ORDERBYSname;本查詢實(shí)際上也可以用集函數(shù)實(shí)現(xiàn)：SELECTSnameFROMStudentWHERESage>(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS'ORDERBYSname;

實(shí)驗(yàn)示例[例4.18]查詢哪些課程只有女生選讀。(本題有多于兩種表達(dá)法)SELECTDISTINCTCNAMEFROMCOURSECWHERE'女'=ALL(SELECTSSEXFROMSC,STUDENTWHERESC.SNO=STUDENT.SNOANDSC.CNO=C.CNO);或SELECTDISTINCTCNAMEFROMCOURSECWHERENOTEXISTS(SELECT*FROMSC,STUDENTWHERESC.SNO=STUDENT.SNOANDSC.CNO=C.CNOANDSTUDENT.SSEX='男');實(shí)驗(yàn)示例[例4.19]查詢所有未修1號課程的學(xué)生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');或SELECTSnameFROMStudentWHERESnoNOTIN(SELECTSnoFROMSCWHERECno='1');但如下是錯的：SELECTSnameFROMStudent,SCWHERESC.Sno=Student.SnoANDCno<>'1';實(shí)驗(yàn)示例[例4.20]查詢選修了全部課程的學(xué)生姓名(為了有查詢結(jié)果，自己可調(diào)整一些表的)。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESno=SC.SnoANDCno=Course.Cno));實(shí)驗(yàn)示例由于沒有全稱量詞，我們將題目的意思轉(zhuǎn)換成等價(jià)的存在量詞的形式：查詢這樣的學(xué)生姓名沒有一門課程是他不選的。本題的另一操作方法是：SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoGroupbyStudent.Sno,Snamehavingcount(*)>=(SELECTcount(*)FROMCourse);實(shí)驗(yàn)示例[例4.21]查詢至少選修了學(xué)生98001選修的全部課程的學(xué)生號碼。本題的查詢要求可以做如下解釋，不存在這樣的課程y，學(xué)生98001選修了y，而要查詢的學(xué)生x沒有選。寫成的SELECT語句為：SELECTSnoFROMStudentSXWHERENOTEXISTS(SELECT*FROMSCSCYWHERESCY.Sno='98001'ANDNOTEXISTS(SELECT*FROMSCSCZWHERESCZ.Sno=SX.SnoANDSCZ.Cno=SCY.Cno));

實(shí)驗(yàn)示例[例4.22]查詢選修了課程1或者選修了課程2的學(xué)生學(xué)號集。SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2';[注意]擴(kuò)展的SQL中有集合操作并(UNION)、集合操作交(INTERSECT)和集合操作差(EXCEPT或MINUS)等。SQL的集合操作要求相容即屬性個(gè)數(shù)、類型必須一致，屬性名無關(guān)，最終結(jié)果集采用第一個(gè)結(jié)果的屬性名，缺省為自動去除重復(fù)元組，各子查詢不帶OrderBy，OrderBy放在整個(gè)語句的最后。實(shí)驗(yàn)示例[例4.23]查詢計(jì)算機(jī)科學(xué)系的學(xué)生與年齡不大于19歲的學(xué)生的交集。SELECT*FROMStudentWHERESdept='CS'INTERSECTSELECT*FROMStudentWHERESage<=19;--SQLServer2005支持運(yùn)行本查詢的本查詢等價(jià)于“查詢計(jì)算機(jī)科學(xué)系中年齡不大于19歲的學(xué)生。”，為此變通法為：SELECT*FROMStudentWHERESdept='CS'ANDSage<=19;

實(shí)驗(yàn)示例[例4.24]查詢選修課程1的學(xué)生集合與選修課程2的學(xué)生集合的差集。SELECTSnoFROMSCWHERECno='2'EXCEPT(或MINUS)SELECTSnoFROMSCWHERECno='1';--SQLServer2005支持含EXCEPT查詢的本例實(shí)際上是查詢選修了課程1但沒有選修課程2的學(xué)生。為此變通法為：SELECTSnoFROMSCWHERECno='1'ANDSnoNOTIN(SELECTSnoFROMSCWHERECno='2');

實(shí)驗(yàn)示例[例4.25]查詢平均成績大于85分的學(xué)號、姓名、平均成績。Selectstu_no,sname,avgrFromStudent,(Selectsno,avg(grade)FromSCGroupBysno)asSG(stu_no,avgr)WhereStudent.sno=SG.stu_noAndavgr>85;SQL-92允許在From中使用查詢表達(dá)式，并必須為查詢表達(dá)式取名。它等價(jià)于如下未使用查詢表達(dá)式的形式：SelectStudent.Sno,Sname,AVG(Grade)FromStudent,SCWhereStudent.Sno=SC.SnoGroupByStudent.Sno,SnameHAVINGAVG(Grade)>85;

實(shí)驗(yàn)示例[例4.26]查出課程成績在90分以上的女學(xué)生的姓名、課程名和成績。SELECTSNAME,CNAME,GRADEFROM(SELECTSNAME,CNAME,GRADEFROMSTUDENT,SC,COURSEWHERESSEX='女'ANDSTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNO)ASTEMP(SNAME,CNAME,GRADE)WHEREGRADE>90;--特意用查詢表達(dá)式實(shí)現(xiàn)，完全可用其它方式實(shí)現(xiàn)

實(shí)驗(yàn)示例但如下使用查詢表達(dá)式的查詢，則不易改寫為其它形式。[例4.27]查詢各不同平均成績所對應(yīng)的學(xué)生人數(shù)(給出平均成績與其對應(yīng)的人數(shù))。Selectavgr,COUNT(*)From(Selectsno,avg(grade)FromSCGroupBysno)asSG(Sno,avgr)GroupByavgr;

實(shí)驗(yàn)示例[例4.28]查出學(xué)生、課程及成績的明細(xì)信息及課程門數(shù)、總成績及平均成績。SELECTsno,cno,grade--COMPUTE的其它使用方法，請參閱幫助。FROMscORDERBYsnoCOMPUTEcount(cno),SUM(grade),avg(grade)BYsno

實(shí)驗(yàn)示例[例4.29]建立信息系學(xué)生的視圖（含有學(xué)號、姓名、年齡及性別），并要求進(jìn)行修改和插入操作時(shí)仍須保證該視圖只有信息系的學(xué)生。通過視圖查找年齡大于等于18歲的女學(xué)生。CREATEVIEWIS_StudentASSELECTSno,Sname,Sage,SsexFROMStudentWHERESdept='IS'WITHCHECKOPTIONGOSELECT*FROMIS_StudentWHERESage>=18ANDSsex='女';

實(shí)驗(yàn)示例[例4.30]設(shè)有學(xué)生-課程關(guān)系數(shù)據(jù)庫，其數(shù)據(jù)庫關(guān)系模式為：學(xué)生S(學(xué)號S#，姓名SN，所在系SD，年齡SA)、課程C(課程號C#,課程名稱CN,先修課號PC#)、學(xué)生選課SC(學(xué)號S#,課程號C#,成績G)。試用SQL語言分別寫出下列查詢（只需寫出SQL命令）：(1)求學(xué)生'98001'(為學(xué)號)所選的成績?yōu)?0以上的課程號。(2)求選讀了“數(shù)據(jù)庫概論”，并成績?yōu)?0或90的學(xué)生學(xué)號和姓名。(3)求選修了全部課程的學(xué)生學(xué)號、姓名及其所在系名。(4)找出沒有學(xué)生選修的課程號及課程名稱。(5)列出選課數(shù)超過3門的學(xué)生學(xué)號、其所修課程數(shù)及平均成績。(6)刪除“數(shù)據(jù)結(jié)構(gòu)”課程及所有對它的選課情況。實(shí)驗(yàn)示例解：(1)SELECTC#FROMSCWHERES#='98001'ANDG>=60;(2)SELECTS#,SNFROMS,SC,CWHEREC.C#=SC.C#ANDSC.S#=S.S#ANDC.CN='數(shù)據(jù)庫概論'AND(G=90ORG=80);(3)SELECTS#,SN,SDFROMSWHERENOTEXISTS(SELECT*FROMCXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.C#=X.C#ANDY.S#=S.S#));(4)SELECTC#,CNFROMCWHEREC.C#NOTIN(SELECTSC.C#FROMSC);(5)SELECTS#,COUNT(C#),AVG(G)FROMSCGROUPBYS#HAVINGCOUNT(C#)>3;(6)DELETEFROMSCWHERESC.C#IN(SELECTC.C#FROMCWHERECN='數(shù)據(jù)結(jié)構(gòu)');DELETEFROMCWHERECN='數(shù)據(jù)結(jié)構(gòu)';實(shí)驗(yàn)示例實(shí)驗(yàn)內(nèi)容與要求2、基于“教學(xué)管理”數(shù)據(jù)庫jxgl，試用SQL的查詢語句表達(dá)下列查詢：(1)統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。(2)求選修4號課程的學(xué)生的平均年齡。(3)求學(xué)分為3的每門課程的學(xué)生平均成績。(4)統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)，超過3人的課程才統(tǒng)計(jì)。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。(5)檢索學(xué)號比王非同學(xué)大，而年齡比他小的學(xué)生姓名。(6)檢索姓名以王打頭的所有學(xué)生的姓名和年齡。(7)在SC中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號和課程號。實(shí)驗(yàn)內(nèi)容與要求2、基于“教學(xué)管理”數(shù)據(jù)庫jxgl，試用SQL的查詢語句表達(dá)下列查詢：(8)求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。(9)求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。(10)檢索所有比“王華”年齡大的學(xué)生姓名、年齡和性別。(11)檢索選修“2”課程的學(xué)生中成績最高的學(xué)生的學(xué)號。(12)檢索學(xué)生姓名及其所選修課程的課程號和成績。(13)檢索選修4門以上課程的學(xué)生總成績(不統(tǒng)計(jì)不及格的課程)，并要求按總成績的降序排列出來。實(shí)驗(yàn)內(nèi)容與要求3、設(shè)有如下4個(gè)基本表(表結(jié)構(gòu)與表內(nèi)容是假設(shè)的)，請先創(chuàng)建數(shù)據(jù)庫及根據(jù)表內(nèi)容創(chuàng)建表結(jié)構(gòu)，并添加表記錄，實(shí)踐以下各題的SQL命令操作：寫出實(shí)現(xiàn)以下各題功能的SQL語句：(1)查詢選修課程'8105'且成績在80到90之間的所有記錄。(2)查詢成績?yōu)?9、89或99的記錄。(3)查詢9803班的學(xué)生人數(shù)。(4)查詢至少有20名學(xué)生選修的并以8開頭的課程的平均成績。(5)查詢最低分大于80，最高分小于95的SNO與平均分。(6)查詢9803班學(xué)生所選各課程的課程號及其平均成績。實(shí)驗(yàn)內(nèi)容與要求實(shí)驗(yàn)內(nèi)容與要求3、設(shè)有如下4個(gè)基本表(表結(jié)構(gòu)與表內(nèi)容是假設(shè)的)，請先創(chuàng)建數(shù)據(jù)庫及根據(jù)表內(nèi)容創(chuàng)建表結(jié)構(gòu)，并添加表記錄，實(shí)踐以下各題的SQL命令操作：(12)查詢選修編號為“8105”課程且成績高于所有選修編號為“8245”課程成績的同學(xué)的CNO、SNO、GRADE。(13)列出所有教師和同學(xué)的姓名、SEX、AGE。(14)查詢成績比該課程平均成績高的學(xué)生的成績表。(15)列出所有任課教師的TNAME和DEPT。(16)列出所有未講課教師的TNAME和DEPT。實(shí)驗(yàn)內(nèi)容與要求3、設(shè)有如下4個(gè)基本表(表結(jié)構(gòu)與表內(nèi)容是假設(shè)的)，請先創(chuàng)建數(shù)據(jù)庫及根據(jù)表內(nèi)容創(chuàng)建表結(jié)構(gòu)，并添加表記錄，實(shí)踐以下各題的SQL命令操作：(17)列出至少有4名男生的班號。(18)查詢不姓“張”的學(xué)生記錄。(19)查詢每門課最高分的學(xué)生的SNO、CNO、GRADE。(20)查詢與“李華”同性別并同班的同學(xué)SNAME。(21)查詢“女”教師及其所上的課程。(22)查詢選修“數(shù)據(jù)庫系統(tǒng)”課程的“男”同學(xué)的成績表。(23)查詢所有比劉濤年齡大的教師姓名、年齡和劉濤的年齡。(24)查詢不講授“8104”號課程的教師姓名。實(shí)驗(yàn)內(nèi)容與要求SNOSNAMESEXAGECLASS980101李華男199801980102張軍男189801980103王紅女199801980301黃華女179803980302大衛(wèi)男169803980303趙峰男209803980304孫娟女219803實(shí)驗(yàn)內(nèi)容與要求STUDENT(學(xué)生表)SNOCNOGRADE980101810467980101810586980102824496980102824576980103810486980103810556980301824476980302824596980302810445980302810585980303824476980303824579980304810486980304810595實(shí)驗(yàn)內(nèi)容與要求

SC(成績表)TNOTNAMESEXAGEPROFDEPT801李新男38副教授計(jì)算機(jī)系802錢軍男45教授計(jì)算機(jī)系803王立女35副教授食品系804李丹女22講師食品系實(shí)驗(yàn)內(nèi)容與要求TEACHER(教師表)CNOCNAMETNO8104計(jì)算機(jī)導(dǎo)論8018105C語言8028244數(shù)據(jù)庫系統(tǒng)8038245數(shù)據(jù)結(jié)構(gòu)804實(shí)驗(yàn)內(nèi)容與要求COURSE(課程表)實(shí)驗(yàn)內(nèi)容與要求

按照下四表的內(nèi)容，請先創(chuàng)建數(shù)據(jù)庫及根據(jù)表內(nèi)容創(chuàng)建表結(jié)構(gòu)，并添加表記錄，實(shí)踐以下各題的SQL命令操作。實(shí)驗(yàn)內(nèi)容與要求SNSNAMECITYSlSN1上海S2SN2北京S3SN3南京S4SN4西安S5SN5上海實(shí)驗(yàn)內(nèi)容與要求sSNP