數據庫應用技術教程 課件 第4章 數據查詢2

課前練習1、查詢所有選課學生的學號2、查詢考試成績有不及格的學生的學號3、查詢既不姓鄭又不姓王的學生學號和姓名4、查詢信息系和數學系的學生姓名和所在系（6）多重條件查詢在WHERE子句中可以使用邏輯運算符AND和OR來組成多條件查詢。用AND連接的條件表示必須全部滿足所有的條件的結果才為True；用OR連接的條件表示只要滿足其中一個條件結果即為True。例24．查詢計算機系年齡在20歲以下的學生姓名。

SELECTSnameFROMStudentWHERESdept='計算機系'

ANDSage<20

示例例25．查詢計算機系和信息系年齡大于等于21歲的學生姓名、所在系和年齡。SELECTSname,Sdept,SageFROMStudentWHERE(Sdept='計算機系'

ORSdept='信息系')ANDSage>=21或：SELECTSname,Sdept,SageFROMStudentWHERESdeptIN('計算機系','信息系')ANDSage>=21

4.2簡單查詢對查詢結果進行排序

對查詢結果進行排序(orderby)排序子句為：

ORDERBY<列名>[ASC|DESC][,<列名>…]說明：按<列名>進行升序（ASC）或降序（DESC）排序，默認是按升序進行排序。示例例26．將學生按年齡的升序排序。

SELECT*FROMStudentORDERBYSage例27．查詢選修了3號課程的學生的學號及其成績，查詢結果按成績降序排列。SELECTSno,GradeFROMSC WHERECno='3'

ORDERBYGradeDESC

例28.查詢全體學生的信息，查詢結果按所在系的系名升序排列，同一系的學生按年齡降序排列。SELECT*FROMStudent

ORDERBYSdept,SageDESC

4.2簡單查詢使用聚合函數匯總數據

使用計算函數匯總數據SQL提供的聚合函數有：COUNT（*）：統計表中元組個數；COUNT（[DISTINCT]<列名>）：統計本列列值個數；SUM（[DISTINCT]<列名>）：計算列值總和；AVG（[DISTINCT]<列名>）：計算列值平均值；MAX（[DISTINCT]<列名>）：求列值最大值；MIN（[DISTINCT]<列名>）：求列值最小值。上述函數中除COUNT（*）外，其他函數在計算過程中均忽略NULL值。示例例29．統計學生總人數。

SELECTCOUNT(*)FROMStudent

例30．統計選修了課程的學生的人數。

SELECTCOUNT(DISTINCTSno)FROMSC例31．統計95002號學生的考試總成績之和。SELECTSUM(Grade)FROMSCWHERESno='95002'

示例例32．計算選修1號課程學生的考試平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'例33．查詢1號課程的考試最高分和最低分。

SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno='1'注意：計算函數不能出現在WHERE子句中示例例34．查詢“95001”學生的選課門數、考試課程門數以及考試最高分、最低分和平均分。SELECTCOUNT(*)AS選課門數,

COUNT(Grade)AS考試門數,MAX(Grade)AS最高分,

MIN(Grade)AS最低分,AVG(Grade)AS平均分FROMSCWHERESno='95001'4.2簡單查詢對查詢結果進行分組統計

對查詢結果進行分組統計

作用：可以控制計算的級別：對全表還是對每一分組。目的：細化計算函數的作用對象。分組語句的一般形式： GROUPBY<分組依據列>[,…n]

[HAVING<組選擇條件>]（1）使用GROUPBY例35．統計每門課程的選課人數，列出課程號和人數。SELECTCnoas課程號,

COUNT(Sno)as選課人數FROMSCGROUPBYCno

對查詢結果按Cno的值分組，所有具有相同Cno值的元組為一組，然后再對每一組使用COUNT計算，求得每組的學生人數。SnoCnoGrade95001187950012769500137995001480950021899500418395004256CnoCount(Sno)13223141SnoCnoGrade95001187950021899500418395001276950042569500137995001480例36．查詢每個學生的選課門數和最高分。SELECTSnoas學號,

COUNT(*)as選課門數,

MAX(Grade)as最高成績FROMSCGROUPBYSno注意GROUPBY子句中的分組依據列必須是表中存在的列名，不能使用AS指派的列別名。例如，例36中不能將GROUBY子句寫成：GROUPBY學號。帶有GROUPBY子句的SELECT語句的查詢列表中只能出現分組依據列或聚合函數，因為分組后每個組只返回一行結果。示例例37．統計每個系的學生人數和平均年齡。SELECTSdept,COUNT(*)AS學生人數,

AVG(Sage)AS平均年齡

FROMStudentGROUPBYSdept示例例38.帶WHERE子句的分組。統計每個系的男生人數。SELECTSdept,Count(*)男生人數

FROMStudentWHERESsex='男'

GROUPBYSdept示例例39.按多列分組。統計每個系的男生人數和女生人數，以及男生的最大年齡和女生的最大年齡。結果按系名的升序排序。SELECTSdept,Ssex,Count(*)人數,

Max(Sage)最大年齡FROMStudentGROUPBYSdept,SsexORDERBYSdept（2）使用HAVINGHAVING用于對分組進行篩選，它有點象WHERE子句，但它用于分組而不是對單個記錄。如：查詢修了3門以上課程的學生的學號

SELECTSnoFROMSCGROUPBYSno

HAVINGCOUNT(*)>3

示例例40.查詢選修了3門以上課程的學生的學號和選課門數。SELECTSno,Count(*)選課門數

FROMSCGROUPBYSnoHAVINGCOUNT(*)>3處理過程：先執行GROUPBY子句對SC表數據按Sno進行分組，然后再用統計函數COUNT分別對每一組進行統計，最后篩選出統計結果滿足大于3的組。分組統計篩選處理過程示意圖示例例41．查詢修課門數等于或大于4的學生的平均成績和選課門數。

SELECTSno,AVG(Grade)平均成績,

COUNT(*)修課門數

FROMSCGROUPBYSno

HAVINGCOUNT(*)>=4

幾個子句比較WHERE子句用來篩選FROM子句中指定的數據源所產生的行數據。GROUPBY子句用來對經WHERE子句篩選后的結果數據進行分組。HAVING子句用來對分組后的結果數據再進行篩選。示例例42.查詢計算機系和數學系的學生人數。方法1：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSdeptIN('計算機系','數學系')方法2：SELECTsdept,COUNT(*)FROMStudentWHERESdeptIN('計算機系','數學系')GROUPBYSdept第二種寫法先篩選數據，再分組，效率更高。示例例43.查詢每個系年齡小于等于21歲的學生人數。SELECTSdept,COUNT(*)FROMStudentWHERESage<=21