數(shù)據(jù)庫第9章-選擇查詢

第9章選擇查詢9.1聚合函數(shù)聚合函數(shù)用以在查詢中生成對數(shù)據(jù)的統(tǒng)計結果。常用聚合函數(shù)CountSumAvgMaxMinCount用于對符合查詢條件的記錄進行計數(shù)。例1．統(tǒng)計姓名中含有“小”的學生人數(shù)。SELECTCount(*)FROM2003學生名單WHERE姓名Like"*小*"例2．統(tǒng)計學生總人數(shù)。SELECTCount(*)FROM2003學生名單Sum用來返回在查詢的指定字段中所包含的一組值的總計。

例3．求031國貿(mào)1班的學生的入學成績總和。 SELECT“031國貿(mào)1”AS班級,Sum(入學成績)AS總成績 FROM2003學生名單 WHERE班級=“031國貿(mào)1"Avg用于計算查詢的指定字段中包含的一組值的算術平均值。執(zhí)行時，先逐條記錄進行檢查，如果某記錄的指定字段為空值或者某記錄不符合篩選條件，就忽略該記錄，否則參加計算。最后得到的結果是符合篩選條件的所有有效字段值的平均值。在沒有任何符合條件的記錄的情況下，該值為0。Avg例3．求所有學生的入學成績的平均值。SELECTAvg(入學成績)FROM2003學生名單本例中，放在SELECT后的Avg(入學成績)為一表達式，描述將生成一個數(shù)據(jù)列，該列的值為對入學成績字段求平均值。本例沒有對記錄進行條件篩選，因此是對全部學生的入學成績求平均值。Avg例4．求“031國貿(mào)3班”的“男”同學的平均入學成績。SELECTAvg(入學成績)AS平均成績FROM2003學生名單WHERE班級=“031國貿(mào)3"AND性別="男"Avg例5．求02科室男醫(yī)生的平均年齡。SELECTAvg(Year(DATE())-Year(出生日期))AS平均年齡FROM醫(yī)生WHERE科室號=“02”AND性別=“男"Max、MinMax、Min函數(shù)用來返回在查詢的指定字段內(nèi)所包含的一組值中的最大值或最小值。Max和Min函數(shù)的參數(shù)可以是文本、數(shù)字、日期等類型的字段或表達式。如果是表達式，將先進行指定的計算，然后用表達式的值進行比較選擇。

例7．查詢年齡最大和最小的學生的出生日期。SELECTMin(出生日期),Max(出生日期)FROM2003學生名單Max、Min例8．統(tǒng)計學生人數(shù)、最低入學成績、最高入學成績和平均成績。SELECTCount(*)AS學生人數(shù), Min(入學成績)AS最低分, Max(入學成績)AS最高分, Avg(入學成績)AS平均分

FROM

2003學生名單本例同時進行了四項統(tǒng)計處理，其結果為四個值。9.2分組查詢在進行統(tǒng)計處理時，往往需要進行分類統(tǒng)計，就是說按一定的規(guī)則將表中的數(shù)據(jù)記錄分成若干組，對每組數(shù)據(jù)進行統(tǒng)計。完成分組統(tǒng)計時，每一組的統(tǒng)計結果形成一行數(shù)據(jù)，最終由這些數(shù)據(jù)行形成統(tǒng)計查詢的結果集。例如，如果學生來自20個不同的班級，當按“班級”進行人數(shù)統(tǒng)計時，系統(tǒng)將會把學生記錄按不同的班級值分成形成20個組，對每組中的記錄進行計數(shù)，最后形成20條記錄。9.2分組查詢進行分組統(tǒng)計的關鍵在于如何對數(shù)據(jù)記錄進行分組。

GROUPBY<分組字段表>分組字段表中可以有1到10個字段名。在結果集中，將形成與每個分組相應的記錄，并在該記錄行中存放規(guī)定的計算值。9.2.1分組查詢示例例9．統(tǒng)計來自不同地區(qū)的學生人數(shù)。SELECT地區(qū),Count(*)AS人數(shù)FROM學生情況

GROUPBY地區(qū)有多少個不同的地區(qū)，就把記錄分成多少個組，最終每組的統(tǒng)計結果形成一行。在設計分組查詢時，應該注意SELECT字段列表中單獨出現(xiàn)的所有字段都必須包括在GROUPBY子句中，如果不包含則出錯。9.2.1分組查詢示例例10．統(tǒng)計來自不同科室、不同職稱的男醫(yī)生人數(shù)，結果按職稱升序排序。SELECT科室號,職稱,Count(*)AS人數(shù)FROM醫(yī)生WHERE性別="男"GROUPBY科室號,職稱ORDERBY職稱如果在查詢中使用了WHERE子句，分組時那些不符合查詢條件的記錄將被篩選掉。9.2.1分組查詢示例例11．統(tǒng)計各班學生的人數(shù)、平均入學成績，結果按入學成績的平均值排序。SELECT班級,Count(*)AS人數(shù),Avg(入學成績)AS平均成績FROM2003學生名單GROUPBY班級

ORDERBYAvg(入學成績)在查詢中可以使用多個聚合函數(shù)，并且可以在聚合函數(shù)中使用相關字段，在聚合函數(shù)中使用的字段不必出現(xiàn)在GROUPBY子句中。如果在GROUPBY子句中使用了某些字段，但沒有在SELECT字段列表中列出，分組是可以進行的，只不過無法看到按什么字段進行的分組及分組值的具體情況了。所以，一般情況下，分組字段應該在SELECT字段列表中列出。9.2.1分組查詢示例9.2.2篩選分組查詢的結果在進行分組查詢時，如果只對結果集中的某些數(shù)據(jù)感興趣，可以再用HAVING子句對分組的結果記錄再進一步篩選，使得最終在結果集中保留的是那些滿足HAVING子句條件的那些記錄。9.2.2篩選分組查詢的結果例12．列舉學生人數(shù)大于40人的班級。SELECT班級,Count(*)AS人數(shù)FROM

2003學生名單GROUPBY班級HAVINGCount(*)>40HAVING子句通常與GROUPBY子句配合使用，因為它的作用是對分組查詢的結果進行篩選。HAVING子句中的表達式用來描述篩選條件，在表達式中應該用到SELECT字段列表中的一個或多個字段，如果是對聚合函數(shù)的計算值進行篩選，則應該直接用聚合函數(shù)構成條件表達式。9.2.2篩選分組查詢的結果9.2.2篩選分組查詢的結果例13．列舉參加考試的學生人數(shù)少于30的課程，結果按考試人數(shù)的統(tǒng)計值排序。SELECT課程號,Count(*)AS考試人數(shù)FROM成績GROUPBY課程號HAVINGCount(*)<30ORDERBYCount(*)基于“生源情況”表完成下列查詢統(tǒng)計不同的姓的人數(shù)，列出：姓、人數(shù)，結果按人數(shù)降序排序。按地區(qū)分類統(tǒng)計“入學成績”的平均值、最大值、最小值，并按平均值降序排序。找出年齡平均值最高的班級。列出學生人數(shù)少于100的地區(qū)。9.3聯(lián)接查詢學院（學院編號，學院名稱）班級（班級編號，班級名稱，學院編號，班級人數(shù)）學生（學號，姓名，性別，出生日期，班級編號，民族，地區(qū)，政治面貌）課程（課程編號，課程名稱，學院編號，考核類型）學生考試（ID，學號，場次，課程編號，教室）教師（教師編號，姓名，學院編號）教室(教室，校區(qū))場次（場次）教師監(jiān)考（ID，教師編號，場次，課程編號，教室）9.3.1內(nèi)部聯(lián)接例14．查詢所有教師數(shù)據(jù)，顯示他們的教師編號、姓名和所在學院名稱，結果按教師編號排序。教師（教師編號，姓名，學院編號）學院（學院編號，學院名稱）教師編號，

姓名，

學院名稱SELECT教師編號,姓名,學院名稱FROM教師INNERJOIN學院

ON教師.學院編號=學院.學院編號ORDERBY教師編號9.3.1內(nèi)部聯(lián)接教師INNERJOIN學院

ON教師.學院編號=學院.學院編號在內(nèi)部聯(lián)接中描述聯(lián)接條件時，通常使用等號來構成聯(lián)接表達式，并且引用左、右表中具有相同意義和類型的字段。在處理具有一對多聯(lián)系的表間的聯(lián)接查詢時，通常是通過兩個表的主鍵和外鍵進行聯(lián)接。內(nèi)部聯(lián)接要求聯(lián)接雙方具有完全對應的行。否則，一個表中存在的某行而在另一個表中找不到具有相同值的行時，這一行就不可能被查詢到。左表右表聯(lián)接條件9.3.1內(nèi)部聯(lián)接例15．查詢所有來自四川地區(qū)的男同學的學號、姓名、班級名稱和學院名稱，結果按學號排序。SELECT學生.學號,學生.姓名,班級.班級名稱,學院.學院名稱FROM

(學生INNERJOIN班級

ON學生.班級編號=班級.班級編號)INNERJOIN學院

ON班級.學院編號=學院.學院編號WHERE學生.地區(qū)="四川"AND學生.性別="男"ORDERBY學生.學號三個表的內(nèi)聯(lián)接9.3.1內(nèi)部聯(lián)接例16．查詢“商務學院”所有學生的各項數(shù)據(jù)及班級名稱，結果按學號排序。SELECT班級.班級名稱,學生.*FROM學生,班級,學院WHERE

(學生.班級編號=班級.班級編號)AND (班級.學院編號=學院.學院編號)AND

(學院.學院名稱="商務學院")ORDERBY學生.學號在WHERE部分描述聯(lián)接9.3.2外部聯(lián)接在進行聯(lián)接查詢時，如果希望某個表中的記錄在另一個表中找不到完全對應記錄的情況下仍出現(xiàn)在結果集中，可以采用外部聯(lián)接。SQL語言支持用來完成不完全對應的聯(lián)接，此類聯(lián)接稱為外部聯(lián)接。外部聯(lián)接有左外部聯(lián)接和右外部聯(lián)接兩種。9.3.2外部聯(lián)接左外聯(lián)接在左外部聯(lián)接中，對于SQL語句FROM子句中的第一個（左）表，查詢包括所有行；對于另一個（右）表，則只包括兩個表的聯(lián)接字段值能匹配的行。右外聯(lián)接在右外部聯(lián)接中，對于SQL語句FROM子句中的第二個（左）表，查詢包括所有行；對于另一個（右）表，則只包括兩個表的聯(lián)接字段值能匹配的行。9.3.2外部聯(lián)接例17．查詢編號大于“13”的學院的班級目錄SELECT學院.學院名稱,班級.班級名稱,學院.學院編號FROM學院LEFTOUTERJOIN班級

ON學院.學院編號=班級.學院編號WHERE學院.學院編號>="13"9.3.2外部聯(lián)接例18．查詢教師名單，顯示教師編號、姓名和所在學院名稱，結果按學院名稱和教師編號排序。SELECT學院.學院名稱,教師.教師編號,教師.姓名FROM學院RIGHTOUTERJOIN教師

ON學院.學院編號=教師.學院編號ORDERBY學院.學院名稱,教師.教師編號9.3.3自聯(lián)接從一個表中查詢出與該表中某些記錄有聯(lián)系的其他記錄。在同一個表上建立聯(lián)接，以完成對一個表進行一些特殊的查詢。查詢與某同學同班的學生名單查詢與某教師同一時間監(jiān)考的其他教師9.3.3自聯(lián)接例19．查詢方晨所在班級的學生名單SELECT學生B.學號,學生B.姓名FROM學生AS學生AINNERJOIN學生AS學生BON學生A.班級編號=學生B.班級編號WHERE學生