數(shù)據(jù)庫應(yīng)用技術(shù)教程 課件 第4章 數(shù)據(jù)查詢3

上節(jié)知識回顧1、查詢選課人數(shù)大于等于3人的課程號和選課人數(shù)。4.3集合查詢?nèi)绻卸鄠€(gè)不同的查詢結(jié)果集，但又希望將它們按照一定的關(guān)系連接在一起，組成一組數(shù)據(jù)，這就可以用集合運(yùn)算來實(shí)現(xiàn)。UNION（并）、INTERSECT（交）、EXCEPT（差）參加聯(lián)合查詢操作的各查詢結(jié)果的列數(shù)必須相同，對應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同。集合并運(yùn)算（UNION）集合并運(yùn)算是將來自不同查詢結(jié)果集合組合起來，形成一個(gè)查詢結(jié)果集（并集），UNION操作會自動將重復(fù)元組去除。例44：查詢選修了1號課程或選修了2號課程的學(xué)生的學(xué)號。集合交運(yùn)算（INTERSECT）集合交運(yùn)算是將來自不同查詢結(jié)果集合中公共的元組組合起來，形成一個(gè)查詢結(jié)果集（交集）。INTERSECT操作會自動將重復(fù)的元組去除。例45：查詢既選修了1號課程又選修了2號課程的學(xué)生的學(xué)號。集合差運(yùn)算（EXCEPT）集合差運(yùn)算是將屬于左查詢結(jié)果集但不屬于右查詢結(jié)果的元組組合起來，形成一個(gè)查詢查詢集（差集）。例46：查詢選修了1號課程但沒有選修2號課程的學(xué)生的學(xué)號。集合運(yùn)算舉例例47：查詢學(xué)生表，列出除數(shù)學(xué)系的女生外，所有學(xué)生的學(xué)號、姓名、所在系和性別。4.4連接查詢?nèi)粢粋€(gè)查詢同時(shí)涉及兩個(gè)或兩個(gè)以上的表，則稱之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢連接查詢包括內(nèi)連接、自身連接、外連接等。連接基礎(chǔ)知識連接查詢中用于連接兩個(gè)表的條件稱為連接條件或連接謂詞。一般格式為：

[<表名1.>][<列名1>]<比較運(yùn)算符>[<表名2.>][<列名2>]必須語義相同!內(nèi)連接內(nèi)連接語法如下：

SELECT…FROM表名[INNER]JOIN被連接表

ON連接條件執(zhí)行連接操作的過程：首先取表1中的第1個(gè)元組，然后從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第1個(gè)元組與該元組拼接起來，形成結(jié)果表中的一個(gè)元組。表2全部查找完畢后，再取表1中的第2個(gè)元組，然后再從頭開始掃描表2，…重復(fù)這個(gè)過程，直到表1中的全部元組都處理完畢為止。

示例例48．查詢每個(gè)學(xué)生及其選課的詳細(xì)信息。SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno改進(jìn)例1SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.Sno示例例49.查詢計(jì)算機(jī)系學(xué)生的修課情況，要求列出學(xué)生的名字、所修課的課程號和成績。

SELECTSname,Cno,GradeFROMStudentJOINSCONStudent.Sno=SC.SnoWHERESdept='計(jì)算機(jī)系'

表別名可以為表提供別名，其格式如下：

FROM<源表名>[AS]<表別名>為表指定別名可以簡化表的書寫。當(dāng)為表指定了別名，在查詢語句中的其他地方，所有用到表名的地方都要使用別名，而不能再使用原表名。示例例50.查詢信息系修了“數(shù)據(jù)庫”課程的學(xué)生信息，要求列出學(xué)生姓名、課程名和成績。SELECTSname,Cname,GradeFROMStudentsJOINSCONs.Sno=SC.Sno

JOINCoursecONc.Cno=SC.CnoWHERESdept='信息系'

ANDCname='數(shù)據(jù)庫'

示例例51．查詢所有修了C++課程的學(xué)生的修課情況，要求列出學(xué)生姓名、所在系和選課成績。SELECTSname,Sdept，GradeFROMStudentSJOINSCONS.Sno=SC.Sno

JOINCourseCONC.Cno=SC.cnoWHERECname='C++'示例例52.有分組的多表連接查詢。統(tǒng)計(jì)每門課（列出課程名）的學(xué)生考試平均成績。SELECTCname,AVG(grade)asAverageGradeFROMCoursecJOINSCONC.Cno=SC.CnoGROUPBYCname示例例53.有分組和行選擇條件的多表連接查詢。統(tǒng)計(jì)信息系每門課程的選課人數(shù)、平均成績、最高成績和最低成績。SELECTCno,COUNT(*)ASTotal,AVG(Grade)asAvgGrade,MAX(Grade)asMaxGrade,MIN(Grade)asMinGrade

FROMStudentSJOINSCONS.Sno=SC.SnoWHERESdept=‘信息系'

GROUPBYCno2.自身連接為特殊的內(nèi)連接相互連接的表物理上為同一張表。必須為兩個(gè)表取別名，使之在邏輯上成為兩個(gè)表。

FROM表1ASA1--在內(nèi)存中生成“A1”表

JOIN表1ASA2--在內(nèi)存中生成“A2”表示例例54.查詢與劉超華在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在的系。SELECTS2.Sname,S2.SdeptFROMStudentS1JOINStudentS2ONS1.Sdept=S2.SdeptWHERES1.Sname='劉超華'

ANDS2.Sname!='劉超華'示例例55.查詢與“數(shù)據(jù)庫”學(xué)分相同的課程的課程名和學(xué)分。SELECTC1.Cname,C1.CreditFROMCourseC1JOINCourseC2ONC1.Credit=C2.CreditWHEREC2.Cname='數(shù)據(jù)庫'3.外連接只限制一張表中的數(shù)據(jù)必須滿足連接條件，而另一張表中數(shù)據(jù)可以不滿足連接條件。

外連接的語法格式為：

FROM表1LEFT|RIGHT[OUTER]JOIN表2ON<連接條件>示例例56.查詢學(xué)生的修課情況，包括修了課程的學(xué)生和沒有修課的學(xué)生。SELECTStudent.Sno,Sname,Cno,GradeFROMStudentLEFTOUTERJOINSC ONStudent.Sno=SC.Sno示例例57.查詢哪些課程沒有人選，列出其課程號，課程名。SELECTC.cno,CnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoWHERESC.CnoISNULL示例例58.查詢數(shù)學(xué)系沒有選課的學(xué)生，列出學(xué)生的學(xué)號，姓名和性別。SELECTSno,Sname,SsexFROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHERESdept='數(shù)學(xué)系'ANDSC.SnoISNULL示例例59.統(tǒng)計(jì)信息系每個(gè)學(xué)生的選課門數(shù)，包括沒有選課的學(xué)生，結(jié)果按選課門數(shù)遞減排序。SELECTS.Sno學(xué)號,COUNT(SC.Cno)