數(shù)據(jù)庫概論 SQL-數(shù)據(jù)查詢

第2章SQL數(shù)據(jù)查詢SQL查詢功能匯覽查詢基本結(jié)構(gòu)select子句from子句where子句重復(fù)行的處理更名運算行顯示順序字符串操作全文檢索關(guān)系的連接分組和聚集函數(shù)空值嵌套子查詢集合操作查詢基本結(jié)構(gòu)

錙銖必較：SQL返回結(jié)果是多集select R.Afrom Rwhere R.B<10select R.Afrom R,Swhere R.B<10VSPKKORAB1213SCD3413selectR1.*fromRR1,RR2,RR3,RR4,RR5快速生成測試數(shù)據(jù)select子句中的目標列形式可以為列名，*，算術(shù)表達式，聚集函數(shù)“*”：表示“所有的屬性”

給出所有學(xué)生的所有信息

select*

from S帶，，，的算術(shù)表達式 給出所有學(xué)生的姓名及出生日期

selectSNAME，2016-AGE

from S*對性能的影響將多個列組合為一個目標列給出每個老師信息的自然語言描述 select pname+’老師的工資是’+salary+ ’,年齡是’+age+’,職稱是’+title from professor

輸出行的形式類似：“李明老師的工資是1500,年齡是45,職稱是教授”from子句from子句列出查詢的對象表示例：找出選修課程的學(xué)生姓名、課程名、成績select SNAME,CNAME,GRADEfrom S,C,SCwhereS.S#=SC.S# and C.C#=SC.C#亂花漸欲迷人眼寫出與等價的SQL當目標列取自多個表時，需要顯式指明來自哪個關(guān)系select *from R,SwhereR.B=S.Bselect A,B,Cfrom R,SwhereR.B=S.Bselect A,R.B,Cfrom R,SwhereR.B=S.Bwhere子句比較運算符：、、、、=、邏輯運算符：and，or，not等價表達功能與效能between子句判斷表達式的值是否在某范圍內(nèi)示例：列出工資在500~800之間的老師姓名

selectPNAME

fromPROF

whereSALbetween500and800 Abetweena1anda2 A>=a1andA<=a2?not(Abetweena1anda2)

A<a1orA>a2優(yōu)化小竅門：用between合并兩個比較謂詞重復(fù)行的處理SQL缺省為保留重復(fù)行，也可用關(guān)鍵字all顯式指明。若要去掉重復(fù)行，可用關(guān)鍵字distinct指明示例：找出所有選修課程的學(xué)生

select

distinctS# fromSC

優(yōu)化小竅門：只在必要時使用distinct重復(fù)行的處理查詢一：查詢二：查詢?nèi)簊elect

distinctR.A,S.Afrom

R,Swhere R.B=S.Cselect

distinctR.Afrom

R,Swhere R.B=S.Cselect

distinctR.Afrom

R,Swhere R.B=S.A兩個表R(A,B),S(A,C)，其中A是這兩個表的主碼,哪些查詢中的distinct可以去掉？輸出顯示順序命令orderby

列名[asc|desc]示例：按年齡升序列出學(xué)生信息，相同年齡學(xué)生按姓名降序排列

select*

fromS

orderbyAGEasc，SNAMEdesc輸出顯示順序示例：對教工按繳納所得稅的多少排序select

pname,sal*0.2from

proforderby 2示例：按年齡順序輸出學(xué)生姓名select snamefrom studentorderby age更名運算old_nameasnew_name

為關(guān)系和屬性重新命名，可出現(xiàn)在select和from子句中 注：as可選屬性更名示例：給出所有學(xué)生的姓名、性別、出生日期，并按出生日期升序排列

select SNAME‘姓名’,SEX‘性別’, 2016-AGE‘出生日期’

from S

orderby

出生日期（或者orderby 3)關(guān)系更名示例：找出比s1學(xué)生選修c1課程成績高的學(xué)生號

select S2.S#

from SCasS1，SCasS2

where S1.S#=‘s1’

and S1.C#=‘c1’ and S2.C#=‘c1’

and S1.GRADE<S2.GRADE關(guān)系更名示例：找出工資比所在系主任工資高的老師姓名及工資

PROFP#D#SALP1D1800P2D1700DEPTD#DEAND1P2select P1.PNAME，P1.SALfrom PROFP1，PROFP2，DEPTwhere P1.D#=DEPT.D#

and DEPT.DEAN=P2.P#

and P1.SAL>P2.SAL

字符串操作列名[not]like

‘字符串’

匹配規(guī)則‘%’

：匹配零個或多個字符‘＿’：匹配任意單個字符[]：任何在指定范圍內(nèi)的字符[a-f]，[abcdef][^]：任何不在指定范圍內(nèi)的字符[^a-f]，[^abcdef]Like中的轉(zhuǎn)義字符用escape定義轉(zhuǎn)義字符，以去掉特殊字符的特定含義，使其被作為普通字符看待如escape‘\‘，定義\作為轉(zhuǎn)義字符，則可用\%去匹配%，用\＿去匹配＿‘%a_bx’select*fromttwherec1like‘x%%xx'escape‘x'思考：用什么去匹配\？字符串操作示例列出姓名以“張”打頭的教師的所有信息

select*

fromPROF

wherePNAMElike‘張%‘列出名稱中含有3個以上字符，且倒數(shù)第三個是d，倒數(shù)第二個是_的課程

select * from C whereCNAMELIKE‘%_d\__‘escape‘\‘捉衿見肘的like

在CNAME上建有索引CNAME_idx，觀察下面查詢的執(zhí)行計劃，看誰用到了該索引。A. select*fromCwhereCNAMElike'%d'B. select*fromCwhereCNAMElike'd%'小巫見大巫的Like全文索引正則表達式正則表達式（RegularExpression）記錄文本規(guī)則的代碼；用來描述或者匹配一系列符合某個句法規(guī)則的字符串的單個字符串簡單示例zo*匹配“z”和“zoo”zo+與“zo”和“zoo”匹配，但與“z”不匹配o{2}與“fol”,”foool”不匹配，但與“fool”匹配o{2,}不匹配”fol”，匹配”fool”,”foooooool”z|food匹配”z”或”food”(z|f)ood匹配“zood”或”food”常見正則表達式用戶名：/^[a-z0-9_-]{3,16}$/密碼：/^[a-z0-9_-]{6,18}$/電子郵箱：/^([a-z0-9_\.-]+)@([\da-z\.-]+)\.([a-z\.]{2,6})$/IP地址：/^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$/HTML標簽：/^<([a-z]+)([^<]+)*(?:>(.*)<\/\1>|\s+\/>)$/用check約束表達正則表達式createtableIP_Address(ipchar(15)primarykey)altertableIP_AddressaddconstraintCHK_IP_Validcheck( iplike'_%._%._%._%‘ andipnotlike'%.%.%.%.%' andipnotlike'%[^0-9.]%' andipnotlike'%[0-9][0-9][0-9][0-9]%' andipnotlike'%[3-9][0-9][0-9]%' andipnotlike'%2[6-9][0-9]%' andipnotlike'%25[6-9]%')正則表達式：Oracle函數(shù)名說明REGEXP_LIKE類似于LIKE運算符，但執(zhí)行正則表達式匹配而不是簡單的模式匹配REGEXP_INSTR在給定字符串中搜索某個正則表達式模式，并返回匹配項的位置REGEXP_REPLACE搜索某個正則表達式模式并使用替換字符串替換它REGEXP_SUBSTR在給定字符串中搜索某個正則表達式模式并返回匹配的子字符串家族的第一人在樹上被吊死，最后一人則被螞蟻吃掉正則表達式：OracleALTERTABLEstudents

ADDCONSTRAINTstud_ssn_ck

CHECK(REGEXP_LIKE(ssn,

'^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'))標準SQL：similarto全文檢索dialogueAreyoukidding?No,I’mserious.你是凱丁嗎？不，我是史瑞斯。YoucanyouupNocanNoBBcreateindexidx1onfilm(dialogus)select*fromfilmwheredialoguelike‘%serious%’全文檢索正文倒排索引搜索引擎的挑戰(zhàn)：數(shù)量、分類、重復(fù)PageRankstepABCDEFGH11/21/161/161/161/161/161/161/825/161/41/41/321/321/321/321/16HITS：樞紐-權(quán)威算法全文檢索：創(chuàng)建 createfulltextcatalogcatalog_name createfulltextindexon

table_name[(column_name] keyindexindex_name

oncatalog_name keyindex指定table_name上唯一鍵索引的名稱，最好是聚簇索引全文檢索：查詢Contains(屬性列|*，查找條件)freetext(屬性列|*，查找文本)使用freetext時，全文查詢引擎內(nèi)部將查找文本拆分為若干個搜索詞，并賦予每個詞以不同的加權(quán)，然后查找匹配創(chuàng)建全文檢索的示例 doc(doc_id,title,author,abstract,content) createuniqueclusteredindexdoc_idx ondoc(doc_id) createfulltextcatalogdoc_fulltext_catalog createfulltextindexon

doc(title,author,abstract,content)

keyindexdoc_idx ondoc_fulltext_catalog全文檢索的查詢示例select *from docwhere

contains(author,'JimGrayandJeffUllman')select *from docwhere

contains(*,'databaseandnotdataspace')全文檢索的創(chuàng)建及使用演示select *from docwhere

contains((title,abstract),'graphmining')select *from docwhere

freetext(content, 'AdaptiveQueryProcessing')數(shù)據(jù)庫查詢和信息檢索的區(qū)別關(guān)系的連接類型連接成分 包括兩個輸入關(guān)系、連接條件、連接類型連接條件 決定兩個關(guān)系中哪些元組相互匹配，以及連接結(jié)果中出現(xiàn)哪些屬性連接類型 決定如何處理與連接條件不匹配的元組關(guān)系的連接類型連接類型連接條件innerjoinleftouterjoinrightouterjoinfullouterjoinon<謂詞>(R

crossjoin

S)asT兩個關(guān)系的笛卡兒積關(guān)系的連接類型列出老師的教工號、姓名、工資、所教課程號selectP#，PNAME，SAL，C#from(PROFleftouterjoinPConPROF.P#=PC.P#)select P#，PNAME，SAL，C#from PROF,PCwhere PROF.P#=PC.P#unionselect P#，PNAME，SAL，nullfrom PROFwhere P#notin(selectP#fromPC)空值的定義Nocathas12tailsAcathasonemoretailthannocatTherefore,acathas13tailsC.JDate：null是標識，不是值。包含null違反了關(guān)系定義Codd提出了兩類nullA-Marknull：未知的，墨鏡人眼睛的顏色T-Marknull：不適用的，汽車眼睛的顏色俄底修斯：我叫nobodyselect S.SNO,P.PNOfrom S,Pwhere S.CITY<>P.CITY or P.CITY<>’Paris’SNOCITYS1LondonPNOCITYP1GetSNO-PNOpairswhereeitherthesupplierandpartcitiesaredifferentorthepartcityisn’tParis(orboth)DisjunctivedatabaseQ(Sno,Pno)←Suppliers(Sno,City1),Parts(Pno,City2),City1City2Q(Sno,Pno)←Suppliers(Sno,City1),Parts(Pno,City2),City2Paris空值的邏輯計算ANDTRUEFALSEUNKNOWNTRUETRUEFALSEUNKNOWNFALSEFALSEFALSEFALSEUNKNOWNUNKNOWNFALSEUNKNOWNORTRUEFALSEUNKNOWNTRUETRUETRUETRUEFALSETRUEFALSEUNKNOWNUNKNOWNTRUEUNKNOWNUNKNOWN空值測試is

[not]null測試指定列的值是否為空值注意事項除is[not]null之外，空值不滿足任何查找條件如果null參與算術(shù)運算，則該算術(shù)表達式的值為null如果null參與比較運算，則結(jié)果可視為unknown表中存在兩行(1,2,null),(1,2,null)，selectdistinct*?空值測試示例示例：找出成績值為空的學(xué)生號

selectS#

fromSC

whereGRADEisnull不可寫為whereGRADE=null

空值處理函數(shù)isnull(check_expression,replacement_value)

如果check_expression值為空,則返回replacement_value,否則返回check_expressionselect

S#,C#,isnull(GRADE,0

)from

SC空值處理函數(shù)coalesce(expression1,expression2,…)，返回第一個不為null的expressionselect s#,c#,coalesce(grade,0)from scnullif(expression1,expression2)

如果兩個表達式相等，則返回空值，否則返回第一個表達式 select nullif(1,2),nullif(1,1)空值的排序處理缺省情況下空值是最后輸出的。當指定orderby時，降序情況下首先輸出空值，升序情況下最后輸出空值示例：首先由小到大輸出非空sal，然后是空值salselect pname,salfrom

proforderby 2首先輸出空值sal，然后由大到小輸出非空salselect pname,salfrom

proforderby 2desc空值的排序處理首先輸出空值sal，然后由小到大輸出非空salselectpname,salfrom(select pname,sal, casewhensalisnull then0else1asis_null

from

prof)temp_facultyorderby is_null,salpnamesalis_nullbob10001tomnull0cat12001聚集函數(shù)將一列中所有的值聚集為單個值平均值：avg最小值：min最大值：max總和：sum記數(shù)：count火眼金睛之一select S#from SCwhere GRADE=max(GRADE)select S#from SCwhere GRADE= (selectmax(GRADE) fromSC)火眼金睛之一count（*）VScount（列名）S#C#Gs1c180s1c290s1c395s2c185s2c2nulls3c2null4selectcount(G)fromSC6selectcount(*)fromSC分組groupby

列名[having

條件表達式]groupby將表中的元組按指定列上值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值

having則對分組進行選擇，只將聚集函數(shù)作用到滿足條件的分組上分組S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388{{{S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388列出每個學(xué)生的平均成績列出每門課程的平均成績groupbyS#groupbyC#928590929290火眼金睛之二R(A,B,C)selectA fromR groupbyBselectA,B fromR groupbyAselectA,C fromR groupbyA,BselectA fromR groupbyA,CselectA fromR groupbyAselect* fromR groupbyA,B綱舉目張：目標列必須是分組屬性AB1213AB12，3groupbyA分組的查詢示例示例：列出每個學(xué)生的最高、最低、平均成績

select S#， max(GRADE)，

min(GRADE), avg(GRADE)

from SC groupby S#白馬非馬select S#，avg(GRADE)

from SC groupby S#

having

min(GRADE)>=60

select S#，avg(GRADE)

from SC

where GRADE>=60 groupby S#物莫非指而指非指天下無指物不可謂天下無物誰徑謂指分組查詢中各子句的順序列出每一年齡組中男學(xué)生（超過50人）的人數(shù)

select AGE，count(S#) from S where SEX=‘M’ groupby AGE having count(*)>50從groupby到cube所有可能的分析需求每種車型：Groupbymodel每個年份：Groupbyyear每種顏色：Groupbycolor每個年份、每種車型：Groupbymodel,year每個年份、每種顏色：Groupbycolor,year每種顏色、每種車型：Groupbymodel,colorn個屬性的所有g(shù)roupby共有2n個cubeCUBEcubeselect Model,Year,Color,sum(Sales)from car_salesgroupby Model,Year,Colorwithcube總行數(shù)=(model個數(shù)+1)*(year個數(shù)+1)*(color個數(shù)+1)=(2+1)*(3+1)*(3+1)=48

cubeselect ‘TotleSold‘=sum(sales),

case when(grouping(model)=1)then‘ALL’ elseisnull(model,’????’) endmodel, case when(grouping(year)=1)then‘ALL’ elseisnull(year,’????’) endyear, case when(grouping(color)=1)then‘ALL’ elseisnull(color,’????’) endcolorfrom my_cubegroupby model,theyear,colorwithcubegrouping是一個聚合函數(shù),它產(chǎn)生一個附加的列，當用cube或rollup運算符添加行時，附加的列輸出值為1，否則為0rollupgroupby Model,Year,ColorwithrollupALLChevyFord199019911992199019911992RedBlueWhite分組屬性集select model,year,nullascolor,sum(sales)from

car_salesgroupby model,yearunionallselect model,nullasyear,color,sum(sales)from

car_salesgroupby model,colorunionallselect nullasmodel,year,color,sum(sales)from

car_salesgroupby year,colorunionallselect nullasmodel,nullasyear,nullascolor,sum(sales)from

car_sales繁瑣低效分組屬性集groupbygroupingsets((分組屬性集1),(分組屬性集2),…….(分組屬性集n))select model,year,color,sum(sales)from car_salesgroupby groupingsets( (model,theyear), (model,color), (theyear,color), ())分組屬性集借助grouping_id()函數(shù)，可以標示每一行到底和哪個groupby相關(guān)聯(lián)，這是通過為不同的分組分配不同的整數(shù)來做到的例如grouping_id(A,B,C,D)分組(A,B,C,D)的標識為8*0+4*0+2*0+1*0＝0分組(A,B,C)的標識為8*0+4*0+2*0+1*1＝1分組(A,B)的標識為8*0+4*0+2*1+1*1＝3分組(A,C)的標識為8*0+4*1+2*0+1*1＝5分組(C)的標識為8*1+4*1+2*0+1*1＝13分組屬性集cube(a,b)=groupingsets((a,b),(a),(b),())rollup(x,y,z)=groupingsets((x,y,z),(x,y),(x),())groupbycube(a,b),rollup(x,y,z)=groupby groupingsets((a,b),(a),(b),()), groupingsets((x,y,z),(x,y),(x),())=groupbygroupingsets( (a,b,x,y,z),(a,b,x,y),(a,b,x),(a,b), (a,x,y,z),(a,x,y),(a,x),(a), (b,x,y,z),(b,x,y),(b,x),(b), (x,y,z),(x,y),(x),())嵌套子查詢集合成員資格（in子查詢）集合之間的比較（some/all子查詢）集合基數(shù)的測試（exists子查詢）測試集合是否為空in子查詢表達式[not]in

（子查詢）判斷表達式的值是否在子查詢的結(jié)果中示例：列出張軍和王紅同學(xué)的所有信息

select*

fromS

whereSNAMEin

(‘張軍’,’王紅’)in子查詢示例：選修了c1號課程的學(xué)生的姓名selectSNAMEfromS,SCwhereS.S#=SC.S#

andC#=c1)selectSNAMEfromSwhereS#in (selectS#

fromSC

whereC#=c1)問題1：等價否？問題2：誰更有效？in子查詢示例：列出選修了c1號和c2號課程的學(xué)生的學(xué)號

selectS#

fromSC

whereSC.C#=c1

andS#in

(selectS#

fromSC

whereC#=c2)some/all子查詢表達式比較運算符

some

（子查詢）表達式的值至少與子查詢結(jié)果中的一個值相比滿足比較運算符

表達式比較運算符

all

（子查詢）表達式的值與子查詢結(jié)果中的所有的值相比都滿足比較運算符some/all子查詢056(5<some)=true05(5

some)=true05)=false(5<some05)=true(5=some056(5<all)=false46(5

all)=true610)=true(5<all45)=false(5=all(

all)notin(=all)in(=some)in(some)notinsome/all子查詢找出平均成績最高的學(xué)生號

select S#

from SC

groupby S#

having

avg(GRADE)>=all

(select

avg(GRADE)

from SC groupby S#)some/all子查詢select D#,X.S#from SX,SCwhere X.S#=SC.S#groupby D#,X.S#having

avg(GRADE)>=all

(select

avg(GRADE)

from S,SC

where S.S#=SC.S#

and

S.D#=X.D# groupby S.S#)找出每個系平均成績最高的學(xué)生exists子查詢[not]exists

（子查詢）判斷子查詢的結(jié)果集合中是否有任何元組存在in后的子查詢與外層查詢無關(guān)，每個子查詢執(zhí)行一次，而exists后的子查詢與外層查詢有關(guān)，需要執(zhí)行多次，稱之為相關(guān)子查詢exists子查詢示例：列出選修了c1號課程的學(xué)生姓名

selectSNAME

from

S

whereexists

(select *

from SC

where C#=c1

and S#=S.S#)S#SNAMES1AS2BS3CS#C#S1c1S2c2S3c1(s1,c1)(s2,c1)(s3,c1)exists子查詢列出選修了c1號和c2號課程的學(xué)生的學(xué)號

select S#

from SCSC1

where SC1.C#=c1 and

exists

(select S#

from SC

where C#=c2

and S#=SC1.S#)反半連接：notin,notexists列出沒有選修c1號課程的學(xué)生的姓名

何時notexists優(yōu)于notin?selectSNAMEfromSwhereS#notin (selectS#

fromSC

whereC#=c1)selectSNAMEfromSwhere

notexists (selectS#

fromSC

whereS#=S.S#

andC#=c1)反半連接：notin,notexistsselectSNAMEfromSwhereS#in ((selectS#

fromS)

except (selectS# fromSC))selectSNAMEfrom(SleftouterjoinSC onS.S#=SC.S#)whereC#isnull子查詢中的屬性解析匹配selectsnofromMySwheresnonotin(selectsnofromSCwhereC#=‘c01’)selectsnofromMySwheresnonotin(selectMyS.snofromSCwhereC#=‘c01’)S#C#s1c1s2c2snos1s2SCMyS子查詢與nullselect *from t1where notexists (select*fromt2wherea=b)a12bnullt1t2select *from t1where anotin(select*fromt2)返回1,2返回空集北京無處不飛沙除法在SQL中的表達Youcannotbetoocareful三年來，我獨自一人，無時無刻思念著你NegationPlusNegationMeansAffirmationNothingisnothingatallThereisnosuccesswithouthardships否定之否定vs

雙重否定Youcannotmakeeggrollswithoutb