查詢語句連接,嵌套課件

第二章SQLSQL概述SQL數(shù)據(jù)定義功能SQL數(shù)據(jù)查詢功能SQL數(shù)據(jù)修改功能SQL數(shù)據(jù)控制功能嵌入式SQL連接查詢-等值與非等值查詢

若一個(gè)查詢涉及兩個(gè)以上的表，則稱為連接查詢。連接查詢包括等值連接、自然連接、非等值連接、自身連接、外連接查詢和符合條件連接查詢。一、等值與非等值查詢連接查詢中用來連接兩個(gè)表的條件稱為連接條件或連接謂詞，其一般格式為：[<表名1>.]<列名><比較運(yùn)算符>[<表名2.>]<列名2>當(dāng)連接運(yùn)算符為＝時(shí)，稱為等值連接。使用其他運(yùn)算符稱為非等值連接。連接謂詞中的列名稱為連接字段例32查詢每個(gè)學(xué)生及其選修課程的情況學(xué)生情況存于STUDENT表中，選修課程的情況存于SC表中，這兩個(gè)表的聯(lián)系通過公共屬性SNO實(shí)現(xiàn)的。連接查詢-等值與非等值查詢

SELECTSTUDENT.*,SC.*FROMSTUDENT,SCWHERESTUDENT.SNO=SC.SNO

如果屬性名在參加連接的各表中是唯一的，則可以省略表名前綴查詢每個(gè)學(xué)生的選課情況，要求顯示學(xué)生的學(xué)號(hào)，姓名，課程編號(hào)；查詢每個(gè)學(xué)生的選課情況，要求顯示學(xué)生的學(xué)號(hào)，姓名，課程名稱，課程學(xué)分，成績；統(tǒng)計(jì)每個(gè)學(xué)生的選課數(shù)目，要求顯示學(xué)生的姓名，選課數(shù)目統(tǒng)計(jì)每門課程的選課人數(shù)，要求顯示課程的名稱，選課人數(shù)，結(jié)果按照人數(shù)的升序顯示連接查詢-自身連接A表李勇男18CS劉晨男20MA王敏女21CS張瑤涵女17IS劉晨女19IS李勇男18CS劉晨男20MA王敏女21CS張瑤涵女17IS劉晨女19IS關(guān)于自身連接的思考（1）例：查詢至少選修了兩門課的學(xué)生學(xué)號(hào)觀察下面代碼的運(yùn)行結(jié)果

selectdistincta.snofromsca,scbwherea.sno=o!=oselecta.snofromsca,scbwherea.sno=o!=o關(guān)于自身連接的思考（2）例：既選修了C01又選修C02課程的學(xué)生學(xué)號(hào)（用自身連接）既選修了C01又選修C02課程的學(xué)生學(xué)號(hào),姓名（用自身連接）selecta.snofromsca,scbwherea.sno=o='c01'o='c02'selecta.sno,student.snamefromsca,scb,studentwherea.sno=o='c01'o='c02'anda.sno=student.sno連接查詢-復(fù)合條件連接連接操作除了可以是兩表連接，自身連接，還可以是兩張表以上的連接，后者稱為多表連接例37查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績例38查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名

selectstudent.sno,sname,cname,scorefromstudent,sc,coursewherestudent.sno=o=oselectstudent.sno,snamefromstudent,sc,coursewherestudent.sno=o=ame='信息系統(tǒng)'連接查詢-外連接

觀察如下語句運(yùn)行結(jié)果的不同：selectstudent.sno,cno,scorefromstudent,scwherestudent.sno=sc.snoselectstudent.sno,cno,scorefromstudent,scwherestudent.sno*=sc.sno第二個(gè)語句稱為外連接，表示方法為：在連接謂詞的某一邊加符號(hào)*。*所在邊的表中的記錄全部顯示連接查詢-外連接觀察如下代碼的運(yùn)行結(jié)果：selectsc.sno,cname,scorefromsc,o=oselectsc.sno,cname,scorefromsc,o=*o關(guān)于連接查詢的思考（2）在SPJ數(shù)據(jù)庫中：統(tǒng)計(jì)天津地區(qū)的項(xiàng)目使用零件的種數(shù)(超過3種)和零件總數(shù)量。要求查詢結(jié)果按零件的種數(shù)升序排列，種數(shù)相同時(shí)按總數(shù)量降序排列。SELECTSPJ.JNO,COUNT(DISTINCTSPJ.PNO)ASCOUNT_PNO,SUM(QTY)ASSUM_QTYFROMJ,SPJWHEREJ.CITY=’天津’ANDJ.JNO=SPJ.JNOGROUPBYSPJ.JNOHAVINGCOUNT(DISTINCTPNO)>3ORDERBYCOUNT_PNO,SUM_QTYDESC;期中考中的部分題目查詢名稱中含有4個(gè)字符以上，且倒數(shù)第3個(gè)字符是d，倒數(shù)第2個(gè)字符是_的課程的所有信息查詢每門課都及格的學(xué)生的學(xué)號(hào)，平均成績

select*fromcoursewherecnamelike'%_d\__'escape'\'selectsno,avg(score)fromscwherescore>=60groupbysnoselectsno,avg(score)fromscgroupbysnohavingmin(score)>=60嵌套查詢集合成員資格selectSnamefromStudentwheresnoin(selectsnofromscwherecno=‘c01’)集合之間的比較集合基數(shù)的測(cè)試測(cè)試集合是否為空測(cè)試集合是否存在重復(fù)元組

在SQL語言中，一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊，將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢集合成員資格（Ⅱ）查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。

1、確定劉晨所在的系名

selectSdeptfromStudentwhereSname='劉晨'2、查找所有在IS系學(xué)習(xí)的學(xué)生selectSno,Sname,Sdeptfromstudentwheresdept='is'Sdept----isSnoSnameSdept------------------------------------2003002劉晨IS2003004張立IS集合成員資格（Ⅲ）構(gòu)造嵌套后：selectSno,Sname,SdeptfromStudentwhereSdeptIN(selectSdeptfromStudentwhereSname='劉晨')列出選修了C01號(hào)和C02號(hào)課程的學(xué)生的學(xué)號(hào)。selectSNO

fromSC

whereSC.CNO='C01'

andSNOin

（selectSNO

fromSC

whereCNO='C02'）查詢沒有選修任何課程的學(xué)生的學(xué)號(hào)，姓名在選課名單中查詢沒有選修c01號(hào)課程的學(xué)生的學(xué)號(hào)，姓名在選課名單中查詢沒有選修c01，也沒有選修c02課程的學(xué)生學(xué)號(hào)，姓名集合成員資格（IV

）集合之間的比較（Ⅰ）some/any/all子查詢表達(dá)式比較運(yùn)算符

any（子查詢）表達(dá)式的值至少與子查詢結(jié)果中的一個(gè)值相比滿足比較運(yùn)算符。表達(dá)式比較運(yùn)算符

all（子查詢）表達(dá)式的值與子查詢結(jié)果中的所有的值相比都滿足比較運(yùn)算符。集合之間的比較（Ⅱ）>any大于子查詢結(jié)果中的某個(gè)值>all大于子查詢結(jié)果中的所有值<any小于子查詢中的某個(gè)值<all小于子查詢中的所有值=any等于子查詢中的某個(gè)值=all等于子查詢中的任意一個(gè)值!=any不等于子查詢中的某個(gè)值!=all不等于子查詢中的任意一個(gè)值集合之間的比較（Ⅲ）查詢其他系中比信息系某個(gè)學(xué)生年齡小的學(xué)生姓名年齡等價(jià)于：selectSname,SagefromstudentwhereSage<any(selectSagefromstudentwhereSdept='is')andSdept<>'is'selectSname,SagefromstudentwhereSage<(selectmax(Sage)fromstudentwhereSdept='is')andSdept<>'is'集合之間的比較（V

）示例找出平均成績最高的學(xué)生號(hào)。

selectSNO

fromSC

groupbySNO

having

avg(SCORE)>=all

（select

avg(SCORE)

fromSC

groupbySNO）思考：查詢每個(gè)系年齡最小的學(xué)生的學(xué)號(hào)，姓名，年齡，系名集合之間的比較（VI

）事實(shí)上，用集函數(shù)實(shí)現(xiàn)子查詢通常比直接用ANY或者ALL查詢效率要高

＝<>或!=<<=>>=anyin<max<=max>min>=minallnotin<min<=min>max>=max集合基數(shù)的測(cè)試（3）查詢沒有選修c01號(hào)課程的學(xué)生姓名由于帶有exists量詞的相關(guān)子查詢只關(guān)心內(nèi)層查詢是否有返回值，并不需要具體值，因此其效率并不一定低于不相關(guān)子查詢，有時(shí)是高效的方法selectSnamefromstudentwherenotexists(select*fromscwheresno=student.snoandcno='C01')集合基數(shù)的測(cè)試（4）列出選修了001號(hào)和002號(hào)課程的學(xué)生的學(xué)號(hào)。思考：查詢選修了全部課程的學(xué)生姓名

selectSNOfromSCSC1whereSC1.CNO='c01'andexists (selectSNO fromSCSC2 whereSC2.CNO='c02' andSC2.SNO=SC1.SNO)列出至少選修了2003001號(hào)學(xué)生選修的所有課程的學(xué)生名。

selectSNAME

fromStudentasS

where

notexists

（selectCNO

fromCOURSE

where

exists

（select*

fromSC