数据库英文课件4.ppt

1、1,MoreSQL,DatabaseModificationDefiningaDatabaseSchemaViews,2,DatabaseModifications,Amodificationcommanddoesnotreturnaresult(asaquerydoes),butchangesthedatabaseinsomeway.Threekindsofmodifications:Insertatupleortuples.Deleteatupleortuples.Updatethevalue(s)ofanexistingtupleortuples.,3,Insertion,Toinser

2、tasingletuple:INSERTINTOVALUES();Example:addSydneyGreenstreettothelistofstarsofTheMalteseFalcon.INSERTINTOStarsInVALUES(TheMalteseFalcon,1942,SydneyGreenStreet);,4,SpecifyingAttributesinINSERT,Wemayaddtotherelationnamealistofattributes.Tworeasonstodoso:Weforgetthestandardorderofattributesfortherelat

3、ion.Wedonthavevaluesforallattributes,andwewantthesystemtofillinmissingcomponentswithNULLoradefaultvalue.,5,Example:SpecifyingAttributes,AnotherwaytoaddSydneyGreenstreettothelistofstarsofTheMalteseFalcon.INSERTINTOStarsIn(movieTitle,movieYear,starName)VALUES(TheMalteseFalcon,1942,SydneyGreenStreet);,

4、6,InsertingManyTuples,Wemayinserttheentireresultofaqueryintoarelation,usingtheform:INSERTINTO();,7,Example:InsertaSubquery,UsingStudioandMovie,addtotherelationStudioallmoviestudiosthatarementionedintherelationMovie,butdontappearinStudio.,8,Solution,INSERTINTOStudio(name)(SELECTDISTINCTstudioNameFROM

5、MovieWHEREstudioNameNOTIN(SELECTnameFROMStudio);,9,Deletion,Todeletetuplessatisfyingaconditionfromsomerelation:DELETEFROMWHERE;,10,Example:Deletion,DeletefromrelationStarsInthefactthatSydneyGreenStreetwasastarinTheMalteseFalcon:DELETEFROMStarsInWHEREmovieTitle=TheMalteseFalconANDmovieYear=1942ANDsta

6、rName=SydneyGreenstreet;,11,Example:DeleteallTuples,MaketherelationLikesempty:DELETEFROMLikes;NotenoWHEREclauseneeded.,12,Example:DeleteManyTuples,DeletefromMovieExecallmovieexecutiveswhosenetworthislow-lessthantenmilliondollars.DELETEFROMMovieExecWHEREnetWorth,theresultisUNKNOWN.ThevalueUNKNOWNisan

7、othertruth-value,likeTRUEandFALSE.,38,ToaskifxhasthevalueNULL,xISNULL,thisexpressionhavethevalueTRUEifxhasthevalueNULLandithasFALSEotherwise.xISNOTNULL,thisexpressionhavethevalueFALSEifxhasthevalueNULLandithasTRUEotherwise,39,ComparingNULLstoValues,ThelogicofconditionsinSQLisreally3-valuedlogic:TRUE

8、,FALSE,UNKNOWN.ButaqueryonlyproducesatupleintheanswerifitstruthvaluefortheWHEREclauseisTRUE(notFALSEorUNKNOWN).,40,JoinExpressions,SQLprovidesseveralversionsofjoins.Theseexpressionscanbestand-alonequeriesorusedinplaceofrelationsinaFROMclause.,41,ProductsandNaturalJoins,Naturaljoin:RNATURALJOINS;Prod

9、uct:RCROSSJOINS;Example:LikesNATURALJOINServes;Relationscanbeparenthesizedsubqueries,aswell.,42,ThetaJoin,RJOINSONExample:usingMovieandStarIn:MovieJOINStarInONtitle=movieTitleANDyear=movieyear;,43,Outerjoins,ROUTERJOINSisthecoreofanouterjoinexpression.Itismodifiedby:OptionalNATURALinfrontofOUTER.Opt

10、ionalONafterJOIN.OptionalLEFT,RIGHT,orFULLbeforeOUTER.LEFT=paddanglingtuplesofRonly.RIGHT=paddanglingtuplesofSonly.FULL=padboth;thischoiceisthedefault.,44,Example,MovieStarNATURALFULLOUTERJOINMovieExec;WhereFULLcanbereplacedbyLEFTorRIGHTMovieFULLOUTERJOINStarInONtitle=movieTitleANDyear=movieYear;WhereFULLcanbereplacedby