數(shù)據(jù)庫基礎(chǔ)教材(PPT 46頁).ppt

SQLServer2005數(shù)據(jù)庫基礎(chǔ),項(xiàng)目五學(xué)生成績查詢,模塊5-1統(tǒng)計(jì)查詢,學(xué)習(xí)目標(biāo),工作任務(wù),任務(wù)1各類職稱的教師人數(shù)的統(tǒng)計(jì),【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為teacher。最后要顯示的信息為兩列，一列是職稱的名稱，一列為該類職稱的教師人數(shù)。職稱列在表中對(duì)應(yīng)的字段為ttitle，而人數(shù)是需要統(tǒng)計(jì)的信息，這就需要用到聚合函數(shù)COUNT了。,任務(wù)1各類職稱的教師人數(shù)的統(tǒng)計(jì),【程序代碼】USEstudentGOSELECTttitleAS職稱,COUNT(*)AS人數(shù)FROMteacherGROUPBYttitle,任務(wù)1各類職稱的教師人數(shù)的統(tǒng)計(jì),【執(zhí)行結(jié)果】,任務(wù)2統(tǒng)計(jì)各課程的最高分、最低分和平均成績,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為choice。最后要顯示的信息為四列，即課程編號(hào)、最高分、最低分和平均分。其中最高分、最低分和平均分都不是表中的列，要利用聚合函數(shù)MAX、MIN和AVG顯示信息。,【程序代碼】USEstudentGOSELECTcnoAS課程編號(hào),MAX(grade)AS最高分,MIN(grade)AS最低分,AVG(grade)AS平均分FROMchoiceGROUPBYcno,任務(wù)2統(tǒng)計(jì)各課程的最高分、最低分和平均成績,【執(zhí)行結(jié)果】,任務(wù)2統(tǒng)計(jì)各課程的最高分、最低分和平均成績,任務(wù)3查詢選修了4門以上（包括4門）選修課的學(xué)生,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為choice。最后要顯示學(xué)生學(xué)號(hào)和課程門數(shù)，這里要篩選出的學(xué)生是選修了四門課程及以上的學(xué)生學(xué)號(hào)。要完成這個(gè)任務(wù)，首先可以統(tǒng)計(jì)出每個(gè)學(xué)生選修的課程門數(shù)，然后將選修課程門數(shù)大于等于4的學(xué)生篩選出來。這樣就要用到GROUPBY和HAVING子句，HAVING子句可以對(duì)分類匯總的結(jié)果進(jìn)行篩選,【任務(wù)代碼】USEstudentGOSELECTsnoAS學(xué)號(hào),COUNT(*)AS課程門數(shù)FROMchoiceGROUPBYsnoHAVINGCOUNT(*)=4,任務(wù)3查詢選修了4門以上（包括4門）選修課的學(xué)生,【執(zhí)行結(jié)果】,任務(wù)3查詢選修了4門以上（包括4門）選修課的學(xué)生,知識(shí)說明,聚合函數(shù)聚合函數(shù)屬于系統(tǒng)內(nèi)置函數(shù)之一，它與前面介紹的數(shù)學(xué)函數(shù)、字符串函數(shù)等內(nèi)置函數(shù)不同，它能夠?qū)σ唤M值執(zhí)行計(jì)算并返回單一的值。聚合函數(shù)經(jīng)常與SELECT語句的GROUPBY子句一同使用。除COUNT函數(shù)之外，聚合函數(shù)一般忽略空值。,知識(shí)說明,聚合函數(shù)的種類表51常用聚合函數(shù)的功能,知識(shí)說明,GROUPBY子句GROUPBY語句從英文的字面意義上理解就是“根據(jù)(BY)一定的規(guī)則進(jìn)行分組(GROUP)”。它的作用是通過一定的規(guī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域，然后針對(duì)若干個(gè)小區(qū)域進(jìn)行數(shù)據(jù)處理。HAVING子句用于在包含GROUPBY子句的SELECT語句中指定顯示哪些分組記錄。在GROUPBY對(duì)記錄進(jìn)行組合之后，將顯示滿足HAVING子句條件的GROUPBY子句進(jìn)行分組的任何記錄。,模塊5-2多表查詢連接查詢,學(xué)習(xí)目標(biāo),工作任務(wù),任務(wù)1查詢孫曉龍的所有選修課的成績,【任務(wù)分析】此查詢要用到的數(shù)據(jù)庫為student，用到的表為student和choice。最后要顯示的信息為兩列，一列是課程編號(hào)，一列為課程成績。雖然這兩列在choice表中都有，但是本例中要顯示的是名叫孫曉龍的學(xué)生的選課信息。解決這個(gè)問題可以將student和choice兩表進(jìn)行內(nèi)連接操作，然后再篩選出滿足條件的記錄，即姓名為孫曉龍的學(xué)生的選課信息。,【程序代碼】USEstudentGOSELECTcno,gradeFROMstudentINNERJOINchoiceONstudent.sno=choice.snoWHEREsname=孫曉龍,任務(wù)1查詢孫曉龍的所有選修課的成績,【執(zhí)行結(jié)果】,任務(wù)1查詢孫曉龍的所有選修課的成績,任務(wù)2查詢選修了課程編號(hào)為“0101001”的學(xué)生的姓名和成績,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為student和choice。最后要顯示的信息為兩列，一列是學(xué)生姓名，一列為課程成績。解決這個(gè)問題可以將student和choice兩表進(jìn)行內(nèi)連接操作，然后再篩選出滿足條件的記錄，即課程編號(hào)為0101001的課程成績。,【程序代碼】USEstudentGOSELECTsnameAS姓名,gradeAS成績FROMstudentINNERJOINchoiceONstudent.sno=choice.snoWHEREcno=0101001,任務(wù)2查詢選修了課程編號(hào)為“0101001”的學(xué)生的姓名和成績,【執(zhí)行結(jié)果】,任務(wù)2查詢選修了課程編號(hào)為“0101001”的學(xué)生的姓名和成績,任務(wù)3查詢選修了“Illustrator平面設(shè)計(jì)”課程的學(xué)生的姓名和課程成績，并按成績降序排列,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為student、choice和course。最后要顯示的信息為兩列，一列是學(xué)生姓名，一列為課程成績。學(xué)生姓名在student表中，而課程成績則在choice表中。解決這個(gè)問題可以將student、choice和course三表進(jìn)行內(nèi)連接操作，然后再篩選出滿足條件的記錄，即課程名稱為“Illustrator平面設(shè)計(jì)”的課程成績。,【任務(wù)代碼】USEstudentGOSELECTA.snameAS姓名,B.gradeAS成績FROMstudentASAJOINchoiceASBONA.sno=B.snoJOINcourseASCONB.cno=C.cnoWHEREC.cname=Illustrator平面設(shè)計(jì)ORDERBYB.gradeDESC,任務(wù)3查詢選修了“Illustrator平面設(shè)計(jì)”課程的學(xué)生的姓名和課程成績，并按成績降序排列,【執(zhí)行結(jié)果】,任務(wù)3查詢選修了“Illustrator平面設(shè)計(jì)”課程的學(xué)生的姓名和課程成績，并按成績降序排列,知識(shí)說明,連接查詢連接查詢是關(guān)系型數(shù)據(jù)庫中重要的查詢類型之一，通過表間的相關(guān)字段，可以追蹤各個(gè)表之間的邏輯關(guān)系，從而實(shí)現(xiàn)跨表間的查詢。,知識(shí)說明,連接查詢的種類表52JOIN關(guān)鍵字的含義,知識(shí)說明,內(nèi)連接的種類等值連接在連接條件中使用等號(hào)(=)運(yùn)算符比較被連接列的列值，其查詢結(jié)果中列出被連接表中的任何列，包括其中的重復(fù)列。自然連接自然連接是等值連接的一種特殊情況，即在連接條件中使用等于(=)運(yùn)算符比較被連接列的列值，但它使用選擇列表指出查詢結(jié)果集合中所包括的列，并刪除連接表中的重復(fù)列。不等值連接在連接條件使用除等于運(yùn)算符以外的其他比較運(yùn)算符比較被連接的列的列值。這些運(yùn)算符包括、=、!。,模塊5-3多表查詢子查詢,學(xué)習(xí)目標(biāo),工作任務(wù),任務(wù)1查詢和“孫曉龍”同班的學(xué)生信息,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為student。最后要顯示的信息為與孫曉龍同班的學(xué)生的所有信息。解決這個(gè)問題可以分兩步走，首先查出孫曉龍所在的班級(jí)編碼，然后再以班級(jí)編碼作為查詢條件找出該班所有學(xué)生的信息。子查詢的本質(zhì)就是嵌套查詢，內(nèi)層查詢的結(jié)果作為外層查詢的條件來進(jìn)行查詢。,【程序代碼】USEstudentGOSELECT*FROMstudentWHEREclassno=(SELECTclassnoFROMstudentWHEREsname=孫曉龍),任務(wù)1查詢和“孫曉龍”同班的學(xué)生信息,【執(zhí)行結(jié)果】,任務(wù)1查詢和“孫曉龍”同班的學(xué)生信息,任務(wù)2查詢比“07010111”班學(xué)生入學(xué)成績都高的其他班的學(xué)生的學(xué)號(hào)和姓名,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為student。最后要顯示的信息為三列，分別是學(xué)生學(xué)號(hào)、姓名和班級(jí)編碼。解決這個(gè)問題可以利用帶ALL的子查詢，首先從student表中找出“07010111”班的所有學(xué)生的入學(xué)成績，然后再將此作為父查詢的條件，從表student找出比該班所有學(xué)生的入學(xué)成績均高且不在該班的學(xué)生信息。,【程序代碼】USEstudentGOSELECTsno,sname,classnoFROMstudentWHEREsscoreALL(SELECTsscoreFROMstudentWHEREclassno=07010111)ANDclassno07010111,任務(wù)2查詢比“07010111”班學(xué)生入學(xué)成績都高的其他班的學(xué)生的學(xué)號(hào)和姓名,【執(zhí)行結(jié)果】,任務(wù)2查詢比“07010111”班學(xué)生入學(xué)成績都高的其他班的學(xué)生的學(xué)號(hào)和姓名,任務(wù)3查詢選修課考試不及格的學(xué)生的學(xué)號(hào)和姓名,【任務(wù)分析】根據(jù)任務(wù)要求，此查詢要用到的數(shù)據(jù)庫為student，用到的表為student和choice。最后要顯示的信息為兩列，一列是學(xué)生學(xué)號(hào)，一列為學(xué)生姓名。解決這個(gè)問題可以利用子查詢，首先從choice表中找出考試不及格的學(xué)生的學(xué)號(hào)，然后再將此作為父查詢的條件，從表student找出學(xué)生的姓名。,任務(wù)4查詢選修了課程編號(hào)為“0101001”的學(xué)生的學(xué)號(hào)和姓名,【任務(wù)代碼】USEstudentGOSELECTsno,snameFROMstudentWHEREEXISTS(SELECT*FROMchoiceWHEREstudent.sno=choice.snoANDcno=0101001),【執(zhí)行結(jié)果】,任務(wù)4查詢選修了課程編號(hào)為“0101001”的學(xué)生的學(xué)號(hào)和姓名,知識(shí)說明,子查詢子查詢也稱為內(nèi)部查詢，而包含子查詢的語句也稱為外部查詢或父查詢。子查詢是一個(gè)SELECT語句，它嵌套在一個(gè)SELECT語句、SELECT.INTO語句、INSERT.INTO語句、DELETE語句、或UPDATE語句或嵌套在另一子查詢中。子查詢的SELECT查詢總是使用圓括號(hào)括起來。它不能包含COMPUTE或者FORBROWSE子句，如果同時(shí)指定了TOP子句，則只能包含