數(shù)據(jù)庫原理第9講

四、使用集函數(shù)5類主要集函數(shù)計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計(jì)算總和SUM（[DISTINCT|ALL]<列名>） 計(jì)算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）.使用集函數(shù)（續(xù)）求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短語：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值A(chǔ)LL為缺省值.使用集函數(shù)（續(xù)）[例26]查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent；

[例27]查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù).使用集函數(shù)（續(xù)）[例28]計(jì)算1號課程的學(xué)生平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'；

[例29]查詢選修1號課程的學(xué)生最高分?jǐn)?shù)。

SELECTMAX(Grade)FROMSCWHERCno='1'；.五、對查詢結(jié)果分組使用GROUPBY子句分組 細(xì)化集函數(shù)的作用對象未對查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果對查詢結(jié)果分組后，集函數(shù)將分別作用于每個(gè)組.使用GROUPBY子句分組[例30]求各個(gè)課程號及相應(yīng)的選課人數(shù)。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

結(jié)果

CnoCOUNT(Sno)

122

234 344 433 548.對查詢結(jié)果分組（續(xù)）GROUPBY子句的作用對象是查詢的中間結(jié)果表分組方法：按指定的一列或多列值分組，值相等的為一組使用GROUPBY子句后，SELECT子句的列名列表中只能出現(xiàn)分組屬性和集函數(shù)

.使用HAVING短語篩選最終輸出結(jié)果[例31]查詢選修了3門以上課程的學(xué)生學(xué)號。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

.例題[例32]查詢有3門以上課程是90分以上的學(xué)生的學(xué)號及（90分以上的）課程數(shù)

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(*)>=3;

.使用HAVING短語篩選最終輸出結(jié)果只有滿足HAVING短語指定條件的組才輸出HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語作用于組，從中選擇滿足條件的組。

.3.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結(jié).3.3.3連接查詢同時(shí)涉及多個(gè)表的查詢稱為連接查詢用來連接兩個(gè)表的條件稱為連接條件或連接謂詞一般格式：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符：=、>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>.連接查詢（續(xù)）連接字段連接謂詞中的列名稱為連接字段連接條件中的各連接字段類型必須是可比的，但不必是相同的.連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個(gè)元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。表2全部查找完后，再找表1中第二個(gè)元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢.排序合并法(SORT-MERGE)常用于=連接首先按連接屬性對表1和表2排序?qū)Ρ?的第一個(gè)元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時(shí)，對表2的查詢不再繼續(xù).排序合并法找到表1的第二條元組，然后從剛才的中斷點(diǎn)處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。直接遇到表2中大于表1連接字段值的元組時(shí)，對表2的查詢不再繼續(xù)重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止.索引連接(INDEX-JOIN)對表2按連接字段建立索引對表1中的每個(gè)元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組.連接查詢（續(xù)）SQL中連接查詢的主要類型

廣義笛卡爾積 等值連接(含自然連接)

非等值連接查詢 自身連接查詢 外連接查詢 復(fù)合條件連接查詢.一、廣義笛卡爾積不帶連接謂詞的連接很少使用例：

SELECTStudent.*,SC.*FROMStudent,SC.二、等值與非等值連接查詢等值連接、自然連接、非等值連接[例32]查詢每個(gè)學(xué)生及其選修課程的情況。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；.等值連接連接運(yùn)算符為=的連接操作

[<表名1>.]<列名1>=[<表名2>.]<列名2>任何子句中引用表1和表2中同名屬性時(shí)，都必須加表名前綴。引用唯一屬性名時(shí)可以加也可以省略表名前綴。.等值連接假設(shè)Student表、SC表分別有下列數(shù)據(jù)：

Student表SnoSnameSsexSageSdept95001

李勇

男20CS95002

劉晨

女19IS95003

王敏

女18MA95004

張立

男19IS.等值連接SC表SnoCnoGrade9500119295001285950019500295002323889080.等值連接結(jié)果表

Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade

95001李勇男20 CS 9500119295001李勇男20 CS 9500128595001李勇男20 CS 9500138895002劉晨女19 IS 9500229095002劉晨女19 IS 95002380

.自然連接等值連接的一種特殊情況，把目標(biāo)列中重復(fù)的屬性列去掉。[例33]對[例32]用自然連接完成。

SELECTStudent.Sno，Sname，Ssex，Sage， Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；.非等值連接查詢連接運(yùn)算符不是=的連接操作

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>比較運(yùn)算符：>、<、>=、<=、!=[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

.三、自身連接一個(gè)表與其自己進(jìn)行連接，稱為表的自身連接需要給表起別名以示區(qū)別由于所有屬性名都是同名屬性，因此必須使用別名前綴.自身連接（續(xù)）[例34]查詢每一門課的間接先修課（即先修課的先修課）

SELECTFIRST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；.自身連接（續(xù)）FIRST表（Course表）

CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64.自身連接（續(xù)）SECOND表（Course表）

CnoCnameCpnoCcredit1數(shù)據(jù)庫542數(shù)學(xué)

23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理

27PASCAL語言64.自身連接（續(xù)）查詢結(jié)果

173556

cnocpno.四、外連接（OuterJoin）外連接與普通連接的區(qū)別普通連接操作只輸出滿足連接條件的元組外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出.外連接（續(xù)）

[例33]查詢每個(gè)學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生----用外連接操作SELECTStudent.Sno，Sname，Ssex， Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；.外連接（續(xù)）結(jié)果：

Student.Sno SnameSsexSageSdeptCnoGrade

95001李勇男20CS19295001李勇男20CS28595001李勇男20CS38895002劉晨女19IS29095002劉晨女19IS38095003王敏女18MA95004張立男19IS.外連接（續(xù)）在表名后面加外連接操作符(*)或(+)指定非主體表非主體表有一“萬能”的虛行，該行全部由空值組成虛行可以和主體表中所有不滿足連接條件的元組進(jìn)行連接由于虛行各列全部是空值，因此與虛行連接的結(jié)果中，來自非主體表的屬性值全部是空值.外連接（續(xù)）

左外連接

外連接符出現(xiàn)在連接條件的左邊 右外連接

外連接符出現(xiàn)在連接條件的右邊

.五、復(fù)合條件連接WHERE子句中含多個(gè)連接條件時(shí)，稱為復(fù)合條件連接[例35]查詢選修2號課程且成績在90分以上的所有學(xué)生的學(xué)號、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoAND