数据库概论 SQL-数据查询

第2章SQL数据查询SQL查询功能汇览查询基本结构select子句from子句where子句重复行的处理更名运算行显示顺序字符串操作全文检索关系的连接分组和聚集函数空值嵌套子查询集合操作查询基本结构

锱铢必较：SQL返回结果是多集select R.Afrom Rwhere R.B<10select R.Afrom R,Swhere R.B<10VSPKKORAB1213SCD3413selectR1.*fromRR1,RR2,RR3,RR4,RR5快速生成测试数据select子句中的目标列形式可以为列名，*，算术表达式，聚集函数“*”：表示“所有的属性”

给出所有学生的所有信息

select*

from S带，，，的算术表达式 给出所有学生的姓名及出生日期

selectSNAME，2016-AGE

from S*对性能的影响将多个列组合为一个目标列给出每个老师信息的自然语言描述 select pname+’老师的工资是’+salary+ ’,年龄是’+age+’,职称是’+title from professor

输出行的形式类似：“李明老师的工资是1500,年龄是45,职称是教授”from子句from子句列出查询的对象表示例：找出选修课程的学生姓名、课程名、成绩select SNAME,CNAME,GRADEfrom S,C,SCwhereS.S#=SC.S# and C.C#=SC.C#乱花渐欲迷人眼写出与等价的SQL当目标列取自多个表时，需要显式指明来自哪个关系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子句判断表达式的值是否在某范围内示例：列出工资在500~800之间的老师姓名

selectPNAME

fromPROF

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

A<a1orA>a2优化小窍门：用between合并两个比较谓词重复行的处理SQL缺省为保留重复行，也可用关键字all显式指明。若要去掉重复行，可用关键字distinct指明示例：找出所有选修课程的学生

select

distinctS# fromSC

优化小窍门：只在必要时使用distinct重复行的处理查询一：查询二：查询三：select

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]示例：按年龄升序列出学生信息，相同年龄学生按姓名降序排列

select*

fromS

orderbyAGEasc，SNAMEdesc输出显示顺序示例：对教工按缴纳所得税的多少排序select

pname,sal*0.2from

proforderby 2示例：按年龄顺序输出学生姓名select snamefrom studentorderby age更名运算old_nameasnew_name

为关系和属性重新命名，可出现在select和from子句中 注：as可选属性更名示例：给出所有学生的姓名、性别、出生日期，并按出生日期升序排列

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

from S

orderby

出生日期（或者orderby 3)关系更名示例：找出比s1学生选修c1课程成绩高的学生号

select S2.S#

from SCasS1，SCasS2

where S1.S#=‘s1’

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

and S1.GRADE<S2.GRADE关系更名示例：找出工资比所在系主任工资高的老师姓名及工资

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

‘字符串’

匹配规则‘%’

：匹配零个或多个字符‘＿’：匹配任意单个字符[]：任何在指定范围内的字符[a-f]，[abcdef][^]：任何不在指定范围内的字符[^a-f]，[^abcdef]Like中的转义字符用escape定义转义字符，以去掉特殊字符的特定含义，使其被作为普通字符看待如escape‘\‘，定义\作为转义字符，则可用\%去匹配%，用\＿去匹配＿‘%a_bx’select*fromttwherec1like‘x%%xx'escape‘x'思考：用什么去匹配\？字符串操作示例列出姓名以“张”打头的教师的所有信息

select*

fromPROF

wherePNAMElike‘张%‘列出名称中含有3个以上字符，且倒数第三个是d，倒数第二个是_的课程

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

在CNAME上建有索引CNAME_idx，观察下面查询的执行计划，看谁用到了该索引。A. select*fromCwhereCNAMElike'%d'B. select*fromCwhereCNAMElike'd%'小巫见大巫的Like全文索引正则表达式正则表达式（RegularExpression）记录文本规则的代码；用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串简单示例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函数名说明REGEXP_LIKE类似于LIKE运算符，但执行正则表达式匹配而不是简单的模式匹配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%’全文检索正文倒排索引搜索引擎的挑战：数量、分类、重复PageRankstepABCDEFGH11/21/161/161/161/161/161/161/825/161/41/41/321/321/321/321/16HITS：枢纽-权威算法全文检索：创建 createfulltextcatalogcatalog_name createfulltextindexon

table_name[(column_name] keyindexindex_name

oncatalog_name keyindex指定table_name上唯一键索引的名称，最好是聚簇索引全文检索：查询Contains(属性列|*，查找条件)freetext(属性列|*，查找文本)使用freetext时，全文查询引擎内部将查找文本拆分为若干个搜索词，并赋予每个词以不同的加权，然后查找匹配创建全文检索的示例 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')全文检索的创建及使用演示select *from docwhere

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

freetext(content, 'AdaptiveQueryProcessing')数据库查询和信息检索的区别关系的连接类型连接成分 包括两个输入关系、连接条件、连接类型连接条件 决定两个关系中哪些元组相互匹配，以及连接结果中出现哪些属性连接类型 决定如何处理与连接条件不匹配的元组关系的连接类型连接类型连接条件innerjoinleftouterjoinrightouterjoinfullouterjoinon<谓词>(R

crossjoin

S)asT两个关系的笛卡儿积关系的连接类型列出老师的教工号、姓名、工资、所教课程号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违反了关系定义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参与算术运算，则该算术表达式的值为null如果null参与比较运算，则结果可视为unknown表中存在两行(1,2,null),(1,2,null)，selectdistinct*?空值测试示例示例：找出成绩值为空的学生号

selectS#

fromSC

whereGRADEisnull不可写为whereGRADE=null

空值处理函数isnull(check_expression,replacement_value)

如果check_expression值为空,则返回replacement_value,否则返回check_expressionselect

S#,C#,isnull(GRADE,0

)from

SC空值处理函数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聚集函数将一列中所有的值聚集为单个值平均值：avg最小值：min最大值：max总和：sum记数：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将表中的元组按指定列上值相等的原则分组，然后在每一分组上使用聚集函数，得到单一值

having则对分组进行选择，只将聚集函数作用到满足条件的分组上分组S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388{{{S#C#Gs1c184s1c290s1c396s2c180s2c290s3c296s3c388列出每个学生的平均成绩列出每门课程的平均成绩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分组的查询示例示例：列出每个学生的最高、最低、平均成绩

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#物莫非指而指非指天下无指物不可谓天下无物谁径谓指分组查询中各子句的顺序列出每一年龄组中男学生（超过50人）的人数

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个属性的所有groupby共有2n个cubeCUBEcubeselect Model,Year,Color,sum(Sales)from car_salesgroupby Model,Year,Colorwithcube总行数=(model个数+1)*(year个数+1)*(color个数+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是一个聚合函数,它产生一个附加的列，当用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()函数，可以标示每一行到底和哪个groupby相关联，这是通过为不同的分组分配不同的整数来做到的例如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子查询）集合基数的测试（exists子查询）测试集合是否为空in子查询表达式[not]in

（子查询）判断表达式的值是否在子查询的结果中示例：列出张军和王红同学的所有信息

select*

fromS

whereSNAMEin

(‘张军’,’王红’)in子查询示例：选修了c1号课程的学生的姓名selectSNAMEfromS,SCwhereS.S#=SC.S#

andC#=c1)selectSNAMEfromSwhereS#in (selectS#

fromSC

whereC#=c1)问题1：等价否？问题2：谁更有效？in子查询示例：列出选修了c1号和c2号课程的学生的学号

selectS#

fromSC

whereSC.C#=c1

andS#in

(selectS#

fromSC

whereC#=c2)some/all子查询表达式比较运算符

some

（子查询）表达式的值至少与子查询结果中的一个值相比满足比较运算符

表达式比较运算符

all

（子查询）表达式的值与子查询结果中的所有的值相比都满足比较运算符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子查询找出平均成绩最高的学生号

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#)找出每个系平均成绩最高的学生exists子查询[not]exists

（子查询）判断子查询的结果集合中是否有任何元组存在in后的子查询与外层查询无关，每个子查询执行一次，而exists后的子查询与外层查询有关，需要执行多次，称之为相关子查询exists子查询示例：列出选修了c1号课程的学生姓名

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号课程的学生的学号

select S#

from SCSC1

where SC1.C#=c1 and

exists

(select S#

from SC

where C#=c2

and S#=SC1.S#)反半连接：notin,notexists列出没有选修c1号课程的学生的姓名

何时notexists优于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