数据库英文完整4ppt课件

.,1,MoreSQL,DatabaseModificationDefiningaDatabaseSchemaViews,.,2,DatabaseModifications,Amodificationcommanddoesnotreturnaresult(asaquerydoes),butchangesthedatabaseinsomeway.Threekindsofmodifications:Insertatupleortuples.Deleteatupleortuples.Updatethevalue(s)ofanexistingtupleortuples.,.,3,Insertion,Toinsertasingletuple:INSERTINTOVALUES();Example:addSydneyGreenstreettothelistofstarsofTheMalteseFalcon.INSERTINTOStarsInVALUES(TheMalteseFalcon,1942,SydneyGreenStreet);,.,4,SpecifyingAttributesinINSERT,Wemayaddtotherelationnamealistofattributes.Tworeasonstodoso:Weforgetthestandardorderofattributesfortherelation.Wedonthavevaluesforallattributes,andwewantthesystemtofillinmissingcomponentswithNULLoradefaultvalue.,.,5,Example:SpecifyingAttributes,AnotherwaytoaddSydneyGreenstreettothelistofstarsofTheMalteseFalcon.INSERTINTOStarsIn(movieTitle,movieYear,starName)VALUES(TheMalteseFalcon,1942,SydneyGreenStreet);,.,6,InsertingManyTuples,Wemayinserttheentireresultofaqueryintoarelation,usingtheform:INSERTINTO();,.,7,Example:InsertaSubquery,UsingStudioandMovie,addtotherelationStudioallmoviestudiosthatarementionedintherelationMovie,butdontappearinStudio.,.,8,Solution,INSERTINTOStudio(name)(SELECTDISTINCTstudioNameFROMMovieWHEREstudioNameNOTIN(SELECTnameFROMStudio);,.,9,Deletion,Todeletetuplessatisfyingaconditionfromsomerelation:DELETEFROMWHERE;,.,10,Example:Deletion,DeletefromrelationStarsInthefactthatSydneyGreenStreetwasastarinTheMalteseFalcon:DELETEFROMStarsInWHEREmovieTitle=TheMalteseFalconANDmovieYear=1942ANDstarName=SydneyGreenstreet;,.,11,Example:DeleteallTuples,MaketherelationLikesempty:DELETEFROMLikes;NotenoWHEREclauseneeded.,.,12,Example:DeleteManyTuples,DeletefromMovieExecallmovieexecutiveswhosenetworthislow-lessthantenmilliondollars.DELETEFROMMovieExecWHEREnetWorth,theresultisUNKNOWN.ThevalueUNKNOWNisanothertruth-value,likeTRUEandFALSE.,.,38,ToaskifxhasthevalueNULL,xISNULL,thisexpressionhavethevalueTRUEifxhasthevalueNULLandithasFALSEotherwise.xISNOTNULL,thisexpressionhavethevalueFALSEifxhasthevalueNULLandithasTRUEotherwise,.,39,ComparingNULLstoValues,ThelogicofconditionsinSQLisreally3-valuedlogic:TRUE,FALSE,UNKNOWN.ButaqueryonlyproducesatupleintheanswerifitstruthvaluefortheWHEREclauseisTRUE(notFALSEorUNKNOWN).,.,40,JoinExpressions,SQLprovidesseveralversionsofjoins.Theseexpressionscanbestand-alonequeriesorusedinplaceofrelationsinaFROMclause.,.,41,ProductsandNaturalJoins,Naturaljoin:RNATURALJOINS;Product:RCROSSJOINS;Example:LikesNATURALJOINServes;Relationscanbeparenthesizedsubqueries,aswell.,.,42,ThetaJoin,RJOINSONExample:usingMovieandStarIn:MovieJOINStarInONtitle=movieTitleANDyear=movieyear;,.,43,Outerjoins,ROUTERJOINSisthecoreofanouterjoinexpression.Itismodifiedby:OptionalNATURALinfrontofOUTER.OptionalONafterJOIN.OptionalLEFT,RIGHT,orFULLbeforeOUTER.LEFT=paddanglingtuplesofRonly.RIGHT=paddanglingtuplesofSonly.FULL=padboth;thischoiceisthedefault.,.,44,Example,MovieStarNATURALFULLOUTERJOINMovieExec;WhereFULLcanbereplacedbyLEFTorRIGHTMovieFULLOUTERJOINStarInONtitle=movieTitleANDyear=movieYear;WhereFULLcanbereplacedby