下载

2下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 数据库英文第五章

数据库英文第五章.ppt

数据库英文第五章

yuan830402
2010-11-06 0人阅读 举报 0 0 暂无简介

简介:本文档为《数据库英文第五章ppt》,可适用于IT/计算机领域

ChapterTheDatabaseLanguageSQLChapterTheDatabaseLanguageSQLSQL(sometimespronounced“sequel”)standsfor“StructuredQueryLanguage”EvolutionofSQLstandard:SQLSQL(SQL)SQLSQLisbasedonsetandrelationaloperationswithcertainmodificationsandenhancementsComponentsofSQL:DDL:DataDefinitionLanguageCREATETABLE,DROPTABLE,etcDML:DataManipulationLanguageQuery:SELECTModification:INSERT,DELETE,UPDATESimpleQueriesinSQLSimpleQueriesinSQLPrincipalform:SELECTA,A,,AnFROMr,r,,rmWHEREPAisrepresentattributesrisrepresentrelationsPisapredicateThisqueryisequivalenttotherelationalalgebraexpressionA,A,,An(P(rxrxxrm))FormalSemanticsofSingleRelationSQLQueryStartwiththerelationintheFROMclauseApply(bag),usingconditioninWHEREclauseApply(extended,bag)usingattributesinSELECTclauseTheresultofanSQLqueryisarelationSimpleQueriesinSQL(cont)SimpleQueriesinSQL(cont)Example:WhatmoviesareproducedbyDisneyStudiosinMovie(title,year,length,inColor,studioName,producerC#)SELECT*FROMMovieWHEREstudioName=’Disney’ANDyear=Note:singlequotesforstringsAnasteriskintheselectclausedenotes“allattributes”EquivalentOperationalSemanticsImagineatuplevariablerangingoveralltuplesoftherelationForeachtuple:CheckifitsatisfiestheWHEREclausePrintthevaluesoftermsinSELECT,ifsoProjectioninSQLTheselectclauseProjectioninSQLTheselectclauseStarasListofAllAttributesTheselectclausecorrespondstotheprojectionoperationoftherelationalalgebraItisusedtolisttheattributesdesiredintheresultofaqueryFindthetitlesandlengthofallmoviesareproducedbyDisneyStudiosinMovie(title,year,length,inColor,studioName,producerC#)SELECTtitle,lengthFROMMovieWHEREstudioName=’Disney’ANDyear=Inthe“pure”relationalalgebrasyntax,whatwouldthequerybeRenamingcolumnsTheSQLallowsrenamingrelationsandattributesusingtheasclause:oldnameASnewnameExample:SELECTtitleASname,lengthASdurationFROMMovieWHEREstudioName=’Disney’ANDyear=ProjectioninSQLTheselectclause(cont)ProjectioninSQLTheselectclause(cont)ExpressionsASValuesinColumnsExample:SELECTtitleASname,length*ASlengthInHoursFROMMovieWHEREstudioName=’Disney’ANDyear=Trick:Ifyouwantananswerwithaparticularstringineachrow,usethatconstantasanexpressionSELECTtitleASname,length*ASlength,‘hrs’ASinHoursFROMMovieWHEREstudioName=’Disney’ANDyear=Summary:Theselectclausecancontainarithmeticexpressionsinvolvingtheoperation,,–,*,and,andoperatingonconstantsorattributesoftuplesRemember:SQLiscaseinsensitive,meaningyoucanuseupperlowercaseasyoulikeOnlyinsidequotedstringsdoescasematterSelectioninSQLThewhereclauseSelectioninSQLThewhereclauseThewhereclausecorrespondstotheselectionpredicateoftherelationalalgebra,ifconsistsofapredicateinvolvingattributesoftherelationsthatappearinthefromclauseConditionsinWHEREclausecanuselogicaloperatorsAND,OR,NOTandparenthesesintheusualwayComparisonresultscanbecombinedusingthesixcommoncomparisonconnectives=,<>,<,>,<=,and>=ComparisonscanbeappliedtoresultsofarithmeticexpressionsExample:FindthetitlesofallmoviesmadebyMGMstudiosthatwereeithermadeafterorwerelessthanminuteslongMovie(title,year,length,inColor,studioName,producerC#)SELECTtitleFROMMovieWHERE(year>ORlength<)ANDstudioName=’MGM’SelectioninSQLThewhereclause(cont)SelectioninSQLThewhereclause(cont)SQLIncludesabetweencomparisonoperatorinordertosimplifywhereclausesthatspecifythatavaluebelessthanorequaltosomevalueandgreaterthanorequaltosomeothervalueFindthetitlesofthosemovieswiththelengthbetweenand(thatis,and)selecttitlefrommoviewherelengthbetweenandComparisonofstringsComparisonofstringsSQLincludesastringmatchingoperatorforcomparisonsoncharacterstrings“AttributeLIKEpattern”isaconditionthatistrueifthestringvalueoftheattributematchesthepatternAlsoNOTLIKEfornegationPatternsaredescribedusingtwospecialcharacters:percent:standsforanystringUnderscore:standsforanyonecharacterExampleWerememberamovie“Starsomething”,andwerememberthatthesomethinghasfourlettersWhatcouldthismoviebeSELECTtitleFROMmovieWHEREtitleLIKE’Star’Notepatternsmustbequoted,likestringsComparisonofstrings(cont)Comparisonofstrings(cont)ExampleFindallmovieswithapossessive(‘s)intheirtitlesSELECTtitleFROMmovieWHEREtitleLIKE’’’s’Note:twosinglequotesinacharacterstringrepresentonesinglequoteSQLsupportsavarietyofstringoperationssuchasconcatenation(using“||”)convertingfromuppertolowercase(andviceversa)findingstringlength,extractingsubstrings,etcComparingDatesandTimesComparingDatesandTimesImplementationsofSQLgenerallysupportdatesandtimesasspecialdatatypesorMayorDATE’’TIME‘::’Wecancomparedatesortimesusingthesamecomparisonoperators(<,>,<=,>=,<>,=)weusefornumbersorstringsOrderingtheoutputOrderingtheoutputTogetthemovieslistedbylength,shortestfirst,andamongmoviesofequallength,alphabetically,wecansay:select*frommoviewherestudioName=‘Disney’andyear=orderbylength,titleWemayspecifydescfordescendingorderorascforascendingorder,foreachattributeascendingorderisthedefaultEgorderbylengthdescWecanusethenumberoftheattributesinsteadofthenamesorderby,QueriesinvolvingmorethanonerelationQueriesinvolvingmorethanonerelationSometimes,queriesinvolvingmorethanonerelationListofrelationsinFROMclauseRelationdotattributedisambiguatesattributesfromseveralrelationsSameasforsinglerelation,butstartwiththeproductofalltherelationsmentionedintheFROMclauseProductsandJoinsinSQLProductsandJoinsinSQLListeachrelationsintheFROMclauseThentheSELECTandWHEREclausescanrefertotheattributesofanytherelationsintheFROMclauseExample:Movie(title,year,length,inColor,studioName,producerC#)MovieExec(name,address,cert#,netWorth)FindthenameoftheproducerofStarWarsSELECTnameFROMMovie,MovieExecWHEREtitle=’StarWars’ANDproducerC#=cert#OperationalSemanticsConsideratuplevariableforeachrelationintheFROMImaginethesetuplevariableseachpointingtoatupleoftheirrelation,inallcombinations(eg,nestedloops)IfthecurrentassignmentoftuplevariablestotuplesmakestheWHEREtrue,thenoutputtheattributesoftheSELECTDisambiguatingattributesDisambiguatingattributesListofrelationsinFROMclauseRelationdotattributedisambiguatesattributesfromseveralrelationsExampleFindpairsconsistingofastarandanexecutivewiththesameaddressMovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)SELECTMovieStarname,MovieExecnameFROMMovieStar,MovieExecWHEREMovieStaraddress=MovieExecaddressTupleVariablesTupleVariablesSometimesweneedtorefertotwoormorecopiesofarelationUsetuplevariablesasaliasesoftherelationsTuplevariablesaredefinedinthefromclauseviatheuseoftheasclauseExample:FindpairsofmoviestarsbythesameaddressMovieStar(name,address,gender,birthdate)SELECTStarname,StarnameFROMMovieStarASStar,MovieStarASStarWHEREStaraddress=StaraddressANDStarname<StarnameSQLpermitsASbetweenrelationanditstuplevariableOracledoesnotNotethatStarname<Starnameisneededtoavoidproducing(Sue,Sue)andtoavoidproducingapairinbothordersifweused<>asthecomparisonoperator,thenwewouldproducepairsofstarstwicestarnamestarnameAlecBaldwinKimBasingerKimBasingerAlecBaldwinInterpretingMultirelationQueriesInterpretingMultirelationQueriesThereareseveralwaystodefinethemeaningoftheselectfromwhereexpressionsNestedloopsConsideratuplevariableforeachrelationintheFROMImaginethesetuplevariableseachpointingtoatupleoftheirrelation,inallcombinations(eg,nestedloops)IfthecurrentassignmentoftuplevariablestotuplesmakestheWHEREtrue,thenoutputtheattributesoftheSELECTForeachtupletinrelationRdoForeachtupletinrelationRdo……ForeachtupletninrelationRndoifthewhereclauseissatisfiedwhenthevaluesfromt,t…tnaresubstitutedforallattributereferencesthenevaluatetheattributesoftheselectclauseaccordingtot,t…tnandproducethetupleofvaluesthatresultsParallelAssignmentWeconsiderinarbitraryorderorinparallelallpossibleassignmentsoftuplesfromtheappropriaterelationstothetuplevariablesConversiontoRelationalAlgebraWestartwiththetuplevariablesintheFROMclauseandtaketheirCartesianproduceUseaselectionoperatortoitbyconvertingtheWHEREclausetoaselectionconditionFinally,wecreatefromtheselectclausealistofattributesforafinalprojectionoperationExample:Selectstarnaem,starnameFromMoviestarASstar,MoviestarASstarWherestaraddress=staraddressANDstarname<starnameA,A(A=AANDA<A(M(A!,A,A,A)(Moviestar)xN(A,A,A,A)(Moviestar)))Union,Intersection,andDifferenceofQueriesUnion,Intersection,andDifferenceofQueriesThesetoperationsunion,intersect,andexceptoperateonrelationsandcorrespondtotherelationalalgebraoperationsEachoftheaboveoperationsautomaticallyeliminatesduplicatestoretainallduplicatesusethecorrespondingmultisetversionsunionall,intersectallandexceptallExample:MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Findthenamesallmoviestarsandmovieexecutives:(selectnamefromMovieStar)union(selectnamefromMovieExec)Union,Intersection,andDifferenceofQueries(cont)Union,Intersection,andDifferenceofQueries(cont)Example:MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Findthenamesandaddressesofallfemalemoviestarswhoarealsomovieexecutiveswithanetworthover$,,:(selectname,addressfromMovieStarwheregender=‘F’)intersect(selectname,addressfromMovieExecwherenetworth>)Findthenamesandaddressesofmoviestarswhoarenotalsomovieexecutives:(selectname,addressfromMovieStar)except(selectname,addressfromMovieExec)SubqueriesSubqueriesResultofaselectfromwherequerycanbeusedinthewhereclauseofanotherquerySubqueriesthatproducescalarvaluesSubqueriesthatproducescalarvaluesSimplestCase:SubqueryReturnsaSingle,UnaryTupleExample:Movie(title,year,length,inColor,studioName,producerC#)MovieExec(name,address,cert#,netWorth)FindthenameoftheproducerofStarWarsSELECTnameFROMMovie,MovieExecWHEREtitle=’StarWars’ANDproducerC#=cert#SELECTnameFROMMovieExecWHEREcert#=(SELECTproducer#FROMMovieWHEREtitle=’StarWars’)Noticethescopingrule:anattributereferstothemostcloselynestedrelationwiththatattributeParenthesesaroundsubqueryareessentialConditionsinvolvingrelationsandtuplesConditionsinvolvingrelationsandtuplesTheINOperator“TupleINrelation”istrueiffthetupleisintherelationExampleFindthenameandmanufacturerofbeersthatFredlikesBeers(name,manf)Likes(drinker,beer)SELECT*FROMBeersWHEREnameIN(SELECTbeerFROMLikesWHEREdrinker='Fred’)Also:NOTINConditionsinvolvingrelationsandtuples(cont)Conditionsinvolvingrelationsandtuples(cont)EXISTS“EXISTS(relation)”istrueifftherelationisnonemptyExampleFindthebeersthataretheuniquebeerbytheirmanufacturerBeers(name,manf)SELECTnameFROMBeersbWHERENOTEXISTS(SELECT*FROMBeersWHEREmanf=bmanfANDname<>bname)Notescopingrule:torefertoouterBeersintheinnersubquery,weneedtogivetheouteratuplevariable,binthisexampleAsubquerythatreferstovaluesfromasurroundingqueryiscalledacorrelatedsubqueryConditionsinvolvingrelationsandtuples(cont)Conditionsinvolvingrelationsandtuples(cont)Quantifiers:ANYandALLbehaveasexistentialanduniversalquantifiers,respectivelyBeware:incommonparlance,“any”and“all”seemtobesynonyms,eg,“Iamfatterthananyofyou”vs“Iamfatterthanallofyou”ButinSQL:ExampleFindthebeer(s)soldforthehighestpriceSells(bar,beer,price)SELECTbeerFROMSellsWHEREprice>=ALL(SELECTpriceFROMSells)ClassProblemFindthebeer(s)notsoldforthelowestpriceCorrelatedsubqueriesCorrelatedsubqueriesFindthetitlesthathavebeenusedfortwoormoremoviesMovie(title,year,length,inColor,studioName,producerC#)SELECTtitleFROMMovieASOldWHEREyear<ANY(SELECTyearFROMMovieWHEREtitle=Oldtitle)Duplicates(ForcingSetBagSemantics)Duplicates(ForcingSetBagSemantics)Defaultforselectfromwhereisbagdefaultforunion,intersection,anddifferenceissetWhySavestimeofnotcomparingtuplesaswegeneratethemButweneedtosortanywaywhenwetakeintersectionordifference(Unionseemstobethrowninforgoodmeasure!)EliminatingduplicatesForcesetsemanticswithDISTINCTafterSELECTButmakesuretheextratimeisworthitExample:FindthedifferentpriceschargedforbeersSells(bar,beer,price)SELECTDISTINCTpriceFROMSellsEliminatingduplicatesDuplicatesinUnions,Intersection,andDifferenceDuplicatesinUnions,Intersection,andDifferenceForcebagsemanticswithALLafterUNION,etcExample:MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Findthenamesallmoviestarsandmovieexecutives:(selectnamefromMovieStar)unionall(selectnamefromMovieExec)Supposeatupleoccursmtimesinrandntimesins,then,itoccurs:mntimesinrunionallsmin(m,n)timesinrintersectallsmax(,m–n)timesinrexceptallsAggregationsAggregationsSum,avg,min,max,andcountapplytoattributescolumnsAlso,count(*)appliestotuplessum:sumofvaluesavg:averagevaluemin:minimumvaluemax:maximumvaluecount:numberofvaluesUsetheseinlistsfollowingSELECTExample:FindtheaveragepriceofBudSells(bar,beer,price)SELECTAVG(price)FROMSellsWHEREbeer='Bud'AggregationoperatorsAggregationoperators(cont)EliminatingDuplicatesBeforeAggregationExample:FindthenumberofdifferentpricesatwhichBudissoldSells(bar,beer,price)SELECTCOUNT(DISTINCTprice)FROMSellsWHEREbeer='Bud'DISTINCTmaybeusedinanyaggregation,buttypicallyonlymakessensewithCOUNTAggregationoperators(cont)GroupingGroupingFollowselectfromwherebyGROUPBYandalistofattributesTherelationthatistheresultoftheFROMandWHEREclausesisgroupedaccordingtothevaluesoftheseattributes,andaggregationstakeplaceonlywithinagroupExampleFindtheaveragesalespriceforeachbeerSells(bar,beer,price)SELECTbeer,AVG(price)FROMSellsGROUPBYbeerGrouping(cont)Grouping(cont)Example:PrintthebarsintherelationSellsSells(bar,beer,price)SELECTbarFROMSellsGROUPBYbarThisqueryhasthesameeffectas:SELECTDISTINCTbarFROMSellsHAVINGClausesHAVINGClausesHAVINGclausesareselectionsongroups,justasWHEREclausesareselectionsontuplesExample:FindtheaveragepriceofthosebeersthatareeitherservedinatleastbarsormanufacturedbyAnheuserBuschBeers(name,manf)Sells(bar,beer,price)SELECTbeer,AVG(price)FROMSellsGROUPBYbeerHAVINGCOUNT(*)>=ORbeerIN(SELECTnameFROMBeersWHEREmanf='AnheuserBusch’)OrderofclausesinSQLqueries()SELECT()FROM()WHERE()GROUPBY()HAVING()ORDERBYOnlythefirsttwoarerequiredDBModificationsDBModificationsModification=insertdeleteupdateInsertionInsertionofaTupleINSERTINTOrelationVALUES(listofvalues)Insertsthetuple=listofvalues,associatingvalueswithattributesintheordertheattributesweredeclaredForgettheorderListtheattributesasargumentsoftherelationExample:InsertthefactthatSallylikesBudLikes(drinker,beer)INSERTINTOLikes(drinker,beer)VALUES('Sally','Bud')Insertion(cont)Insertion(cont)InsertionoftheResultofaQueryINSERTINTOrelation(subquery)Example:SupposewewanttoaddtotherelationLikes(drinker,beer)allbeersthatarementionedintherelationSells(bar,beer,price)INSERTINTOLikes(beer)(SELECTDISTINCTbeerFROMSellsWHEREbeerNOTIN(SELECTbeerFROMLikes)DeletionDeletionDE

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/62

数据库英文第五章

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利