数据库英文ppt课件.ppt

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 Wedon thavevaluesforallattributes andwewantthesystemtofillinmissingcomponentswithNULLoradefaultvalue 5 Example SpecifyingAttributes AnotherwaytoaddSydneyGreenstreettothelistofstarsofTheMalteseFalcon INSERTINTOStarsIn movieTitle movieYear starName VALUES TheMalteseFalcon 1942 SydneyGreenStreet 6 InsertingManyTuples Wemayinserttheentireresultofaqueryintoarelation usingtheform INSERTINTO 7 Example InsertaSubquery UsingStudioandMovie addtotherelationStudioallmoviestudiosthatarementionedintherelationMovie butdon tappearinStudio 8 Solution INSERTINTOStudio name SELECTDISTINCTstudioNameFROMMovieWHEREstudioNameNOTIN SELECTnameFROMStudio 9 Deletion Todeletetuplessatisfyingaconditionfromsomerelation DELETEFROMWHERE 10 Example Deletion DeletefromrelationStarsInthefactthatSydneyGreenStreetwasastarinTheMalteseFalcon DELETEFROMStarsInWHEREmovieTitle TheMalteseFalcon ANDmovieYear 1942ANDstarName SydneyGreenstreet 11 Example DeleteallTuples MaketherelationLikesempty DELETEFROMLikes NotenoWHEREclauseneeded 12 Example DeleteManyTuples DeletefromMovieExecallmovieexecutiveswhosenetworthislow lessthantenmilliondollars DELETEFROMMovieExecWHEREnetWorth 10000000 13 Updates Tochangecertainattributesincertaintuplesofarelation UPDATESETWHERE 14 Example Update ModifytherelationMovieExecbyprependingthetitlePres Infrontofeverymovieexecutiveswhoisthepresidentofastudio UPDATEMovieExecSETname Pres nameWHEREcert IN SELECTpresC FROMStudio 15 DefiningaDatabaseSchema Adatabaseschemacomprisesdeclarationsfortherelations tables ofthedatabase Severalotherkindsofelementsalsomayappearinthedatabaseschema includingviews indexes andtriggers whichwe llintroducelater 16 Creating Declaring aRelation Simplestformis CREATETABLE Todeletearelation DROPTABLE 17 ElementsofTableDeclarations Mostbasicelement anattributeanditstype Themostcommontypesare INTorINTEGERREALorFLOATCHAR n fixed lengthstringofncharacters VARCHAR n variable lengthstringofuptoncharacters 18 Example CreateTable CREATETABLEMovieStar nameCHAR 30 addressVARCHAR 255 genderCHAR 1 birthdateDATE 19 DatesandTimes DATEandTIMEaretypesinSQL Theformofadatevalueis DATE yyyy mm dd Example DATE 2004 09 30 20 TimesasValues Theformofatimevalueis TIME hh mm ss withanoptionaldecimalpointandfractionsofasecondfollowing Example TIME 15 30 02 5 21 Modifyingrelationschemas WecanuseALTERtomodifyarelationschema Wehaveseveraloptions themostimportantofwhichare ADDfollowedbyacolumnnameanditsdatatype DROPfollowedbyacolumnname 22 AddingAttributes Wemayaddanewattribute column toarelationschemaby ALTERTABLEADD Example ALTERTABLEMovieStarADDphoneCHAR 16 23 DeletingAttributes Removeanattributefromarelationschemaby ALTERTABLEDROP Example wedon treallyneedthelicenseattributeforbars ALTERTABLEMovieStarDROPbirthdate 24 Defaultvalues Whenwecreateormodifytuples wesometimesdon thavevaluesforallcomponents Toaddressthisproblem SQLprovidestheNULLvalue However therearetimeswhenwewouldprefertousedefaultvalue thevaluethatisplacedinacomponentifnoothervalueisknown 25 Example Wemightwishtousethecharacter asthedefaultforanunknowngender andwemightalsowishtousetheearliestpossibledate DATE 0000 00 00 foranunknownbirthdate CREATETABLEMovieStar nameCHAR 30 addressVARCHAR 255 genderCHAR 1 DEFAULT birthdateDATEDEFAULTDATE 0000 00 00 26 Indexes AnindexonanattributeAofarelationisadatastructurethatmakesitefficienttofindthosetuplesthathaveafixedvalueforattributeA 27 Tocreateaindex CreateanindexonattributeyearfortherelationMovieCREATEINDEXYearIndexONMovie year FromMovie createanindexontitleandyearCREATEINDEXKeyIndexONMovie title year 28 Todeleteaindex Ifwewishtodeletetheindex wesimplyuseitsnameinastatementlike DROPINDEXYearIndex Selectionofindexesrequiresatrade offbythedatabasedesignerTheexistenceofanindexonanattributegreatlyspeedsupqueriesinwhichavalueforthatattributeisspecified Ontheotherhand erveryindexbuiltforanattributeofsomerelationmakesinsertions deletion andupdatestothatrelationmorecomplexandtime consuming 29 Views Aviewisa virtualtable arelationdefinedintermsofthecontentsofothertablesandviews Declareby CREATEVIEWAS Antonym arelationwhosevalueisreallystoredinthedatabaseiscalledabasetable 30 Example ViewDefinition TodefineaviewthatisapartoftheMovierelation specifically thetitlesandyearsofthemoviesmadebyParamountStudio CREATEVIEWParamountMovieASSELECTtitle yearFROMMovieWHEREstudioName Paramount 31 Example AccessingaView Queryaviewasifitwereabasetable Also alimitedabilitytomodifyviewsifitmakessenseasamodificationofoneunderlyingbasetable Examplequery SELECTtitleFROMParamountMovieWHEREyear 1979 32 WhatHappensWhenaViewIsUsed TheSQLsystemwilltranslatethequeryontheviewParamountMovieintoaqueryaboutthebasetableMoviethathasthesameeffectasouroriginalquery SELECTtitleFROMMovieWHEREstudioName Paramount ANDyear 1979 33 Defineaquerybasedonviewsandbasetables Example SELECTDISTINCTstarNameFROMParamountMovie StarsInWHEREtitle movieTitleANDyear movieYear 34 Renamingattributes Wecangiveaview sattributesnamesofourownchoosing Forexample CREATEVIEWMovieProd movieTitle prodName ASSELECTtitle nameFROMMovie MovieExecWHEREproducerC cert 35 Deleteaview Ifaviewbecomesunuseful wecandeleteit Forinstance DROPVIEWParamountMovie 36 NULLValues TuplesinSQLrelationscanhaveNULLasavalueforoneormorecomponents Meaningdependsoncontext Twocommoncases Missingvalue e g weknowJoe sBarhassomeaddress butwedon tknowwhatitis Inapplicable e g thevalueofattributespouseforanunmarriedperson 37 Twoimportantrules WhenweoperateonaNULLandanyothervalue includinganotherNULL usinganarithmeticoperatorlike or theresultisNULL WhenwecompareaNULLvalueandanyvalue includinganotherNULL usingacomparisonoperatorlike or theresultisUNKNOWN ThevalueUNKNOWNisanothertruth value likeTRUEandFALSE 38 ToaskifxhasthevalueNULL xISNULL thisexpressionhavethevalueTRUEifxhasthevalueNULLandithasFALSEotherwise xISNOTNULL thisexpressionhavethevalueFALSEifxhasthevalueNULLandithasTRUEotherwise 39 ComparingNULL stoValues ThelogicofconditionsinSQLisreally3 valuedlogic TRUE FALSE UNKNOWN ButaqueryonlyproducesatupleintheanswerifitstruthvaluefortheWHEREclauseisTRUE notFALSEorUNKNOWN 40 JoinExpressions SQLprovidesseveralversionsofjoins Theseexpressionscanbestand alonequeriesorusedinplaceofrelationsinaFROMclause 41 ProductsandNaturalJoins Naturaljoin RNATURALJOINS Product RCROSSJOI