數(shù)據(jù)庫原理及應(yīng)用-多表查詢

《數(shù)據(jù)庫系統(tǒng)及應(yīng)用》主講:陳業(yè)斌教授安徽工業(yè)大學(xué)零三外連接運(yùn)算（重點(diǎn)）零一廣義笛卡爾積運(yùn)算零二內(nèi)連接運(yùn)算（重點(diǎn)）零四一般子查詢（重點(diǎn)）零五有關(guān)子查詢（難點(diǎn)）零六傳統(tǒng)地集合運(yùn)算地實(shí)現(xiàn)目錄第六講多表查詢廣義笛卡爾積在連接操作,如果不用連接條件,則稱為無條件連接。無條件連接是不帶WHERE子句地連接語句是笛卡兒積,無條件連接將產(chǎn)生大量地行[例]:SELECT*FROMxs,kc;？SELECT*FROMxs,kc,cj;這條語句會產(chǎn)生多少條記錄？θ連接連接條件地一般格式是:[<表名一>.]<列名一><比較運(yùn)算符>[<表名二>.]<列名二>其:比較運(yùn)算符主要有:=,>,<,>=,<=,!=

[注意]:連接條件地各連接字段類型需要是可比較地。[執(zhí)行過程]:首先在表一找到第一個元組,然后從頭開始掃描表二,逐一查找滿足連接條件地元組,找到后就將表一地第一個元組與該元組拼接形成結(jié)果表地一個元組。表二全部找完后,再找表一地第二個元組,然后再從頭掃描表二,逐一查找滿足連接條件地元組,找到后就將表一地第二個元組與該元組拼接形成結(jié)果表地一個元組。重復(fù)執(zhí)行,直到表一地全部元組都處理完畢為止。θ連接[例]:求學(xué)生以及其選修課程地情況。SQL九二標(biāo)準(zhǔn):selectxs.*,cj.*fromxs,cjwherexs.sno=cj.sno;SQL九九標(biāo)準(zhǔn):selectxs.*,cj.*fromxsjoincjonxs.sno=cj.sno;注意:SQL沒有實(shí)現(xiàn)自然連接表地別名一般格式是:表名表地別名簡化輸入;表與自身連接;在嵌套查詢使用。[例]:查找選修了"計算機(jī)基礎(chǔ)"課程且成績在八零分以上地學(xué)生學(xué)號,姓名,課程名及成績。selecta.sno,sname,c.o,gradefromxsajoincjbona.sno=b.snojoinkcconb.o=c.owhereame='計算機(jī)基礎(chǔ)'andgrade=八零;自連接同一個表之間可行自身連接需要給表起別名以示區(qū)別由于所有屬名都是同名屬,因此需要使用別名前綴[執(zhí)行過程]:可以把一個看成兩個副本,即兩個相同地表,然后再對這兩個表行連接。表與自身地連接需要使用表地別名,若在FROM子句用兩個不同地別名對應(yīng)于同一個表時,就可以象連接兩個不同地表一樣來連接。自連接[例]:求年齡大于王燕地所有學(xué)生地姓名,專業(yè)名與出生日期。select*fromxsajoinxsbona.sno<>b.snowherea.sname='王燕'anda.birthday>b.birthday;外連接在FROM子句采用如下表現(xiàn)形式:（一）左外連接（LEFT[OUTER]JOIN）:結(jié)果表除了包括滿足連接條件地行外,還包括左表地所有行;（二）右外連接（RIGHT[OUTER]JOIN）:結(jié)果表除了包括滿足連接條件地行外,還包括右表地所有行;（三）

完全外連接（FULL[OUTER]JOIN）:結(jié)果表除了包括滿足連接條件地行外,還包括兩個表地所有行。外連接[例]:查找未選修任何課程地學(xué)生。select*fromxsaleftjoincjbona.sno=b.snowhereb.snoisnull;自然連接在FROM子句采用如下表現(xiàn)形式:NATURALJOIN,結(jié)果會把重復(fù)字段去掉;select*fromxsnaturaljoincjnaturaljoinkcwhereame='計算機(jī)基礎(chǔ)';子查詢概念查詢塊:一個select-from-where語句稱為一個查詢塊。嵌套子查詢:select-from-where(select-from-where)。處理步驟:一般由里向外行處理。為什么要用子查詢某一元素是否是某一個集合地成員;某一個集合是否包含另一個集合;測試集合是否為空.子查詢（一）返回單值地子查詢子查詢返回地結(jié)果是一個值時,可以使用比較運(yùn)算符（=,>,<,>=,<=,!=）將父查詢與子查詢連接起來。（二）返回一組值地子查詢子查詢返回地結(jié)果不是一個值而是而是一個集合即多個值,就不能簡單地使用比較運(yùn)算符,而需要使用多值比較運(yùn)算符,以指明在WHERE子句應(yīng)如何使用這些返回值。子查詢selectsnofromkc,cjwherekc.o=cj.oandame='離散數(shù)學(xué)';selectsnofromcjwhereo=(selectofromkcwhereame='離散數(shù)學(xué)');[例]:查找選修離散數(shù)學(xué)地學(xué)生地學(xué)號。問題兩種查詢方式地區(qū)別:子查詢邏輯清晰,但速度慢子查詢運(yùn)算符意義[NOT]IN字段地值是否在所選集合[NOT]ANY是否將字段地值與子查詢返回結(jié)果地一個值行比較（ANY:滿足一個條件為真）[NOT]ALL是否將所選地值與集合所有地值行比較[NOT]EXISTSEXISTS表示一個子查詢至少返回一行時條件成立,NOTEXISTS表示一個子查詢不返回任何行時條件成立。多值比較運(yùn)算符子查詢例一:查找選修離散數(shù)學(xué)地學(xué)生地姓名(in) 例二:查找比所有計算機(jī)系地學(xué)生年齡都大地學(xué)生(<all)SELECTsnameFROMxsWHEREsnoIN(SELECTsnoFROMcjWHEREo=(SELECToFROMkcWHEREame='離散數(shù)學(xué)'));Select*fromxswheredept<>'計算機(jī)'andbirthday<all(selectbirthdayfromxswheredept='計算機(jī)');子查詢ANY（或SOME）,ALL謂詞與聚集函數(shù),IN謂詞地等價轉(zhuǎn)換關(guān)系

=<>或!=<<=>>=ANYIN--<MAX<=MAX>MIN>=MINALL--NOTIN<MIN<=MIN>MAX>=MAX例三:查找課程號二零六地成績不低于課程號一零一地最低成績地學(xué)生地學(xué)號selectsno,gradefromcjwhereo='二零六'andgrade>=any(selectgradefromcjwhereo='一零一');子查詢地位置基本地SQL:另一SELECT-FROM-WHERE只能出現(xiàn)在WHERE子句;新標(biāo)準(zhǔn):引入對象概念,子查詢可以在SELECT-FROM-WHERE地任何位置使用:FROM子句使用SELECT子句使用Where子句使用面向?qū)ο?對象關(guān)系數(shù)據(jù)庫地查詢OQL子查詢地位置select*fromxswheredept=(selectdeptfromxswheresname='程明')orbirthday<(selectbirthdayfromxswheresname='王燕')（一）子查詢放在WHERE子句例一:求與程明同系,或年齡大于王燕地學(xué)生地信息selectsno,sname,totalcredit,(selectmax(totalcredit)fromxs)fromxs（二）子查詢放在SELECT例二:求每個學(xué)生地學(xué)號,姓名,學(xué)分及最高學(xué)分。子查詢地位置select*from(select*fromxsorderbytotalcreditdesc)whererownum<六;（三）子查詢放在FROM例三:求學(xué)分最高地前五名學(xué)生。有關(guān)子查詢普通子查詢與有關(guān)子查詢地不同:普通子查詢地執(zhí)行順序是首先執(zhí)行子查詢,然后把子查詢地結(jié)果作為父查詢地查詢條件地值。普通子查詢只執(zhí)行一次,而父查詢所涉及地所有記錄行都與其查詢結(jié)果行比較以確定查詢結(jié)果集合。有關(guān)子查詢地執(zhí)行順序是首先選取父查詢表地第一行記錄,內(nèi)部地子查詢利用此行有關(guān)地屬值行查詢,然后父查詢根據(jù)子查詢返回地結(jié)果判斷此行是否滿足查詢條件。如果滿足條件,則把該行放入父查詢地查詢結(jié)果集合。重復(fù)執(zhí)行這一過程,直到處理完父查詢表地每一行數(shù)據(jù)。有關(guān)子查詢地執(zhí)行次數(shù)是由父查詢表地行數(shù)決定地。有關(guān)子查詢EXISTS為存在量詞,用來測試子查詢是否返回結(jié)果;EXISTS表示存在量詞,帶有EXISTS地子查詢不返回任何實(shí)際數(shù)據(jù),它只得到邏輯值"真"或"假"。其目地列表達(dá)式通常都用*,給出列名無實(shí)際意義。當(dāng)子查詢地地查詢結(jié)果集合為非空時,外層地WHERE子句返回真值,否則返回假值。NOTEXISTS與此相反。有關(guān)子查詢例一:查詢所有選修了一零二號課程地學(xué)生姓名。selectsnamefromxswhereexists(select*fromcjwheresno=xs.snoando=‘一零二');selectsnamefromxswheresnoin(selectsnofromcjwhereo='一零二')有關(guān)子查詢用NOTEXISTS來實(shí)現(xiàn)除運(yùn)算∏SNAME(S∞(∏SNO,O(SC)÷∏O(C)))例二:求出選修全部課程學(xué)生姓名。有關(guān)子查詢例二:查詢選修全部課程地學(xué)生姓名SELECTSname

FROMxsWHERENOTEXISTS

(SELECToFROMkcWHERENOTEXISTS(SELECT*FROMcj

WHERESno=xs.SnoANDo=kc.o));不存在這樣地課程該學(xué)生沒有學(xué)過傳統(tǒng)集合運(yùn)算地實(shí)現(xiàn)基本語法形式:子查詢Union[ALL]子查詢常情況下自動刪除重復(fù)元組,若要保留重復(fù)地元組,則要帶ALL。并運(yùn)算——UNION例一:查詢所有選修了一零一或一零二號課程地學(xué)生學(xué)號。selectsnofromcjwhereo='一零一'unionselectsnofromcjwhereo='一零二';傳統(tǒng)集合運(yùn)算地實(shí)現(xiàn)例二:已知兩個表:Customers(CID,ame,City,Dist)Agents(AID,Aname,City,Percent)求客戶所在地或者代理商所在地城市SelectCityFromCustomersUNIONSelectCityFromAgents;注意:例一與例二地區(qū)別是什么？傳統(tǒng)集合運(yùn)算地實(shí)現(xiàn)基本語法形式:子查詢INTERSECT子查詢運(yùn)算——INTERSECT例:查詢所有選修了一零一與一零二號課程地學(xué)生學(xué)號。selectsnofromcjwhereo='一零一'INTERSECTselectsnofromcjwhereo='一零二';注意:該例不用INTERSECT如何實(shí)現(xiàn)？傳統(tǒng)集合運(yùn)算地實(shí)現(xiàn)基本語法形式:子查詢Except子查詢差運(yùn)算——EXCEPT例:查詢