第6講 多表查詢《數(shù)據(jù)庫原理及應(yīng)用》教學(xué)課件

《數(shù)據(jù)庫原理及應(yīng)用》?精品課件合集03DDL和DML中的子查詢01

連接查詢02子查詢04傳統(tǒng)的集合運(yùn)算目錄第六章多表查詢連接查詢

若一個查詢同時涉及兩個以上的關(guān)系,則稱之為連接查詢。連接查詢是關(guān)系數(shù)據(jù)庫中最主要的查詢,包括：交叉連接查詢內(nèi)連接查詢外連接查詢。連接查詢-交叉連接交叉連接查詢：又稱廣義笛卡兒積，是不帶連接謂詞的查詢。【例6.1】求學(xué)生表student和課程表course的廣義笛卡兒積。SELECT*FROMstudent,course;--或SELECT*FROMstudentCROSSJOINcourse;連接查詢-交叉連接交叉連接查詢的原理：按照表在from后面出現(xiàn)順序，第一個表叫驅(qū)動表，第二個表叫被驅(qū)動表，當(dāng)兩表連接時，執(zhí)行雙重循環(huán)進(jìn)行記錄連接，第一個表的記錄用做外循環(huán)，第二個表的記錄用做內(nèi)循，返回兩條記錄連接的結(jié)果。例：SELECT*FROMstudent,course;for

t

in

student

{

for

s

incourse

{

output

t+s;

}

}

連接查詢-內(nèi)連接內(nèi)連接查詢：內(nèi)連接查詢中用來連接兩個表的條件稱為連接條件或連接謂詞,其一般格式為:<表名1>[INNER]JOIN<表名2>

ON<表名1>.<列名1><比較運(yùn)算符><表名2>.<列名2>其中比較運(yùn)算符主要有:=、<>、<、>、<=、>=。當(dāng)連接運(yùn)算符為“=”時,稱為等值連接,使用其他運(yùn)算符稱為非等值連接。連接謂詞中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的,但不必是相同的。連接查詢-內(nèi)連接等值連接：當(dāng)比較運(yùn)算符為“=”時，稱為等值連接，其一般格式為：<表名1>[INNER]JOIN<表名2>

ON<表名1>.<列名1>=<表名2>.<列名2>連接條件也可以放在WHERE子句中，其格式為:<表名1>.<列名1>=<表名2>.<列名2>連接查詢-內(nèi)連接【例6.2】求學(xué)生以及其選修課程的情況。--格式1：SELECTstudent.*,score.*FROMstudentJOINscoreONstudent.sno=score.sno;--格式2：SELECTstudent.*,score.*FROMstudent,scoreWHEREstudent.sno=score.sno;連接查詢-內(nèi)連接自然連接：當(dāng)進(jìn)行連接的兩個表中有同名列時，可以使用自然連接來完成，其一般格式為：<表名1>NATURALJOIN<表名2>

也可以使用格式:<表名1>JOIN<表名2>USING<字段>注意：自然連接與其他連接的區(qū)別是去掉重復(fù)的列。連接查詢-內(nèi)連接【例6.3】用自然連接完成：求學(xué)生以及其選修課程的情況。--格式2：SELECT*FROMstudentJOINscoreUSING(sno);--格式1：SELECT*FROMstudentNATURALJOINscore;連接查詢-內(nèi)連接自身連接：連接操作不僅可以在兩個表之間進(jìn)行,也可以是一個表與其自身進(jìn)行連接,稱為表的自身連接。當(dāng)表進(jìn)行自身連接時，為了區(qū)別兩個表，必須給他們?nèi)〔煌膭e名,表取別名的方式為：

表名別名

進(jìn)行自身連接時，由于所有屬性名都是同名屬性，因此必須使用別名前綴。其一般格式為：<表名>aJOIN<表名>

bONa.<字段><較比運(yùn)算符>b.<字段>連接查詢-內(nèi)連接【例6.4】求年齡大于王燕的所有學(xué)生的姓名、專業(yè)名和出生日期。SELECTb.sname,b.dept,b.birthdayFROMstudentaJOINstudentbONa.birthday>b.birthdayWHEREa.sname='王燕';連接查詢-外連接在通常的連接操作中,只有滿足連接條件的記錄才能作為結(jié)果輸出，有些用戶需要的信息可能在結(jié)果中就無法出現(xiàn)，可使用外連接解決這個問題，外連接的格式為：<表名1>{LEFT|RIGHT|FULL}OUTERJOIN<表名2>ON

連接條件LEFT表示左邊的表中所有記錄都出現(xiàn)在結(jié)果中；RIGHT表示左邊的表中所有記錄都出現(xiàn)在結(jié)果中；FULL表示左邊的表中所有記錄都出現(xiàn)在結(jié)果中；注意：不符合連接條件的連接空記錄。連接查詢-內(nèi)連接【例6.5】查找未選修任何課程的學(xué)生。SELECT*FROMstudentaLEFTJOINscorebona.sno=b.snoWHEREb.snoisnull;子查詢在SQL中,一個SELECT-FROM-WHERE語句嵌套在另一個查詢中的查詢稱為嵌套查詢（或子查詢），如：SELECT-FROM-WHERE(SELECT-FROM-WHERE)上層的查詢塊A稱為外層查詢或父查詢,下層查詢塊稱B為內(nèi)層查詢或子查詢。SQL語句允許多層嵌套查詢，即一個子查詢還可以嵌套其他子查詢。嵌套查詢時，根據(jù)內(nèi)層查詢條件是否依賴外層查詢，可以將子查詢分為不相關(guān)子查詢和相關(guān)子查詢兩種。不相關(guān)子查詢?nèi)糇硬樵兊牟樵儣l件不依賴于父查詢,則這類查詢稱為不相關(guān)子查詢。內(nèi)層子查詢根據(jù)其返回值的不同可以分為：返回單值的子查詢（標(biāo)量子查詢）返回一組值的子查詢（列子查詢、表子查詢）。不相關(guān)子查詢-標(biāo)量子查詢標(biāo)量子查詢?yōu)榉祷氐慕Y(jié)果是一個單值的子查詢，可以使用比較運(yùn)算符（=,>,<,>=,<=,!=）將父查詢和子查詢連接起來?！纠?.6】查找選修離散數(shù)學(xué)的學(xué)生的學(xué)號。SELECTsnoFROMscoreWHEREcno=(SELECTcnoFROMcourseWHEREcname='離散數(shù)學(xué)');【例6.7】求年齡大于王燕的所有學(xué)生的姓名、專業(yè)名和出生日期。SELECTsname,dept,birthdayFROMstudentWHEREbirthday<(SELECTbirthdayFROMstudentWHEREsname='王燕');不相關(guān)子查詢-列子查詢列子查詢當(dāng)子查詢返回的結(jié)果不是一個值而是一列多行的集合時，必須使用多值比較運(yùn)算符。不相關(guān)子查詢-列子查詢（1）<屬性名>[NOT]IN(SELECT子查詢)無[NOT]時,只要屬性值在SELECT子查詢結(jié)果中,條件表達(dá)式的值為真,否則為假;有[NOT]時,則相反?！纠?.8】查找選修離散數(shù)學(xué)的學(xué)生的姓名。SELECTsnameFROMstudentWHEREsnoIN(SELECTsnoFROMscoreWHEREcno=(SELECTcnoFROMcourseWHEREcname='離散數(shù)學(xué)'));不相關(guān)子查詢-列子查詢（2）<屬性名>θ[ANY|ALL](SELECT子查詢)θ表示比較運(yùn)算符（如>、>=、<、<=、=、<>），使用ANY或ALL謂詞時必須同時使用比較運(yùn)算符?！纠?.9】查找比所有通信工程系的學(xué)生年齡都大的學(xué)生。SELECT*FROMstudentWHEREdept<>'通信工程'ANDbirthday<ALL(SELECTbirthdayFROMstudentWHEREdept='通信工程');不相關(guān)子查詢-列子查詢（3）ANY或ALL謂詞與組函數(shù)及IN謂詞的等價轉(zhuǎn)換關(guān)系【例6.10】查找課程號為206的成績不低于課程號為101的最低成績的學(xué)生的學(xué)號。SELECTsno,gradeFROMscoreWHEREcno='206'ANDgrade>=ANY(SELECTgradeFROMscoreWHEREcno='101');不相關(guān)子查詢-列子查詢SELECTsno,gradeFROMscoreWHEREcno='206'ANDgrade>=(SELECTMIN(grade)FROMscoreWHEREcno='101');不相關(guān)子查詢-表子查詢表子查詢返回一個表時（多行多列），表子查詢可以在FROM后面當(dāng)做數(shù)據(jù)源?！纠?.11】求計(jì)算機(jī)專業(yè)總學(xué)分排名前三的學(xué)生信息。SELECT*FROM(SELECT*FROMstudentWHEREdept='計(jì)算機(jī)')AScsORDERBYtotalcreditDESCLIMIT3;用于DDL和DML中的子查詢（1）用子查詢的結(jié)果創(chuàng)建表【例6.12】將計(jì)算機(jī)專業(yè)的所有學(xué)生保存到stu表中（stu表不存在）。CREATETABLEstu

(SELECT*FROMstudentWHEREdept='計(jì)算機(jī)');用于DDL和DML中的子查詢（2）將子查詢的結(jié)果保存到已有表中?！纠?.13】將計(jì)算機(jī)專業(yè)的所有學(xué)生保存到stu表中（stu表已存在）。INSERTINTOstu

(SELECT*FROMstudentWHEREdept='計(jì)算機(jī)');用于DDL和DML中的子查詢（3）用子查詢的結(jié)果更新表?！纠?.14】將stu表中的所有學(xué)生的總學(xué)生更新為student表中的平均學(xué)分。UPDATEstuSETtotalcredit=(SELECTAVG(totalcredit)FROMstudent);用于DDL和DML中的子查詢（4）用子查詢的結(jié)果刪除表。例6.15】從stu表中刪除和student表中“王”姓同學(xué)相同學(xué)號的學(xué)生。DELETEFROMstuWHEREsnoIN(SELECTsnoFROMstudentWHEREsnameLIKE'王%');相關(guān)子查詢相關(guān)子查詢是指子查詢的查詢條件依賴于其父查詢的某屬性值。相關(guān)子查詢一般使用EXISTS關(guān)鍵字，EXISTS查詢條件表達(dá)式格式:[NOT]EXISTS(SELECT子查詢)EXISTS代表存在量詞,子查詢不返回任何數(shù)據(jù),子查詢查到記錄,條件表達(dá)式值為真,否則為假。子查詢中<目標(biāo)列表達(dá)式>一般都用“*”號。求執(zhí)行相關(guān)子查詢的過程為:從外查詢的關(guān)系中依次取一個記錄,根據(jù)它的值在內(nèi)查詢進(jìn)行檢查,若WHERE子句為真,將此記錄放入結(jié)果表(為假,則舍去)。這樣反復(fù)處理,直至外查詢關(guān)系的記錄全部處理完為止。相關(guān)子查詢【例6.16】查詢選修了102號課程的學(xué)生姓名。SELECTsname

FROMstudentWHEREEXISTS(SELECT*FROMscoreWHEREsno=student.snoANDcno='102');

用NOTEXISTS來實(shí)現(xiàn)除運(yùn)算∏SNAME(XS∞(∏SNO,CNO(CJ)÷∏CNO(KC)))【例6.17】查詢選修了全部課程的學(xué)生姓名。相關(guān)子查詢注意：選修了全部課程的學(xué)生可以變成——沒有這樣的一門課程，該學(xué)生沒有選修。相關(guān)子查詢【例6.17】查詢選修了全部課程的學(xué)生姓名。SELECTsnameFROMstudentWHERENOTEXISTS(SELECTcno

FROMcourse WHERENOTEXISTS

(SELECT*FROMscore WHEREsno=student.snoANDcno=o));沒有這樣的課程該學(xué)生沒有選修過相關(guān)子查詢【例6.17】查詢選修了全部課程的學(xué)生姓名。SELECTsnameFROMstudentWHEREsno

IN( SELECTsno FROMscore GROUPBYsno HAVINGCOUNT(sno)= (SELECTCOUNT(*)FROMcourse));相關(guān)子查詢思考題:求選修了'林一帆'同學(xué)選修的全部課程的學(xué)生的姓名。SELECTsnameFROMstudentaWHEREa.sname<>'林一帆'ANDNOTEXISTS(SELECTcno

FROM(SELECTcnoFROMstudentNATURALJOINscoreWHEREstudent.sname='林一帆')bWHERENOTEXISTS(SELECT*FROMscoreWHERE

sno=a.snoANDcno=o));傳統(tǒng)集合運(yùn)算傳統(tǒng)的集合運(yùn)算有并（UNION）、交（INTERSECT）、差（MINUS）三種，Oracle支持三種類型的集合運(yùn)算符，MySQL只支持并運(yùn)算。并運(yùn)算是將兩個SELECT語句各自得到的結(jié)果集并為一個集?；菊Z法形式：

<子查詢>

Union[ALL]<子查詢>