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

第2章SQL數(shù)據(jù)查詢SQL查詢功能匯覽查詢基本結(jié)構(gòu)select子句from子句where子句重復(fù)行的處理更名運(yùn)算行顯示順序字符串操作全文檢索關(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快速生成測(cè)試數(shù)據(jù)select子句中的目標(biāo)列形式可以為列名，*，算術(shù)表達(dá)式，聚集函數(shù)“*”：表示“所有的屬性”

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

select*

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

selectSNAME，2016-AGE

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

輸出行的形式類似：“李明老師的工資是1500,年齡是45,職稱是教授”from子句from子句列出查詢的對(duì)象表示例：找出選修課程的學(xué)生姓名、課程名、成績(jī)select SNAME,CNAME,GRADEfrom S,C,SCwhereS.S#=SC.S# and C.C#=SC.C#亂花漸欲迷人眼寫(xiě)出與等價(jià)的SQL當(dāng)目標(biāo)列取自多個(gè)表時(shí)，需要顯式指明來(lái)自哪個(gè)關(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子句比較運(yùn)算符：、、、、=、邏輯運(yùn)算符：and，or，not等價(jià)表達(dá)功能與效能between子句判斷表達(dá)式的值是否在某范圍內(nèi)示例：列出工資在500~800之間的老師姓名

selectPNAME

fromPROF

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

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

select

distinctS# fromSC

優(yōu)化小竅門(mén)：只在必要時(shí)使用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兩個(gè)表R(A,B),S(A,C)，其中A是這兩個(gè)表的主碼,哪些查詢中的distinct可以去掉？輸出顯示順序命令orderby

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

select*

fromS

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

pname,sal*0.2from

proforderby 2示例：按年齡順序輸出學(xué)生姓名select snamefrom studentorderby age更名運(yùn)算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課程成績(jī)高的學(xué)生號(hào)

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ī)則‘%’

：匹配零個(gè)或多個(gè)字符‘＿’：匹配任意單個(gè)字符[]：任何在指定范圍內(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個(gè)以上字符，且倒數(shù)第三個(gè)是d，倒數(shù)第二個(gè)是_的課程

select * from C whereCNAMELIKE‘%_d\__‘escape‘\‘捉衿見(jiàn)肘的like

在CNAME上建有索引CNAME_idx，觀察下面查詢的執(zhí)行計(jì)劃，看誰(shuí)用到了該索引。A. select*fromCwhereCNAMElike'%d'B. select*fromCwhereCNAMElike'd%'小巫見(jiàn)大巫的Like全文索引正則表達(dá)式正則表達(dá)式（RegularExpression）記錄文本規(guī)則的代碼；用來(lái)描述或者匹配一系列符合某個(gè)句法規(guī)則的字符串的單個(gè)字符串簡(jiǎn)單示例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”常見(jiàn)正則表達(dá)式用戶名：/^[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標(biāo)簽：/^<([a-z]+)([^<]+)*(?:>(.*)<\/\1>|\s+\/>)$/用check約束表達(dá)正則表達(dá)式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]%')正則表達(dá)式：Oracle函數(shù)名說(shuō)明REGEXP_LIKE類似于LIKE運(yùn)算符，但執(zhí)行正則表達(dá)式匹配而不是簡(jiǎn)單的模式匹配REGEXP_INSTR在給定字符串中搜索某個(gè)正則表達(dá)式模式，并返回匹配項(xiàng)的位置REGEXP_REPLACE搜索某個(gè)正則表達(dá)式模式并使用替換字符串替換它REGEXP_SUBSTR在給定字符串中搜索某個(gè)正則表達(dá)式模式并返回匹配的子字符串家族的第一人在樹(shù)上被吊死，最后一人則被螞蟻吃掉正則表達(dá)式：OracleALTERTABLEstudents

ADDCONSTRAINTstud_ssn_ck

CHECK(REGEXP_LIKE(ssn,

'^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'))標(biāo)準(zhǔn)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時(shí)，全文查詢引擎內(nèi)部將查找文本拆分為若干個(gè)搜索詞，并賦予每個(gè)詞以不同的加權(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ù)庫(kù)查詢和信息檢索的區(qū)別關(guān)系的連接類型連接成分 包括兩個(gè)輸入關(guān)系、連接條件、連接類型連接條件 決定兩個(gè)關(guān)系中哪些元組相互匹配，以及連接結(jié)果中出現(xiàn)哪些屬性連接類型 決定如何處理與連接條件不匹配的元組關(guān)系的連接類型連接類型連接條件innerjoinleftouterjoinrightouterjoinfullouterjoinon<謂詞>(R

crossjoin

S)asT兩個(gè)關(guān)系的笛卡兒積關(guān)系的連接類型列出老師的教工號(hào)、姓名、工資、所教課程號(hào)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是標(biāo)識(shí)，不是值。包含null違反了關(guān)系定義Codd提出了兩類nullA-Marknull：未知的，墨鏡人眼睛的顏色T-Marknull：不適用的，汽車(chē)眼睛的顏色俄底修斯：我叫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空值的邏輯計(jì)算ANDTRUEFALSEUNKNOWNTRUETRUEFALSEUNKNOWNFALSEFALSEFALSEFALSEUNKNOWNUNKNOWNFALSEUNKNOWNORTRUEFALSEUNKNOWNTRUETRUETRUETRUEFALSETRUEFALSEUNKNOWNUNKNOWNTRUEUNKNOWNUNKNOWN空值測(cè)試is

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

selectS#

fromSC

whereGRADEisnull不可寫(xiě)為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,…)，返回第一個(gè)不為null的expressionselect s#,c#,coalesce(grade,0)from scnullif(expression1,expression2)

如果兩個(gè)表達(dá)式相等，則返回空值，否則返回第一個(gè)表達(dá)式 select nullif(1,2),nullif(1,1)空值的排序處理缺省情況下空值是最后輸出的。當(dāng)指定orderby時(shí)，降序情況下首先輸出空值，升序情況下最后輸出空值示例：首先由小到大輸出非空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ù)將一列中所有的值聚集為單個(gè)值平均值：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

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

having則對(duì)分組進(jìn)行選擇，只將聚集函數(shù)作用到滿足條件的分組上分組S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388{{{S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388列出每個(gè)學(xué)生的平均成績(jī)列出每門(mén)課程的平均成績(jī)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綱舉目張：目標(biāo)列必須是分組屬性AB1213AB12，3groupbyA分組的查詢示例示例：列出每個(gè)學(xué)生的最高、最低、平均成績(jī)

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#物莫非指而指非指天下無(wú)指物不可謂天下無(wú)物誰(shuí)徑謂指分組查詢中各子句的順序列出每一年齡組中男學(xué)生（超過(guò)50人）的人數(shù)

select AGE，count(S#) from S where SEX=‘M’ groupby AGE having count(*)>50從groupby到cube所有可能的分析需求每種車(chē)型：Groupbymodel每個(gè)年份：Groupbyyear每種顏色：Groupbycolor每個(gè)年份、每種車(chē)型：Groupbymodel,year每個(gè)年份、每種顏色：Groupbycolor,year每種顏色、每種車(chē)型：Groupbymodel,colorn個(gè)屬性的所有g(shù)roupby共有2n個(gè)cubeCUBEcubeselect Model,Year,Color,sum(Sales)from car_salesgroupby Model,Year,Colorwithcube總行數(shù)=(model個(gè)數(shù)+1)*(year個(gè)數(shù)+1)*(color個(gè)數(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是一個(gè)聚合函數(shù),它產(chǎn)生一個(gè)附加的列，當(dāng)用cube或rollup運(yùn)算符添加行時(shí)，附加的列輸出值為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ù)，可以標(biāo)示每一行到底和哪個(gè)groupby相關(guān)聯(lián)，這是通過(guò)為不同的分組分配不同的整數(shù)來(lái)做到的例如grouping_id(A,B,C,D)分組(A,B,C,D)的標(biāo)識(shí)為8*0+4*0+2*0+1*0＝0分組(A,B,C)的標(biāo)識(shí)為8*0+4*0+2*0+1*1＝1分組(A,B)的標(biāo)識(shí)為8*0+4*0+2*1+1*1＝3分組(A,C)的標(biāo)識(shí)為8*0+4*1+2*0+1*1＝5分組(C)的標(biāo)識(shí)為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ù)的測(cè)試（exists子查詢）測(cè)試集合是否為空in子查詢表達(dá)式[not]in

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

select*

fromS

whereSNAMEin

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

andC#=c1)selectSNAMEfromSwhereS#in (selectS#

fromSC

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

selectS#

fromSC

whereSC.C#=c1

andS#in

(selectS#

fromSC

whereC#=c2)some/all子查詢表達(dá)式比較運(yùn)算符

some

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

表達(dá)式比較運(yùn)算符

all

（子查詢）表達(dá)式的值與子查詢結(jié)果中的所有的值相比都滿足比較運(yùn)算符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子查詢找出平均成績(jī)最高的學(xué)生號(hào)

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#)找出每個(gè)系平均成績(jī)最高的學(xué)生exists子查詢[not]exists

（子查詢）判斷子查詢的結(jié)果集合中是否有任何元組存在in后的子查詢與外層查詢無(wú)關(guān)，每個(gè)子查詢執(zhí)行一次，而exists后的子查詢與外層查詢有關(guān)，需要執(zhí)行多次，稱之為相關(guān)子查詢exists子查詢示例：列出選修了c1號(hào)課程的學(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號(hào)和c2號(hào)課程的學(xué)生的學(xué)號(hào)

select S#

from SCSC1

where SC1.C#=c1 and

exists

(select S#

from SC

where C#=c2

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

何時(shí)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返回空集北京無(wú)處不飛沙除法在SQL中的表達(dá)Youcannotbetoocareful三年來(lái)，我獨(dú)自一人，無(wú)時(shí)無(wú)刻思念著你NegationPlusNegationMeansAffirmationNothingisnothingatallThereisnosuccesswithouthardships否定之否定vs

雙重否定Youcannotmakeeggrollswithoutb