第8講SQL數(shù)據(jù)查詢

第4章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL——SQL語言數(shù)據(jù)查詢SQL語言概述SQL中的數(shù)據(jù)定義SQL中的數(shù)據(jù)查詢SQL中的數(shù)據(jù)更新SQL中的視圖SQL中的授權(quán)控制本章內(nèi)容數(shù)據(jù)查詢SELECT語句是SQL中的數(shù)據(jù)查詢語句，也是SQL語言中功能最強(qiáng)大的數(shù)據(jù)操縱語句。SELECT語句具有數(shù)據(jù)查詢、統(tǒng)計(jì)、分組和排序的功能，4.3SQL的數(shù)據(jù)查詢一個(gè)完整的數(shù)據(jù)查詢語句的格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]...

FROM<表名或視圖名>[,<表名或視圖名>]...[WHERE<元組選擇條件表達(dá)式>][GROUPBY<列名1>[HAVING<組選擇條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];4.3SQL的數(shù)據(jù)查詢?nèi)粢獙?shí)現(xiàn)關(guān)系代數(shù)中最常見的查詢

∏A1,A2,…,AmσF（R1R2

…Rn

）可用最常用、最基本的SQL查詢語句來實(shí)現(xiàn)：

SELECTA1，A2，…，Am

FROMR1，R2，…，Rn

WHEREF；4.3SQL的數(shù)據(jù)查詢4.3SQL的數(shù)據(jù)查詢【例】“學(xué)生選課”數(shù)據(jù)庫中的關(guān)系模式（表結(jié)構(gòu)）：

學(xué)生（學(xué)號(hào)，姓名，所在系，出生時(shí)間，性別）

課程（課程編號(hào)，課程名，先修課程號(hào)）選課（學(xué)號(hào)，課程編號(hào)，成績）

S（SNO，SN，SD，SB，SEX）

C（CNO，CN，PC）SC（SNO，CNO，GRADE）4.3SQL的數(shù)據(jù)查詢SQL的單表查詢查詢結(jié)果的顯示查詢滿足條件的元組查詢結(jié)果的排序查詢結(jié)果的計(jì)算和統(tǒng)計(jì)查詢結(jié)果的分組SQL的連接、嵌套和集合查詢4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的顯示【例】查詢學(xué)生表S、課程表C、選課表SC

。

SELECT*FROMS；SELECT*FROMC；

SELECT*FROMSC；顯示輸出所有屬性列的信息，且與基表中相同4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的顯示【例】查詢所有女生的姓名和出生時(shí)間。

SELECTSN，SBFROMSWHERESEX=‘女’；【例】查詢選修了課程的學(xué)生的學(xué)號(hào)。

SELECTDISTINCTSNOFROMSC；【例】查詢學(xué)生學(xué)號(hào)和學(xué)生的年齡。

SELECTSNO，SA=datediff(Year，SB，Getdate())+1

FROMS；去掉結(jié)果表中的重復(fù)行4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組運(yùn)算符的優(yōu)先順序（從高到低）：+，-，*，/，%（取余）=，!=，>=，>，<=，<，[NOT]BETWEEN…AND[NOT]IN

[NOT]LIKEIS[NOT]NULLNOT、AND、OR

4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組BETWEEN謂詞用于判斷某個(gè)值是否屬于一個(gè)指定的區(qū)間。一般形式為：

E[NOT]BETWEENE1ANDE2其語義為：

[NOT]（E>=E1ANDE<=E2）E、E1和E2都是表達(dá)式，且E1<E2。【例】查詢出生時(shí)間在1990-01-01到1991-12-30之間的學(xué)生的姓名和出生時(shí)間。SELECTSN，SBFROMSWHERESBBETWEEN‘1990-01-01’AND‘1991-12-31’；

4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組LIKE謂詞用于字符串的比較。一般形式為：

列名[NOT]LIKE字符串表達(dá)式“字符串表達(dá)式”中提供了以下匹配方式：字符“_”：代表任意一個(gè)單字符；字符“%”：代表長度可為零的任意長字符串；所有其他字符只代表自己；若查詢的字符串本身就含有通配符“%”或“_”，需對通配符進(jìn)行轉(zhuǎn)義。

【例】查詢姓“王”的所有學(xué)生的學(xué)號(hào)和姓名。

SELECTSNO，SNFROMSWHERESNLIKE‘王％’;4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組LIKE謂詞用于字符串的比較。一般形式為：

列名[NOT]LIKE字符串表達(dá)式“字符串表達(dá)式”中提供了以下匹配方式：字符“_”：代表任意一個(gè)單字符；字符“%”：代表長度可為零的任意長字符串；所有其他字符只代表自己；若查詢的字符串本身就含有通配符“%”或“_”，需對通配符進(jìn)行轉(zhuǎn)義?！纠坎樵冋n程名后綴為“_Design”課程設(shè)計(jì)的課程信息。SELECT*FROMCWHERECNLIKE‘%\_Design’ESCAPE‘\’；

4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組IN謂詞適用于判斷一個(gè)值是否屬于一個(gè)集合。一般格式為：

E[NOT]IN（V1，V2，…，Vn）其語義為：

[NOT]（E=V1ORE=V2OR…ORE=Vn）IN后面也可以是一個(gè)子查詢塊——嵌套查詢【例】查詢非數(shù)學(xué)系和非計(jì)算機(jī)系的學(xué)生的學(xué)號(hào)、姓名和所在系；

SELECTSNO，SN，SDFROMSWHERESDNOTIN（‘?dāng)?shù)學(xué)’，‘計(jì)算機(jī)’）；4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組

NULL謂詞用于判斷值是否為空。由于NULL不是一個(gè)具體的值，涉及NULL的條件不得使用=、等比較符，而只能用

ISNULL

或ISNOTNULL【例】查詢所有缺少選課成績的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。

SELECTSNO，CNOFROMSCWHEREGRADEISNULL；4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的排序

ORDERBY子句指定按照一個(gè)或多個(gè)屬性列的升序（ASC缺省值）或降序（DESC）重新排列查詢結(jié)果。一般格式：

ORDERBY列1[ASCDESC][，列2[ASCDESC]，…]【例】查詢選修課程號(hào)為“C02”的學(xué)號(hào)和成績，并按成績降序排列。

SELECTSNO，GRADEFROMSCWHERECNO=‘C02’ORDERBYGRADEDESC；4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的計(jì)算和統(tǒng)計(jì)

COUNT(*)統(tǒng)計(jì)元組個(gè)數(shù)COUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中值的個(gè)數(shù)SUM([DISTINCT|ALL]<列名>)計(jì)算一列值的總和（此列必須是數(shù)值型）AVG([DISTINCT|ALL]<列名>)計(jì)算一列值的平均值（此列必須是數(shù)值型）MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值

4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的計(jì)算和統(tǒng)計(jì)

【例】求學(xué)生表S中學(xué)生的總?cè)藬?shù)。

SELECTCOUNT（*）

FROMS；

【例】查詢選修了課程的學(xué)生人數(shù)；

SELECTCOUNT（DISTINCTSNO）

FROMSC；【思考題】計(jì)算選修C01課程的學(xué)生平均成績；

查詢學(xué)習(xí)C01課程的學(xué)生最高分?jǐn)?shù)。4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的分組

通過GROUPBY子句可將查詢結(jié)果按某一列或多列的值分組，值相等的為一組。一般格式為：

GROUPBY列名1[,列名2][,…列名n][HAVING條件]HEVING子句用于對分組進(jìn)行篩選，只有滿足條件的組才會(huì)選出來。對查詢結(jié)果分組的目的是為了細(xì)化聚集函數(shù)的作用對象。分組后聚集函數(shù)將作用于查詢結(jié)果中的每一個(gè)分組，即每一個(gè)分組都有一個(gè)函數(shù)值。SELECT子句所要顯示輸出的值必須在分組中是唯一的，即只能是GROUPBY子句所指明的列或聚集函數(shù)。4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的分組

【例】查詢平均成績在80分以上的學(xué)生學(xué)號(hào)和平均成績

SELECTSNO，AVG(GRADE)FROMSCGROUPBYSNOHAVINGAVG(GRADE)>80SNOCNOGRADEs01s01s01s01s01c01c02c03c04c0780.098.085.078.089.0SNOCNOGRADEs02s02c02c1180.080.04.3SQL的數(shù)據(jù)查詢查詢結(jié)果的分組

【例】查詢選修的課超過三門的學(xué)生的學(xué)號(hào)。

SELECTSNO

FROMSCGROUPBYSNOHAVINGCOUNT（*）＞3；4.3SQL的數(shù)據(jù)查詢查詢結(jié)果的分組

【例】查找男生人數(shù)超過20的系的名稱

SELECTSD

FROMSWHERESEX=‘男’

GROUPBYSD

HAVINGCOUNT（*）＞=20WHERE作用于由FROM指定的數(shù)據(jù)對象（基本表或視圖），從中選擇滿足條件的元組；GROUPBY子句用于對WHERE的結(jié)果進(jìn)行分組；HAVING則是對GROUPBY以后的分組按條件進(jìn)行選擇。4.3SQL的數(shù)據(jù)查詢SQL的連接、嵌套和集合查詢連接查詢二表連接查詢多表連接查詢自身連接查詢連接查詢中的連接條件通過WHERE子句表達(dá)，其中連接條件和元組選擇條件之間用AND（與）操作符銜接。若無WHERE子句，則查詢?yōu)閷Χ啾淼膹V義笛卡爾積進(jìn)行操作。4.3SQL的數(shù)據(jù)查詢二表連接查詢【例】查詢選修課程號(hào)為“C01”的學(xué)生姓名和成績

SELECTS.SN，SC.GRADEFROMS，SCWHERESC.CNO=‘C01’AND

S.SNO=SC.SNO；4.3SQL的數(shù)據(jù)查詢二表連接查詢【例】查詢學(xué)生姓名及選修課程號(hào)為“C01”的成績

SELECTS.SN，SC.GRADEFROMSLEFTOUTERJOINSC

ONSC.CNO=‘C01’ANDS.SNO=SC.SNO；4.3SQL的數(shù)據(jù)查詢二表連接查詢

在SQL語句中，外連接可以在FROM子句中指定，其語法規(guī)則如下：FROM<左關(guān)系>LEFT|RIGHT|FULL[OUTER]JOIN<右關(guān)系>ON<search_condition>

說明：FULL[OUTER]進(jìn)行全外連接，LEFT[OUTER]進(jìn)行左外連接RIGHT[OUTER]進(jìn)行右外連接ON<search_condition>指定連接所基于的條件

4.3SQL的數(shù)據(jù)查詢多表連接查詢【例】查詢選修“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生的姓名和成績。

SELECTS.SN，SC.GRADEFROMS，C，SCWHEREC.CN=‘?dāng)?shù)據(jù)結(jié)構(gòu)’AND

C.CNO=SC.CNOAND

S.SNO=SC.SNO；4.3SQL的數(shù)據(jù)查詢自身連接查詢【例】查詢每門課程的間接先修課程（即先修課程的先修課程）。SELECTFIRST.CNO，SECOND.PC

FROMCFIRST，CSECONDWHEREFIRST.PCISNOTNULLANDSECOND.PCISNOTNULLANDFIRST.PC=SECOND.CNO；4.3SQL的數(shù)據(jù)查詢SQL的連接、嵌套和集合查詢嵌套查詢一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。在子查詢的WHERE子句中還可以嵌入子查詢，只嵌入一層子查詢的稱為單層嵌套查詢，嵌入子查詢多于一層的稱為多層嵌套查詢。4.3SQL的數(shù)據(jù)查詢SQL的連接、嵌套和集合查詢嵌套查詢嵌套語句的執(zhí)行由下層向上層處理，即下層查詢結(jié)果集用于建立上層查詢塊的查找條件。4.3SQL的數(shù)據(jù)查詢SQL的連接、嵌套和集合查詢嵌套查詢使用IN操作符實(shí)現(xiàn)使用比較操作符實(shí)現(xiàn)使用ANY或ALL操作符實(shí)現(xiàn)使用EXISTS操作符實(shí)現(xiàn)

4.3SQL的數(shù)據(jù)查詢使用IN操作符實(shí)現(xiàn)【例】查詢選修“操作系統(tǒng)”課程的學(xué)生的學(xué)號(hào)SELECTSNOFROMSCWHERECNOIN

（SELECTCNOFROMCWHERECN=‘操作系統(tǒng)’）；4.3SQL的數(shù)據(jù)查詢使用IN操作符實(shí)現(xiàn)【例】查詢選修“操作系統(tǒng)”課程的學(xué)生的學(xué)號(hào)和姓名SELECTSNO，SNFROMSWHERESNOIN

（SELECTSNOFROMSCWHERECNOIN

（SELECTCNOFROMCWHERECN=‘操作系統(tǒng)’））；獨(dú)立子查詢：子查詢的結(jié)果只是用于建立其父查詢的查詢條件，不依賴于父查詢。4.3SQL的數(shù)據(jù)查詢使用比較操作符實(shí)現(xiàn)

【例】查詢“c01”課程的選修成績高于王玲的學(xué)生的學(xué)號(hào)和成績SELECTSNO，GRADEFROMSCWHERECNO=‘c01’ANDGRADE

＞

（SELECTGRADEFROMSCWHERECNO=‘c01’ANDSNO=（SELECTSNOFROMSWHERESN=‘王玲’））；4.3SQL的數(shù)據(jù)查詢使用比較操作符實(shí)現(xiàn)

【例】查詢每個(gè)學(xué)生所修課程成績超過其選修課程平均成績的課程號(hào)SELECTSNO，CNOFROMSCSC1WHEREGRADE＞（SELECTAVG(GRADE)FROMSCSC2WHERESC2.SNO=SC1.SNO）；相關(guān)子查詢：不能獨(dú)立得到查詢結(jié)果，子查詢的結(jié)果與父查詢有關(guān)，可能需要針對父查詢進(jìn)行反復(fù)查詢。

4.3SQL的數(shù)據(jù)查詢使用ANY或ALL操作符實(shí)現(xiàn)在有的DBMS（如SQLServer2005）中，使用ANY或ALL操作符與比較符配合使用實(shí)現(xiàn)嵌套查詢。ANY操作符的語義為查詢結(jié)果中的某個(gè)值

ALL操作符的語義為查詢結(jié)果中的所有值。4.3SQL的數(shù)據(jù)查詢使用ANY或ALL操作符實(shí)現(xiàn)【例】查詢其他系中比計(jì)算機(jī)系某一學(xué)生年齡小的學(xué)生（即查詢年齡小于計(jì)算機(jī)系中年齡最大者的學(xué)生）。SELECT*FROMSWHERESD<>‘計(jì)算機(jī)’ANDSB>ANY（SELECTSB

FROMSWHERESD=‘計(jì)算機(jī)’）；4.3SQL的數(shù)據(jù)查詢使用ANY或ALL操作符實(shí)現(xiàn)【例】查詢其他系中比計(jì)算機(jī)系學(xué)生年齡都小的學(xué)生。

SELECT*FROMSWHERESD<>‘計(jì)算機(jī)’ANDSB>ALL（SELECTSB

FROMSWHERESD=‘計(jì)算機(jī)’）；4.3SQL的數(shù)據(jù)查詢使用ANY或ALL操作符實(shí)現(xiàn)此類查詢也可以用聚集函數(shù)來實(shí)現(xiàn)。且效率更高。=ANY等價(jià)于IN謂詞<ANY等價(jià)于<MAX<>ALL等價(jià)于NOTIN謂詞<ALL等價(jià)于<MIN4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)EXISTS謂詞用于判斷一個(gè)子查詢塊的結(jié)果是否存在。一般格式為：

[NOT]EXISTS（子查詢塊）其語義為：若子查詢塊的查詢結(jié)果非空，則EXISTS為真，否則為假。4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)【例】查詢選修了課程號(hào)為“c02”的學(xué)生姓名。SELECTSNFROMSWHEREEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；請思考：該例使用連接查詢、嵌套查詢?nèi)绾伪硎?？子查詢只返回真值或假值，給出列名無實(shí)際意義。4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)

【例】查詢沒有選修‘c02’課程的學(xué)生姓名

SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；請思考：該例可否使用連接查詢、IN嵌套查詢表示？學(xué)號(hào)姓名性別年齡s01王小艷女18s02李明男20s03司馬南男18s04李昕女19s05成功男21學(xué)號(hào)課程號(hào)成績s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的數(shù)據(jù)查詢SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；學(xué)號(hào)姓名性別年齡s01王小艷女18s02李明男20s03司馬南男18s04李昕女19s05成功男21學(xué)號(hào)課程號(hào)成績s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的數(shù)據(jù)查詢SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；學(xué)號(hào)姓名性別年齡s01王小艷女18s02李明男20s03司馬南男18s04李昕女19s05成功男21學(xué)號(hào)課程號(hào)成績s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的數(shù)據(jù)查詢SELECTSNFROMSWHERENOTEXISTS

（SELECT*FROMSCWHERES.SNO=SC.SNOANDCNO=‘c02’）；學(xué)號(hào)姓名性別年齡s01王小艷女18s02李明男20s03司馬南男18s04李昕女19s05成功男21學(xué)號(hào)課程號(hào)成績s01c0198s01c0478s02c0288s01c0298s03c0389SSC4.3SQL的數(shù)據(jù)查詢SELECTSNFROMS，SCWHERES.SNO=SC.SNOANDCNO<>‘c02’；

4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)

結(jié)論：

一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價(jià)替換，但所有帶IN謂詞、比較運(yùn)算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價(jià)替換。4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)【例】查詢選修全部課程的學(xué)生的姓名{t(1)(u)(v)(w)(S(u)∧C(v)∧SC(w)∧u[1]=w[1]∧v[1]=w[2]∧t[1]=u[2])}

（

x）P≡┐(x）(┐P)查詢選修全部課程的學(xué)生的姓名≡沒有一門課他不選

4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)【例】查詢選修全部課程的學(xué)生的姓名SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS

(SELECT*FROMSCWHERESC.SNO=S.SNOANDSC.CNO=C.CNO));4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)【例】查詢所學(xué)課程包含學(xué)生“s03”所學(xué)課程的學(xué)生的姓名

本查詢可以用邏輯蘊(yùn)涵來表達(dá)：查詢學(xué)號(hào)為X的學(xué)生，對所有的課程Y，只要S03學(xué)生選修課程Y，則X也選修了Y。用p表示謂詞“s03學(xué)生選修課程Y”

用q表示謂詞“學(xué)生X選修了課程Y”

則查詢?yōu)椋海▂）p→q{t(1)(u)(S(u)∧(v)(SC(v)∧(v[1]=‘s03’→(w)(SC(w)∧w[1]=u[1]∧w[2]=v[2])))∧t[1]=u[1])}4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)【例】查詢所學(xué)課程包含學(xué)生“s03”所學(xué)課程的學(xué)生的姓名

p→q≡┐p∨q（y）p→q≡┐(y）(┐(p→q)≡┐(y）(┐(┐p∨q)≡┐(y）(p∧┐q)不存在這樣的課程y，學(xué)生s03選修了y，而學(xué)生x沒有選。

4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實(shí)現(xiàn)【例】查詢所學(xué)課程包含學(xué)生“s03”所學(xué)課程的學(xué)生的姓名

SELECTSNFROMSWHERENOTEXISTS(SELECT*