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

第4章關(guān)系數(shù)據(jù)庫標準語言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語言中功能最強大的數(shù)據(jù)操縱語句。SELECT語句具有數(shù)據(jù)查詢、統(tǒng)計、分組和排序的功能，4.3SQL的數(shù)據(jù)查詢一個完整的數(shù)據(jù)查詢語句的格式SELECT[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]...

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

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

…Rn

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

SELECTA1，A2，…，Am

FROMR1，R2，…，Rn

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

學生（學號，姓名，所在系，出生時間，性別）

課程（課程編號，課程名，先修課程號）選課（學號，課程編號，成績）

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

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

。

SELECT*FROMS；SELECT*FROMC；

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

SELECTSN，SBFROMSWHERESEX=‘女’；【例】查詢選修了課程的學生的學號。

SELECTDISTINCTSNOFROMSC；【例】查詢學生學號和學生的年齡。

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

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

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

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

E[NOT]BETWEENE1ANDE2其語義為：

[NOT]（E>=E1ANDE<=E2）E、E1和E2都是表達式，且E1<E2?！纠坎樵兂錾鷷r間在1990-01-01到1991-12-30之間的學生的姓名和出生時間。SELECTSN，SBFROMSWHERESBBETWEEN‘1990-01-01’AND‘1991-12-31’；

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

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

【例】查詢姓“王”的所有學生的學號和姓名。

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

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

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

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

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

SELECTSNO，SN，SDFROMSWHERESDNOTIN（‘數(shù)學’，‘計算機’）；4.3SQL的數(shù)據(jù)查詢查詢滿足條件的元組

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

ISNULL

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

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

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

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

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

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

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

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

SELECTCOUNT（*）

FROMS；

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

SELECTCOUNT（DISTINCTSNO）

FROMSC；【思考題】計算選修C01課程的學生平均成績；

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

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

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

【例】查詢平均成績在80分以上的學生學號和平均成績

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

【例】查詢選修的課超過三門的學生的學號。

SELECTSNO

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

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

SELECTSD

FROMSWHERESEX=‘男’

GROUPBYSD

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

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

S.SNO=SC.SNO；4.3SQL的數(shù)據(jù)查詢二表連接查詢【例】查詢學生姓名及選修課程號為“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]進行全外連接，LEFT[OUTER]進行左外連接RIGHT[OUTER]進行右外連接ON<search_condition>指定連接所基于的條件

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

SELECTS.SN，SC.GRADEFROMS，C，SCWHEREC.CN=‘數(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的連接、嵌套和集合查詢嵌套查詢一個SELECT-FROM-WHERE語句稱為一個查詢塊。將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。在子查詢的WHERE子句中還可以嵌入子查詢，只嵌入一層子查詢的稱為單層嵌套查詢，嵌入子查詢多于一層的稱為多層嵌套查詢。4.3SQL的數(shù)據(jù)查詢SQL的連接、嵌套和集合查詢嵌套查詢嵌套語句的執(zhí)行由下層向上層處理，即下層查詢結(jié)果集用于建立上層查詢塊的查找條件。4.3SQL的數(shù)據(jù)查詢SQL的連接、嵌套和集合查詢嵌套查詢使用IN操作符實現(xiàn)使用比較操作符實現(xiàn)使用ANY或ALL操作符實現(xiàn)使用EXISTS操作符實現(xiàn)

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

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

（SELECTSNOFROMSCWHERECNOIN

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

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

＞

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

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

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

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

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

SELECT*FROMSWHERESD<>‘計算機’ANDSB>ALL（SELECTSB

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

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

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

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

SELECTSNFROMSWHERENOTEXISTS

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

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

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

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

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

結(jié)論：

一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換，但所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換。4.3SQL的數(shù)據(jù)查詢使用EXISTS操作符實現(xiàn)【例】查詢選修全部課程的學生的姓名{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)查詢選修全部課程的學生的姓名≡沒有一門課他不選

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

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

本查詢可以用邏輯蘊涵來表達：查詢學號為X的學生，對所有的課程Y，只要S03學生選修課程Y，則X也選修了Y。用p表示謂詞“s03學生選修課程Y”

用q表示謂詞“學生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操作符實現(xiàn)【例】查詢所學課程包含學生“s03”所學課程的學生的姓名

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

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

SELECTSNFROMSWHERENOTEXISTS(SELECT*