陕西师范大学计算机科学院第四章SQL课件_第1页
陕西师范大学计算机科学院第四章SQL课件_第2页
陕西师范大学计算机科学院第四章SQL课件_第3页
陕西师范大学计算机科学院第四章SQL课件_第4页
陕西师范大学计算机科学院第四章SQL课件_第5页
已阅读5页,还剩243页未读, 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

袂由冲我大学

普圉初翩拳绘跄

COLLEGEOFCOMPUTERSCIENCE

Chapter4:SQL

陕西师范大学计算机科学院

数据库原理与应用课程组

SHAANXINORMAL.UNIVERSITY

Chapter4:SQL

■BasicStructure

■SetOperations

■AggregateFunctions

■NullValues

■NestedSubqueries

■DerivedRelations

■Views

■ModificationoftheDatabase

■JoinedRelations

■DataDefinitionLanguage

■EmbeddedSQL,ODBCandJDBC

SHAANXINORMAL.UNIVERSITY

SchemaUsedinExamples

branchaccountdepositorcustomer

SHAANXINORMAL.UNIVERSITY

BasicStructure

■SQLisbasedonsetandrelationaloperationswithcertain

modificationsandenhancements

■AtypicalSQLqueryhastheform:

selectA2,An

fromr2,rm

whereP

□AjSrepresentattributes

□rjSrepresentrelations

□Pisapredicate.

Thisqueryisequivalenttotherelationalalgebra

expression.

—尸伉xqx...xrj)

TheresultofanSQLqueryisarelation.

SHAANXINORMAL.UNIVERSITY

TheselectClause

Theselectclauselisttheattributesdesiredintheresultof

aquery

□correspondstotheprojectionoperationoftherelationalalgebra

■E.g.findthenamesofallbranchesintheloanrelation

selectbranch-name

fromloan

Inthe“pure”relationalalgebrasyntax,thequerywouldbe:

Dbranch-nanne(/°a")

NOTE:SQLdoesnotpermitthecharacterinnames,

□Use,e.g.,branch_nameinsteadofbranch-nameinareal

implementation.

□Weusesinceitlooksnicer!

NOTE:SQLnamesarecaseinsensitive,i.e.youcanuse

capitalorsmallletters.

□Youmaywishtouseuppercasewhere-everweuseboldfont.

SHAANXINORMAL.UNIVERSITY

TheselectClause(Cont.)

SQLallowsduplicatesinrelationsaswellasinqueryresults.

Toforcetheeliminationofduplicates,insertthekeyword

distinctafterselect.

Findthenamesofallbranchesintheloanrelations,and

removeduplicates

selectdistinctbranch-name

fromloan

Thekeywordallspecifiesthatduplicatesnotberemoved,

selectallbranch-name

fromloan

SHAANXINORMAL.UNIVERSITY

TheselectClause(Cont.)

Anasteriskintheselectclausedenotes“allattributes”

select*

fromloan

Theselectclausecancontainarithmeticexpressions

involvingtheoperation,+,*,and/,andoperatingon

constantsorattributesoftuples.

■Thequery:

selectloan-number,branch-name,amount*

100

fromloan

wouldreturnarelationwhichisthesameastheloan

relations,exceptthattheattributeamountismultipliedby

100.

SHAANXINORMAL.UNIVERSITY

ThewhereClause

■Thewhereclausespecifiesconditionsthattheresultmust

satisfy

□correspondstotheselectionpredicateoftherelationalalgebra.

■TofindallloannumberforloansmadeatthePerryridge

branchwithloanamountsgreaterthan$1200.

selectloan-number

fromloan

wherebranch-name=Perryridge'andamount>

1200

■Comparisonresultscanbecombinedusingthelogical

connectivesand,or,andnot.

■Comparisonscanbeappliedtoresultsofarithmetic

expressions.

SHAANXINORMAL.UNIVERSITY

ThewhereClause(Cont.)

SQLincludesabetweencomparisonoperator

■E.g.Findtheloannumberofthoseloanswithloanamounts

between$90,000and$100,000(thatis,>$90,000and

<$100,000)

selectloan-number

fromloan

whereamountbetween90000and100000

SHAANXINORMAL.UNIVERSITY

ThefromClause

■Thefromclauseliststherelationsinvolvedinthequery

□correspondstotheCartesianproductoperationoftherelationalalgebra.

■FindtheCartesianproductborrowerxloan

select*

fromborrower,loan

■Findthename,loannumberandloanamountofallcustomers

havingaloanatthePerryridgebranch.

selectcustomer-name,borrower.loan-number,amount

fromborrower,loan

whereborrower.loan-number=loan.loan-numberand

branch-name=Perryridge'

SHAANXINORMAL.UNIVERSITY

TheRenameOperation

TheSQLallowsrenamingrelationsandattributesusingthe

asclause:

old-nameasnew-name

■Findthename,loannumberandloanamountofall

customers;renamethecolumnnameloan-numberasloan-id.

selectcustomer-name,borrower.loan-numberasloan-id,amount

fromborrower,loan

whereborrower.loan-number=loan.loan-number

SHAANXINORMAL.UNIVERSITY

TupleVariables

■Tuplevariablesaredefinedinthefromclauseviatheuseof

theasclause.

Findthecustomernamesandtheirloannumbersforall

customershavingaloanatsomebranch.

selectcustomer-name,T.loan-number,S.amount

fromborrowerasT,loanasS

whereT.loan-number=S.loan-number

■Findthenamesofallbranchesthathavegreaterassetsthan

somebranchlocatedinBrooklyn.

selectdistinctT.branch-name

frombranchasT,branchasS

whereT.assets>S.assetsandS.branch-city=Brooklyn"

SHAANXINORMAL.UNIVERSITY

StringOperations

SQLincludesastring-matchingoperatorforcomparisonson

characterstrings.Patternsaredescribedusingtwospecial

characters:

□percent(%).The%charactermatchesanysubstring.

□underscore(_).The_charactermatchesanycharacter.

■Findthenamesofallcustomerswhosestreetincludesthe

substring“Main”.

selectcustomer-name

fromcustomer

wherecustomer-streetlike*%Main%'

Matchthename“Main%”

liketMain\%'escape*\'

■SQLsupportsavarietyofstringoperationssuchas

□concatenation(using"『')

□convertingfromuppertolowercase(andviceversa)

□findingstringlength,extractingsubstrings,etc.

SHAANXINORMAL.UNIVERSITY

OrderingtheDisplayofTuples

Listinalphabeticorderthenamesofallcustomershavinga

loaninPerryridgebranch

selectdistinctcustomer-name

fromborrower,loan

whereborrowerloan-number=loan.loan-numberand

branch-name=*Perryridge)

orderbycustomer-name

■Wemayspecifydescfordescendingorderorascfor

ascendingorder,foreachattribute;ascendingorderisthe

default.

□E.g.orderbycustomer-namedesc

SHAANXINORMAL.UNIVERSITY

Duplicates

Inrelationswithduplicates,SQLcandefinehowmany

copiesoftuplesappearintheresult.

■Multisetversionsofsomeoftherelationalalgebraoperators

-givenmultisetrelationsqandr2:

1.Iftherearec1copiesoftupleinq,andsatisfiesselections

OQ,thentherearec1copiesofinG8(rj.

2.n4(rj:Foreachcopyoftuple。inq,thereisacopyoftuplenA(t1)in

nA(r^)wheren/Jdenotestheprojectionofthesingletuple

3.〃xQ:Iftherearec1copiesoftuplet1inqandc2copiesoftuplet2in

r2,thereareqxc2copiesofthetupletrt2inqxr2

SHAANXINORMAL.UNIVERSITY

Duplicates(Cont.)

Example:Supposemultisetrelationsq(A,B)andr2

(C)areasfollows:

。={(1,a)(2,a)}r2={(2),(3),(3))

■Then口口亿)wouldbe{(a),(a)},while口-亿)xr2

wouldbe

{(a,2),(a,2),(a,3),(a,3),(a,3),(a,3)}

■SQLduplicatesemantics:

select4,A,…,An

fromr1,r2,rm

where尸

isequivalenttothemultisetversionoftheexpression:

口41,)2…An(^p^xr2x...xrm))

SHAANXINORMAL.UNIVERSITY

SetOperations

■Thesetoperationsunion,intersect,andexceptoperateon

relationsandcorrespondtotherelationalalgebraoperations

u,n,

Eachoftheaboveoperationsautomaticallyeliminates

duplicates;toretainallduplicatesusethecorresponding

multisetversionsunionall,intersectallandexceptall.

Supposeatupleoccursmtimesinrandntimesins,then,it

occurs:

□m+ntimesinrunionalls

□min(/77,njtimesinrintersectalls

□max(0,m-n)timesinrexceptalls

SHAANXINORMAL.UNIVERSITY

SetOperations

Findallcustomerswhohavealoan,anaccount,orboth:

(selectcustomer-namefromdepositor)

union

(selectcustomer-namefromborrower)

■Findallcustomerswhohavebothaloanandanaccount.

(selectcustomer-namefromdepositor)

intersect

(selectcustomer-namefromborrower)

■Findallcustomerswhohaveanaccountbutnoloan.

(selectcustomer-namefromdepositor)

except

(selectcustomer-namefromborrower)

SHAANXINORMAL.UNIVERSITY

AggregateFunctions

Thesefunctionsoperateonthemultisetofvaluesofa

columnofarelation,andreturnavalue

avg:averagevalue

min:minimumvalue

max:maximumvalue

sum:sumofvalues

count:numberofvalues

SHAANXINORMAL.UNIVERSITY

AggregateFunctions(Cont.)

FindtheaverageaccountbalanceatthePerryridgebranch,

selectavg(balance)

fromaccount

wherebranch-name=Perryridge'

■Findthenumberoftuplesinthecustomerrelation.

selectcount(*)

fromcustomer

■Findthenumberofdepositorsinthebank.

selectcount(distinctcustomer-name)

fromdepositor

SHAANXINORMAL.UNIVERSITY

AggregateFunctions-GroupBy

■Findthenumberofdepositorsforeachbranch.

selectbranch-name,count(distinctcustomer-name)

fromdepositor,account

wheredepositor.account-number=account.account-number

groupbybranch-name

Note:Attributesinselectclauseoutsideofaggregatefunctionsmust

appearingroupbylist

SHAANXINORMAL.UNIVERSITY

AggregateFunctions-HavingClause

Findthenamesofallbrancheswheretheaverageaccount

balanceismorethan$1,200.

selectbranch-name,avg(balance)

fromaccount

groupbybranch-name

havingavg(balance)>1200

Note:predicatesinthehavingclauseareappliedafterthe

formationofgroupswhereaspredicatesinthewhere

clauseareappliedbeforeforminggroups

SHAANXINORMAL.UNIVERSITY

NullValues

Itispossiblefortuplestohaveanullvalue,denotedbynull,for

someoftheirattributes

■nullsignifiesanunknownvalueorthatavaluedoesnotexist.

■Thepredicateisnullcanbeusedtocheckfornullvalues.

□E.g.Findallloannumberwhichappearintheloanrelationwithnull

valuesforamount.

selectloan-number

fromloan

whereamountisnull

■Theresultofanyarithmeticexpressioninvolvingnullisnull

□E.g.5+nullreturnsnull

■However,aggregatefunctionssimplyignorenulls

□moreonthisshortly

SHAANXINORMAL.UNIVERSITY

NullValuesandThreeValuedLogic

■Anycomparisonwithnullreturnsunknown

□E.g.5<nullornull<>nullornull=null

■Three-valuedlogicusingthetruthvalueunknown-.

□OR:(unknownortrue)=true,(unknownorfalse)=unknown

(unknownorunknown)=unknown

□AND:(trueandunknown)=unknown,(falseandunknown)=false,

(unknownandunknown)=unknown

□NOT:fnotunknown)=unknown

□“尸isunknown55evaluatestotrueifpredicatePevaluatestounknown

■Resultofwhereclausepredicateistreatedasfalseifit

evaluatestounknown

SHAANXINORMAL.UNIVERSITY

NullValuesandAggregates

Totalallloanamounts

selectsum(amount)

fromloan

□Abovestatementignoresnullamounts

□resultisnullifthereisnonon-nullamount

□Allaggregateoperationsexceptcount(*)ignoretupleswithnullvalues

ontheaggregatedattributes.

SHAANXINORMAL.UNIVERSITY

NestedSubqueries

■SQLprovidesamechanismforthenestingofsubqueries.

■Asubqueryisaselect-from-whereexpressionthatis

nestedwithinanotherquery.

一个SELECT-FROM-WHERE语句称为一个查询块

将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查

询称为嵌套查询

■Acommonuseofsubqueriesistoperformtestsforset

membership,setcomparisons,andsetcardinality.

SHAANXINORMAL.UNIVERSITY

嵌套查询

■嵌套查询概述

■嵌套查询分类

■嵌套查询求解方法

■引出子查询的谓词

XINORMAL.UNIVERSITY

SELECTSname外层查询/父查询

FROMStudent

WHERESnoIN

(SELECTSno内层查询/子查询

FROMSC

WHERECno='2');

XINORMAL.UNIVERSITY

•子查询的限制

■不能使用ORDERBY子句

•层层嵌套方式反映了SQL语言的结构化

•有些嵌套查询可以用连接运算替代

SHAANXINORMAL.UNIVERSITY

嵌套查询分类

■不相关子查询

子查询的查询条件不依赖于父查询

■相关子查询

子查询的查询条件依赖于父查询

SHAANXINORMAL.UNIVERSITY

嵌套查询求解方法

■不相关子查询

是由里向外逐层处理。即每个子查询在

上一级查询处理之前求解,子查询的结果

用于建立其父查询的查找条件。

SHAANXINORMAL.UNIVERSITY

O嵌套查询求解方法

------------------------------

■相关子查询

■首先取外层查询中表的第一个元组,根据它与内层查询相关的属性值处

理内层查询,若WHERE子句返回值为真,则取此元组放入结果表;

■然后再取外层表的下一个元组;

■重复这一过程,直至外层表全部检查完为止。

SHAANXINORMAL.UNIVERSITY

引出子查询的谓词

踊带有IN谓词的子查询

-带有比较运算符的子查询

■带有ANY或ALL谓词的子查询

■带有EXISTS谓词的子查询

SHAANXINORMAL.UNIVERSITY

⑥一、带有IN谓词的子查询

:

[例37]查询与“刘晨”在同一个系学习的学生。

此查询要求可以分步来完成

①确定“刘晨”所在系名

SELECTSdept

FROMStudent

WHERESname='刘晨';

结果为:

Sdept

IS

SHAANXINORMAL.UNIVERSITY

带有IN谓词的子查询

②查找所有在is系学习的学生。

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept='IS';

结果为:

SnoSnameSdept

95001刘晨IS

95004张立IS

SHAANXINORMAL.UNIVERSITY

构造嵌套查询

将第一步查询嵌入到第二步查询的条件中

SELECTSno,Sname,Sdept

FROMStudent

WHERESdeptIN

(SELECTSdept

FROMStudent

WHERESname±刘晨);

此查询为不相关子查询。DBMS求解该查询时也是分步

去做的。

SHAANXINORMAL.UNIVERSITY

带有IN谓词的子查询

用自身连接完成本查询要求

SELECTSl.Sno,Sl.Sname,Sl.Sdept

FROMStudentS1,StudentS2

WHERESl.Sdept=S2.SdeptANDS2.Sname=刘晨';

SHAANXINORMAL.UNIVERSITY

O)带有IN谓词的子查询______

父查询和子查询中的表均可以定义别名

SELECTSno,Sname,Sdept

FROMStudentS1

WHERES1.SdeptIN

(SELECTSdept

FROMStudentS2

WHERES2.Sname=,刘晨)

SHAANXINORMAL.UNIVERSITY

带有IN谓词的子查询

[例38]查询选修了课程名为“信息系统”的学生学号和姓名

SELECTSno,Sname③最后在Student关系中

FROMStudent取出Sno和Sname

WHERESnoIN

(SELECTSno②然后在SC关系中找出选

FROMSC修了3号课程的学生学号

WHERECnoIN

(SELECTCno①首先在Course关系中找出“信

FROMCourse息系统”的课程号,结果为3号

WHERECname」信息系统'));

SHAANXINORMAL.UNIVERSITY

O带有IN谓词的子查询

结果:

SnoSname

95001李勇

95002刘晨

SHAANXINORMAL.UNIVERSITY

带有IN谓词的子查询

■用连接查询

SELECTSno,Sname

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoAND

SC.Cno=Course.CnoAND

Course.(2皿111日信息系统:

SHAANXINORMAL.UNIVERSITY

二、带有比较运算符的子查询

■当能确切知道内层查询返回单值时,可用比较运算符(>,<,

=,>=,<=,!=或<>)o

■与ANY或ALL谓词配合使用

SHAANXINORMAL.UNIVERSITY

带有比较运算符的子查询

例:假设一个学生只可能在一个系学习,并且必须属于一个系,

则在[例37]可以用二代替IN

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept=

SELECTSdept

FROMStudent

WHERESname='刘晨';

SHAANXINORMAL.UNIVERSITY

带有比较运算符的子查询

子查询一定要跟在比较符之后

错误的例子:

SELECTSno,Sname,Sdept

FROMStudent

WHERE(SELECTSdept

FROMStudent

WHERESname='刘晨,)

=Sdept;

SHAANXINORMAL.UNIVERSITY

三、带有ANY或ALL谓词的子查询

谓词语义

•ANY:任意一个值

•ALL:所有值

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

需要配合使用比较运算符

>ANY大于子查询结果中的某个值

>ALL大于子查询结果中的所有值

<ANY小于子查询结果中的某个值

<ALL小于子查询结果中的所有值

>=ANY大于等于子查询结果中的某个值

>=ALL大于等于子查询结果中的所有值

<=ANY小于等于子查询结果中的某个值

<=ALL小于等于子查询结果中的所有值

=ANY等于子查询结果中的某个值

=ALL等于子查询结果中的所有值(通常没有实际意义)

!=(或。)ANY不等于子查询结果中的某个值

!=(或v>)ALL不等于子查询结果中的任何一个值

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

[例39]查询其他系中比信息系任意个(其中某个)学生年龄小的学

生姓名和年龄

SELECTSname,Sage

FROMStudent

WHERESage<ANY(SELECTSage

FROMStudent

WHERESdept='IS')

ANDSdept<>'IS';

/*注意这是父查询块中的条件*/

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

结果

SnameSage

王敏18

执行过程

1.DBMS执行此查询时,首先处理子查询,找出

IS系中所有学生的年龄,构成一个集合(19,18)

2,处理父查询,找所有不是IS系且年龄小于

19或18的学生

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

■ANY和ALL谓词有时可以用集函数实现

ANY与ALL与集函数的对应关系

=O或!=<<=>>=

ANYIN——<MAX<=MAX>MIN>=MIN

ALL—NOTIN<MIN<=MIN>MAX>=MAX

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

用集函数实现子查询通常比直接用ANY或ALL查询效

率要高,因为前者通常能够减少比较次数

[例3刃:用集函数实现[例39]

SELECTSname,Sage

FROMStudent

WHERESage<

(SELECTMAX(Sage)

FROMStudent

WHERESdept='IS')

ANDSdept<>'IS';

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

[例40]查询其他系中比信息系所有学生年龄都小的学生姓名

及年龄。

方法一:用ALL谓词

SELECTSname,Sage

FROMStudent

WHERESage<ALL

(SELECTSage

FROMStudent

WHERESdept='IS')

ANDSdept<>'IS';

查询结果为空表。

SHAANXINORMAL.UNIVERSITY

带有ANY或ALL谓词的子查询

方法二:用集函数

SELECTSname,Sage

FROMStudent

WHERESage<

(SELECTMIN(Sage)

FROMStudent

WHERESdept='IS')

ANDSdept<>'IS';

SHAANXINORMAL.UNIVERSITY

四、带有EXISTS谓词的子查询

1.EXISTS谓词

2.NOTEXISTS谓词

3.不同形式的查询间的替换

4.相关子查询的效率

5.用EXISTS/NOTEXISTS实现全称量词

6.用EXISTS/NOTEXISTS实现逻辑蕴函

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

•1.EXISTS谓词

■存在量词三

■带有EXISTS谓词的子查询不返回任何数据,只产生逻辑真值“true”

或逻辑假值"false,

/若内层查询结果非空,则返回真值

/若内层查询结果为空,则返回假值

■由EXISTS引出的子查询,其目标列表达式通常都用*,因为带

EXISTS的子查询只返回真值或假值,给出列名无实际意义

•2.NOTEXISTS谓词

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

[例41]查询所有选修了1号课程的学生姓名。

■用嵌套查询

SELECTSname

FROMStudent

WHEREEXISTS

(SELECT*

FROMSC/*相关子查询*/

WHERESno=Student.SnoANDCno-1');

求解过程

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

思路分析:

•本查询涉及Student和SC关系。

•在Student中依次取每个元组的Sno值,用此值去检查SC关系。

•若SC中存在这样的元组,其Sno值等于此Student.Sno值,并且其

Cno-1',则取此Student.Sname送入结果关系。

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

■用连接运算

SELECTSname

FROMStudent,SC

WHEREStudent.Sno=SC.SnoANDSC.Cno=l;

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

[例42]查询没有选修1号课程的学生姓名。

SELECTSname

FROMStudent

WHERENOTEXISTS

(SELECT*

FROMSC

WHERESno=Student.SnoANDCno=T);

此例用连接运算难于实现

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

3.不同形式的查询间的替换

一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价

替换

所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓

词的子查询等价替换。

SHAANXINORMAL.UNIVERSITY

做)带有EXISTS谓词的子查询

例:[例37]查询与“刘晨”在同一个系学习的学生。可以用带

EXISTS谓词的子查询替换:

SELECTSno,Sname,Sdept

FROMStudentSI

WHEREEXISTS

SELECT*

FROMStudentS2

WHERES2.Sdept=SI.SdeptAND

S2.Sname='刘晨';

SHAANXINORMAL.UNIVERSITY

O)带有EXISTS谓词的子查询

5.用EXISTS/NOTEXISTS实现全称量词(难点)

■SQL语言中没有全称量词V(Forall)

■可以把带有全称量词的谓词转换为等价的带有存在量词的谓词:

(Vx)P三「0x(「P))

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

[例43]查询选修了全部课程的学生姓名。

SELECTSname

FROMStudent

WHERENOTEXISTS

(SELECT*

FROMCourse

WHERENOTEXISTS

(SELECT*

FROMSC

WHERESno=Student.Sno

ANDCno=Course.Cno));

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

6.用EXISTS/NOTEXISTS实现逻辑蕴函(难点)

■SQL语言中没有蕴函(Implication)逻辑运算

■可以利用谓词演算将逻辑蕴函谓词等价转换为:

pfq三一1pVq

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

[例44]查询至少选修了学生95002选修的全部课程的学生号码。

解题思路:

•用逻辑蕴函表达:查询学号为x的学生,对所有的课程y,只要

95002学生选修了课程y,贝Ijx也选修了y。

•形式化表示:

用P表示谓词“学生95002选修了课程y”

用q表示谓词“学生x选修了课程y”

则上述查询为:(Vy)p->q

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

.等价变换:

(Vy)p->q-0y(「(p—q))

=「(3y(-1(-1pVq)

=-i3y(pA-!q)

•变换后语义:不存在这样的课程y,学生95002选修了y,而学生x没

有选。

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

用NOTEXISTS谓词表示:

SELECTDISTINCTSno

FROMSCSCX

WHERENOTEXISTS

(SELECT*

FROMSCSCY

WHERESCY.Sno='950021AND

NOTEXISTS

(SELECT*

FROMSCSCZ

WHERESCZ.Sno=SCX,SnoAND

SCZ,Cno=SCY.Cno));

SHAANXINORMAL.UNIVERSITY

带有EXISTS谓词的子查询

土,设有关系S、SC、C,用SQL找出选修了“程军”老师教的所有

课程的学生姓名。

SELECTS.SNAME

FROMS

WHERENOTEXISTS

SELECT*

FROMC

WHEREC.TEACHER±程军'ANDNOTEXISTS

SELECT*

FROMSC

WHERESC.C#=C.C#ANDSC.S#=S.S#

))

SHAANXINORMAL.UNIVERSITY

本题解释:

SELECT*

FROMC

WHEREC.TEACHER±程军'ANDEXISTS

SELECT*

FROMSC

WHERESC.C#=C.C#

)

这条SQL是查询“程军”所授的所有课程,外层查询中C.C#作为变量在内

层查询中被使用,外层教师“程军”所授课程的课程号C#给内层,内层根

据该课程号C#在SC中查询是否存在这样的课程号,即程军所授的课程,如

果存在则括号中的记录集不空,EXISTS条件为TRUE。因此,执行结果是

显示程军所授的所有课程。

SHAANXINORMAL.UNIVERSITY

本题解释:

FROMC

WHEREC.TEACHER士程军'ANDNOTEXISTS

(

SELECT*

FROMSC

WHERESC.C#=C.C#

)

这条SQL语句在刚才语句的EXISTS前加了NOT,变成当内层

查询全部是程军,所授课程时NOTEXISTS条件为假。

SHAANXINORMAL.UNIVERSITY

本题解释:

市幺SELECTS.SNAME

FROMS

WHERENOTEXISTS

(SELECT*

FROMC

WHEREC.TEACHERT程军,ANDNOTEXISTS

(SELECT*

FROMSC

WHERESC.C#=C.C#ANDSC.S#=S.S#)

这条是完整的语句,比上面一条语句多了一个ANDSC.S#=S.S#表示

某个学生的学号,加上最外层的NOTEXISTS的整个语句的含义是,

对该某个学生,如果最内层查询中查询结果全部是‘程军'所授课程

时,条件C.CNAME士程军'ANDNOTEXISTS为FALSE,所以

SELECT*FROMC的查询结果为空(第二层),这时最外层的NOT

EXISTS为TRUE,最外层查找成功,该学生是选修了‘程军'所授全

部课程的。_______

SHAANXINORMAL.UNIVERSITY

ExampleQuery

Findallcustomerswhohavebothanaccountandaloanat

thebank.

selectdistinctcustomer-name

fromborrower

wherecustomer-namein(selectcustomer-name

fromdepositor)

■Findallcustomerswhohavealoanatthebankbutdonothave

anaccountatthebank

selectdistinctcustomer-name

fromborrower

wherecustomer-namenotin(selectcustomer-name

fromdepositor)

SHAANXINORMAL.UNIVERSITY

ExampleQuery

■Findallcustomerswhohavebothanaccountandaloanat

thePerryridgebranch

selectdistinctcustomer-name

fromborrower,loan

whereborrower.loan-number=loan.loan-numberand

branch-name="Perryridge”and

(branch-name,customer-name)in

(selectbranch-name,customer-name

fromdepositor

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论