Springer.Database.Design.Manual.using.MySQL.for.Windows.(2004)

资源描述：

《Springer.Database.Design.Manual.using.MySQL.for.Windows.(2004) 》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

DatabaseDesignManual:usingMySQL™forWindows SpringerLondonBerlinHeidelbergNewYorkHongKongMilanParisTokyo MatthewNormanDatabaseDesignManual:usingMySQLforWindows1232 MatthewNorman,BSc(Hons)mysql@coldfusionfast.comThepublisherwouldliketoacknowledgethesupportofJohnCowell,DeMonfortUniversity,Leicester,SeriesEditoroftheEssentialseries,forwhichthisbookwasoriginallyproposed.BritishLibraryCataloguinginPublicationDataNorman,Matthew,1968-Databasedesignmanual:usingMySQLforWindows1.Databasedesign2.Relationaldatabases3.SQL(Computerprogramlanguage)I.Title005.7′565ISBN1852337168LibraryofCongressCataloging-in-PublicationDataAcatalogrecordforthisbookisavailablefromtheLibraryofCongressApartfromanyfairdealingforthepurposesofresearchorprivatestudy,orcriticismorreview,aspermittedundertheCopyright,DesignsandPatentsAct1988,thispublicationmayonlybereproduced,storedortransmitted,inanyformorbyanymeans,withthepriorpermissioninwritingofthepublishers,orinthecaseofreprographicreproductioninaccordancewiththetermsoflicencesissuedbytheCopyrightLicensingAgency.Enquiriesconcerningreproductionoutsidethosetermsshouldbesenttothepublishers.ISBN1-85233-716-8Springer-VerlagLondonBerlinHeidelbergAmemberofBertelsmannSpringerScience
BusinessMediaGmbH©Springer-VerlagLondonLimited2004MySQLisatrademarkofMySQLAB.Theuseofregisterednames,trademarksetc.inthispublicationdoesnotimply,evenintheabsenceofaspecificstatement,thatsuchnamesareexemptfromtherelevantlawsandregulationsandthereforefreeforgeneraluse.Thepublishermakesnorepresentation,expressorimplied,withregardtotheaccuracyoftheinformationcontainedinthisbookandcannotacceptanylegalresponsibilityorliabilityforanyerrorsoromissionsthatmaybemade.TypesetbyGrayPublishing,TunbridgeWells,UKPrintedandboundintheUnitedStatesofAmerica34/3830-543210Printedonacid-freepaperSPIN10901137 ToRowan,LittleRooandZed.IknowI'dsaidIwouldn’twriteanotherone Thispageintentionallyleftblank Contents1MySQLandDatabases.........................................................1AboutThisBook..............................................................1AboutMySQL................................................................1WhoCanUseThisBook?......................................................1DatabasesandtheInternet...................................................2DBMS........................................................................2MySQLDatabases............................................................2Tables........................................................................3Client-serverSystems.........................................................6StructuredQueryLanguage...................................................72InstallingandTestingMySQL...................................................9ObtainingMySQL.............................................................9InstallingtheMySQLServer...................................................9InstallingtheGraphicalClientTool............................................9MySQLDirectoryStructure....................................................10TheOnlineMySQLManual....................................................11StartingtheMySQLService...................................................11MySQLConfigurationFiles....................................................13ConnectingtotheMySQLService.............................................13ConnectingwiththeGraphicalTool...........................................15MySQLGUIQuickTour........................................................173HowShallWeStoreit?–Datatypes..............................................19TheDatatype.................................................................19NumericTypes...............................................................20CharacterTypes..............................................................24FixedorVariableLength?.....................................................26StoringText..................................................................26BLOB.........................................................................27DateTypes...................................................................27OtherTypes..................................................................29SET...........................................................................30ENUM........................................................................31vii viiiContents4DesigningandCreatingTables..................................................33RedundantData..............................................................34ThePrimaryKey..............................................................34ForeignKeys.................................................................35RedundantorNot?...........................................................36ReferentialIntegrity..........................................................36NULL.........................................................................37CREATEDATABASE...........................................................38CREATETABLE................................................................39IdentifyingForeignKeys......................................................40CreatefromSelectStatement.................................................42CREATETABLELIKE...........................................................435PopulatingtheDatabase.......................................................45INSERT.......................................................................45INSERT–MultipleRows.......................................................46INSERT–AllColumns.........................................................47INSERT–ColumnsfromaQuery...............................................48InsertingDatawithScripts....................................................49DirectlyInsertingData........................................................506RetrievingtheData...........................................................55SELECT.......................................................................55SELECTDISTINCT.............................................................57WHERE.......................................................................58MySQLOperators.............................................................59Subqueries...................................................................717JoiningTables................................................................73InformationinMultipleTables................................................73CrossJoins...................................................................74InnerJoins...................................................................75Equi-join.....................................................................75Equi-joinsonMoreTables....................................................78RestrictingEqui-joins.........................................................79INNERJOIN–AnotherFormat.................................................79OuterJoins...................................................................818ChangingData................................................................87AlteringData.................................................................87UPDATERevisited............................................................97REPLACE.....................................................................98CREATEorREPLACE..........................................................100DUPLICATEKEYUPDATE......................................................100 Contentsix9DeletingourData.............................................................103KeepingDataAccurate.......................................................103DELETEFROM................................................................103DROPTABLE..................................................................105DROP........................................................................10610AggregateFunctions..........................................................107Count........................................................................107GROUPBY....................................................................108MAX.........................................................................111MIN..........................................................................112SUM.........................................................................113UsingMultipleAggregates....................................................113AVG..........................................................................115HAVING......................................................................11511WorkingwithDatesandTimes..................................................117UsingDateandTime.........................................................117WorkingwithDates...........................................................133CommonDateQueries.......................................................13412SecuringtheDatabase.........................................................135ProtectingValuableData.....................................................135TheUserTable...............................................................135Thetables_privTable.........................................................141TheGrantTables.............................................................142RestrictingUsers..............................................................145OtherPasswords.............................................................14613OptimizingMySQL.............................................................151TablesGetBigger.............................................................151HowWellDoesItRun?........................................................151ExaminingQueries...........................................................152LookingatCross-joinsAgain..................................................154Indexing.....................................................................156CREATEINDEX................................................................157OPTIMIZETABLE..............................................................158CHECKTABLE.................................................................159REPAIRTABLE................................................................160ANALYZETABLE..............................................................16114SharingMySQLdatawithMyODBC...............................................163It’sNotJustForMySQL.......................................................163MoreStandards..............................................................163OpenDatabaseConnectivity..................................................163 xContentsDataSources.................................................................164ObtainingMyODBC...........................................................164InstallingMyODBCDrivers....................................................164SettingUpaDataSource.....................................................166ExampleConnections.........................................................170MySQLandASP..............................................................171MySQLandColdFusion.......................................................176CreatingaDataSourceinColdFusion.........................................176AccessingtheDataSourceinColdFusion......................................17815MySQLwithPHP,ApacheandPerl................................................183IndigoPerl....................................................................183AQuickPHPScript............................................................186AccessingMySQL.............................................................187WorkingwithPerl............................................................191DefiningaPerlandHTMLDocument..........................................192ConnectingtoMySQLwithDBI...............................................193GeneratingtheQuery........................................................193ProcessingtheQueryResults.................................................194CopingwithErrors............................................................195ReformattingtheOutput.....................................................19616Makinglifeeasier.............................................................199OtherTools...................................................................199MySQLControlCentre........................................................199PHPMyAdmin................................................................203 MySQLandDatabases1AboutThisBookTherearemanybooksouttherethatdescribeMySQLandPHP.WhilemostofthemdevotealotofspacetodescribingPHP,theyonlybrieflydescribeMySQL.Iwantedtowriteabookthatwasdifferentfromthis,inthatIwantedabookthatdescribedMySQLfromawebper-spectivewithonlypassingreferencestoPHP.Thisisthatbook.ThisbookwilldescribethesetupanduseofaMySQLserverandclientandhowtoaccess,useandsecurethissystem.MySQLandPHPoriginallyappearedontheLinuxplatform,somanybooksdealwithitfromaLinuxperspective.ThisbookwillteachyouaboutinstallingandusingMySQLontheMicrosoftWindowsplatform.However,muchofthetheorybehindthesoftwarewillbeofusewhateverplatformyouareusing.AboutMySQLTheMySQLwebsitedescribesMySQLasthe“world’smostpopularOpenSourcedatabase.”ItspopularityisnodoubtencouragedbythefactthatifyouneedMySQLfornon-commer-cialuse,youcandownloadacopyfreefromthewebsite.MySQLisnearlyalwaysbundledwiththePHPwebscriptinglanguage,andthetwoproductsareoftenseenmentionedtogether.MostLinuxdistributionscomewithMySQLandPHPasstandardandMySQLhasbeenportedforusetoavarietyofdifferentplatforms.DuetoitsbundlingwithPHP,MySQLismostoftenusedasadatabasebackendtoaweb-server.WhoCanUseThisBook?Thisbookshouldappealtoanyonewhoneedsmoreinsightintohowdatabasesworkinrelationtotheweb.IfyouarecompletelynewtousingMySQLthenthisbookwillserveasagoodintroductiontothesoftware.Withaninternetconnection,youcaneasilyobtainMySQLandassociatedtoolsatnocost.ThisbookwillalsobeofbenefittosomeonewantingtolearnSQL.Whileworkingthroughtheexamplesyouwillgetafirmunderstandingoftheconceptsbehindstructuredquerylanguageandhowyouuseittocommunicatewithdatabases.1 2DatabaseDesignManual:usingMySQLforWindowsIfyouareawebdesignerthisbookshouldalsoappealifyouwanttoseehowthedatabasecaninfluenceyourwebdesign.Youcanseewhatisgoingonbehindthesceneswithawebdatabase.IfyouareahardeneddatabaseprogrammerthenthisbookmayhaveaplaceinyourlibraryifyouwanttoseewhatMySQLdoesspecifically,orhowitcaninterfacewithothersystems.Thebookalsogivesabriefintroductiontodatabasedesign,andshowshowanefficientlydesigneddatabasecanimprovetheperformanceofMySQL.LotsofbooksaboutSQLanddatabasesstartwithalotoftheory.Thetheorynormallydescribesthe“relationalmodel”andinstilsterrorintoallbutthemosttechnicalreader!I’mnotgoingtodothishere.Youdonothavetolearnaboutdatabasesbylookingatthetheory.IwantyoutolearnhowtouseMySQL,andbyassociationStructuredQueryLanguage(SQL).Youmaypickupabitofthetheoryontheway.DatabasesandtheInternetAsawebdesigner,databasescanbeofgreatuse.Intheearlydaysoftheinternet,websiteswerejuststaticthingsthatrequiredeachpagetobecreatedmanually.Thismayhavebeenlucrativeonanhourlyratebutthemorepagesthesitehadthelesssustainabletheywere.Imaginetryingtomountasalescatalogueontheweb;youmayhavehadtocreateapageforeveryiteminthecatalogue.Whendatabasesbegantoget“glued”towebsites,itsud-denlymeantthatyoucouldcreateastandardtemplatepage,whichretrievedtheproductdetailfromthedatabaseandcreatedawebpage“onthefly”foreachproduct.Thatisnottheonlyuseforawebdatabase.Assoonasyoustarttoputproductsonlineyouruserswanttobuythem!Anotherdatabasecanbeusedtologtheirorders,andeventoprocessandpicktheirordersbythewarehousestaffandreorderfromsuppliers.Eventhewebitselfrequiresdatabasestorun.SearchengineslikeGoogleuseincrediblyefficientdatabasesthatretrievelinkstopagesforussosimplythatwetakethemforgranted.DBMSMySQLiswhatisknownasaDatabaseManagementSystem(DBMS).Themanagementsystemdecideshowthedataisstored,sortedandretrieved,aswellascontrollinguseraccesstoit.Everytimeauserretrievesdata,deletesdata,oraddsmoredata,theDBMSdealswiththerequest.Theusercannotaccessthedatafilesdirectly,hecanonlytalktotheDBMS.Themanagementsystemisabarrierthatcontrolsaccesstotheunderlyingdata.Figure1.1illustrateshowarequesttothedatabasehastogothroughtheDBMSfirst,andcannotgodirectlytothedatabaseitself.MySQLDatabasesMySQLcancontrolseveraldatabasesatonce.Forinstance,whenyouinstallMySQL,thesystemcreatesthesystemdatabasewhichisnamedmysql.Thisdatabasecontainsallofthe Chapter1•MySQLandDatabases3UserDBMSUserRequestRequestDatabasesUserRequestUserRequestFigure1.1DBMScontrolsaccesstotheunderlyingdata.datarequiredtodefineanyoftheactivitiesthatMySQLhastoperform.Itstoresdetailsofotherdatabases,usersandallotherfilesthatthesystemusestostoredata.Ititselfisacol-lectionofdatausedforaspecificpurpose.ThismakesMySQLself-defining,inthatthetablesthatitstoresareusedtodefineothertablesthatitstores.Whenyouarecreatingyourownsetsofdata,createtheseinanotherdatabase.Inthisbookwewillbeusingthemysqldatabasetolookatspecificsystemfunctions,butalloftheotherdatathatwecreatewillbestoredinadatabasecalledmysqlfast.MySQLcaneasilycontrolmorethanonedatabase,sotopreventyourtablesbeingconfusedwithsystemdata,itisbesttoseparatethembyusingdifferentdatabases.TablesThedatabaseisacontainertostoreyourtablesin.Thetableisacollectionofdataofasim-ilartype.Forinstance,youcouldstorealloftheaccessestoawebsiteinonetable,calledlog.YoumayhaveseenalogfilesuchastheoneshowninFigure1.2fromanApachewebserver.Figure1.2showsanexcerptfromalogfile.Thefile,calledaccess.log,hasanewlineaddedtoiteverytimearequestismadetotheApacheserver.Theentirefilecouldbelikenedtoatablewithinthedatabase.Allofthetypesofdatathatrepresentanaccesstothewebserverarestoredwithinthisonefile,ortable,whichhasaname(access.log).Inthesamedirectorythatthisfileisstoredinisanotherfilecallederrors.log.ThisfilestoresalloftheerrorsthattheApacheserverlogsandisreallyanothertable.MySQLcouldbeusedtostoreallofthelogentriesandalloftheerrormessagesgener-atedbythewebserver.Theoriginaldatabaseprogramsallowedyoutosetuplittlemorethanatable.Moderndatabasesystemsareclassedasrelational,althoughthereismuchargumentinthedatabasefraternityastowhatisrelationalandwhatisnot.Wewillnotenterthisdebateinthisbook.However,thetermrelationalisusefulindescribingsystemssuchasMySQLastheDBMSallowsyoutorelatedataintablestodatainothertables.Infact,sometablescontain 4DatabaseDesignManual:usingMySQLforWindowsFigure1.2AnApacheaccesslogfile.nothingmorethandataonhowtwoormoreothertablesarerelated.Inrelationaldatabasetheory,tablesarecalledrelations.ColumnsAnytablewillhaveasetofattributes,thatis,alistoftypesofitemsthateachtablewillstore.Inthisbookwewillrefertothisattributeasacolumn.Intheold-styledatabasesyouwouldhavecalledthisafield.IfyoulookatFigure1.2youwillseethateachcolumncontainsaspecifictypeofdata.ThefirstcolumncontainstheIPaddressofthemachinethatisaccessingthewebserver.Thesecondandthirdcolumnsappeartostorenothing,whichisrepresentedasahyphenchar-acter(-).Thefourthcolumncontainsthedateandtimethattheaccesstookplace.ThefifthcontainstheHTTPcommandthatwasreceivedandthecolumnscontinue.Eachcolumninatablehasauniquenamewithinthattable,andthatishowwerefertothecolumn.SobasedontheinformationthatwehavefromFigure1.2wecouldbegintodefinethetableasfollows:Log(ip,accesstime,http_command)Logisthenameofthetable,andip,accesstimeandhttp_commandarethecolumns.Atabledoesnotneedtohavedatawithinittobeclassedasatable.Atablewithnocontentisstillatable.Thisdescriptionofthedatabasestructurewithoutactualdataisknownasmeta-data.Themeta-dataalsocontainsdetailsaboutanyrestrictionsofaccesstothedata-baseaswell.Youwillalsonoticethateachcolumncontainsdataofaspecifictype.Thefirstcolumnisacollectionoffournumbersseparatedbystops.Thenexttwocouldbestrings,andthenthelasttwocolumnsinthefigurearenumbers.Alldatainacolumnmustbeofthe Chapter1•MySQLandDatabases5sametype.Whenyoudefinethecolumn,youspecifythetypeofdatawithinit.Thenextchapterwilldescribesomeofthetypesofdatathatyoucanstoreineachcolumn.Rows,RecordsandTuplesArow,arecordandatuplearealldifferentwaysofreferringtoalineofdatawithinatable.InthisbookIwillgenerallyusethetermrow.Thetupleistherelationaltheorytermforarow,andarecordistheearlierformofdatabasesterm.InFigure1.2thefollowingcouldbedescribedasasingleroworrecord:191.168.1.3--[07/Nov/2002:09:39:47+0000]“GET/html/index.phpHTTP/1.1”302262Alotofthedatainthelogisthesameforeachrow;infact,intheexcerptwehaveshown,theonlythingthatchangesisthetime.Thisisnotactuallyaveryefficientwayofstoringthisinformation,assomuchofitisstatic.Wewilldealwithstoringthisinabetterwayinalaterchapter.RelationsWehavealreadystatedthattherelationaltheorytermforatableisarelation.However,tablesinrelationaldatabasescanhaverelationshipsbetweenthem.LookagainatthelastbutonecolumnofthelogfileinFigure1.2.Itcontainsthenumber302.Thiscolumnstoresanumberthatrepresentsthemessagethatthesystemislogging.Forinstance,ifyourequestapagethatisnotonawebserver,youmaybefamiliarwithreceivinga404error.Thisnum-berrepresentstheerrorcondition:Notfound.Infactthe302messagerepresentsthecondi-tion:Movedtemporarily.Table1.1showssomemoreoftheseconditionsTable1.1Webserverstatuscodes.CodeCondition200OK300Multiplechoices302Movedtemporarily401Authorizationrequired403Forbidden404NotfoundImaginethatTable1.1wasstoredasatableinadatabase.Everytimethatthewebserveraddedarowtoitslogtable,itcouldlogthestatusconditionforthelogentrybyaddingthestring“Movedtemporarily”,oritcouldaddthenumericcodefromTable1.1tothelogentryinstead.Apacheusesthelatterapproach.Itincludesacodeinthelogfilethatcanbeusedtolookupanothervalueinanothertable.Thesetwotablesarethereforerelatedtoeachother.Theyarerelatedinthatanentryinonetableisakeytoanentryinanother.Mostdatabasesclaimtoberelationalones.Thereismuchdebateastowhatmakesadatabaserelationalornot,andmanyclaimsthatadatabasesystemisrelationalaredis- 6DatabaseDesignManual:usingMySQLforWindowsputedbyothers.Wewillnotgointothisargumenthere,butifsomeonepointsthisouttoyou,donotbetooconcerned.Letthesedebatescontinue,andjustusethesoftware!Client-serverSystemsWehavealreadydescribedhowMySQLisadatabasemanagementsystem,inthatitcontrolsaccesstothedatabaseaswellashowthedatabaseisphysicallyorderedonthestoragemedium.Youprobablyareveryfamiliarwiththeconceptofaclient-serversystem.Figure1.3showsasimplediagramofoneofthese.Dataisstoredontheserver,andwhentheclientneedstoaccessthatdata,itmakesarequesttotheserver.Onreceivingthatrequest,theservertakesthenecessaryaction,andsendsitsresponsebacktotheclient.Theresponsecouldbethedata,arequestformoredetails,oranerrormessage.Oneofthemostwidespreadclient-serversystemsistheinternet.Aremoteclient,ofteninanothercountryfromtheserver,makesarequestforsomeformofdataovertheinternet,whichtheserverrespondsto.Theclientandtheserverdonotnecessarilyhavetobeondifferentmachines.AsyousetupMySQLinthecourseofreadingthisbook,youwillsetuptheMySQLserversoftwareandtheclientsoftwareonthesamemachine.Thesystemisstillaclient-serversystemevenifitallrunsinthesamebox.OneofthecommonestusesofMySQLisasadatabasebehindawebsite.Whenitisusedinthiswaytherearelotsofclient-serverrequestshappening.Figure1.4illustratesthis.InFigure1.4youcanseethatfirstofallauserasksawebbrowsertolookatawebpage.Thisrequestgetssenttothewebserver.IfthatwebpageisbuiltusingascriptinglanguagesuchasPHP,therequestgetsforwardedtothescriptprocessor.Ifthescriptprocessorfindsdatabasecommands(SQL)inthescriptitpassesthemtotheDBMS,thatis,theMySQLserver.MySQLprocessestheSQL,whichissentbacktothescriptprocessorastext.ThistextisusedtogeneratetheHTMLalongwithothercommandsinthescriptinglanguage,andthisgetssentbacktotheuserinHTML,whichfinallygetsprocessedbythewebbrowserintoawebpage.Althoughthisseemslikeacomplexroute,itcanhappenveryquickly,evenwhentheserversareondifferentmachinesthattherequestsandresponseshavetobeforwardedbetween.Eachserverisspecificallyoptimizedtodoitsowntaskquickly,sotheyallworkwelltogether.ClientRequestServerResponseFigure1.3Client-serversystem. Chapter1•MySQLandDatabases7pageScriptRequestLanguageSQLWebWebScriptDBMSBrowserServerProcessorHTMLPageHTMLTextFigure1.4Client-serverrequests.StructuredQueryLanguageAsdatabasesbecamemoreprominent,somestandardmethodofobtaininginformationfromthemwasrequired.ItdoesnotreallymatterhowtheDBMSstoresdataorcontrolsaccesstoit,aslongasyougettheinformationthatyouwantfromitbackinanacceptableway.Soastandardwayofcommunicatingwithdatabaseserverswasneeded.ThestandardthatmostmoderndatabaseswerebuilttosupportwasANSISQL92.ANSIistheAmericanNationalStandardsInstitutewhichsetsthestandardsforthecomputerindustry.Unsurprisingly,basedonthename,thestandardwasagreedin1992.Itistributetotheworkofthisorganizationthatthestandardhasremainedcurrentforsuchalongtime.Onlyrelativelyrecentlyhavenewstandardsbeenagreed.SQL-99orSQL:1999camealongnextandissometimesknownasSQL-3.ThereisalsoanewstandardcalledSQL:2003orSQL:200nwhichisunderdevelopment.MostmanualsonSQLserverscomparetheirproductswiththeSQL92standard.IwillnotattempttodothiswithMySQL,asthiscomparisoncanbefoundintheMySQLonlinemanual.However,someoftheMySQLcommandsareinadditiontothebasicSQL92com-mands,andmaynotbeavailableonotherproducts.Also,becauseMySQLisanongoingproject,someSQL92featuresarenotimplemented.AlotoftheSQL92commandsarestillpresent,butthecodetomakethemworkhasyettobedeveloped.Youneednotworryaboutthistoomuchthough,andyoucanstillusethisbooktolearnaboutSQLingeneralasmostofthecommandsarestandardacrossallSQLplatforms.Onceyouhaveworkedthroughthisbook,youshouldbeabletotransferyourknowledgeofSQLtootherDBMSquickly.QueriesThebasicwayofcommunicatingwiththeSQLinterpreteriswithaquery.AqueryisapieceoftextthatgetssenttotheDBMSwhichcontainsinstructionsthattheservercanprocess.Thequerycancontaincommandstoasktheserverfordata,todeletedata,tocreatenewdataandnewmeta-data.AlmostallcommunicationwiththeDBMSisdonethroughanSQLquery.Theonlyrealexceptiontothisiswhenthedatabaseserverisinsuchdifficultythatitwillnotfunctioncorrectly,inwhichcaseothertoolsmaybeneededtorepairthedatabase.Thefollowingisabasicquery:SELECT*FROMlog; 8DatabaseDesignManual:usingMySQLforWindowsFigure1.5Anoutputfromaquery.Thequeryismadeupofaseriesofwordsandvaluesterminatedwithasemicolon.ThesemicolonalertstheDBMSthatithascometotheendofthequery.Youcannormallysendmultiplequeriestothedatabaseserverbyseparatingeachonewithasemicolon.InthebasiccommandlineMySQLclient,thequeryisonlyexecutedoncethesemicolonisentered.ThisquerywhensenttotheDBMS,willcauseittoexaminethelogtable,workoutalloftherowsandcolumnsinthattable,andoutputthemtotheuser,asshowninFigure1.5.Figure1.5alsoshowstheoutputfromaquery.Youwillnoticethattheserverhasneatlytabulatedtheoutputintocolumnsandrowsforus,andevengivenasummaryofhowlongthequerytookandhowmanyrowsitreturned.Theoutputinthefigureispurelytextbased,asmostofthecommunicationbetweenclientandserverisinthisform.Youcanobtaingraphicalclients,however,whichgiveyoumorecontrolovertheoutput.Whereapplicable,mostoftheexampleswithinthisbookwillbeusingagraphicalclient. InstallingandTestingMySQL2ObtainingMySQLThefilesthatyouneedtoruntheMySQLserveronyourWindowsmachineareavailablefordownloadfromthefollowingaddress:http://www.mysql.com/downloadsThefilesthatyouneedtodownloadarethecurrentproductionreleaseofMySQL(thisbookusesversion4.1)andtheMySQLGUI.Althoughnolongerunderdevelopment,weusetheMySQLGUIasitisaverysimpleclienttouse,andyoucanuseittodisplayyourquerieswithlittlecomplexity.TheoutputfromMySQLGUIissimpleandclearandweuseitformostofthefiguresinthisbook.InstallingtheMySQLServerYoushouldnowhaveazipfileoftheMySQLserveronyourharddrive.Youneedtounzipthisfiletoatemporarydirectoryandrunthesetupprogram.DependingontheversionofMySQLyouareinstalling,youwillbepresentedwithastandardsetupprogramsimilartothatshowninFigure2.1.Onthefollowingscreens,youcanleavemostoftheoptionsattheirdefault.Oneofnoteistheinstallationdirectory,whichisbesttokeepas:C:mysqlAschangingthedefaultdirectoryrequiresotherchanges,itisrecommendedthatyoulettheprograminstalltothedefaultdirectorytolimitconfigurationerrorsinlateruse.ThisbookhasbeenwrittenwiththeTypicalinstallationselected.InstallingtheGraphicalClientToolTheMySQLGUIgraphicaltoolsaredistributedinazipfileinthesamewayasthemainserver.UnziptheMySQLGUIfiletoatemporarydirectory.Theclienttoolsdonothavea9 10DatabaseDesignManual:usingMySQLforWindowsFigure2.1BeginningMySQLserversetup.setupprogramlikethemainserver.Allyouneedtodotorunitistofindthemysqlgui.exefileanddoubleclickwhenyouneedtorunit.Wecannowbeginexploringourinstallation.MySQLDirectoryStructureFigure2.2showsthedirectorystructurethattheMySQLsetupinstalls.Figure2.2TheMySQLserverdirectorystructure. Chapter2•InstallingandTestingMySQL11Mostofthesedirectoriesareconcernedwiththefunctionoftheserverandcanbeignoredunlessyouspecificallyneedtousethem.Thetwodirectoriesofinterest,however,arethe:•Docsdirectory,andthe•bindirectory.TheDocsdirectorycontainsthemanualandotherhelpfilesforusingtheserver,andthebindirectorycontainstheexecutablefilesthatyouusetostartandadministertheserver.Wewillnowexplorethesedirectories.TheOnlineMySQLManualIfyouneedquickonlinereferenceaboutMySQLyoucanusethemanualfilesthatareincludedintheinstallation.Bydefaulttheseareinstalledat:C:mysqlDocsThefilesofnoteinthisdirectoryare:•manual_toc.html•manual.htmlThemanual_toc.htmlfileisawebdocumentthatindexesthemainmanual.htmlfile.Searchingthisusuallyprovidestheanswerthatyouarelookingfor.Ifnot,trysearchingtheactualmanual.htmlfileitself.WhenthisbookreferstotheonlineMySQLmanual,itisreferringtothesefiles.StartingtheMySQLServiceThedefaultthedirectorywhereallofMySQL’sexecutablefilesarestoredisC:mysqlbinTostarttheMySQLservice,doubleclickonthewinmysqladmin.exefile.MomentarilythescreenshowninFigure2.3willappear.WhenevertheMySQLserverisstartedonyourPCthisscreenwillappearforafewmomentsandthenhideitself.Wewillreturntothisscreenlater.ThefirsttimeitisruntheMySQLservicewillpromptyouforsomeusercredentialsfortheadministrativeuser.Weneedtoprovideausernameandpasswordsousethefol-lowing:User:rootPassword:sasquatch 12DatabaseDesignManual:usingMySQLforWindowsFigure2.3ThefirstMySQLadminscreen.IfyouarenotfamiliarwithLinux,rootisthestandardadministratoraccountonaLinuxsystem.Youcanuseothercredentialshere,butthroughouttherestofthesectionwewillassumethatyouhaveusedtheabove.TheinformationthatyouhavetypedwillbestoredintheMySQLstart-upfilesoyouwillnothavetoprovidethisinformationinthiswayagainunlessyoureinstalltheserverorotherwiseremovethestart-upfile.DependingonyouroperatingsystemandtheversionofMySQLthatyouhaveused,theprecedingprocessalsoinstallsashortcuttoWinMySQLadmin.exeinyourstart-upgroup.ThisensuresthattheMySQLserverstartsupeverytimeyourestartyourmachine.Youcanalsoregistermysqlasawin32serviceoncertainoperatingsystems.Thisisdonebyrunningthefollowingatthedosprompt:c:mysqlbinmysqld-nt—installToverifythattheserverisrunning,MySQLputsaniconinthesystemtray.Theiconresemblesatrafficcontrol,asshowninFigure2.4.Whentheserverisactivethetelltaleshowsagreenlight.Whentheserverisloadedbutnotrunningaredlightwillshow.Whenyourunyourmouseoverthetelltaleyouarepresentedwithacontextmenu,asshowninFigure2.5.TheShowmeoptionwillredisplaythescreenshowninFigure2.3andistheonlywaythatyoucangetbacktothisscreenwithoutstartinganotherinstanceoftheMySQLserver.Oneotheroptionwillbeavailabletoyou,whichwillchangedependingonyouroperat-ingsystemandprovidesameansofstartingandstoppingtheservice. Chapter2•InstallingandTestingMySQL13Figure2.4TheMySQLsystemtraytelltaleicon.Figure2.5TheMySQLtelltalecontextmenu.MySQLConfigurationFilesBeforewetesttheservice,wewilluseWinMySQLadmintoviewtheconfigurationfilethatitsetupforusintheprevioussection.RunyourmouseovertheiconinthesystemtrayasinFigure2.4andselectShowme.TheWinMySQLadminformwillappearagain.TheformshowninFigure2.3hasaseriesoftabsthatgiveyouaccesstovarioussettingsandinformationaboutyourMySQLsystem.Clickonthemy.iniSetuptab.Figure2.6showsthestartupfilethatwascreatedforusearlier.ItisusuallystoredintheC:windowsdirec-toryifyouhavechosenastandardinstallation.AnychangesyoumaketothisfilecanbesavedbyclickingtheSaveModificationbuttonaftermakingthechanges.Noticehowtheinifilecontainsvariousparameters,suchasthelocationofthedatastore,theIPaddressoftheserver,andattheendofthefile,theusernameandpasswordthatyoujustentered.Aseverythingisnowsetupandrunning,weneedtotestthatwecanconnecttotheserv-ice.ConnectingtotheMySQLServiceWewillstartbyconnectingtotheserviceinthemostbasicway,viathecommandlineinter-face.StartacommandboxbyselectingitfromtheStartmenuorselectingStart/Runandtypingcommand.com.Onceyouhavethisrunning,type:cdmysqlbintogetyouintothedirectorywiththeMySQLexecutables.FirstwehavetoruntheMySQLcommandinterpreter.Dothisbytyping:mysql 14DatabaseDesignManual:usingMySQLforWindowsFigure2.6Viewingthemy.inifile.YoushouldnowbebeginningtoseetheresultsasshowninFigure2.7.ThenexttaskistoconnecttoadatabasewithinMySQL.MySQLhassomesystemdatabasesbuiltin,sowewilluseoneofthesefornow.Type:connectmysqlYoushouldnowbeconnectedtothedatabase,andallthatremainsistohavealookatoneofthetableswithinthedatabase.Again,therearesomebuiltin,andasafinaltestwewilllookattheusertable.Type:describeuser;Ifyouhavenotforgottentotypethesemicolonattheend,youwillseethetableinFigure2.7.DESCRIBEandatablenamewillgenerateadescriptionofeachofthecolumnsinthetable.ThetesthasworkedandyouhavearunningMySQLserver!TocomeoutoftheMySQLinterpreter,type:exitAlthoughthisstopstheinterpreter,theservicewillstillberunning,waitingforthenextconnection. Chapter2•InstallingandTestingMySQL15Figure2.7TestingtheMySQLconnectionbycommandline.ConnectingwiththeGraphicalToolThefinaltestofyoursystemwillbetoconnecttotheMySQLserverwiththegraphicaltoolthatweinstalledearlier.WedothisbyselectingRunfromtheStartmenu,andtyping:c:mysqlbinwinmysqlgui.exeTheaboveassumesthatthisiswhereyoucopiedthefiletoafterunzippingit.Ifyoucre-atedashortcuttothefileonyourdesktopyoucanjustrunitbydoubleclickingonthatinstead.Youwillseeasplashscreenfortheproductcloselyfollowedbyapassworddialogue.Thegraphicalclientassumesthatyouhavealreadysetuptheusernametologontotheserver,andsoonlypromptsyouforthepassword.Aswehavenotdonethisyetnothingthatwetypeintothepasswordboxwilllogusonatthemoment,sojustpresstheOKbuttontobypassthisprompt.Figure2.8showsthemainscreenoftheclient.Yourversionwillshowanerrormessageatthebottomasyouhavenotyetconnectedtotheserver.Tofinishconfiguringtheclient, 16DatabaseDesignManual:usingMySQLforWindowsFigure2.8WinMySQLGUImainscreen.clickontheOptionsbuttonandthenontheClienttab.Youwillseeafieldonthisscreencalledusername.Type:rootintothisboxandclicktheSavebutton.FromtheManagemenu,selectthefirstoption,Connect,tobringthepasswordboxupagain.Youwillnowbeabletologinusingthepass-word:sasquatchthatwedefinedearlier.ThescreenshouldnowlookexactlythesameasFigure2.8.Youshouldnowbeconnectedtothedatabase.Toverifythis,wewillrepeattheexercisethatwetestedthecommandlineinterfacewith.Inthefirsttextfieldonthescreen,type:describeuser;Unlikethecommandinterface,purelypressingReturnattheendofthiscommanddoesnotmakeitrun.ToexecutethecommandyouneedtopresstheQuerybuttononthebuttonbar.Pressthisnow.YouwillberewardedwiththeverycolourfulscreenshowninFigure2.9.IfyoucompareFigure2.7withtheoutputshowninFigure2.9youwillseethatthisisdisplayingthesameresults,butinaclearermanner. Chapter2•InstallingandTestingMySQL17Figure2.9Theoutputofthetestquery.MySQLGUIQuickTourThroughoutthisbookwewilljustusetheMySQLGUIasawindows-basedclienttoissueSQLcommandsandviewtheresults.MostoutputfromtheMySQLserverisintabularformandwillbesimilartothatdisplayedinFigure2.9.Ifthereisnotabularoutputsuchaserrormessages,thisappearsonthemainGUIscreenalongwithstatusmessagessuchasthenumberofrowsaffectedbyanupdatequery.Figure2.10showssomeotherusefulpartsofthegraphicalclient.TousetheMySQLgraphicalclient:•SelecttherequireddatabaseusingtheDatabaseSelector.Thiswillonlycontainthesys-tem-generatedmysqldatabaseuntilyoucreateyourown.Youcanalsosetthedefaultdatabasethattheclientstartsinintheoptionsmenu.•TypethequerythatyouwishtoexecuteintotheQueryWindow.Youonlyneedtotypeasemicolonifyouarerunningmorethanonequeryatonce.•Oncetyped,clickontheExecuteQuerybuttontorunthequery.•Ifthequerydoesnottriggeranotheroutputbox,checkthetextintheStatusWindow.Ifyouhaveasyntaxerrororotherproblemthatstopssuccessfulexecutionitwillbeexplainedhere. 18DatabaseDesignManual:usingMySQLforWindowsFigure2.10Theoutputofthetestquery.•IfyourqueryhassuccessfullyexecuteditwillbestoredintheQueryHistorywindow.Clickingonaqueryinthehistorywillcopyitbacktothequerywindowsoyoucanexe-cuteitagain.Sometimesyouhavetoclickonseveralofthequeriesbeforeitcopiesittothemainwindow.Bydefault,theclientwilltrytoconnecttoaMySQLserverrunningonthelocalmachine,localhost.Ifyouareconnectingtoaserverthatisnotonthesamemachineastheclientthenyoucansetthisupbyclickingtheoptionsbuttonandreplacinglocalhostwiththehostnameofthemachinewiththeserverinstalled.Youwillneedtoensurethattheusernamethatyouareusinghasaccessrightsfromyourmachine,whichyoucanreadaboutinChapter12. HowShallWeStoreit?–Datatypes3TheDatatypeAlldatabasesstore“chunks”ofinformation.Themostefficientwayofstoringthosechunksistogroupthemintosimilarchunksthatcanallbestoredinasimilarway.Thischapterwilldescribesomeofthewaysthatyoucanstoredata.Youwillneedthesemethodstodefineyourtablessothatyoucanbegintostoredatawithinthem.Iwilltrytoprovidesomeexamplesinthischapter,butasyouneedinformationthatyouwillgetlaterinthebooktofullyunderstandwhatdatatypesdo,practicalexamplesherearedifficult.Ifyoudonotunderstandsomeofthischaptercomebacktoitafteryouhaverunafewqueriesandcreatedafewtables,anditshouldallmakesense.Whenyoucreateatable,aswellasdecidingwhatyouaregoingtocalldifferentgroupsofdata,youalsoneedtodefinethetypeofdatathatyouaregoingtostorewithinthatgroup.Thedatatype(sometimesjustcalledatype)isusedtodefinethedatathatisbeingstoredwithinthegroup.Italsodeterminesthesortofactionsthatyoucandowiththattypeofdata.Therearemanydatatypesinprogramminglanguages,soifyouhaveprogrammedbeforeyouprobablywillonlyneedacursoryexaminationofthischapter(orthetableswithin)tofamiliarizeyourselfwithMySQLdatatypes.IfyouarenewtoprogrammingandMySQL,thengivethischaptersomethought,asstoringthedatainthecorrectwaywillsaveyouhoursofre-designandcodinglateroninyourdatabaseproject.Thischapterwilldescribefoursetsofdatatypes:•numeric•character•dateand•othertypes.Thefirstthreearethebasictypesthatmostdatacanbegroupedinto.MySQLbreaksthesethreetypesdownintofurtherdatatypes.Wewillalsodescribesomeothertypesthathavespecialfunctions.Whenyoucreateatabletostoredata,whenyounameeachcolumnyoualsospecifythedatatypeforthatcolumn.Youspecifythedatatypeusingthekeywordsthatweareabouttodescribe.Thischapterwilljustreviewthesetypes;someexamplesoftheirusewillbeshownwhenweactuallycreatesometablesinthefollowingchapters.19 20DatabaseDesignManual:usingMySQLforWindowsNumericTypesAnumericdatatypeisusedtostorenumbers.Therearenormallyspecificfunctionsthatyoucanperformonnumbersandonmostnumericdatatypes.IntegersAnintegerisanexactwholenumber.Forexample,thenumbers1to10areallintegers.IfwearecreatinguniqueIDsforrowsinourdatabase,theIDfieldisusuallyaninteger,whichstartsat1andgetsbiggerbyoneforeachnewrowadded.Ifyouadd,subtractandmultiplyintegersyouwillgetanintegerasananswer.Integersnormallyrangefromanegativenumber,throughzerotoapositivenumber.Thereareseveraldatatypesthatareusedtostoreintegers,dependingontherange;thegreatertherange,thelargertheamountofstoragethatittakestokeeptheintegerinatable.Table3.1,adaptedfromtheonlineMySQLmanual,showstheapproximaterangethateachoftheintegerdatatypescanrepresent.Table3.1Integerdatatyperanges.DatatypeFromToStorageinbytesperrowTINYINT–128+1271SMALLINT–32thousand+32thousand2MEDIUMINT–8.3million+8.3million3INT–2billion(1012)+2billion4BIGINT–9trillion(1018)+9trillion8IfyoulookatTable3.1youcanseethataswemovedownthetable,therangeofnumbersbecomesvast,withlittlechangeintheamountofstoragethatisrequired.Ifyouareusinganintegertostoreaprimarykey,thestandardINTtypewillprobablysufficeifyouarenotexpectingtostoremorethanapproximately2000000000000rows!Tousethesetypeswhendeclaringadatatype,theyjustfollowthenameofthecolumn,forexample:columnnameTINYINTor:columnnameBIGINTFormattingIntegersByspecifyinganumberinbracketsafteranintegerdatatype,youcanformatthewaythatthattypeisreturnedinqueries.Forexample:columnnameINT(10) Chapter3•HowShallWeStoreit?–Datatypes21willspecifythenumberstoredpaddedwithupto10leadingspaces.Soifyouweretostorethenumber42inanINT(10)datatype,thefollowingwouldbereturned:••••••••42Each•intheaboverepresentsaspace.Thisisusefulwhenyouareoutputtingyournum-bersincolumns,asitwillmakeallofthecolumnlineupneatly(unlessthenumberisbig-gerinthiscasethan999999999).Asimilarkeyword,ZEROFILLwhenappendedtotheendoftheintegerdeclarationwillmakethereturnednumberbepaddedoutwithzerosinsteadofspaces,sothistime,thetypedeclaration:columnnameINT(10)ZEROFILLwillproducethefollowingwhen42isretrievedfromit:0000000042Thesetwovariationsofthedatatypedonotactuallyalterthenumberthatisstoredwithinthetable;itjustchangestheformattingofthenumberwhenitisretrieved.UNSIGNEDYouwillrememberfromTable3.1thattheintegerdatatypesrangedfromanegativenum-bertoapositiveone.IfyouusethekeywordUNSIGNEDafterdeclaringaninteger,thecol-umndefinedwillonlystorenumbersaspositiveintegers.TherangethengoesfromzerothroughtodoublethevaluesintheTocolumnofTable3.1.Forinstance,inthefollowingdeclaration:columnnameBIGINTUNSIGNEDnumbersstoredcanrangefrom0to18trillion.ThisisfineforusingintegersasuniqueIDsforrows,butifyoustartdoingarithmeticwithsignedandunsigneddatatypesitcanleadtoproblems.ConsulttheonlineMySQLdocumentationtofindoutmoreaboutthisbehav-iour.BOOLABooleanvalueisonethatcanhavetwostates,trueorfalse,onoroff,0or1.Becauseitissuchasimpledefinition,youonlyneedtouseonebittostoreit,asasinglebitcanbeeitheronoroff.InMySQL,youmightexpectBOOLtocorrespondtoaBooleanvalue,thatisavalueof0or1(onoroff.)AlthoughBOOLcanbeusedtostorethesevalues,itactuallyisthesameasusingtheTINYINT(1)datatype.Ifyoustoreanumberbiggerthan1inaBOOL,thenthatnumberisstoredaslongasitisnotlargerthan127.Ifitisbiggerthan127,then127isstored.IfyouareexpectingthisdatatypetobeapureBoolean,itisnot.IfyoureferbacktoTable3.1youwillseethatTINYINTtakesuponebytetostore.Abyteis8bits,sousingTINYINTtakeseighttimesmorestoragethanifBOOLwasimplementedcorrectly. 22DatabaseDesignManual:usingMySQLforWindowsYoucanusethedatatypeBITinterchangeablywithBOOL,theyareboththetypejustdescribed.IfyouwanttostoreBooleanvaluesitisbestjusttouseTINYINT,andthenwithinyourcodedeterminewhatnumberyouneedtostoreforonoroff,0or1.BearinmindthatifyouaretransferringyourMySQLtablestoanothersystemthatimplementsBOOLinadifferentway,thescriptsthatyouneedtowritetoexportthedatawillneedtocreatetheactionsnecessarytoreflectthatdifference.FurtherdevelopmentofMySQLmaywelladdressthisdifferenceinabetterway.DECIMALTheDECIMALdatatypeisusedforstoringnumbersthatarenotwholenumbers,wherethenumbersafterthedecimalpointareimportant.Forinstance,retailersoftenadvertisepricesthatareacoupleofcentsorpennieslessthanthedollarorpound,forexample,thedish-washeris$699.95,thecomputeris£599.99.Althoughtheseitemscostthebestpartof$700and£600respectively,thefractionalpartofthenumberisimportant.Ifyoustoredthedish-washerpriceasaninteger,roundingdown,andsold1000oftheseonyourwebsite,youraccountswouldbeoutby$950.Ifyouroundedthepriceupto$700,youstillwouldhaveadiscrepancyof$50.Themorethatyoumultiplythesenumber,themoretheroundingerrorcosts(ormakes!)you.Usinganintegeristhereforenotrecommendedforstoringnumberswherethedecimalprecisionisimportant,especiallycurrency.Anintegerwouldbefineforstoringthenumberofdishwashersthatyouhaveinstock,butnottheirprices.MySQLprovidestheDECIMALdatatypetoenableyoutostoreprecisionfractionsofnumbers.YoudeclaretheDECIMALdatatypeasfollows:columnnameDECIMAL(precision,decimals)•precisionisthenumberofdigitsthatareneededtostorethecompletenumber.Ifyouarestoringnegativenumberstheminussignisincludedinthesedigits.Ifyoudonotstoreanegativenumber,apositivenumbercanusethisspaceandsothepositivenum-bercanbeafactorof10greater.•decimalsisthenumberofdigitstostoreafterthedecimalpoint.Theprecisionpartofthisisespeciallyconfusing,soTable3.2showssomeexamplesofthedeclarationandofwhathappenswhenyouputdifferentnumbersintoacolumncreatedwiththatdeclaration.Table3.2Decimaldatatypeexamples.DeclarationNumberinsertedNumberstoredDECIMAL(5,2)3.141593.14DECIMAL(5,2)4242.00DECIMAL(5,2)–23453.543–999.99DECIMAL(5,2)4932.324932.32DECIMAL(5,2)493212343.34239999.99DECIMAL(10,5)49321234532.3423999999.99999 Chapter3•HowShallWeStoreit?–Datatypes23Thewaythatyoudefineadecimaldatatypemaymakemoresenseifyourealizethatthenumberisstoredasastring.Ifyouarenotawareofwhatastringis,wewillgettothatinthenextsection.Thedecimaldeclarationdefinesacertainstringlength,andallnumbersthatarestoredhavetoconformwithinthatlength,hencetheroundingissueswhenyouputinanumberthatfallsoutofrangeintooneofthesetypes.ThisallmayseemverycomplicatedbutDECIMAListheonlywaythatyoucanaccu-ratelystoreanumberwithafractionalpart.Youprobablywillonlyuseittostorecurrency,butitisthebestwayofdoingthis.FloatingPointNumbersThedatatypesthathavepreviouslybeenmentionedareforstoringexactvalues,eitherexactwholenumbersorexactwholeandfractionsofnumbers.Floatingpointnumbersenableyoutostoreaverywiderangeofnumbersbutwithsomelevelsofinaccuracy.Thebiggerthenumbergets,thelessdetailisstoredaboutit.Thewholetopicofstoringanddealingwithfloatingpointnumbersisalargeonewhichthisbookwillnotdealwith.IneedtowarnyounowthateventhoughIwillnotbegoingintogreatdetailinthefol-lowingsections,theycangetabitcomplex.IwouldnotwantyoutogetsoconfusedwiththissectionthatyouthinkthatMySQLitselfistoohardtolearn–itisnot–butthesedatatypesare!Ifyoudonotintendtostoreverylargenumbersinyourdatabasesyoumightjustjumpaheadtothecharactertypes,rememberingthatthissectionishereshouldyoueverneedtorefertoitinamomentofcouragelateron!FLOATMySQLsupportsfloatingpointnumberswiththeFLOATdatatype.Youcanspecifythepre-cisionofafloatingpointnumberasfollows:columnnameFLOAT(precision)precisioninthisinstanceisthenumberofdigitsthataretobestored.Ifyoustoreupto24youaredeclaringasingleprecisionnumberandbetween25to53itbecomesadoubleprecisionnumber.YoucanalsospecifyaFLOATwithtwoattributesasfollows:columnnameFLOAT(magnitude,decimals)Withthistypeofdeclarationthefloatisforcedtobeofthesingleprecisiontype.Theattributesareasfollows:•magnitudeisthenumberofdigitsusedtostorethenumber.•decimalsisthenumberofdecimaldigitstobestored.Table3.3showssomeexamplesofnumbersthathavebeenstoredusingtheFLOAT(5,2)declaration.Youwillnoticethatitstores3.14159andthesmallernumbersaccurately,withonlythenumberofdecimalplacesbeingroundedwherespecified.However,lookatthelargernumbersatthebottomofthetable;asthesehavegoneoutoftheprecisionrange 24DatabaseDesignManual:usingMySQLforWindowsTable3.3Singleprecisionfloatsdatatyperanges.DeclarationNumberinsertedNumberstoredFLOAT(5,2)3.141593.14FLOAT(5,2)4242.00FLOAT(5,2)–23453.543–23453.54FLOAT(5,2)4932.324932.32FLOAT(5,2)493212343.3423493212352.00FLOAT(5,2)49321234532.3423493212344432.00specified,thedecimalplaceshavejustbeensettozero,andthelesssignificantpartofthenumbersstoredisnotthesameasthenumberthatwasentered.Theideabehindthissortofdatatypeisthatwhenhandlingverylargenumbers,themagnitudeofthenumberismoreimportantthanthefinedetailsofthenumber.Forinstanceifyouhad$10000026.51,inthebank,the$10millionwouldbeofmoreimpor-tancetoyouthanthe$26.51!DOUBLEAnotherfloatingpointdatatypeisDOUBLE.ThisworksinexactlythesamewayastheFLOATdeclarationabovewhenusedwiththetwoattributesasfollows:columnnameDOUBLE(magnitude,decimals)Thistimethenumberstoredwillobviouslybeofthedoubleprecisiontype,buttheattributesarethesameasfollows:•magnitudeisthenumberofdigitsusedtostorethenumber.•decimalsisthenumberofdecimaldigitstobestored.InotherSQLtextsyoumayseereferencestoREALandDOUBLEPRECISIONdatatypes.YoucanusetheseaswellinMySQLbuttheyareexactlythesameastheDOUBLEdatatype.CharacterTypesThecharacterdatatypesarethoseusedforstoringcharactersandstrings.Thelettersinthissentenceareallcharacters,andthesentenceitselfisastring.InfactasIamsittingheretyp-ingthis,MicrosoftWordisprobablythinkingthatthiswholebookisjustastringofchar-acters,allbeitabigone.Whenspecifyingastring,younormallysurrounditwithsomething,suchasquotemarks,tohighlighttotheMySQLserverthatwhatisinsidethequotesshouldbetreateddifferentlytowhatisoutsideofthem.Thisissomethingthatweareusedtoinwrittentext,forinstance:Gandalfspluttered,“Fly,youfools!” Chapter3•HowShallWeStoreit?–Datatypes25Intheabove,thenarrativeisjustinstraightforwardtext,butthewordsthatthecharacterisspeakingissurroundedwithquotemarks,sowecantellthedifferencebetweenthespokenwordandthestory.MySQLneedstotellthedifferencebetweenstringsandthemainsyn-taxofqueriessothatitdoesnotthinkthatthereareerrorsinthequery.CHARTheCHARdatatypeisusedtodefineastringoffixedlengthasfollows:columnnameCHAR(length)lengthisthewidthofthestringincharacters,whichcanbebetween0and253.OnceyoudeclareacolumnwithaCHARvalueoffixedlength,allofthestringsthatarestoredinthatcolumnbecomethatlength.Forexample,supposeweusedthefollowingasacolumndeclaration:columnnameCHAR(10)Ifweweretostorethestring“Hello,World”inacolumncreatedliketheoneabove,thesystemwouldonlystore“Hello,Wor”,thefirst10charactersofthestring.Alloftheremain-ingcharactersarelost,andnoerrormessageisgiven.Ifyouarestoringastringlessthan10characterslong,forinstance,“Hello!”,thenMySQLwillpadoutthestringwithspacestomakeituptothe10charactersfixedlength.Sothiswillbestoredas:Hello!••••whereeach•intheaboverepresentsaspace.ThiscanbeanissueifyouaresearchingCHARcolumns.Forinstance,ifyouaresearchingfortheword“Hello!”inaCHAR(10)col-umn,nothingwouldbefound,insteadyouhavetosearchfor“Hello!••••”whichiswhatthedatatypeactuallystores.Rememberthateachstringhastobethelengththatthecolumndeclarationrequires.Wewillnotdealwiththisnow,butinChapter6thereisawaytosearchforjustthestartofastring,whichwouldallowyoutofindastringoflessthanthedefinedlengthofthecolumn.VARCHARTheCHARdatatypedescribedaboveonlyallowsfixedlengthstrings.Asyouwillhaveseen,thiscancauseunexpectedresultsinsearchingifyouforgettopadoutthesearchtermtotherequiredlengthofthecolumn.TheVARCHARdatatypegetsaroundthisproblembyallow-ingavariablelengthofstringupto255characters.YoudefineaVARCHARcolumnasfol-lows:columnnameVARCHAR(length)lengthisthemaximumwidthofthestringincharacters,whichcanbebetween0and253.Again,ifweweretoinsert“Hello,World!”intothisdatatypewhendefinedwithalengthof10,wewouldget“Hello,Wor”stored.However,ifweinsertedthestring“Hello!”intoit,it 26DatabaseDesignManual:usingMySQLforWindowswouldstore“Hello!”withnoextraspaces.Searchingthecolumnfor“Hello!”wouldfindthestringwithnofurthermanipulationneeded.FixedorVariableLength?Althoughvariablelengthdatatypesasmentionedabovemakeiteasierforsearching,usingfixedlengthtypeshavetheadvantagethattheyarefastertosearch.Whenyouaredesign-ingyourtables,ifyouwantthesearchestobefasteryoushouldconsiderusingfixedlengthtypes.Variablelengthtypesalsocanproducetheneedtoperformmaintenanceonthetableperiodically.Ifyoumakelotsofchangestoatablewithvariablelengthrows,overtimethetablewillbecomefragmentedduetoentriesandspacebecomingde-allocatedandre-allocated.MySQLprovidessomecommandstoremedythiswhichyoucanreadaboutinChapter13onOptimizingMySQL.Youshouldconsidertheseissueswhenchoosingthedatatypesforyourcolumns.StoringTextCHARandVARCHARaregreatforstoringstringslikepeople’snamesandaddresses,buttheyhavethelimitthatthestringcanbeamaximumlengthof255characters.Ifwearestoringthingslikethecommentsofausertoawebsite,orabookreview,orotherlargeblocksoftextthenthislimitisprohibitive.TheTEXTdatatypegetsaroundthislimitbydefiningastringthathasamuchbiggerlimit.ATEXTcolumncancontainstringsthathaveamaximumof65535characters.Thisshouldcovermostthingsthatawebpageusershouldtypeintoawebpage!Ifwetaketheaveragewordtobe5characterslong,thatisstillover13000words.YoudefineaTEXTcolumnasfollows:columnnameTEXTItiseasytobetemptedtodefineallcolumnsthataregoingtocontainstringsasTEXT.Althoughthiswillstopyoufromrunningoutofspacewhenstoringastring,itdoesplacemuchmorestrainontheMySQLenginewhensortingthroughlotsofTEXTcolumns.ItismuchquickertosearchthroughcolumnsofCHARandVARCHARforsmallstrings,becauseofthewaythatMySQLstorestheTEXTdatatype.Ifyouwantafastdatabaseserver,gettingthedatatypescorrectisimportant.MySQLalsosupportsthreeothertypesofTEXTdatatype:TINYTEXT,MEDIUMTEXTandLONGTEXT.Table3.4showstheattributesofalloftheTEXTdatatypes.Table3.4Textdatatypes.DeclarationMaximumlengthofstringincharacterscolumnnameTINYTEXT255columnnameTEXT65535columnnameMEDIUMTEXT16777215columnnameLONGTEXT4294967295 Chapter3•HowShallWeStoreit?–Datatypes27YoucouldjustaboutstoretheentiretextofthisbookinoneMEDIUMTEXTdatabaserecord!BLOBDon’tworry,youarenotlosingyourmindfromlookingattoomanydatatypes!ABLOBinMySQLisaBinaryLargeObject,andisusedforstoringdata.Forinstance,youcouldstoreajpegimagewithinaBLOBcolumn.BLOBsconformtothesamesizeconstraintsastheTEXTcolumnsmentionedinTable3.4,butarecalledTINYBLOB,BLOB,MEDIUMBLOBandLONGBLOB.ABLOBcolumnisessentiallythesameasthecorrespondingTEXTcol-umn,butwhenyouaresearchingororderingittheBLOBwillbecasesensitiveandtheTEXTwillnotbe.BothBLOBandTEXTobjectsarereallypointerstoareasofstorageontheserver,andsoarenotphysicallystoredwithinthetable.Thisexplainswhyoperationsonthistypeofcol-umnareslowerthanwhensearchingnormalcolumntypes.DateTypesOneofthecommonestthingsthatyoustorewithintablesaredatesandtimes.Anexampleofthiswouldbeaweblog,whereeveryaccesstoawebsitewouldhavethedateandtimeofthataccessstoredwithinatextfileoralogtableinadatabase.DATETIMEDATETIMEisthedatetypethatIusethemost.Thisallowsyoutostorethedateandthetimeinonecolumn.YoudefineaDATETIMEcolumnsimplyasfollows:columnnameDATETIMEPleaserefertoChapter11onworkingwithdatesandtimesfordetailshowtoinsertval-uesintothiscolumntype.WhenyouretrieveaDATETIMEvalue,MySQLreturnsastringoftheformat“YYYY-MM-DDHH:MM:SS”irrespectiveofhowyouhaveinsertedthedate/timeinthefirstplace.IfyouonlyinsertthedatepartintosuchacolumntheHH:MM:SSaresetbydefaultto00:00:00.Ifyouinsertadatethatisinvalid,suchas40February2003,MySQLstoresthisdateas“0000-00-0000:00:00”toshowthattherehasbeenanerrorsomewhereinthedate.Youcanstorevaluesinthisdatatypeforalldatesandtimesbetweentheyear1000andtheyear9999.TIMESTAMPTIMESTAMPworksinasimilarwaytoDATETIME,inthatitstoresboththedateandtimeinasinglecolumn.However,theTIMESTAMPdatatypehastheaddedfunctionalitythatitwillautomaticallyupdateitselfundercertainconditions.Thisisusefulifyouwanttokeeparecordofwhenadatabaserowwascreatedorlastupdated.Wheneveryoucreateanew 28DatabaseDesignManual:usingMySQLforWindowsrow,orchangethecontentsofarow,anddonotexplicitlychangethecontentsofthefirstTIMESTAMPcolumninthatrow,thiscolumnwillbeautomaticallyupdatedwiththedateandtimeofthechange.If,however,youexplicitlysetthevalueofthecolumnwhenyouarealteringorcreatingtherow,theTIMESTAMPcolumnwillnotauto-updatebecauseyouhavespecifiedwhatyouwanttogoinitmanually.Thiscanbeaveryusefulcolumndatatypeasitwillupdateitselfwithouttheneedforyoutocreatecodethatwillupdateitforyou.YoudefineaTIMESTAMPcolumnwithavalue,asfollows:columnnameTIMESTAMP(length)lengthreferstothenumberofcharactersthatthecolumnwilloutputwhenretrievingitsvalue.Forinstance,ofweweretostore28February2003,01:50:59inaTIMESTAMP(14)column,wewouldobtainthefollowingifweweretoretrievethisvalueatalatertime:20030228015059Table3.5showstheothervaluesthatwouldbereturnedifweweretostorethesamedateintimestampcolumnsdeclaredwithotherlengths.Table3.5TIMESTAMPdatatypevalues.DeclarationDateTimereturnedcolumnnameTIMESTAMP(2)02(YY)columnnameTIMESTAMP(4)0202(YYMM)columnnameTIMESTAMP(6)020228(YYMMDD)columnnameTIMESTAMP(8)20020228(YYYYMMDD)columnnameTIMESTAMP(10)0202280150(YYMMDDHHMM)columnnameTIMESTAMP(12)020228015059(YYMMDDHHMMSS)columnnameTIMESTAMP(14)20020228015059(YYYYMMDDHHMMSS)ThelengthattributeintheTIMESTAMPdeclarationonlyaltersthesizeofthestringthatyougetbackwhenyouretrievethedata.ATIMESTAMProwtakesexactlythesamestoragespaceirrespectiveofthelengthattributespecifiedwhenyoudeclareit.DATETheDATEdatatypeisusedwhenyouareconcernedaboutstoringadatebutdonotneedtostorethetimeaswell.TheDATEdatatypeisdefinedwithnoattributesasfollows: Chapter3•HowShallWeStoreit?–Datatypes29columnnameDATEYoucaninsertadateintoadatecolumnusinganyofthemethodsdescribedinChapter11,howevertheDATEdatatypedoeshavealimitedrange.Datescanbestoredfrom1January1000throughto31December9999.Thatrangeshouldbeadequateformostofus!TIMETheTIMEdatatypeisusedtostoreatimevalueorperiod.Itcanrangefrom–838hoursto838hours.Thisenablesyounotonlytostorethetimeofday,butalsotostorealonglengthoftimeinthiscolumntype.ATIMEcolumnisdeclaredwithoutattributesasfollows:columnnameTIMENormallytheoutputofthetimecolumnwouldbe“HH:MM:SS”,howeverwhenyouarestoringanhourvalueofover99hourstheformatwillallowanextrahourdigittoallowthemeasurementofthislongertimeperiod.YEARTheYEARdatatypeallowsyoutostoreayearwithverylimitedstoragespace.Youspecifyayearcolumnbyusing:columnnameYEARTheYEARthatyoustoreinthistypeofcolumncanrangefrom1901to2153.ThesmallrangeisduetotheYEARbeingstoredinasinglebyte.Thebestwayofinsertingadateintothiscolumnisbyspecifyingastringwith4digits,forexample:195220102063Ifyouonlyusetwodigits,MySQLhastomakeadecisionaboutwhatyearyouarerefer-ringto.FormoreinformationabouthowitchoosesyoucanconsultthesectionintheonlineMySQLmanual.MySQLwillalwaysoutputthecontentsoftheYEARdatatypewith4digitstoavoidconfusion.OtherTypesAllofthepreviousdatatypeswillhavebeenusedtostoredatathatyouarefamiliarwith,likedates,timesandnumbers.MySQLalsosupportsothertypesthatyoumaybelessfamil-iarwith.Twoofthesearedescribedbelow. 30DatabaseDesignManual:usingMySQLforWindowsSETAsetisacollectionofthingsthatareunique,theorderofwhichisnotimportant.Forinstance,thefollowingisaset:•Spoon•Fork•KnifeThatsetisexactlythesameasthefollowingset:•Knife•Fork•SpoonItdoesnotmatteriftheelementsofthesetcontaintheitemsinadifferentorder;ifalloftheelementsarethesamethenthesetisthesame,irrespectiveoftheorder.However,thefollowingisnotasetasitcontainsaduplicate:•Knife•Spoon•Fork•KnifeTheSETdatatypeinMySQLisusedforstoringelementsofasetineachrowofthetable.Theelementsofthesetaredefinedatthetimethatthecolumnisdeclaredandarestoredasstringsinthedeclaration.Youcanhaveamaximumof64elementsinaMySQLset.Tostoreoccurrencesofthesetaboveyouwouldusethefollowing:columnnameSET(“Knife”,“Fork”,“Spoon”)Whatthisdatatypeactuallydoes,istoalloweachrowofthecolumntostorethefollow-ingvalues:•“”•“Knife”•“Fork”•“Fork,Knife”•“Spoon,Fork,Knife”•“Spoon”etc.Thefirstitemonthelistabovecontainsnoelements,itisreferredtoas“theemptyset”anditisstillavalidelementofthedefinedset.Ifyoutryandinsertastringthatisnotwithinthesetdefinition,thenthatisignored.Ifyouareaddingmorethanoneelementtoaset,thenyoushouldincludethemallinthesamestring,separatedbycommas.SETisusefulforstoringtheresponsesfromanonlinequestionnaireoranythingthatrequiresselectionsfromapredefinedlist.Thisisusefulasalthoughwhenyouretrieve Chapter3•HowShallWeStoreit?–Datatypes31entriesfromaSETcolumnyougetthemasstrings,theyareactuallystoredasnumericval-ues,thussavingstoragespaceinthedatabase.ENUMTheMySQLENUMdatatypeworksinasimilarwaytotheSETtypedescribedabove.TheENUMtypeallowseachrowofitscolumntohaveavaluechosenfromasetlistwhichyoudefineatcolumncreationtime.UnlikeaSET,whichcanhavemultipleelementsofthesetstoredineachrow,ENUMallowsonlyoneornoneatall.SETalsohasalimitof64elements,butENUMcanhaveamaximumof65535elements.YoudefineanENUMdatatypeasfol-lows:columnnameENUM(“Knife”,“Fork”,“Spoon”)YouwillnoticethatthisisverysimilartotheSETdeclaration.Eachoftherowsinacol-umndefinedasabovecouldthereforecontainonlyoneofthefollowing:•“”•“Knife”•“Fork”•“Spoon”Again,thisdoesnotactuallyinsertthestringintothecolumn,itinsertsanindexforthetextbasedontheorderthatthestringsweredeclaredwhenthecolumnwascreated.Soifyouweretoinsert“Fork”intoarow,thentheactualdatastoredwouldbe2,asthiswasthesecondelementthatwasinthelistwhenthecolumnwasdefined.However,ifyouretrievethedata,MySQLrealizesthatthecolumnisanENUMtypeandsogivesyouthestring“Fork”back.Thisdatatypeisespeciallyusefulifyouarestoringtheresponsesfromstaticdropdownlistsonwebpages.Aslongasthecontentsofthedropdownlistdoesnotchange,ENUMistheperfectwaytostorerecordsofwhathasbeenselected. Thispageintentionallyleftblank DesigningandCreatingTables4RedundantDataNomatterhowfastthedatabaseserveris,itcanonlyworkwellifitisprocessingefficientlyorganizeddata.Muchofthespeedandefficiencyofmoderndatabaseenginesreliesontheuseofwell-designedtablesthatarequicktoprocess.OneofthewaysinwhichmodernDBMShaveimprovedtheperformanceofdatabasesisbyeliminatingredundantdata.Redundantdataisdatathatisrepeatedunnecessarilywithinatable.Figure4.1showsatablethatstoresarecordofaccessestoawebsite.Theclienthostdetailshavebeenchangedforanonymity.LookateachcolumninFigure4.1.Everycolumnhasdatainitthatisrepeated.Forinstance,thewebpagecolumnhasonlythreeuniquepageslistedinit,andtheindex.phppageisrepeatedinthiscolumnfourtimes.Nowlookatthebrowsercolumn.NotonlyistheMozillaentryrepeatedthreetimesitisalsostoringalotoftextforeachrow.Althoughlotsofdataisrepeated,itisnotnecessarilyallredundant.Dataisclassedasredundantifitcanberemovedfromatablewithoutlossofinforma-tion.Forinstance,lookattheextractfromthistableshowninFigure4.2.WhenviewedinisolationfromthemaintableshowninFigure4.1,thedatashowninFigure4.2hasmoreobviousredundancies.Youwillnoticethatinthesixrowsshown,onlythreeoftheserowsareunique.Whenaremotemachineaccessesawebserver,itdoesthisviaitswebbrowsersoftware,usingitsIPnumberwhichismatched(usually)toitsdomainname.Whilethatbrowserislookingatpagesonyoursite,normallythesethreepiecesofinformationremainthesameforeachpagerequest.Onlythepagethattheyarelookingat,thereferringpageandthetimethattheymadetherequestwouldchange.Figure4.1Examplewebsitelog.33 34DatabaseDesignManual:usingMySQLforWindowsFigure4.2ExtractfromFigure4.1.ReducingRedundantDataYoumaythinkthatwiththesizeandspeedofmodernharddrivesthereislittlepointwor-ryingaboutstoringtoomuchtextforeachrow.Toanextentthatisright,howeveritisnotjustthesizeofthedatabasethatmaybecomeanissue.Thebiggerthedatabaseisthelongeritwilltaketoutilizethedata.Ifyouareimplementingapagecounteronyourwebpage,andthewholeofthelogdatabaseisgoingtobecountedeverytimeawebpageisviewed,havingahugelogtablemaywellslowthesitedownconsiderably.Eachtimeapageisviewed,thetablegetslarger,andsothesitegetsslower.Withtheexceptionofthedatecreatedcolumn,alloftheothercolumnscouldhaveredundantdataremoved.Takeforexamplethewebpagecolumn.Thiswillstorethenameofthewebpagethatthelogentryrefersto.Youwillnotethatweimmediatelyhaveadupli-cateentryinthefirsttworows.Everytimethattheindexpageofthewebsiteisviewed,thecurrenttablewillhavethetext“index.php”insertedintoanewrow.Tostoprepeatedlystoringredundantdata,weneedtomovethetextdescriptionofthewebpageintoanothertable,andfindsomewayofrelatingthetwoentriestoeachother.Figure4.3showswhatcouldbestoredinthesecondtable.ThePrimaryKeyIfyouexamineFigure4.3youwillseethatwehavecreatedanewtablewhichstoresinfor-mationaboutthewebpage.Wewillcallthisthewebpagetable.Itcontainsthenameofthepage,aswellasthetitle,butitalsocontainsarowofuniquenumbers,theIDcolumn.Thisisknownastheprimarykey.Eachrowinthewebpagetablecanbeidentifiedbyusingitskey.Forexample,ifweweretoseementionofID3ofthewebpagetable,wewouldknowthatitwasreferringtothelinks.phpscript,whichistheLinkspage.Thisuniquereferencetotherowinatablegreatlyreducesthechancesofredundantdatawithinyourdatabasetables.Figure4.4showsthewebsitelogtableagain,butthistimewehavechangedthefirstwebpagecolumn.Figure4.3Examplewebpagetable. Chapter4•DesigningandCreatingTables35Figure4.4Websitelogwithreducedredundantdata.ForeignKeysIntheexampleshowninFigure4.4,insteadofinsertingastringtodeterminethewebpageaswedidinFigure4.1,wehaveinsertedtheprimarykeyfromthewebpagetableinFigure4.3.Whenyouinsertaprimarykeyfromonetableintoanother,itisknownasafor-eignkey.Byusingtheforeignkeytolookuptherowinthewebpagetable,wecangetbackmoreinformationabouttherowweareexamininginthewebsitetable.Weareabletorecre-atealloftheinformation,withoutstoringanythingunnecessarily.Figure4.4stillhasredundantdata,though,sowewillfollowthisexamplethroughbycreatingacookietable.Thecookietablewillstorethehostinformationthatwegetfromthesitevisitor.ThistableisshowninFigure4.5.YoucanseenowthatFigure4.5containstheuniqueinformationthatwegotfromeachofthevisitorsthathaveviewedourwebpages,andeachofthesevisitorshasauniquenum-ber,whichwehaveconvenientlycalledthecookieID.Thisistheprimarykeyinthecookietable.Thescriptinglanguagethatweareusingcanusethisnumbertosetacookieonthevisitor’smachineaswellforfuturevisits.WecannowinsertthecookieIDasaforeignkeyintoourweblogtable,whichweshowagaininFigure4.6.YouwillnoticefromFigure4.6thatthetablenowcontainsmuchlessredundantdata.Westill,however,haveretainedtheabilitytorecreatethatdata,bylookinguptheforeignkeysintherelatedtables.Forinstance,thefirstrowofdatainFigure4.6showsthefollowinginformation:•Thedateandtimethepagewasviewed(datecreated–2002-01-0112:35:02).•Thepagethatlinkedtothispage(referringpage–index.php).•Thecookienumberoftheclientmachine(cookieID–1).•Thewebpagenumberthatwasviewed(webpage–2).Butbymatchingtheforeignkeysthatwehavestoredwiththerowsintheirparenttables,wecanalsoascertain:•Thebrowserthattheclientused(cookie.browser–Gulliver/1.3).Figure4.5Cookietable. 36DatabaseDesignManual:usingMySQLforWindowsFigure4.6Weblogwithevenlessredundantdata.•Theclient’shostname(cookie.hostname–hb42.noname.com).•Theclient’sIPnumber(cookie.ipnumber–192.168.200.29).•Thescriptnameofthepageviewed(webpage.page–visitor.php).•Thetitleofthepageviewed(webpage.title–VisitorBook).Werefertocolumnsinexternaltablesbygivingthetablenamefollowedbyaperiodandthenthecolumnname.Soyoucanseefromtheaboveexamplethatwehaveactuallystoredthesameamountofinformation,withoutactuallystoringanydataunnecessarily.Whenwewanttorecreateallofthestoreddataacrossseveraltables,weuseanSQLjoincommand.Thiswillbeexplainedinalaterchapter.RedundantorNot?LookatFigure4.6again.Istherestillredundantdatainthistable?Inthecookiecolumn,thefirstlineandthethirdlinecontainthecookieIDof1.Ifweweretoremovethisvalue,asitisrepeated,wewouldlosethelinktothecookietable,andthuslosesomeinformationaboutthepageview.Whenaforeignkeyisrepeatedwithinacolumn,itusuallydoesnotsignifyredundantdata.SothereisnoredundantdatawithinthecookieIDcolumnnow.Ofcourse,youmaynoticethatFigure4.6stillhassomeredundantdatainit,astherearestillrepetitionsinthereferringpagecolumn.However,nootherinformationaboutthereferringpageneedstobestored,socreatinganothertablejusttologthismaywellbeanunnecessaryoverhead.Onthisoccasionwewillleavethisdataasitisandnotstoreitinanothertable.ReferentialIntegrityCreatingrelationshipsbetweentablesusingprimaryandforeignkeystoremoveredundantdataisreasonablystraightforward.However,whenyouwishtoalterordeleterecordsinrelatedtablesyoucancomeacrossproblemswithreferentialintegrity.Forexample,lookatthetwotablesshowninFigure4.7.Ifweweretoremoverow2fromtheweblogtable,thiswouldbeallowableasnoothertableinourexamplemakesreferencetothisrow.Thereferentialintegrityofourdataismaintained.Weareremovingsomeofthedata,buttherestofthedatabaseremainssound.Ifweweretoremoverow2fromthecookietablewehaveaproblem.WewouldlosesomeinformationabouttheMozillaclient,butwhatabouttherestofthedatabase?Inthiscase Chapter4•DesigningandCreatingTables37Figure4.7Referencesmustremainconsistent.row2oftheweblogtablestillreferstorow2ofthecookietablewhichwehavejustremoved.Ifwetriedtojointhetwotablestogethertogetbackalloftheinformationstoredbetweenthem,thesystemwouldnotbeabletofindthereferencetotheMozillacookie,sothejoinwouldfail.Removalofrow2fromthecookietablehascompromisedthereferen-tialintegrityoftherestofthedatabase.Theonlywaythatwecouldremoverow2ofthecookietableisifweremovedeveryrowthatcontainedareferencetoitinallrelatedtables.Wehavetoremovealloftheforeignkeyreferencestothisrowfirst,andthenremovetheprimarykeyfromthecookietable.ThereisaspecifictypeofcommandusedfordeletingdatawhilemaintainingreferentialintegritycalledaCASCADEdelete.Thiswillbetoucheduponinalaterchapter.MostDBMSwilltrytomaintainandenforcereferentialintegrity,butthiswillonlyworkifyouhavecorrectlytoldthesystemwheretherelationshipsbetweentablesare.IfyouareusingascriptinglanguagesuchasPHP,itwillbepossibletoenforcereferentialintegrityinyourcode,andnotwiththedatabase.However,thiswillonlyworkifyourcodeworks,andsoitismuchbettertolettheDBMSenforcetherules.MySQLwillonlyenforcereferentialintegrityifyouareusingaspecifictypeoftable.ThedefaulttableonmostinstallationswillbeoftheMyISAMtype.Thistypedoesnotenforcereferentialintegrity.Ifyouarecreatingtableswhichneedreferentialintegrityenforced,youwillneedtocreateInnoDBtables.TheonlineMySQLmanualcontainsasectiononensur-ingthatyourMySQLserversupportsthesetables.NULLFinally,beforeIshowyouhowtocreateatable,weneedtodealwithNULL.ANULLentryisplacedinacolumnwhenthereisnovaluestoredinthatcolumn.TheNULLisaspecialsymbolwhichsignifiesthatnovaluehasbeeninsertedyet.ANULLisnotliketheemptystring(“”),becausetheemptystringisastringwithnocontents.NULListheabsenceofcontent.BecauseofthisyoucannotmatchaNULLincomparisons.Whencreatingatable,youcanspecifythatacolumncannotcontainaNULLbyusingNOTNULLafterthecolumntype.Ifyouarespecifyingacolumnthatisaforeignkey,the 38DatabaseDesignManual:usingMySQLforWindowsNOTNULLcommandimpliesthattherowmusthavearelationshipwitharowinanothertable.YourprimarykeycolumnwouldnormallyalsobespecifiedasNOTNULL,aseachvalueinthiscolumnmustbeunique.CREATEDATABASEBeforewecreateatablewehavetocreateadatabase.Thedatabaseisthecontainerinwhichwestorethetables.Youcreatethedatabaseusingthefollowingcommand:CREATEDATABASEdatabasenameOnceyouhavecreatedthedatabaseyouhavetoindicatetoMySQLwhichdatabaseyouwillbeusing.Youdothisbymeansofthefollowing:USEdatabasenameAshasbeenpreviouslydiscussed,thedefaultdatabasethatMySQLcreatestostoreallofitssystemtablesiscalledmysql.Itisbestifyoucreateaseparatedatabasetostoretheexampletablesusedinthisbook.WewillcallthisdatabaseMySQLfast.Wecanselectthisdatabasetousebyissuingthefollowingcommands:CREATEDATABASEMySQLfast;USEMySQLfast;Ifyoucreatethenewdatabaseusingthegraphicalclient,itdoesnotimmediatelyappearinthedropdownlistofdatabases.Togetittoappearaftercreatingthedatabase,closedowntheclientandrestartit.Itshouldnowbeselectablefromthedropdownlist,asshowninFigure4.8.SelectingadatabasefromthedropdownlistinthegraphicalclientisexactlythesameasissuingtheUSEdatabasecommand.Figure4.8Selectingadatabasefromthedropdownlist. Chapter4•DesigningandCreatingTables39CREATETABLEAfteryouhavedesignedyourdatabase,youneedtodefinethetablesinMySQL.ThemainwaytodothisiswiththeCREATETABLEcommandwhichisusedasfollows:CREATETABLEtablename(columnnametypeoptions,columnnametypeoptions,…otheroptions)Intheabovetablenameandcolumnnameareself-evident,andtypeisthedatatypeofthecolumnaswedescribedinChapter3.optionsarevariousoptionsthatyoucanapplytothecolumn,suchasthefollowing:•NOTNULLwhichprohibitstheuseofNULLwithinthecolumn.•AUTO_INCREMENTwhichwilladdavaluetothevalueofthiscolumninthepreviouslyaddedrow,andinsertthenewvalueintothisrowautomatically.Thisnormallydefaultstoanincrementof1,andisusefulforautomaticallycreatinganew,uniqueprimarykeyvalue.•DEFAULTwhichwhenfollowedbyavalueallowstosetthedefaultvaluethatthecolumnwillbecreatedwithifnotspecifiedwheninsertingdata.Sowewillnowcreateatabletostoreourwebsite’swebpages.Wewillstorethefollowing:•TheIDofthewebpage,whichwewillautomaticallycreate,andwillbetheprimarykeyandamediuminteger.•Thetitleofthepage,whichwillbethetextthatweputbetweenthetagsintheHTMLdocument.Thiswillbeavariablelengthstringof50charactersmax-imum.•Theactualcontentofthewebpage,whichwillbeinsertedbetweenthetagsoftheHTMLdocument.Wewillusethetexttypeforstoringthis,asitwillenableustostorealargeamountoftextifneeded.Makesureyouareloggedintothecorrectdatabasebytypingthefollowingintothegraphicalclient:USEMySQLfastWecannowcreatethewebpagetableasfollows:CREATETABLEwebpage(idMEDIUMINTNOTNULLAUTO_INCREMENT,ContentTEXT,TitleVARCHAR(50),PRIMARYKEY(id));Runningthequeryabovecreatesthetable,butdoesnotgiveyouanyotherfeedbackunlessyouhavetypedsomethinginwrong.Tocheckthatthedatabaseiscorrectlycreated,runthefollowing:DESCRIBEwebpage 40DatabaseDesignManual:usingMySQLforWindowsFigure4.9DESCRIBEthenewlycreatedtable.Figure4.9showstheresultsofthis.Youcanseethatthetablehasbeencreated,andtheIDcolumnistheprimarykeyandthereisanauto-incrementcolumn.YouwillnoticethatafterwedefinedthelistofcolumnsweaddedthePRIMARYKEYoption,whichsetthecolumnthatwespecifiedastheprimarykeyforthistable.Youcanalsospecifyacolumnasaprimarykeybyputtingthesewordsdirectlyafterthetypedefinitionforacolumn.Wewilldemonstratethisbycreatinganothertableforthewebsitelogasfollows:CREATETABLELog(IDMEDIUMINTNOTNULLAUTO_INCREMENTPRIMARYKEY,CookieIDMEDIUMINT,WebpageIDMEDIUMINT,BrowserVARCHAR(50),DateCreatedDATETIME,IPNumberVARCHAR(15),ReferringPageVARCHAR(255))AtthisstageI’vedecidedtostorethevisitorbrowserinformationwithinthelogfile,andnotasaseparatetable.Weare,however,onlystoringtheIPnumberoftheclientmachine.IfweneedtofindthehostnamewecanlookitupusingaDNSserver.IdentifyingForeignKeysOurwebpagetabledidnothaveanyforeignkeys,butourlogtabledoes.Todefinethefor-eignkeywehavetoinsertthefollowingintoourcreatetabledefinition:FOREIGNKEY(column)REFERENCESparenttable(primarycolumn)Inthiscommand:•columnisthenameofthecolumnthatcontainstheforeignkeyinthistable.•primarycolumnisthenameoftheprimarykeycolumnthatthisforeignkeyreferences. Chapter4•DesigningandCreatingTables41•parenttableisthenameofthetablewherethecolumnresides.WecanaddthistotheCREATEdefinitionforourlogtableasfollows:CREATETABLELog(IDMEDIUMINTNOTNULLAUTO_INCREMENTPRIMARYKEY,CookieIDMEDIUMINT,WebpageIDMEDIUMINT,BrowserVARCHAR(50),DateCreatedDATETIME,IPNumberVARCHAR(15),ReferringPageVARCHAR(255),FOREIGNKEY(WebpageID)REFERENCESWebpage(ID),FOREIGNKEY(CookieID)REFERENCESCookies(CookieID))TYPE=InnoDBFigure4.10showsthistablebeingdescribed.IfyoutrytocreatethistableafteryouhaverunthepreviousscriptyouwillgetaTablealreadyexistserror.Youcanaddthewords:IFNOTEXISTSdirectlyafterthe:CREATETABLEstatementtostopMySQLgeneratinganerror;howeverthisdoesnotreplacethetable,itjustsuppressestheerrorifitalreadyexists,soitisofquestionableuse.OtherversionsofFigure4.10DESCRIBELogtable. 42DatabaseDesignManual:usingMySQLforWindowsSQLallowyoutoissuethecommandCREATEORREPLACEwhenyouarecreatingatable,whichwillreplacetheexistingtableifneeded.MySQLdoesnotyetsupportthisfunction.Youwillnoticethattheline:TYPE=InnoDBhasbeenaddedattheendofthetabledefinition.ThisisthetabletypethatyouneedtouseifyouwantMySQLtosupportreferentialintegrity.CreatefromSelectStatementYoucanalsocreatetablesbasedontheoutputofaselectstatement.Theformatisasfollows:CREATETABLEtablenameASselectstatementWewilldescribetheselectstatementindetailinalaterchapter.Theselectstatementallowsyoutoselectsomeorallofthecolumnandrowsinatable.Forinstance,ifwedecidedthatwewantedtomakeadifferentlogtablewithalloftheclientdetailsremoved,wecoulddoitwiththefollowingscript:CREATETABLELog2ASSELECTID,CookieID,WebpageID,DateCreated,ReferringpageFROMlogTheselectstatementthatweusehererequeststhosecolumnnamesthatdonotcontainclientdetails.Aftercreatingthistable,describingitgivestheresultsshowninFigure4.11.CompareFigure4.11withFigure4.9.Youwillnoticethatthenewtablehasfewercolumns,asweexpected.Thenewtablehasbeencreatedpurelyfromthecolumnnamesanddatatypesoftheselectstatement,andtooknonoticeoftheothercolumnoptionsourFigure4.11DESCRIBELog2table. Chapter4•DesigningandCreatingTables43originaltablehad,suchastheprimarykeyandauto-incrementoptions.Ifyouneedtoaddtheseoptionstoanexistingtable,seethedetailonALTERTABLEinChapter8.CREATETABLELIKEMySQLversion4.1givesusanextrafunctionthatsolvestheproblemofthecolumnattrib-utesnotbeingcopiedoverwhencreatingatablefromaSELECTstatement.Thisisusedasfollows:CREATETABLEdestinationLIKEsourceThiswillcreateanewdestinationtablethatisexactlybasedonthesourcetable’sstruc-ture.Createalog3tableasfollows:CREATETABLElog3LIKElogIfyoudescribethistableandcompareitwiththeoriginallogtable,youwillnoticethatallofthecolumnattributeshavenowbeencopiedover.Wewillcreatesomeothertablesasneededthroughthecourseofthisbook. Thispageintentionallyleftblank PopulatingtheDatabase5INSERTThemainmethodofputtingdataintoourtableisbyusingtheSQLINSERTcommand.Themostcommonlyusedformatofthiscommandisasfollows:INSERTINTOtablename(columnname,columnname,…)VALUES(value,value,…);Theinsertcommandaddsanewrecordtothetable,puttingthedategivenintothefieldsspecified.YoumustensurethatallofthecolumnnamesspecifiedafterthetablenamehavecorrespondingentriesintheVALUESsection.Thevaluesmustbeofthesamedatatypeasthecolumntheyaregoingtobeinsertedinto.Anycolumnsthatarenotspecifiedinthestatementareleftattheirdefaultvalueifyouspecifiedit,orsettoNULL.Todemonstrate,wewillbegintoaddsomedataintothedatabasethatwedescribedearlier.Tostartwithwewilladddatatothewebpagetable,whichwillstorethetitleandcontentofourweb-site.Allwebsiteshaveahomepagesowewillstartbyinsertingahomepage.Startupthegraphicaltoolsandlogin.Typethefollowingtoensurethatweareconnectedtothecorrectdatabase:USEMySQLfastYoucanalsoensurethatyouareconnectedtotheMySQLfastdatabasebycheckingthattheclientsaysMySQLfastnexttothewordConnectedonthesecondrowofcontrolsontheclient.Ifitsaysmysqlyouareinthewrongdatabase,sopulldownthemenubyclickingonthesquarenexttomysqlandselectMySQLfast.Onceyouareconnectedtothecorrectdata-baseyoucantypethefollowinginthequerybox:INSERTINTOwebpage(Title)VALUES(“Home”)ClickontotheExecuteQuerybuttontorunthequery.Unlessthereisanerrortherewillnotbeanyoutputtoshowyouwhatyouhavejustdone.Ifyoudohaveanerrormessageitislikelythatyouhavenotconnectedtothedatabase,oryouhavenotyetcreatedtheweb-pagetableasdescribedinthepreviouschapter.Ifthelatteristhecase,pleasereviewthecreatetablesectioninChapter4.45 46DatabaseDesignManual:usingMySQLforWindowsToviewthecontentsofthedatabase,typethefollowingandclickonExecuteQuery:SELECT*FROMwebpageWewillreviewtheSELECTcommandingreatdepthinthenextchapter,butfornowallthisqueryissayingis“givemeeverythingfromthetablecalledwebpage”.TheresultsareshowninFigure5.1.IfyoulookatFigure5.1youwillseethatalthoughweonlyinsertedthewordHome,MySQLhasauto-generatedthevalue1fortheIDcolumn.WealsodidnotspecifyavalueforthecontentcolumnsothishasbeensettoNULLasitcontainsnothingatthemoment.Ifyourememberourexampledatabasethatwedescribedinthepreviouschapterwehadatableforstoringtheentriesinavisitor’sbookforourwebsite.Thisimpliesthattherewouldbeapagetovieworaddentriestoavisitor’sbook,solet’saddanentryforthatpagebyexecutingthefollowing:INSERTINTOwebpage(Title,Content)VALUES(“Visitorbook”,“Pleaseaddacommenttomyvisitorbook”)Noticethatonthisoccasionwehavechosentoaddsomecontentforapage.Again,ifyouwanttoseetheresultsofthis,execute:SELECT*FROMwebpageINSERT–MultipleRowsWecouldcarryoninsertingvariousnamesforpagesusingtheabovecommands,butifyouhavemanyrowstoaddtoatableyoucanspeeduptheprocessbyaddingmultiplerowsaftertheVALUESkeywordasfollows:INSERTINTOwebpage(Title)VALUES(“Links”),(“Resume”),(“MyHobbies”)Figure5.1Thewebpagetable. Chapter5•PopulatingtheDatabase47Figure5.2Thewebpagetable.Whenyouareinsertingmultiplerowsintoadatabaseyouplaceeachrowinsideparen-thesesandseparaterowswithacomma.Selectalloftherowsfromyourwebpagetableagain.ItshouldlooklikeFigure5.2.INSERT–AllColumnsAnalternativeformoftheINSERTstatementmissesoutthecolumnnamelistasfollows:INSERTINTOtablenameVALUES(value,value,…);InthiscaseyoumustspecifyavalueforeachofthecolumnsinthetableintheVALUESsection,intheorderwhichyouoriginallycreatedthetable.Youhavelesscontrolovertheinsertingofdatausingthismethod,anditcanhaveprob-lemsassociatedwithit.Forexample,ifyouhaveacolumnthatisauto-generating(AUTO_INCREMENT)toprovideyourrowwithauniqueprimarykey,insertinganumberoverthismaywellcausethetabletohaveaduplicateprimarykey,whichisnotallowed.Itmaywork,butmayleadtoerrorsintheprocessingofyourdatalateron.IfwelookatFigure5.2again,wecanseethatwecansafelyaddarowwiththeIDnumber6asthereisnotanotherrowwiththisID.ThefollowingwilladdarowwiththatID:INSERTINTOwebpageVALUES(6,“”,”HowtoContactMe”)Thisshouldhavesuccessfullyaddedanotherrowintoyourtable.Asafurtherdemonstra-tionofhowhard-codinganauto-generatingfieldcanleadtoerrors,runtheabovequeryasecondtime.Figure5.3showstheresultsofthis.NoticehowthelastlineofFigure5.3hastheerrormessage:Duplicateentry‘6’forkey1. 48DatabaseDesignManual:usingMySQLforWindowsFigure5.3Errorcausedbyduplicateprimarykey.WhenyoutriedtoaddanotherrowtothewebpagetablethathadanIDof6,MySQLreal-izedthattheIDcolumnistheprimarykeyofthedatabaseandsowouldn’tallowyoutoinsertakeywiththatvalueasecondtime.INSERT–ColumnsfromaQueryYoucanalsoinsertrowsthatareoutputfromanothertable.Thecommandtakesthefol-lowingformat:INSERTINTOtablenameSELECTrowsFROManothertablenameWHEREcondition;Todemonstratetheabovecommand,wewillcopyourtabletoanotherone.Firstweneedtocreateanewtablewiththesamecolumnsasthewebpagetable:CREATETABLEwebpage2(IDMEDIUMINTNOTNULLAUTO_INCREMENTPRIMARYKEY,Contenttext,Titletext)Nowwewillcopythedatafromwebpagetowebpage2byexecutingthefollowing:INSERTINTOwebpage2SELECT*fromwebpage Chapter5•PopulatingtheDatabase49Whenusingtheabovecommand,thetablethatyouareselectingfrommusthavethesamenumberofcolumnsasthetablethatyouareinsertinginto.Forinstance,ifyoutrythefollowing:INSERTINTOwebpage2SELECTtitleFROMwebpageyouwillgettheerror:Columncountdoesnotmatchvaluecountatrow1.Toensurethatyourcolumnsmatch,youcanspecifyacolumnlistafterthefirsttablenameasfollows:INSERTINTOwebpage2(title,content)SELECTtitle,contentFROMwebpageAslongasyouhavethesamenumberofcolumnsintheSELECTstatementasyoudointhetableyouareinsertinginto,youcanusejustaboutanycodefortheSELECTstatement.ThefollowingwillsearchforrowsthathavethewordHomeinthetitlefieldandinsertthemintothewebpage2table:INSERTINTOwebpage2(title,content)SELECTtitle,contentFROMwebpageWHEREtitle=‘Home’Toviewthecontentsofthisnewtable,executethefollowing:SELECT*FROMwebpage2Figure5.4showsthecurrentcontentofourwebpage2table.IfyoulookatFigure5.4,youwillseethatwehaveauniqueprimarykeyforeachrowofthetable.YoucanalsoseethatthecontentcolumnsometimeshasNULLinit,wherenodatawasspecifiedinthecontentstring,anemptystringasinrow6,andtextdataasinrow2.Allareequallyvalidentries.Youcanalsoseethatinthetitlecolumnwenowhaveduplicatepageentries.Thisisacceptableinourdemonstrationdatabasebutwouldprobablyhavelit-tleuseifweweregoingtousethistabletopowerawebsite.WewillremovethistemporarytableinChapter9.InsertingDatawithScriptsAnotherwaytogetdataintoourdatabaseistorunascript.AscriptisjustacollectionofSQLqueriesseparatedbycolonsandstoredinatextfile. 50DatabaseDesignManual:usingMySQLforWindowsFigure5.4Thewebpage2tablepopulatedfromanothertable.Forexample,thefollowingfileisacollectionofcommandsthatcreatesatablecalledCookiesandfillsitwithsomesampledata:USEmysqlfast;CREATETABLECookies(CookieIDMEDIUMINTNOTNULLAUTO_INCREMENTPRIMARYKEY,DateCreatedDATETIME);INSERTINTOCookies(DateCreated)VALUES(“2002-01-01”);INSERTINTOCookies(DateCreated)VALUES(“2002-01-02”);INSERTINTOCookies(DateCreated)VALUES(“2002-01-04”);INSERTINTOCookies(DateCreated)VALUES(“2002-01-07”);INSERTINTOCookies(DateCreated)VALUES(“2002-01-13”);INSERTINTOCookies(DateCreated)VALUES(“2002-01-22”);SELECT*FROMCookies;Unfortunately,WinMySQLGUIdoesnotsupporttherunningofscriptfiles,soweneedtousethemysqlcommandlinetoexecutethescript.TypetheabovescriptintoatexteditorsuchasNotepadandsaveitasscriptimport.sqlinthedirectory:C:mysqlbinThefileneedstobesavedwithinthatdirectoryasitishardtoselectfilesfromdirecto- Chapter5•PopulatingtheDatabase51riesotherthantheonethatMySQLusrunningfrom.Startupacommandprompt,byselectingStart/Runandtypingcommand.Thentype:cdmysqlbintogettothebinarydirectoryandthentype:mysqltoruntheclient.Oncetheclientisrunningyoucanthenrunthescript.Youdothisbyusingthecommandsourceandgivingthecommandafilenameasfollows:sourcescriptimport.sqlThiswillrunthescriptandtheoutputcanbeseeninFigure5.5.Youwillnoticefromthelastlineofourscriptthatweadded:SELECT*FROMCookiesattheendofthescript.Becauseofthiswehaveasampleoutputattheendofthequery.Youcanclearlyseethat6rowshavebeencreated,thedateshavebeeninsertedandtheauto-generatedcookieIDshavebeencreated.ToprovethatMySQLisamulti-usersystem,loadFigure5.5Runningascriptfromthecommandline. 52DatabaseDesignManual:usingMySQLforWindowsFigure5.6Thecookietableviewedinthegraphicaltools.thegraphicaltools,connecttothemysqlfastdatabaseandrunthequeryagain.Figure5.6showstheresultswhichcomparedwithFigure5.5areidentical.DirectlyInsertingDataAnotherwaythatwecaninsertdataintotablesisbyusingtheMySQLcommandLOADDATA.Thisfileloadsdataintoaspecifiedtablefromatextfile.Thebasicformatisasfol-lows:LOADDATAINFILE‘filename’INTOTABLEtablename(Column1,Column2,…)Filenameisthenameofthetextfilewiththedatathatyouwanttoimport.Tablenameisthetabletoinsertthedatainto.Thecolumnsarethecolumnsthatyouwanttoinsert,intheorderthattheyappearwithinyourtextfile.Forexample,thefollowingisaheavilyeditedsectionofaweblogfile:ID,DateCreated,Browser,IPNumber,CookierID,WebPageID,ReferringPage1,2002-01-01,Mozilla/4.0,192.168.1.10,2,1,Typed2,2002-01-01,Mozilla/4.0,192.168.1.10,2,2,http://minbar.homeip.net/3,2002-01-01,Mozilla/4.0,192.168.1.10,2,3,http://www.google.com4,2002-01-01,Mozilla/4.0,192.168.1.10,2,1,http://www.easyrew.com5,2002-01-01,Mozilla/4.0,192.168.1.10,2,1,http://www.kli.orgIfwewantedtoimportthatfileintoourlogtable,wewouldneedtosavetheaboveasatextfile.Callthefilelog.sqlandsaveitin:C:mysqldatamysqlfast Chapter5•PopulatingtheDatabase53Bydefault,LOADDATAexpectsthefiletobeonthesamemachineastheMySQLserver,withinthedirectorythatcontainstheinformationaboutthecurrentdatabase,inourcase,mysqlfast.Ifwewererunningtheclientandserveronseparatemachines,andwantedthefiletobeontheclientmachine,wewouldneedtoinsertthewordLOCALbeforeINFILEtospecifythatitisonthelocalmachine.Wedonotneedtodothisinourexample.Ifwelookatthetextfileagain,wecanseethatthefirstlineisalistofthecolumnnamesintheorderofthefile.ThisisusefulinformationtousbutnottotheLOADDATAcom-mand.Wecoulddeletethatfirstrowbutthatwouldmeanchangingthecontentsofourlogfilewhichmaybeneededbyanotherprogram,soweneedtoaddtheline:IGNORE1LINESattheendofourquerytogetthecommandtobypassthefirstline.Youwillnoticethatourfilehascommasseparatingthecolumns.Thisisonestandardofrepresentingdataintextfiles,calleda“csv”orCommaSeparatedVariablefile.Bydefault,though,theLOADDATAcommandlooksforatabtoseparatecolumns.WethereforehavetotellLOADDATAtoviewtheseparatorsascommas.Thisisdonebyaddingthefollowingtothequery:FIELDSTERMINATEDBY‘,’Asyoumayexpect,asthereisaFIELDSTERMINATEDcommandthereisalsoaRECORDSTERMINATEDcommandforsettingthewaythattherecord’sendisrepre-sented.AsLOADDATA’sdefaultsettingforarecord’sendisthenewline,thesameasourtextfile,wedon’thavetospecifythis.IfyourememberwhenwecreatedtheLogtableinthepreviouschapter,weusedthefol-lowingcommand:CREATETABLELog(IDMEDIUMINTNOTNULLAUTO_INCREMENTPRIMARYKEY,CookieIDMEDIUMINT,WebpageIDMEDIUMINT,BrowserTEXT,DateCreatedDATETIME,IPNumberTEXT,ReferringPageTEXT)Bydefault,LOADDATAexpectsthecolumnsinthetextfiletobeinthesameorderaswhenthetablewascreated.Ifyoucomparethiswiththetextfileyouwillseethatthecol-umnorderisdifferent.LOADDATAallowsyoutospecifytheorderofcolumnstoimportbyjustgivingitalistoftherows.WenowhaveenoughinformationtoinsertourlogfileintotheLogtable.Thecommandthatwewillusetodothisisasfollows: 54DatabaseDesignManual:usingMySQLforWindowsLOADDATAINFILE‘log.csv’INTOTABLELogFIELDSTERMINATEDBY‘,’IGNORE1LINES(ID,DateCreated,Browser,IPNumber,CookieID,WebPageID,ReferringPage)Figure5.7TheLogtablepopulatedbyLOADDATA.Figure5.7showstheresultsoftheabovequery,whenweselecteverythingfromtheLogtable.Theaboveprocesscanseemlikealotofefforttoput5rowsinatable;wehavehadtomanipulatetheloadcommandandprobablycouldhaveinsertedthatmanyrowsinthesametimebyusingINSERTstatements.Theeffort,however,wouldhavebeenmuchmoreworthifwewereinsertingafulllogfileintoourdatabase.Thefilethattheaboveexamplefilewastakenfromhadover38000rows,whichcouldbeinsertedintoourdatabasewiththesameeffortaswehaveexpendedintheaboveexample.ThisiswherethepowerofLOADDATAcomesintoitsown.Wemayalsohavebeenimportingthedatafromanotherdatabasesystem,suchasOracleorSQLServer.IfthiswasthecaseitwouldbeeasiertooutputthedatafromthatDBMSinaformatthattheLOADDATAcommandinstantlyrecognizesasdefault;forinstanceusingtabsascolumnseparatorsandmakingtheorderofthecolumnsthesameasourcreatetablecommand.Themoreyoucanmanipulateyourdataintoastandardformat,theeasieritistoimportintoothersystems.Nowthatwehavesomedatainourdatabasewecanbegintoseethetechniquesinvolvedinretrievingthedata. RetrievingtheData6SELECTWehavespentquiteawhileoverthelastfewchaptersthinkingabout,designingandpopu-latingourdatabase.Nowwehavegotsomedatainthere,weneedtogetitbackinacon-trolledway.ProbablythemostcommonlyusedSQLcommandistheSELECTstatementwhichreturnsdatafromthedatabase.TheSELECTstatementhasvariousdifferentformats,whichwewilldescribeonebyoneinthischapterwithexamples.Thesimplestformoftheselectstatementisasfollows:SELECT*FROMtablenameTheabovestatementwillreturneverythingfromyourtable.Theasteriskinthiscontextmeansallcolumns.Sothequerymeans:“Givemeallofthecolumnsandalloftherowsofthetabletablename”.Forexample,torunthisqueryonthewebpagetablewouldrequirethefollowingcode:SELECT*FROMwebpageFigure6.1showstheresultsofrunningthisquery.Withthedatathatiscurrentlywithinourwebpagetable,theSELECT*resultsfitneatlyintotheresultsshowninFigure6.1.Inpractice,however,werarelyhavethislimitedamountofdata.Forexample,ourweblogtablewillquicklybecomepopulatedbydatathatwillfillmanyscreens.Wecanseeanexampleofthisalreadyifwerunthesamequeryonthelogtable:SELECT*FROMlogFigure6.2showstheresultsofthissimplequery.Allofthedatainthelogtablehasbeenreturned,ascanbeseenbyscrollingacrosstheresults.However,ifwewanttofindoutonwhichdatescertainpageswereviewed,scrolling55 56DatabaseDesignManual:usingMySQLforWindowsFigure6.1Select*fromwebpage.Figure6.2Awidesetofresultsfromthelogtable.acrossisslowandcouldpossiblyleadtoerrorsinreadingtheresultsifweslippedoureyestothewronglinewhilescrolling.Itismuchbettertogetexactlythedatathatwewant.TheSELECTcommandallowsyoutospecifythecolumnsthatyouwantreturnedasinthefol-lowingexample:SELECTwebpageid,datecreatedFROMlogYouwillnoticethattheabovequeryhasthetablenamesseparatedbycommas.IfwerunthisquerywegettheresultsshowninFigure6.3,whichgiveusjustthedatathatwewant.WhenyouusetheSELECTstatementtoreturncolumnsitdividesyourtablevertically,retrievingonlythespecificcolumnsthatansweryourquery.YoucanspecifyanyofyourcolumnswiththeSELECTstatement,includingallofthem,buttheasteriskmeanslesstyp-ing.Youcanalsoselectthesamecolumnmorethanonetime,aswellasselectingcolumnsfromdifferenttables.Youmaynotyetseewhyyouwouldwanttousethelattertwometh-ods,butwewillreturntothemlater. Chapter6•RetrievingtheData57Figure6.3TwocolumnsreturnedbytheSELECTcommand.SELECTDISTINCTSELECTDISTINCTisamethodofreturningauniquesetofdatafromyourtable.ItisbesttoonlyapplythisSELECTtoaspecificcolumninyourtable.Theformatofthisqueryisasfollows:SELECTDISTINCTcolumnnameFROMtablenameForexample,youmayrememberthatoneofthepagesthatwehaveonourwebsitewasourresume.IfyoureferbacktoFigure6.1youcanseethatthiswaspageID4.WecanuseSELECTDISTINCTtogiveusalistofthepagesthathavebeenviewedinourweblogtable.Wedon’tneedthequantityornumberoftimeseachpagehasbeenviewed,thatcancomelater,butwejustneedalistofpagesthathavebeenviewed.Thefollowingquerywillgiveussuchalist:SELECTDISTINCTwebpageidFROMlogTheresultsoftheabovequeryareshowninFigure6.4.Figure6.4SELECTDISTINCTontheLogtable. 58DatabaseDesignManual:usingMySQLforWindowsIfyouexamineFigure6.4youwillseethatalthoughtheLogtablecontainedmorethanthreerows,theSELECTDISTINCTqueryhasreturnedonlythree,andallofthemcontainauniquepageID.Unfortunately,noneoftheserowscontainthepageID4,sonoonehasviewedtheresumewebpageyet.ItlookslikeIwillnotbechangingjobsinthenearfuture.Inalaterchapterwewillseehowtocountspecifichitsperpage,butyoucanseefromtheabovehowthesimpleadditionoftheDISTINCTcommandcaninstantlypresentourdatainamannerthatismucheasiertouse.Youwillalsorealizethatifourwebsitehadthou-sandsofhitsinthelog,thisquerywouldstillproduceafewrowsthatwereeasytounder-stand.WHEREWehaveseenhowtoselectspecificcolumnsofdatausingtheSELECTcolumnnamequery.Thisslicesthetablevertically.Thenextcommandwillslicethetablehorizontally,selectingspecificrows.ThisisdonebymatchingthedatawithinarecordwithasetconditionthatisspecifiedintheWHEREclause,asfollows:SELECTitemsFROMtablenameWHEREconditionTheWHEREclauseisn’tonlyusedwithselectstatements;itcanbeusedonanyoccasionwherewewishtolimitaquerytoonlyapplytocertainmatchingrows.ToillustratetheWHEREclause,thefollowingqueryshouldtellusallthetimesthattheHomepage(ID=1)hasbeenviewedinourweblog:SELECT*FROMlogWHEREwebpageid=1Figure6.5showtheresultsofthisquery.Thequeryissaying:“Givemealloftherowsthatcontaina1(theIDoftheHomepage)inthewebpageidcolumn.”ComparethiswithFigure6.5WHEREreturnscolumnsthatmatchacriteria. Chapter6•RetrievingtheData59Figure6.6WithouttheWHEREclause.Figure6.6whichistheresultsoftheSELECTstatementwhentheWHEREclauseisomit-ted.TheconditionintheWHEREclausecancheckforoneconditionormany,andcancom-prisemanydifferentformats.Forexample,ifweweresearchingforatextstringwewouldputthestringwithinquotemarksasfollows:SELECT*FROMwebpageWHEREtitle=“Home”ThecurrentversionofMySQLwillacceptstringswithinquotemarks(“”)orapostrophes(‘’).Thismaychangeinfutureversionsbutwhicheveryoudecidetousemakesurethatyouconsistentlyusethesamethroughoutallofyourqueries.MySQLOperatorsAswellas=,MySQLsupportsotheroperators.SomeoftheseareshowninTable6.1.Table6.1SomeMySQLOperators.OperatorMeaning=equal<>notequal!=notequal<=lessthanorequal=greaterthanorequal>greaterthan<=>nullsafeequal 60DatabaseDesignManual:usingMySQLforWindowsLIKEWhenyouaresearchingforastringusingtheequalssign(=),SQLonlysearchesfortheexactstring.IfyouneedtosearchforastringthatbeganwithHome,youneedtousetheLIKEoperatorandpatternmatching.Totestthis,wewilladdanotherrowtoourwebpagetablewhichwillpointtoawebpagethatcontainshappymemoriesofwherewegrewup.Addtherowwiththefollowingquery:INSERTINTOwebpage(Title,Content)VALUES(“HomeiswheretheHeartis”,“Thispagecontainshappymemoriesfrommychildhood”)Selecteverythingfromthewebpagetableafterrunningthequeryabovejusttocheckthatthenewrowhasbeenadded.YoushouldnowseeseveralrowsthathaveaTitlecolumnthatbeginwiththewordHome.Wewillnowtryandretrieveallofthosecolumns.Ifyouhaveprogrammedbefore,thetemptationistotrythefollowingquery:SELECT*FROMwebpageWHERETitle=“Home*”Figure6.7showsthatwhenyourunthisqueryitdoesn’tproducewhatyouwouldexpect.Allthatisgivenistheerrormessagethatthequeryproducednoresults.Togetthatqueryworkingthewaythatwewant,weneedtousetheLIKEoperatorintheconditionasfollows:SELECT*FROMwebpageWHERETitleLIKE“Home%”Figure6.8showstheresultsofrunningthisquery.Figure6.7Equalswillnotworkwithfuzzymatching. Chapter6•RetrievingtheData61Figure6.8UseLIKEtofuzzymatch.Youwillhavenoticedthatwedidnotusetheasteriskasthematchallcharactersasyoumaybefamiliarwithfromotherlanguages.SQLusesthepercentsign(%)tosignifythatanynumberofcharacterscanbematched.PuttingthepercentsignattheendofthestringwearelookingformakesSQLmatchanythingthatbeginswiththestring.Tomatchany-thingwithHomeattheendwewoulduse:SELECT*FROMwebpageWHERETitleLIKE“%Home”AndifwewerelookingforastringwiththewordHomesomewhereinit,wewouldputthepercentsignatbothendsofthestringasfollows:SELECT*FROMwebpageWHERETitleLIKE“%Home%”Beforeyourunthequeryabove,tryandworkoutwhatitwillselectfromourtable.Whydoyouthinkitworksthewayitdoes?Aswellasusingthepercentsigntomatchmultiplecharacters,youcanalsousetheunderscore(_)tomatchsinglecharactersasfollows:SELECT*FROMlogWHEREbrowserLIKE“Mozilla/_._%”ThiswillmatchanyversionoftheMozillabrowser,nomatterwhatbrowserdescriptionfollows.SELECTAS(aliases)Onoccasions,youmayneedtoselectthesamecolumntwiceinaquery.Anexampleofanapplicationofthiswilloccurinthechapteronaggregatefunctions.Thesimplestwaytoselectacolumntwiceisbyquotingittwiceinthecolumnlistasfollows: 62DatabaseDesignManual:usingMySQLforWindowsFigure6.9Selectingthesamecolumntwice.SELECTid,idFROMwebpageAsyouwillexpect,theabovequeryproducesaresultasshowninFigure6.9.However,itmaybethatlaterinthequeryweneedtoperformsomecalculationononeofthosecolumns.Howarewetotellthedifferencebetweenthetwo?Asbothcolumnsarecalledidthatwouldbedifficult.TosolvethisproblemSQLallowstheuseofaliases,thatis,therenamingofacolumnbyuseoftheASkeyword.Tousethiswewouldmodifytheabovecodeasfollows:SELECTid,idASpageidFROMwebpageYoucancomparethedifferentresultsofthetwoqueriesbyexaminingFigures6.9and6.10.Figure6.10Selectingthesamecolumntwicewithanalias. Chapter6•RetrievingtheData63Youcanseethatthenamesofthecolumnsarenowdifferent.TherearemanymoreusesoftheASkeyword.Weuseittogreatextentwithaggregatefunctions.Italsowillappearafewtimesintherestofthischapter.BETWEENTheBETWEENkeywordisusedwhenyouneedtogetdatabackfromyourquerythatmatchesarangeofvalues.Forinstance,youmaywanttoseehowmanywebpageswereviewedlastweek(betweentwodates),orthathavebeenviewedatleasttenbutnotmorethanfiftytimeseach.BETWEENisfollowedwiththefirstvalueintherange,theANDkeywordandthesecondvalue,asfollows:SELECTcolumnsFROMtablesWHEREcolumnBETWEENfirstvalueANDsecondvalueForexample,everynewvisitortoourwebsitegeneratesanewcookie.Westorethesecookiesinourcookietable.However,weknowthatthefirstfourcookiesthatwerecreatedwerejustfortestingpurposes.Soweneedaquerytolookforcookieswithinarange.Thiscanbeachievedasfollows:SELECTcookieidFROMcookiesWHEREcookieidBETWEEN5AND10Figure6.11showstheresultsoftheabovequery.YouwillnotethatthequeryhasonlyproducedtworowswiththeIDs5and6.Althoughthisisaverysimpleexample,theBETWEENkeywordisverypowerfulaswewillseelateronwhenwelookatprocessingdates.NOTTheNOTkeywordwhenappliedtoaconditionwillgivetheoppositeanswer.Allconditionsgiveatrueorfalseresult,andNOTchangestheresulttotheopposite.TodemonstratethiswewillapplythistothequerywejustdiscussedwhichreturnedallrowswiththeIDbetween5and10.AddNOTtothequeryasfollows:SELECTcookieidFROMcookiesWHEREcookieidNOTBETWEEN5AND10Figure6.11showedthatthefirstquerypickedtheIDs5and6.ComparethiswithFigure6.12whichshowstheresultswhenNOTisapplied.Adifferentsetofrowshasbeenselectedthistime. 64DatabaseDesignManual:usingMySQLforWindowsFigure6.11BETWEENselectsfromarange.Figure6.12NOTappliedtotheBETWEENquery.Asanotherexample,wewilllookatwhathappensifyouselectasinglerowfromatable.Wewillusethecookietableagain,soselectasinglerowasfollows:SELECT*FROMcookiesWHEREcookieid=3Thiswilldefinitelyselectasinglerowasweareselectingfromtheprimarykeycolumn,sotherewillbenootherrowswiththeIDof3.Runthequeryandnoticewhatisreturned.NowaddaNOTtotheconditionasfollows:SELECT*FROMcookiesWHERENOT(cookieid=3)YouneedtoaddtheparenthesestoensurethattheNOTworkswiththewholecondition.Withthischange,whatwillthequerynowselect?Figure6.13showstheresults.NoticehowtheNOThasradicallychangedtheresultsofthequery;insteadofreturningasinglerow,itnowreturnseverythinginthetableapartfromthespecifiedrow. Chapter6•RetrievingtheData65Figure6.13NOTappliedtoasinglerowquery.Whydoweneedtheparenthesesintheabovescript?Canyouworkoutwhatwouldhap-penwithoutthemandthenedityourqueryandtestyourtheory?Whydoyouthinkthishappens?CONCATCONCATallowsyoutocustomizeyourqueriesoutputanywayyouwish.NormallyifyouexecuteaSELECTtablenameallthatyouwillgetbackaretheunprocessedcontentsoftheserecords.Thiswillnotalwaysbeexactlywhatyouneed,soyoucanaltertheoutputwithCONCAT.Todemonstratethiswewilladdatableforloggingvisitorbookentriestoourwebsite.Wewillhavetorunafewscriptstosetthisup.Firstwewilladdanentryforthevisitor’spageinourwebpagetable:INSERTINTOwebpage(Title,Content)VALUES(“Visitorbook”,“Pleasesignmyvisitor’sbookbyfillinginthedetailsonthispage”)Nowwewillcreateavisitorbooktableandaddsomefictitiousentries:CREATETABLEvisitorbook(EntryIDMEDIUMINTAUTO_INCREMENTNOTNULLPRIMARYKEY,CookieIDMEDIUMINT,EntryDateDATETIME,EntryTextTEXT,FirstNameTEXT,MiddleNameTEXT,LastNameTEXT); 66DatabaseDesignManual:usingMySQLforWindowsINSERTINTOvisitorbook(CookieID,EntryDate,FirstName,MiddleName,LastName,EntryText)VALUES(1,“2002-01-22”,“Rowan”,“”,“Norman”,“Justtesting!”),(1,“2002-02-01”,“Reuben”,“”,“Norman”,“Idontknowwhattotype”),(1,“2002-02-01”,“Pat”,“”,“Shah”,“ThoughtIwouldleaveamessage”),(1,“2002-02-05”,“Lucy”,“”,“Garrett”,“WherehasPrincessgone?”),(1,“2002-02-15”,“Zephen”,“”,“”,“Ilikethesitebutwhataboutgraphics”),(1,“2002-02-16”,“Matthew”,“”,“Norman”,“Justtesting!”);Youmaynoticethat,forspeed,wehaveusedtheTEXTdatatypeformostofthecolumns.Thisisnotveryefficientbutwewillremedythisinalaterchapter.Justcreatethetableasitisfornow.Onceyouhavepopulatedthevisitorbooktable,checkyourworkwith:SELECT*FROMvisitorbookThisshouldgiveyousixrowsifyouhavecopiedthescriptincorrectly.NowwewilluseCONCATtomergethefirstnameandlastnametablesintoonecolumn.Executethefollow-ing:SELECTCONCAT(FirstName,““,LastName)FROMvisitorbookThisquerysays:“Givemethefirstname,aspace,andthelastnameallinonecolumn,foreveryrowinthevisitorbook.”EachelementinsidetheCONCATparenthesesisseparatedbycommas.Ifyouhavetoincludeastring,suchasthespacecharacter,thenyouneedtosurrounditwithquotationmarks.Figure6.14showstheresultsofthisquery;allnamesaredisplayedneatlyinasinglecolumn,withaspaceinbetween.Ifyoudon’thaveresultsasshowninFigure6.14,itisprobablybecauseyouhaveputaspaceinbetweentheCONCATkeywordandtheparentheses.CONCATisafunction,andallFigure6.14CONCATformatsyourresults. Chapter6•RetrievingtheData67functionkeywordsthatarefollowedbyparenthesesshouldhavethemdirectlyafterthekeyword,withoutaspace.NoticealsothenameofthecolumnatthetopofFigure6.14.ThisnameistheCONCATfunctionweissuedwithinthequery.Althoughthisnamemakesitobviouswhatthecolumnis,ifwehadtocontinuallyrefertothiswithinotherqueriesorphpcodeitwouldsoonbecometedious.SohereisagoodexampleofusingtheASkeywordtogivetheresultsadif-ferentname:SELECTCONCAT(FirstName,““,LastName)ASfullnamesFROMvisitorbookFigure6.15showstheresultsofjustaddingASfullnamestoourquery.Ourcolumnnowhasameaningfulname.Wecandemonstratethisonestepfurther.Onourwebsitewewilleventuallyshowvisi-torsallofthevisitorbookentries.Wewillprobablyhavetoformatthedatawegetbackfromthequeryextensivelytomakeitfitforpresentationonawebpage.Thismightrequiremuchformattingwithinthescriptinglanguagethatthepageiswrittenin.SowewillusetheCONCATfunctionofSQLtodosomeoftheformattingforus,savingsometimeandworklateron.Hereisasamplescript:SELECTCONCAT(“Inourvisitorbook,“,FirstName,““,LastName,“says:“,Entrytext)ASentriesFROMvisitorbookAstheCONCAThasmultipleelementshere,Ihaveover-formattedthescriptsoyoucanseewhatishappening.Therearelotsofcommasandspeechmarksthatcangetquitecon-Figure6.15CONCATandASforreadableresults. 68DatabaseDesignManual:usingMySQLforWindowsFigure6.16CONCATsupportscomplexformatting.fusing,sohavealookattheresultsinFigure6.16toseewhatitdoes.Nowthatyou’veviewedtheresults,thescriptshouldmakemoresense.SoyoucanseehowyoucanuseCONCATtoformatyourquery’soutput,gettingSQLtodotheworkforyoutosavescriptingtimelateron.YoucanputmultipleelementsinsidetheCONCATfunction.AlsorememberthatyouarenotlimitedtousingjustaCONCATfunc-tioninaselectstatement,youcanalsouseothercolumnnamesatthesametime,forexam-ple:SELECTCONCAT(FirstName,““,LastName)ASnames,EntryID,EntrytextFROMvisitorbookBeforerunningthisscript,seeifyoucanworkoutwhatresultsitwillproduce.ORDERBYOneofthethingsthatyouoftenwishtodowithdatabasesissorttheresults.MySQLsortstablesusingtheORDERBYclauseinthefollowingformat:SELECTcolumnnamesFROMvisitorbookORDERBYcolumnnamesForexample,tosortallofourvisitorbookentriesbylastnamewewouldrun:SELECTentryid,lastnameFROMvisitorbookORDERBYlastnameFigure6.17showstheresultsofthisquery.Noticehowtherowshavebeenreturnedinadifferentordertowhichtheywerecreated. Chapter6•RetrievingtheData69Figure6.17ORDERBYsortscolumnsinascendingorder.Bydefault,ORDERBYsortsinascendingorder.YoucanalsospecifythisbyaddingthekeywordASCaftertheclause.TogetthesorttogoindescendingorderweaddthekeywordDESCinstead,asfollows:SELECTentryid,lastnameFROMvisitorbookORDERBYlastnameDESCFigure6.18showstheresultsindescendingorder.Youarenotlimitedtojustsortingbyonecolumn,andyoudonotevenhavetoreturnthecolumnyouaresortingfromthequery.HereisanexampleofthiswhichsortsbythelastnamebutonlyreturnstheentryIDandfirstname:SELECTentryid,firstnameFROMvisitorbookORDERBYlastname,firstnameCompareFigure6.19withFigure6.17.Thelistisnowsortedfirstonthelastnamecol-umnirrespectiveofwhetheritisreturnedornot.Afterthatitissortedbythefirstnamecolumn.ThisstillleavesthethreeNormanstogetherinthemiddleofthequery,butthensortstheirfirstnamesalphabeticallyaswell.AswehavejustbeendiscussingtheORDERBYclause,thismightseemlikeagoodplacetodiscusstheGROUPBYclause.However,asmuchasthetwoclausessoundsimilartheGROUPBYclausehasatotallydifferentuseandsowillbediscussedinChapter10.INOfteninaqueryyouhavetosearchforaspecificmatch,oramatchthatisless,ormore,thanasetvalue.However,sometimesyoumayneedtosearchforvaluesthatdon’teasilyfit 70DatabaseDesignManual:usingMySQLforWindowsFigure6.18ORDERBY…DESCreversesthesortorder.Figure6.19ORDERBYonadifferentcolumnthanthatreturned.intoasimpleclause.Whenthisisneeded,youcanusetheINkeywordtosearchagainstasetofcriteria.Thiscommandworksasfollows:SELECTcolumnsFROMtablenameWHEREcolumnIN(values)Intheabovequery,valuesisacomma-separatedsetofvalues.AsanexampleofthiswewillagainselectallofthewebpagesthatbeginwiththewordHomefromourwebpagetable.UsingLIKEthiscommandis:SELECTid,titleFROMwebpageWHEREtitleLIKE“Home%” Chapter6•RetrievingtheData71Figure6.20SelectingtheHomepages.Figure6.20showstheabovescriptinaction.ItalsoshowsthethreedifferentIDsthatbeginwithHome;1,2and7.WewillnowusethoseIDstoselecttherows:SELECTid,titleFROMwebpageWHEREidIN(1,2,7)RunningtheabovequerywillproducethesameresultsasshowninFigure6.20buttheyhavebeenselectedinacompletelydifferentway.WecanselecttherowsthatarenotinthatIDsetbyaddingaNOTtotheclauseasfollows:SELECTid,titleFROMwebpageWHEREidNOTIN(1,2,7)Thiswillreturnthecomplementarysetofrows.NotewheretheNOTkeywordappearsintheclause.Asdifferentpositionscausedifferentresultsets,itisalwaysagoodideatorunthequerythroughanSQLclientbeforeyouuseitinawebsitesothatyoucanensurethatyouaregoingtogettheresultsthatyouexpect.SubqueriesVersion4.1ofMySQLnowincludestheabilitytoperformsubqueries.Asubqueryisaquerythatisruntoderiveavaluethatisfedintothemainquery.Forinstance,thefollowingquerywasusedtoextractthethreerowsshowninFigure6.20:SELECTid,titleFROMwebpageWHEREtitleLIKE“Home%” 72DatabaseDesignManual:usingMySQLforWindowsWecanmodifythissothatitwillonlyextracttheidoftheserowsasfollows:SELECTidFROMwebpageWHEREtitleLIKE“Home%”Thiswillproduceavaluesetof(1,5,7).Toextractallofthelogentriesusingthisvaluesetwecanusethefollowing:SELECT*FROMlogWHEREwebpageidIN(1,2,7)However,intheabovewearehardcodingthevaluesetthatwearesearchingfor.Wearemanuallyenteringthevaluesthatwegotforthefirstqueryintothesecond.Ifweweretochangethevaluethatwearesearchingforwithinthefirstquery,thenthevaluesetreturnedmaydiffer,andtheresultsinthesecondquerywillbeinvalidunlesswere-writethesecondquerywiththenewvalues.Toautomatethepassingofvaluesbetweenthesequeries,weusetheoutputofonequeryasasubqueryinanotherqueryasfollows:SELECT*FROMlogWHEREwebpageidIN(SELECTidFROMwebpageWHEREtitleLIKE“Home%”)Thesubquerymustbecontainedinbracketsandmustreturnvaluesthatcanbeusedbytheparentquery. JoiningTables7InformationinMultipleTablesWespentalotoftimewhileweweredesigningourtablesremovingredundantorduplicatedatafromourdatabase.Insteadofinsertingmultipleinstancesoftextintoatable,weputthetextonceintoanothertable,andtheninsertedtheprimarykeypointingtothatdataintothetablethatwouldhavecontainedtheduplicates.Thisleadstoveryefficientlystoreddata,butmeansthatwehavetojointhetablestogetthedatabackfromtheminareadableform.Forinstance,Figure7.1showsallthedatathatisstoredinourlogtable.Noticethatthesecondandthirdcolumnscontainforeignkeysfromthecookieandwebpagetables.ItmaybeeasytorememberthatwebpageIDoneistheHomepagebutwhatabouttheotherIDs?AlsowhatissospecialaboutcookieIDtwo?Itseemsthattheyaretheonlypersonthathaslookedatthewebsite,sodidtheyentertheirnameinthevisitorbookaswell?Wecananswerthesesortofquestionsbyjoiningtablestogether.TherearetwobasictypesofjoinsinSQL:•theinnerjoin,and•theouterjoin.Figure7.1TheLogtablecontents.73 74DatabaseDesignManual:usingMySQLforWindowsCrossJoinsThesimplestwayofjoiningtwoormoretablestogetherisbyspecifyingmorethanonetableaftertheFROMkeywordasfollows:SELECTcolumnsFROMtable1,table2,etcHowever,thatseldomproducestheeffectthatyoumightexpect.TryjoiningtheLogtabletothewebpagetableasfollows:SELECT*FROMlog,webpageFigure7.2showstheresultsofthisjoin,calledthecrossjoin.Canyouseewhatthisjoinhasdone?Itisprobablyeasierifyoulookatthisonthescreenasitisdifficulttoshowalloftherowsinthefigure.Lookathowmanyrowsthequeryhasreturned.Byselectingtwotables,containingfiveandsevenrowsrespectively,thequeryhasgenerated35rowsasaresult.ThisisbecausethecrossjoinlinkseveryrowintheLogtablewitheveryrowinthewebpagetable(5×7=35).Ifyouscrollalongtheresultsyouwillseethatthecontentsoftherowsofbothtablesarerepresentedineachrowoftheseresults.Nowscrollacrossandlookatallthecolumnsthatthequeryhasreturned.Youwillseethateachrecordhasreturnedarowthathaseverycolumnofeverytablespecifiedinit.YouFigure7.2Thecrossjoin. Chapter7•JoiningTables75mightthinkthatyoucanrestrictthisbyspecifyingonlycertaincolumnnamesintheselectstatement.Thisworkstoanextent,asyoucanseeifyourunthefollowingquery:SELECTcookieidFROMlog,webpageEventhoughtheresultsnowcontainonlyonecolumn,thequeryhasstillreturned35rows,ascanbeseeninFigure7.3.Becarefulwhenusingcrossjoins;theyareoflittleuseandcancauseincredibleper-formancehitsonyourdatabasesystemifyoujoinbigtablestogether.Trycrossjoiningyourwebpage,log,cookiesandvisitorbooktablestogetherandyouwillseehowquicklythiscangetoutofhand.Joiningafewlittletablesproducesahugeresultset.InnerJoinsThecrossjoinasdescribedaboveisaninnerjoin.Simplyput,innerjoinsrequireamatchbetweenbothtablesthatarebeingjoined.Ifnomatchisspecifiedthenallrowsarematchedsuchasinthecrossjoin.Equi-joinAninnerjointhatismoreusefulthanthecrossjoinistheequi-join.Inanequi-joinyouspecifysomethingtomatchbetweenthetablesbeingjoined,whichresultsinamuchmorecontrolledjoin.Thebasicformatofsuchajoinisasfollows:SELECTcolumnsFROMtable1,table2,etcWHEREconditionFigure7.3Acrossjoinrestrictedtoasinglecolumn. 76DatabaseDesignManual:usingMySQLforWindowsInthiscasetheconditionwillcheckforamatchingpieceofdatainacolumnthatappearsinthetablestobejoined.Mostlikelythiswillbematchingaforeignkeyinonetablewithaprimarykeyinanother.Thelastparagraphmayseemabitconfusing,butaseverwithSQLitsmucheasiertodemonstratebyexample,sowewillturnthepreviouscrossjoinintoanequi-joinbyaddingaWHEREclauseasfollows:SELECT*FROMlog,webpageWHEREwebpage.id=log.webpageidFigure7.4showstheresultsoftheabovequery.NoticehowtheWHEREclauserestrictedthenumberofrowsofthequeryto5,andgotallandmoreofthedatathatwewantoneachrow.NoticethatintheWHEREclausewespecifiedthetablenameaswellasthecolumnnameforthematch.Itisnotalwaysnecessarytodothisbutifyougetintothehabitofdoingsoitcansaveyoualotoftimedebugginglater.Forinstance,inourexample,ifwehadjustspec-ifiedtheIDcolumnwithoutthetablename,wecouldhavebeenreferringtotheIDcolumnineitherthewebpagetableorthelogtable,astherearetwowiththesamename.IfyoucastyourmindbacktothestartofthischapteryouwillrememberweweretryingtogetmoreinformationfromthelogtablesothatwecouldseethetitleofthepagethatwewerelookingforasopposedtoonlyitsID.Weneededtojointhepagestogethertofindthisout.Wecannowtidyupthisequi-joinbyrestrictingthecolumnswereturnasfollows:SELECTwebpage.title,log.*FROMlog,webpageWHEREwebpage.id=log.webpageidFigure7.5showstheresultsofthisequi-join.Whatthejoinhasdoneistakeeveryrowfromthelogtable,whichcontainstheforeignkeypointingtotheentryinthewebpagetable.Itthenhasincludedtherelevantcolumnfromthewebpagetable(title)whichmatchesthatforeignkey.Figure7.4Abasicequi-join. Chapter7•JoiningTables77IfyoulookatthequeryagainaftertheSELECTkeyword,youwillseethatwehavespec-ifiedwhatwewantfromeachtable.Theentry,log.*isselectingeverythingfromthelogtable,whereas,webpage.titlewilljustselectthesinglecolumntitlefromthewebpagetable.Onapreviousqueryweselectedallcolumnsfromalltablesusingtheasterisk.Youcanseethatusingtheasterisktoselecteverythingwouldbethesameasusing,log.*,webpage.*Wewilldoonemorethingtomakeourresultstidier.IfyoulookatFigure7.5againyouwillseethatbyselectingeverythingfromthelogtablewealsoselecttheforeignkeywebpageID.Aswealreadyhavethetitleofthewebpagewhichismoreuseful,wedonotneedtoreturnthatforeignkeyaswell.Unfortunately,thismeansthatwewillhavetospec-ifyalloftheothercolumnsinthelogtableinsteadofjustusingtheasterisk.Wewillshuffletheorderofthecolumnsslightlyanduseanaliasaswellsoastomaketheoutputlookmorelikethelogtable.Ourfinalscriptwillnowreadasfollows:SELECTlog.idaslogid,log.cookieid,webpage.titleASpagetitle,log.browser,log.datecreated,log.ipnumber,log.referringpageFROMlog,webpageWHEREwebpage.id=log.webpageidFigure7.6showstheresultsofourcompletedquery.We’vegiventhelog’sidcolumnthealiaslogidintheoutputtosaveitgettingconfusedwithanyotheridcolumnselsewhere.WeFigure7.5Anequi-joinwithselectedcolumns. 78DatabaseDesignManual:usingMySQLforWindowsFigure7.6Thecompletedequi-joinquery.havedonethesamewiththewebpage’stitlecolumnsothatwhatwearereferringtobecomesclearer.Youwillalsonoticethatwedonothavetoreturnacolumnthatweuseinthematchcon-ditions.Inourexample,weusedwebpage.idinthematchquerybutfilteredthatcolumnoutoftheactualresultsbynotspecifyingitinouroutputcolumnlistaftertheSELECTkey-word.Equi-joinsonMoreTablesTodemonstratefurther,youcanuseanequi-jointoinnerjoinmorethanonetable.Wewillnowjointheresultsweobtainedabovewiththecookietable,sothatwecangetthedatethatthepersonviewingthepagefirstlookedatoursite.Thiswillshowiftheyarevisitingforthefirsttimeorhavecomebacktolookagain.TodothiswewilltaketheabovequeryandaddanothercolumnmatchclausetotheWHEREstatement,andrenameafewcolumnsforclar-ity:SELECTlog.idaslogid,webpage.titleASpagetitle,log.browser,log.datecreatedASlogdate,log.ipnumber,log.referringpage,cookies.datecreatedAScookiecreatedFROMlog,webpage,cookiesWHEREwebpage.id=log.webpageidANDcookies.cookieid=log.cookieidFigure7.7showsthisqueryworking.Bycomparingthedatestampofthelogcolumn,aliaslogdate,withthecookiecreationdate,aliascookiecreated,wecannowseeiftheviewerislookingatthesiteforthefirsttimeorreturning. Chapter7•JoiningTables79Figure7.7Anequi-joinquerywiththreerows.RestrictingEqui-joinsHowever,ourlogtablestillonlyhasafewrows.Inthissituationitiseasytolookthroughtheresultset.Whatifwehadhundredsofrowsreturnedbythisquery?IfthiswasthecasewecanjustfurtherfiltertherowsbyaddinganotherconditiontotheendoftheWHEREclause:SELECTlog.idASlogid,webpage.titleASpagetitle,log.browser,log.datecreatedASlogdate,log.ipnumber,log.referringpage,cookies.datecreatedAScookiecreatedFROMlog,webpage,cookiesWHEREwebpage.id=log.webpageidANDcookies.cookieid=log.cookieidANDlog.webpageid=1Figure7.8showsthisqueryrunning.WehaverestrictedthewholeoutputbylookingfortheIDof1(themainHomepage)intheresults.Inourexamplethishasreducedfiverowstoonlythreerows,butwecouldcontinueaddingclausestoreducethenumberfurther.Forinstance,wecouldalsorestrictbybrowserorreferringpagetoseethecharacteristicsofpeopleviewingourwebsitesfromdifferentlinks.INNERJOIN–AnotherFormatLookagainattheendofthelastquery:WHEREwebpage.id=log.webpageidANDcookies.cookieid=log.cookieidANDlog.webpageid=1 80DatabaseDesignManual:usingMySQLforWindowsFigure7.8Anequi-joinwithrestrictedrowsreturned.Youwillnoticethatreallytherearetwodifferentmeaningstothethreedifferentclausesused.Thefirsttwo,webpage.id=log.webpageidANDcookies.cookieid=log.cookieidareusedbythetwojoinstomatchprimaryandforeignkeysindifferenttables,whereasthelast,log.webpageid=1isjustastandardrestrictionthatwouldworkonanormalSELECTstatementthatdidnotcontainajoin.Sometimesthiscangetconfusingsoitisusefultoknowofanotherformatoftheinnerjointhatworksasfollows:SELECTlog.*,webpage.titleFROMwebpageINNERJOINlogONwebpage.id=log.webpageidThisquerywillproducethesameresultsasdisplayedinFigure7.3.Ifwewantedtorestricttherowsthatweweregettingbackfromthequery,inthisinstanceweshouldnotappendanotherclauseontheendoftheONcondition,wewouldneedtoaddaWHEREclauseasfollows:SELECTlog.*,webpage.titleFROMwebpageINNERJOINlogONwebpage.id=log.webpageidWHERElog.webpageid=1UsingthisformatclearlysetsadistinctionbetweenarestrictionWHEREconditionandaJOINconditionandsocanleadtoqueriesthatareeasiertoread.OnsomedatabasesystemsitisadvisabletousetheINNERJOINformatratherthantheoneshownpreviously,astheDBMShasspecialcodeforperformingtheINNERJOINthat Chapter7•JoiningTables81itonlyusesifinthisformat.Thismayresultinyourjoinqueriesrunningfaster.Ifnothingelse,itsavealotofconfusingWHEREclauses!OuterJoinsTheinnerjoinallowsyoutojointwotableswhichhavematchingdataincertainrows.Inanouterjoin,thewholeofonetableisreturned,alongwiththematchingrowsinanothertable.Thefirsttableisreturnedregardlessofwhetheranythingmatcheswithitinthesec-ondtable.Ifthatsoundsconfusingtoyouthendon’tworrytoomuch.Asusualafewexam-pleswillmakeitclearer.LEFTJOINTheLEFTJOINisanouterjointhatusestheformatthatwejustintroducedinthesectiononINNERJOINasfollows:SELECTcolumnsFROMfirsttableLEFTJOINsecondtableONfirsttable.column=secondtable.columnTodemonstratetheleftjoin,wehavetoexaminetheresultsfromthefollowingquery:SELECTwebpage.title,log.datecreatedFROMlog,webpageWHEREwebpage.id=log.webpageidYoushouldnowrecognizetheaboveasanequi-join.Figure7.9showstheresults.Whatwearetryingtodisplayisalistofallofthewebpagesthatwehaveonoursiteandarelevantlogentryforeachpage.However,ifwelookatFigure7.9wecanseethatwehaveFigure7.9Anequi-joinfailstoshowallwebpages. 82DatabaseDesignManual:usingMySQLforWindowsFigure7.10Alloftheentriesinthewebpagestable.theHomepagelistedfourtimesandtheLinkspageonce.Weareonlydisplayingthepagetitleforrowsinourlogtable:therowsthatmatch.Figure7.10willremindyouofalloftheentriesinthelogtable.Soweneedtoconvertthisequi-joinintoaleftjointogetthedesiredresults.Executethefollowingquery:SELECTwebpage.title,log.datecreatedFROMwebpageLEFTJOINlogONwebpage.id=log.webpageidLookattheresultsinFigure7.11.YouwillseethatwestillhavealloftherowsthatappearedinFigure7.9,withtheadditionoftheextrarowsinthewebpagetable.Asthereisnocorrespondingentryinthelastfourrows,theMySQLserverhasreturnedaNULLforthevalueinthedatecreatedcolumn.Ifwelookatthequeryagain,wecanseewhywecallitaleftjoin:SELECTwebpage.title,log.datecreatedFROMwebpageLEFTJOINlogON….Thishasbeenformattedinadifferentwaysothatyoucanseethatthewebpagetableisontheleftofthelogtableasitiswritten.Alloftherowsinthelefttable–thewebpagetable–willbereturnedwiththematchingrowsintherightmosttable.Thisisagoodwaytorememberwhichcolumndoeswhichinaleftjoin.Theleftmostcolumninthequerywillreturnallitsrowsirrespectiveofmatchesintherightcolumn.Letusnowswapthetwocolumnsaroundinthequery,sothatwemakethelogtabletheleftmost:SELECTwebpage.title,log.datecreatedFROMlogLEFTJOINwebpageONwebpage.id=log.webpageid Chapter7•JoiningTables83Figure7.11Aleftjoin.Thistimethisqueryissaying:“Showmeeverytitleinthewebpagetablethatmatchesarowinthelogtable,andalloftherestofthedatecreatedentriesinthelogtable”.Figure7.12showsthisqueryrunning.Itactuallygivesthesameresultsetastheequi-jointhatweranatthestartofthischapter,butithasbeenexecutedasaleftjoin.Aseveryrowinthelogtablemustmatcharowinthewebpagetable,tomaintainreferentialintegrity,noNULLswillappear.RIGHTJOINAswehavealeftjoin,itfollowsthatwewillalsohavearightjoin.Theformatofarightjoinissimilartothatofaleftjoin,asbelow:SELECTcolumnsFROMfirsttableRIGHTJOINsecondtableONfirsttable.column=secondtable.columnThisqueryissaying:“Showmeeverycolumninthefirsttablethatmatchesacolumninthesecondtable,andalloftherestofthesecondtableentries”.We’llrunthiswithourpre-viousexampleagain:SELECTwebpage.title,log.datecreatedFROMlogRIGHTJOINwebpageONwebpage.id=log.webpageidIfyouhavestillgotthescriptonthescreen,justchangetheLEFTkeywordtoRIGHTandrunitagain. 84DatabaseDesignManual:usingMySQLforWindowsFigure7.12Leftjoinswappingthetables’positions.Againbyformattingthequeryinalinewecanseehowitworksinaclearerway:SELECTwebpage.title,log.datecreatedFROMlogRIGHTJOINwebpageON….Thistimeitistherightmosttable,webpage,whichhasallofitsrowsreturned,withonlythematchingrowsinthelefttable,log,intheresultset.Ifyouhavejustworkedthroughthepreviousexamples,youwillnoticethatrunningthisqueryproducesthesameresultastheleftjoinshowninFigure7.11whenthetableorderisreversed.Thisshowsaninterestingfunctionoftheouterjoin.Thefollowingtwoscriptswillpro-duceidenticalresultsets:SELECTcolumnsFROMfirsttableRIGHTJOINsecondtableONfirsttable.column=secondtable.columnSELECTcolumnsFROMsecondtableLEFTJOINfirsttableONfirsttable.column=secondtable.columnIfyouunderstandthis,youwillrealizethatthereisnotactuallyaneedforasystemtoimplementboththeleftandrightjoin,asyoucanaccomplishbothbythere-orderingoftableorderwithinthequery.MySQLimplementsbothofthesejoinsbutsomeotherSQLsystemsonlyimplementoneofthem.Thoughnotnecessary,thetwotypesofjoinmakequerybuildingeasierwhenjoiningmorethantwotableswithouterjoins. Chapter7•JoiningTables85UNIONTheUNIONkeywordallowsyoutojointworesultsetstogether.Theresultsetsmusthavesimilarcolumnnames.TheUNIONfunctionisusedasfollows:SELECTcolumnsFROMtablesWHEREconditionUNIONSELECTsimilarcolumnsFROMtablesWHEREconditionWecandemonstratethisquicklybycombiningaquerythatselectsalllogIDslessthan3fromthelogtablewithanotherthatselectsalllogIDsthatrefertotheHomepageasfol-lows:SELECT*FROMlogWHEREID<3UNIONSELECT*FROMlogWHEREwebpageID=1Figure7.13showstheresultsofthatquery.MySQLhastakentheresultsofbothofthequeriesandjoinedthemintoonetable,removingtheduplicaterow.(ThetwoindividualquerieswouldbothhavereturnedrowID=1.)ButthatquickexampledoesnotillustratethefullpotentialofusingtheUNIONkey-word.Youmayrealizethattheabovequerycouldbere-writtenwithaconventionalWHEREclauseasfollows:SELECT*FROMlogWHEREID<3ORwebpageID=1Figure7.13JoiningtwoquerieswithUNION. 86DatabaseDesignManual:usingMySQLforWindowsFigure7.14TwodifferenttablesjoinedwithUNION.Theabovecanbere-writtenasitisselectingdatafromthesametable.ThebeautyoftheUNIONcommandisthatitcanjoindatafromdifferenttables,aslongasthecolumnsyouarechoosinghavesimilardatatypes.Forexample,boththecookiestableandthelogtablehaveIDfieldsanddatefields.HowwouldwecombinetheIDandthedatesfrombothtablesintooneresultsset?Youcouldattemptitusingajoinasfollows:SELECTlog.ID,log.datecreated,cookies.cookieid,cookies.datecreatedFROMlog,cookiesHowever,theabovewillproducealargeresultset,asitisanunrestrictedjoin,everyrowinthelogtablereturnedwitharowfromthecookiestableproducing30rowsasaresult.Wejustwanttheresultswiththerowsfrombothofthetwotables.Wewilltrythisqueryusingaunion:SELECTID,datecreatedFROMlogUNIONSELECTcookieid,datecreatedFROMcookiesThisproducesaUNIONofthetwodifferenttablesontheIDanddatecreatedcolumns.TheresultsareshowninFigure7.14.Ifyouhavebeenworkingthroughalloftheexamplesinthisbook,yourcookietableshouldcontain6rowsandyourlogtable5rows.However,theresultset,showninthefig-ure,onlycontains10rows.ThisisbecausethedatecreatedandtheIDfieldsforbothtablesarethesameforID=1.MySQLhasthereforetreatedthisasduplicateentryandremovedoneoftheduplicatesintheresultset.Aswehaveneverspecifiedthetimewhencreatingthesefields,MySQLalwaysdefaultsto00:00:00.Ifourexampleshadusedmoreaccuratedate/timefields,thedifferenceinthetimecolumnswouldhavestoppedthisbeingtreatedasaduplicaterow. ChangingData8AlteringDataOnceyouhavepopulatedyourtableswithdata,thatisseldomtheendoftheprocess.Althoughmostwebsitesaccumulatemuchdatathatdoesnotchangeovertime,suchaspageaccesslogsandcookietables,otherdatachanges.Onawebsitethatallowsuserstologon,forexample,usersmaywishtochangetheirpasswordortheiremailaddress.Onadata-base-drivenwebsite,changestothecontentsofthepageswillrequirechangestotheunder-lyingdatabasethatcontainsthepages.SQLthereforehasvariouscommandsthatallowyoutochangethedatathatyouhavepreviouslystoredinyourtables.Thischaptercontainssomecommoncommandsthatareusedforthispurpose:•UPDATEallowsyoutochangethecontentsofdatawithinatable.•ALTERallowsyoutomakechangestothetablestructureonceyouhavecreatedit.•REPLACEpreventserrorswhencreatingthingsthatmayalreadyexist.Wewillnowdescribethesecommands.UPDATEUPDATEallowsyoutochangethecontentsofpartsofatablewithoutalteringitsstructure.ThebasicformatoftheUPDATEcommandisasfollows:UPDATEtablenameSETcolumnname=numericvalue,columnname=”textvalue”WHEREconditionAsyouworkthroughthebookyoumighthavefounditfrustratingthatourwebpagetablecontainshardlyanycontent,justthenameofeachpage.Selecteverythingfromthewebpagetabletoremindyourselfofitslimitedcontents.Figure8.1showsthetable.YouwillnoticefromFigure8.1thatthecontentcolumnislargelyfilledwithNULLs.WewillusetheUPDATEkeywordtoremedythisandgiveitsomemorerelevantdata.Thefol-lowingscriptwilladdsometexttothecontentcolumn:87 88DatabaseDesignManual:usingMySQLforWindowsUPDATEwebpageSETcontent=“Myemailissomeone@nowhere.com”TheresultofrunningtheaboveisshowninFigure8.2.YouwillnoticethattheUPDATEcommanddoesnotgiveyouthenormalresultsgrid,asthecommandchangesdatabutdoesnotreturnit.Theonlyfeedbackthatyougetisinthestatusbaratthebottomofthebox.YouwillseeinFigure8.2thatfortheabovequeryithasmatchedandchangedsixrows.ThiscouldbeaproblemasweonlywantedtochangetheonerowfortheContactMepage.ThisexamplehasbeenusedtoshowhowdangeroustheUPDATEquerycanbewithouttheWHEREclause.SelecteverythingfromthewebpagetableagainandyouwillseetheresultsoftheunrestrictedUPDATE,asinFigure8.3.Allofthecontentcolumncellshavebeenreplacedwithouremailaddress.Figure8.1Ourcontentlackingwebpagetable.Figure8.2Whenupdating,feedbackappearsonthelastline. Chapter8•ChangingData89Figure8.3ThedamagecausedbyomittingtheWHERE.Thankfully,ourexampledatabaseonlyhassixrows,onlyoneofwhichhadsomemean-ingfuldata,sowewillnothavetodomuchworktofixthisproblem.Imagine,however,theamountofworkthatwouldbeinvolvedifwehadusedasimilarqueryonacommercialwebsitewithhundredsorthousandsofpages!ThisexampleshowsthatitisbesttogetintothehabitofalwaysusingaWHEREclausewiththeUPDATEquery.TrytothinkofthequeryasUPDATE,SET,WHEREasopposedtoUPDATE,SETanditmaysaveyoualotofwork.Ihopethisisthelasttimeyouwillseethishappenandyouwillnotmakethismis-takewhenitmatters!Tofixthis,wewillfirstclearoutthewrongdatabyusingtheUPDATEwithouttheWHEREforonelasttime:UPDATEwebpageSETcontent=“”Theabovequerysetsallofthecellsinthecontentcolumntotheemptystring.Nowwewillre-runtheUPDATEqueryforrow6butthistimeusetheWHEREclause:UPDATEwebpageSETcontent=“Myemailissomeone@nowhere.com”WHEREID=6Afterrunningthequeryabove,selecteverythingfromthewebpagetableandyoushouldhavethemoresatisfactoryresultsasshowninFigure8.4.Wehavestilllostthedatathatwasinrow2butwewillremedythatnext.NoticethatbyusingtheWHEREclausetomatchtheprimarykeyfield(ID)ofthetablewewereabletorestrictourupdatetoexactlytherowthatwewishedtochange.Nowthatwehavesuccessfullychangedthedatainonecolumn,wecanaddsomecontenttoalloftherowsinthewebpagetableonebyone.Thefollowingscriptwilldothisforyou: 90DatabaseDesignManual:usingMySQLforWindowsUPDATEwebpageSETcontent=“WelcometomyMySQLbasedwebsite”WHEREID=1;UPDATEwebpageSETcontent=“Pleaseaddacommenttomyvisitorbook”WHEREID=2;UPDATEwebpageSETcontent=“Herearesomelinksyoumayfindofuse”WHEREID=3;UPDATEwebpageSETcontent=“HereiswhatIhavedonewithmyworklifeuptillnow”WHEREID=4;UPDATEwbpageSETcontent=“Myhobbiesare,webprogramming,anorakspottingandfishcollecting“WHEREID=5;Figure8.5showswhatthewebpagetablewilllooklikeafteryouhaveselectedeverythingfromitagain.Ourwebsite’sdatabasenowhassomecontent.ALTERTheALTERcommandisastrangecommandtohaveinSQLasitcanbearguedthatitisnotneeded.Thiscommandallowsyoutochangethestructureofatableafterithasbeencre-ated.Theargumentisthatifyouhavecorrectlycompletedtheanalysisanddesignofyourdatabase,youshouldnotneedtoalteritsstructureatalatertime.However,therealworldisseldomlikethis,wemakemistakes,andgoalsandrequirementschange,soitisagoodFigure8.4FixingourunconstrainedUPDATE. Chapter8•ChangingData91Figure8.5Thecorrectlyupdatedwebpagetable.thingthatwehavetheALTERcommand.ThegeneralformatofanALTERqueryisasfol-lows:ALTERTABLEtablenamecommandtypecolumnnameparametersTheALTERTABLEcommandcandoseveralthings,dependingonwhatyoutypeinthecommandtypeposition.Commandtypecanbeoneofthefollowing:•ADDaddsanewpropertyorcolumntothetable.•RENAMEchangesthenameofthetable.•CHANGEchangesthenameofacolumnanditsdatatype.•MODIFY,changesthedatatypeofacolumn.•DROPremovesthecolumnfromthetablecompletely.ThenextfewsectionswilldemonstratethesedifferenttypesofALTERquerywithourvisitorbooktable.Toremindourselvesofthistable,runthefollowing:DESCRIBEvisitorbookFigure8.6showsthecurrentformatofthevisitorbooktable.Aswecreatedthisinarushandnotinourchapteronthedesignofdatabases,wemadeafewmistakes.WewillchangesomeofthecolumnsandaddanextraoneusingALTER.Asafinalreminder,Figure8.7showsthecurrentcontentofthetable,afterselectingeverythingfromit.ALTERTABLEADDTheALTERTABLEADDqueryhasthefollowingbasicformat:ALTERTABLEtablenameADDcolumnnamedatatype 92DatabaseDesignManual:usingMySQLforWindowsFigure8.6Thecurrentformatofthevisitorbooktable.Figure8.7Thecurrentcontentofthevisitorbooktable.Onourvisitorbookpageonthewebsite,itwouldbenicetoaskthevisitortogiveussomeideaofwheretheyarebasedgeographically.Wewillstorethisinalocationcolumn.Wewillalsoallowthemtorateoursitewithascorebetween1and20.Forthelocationcol-umn,weneedtostoreastring.Itishardtoguessthelengthofthisdirectlybutletussaythiswouldbenomorethan50characterslong.Thereforeweusethefollowingtoaddalocationcolumntothetable:ALTERTABLEvisitorbookADDlocationVARCHAR(50)Forthescorecolumn,wewillonlybestoringalimitedrangeofnumbers,sowecanjustuseaSMALLINTdatatypetostorethescore.Weaddtheextracolumnasfollows:ALTERTABLEvisitorbookADDscoreSMALLINT Chapter8•ChangingData93Figure8.8Thetwonewcolumnsinthevisitorbooktable.Youmaynoticefromtheabovethattheformattoaddatablefollowsthesameformataswhenwewerecreatingatable.Wecanaddotherparameterstothecommand,shouldtheybeneeded,thatallowthecolumntobecomeaforeignkey,orallownulls.Forourexampleswedonotneedtospecifyanyadditionalparameters.Havealookatthechapteroncreatingdatabasestoremindyouoftheseextraparametersifyouneedto.DescribethewebpagetableagainandyouwillseetheresultsshowninFigure8.8.YouwillseefromFigure8.8thatthevisitorbooktablenowcontainsthetwonewrows.TheADDkeywordcanalsobeusedtoaddotherthingstothetable,suchasprimarykeys,aswellasindexes.Toaddaprimarykeytoatablethatdoesnotalreadyhaveone,use:ALTERTABLEtablenameADDPRIMARYKEYcolumnnameOfcourse,thecolumnthatyouaresettingtheprimarykeyformusthaveuniquedataineachfieldforthecolumntobespecifiedassuch.Toaddanindextoacolumnuse:ALTERTABLEtablenameADDINDEXindexname(columnname)ALTERTABLERENAMETheALTERTABLERENAMEqueryallowsyoutochangethenameofatable.Ithasthefol-lowingbasicformat:ALTERTABLEtablenameRENAMEnewtablename 94DatabaseDesignManual:usingMySQLforWindowsTherefore,torenamethevisitorbooktabletovisitorsbook,wewouldexecutethefollow-ingquery:ALTERTABLEvisitorbookRENAMEvisitorsbookAndtochangeitback:ALTERTABLEvisitorsbookRENAMEvisitorbookRENAMEonlyallowsyoutochangethenameofthewholetable.OnewaythatyoucanchangethenameofacolumnisbyusingCHANGEALTERTABLECHANGETheALTERTABLECHANGEqueryallowsyoutochangethenameofacolumnaswellasitsdatatype.Ithasthefollowingbasicformat:ALTERTABLEtablenameCHANGEoldcolumnnamenewcolumnnamedatatypeCHANGErequiresyoutospecifytheoldandnewcolumnnamesevenifyouarenotchangingthenameofthecolumn,inwhichcasetheycanbothbethesame.Likewise,itrequiresyoutospecifythedatatypeevenifyouarenotchangingit.Forexample,ourcolumnlastnamecouldbeconfusingtosomecultures,sowewillchangeittofamilynameinstead.Todothis,runthefollowingquery:ALTERTABLEvisitorbookCHANGElastnamefamilynameTEXTNoticehowwestillhavetospecifythedatatypeeventhoughwearenotchangingit.Whilewearelookingatthatcolumnthough,doyouseeaproblemwiththedatatype?WeareusingaTEXTfield,whichcancontainstringsofvaryinglength,tostoreastringthatinallprobabilityisonlytensofcharacterslong.Letusmakethearbitrarydecisionthatafam-ilynamewillneverbelongerthan40characters,sochangethetypetoaVARCHARusingthefollowingscript:ALTERTABLEvisitorbookCHANGEfamilynamefamilynameVARCHAR(40)TheCHANGEcommandallowsyoutochangethecolumnnameanddatatypeatthesametimeaswellbyspecifyingthetwodifferentcolumnnamesandthenewdatatype.YoucouldchangethefirstnamecolumntoaVARCHARaswellwiththefollowing: Chapter8•ChangingData95ALTERTABLEvisitorbookCHANGEfirstnamefirstnameVARCHAR(40)However,thenextcommandshowsaneasierwaytochangethedatatypeifthatistheonlychangeyouwishtomaketothecolumn.ALTERTABLEMODIFYMySQLhasgivenustheALTERTABLEMODIFYquerythatsavesusabitoftypingifweareonlygoingtochangethedatatype.Thishasthefollowingbasicformat:ALTERTABLEtablenameMODIFYcolumnnamedatatypeTouseMODIFYtochangethedatatypeofacolumnwenolongerhavetotypethecol-umnnametwice,sotochangethefirstnamecolumnwecanuse:ALTERTABLEvisitorbookMODIFYfirstnameVARCHAR(40)Wehavenotlookedattheformatofourtableforawhile,soFigure8.9showsourchangestodatewithadescribevisitorbookquery.ALTERTABLEDROPTheALTERTABLEDROPqueryhasthefollowingbasicformat:ALTERTABLEtablenameDROPcolumnname,columnnameYoucandeleteonecolumnorseveralbyseparatingthecolumnnameswithcommas.LookingatFigure8.9maymakeyouwonderwhywedidnotchangethedatatypeofthemiddlenamecolumn.Thiscolumnisawaitingaworsefate!IfyouturnbacktoFigure8.7youwillnoticethatthereisnodatabeingstoredatallinthemiddlenamecolumn,andwithhindsight,wehavenorealneedtoobtainthatinformationfromourwebsitevisitors.Currentlythatwillbejustwastingspacewithinourdatabase.Wecanremovethemiddle-namecolumnbyusingthefollowingscript:ALTERTABLEvisitorbookDROPmiddlenameIfyourunthatscriptyoumaybestartledbyhowquicklyitdropsthecolumn.Bearinmindthatwhenyouremovecolumnsitisnotlikeamoderninterfacewhichhappilyasks 96DatabaseDesignManual:usingMySQLforWindowsFigure8.9OurALTERedvisitorbooktable.youifyouaresurethatyouwanttodeletesomething.Therearenosecondchances,allofthecolumn’sdatawillbedestroyedwhenyoudropitassoonasyouissuethecommand.YoucanalsousetheALTERTABLEDROPquerytoremoveanindexoraprimarykeyfromatable.Toremovetheprimarykeyuse:ALTERTABLEtablenameDROPPRIMARYKEYThisdoesnotremovetheactualcolumn,butremovestheprimarykeyflagfromthatcol-umn.Toremoveanindexuse:ALTERTABLEtablenameDROPINDEXindexnameDroppingtheprimarykeyortheindexonatableisnotasdangerousasdroppingawholecolumn,aswhenyoudothisyouwillnotloseanydata,andtheindexorprimarykeycanbeaddedbackonagainafterwardsifyouneedto.GetitRighttheFirstTime!YoumayhavenoticedalotofdiskactivitywhenyouwererunningsomeoftheseALTERTABLEcommands.Thisisbecauseitisveryhardforthedatabasesystemtochangeatablethathasalreadybeencreated.Thewaythatitgetsaroundthisproblemcanbeveryproces-sorintensive,astheservercreatesanewtablewiththechangedcolumn,copiesthedatafromtheoldtabletothenewone,deletestheoldtableandfinallyrenamesthenewtabletothesamenameastheoldone!Onabigtable,runninganALTERcommandmaytakealong Chapter8•ChangingData97time,allthemorereasonwhyyoushouldendeavourtocreatethetableaccuratelyinthefirstinstance.ManyofthesecommandsarealsoaMySQLadditiontotheoriginalANSISQL92defini-tion,somaynotworkonotherdatabasemanagementsystemsthatareonlycompliantwiththisstandard.UPDATERevisitedIpreviouslywarnedofthedangersofdoinganunrestrictedupdate,thatis,anUPDATEwithouttheWHEREclause.However,Iwillnowshowyouatimewhensuchanupdateisuseful.Whenwewereaddingcolumnstoourvisitorbooktableweaddedascorecolumn.Itwouldbeusefultoaddsomedatatothatcolumnforuselateron.Wecouldjustsetallofthecolumnstothesamevalue,butinsteadwewillusethedatastoredinonecolumnto‘seed’thedatainthescorecolumn.Again,wecouldjustcopyfromonecolumntothenextbutwewillchangeitslightlybymultiplyingthesourcecolumntogetthenewone.Thissoundscomplex,sorunthefollowingscript:UPDATEvisitorbookSETscore=entryID*2Foreachrow,thisquerywilltakethevalueoftheentryIDfield,multiplyitby2,andstoreitinthescorefieldofthatsamerow.Toviewtheresults,runthefollowing:SELECTentryID,scoreFROMvisitorbookFigure8.10showstheresultsofourupdate.LookingatFigure8.10,youcanseethatwenowhaveadifferentscoreineachrowofthetable,andeachscoreistwicethevalueoftheentryID.WejustusedtheentryIDasachang-ingnumbertoseedthescorecolumn,toshowthatanunrestrainedUPDATEdoesnotnec-essarilyhavetoresultinalltheupdatedfieldshavingthesamevalue.Figure8.10UPDATEcalculatingvaluesforeachrow. 98DatabaseDesignManual:usingMySQLforWindowsAlthoughweareusingthisexampleto‘fix’thescoreofourwebsite,thistypeofupdatecanbeusefulifwerealizethatwehaveaccumulatedalotofdatathatneedschanging.Forinstance,inane-commercesystem,youmayhavebeenstoringthetotalcostforeachorderwithoutsalestax,andthendecidethatitshouldbewithtax.ItwouldbeeasyenoughtochangeyourPHPcodetostorethetotalvalueplusthetax,butwhataboutthedataalreadyinthetable?Youcancreateanewcolumninthetableforthenewtotal,useanunrestrictedupdatetomultiplytheoldtotalcolumnbythetaxamountandplacethedatainthenewcol-umn.Youthendroptheoldcolumn,renamethenewoneandyourdataisnowstoredcor-rectly.REPLACEREPLACEwillinsertdataintoatable,butifyousupplyaprimarykeythatisthesameasonethatisalreadyinthetable,thenREPLACEwillreplacethatrow.IthasthesameformatastheINSERTquery,andalsocanbeusedinasimilarwaytotheUPDATEquery,butwith-outtheWHEREclause.ThefirstformatforREPLACEisasfollows:REPLACEtable(column,column…)VALUESvalue,value…Ifoneofthecolumnsthatyouspecifyistheprimarykeyforthetable,andthevalueofthatfielddoesnotmatchakeythatisalreadyinthattable,thentheaboveworksexactlyasastandardINSERTquery.If,however,theprimarykeymatchesonethatalreadyexists,itwilldeletethatrow,andreplaceitwiththedatathatyouhavejustprovided.Todemon-strate,wewilladdanewrowtothevisitorbooktableusingREPLACE,andthenchangethatrowusingREPLACEasecondtime.Runthefollowing:REPLACEvisitorbook(entryid,cookieid,firstname,familyname,entrytext,score,location)VALUES(7,1,‘Paul’,‘Davis’,‘HowshouldIknow?’,15,‘Dusseldorf’)Nowselecteverythingfromthevisitorbooktable.TheresultsareshowninFigure8.11.Beforeaddingthisrowthelastrow’sprimarykeywas6,sospecifying7forthisrowhasdoneexactlythesameasanINSERTwouldhave. Chapter8•ChangingData99Figure8.12REPLACEremovesunspecifiedfields.Youmaynoticethatwemissedoutthedateonthelastentry.IfyouhaveusedtheUPDATEquery,thetemptationwouldbetorunthefollowingtoaddadatetotherow:REPLACEvisitorbook(entryid,entrydate)VALUES(7,‘2002-02-20’)However,Figure8.12showstheresultsofthis.Noticethatinsteadofreplacingtheentry-datefieldintherow,ithasreplacedthewholerow,butonlyaddedtheentrydatetothenewrowthatithasinserted.ThisshowsthedifferencebetweenUPDATEandREPLACE.UPDATEcanselectivelychangeafieldwithinarow,whereasREPLACEwilldeletetherowthatitisreplacing,andcreateanewrowwithonlythefieldsthatyouspecify.SotoaddthedatetotherowwejustaddedwithREPLACE,weneedtoessentiallyrunthewholequeryagainasfollows:REPLACEvisitorbook(entryid,entrydate,cookieid,firstname,familyname,entrytext,score,location)VALUES(7,‘2002-02-20’,1,‘Paul’,‘Davis’,‘HowshouldIknow?’,15,‘Dusseldorf’) 100DatabaseDesignManual:usingMySQLforWindowsFigure8.13ReplacingarowwithREPLACE.Figure8.13showstheresultsofrunningtheabovequery.Wenowhavearowthathasallofthefieldspopulated.WecanalsoruntheREPLACEquerywiththealternativeformat:REPLACEtableSETcolumn=value,column=valueIpersonallypreferusingthisformatofthecommandasitsavesyouhavingtospendtimeensuringthatyourcolumnlistisinthesameorderasyourvaluelist.CREATEorREPLACEYoucanalsousetheREPLACEcommandwhenyouarecreatingatable,functionordatatype.Whenyouarecreatingoneoftheseobjects,ifyouuse:CREATEORREPLACEobjectparametersinsteadofjustCREATE,thecommandwilldeleteanexistingobjectbeforecreatinganewone.Thisstopsanerroriftheobjectalreadyexists,andalsostopsyouhavingtodeletetheobjectfirstifyoudowanttoreplaceit.DUPLICATEKEYUPDATEWhenyouareinsertingdataintoatablewithaprimarykey,youcanhaveproblemsifyouinsertakeythatalreadyexists.Forexample,ifwewereinsertinganewrowintoourvisitorbookshowninFigure8.13,wecouldusethefollowing: Chapter8•ChangingData101INSERTintovisitorbook(entryid,entrydate,cookieid,firstname,familyname,entrytext,score,location)VALUES(7,’2002-02-20’,1,’Paul’,’Davis’,’HowshouldIknow?’,15,’Dusseldorf’)However,youwillnoticethatwealreadyhavearowwiththeprimarykeyof7,sothisinsertwillstopwithanerrorasfollows:Duplicateentry‘7’forkey1ThereisanewclauseinMySQLversion4.1thatallowstheinserttoupdatetheduplicatekeyiftheprimarykeyalreadyexists.Tousethiswere-writethequeryasfollows:INSERTintovisitorbook(entryid,entrydate,cookieid,firstname,familyname,entrytext,score,location)VALUES(7,’2002-02-20’,1,’Paul’,’Davis’,’HowshouldIknow?’,15,’Dusseldorf’)ONDUPLICATEKEYUPDATEentryid=entryid+1Inthiscase,theINSERTwillrunwithoutanerror,andtherowthatusedtohaveapri-marykeyof7willhavebeenreplacedwithaprimarykeyof8.Thisworksinthisexamplebecausebeforetheinsertarowwiththeprimarykeyof8didnotexist.Iftherewassucharowthenasimilarerrorwouldbegeneratedforrow8. Thispageintentionallyleftblank DeletingourData9KeepingDataAccurateAfterspendingsomuchtimedesigningourdatabasesandputtingdataintothem,itseemsapitytodiscussdeletingthem,butdeletingrecordsandtablesisstillanimportantpartofusingMySQL.Ifwearetryingtokeepourdatavalidandaccuratewewillhavetoremovedatafromtimetotime.Thischapterwillshowyouhowtoremoverowsfromtables,andthenhowtoremovewholetables.YoumayrememberbackinChapter5wecreatedthewebpage2tablebyinsertingdatafromthewebpagetable.Ifforsomereasonyounolongerhavethewebpage2tableinyourdatabase,youcancreateitagainquicklybyrunningthefollowing:CREATETABLEwebpage2(IDMEDIUMINTNOTNULLAUTO_INCREMENTPRIMARYKEY,Contenttext,Titletext)Nowcopythedataintothistablebyexecutingthefollowing:INSERTINTOwebpage2SELECT*fromwebpageSelecteverythingfromwebpage2andyouwillseethesacrificialtableinFigure9.1.DELETEFROMToDELETErowsfromatable,youusethefollowingquery:DELETEFROMtablenameWHEREconditionAswedemonstratedearlier,thiscommandwillquicklyandunsympatheticallyremoveeverythingfromatablewithoutaWHEREclause,sobecarefulwhenyouareusingit.Forinstance,ifwewantedtodeletethevisitorbookrowfromourwebpage2tablewewouldusethefollowing:103 104DatabaseDesignManual:usingMySQLforWindowsFigure9.1Thewebpage2table.DELETEFROMwebpage2WHEREid=2AsyouseefromFigure9.2,thishascleanlyremovedtherow.Aswewerereferringtoarowspecificallybyusingitsprimarykey,itwassafeenoughtodeleteitaswedid.Theproblemcomeswhenyouwanttodeleteafewrowsusingsomepatternmatching.Youmustbesurethatyouknowwhatyouareabouttoremove.Oneofthewaystoensurethatyouaredeletingtherightrowsistotryandselectthemwiththesamecriteriafirst.Forinstance,ifwewantedtodeletetheHowtocontactmerowwithpatternmatching,testthematchfirstwithaSELECTasfollows:SELECT*FROMwebpage2WHEREtitleLIKE‘Ho%’Figure9.2Thewebpage2tableafterremovingarow. Chapter9•DeletingourData105Figure9.3Thewebpage2tableafterdeletingrowsusingpatternmatching.YouwillnoticefromFigure9.3thatifweweretohavedeletedusingthismatchwewouldhaveremovedtworows.Wasthiswhatyouwantedtodowiththematch?AsimpledeletionofarowmayhaveresultedinourremovingtheHomepageofourwebsite.Finally,youcanuseDELETEtocompletelyclearalloftherowsfromatableasfollows:DELETEFROMwebpage2Runtheaboveandthentrytoselecteverythingfromthetable.Youwilljustreceiveamessagesayingthatyourqueryproducednoresults.Describingwebpage2showsthatthetableisstillthere,butitnolongerhasanydatainit.DROPTABLETherecomesatimewhenyounotonlyneedtodeletethecontentsofatablebutyouneedtoremovethetableaswell.Todothis,usetheDROPcommandasfollows:DROPTABLEtablenameSotofinallyremovealltracesofwebpage2,executethefollowingquery:DROPTABLEwebpage2Ifyounowtryandselectanythingfromwebpage2,youwillgettheerrorshowninFigure9.4.YoucanDROPmultipletablesbyspecifyingalistofthemasfollows:DROPTABLEtable1,table2,table3,etcIfyouareusingaMySQLtabletypethatchecksforreferentialintegrity,theDROPTABLEcommandmaygenerateerrorsifothertablesmakereferencetorowsinthetablebeingdropped.Todropatableandallothersthatreferenceit,youwoulduse:DROPTABLEtablenameCASCADE 106DatabaseDesignManual:usingMySQLforWindowsFigure9.4Thewebpage2tablehasbeendropped.TheCASCADEdoesnotfunctioninthecurrentversionofMySQLbutifyouareusingthisbooktolearnSQLforothersystemsitisusefultoknowabout.NewerversionsofMySQLmaywellimplementthisfunction.DROPTheDROPcommandalsocanbeusedtodeleteentiredatabases.TodothisyouomittheTABLEkeywordasfollows:DROPdatabasenameWewillnotdemonstratethiscommandbecausewewanttouseourdatabasefortherestofthebook! AggregateFunctions10CountAggregatefunctionsinMySQLallowyoutocalculateasummaryofvaluesfromacolumnandfromgroupsofvalueswithinacolumn.Asusual,thebestwaytodemonstratethiscon-ceptiswithanexample.CountinMySQLisafunctionthatallowsyoutocountthenumberofspecifieditemsinatable.Onewaythatyoucanusecountisasfollows:SELECTcount(columnname)FROMtablenameWecanusetheabovestatementtocountthenumberofrowswithinatablebyusingthefollowingscript:SELECTcount(*)FROMvisitorbookIfyouexecutethatqueryyouwillgettheverysimpleresultsshowninFigure10.1.Youmaywonderwhyyouwouldbotherperformingaquerylikethiswhenyoucanjustlookatthetoprowoftheoutputtotellyouhowmanyrowscamefromyourquery.However,whenyouareaccessingthesefunctionsfromwithinascriptlanguage,likePHP,thenitismoreuseful.Forinstance,thefollowingscriptcountsallofthehitsinourlogtable,thusproducingacounterforthewholeofthewebsite:Figure10.1Countingrowsinatable.107 108DatabaseDesignManual:usingMySQLforWindowsSELECTcount(*)asPageHitsFROMlogFigure10.2showstheresultofthatquery.Noticethatweusedanaliastomaketheout-putalittlebitmoremeaningfulthanthecolumntitlethatthefirstqueryshowedinFigure10.1.Ifyouweretowriteascripttoexecutethatqueryonthefirstpageofyourweb-site,thenitwouldbeawonderfulwayofimplementingawebsitepagecounter.Thatquerywillshowallofthehitsonallofthepagesofoursite,aslongasyouhaverememberedtomakeeachpagelogahittoitinthedatabase!Ifyouwantedtoimplementapagecounterforeachindividualpage,thenwecanusethesamescriptbutthistimerestrictthesearchwithaWHEREclauselikethis:SELECTcount(*)asPageHitsFROMlogWHEREwebpageID=2Forinstance,inourwebpagetable,thewebpageIDofthevisitorbookpageis2,sothescriptabovewillcountthenumberoftimesthatanentryhasbeenputinthedatabaseforthatpage.Executingthatqueryoneachpagewherewewantacountertoappearwillenablethesametable,log,tobeusedtogeneratecountersforeverypageonthesite.Nowwehaveencounteredourfirstaggregatefunction,it’stimetoshowyousomethingthatmakesitevenmorepowerful,theGROUPBYclause.GROUPBYGROUPBYisaclausethatisaddedafteraselectstatementthatallowsyoutogetextrafunctionalityfromanaggregatefunction.TheGROUPBYclausehastobeplacedafterawhereclauseifyouincludeone.YouusetheGROUPBYclauseasfollows:SELECTaggregatefunction(columnname),columnsFROMtablenameGROUPBYcolumnnameFigure10.2Countingallthehitsonourwebsite. Chapter10•AggregateFunctions109Figure10.3GROUPBYwithoutanaggregate.Onitsown,theGROUPBYclausedoesnotdomuch.Considertheresultsofthefollow-ingqueryshowninFigure10.3:SELECTfamilynameFROMvisitorbookGROUPBYfamilynameIfthisquerystopswithanerroritisprobablybecauseyouhavenotfollowedscriptsinChapter8,wherewerenamedthelastnamecolumntofamilyname.AtfirstglanceFigure10.3looksasiftheGROUPBYhasdonelittlemorethanaSELECTDISTINCT,inthatithaspickedtheuniquelistoflastnamesfromthevisitorbook.IfyourememberfromthelasttimethatwelookedatthattabletherewerethreeNormans,aGarrett,aDavis,aShahandsomeonewithnolastname.Thequeryhasjustpickedoneinstanceofeachofthem.ThepoweroftheGROUPBYclausecomeswhenweattachittoaquerywithanaggregatefunction.Forexample,takethefollowing:SELECTfamilyname,count(familyname)asfamilynamecountFROMvisitorbookGROUPBYfamilynameFigure10.4showstheresultsofthatquery.GROUPBYhasdonemorethanjustselecttheuniqueentriesofthefamilynamecolumn,ithasgroupedeachsimilarentryintoasetofvaluesthattheaggregatefunctionscanworkwith.Todemonstratefurther,ifwewantedtoapplytheGROUPBYclausetothequerythatcountedwebsitehits,wecanuse:SELECTid,count(*)FROMlogGROUPBYwebpageid 110DatabaseDesignManual:usingMySQLforWindowsFigure10.4CountinggroupswithGROUPBY.Figure10.5showstheresults.Noticethatonthisoccasionwehavenotgroupedbytheclausethatwearecounting,butthatdoesnotmatter,thecountisreallyjustcountingrows,notcolumns.Sothequeryissaying:“Givemethenumberrowsofthetablethatrefertoeachspecificwebpage”.Nowthisisgettingpowerful;thesimplequeryhasshownthatourHomepagehashadthreehits,andourvisitorbookandlinkspagehaveeachreceivedonehit.Imaginehowuse-fulthisdatawouldbeforatablethatcontainedtensofthousandsofhitsonhundredsofwebpages.Ifwewereperformingsuchaqueryandwantedtorestricttheresultstothefirst10pages,thenwewouldaddaWHEREclauseasfollows:SELECTid,count(*)FROMlogWHEREwebpageid<=10GROUPBYwebpageidNoticethepositionoftheWHEREclause.IfyouputitaftertheGROUPBYyougetanerrormessage.Figure10.5CountinggroupswithGROUPBY. Chapter10•AggregateFunctions111Ofcourse,theresultsinFigure10.5arenotveryuserfriendly.Togetamorereadableoutput,trythefollowingquery:SELECTwebpage.title,count(*)asnumberofhitsFROMlogLEFTJOINwebpageONlog.webpageid=webpage.idGROUPBYlog.webpageidFigure10.6showswhathappenswhenweusetheaggregateGROUPBYalongwithaLEFTJOINtogetthewebpagenamesandanaliastogetameaningfulcolumnname.WehavenowconfirmedtheresultsthatwehadguessedatinFigure10.5,bymakingouroutputmorereadableandmeaningful.Wecouldusetheoutputofthisquerywithlittlereformattingonawebpagethatshowedthefullstatisticsforourwebsite.MAXTheMAXfunctioncalculatesthemaximumvaluefromasetofnumericvalues.Onewayofusingthisfunctionis:SELECTMAX(score)FROMvisitorbookThiswillfindthehighestvalueofscorethatsomeonehasenteredintothevisitorbook.YoucanseetheresultofrunningthisqueryinFigure10.7.IfyouhaveacolumnthatusesAUTONUMBERtocreateitsIDasaprimarykey,youcanuseafunctiontotellwhichrowwasthelastadded,byselectingthehighestIDasfollows:SELECTMAX(cookieID)FROMcookiesIfforsomereasonyouwanttomanuallyaddakeytothetablewithoutusingAUTON-UMBER,youcanmodifytheabovequeryasfollows:Figure10.6Acount,GROUPBY,aliasandLEFTJOIN. 112DatabaseDesignManual:usingMySQLforWindowsSELECTMAX(cookieID)+1FROMcookiesThiswilladdonetothehighestprimarykeythatthereis,andyoucaninserttheresultsstraightintothetableasthenewprimarykey.MINTheMINfunctionistheoppositeofMAXinthatitcalculatestheminimumvaluefromasetofnumericvalues.Sotofindthelowestscorethatourvisitorsgaveourwebsitewewoulduse:SELECTMIN(score)FROMvisitorbookTheresultsareshowninFigure10.8.Theminimumscorethatanyofourvisitorshasgiventhewebsiteyetis2outof20.Figure10.7Findingthemaximumofasetofvalues.Figure10.8Findingtheminimumofasetofvalues. Chapter10•AggregateFunctions113SUMSUMfindsthetotalvaluebyaddingtogetherasetofnumericvalues.Toaddupallofthescoresthatourvisitorsgaveourwebsitewewoulduse:SELECTSUM(score)FROMvisitorbookTheresultsareshowninFigure10.9.UsingMultipleAggregatesYouarenotlimitedtojustusingoneaggregateinaquery.Forinstance,ifwewantedtocal-culatetheaveragescoregivenbyavisitor,wecanuseanaggregatetosumthescoredividedbyacountofthescoresgiven,asfollows:SELECTSUM(score)/COUNT(score)ASaverageFROMvisitorbookHavealookatFigure10.10toseetheaveragescoreforoursitebasedonthedatathatwasinthetables.Figure10.9Thesumofthescoresonthevisitorbook.Figure10.10Theaverageofthescoresonthevisitorbook. 114DatabaseDesignManual:usingMySQLforWindowsWecanevenusethefollowingtofindtheaveragescoregivenbypeopleofthesamefam-ilyname:SELECTfamilyname,SUM(score)/COUNT(score)ASaverageFROMvisitorbookGROUPBYfamilynameFigure10.11showsthequeryaboveinaction.Itgivesthenameandworksouttheaver-agescoreforeachname.Italsoshowsthatmyfamilyarenotshowingfavouritismbygivingmetopmarks!Youmaynoticefromthequerythattheselectstatementasksforastandardcolumnname,familyname,andanumberderivedfromaggregatestatements.Althoughitisquiteacceptabletoaskformultipleaggregates,asfollows:SELECTSUM(score)ASsum,COUNT(score)AScount,max(score)ASmaxFROMvisitorbookyouarenotallowedtomixcolumnnamesandaggregatesinthesameselectionsetwith-outusingGROUPBY.Forinstance,thefollowingscriptwillgeneratetheerrorshowninFigure10.12:SELECTfamilyname,SUM(score)/COUNT(score)ASaverageFROMvisitorbookThefullerrorstates,“MixingofGROUPcolumns(MIN(),MAX(),COUNT()…)withnoGROUPcolumnsisillegalifthereisnoGROUPBYclause.”SoalthoughyoucanusemultipleaggregatestogetherinaSELECTstatement,ifyouaremixingthemwithcolumnnamesyoumustremembertoincludeaGROUPBYclausetogetthequerytowork.Figure10.11Theaverageofthescoresforeachfamily. Chapter10•AggregateFunctions115Figure10.12ColumnsmixedwithaggregatesmustuseGROUPBY.AVGAlthoughwecalculatedtheaveragemanuallyintheprevioussection,itissuchausefultoolthatthereisabuilt-infunctioninMySQL.TheAVGfunctioncalculatestheaveragevalueofasetofnumericvalues.Sotofindtheaveragethatourvisitorsgaveourwebsitewewoulduse:SELECTAVG(score)FROMvisitorbookThisquerywillgivesimilarresultstothoseshowninFigure10.10,althoughtoahigherprecisionthanthemanualcalculation.HAVINGWhenusingtheWHEREclauseinanaggregatequery,wehavealreadymentionedthatithastobeplacedbeforetheGROUPBYclauseasitrestrictsthequerybeforeithasbeensortedintogroups.TheHAVINGclauseisplacedaftertheGROUPBYclause,andallowsyoutorestrictafterthegroupandbyanaggregate.Examinethefollowingquery:SELECTfamilyname,AVG(score)ASgoodaverageFROMvisitorbookGROUPBYfamilynameHAVINGAVG(score)>=8Similartothepreviousquery,thisonecalculatesanddisplaystheaveragescoreforeachfamily,butthenrestrictstheresults,usingtheHAVINGclause,togroupswhoseaverageis 116DatabaseDesignManual:usingMySQLforWindowsFigure10.13Theaverageofthescoresforeachfamily.greaterthanorequalto8.TheseresultsareshowninFigure10.13,andyoucancomparethesewiththeresultsofthequerywithouttheHAVINGclausethatareshowninFigure10.11. WorkingwithDatesandTimes11UsingDateandTimePuttingadateandtimecolumninadatabaseisasimpletask.Populatingarowwiththecurrentdateandtimeiseveneasierinmanyscriptinglanguages.However,gettingthedateandtimeback,althoughsometimesconfusing,canbeanextremelypowerfulwayofanalysingyourdata,andgettingmorefromyourtables.NOWToobtainthecurrentdateandtime,usetheNOW()function.Thefunctionrequirestheopenandclosebracketseventhoughyouarenotpassinganyvaluetoit.ToquicklyobtainthecurrentdateandtimeyoucanuseaSELECTasfollows:SELECTNOW()ThiswillproducetheoutputshowninFigure11.1.WhenwecreatedtheLogtable,wemanuallyfilleditwithsampledata.WhentheLogtableisinuseonthesystem,wewillwantthedateandtimetobeautomaticallyaddedintothetable,sowecanusethefollowingtoaccomplishthis:INSERTINTOLog(DateCreated,Browser,IPNumber,CookieID,WebPageID)VALUES(NOW(),‘Mozilla/4.0’,‘192.168.1.10’,2,1)TheabovewillusetheNOW()functiontoinsertthecurrentsystemdateandtimeintothenewlycreatedrow.Ifyouhavebeenkeepingupwiththeexamples,thisshouldhavecre-atedarowwiththeauto-IDof6,sotoviewthisrowwecanissuethefollowingquery:117 118DatabaseDesignManual:usingMySQLforWindowsFigure11.1SELECTNOW().SELECTid,datecreatedFROMlogWHEREID=6Figure11.2showstheresults,yourswillshowadifferentdateandtime!CURRENT_DATECURRENT_DATEgivesyouthesystemdatefortoday.ItdiffersfromNOW()inthatitdoesnotrequirethebracketsandonlyoutputsthedate.Youcanusethefollowingtofindthecur-rentdatequickly:SELECTCURRENT_DATEFigure11.3showsthewaythatCURRENT_DATEisoutputinaquery.CURRENT_TIMECURRENT_TIMEreturnsthesystemtime.AgainitdiffersfromNOW()inthatitdoesnotrequirethebracketsandonlyoutputsthetime.Youcanusethefollowingtofindthecurrenttimequickly:SELECTCURRENT_TIMEFigure11.2AninsertedNOW(). Chapter11•WorkingwithDatesandTimes119Figure11.3Selectingthecurrentdateandtime.Figure11.3showsthewaythatCURRENT_TIMEisoutputinaquery.YoucaninsertthecurrenttimeintoatableusingasimilartechniquetothatwhichwedemonstratedwiththeNOW()function.YEARYEAR()allowsyoutoextracttheyearfromagivendate.YouneedtopassadatetotheYEARfunctionasfollows:YEAR(aDate)aDateisanyobjectthatcontainsadatepart.Sowecouldusethefollowingtooutputthecurrentyearinaselectstatement:SELECTYEAR(NOW())ASThis_year_isFigure11.4showsthisqueryalongwiththenextfunction.WehavepassedNOW()inasthedate,whichwillactuallypassinthedateandtime,buttheYEAR()functionignorestheextradataandworkswithjustthedateportionoftheNOW()output.MONTHMONTH()worksinthesamewayasYEAR()butoutputsthenumericalvaluecorrespon-dingtothemonthofthedategiven.Thefunctiontakesthefollowingformat:MONTH(aDate)aDateisanyobjectthatcontainsadatepart.Sowecouldusethefollowingtooutputthecurrentmonthnumberinaselectstatement:SELECTMONTH(NOW())ASThe_month_is 120DatabaseDesignManual:usingMySQLforWindowsFigure11.4Selectingthecurrentyearandmonth.Aspreviously,wehavecombinedthisquerywiththeresultsfromtheYEARfunctionwhichareshowninFigure11.4.MONTHNAMEUsingMONTH()tofindthemonthnumberasshownaboveisfineifyouarewantingtoperformsomemonthcalculations,butifwearepresentingthistotheuserwemaywishtooutputthemonthnameinwords.WedothisbyusingtheMONTHNAME()function,whichtakesthefollowingformat:MONTHNAME(aDate)Asusual,aDateisanyobjectthatcontainsavaliddate.Wewilldemonstratethisbyadif-ferentquerythistime.Thefollowingquerywillshowthemonthsofalltheentriesinthelogtable:SELECTMONTHNAME(datecreated)FROMLogFigure11.5showsourlogtablewiththemonth,asopposedtotheexactdateandtime,thateachentrywascreated.Noticehowonthisoccasionweinsertedthecurrentrow’sDateCreatedfieldintothefunctionasthedate.Thequeryhasprocessedthefunctionforeachrowinthetable.DependingonhowcloselyyouhavefollowedtheINSERTstatementsinthisbook,yourtablemayhavemoreorfewerrows.DAYNAMEDAYNAME()willreturnthedaythatagivendatefallson.Youneedtopassthefunctionadateasfollows:SELECTDAYNAME(NOW())Figure11.6showstheresultswhencombinedwiththenexttwoexamples.YouwillseefromthefigurethatIamwritingthison23December2002,whichisaMonday. Chapter11•WorkingwithDatesandTimes121Figure11.5MONTHNAME()outputtingthelogtable.DAYOFWEEKDAYOFWEEK()willreturnanumberbasedonthedaythatthecurrentdatefallsonfromTable11.1.Table11.1DAYOFWEEKreturnedvalues.DayValuereturnedSunday1Monday2Tuesday3Wednesday4Thursday5Friday6Saturday7DAYOFWEEKneedsyoutopassitanobjectwithadatecomponent,aswithotherdatefunctionsthatwehavediscussedpreviously.Toobtainthenumericdayoftheweekfortoday,youwouldissuethefollowingquery:SELECTDAYOFWEEK(NOW())Figure11.6showstheresultsfromthatquery.YoucanseethatthedayisMondayanditsDAYOFWEEKvalueistherefore2.WEEKDAYWEEKDAY()worksinasimilarwayasDAYOFWEEK()exceptitindexesthedaynamesinadifferentway.ItreturnsvaluesforthedayoftheweekbasedonthevaluesinTable11.2. 122DatabaseDesignManual:usingMySQLforWindowsTable11.2WEEKDAYreturnedvalues.DayValuereturnedMonday0Tuesday1Wednesday2Thursday3Friday4Saturday5Sunday6Toobtainthenumericweekdayfortoday,youwouldissuethefollowingquery:SELECTWEEKDAY(NOW())Figure11.6showstheresultsfromthatquery.YoucanseethatthedayisMondayanditsWEEKDAYVALUEistherefore0.DAYOFMONTHTheDAYOFMONTH()functionreturnsthedaynumberofagivenmonth.Weusethefunc-tionasfollows:DAYOFMONTH(aDate)BypassingtheoutputoftheNOW()functionintoDAYOFMONTH,youcanobtainthecurrentdaynumber.Figure11.7showsthisatthetimeofwriting,whichis23December.DAYOFYEARTheDAYOFYEAR()functionreturnsacountofthenumberofdayssince1Januaryforagivendate.Weusethefunctionasfollows:DAYOFYEAR(aDate)Figure11.6DAYNAME(),DAYOFWEEK()andWEEKDAY(). Chapter11•WorkingwithDatesandTimes123Figure11.7DAYOFMONTH()andDAYOFYEAR().aDateisanyobjectthatcontainsadatepart.BypassingtheoutputoftheNOW()func-tionintoDAYOFYEAR,youcanobtainthenumberofdayssincethestartoftheyearforagivendate.Figure11.7showstheoutputoftheDAYOFYEAR()functionwhenusedinaSELECTstatement.DAYOFYEAR()isusefulwhencalculatingwithdates,asitprovidesanumericalversionofthedatetoworkwith.Youcanusefunctionslikethistocalculatethingslikeages,althoughthereareotherfunctionsthatmakethiseasier.WEEKWEEK()returnsthenumberofweekssincethestartoftheyearforagivendate.Asusual,allthatitrequiresasaparameterisanobjectwithadatepartasfollows:WEEK(aDate)So,tofindthecurrentweeknumberwewoulduse:SELECTWEEK(NOW())Figure11.8showstheWEEK()functioninaction.Youcanseethatitisnearlythelastweekoftheyearattimeofwriting.Incidentally,dependingonthedaythattheyearstartson,itispossibletohavethebegin-ningsofaweek53,soif53isreturnedfromtheWEEK()function,itisnotanerror.Usingtheaboveformat,WEEK()calculatestheweeknumberusingtheassumptionthatthefirstdayoftheweekisSunday.IfyouwantthecalculationtobebasedonaMonday,youusetheamendedformat:WEEK(aDate,1)Thereareotherpeculiaritieswhentheweekfallsaroundtheyearend;isitthefirstweekofthisyearorthelastweekoflastyear?HavealookattheDateandTimeFunctionssec-tionoftheMySQLonlinemanualforfurtherdetails.YEARWEEKYEARWEEK()returnstheyearandtheweeknumberasonebignumber.Youusethefunc-tionbypassingitavaluewhichhasadatecomponentasfollows: 124DatabaseDesignManual:usingMySQLforWindowsYEARWEEK(aDate)Figure11.8showsthisfunctionworkinginweek51of2002.Iamsurethatthisfunctionisofusetosomeone;however,Ihaveyettofindauseforitmyself.Itwouldbeinterestingtofindoutwhatothersareusingitfor.QUARTERQUARTER()returnsanumberrangingbetween1and4dependingonthepartoftheyearthatitisgiven.ThenumberreturnedisbasedonTable11.3.Table11.3QUARTERreturnedvalues.Between:And:Valuereturned1January31March11April30June21July30September31October31December4QUARTER()workslikeotherdatefunctionsinthatyousupplyitanobjectwithadatepartasfollows:QUARTER(aDate)Figure11.8showsthefunctionworkinginthefourthquarterof2002.HOURHOUR()returnsthehourforagiventimeobject,basedonthe24-hourclock.Youpassthetimevaluetoitasfollows:HOUR(aTime)Toobtainthecurrenthour,wecanusethefollowing:SELECTMINUTE(NOW())Figure11.8WEEK(),YEARWEEK()andQUARTER(). Chapter11•WorkingwithDatesandTimes125Figure11.9HOUR(),MINUTE()andSECOND().Figure11.9showsthisfunctionrunningalongwithsomeothertimefunctions.MINUTEMINUTE()returnstheminutespastthehourforagiventimeobject.Youpassthetimevaluetoitasfollows:MINUTE(aTime)Toobtainthecurrentminutepastthehour,wecanusethefollowing:SELECTMINUTE(NOW())Figure11.9showsthisqueryinaction.SECONDSECOND()returnsthesecondsintheminuteofagiventimeobject.Youpassthetimevaluetoitasfollows:SECOND(aTime)Toobtainthesecondspartofthecurrenttime,wecanusethefollowing:SELECTSECOND(NOW())Figure11.9showstheresultsforthisatthetimeofwriting.DATE_ADDThereisnorealneedtoadddatestogether,howeveraddingaperiodoftimetoadateisuse-ful.TheDATE_ADD()functionallowsyoutoaddatimeperiodtoadate.TheformatofDATE_ADD()isasfollows:DATE_ADD(aDate,INTERVALtimeperiod) 126DatabaseDesignManual:usingMySQLforWindowsThetimeperiodwillbeavalue,followedbythetypeoftimeperiod.Thissoundscom-plex,soasusualwewilldemonstratebyexample.Tofindoutwhatdateitwillbeonthisdaynextweek,wecanadd7daystothecurrentdatewiththefollowing:DATE_ADD(‘2002-12-23’,INTERVAL7DAY)Youwillnotethatwehavetypedthedatehere,insteadofusingNOW()toshowyouanalternativewayofenteringthedate.Figure11.10showsthisrunning,withthecolumnnameofnextweek.Sothedatethistimenextweekwillbe30December.Youcanalsoaddothertimeperiods.Forinstance,thefollowingwillreturnthedatethatitwillbethreemonthsfromtoday:DATE_ADD(‘2002-12-23’,INTERVAL3MONTH)TheaboveisshowninthethreemonthscolumnofFigure11.10;threemonthsfromtodaywillbe23March2003.ThecompletescripttogenerateFigure11.10isasfollows:SELECT‘2002-12-23’ASTodaysDate,DATE_ADD(‘2002-12-23’,INTERVAL7DAY)ASNextWeek,DATE_ADD(‘2002-12-23’,INTERVAL3MONTH)ASThreeMonthsAswellasaddingday,monthandyearperiodstoadate,youcanalsoaddtimeperiodsaswell.Forinstance,toaddsixhourstothecurrentdatewewoulduse:DATE_ADD(‘2002-12-23’,INTERVAL6HOUR)YoucanseethisinFigure11.11,underthe6hrscolumn.Noticethoughthatbecauseweonlyspecifiedadateinthecreationstring,ithastakenthedefaultvalueof00:00:00,mid-night,asthetime,andaddedsixhourstothat.The6fromnowcolumnshowstheresultswhenweusetheNOW()functiontogenerateadatethatalsohasatimepart.Thistime,addingsixhoursgivesusadateandtimefortomorrow.Again,torecreatetheresultsofFigure11.11,youcanusethefollowing:SELECT‘2002-12-23’ASTodaysDate,DATE_ADD(‘2002-12-23’,INTERVAL6HOUR)AS6Hrs,DATE_ADD(NOW(),INTERVAL6HOUR)AS6fromnowFigure11.10DATE_ADD()addsdayperiodstogivendates. Chapter11•WorkingwithDatesandTimes127Figure11.11DATE_ADD()addstimeperiodstogivendates.Table11.4showssomeoftheperiodtypesthatyoucanusewithDATE_ADD()andotherrelatedfunctionstospecifyyourtimeperiod.Table11.4Sometimeperiodtypes.TypeValueformatTimeperiodexampleSECONDnumericINTERVAL5SECONDMINUTEnumericINTERVAL5MINUTEHOURnumericINTERVAL5HOURDAYnumericINTERVAL5DAYMONTHnumericINTERVAL5MONTHYEARnumericINTERVAL5YEARHOURSECOND“hours:minutes:seconds”INTERVAL“05:10:00”HOURSECONDHOURMINUTE“hours:minutes”INTERVAL“05:10”HOURMINUTEDAYHOUR“dayshours”INTERVAL“512”DAYHOURAfulllistingofthesetypesisintheMySQLonlinedocumentation.ADDDATETheADDDATE()functionworksexactlythesameastheDATE_ADD()functionjustdescribed.Theformatistherefore:ADDDATE(aDate,INTERVALtimeperiod)PleaserefertotheDATE_ADDsectionaboveforfurtherinformation.DATE_SUBTheDATE_SUB()functionworkssimilarlytotheDATE_ADD()function,butitsubtractsthetimeperiodinstead.Theformatis:DATE_SUB(aDate,INTERVALtimeperiod) 128DatabaseDesignManual:usingMySQLforWindowsThetimeperiodwillbeavalue,followedbythetypeoftimeperiodfromTable11.4.Forexample,tofindoutwhatthedatewasthistimelastweek,wecanuse:DATE_SUB(‘2002-12-23’,INTERVAL7DAY)Andwecantaketimeawayfromadateasfollows:DATE_SUB(‘2002-12-23’,INTERVAL1SECOND)Figure11.12showsthesetwofunctionsrunning,usingthefollowingscript:SELECT‘2002-12-23’ASTodaysDate,DATE_SUB(‘2002-12-23’,INTERVAL7DAY)ASlastweek,DATE_SUB(‘2002-12-23’,INTERVAL1SECOND)ASyesterdaySUBDATETheSUBDATE()functionworksexactlythesameastheDATE_SUB()functionjustdescribed.Theformatistherefore:SUBDATE(aDate,INTERVALtimeperiod)PleaserefertotheDATE_SUBsectionaboveforfurtherinformation.TO_DAYSTheTO_DAYS()functionconvertsadateintothenumberofdayssinceyear0.Youpassthefunctionadateasfollows:TO_DAYS(aDate)Sotoconverttoday’sdateintodayswecanuse:TO_DAYS(‘2002-12-23’)asToDaysFigure11.12DATE_SUB()subtractstimeperiodsfromgivendates. Chapter11•WorkingwithDatesandTimes129Figure11.13Thenumberofdayssinceyear0.Figure11.13showsthisfunctionrunningwithinaSELECTquery.Again,thiscanbeusefulwhenyouneedtoperformsomecalculationusingdates.Forinstance,ourcookietablecontainsthedatethatthecookiewascreated,whichisthefirstdaythattheuserwouldhaveaccessedthesite.Thefollowingquerycouldbeusedtoascer-tainhowmanydayshavepassedsincethatfirstaccess:SELECTNOW()AStodaysdate,TO_DAYS(NOW())-TO_DAYS(datecreated)ASdayssinceFROMcookiesFigure11.14showsthequeryrunning.You’llnoticethatithasbeennearlyayearsincethecreationofmostofthecookies.Onawebpage,youwouldprobablywanttorestrictthatquerytoasinglerowbyselect-ingtheIDoftheuser’scookiewithaWHEREstatement,forinstance:SELECTNOW()AStodaysdate,TO_DAYS(NOW())-TO_DAYS(datecreated)ASdayssinceFROMcookiesWHEREID=1Figure11.14TO_DAYS()usedfordatecalculation. 130DatabaseDesignManual:usingMySQLforWindowsFROM_DAYSFROM_DAYS()isthecomplementaryfunctiontoTO_DAYS()inthatitconvertsagivennumberofdaysbackintoadatesinceyear0.Youpassthefunctionadateasfollows:FROM_DAYS(Days)Daysisthenumberofdaysthatyouwantthefunctiontouse.Sowecouldusethefol-lowingtofindoutwhichdatewas300000daysafterthestartofyear0:SELECT‘2002-12-23’ASTodaysDate,FROM_DAYS(300000)ASFromDaysFigure11.15showsthatitwas16May821–quitealongtimeago!Again,youcanusethisfunctionindaycalculations.Forinstance,wecanre-writeoneofourpreviousDATE_ADD()examplesasfollows:SELECT‘2002-12-23’ASTodaysDate,FROM_DAYS(TO_DAYS(NOW())+7)ASNextWeekSothisquery:•CalculatesthecurrentdateusingNOW().•Convertsthecurrentdateintodayssinceyear0withTO_DAYS().•Adds7tothisfigure,togettonextweek.•Andthenconvertsthetotalbackintoadate.Ofcourse,theDATE_ADD()functiondoesthismoreefficiently,howeveryoucanseehowthesetwofunctionscanbeusedwhenyouarecalculatingwithdates.TIME_FORMATTIME_FORMAT()allowsyoutochangethewaythatagiventimeisdisplayed.Theformatofthestatementis:TIME_FORMAT(aDate,format)Figure11.15300000daysfromyear0! Chapter11•WorkingwithDatesandTimes131aDateisanyobjectthatcontainsatimepart.formatisastringthatcontainstherequiredpartsofthedate,usingthefollowingsym-bols:•%H–thehour,inthe24-hourclockformat,withaleadingzero•%k–thehour,inthe24-hourclockformat,withoutaleadingzero•%l–thehour,between1and12•%h–thehour,between1and12,withaleadingzero•%i–theminutes•%r–thefulltime,inthe12-hourclock(hh:mm:ss)withAM/PMshown•%T–thefulltimeinthe24-hourclock(hh:mm:ss)•%S–theseconds•%p–AMorPMSoanalternativeformatfortheCURRENT_TIMEfunctionwouldbe:TIME_FORMAT(NOW(),“Thetimeis:%T”)Ifyoulikethetimetobeoutputthatway,butprefertousehyphensinsteadofcolons,youcoulduse:TIME_FORMAT(NOW(),”MyTime:%H-%i-%S”)Inthefirstexample,wehavejustusedthesymbol%T,whichhasoutputoneofthestan-dardformsofthetimeformat.Comparethiswiththesecondexample,where,togetmoreflexibility,wehaveusedthreesymbolstorefertoeachpartofthetimespecifically.Wecantheneasilyinsertanyothercharacters(suchasourhyphens)atanypointintheformatstring.Wehavealsoaddedothertextwithintheformatstring,togiveusareadableoutputfromthefunction.Figure11.16showsthesefunctionsworking.DATE_FORMATDATE_FORMAT()allowsyoutochangethewaythatagivendateandtimeisdisplayed.Theformatofthestatementis:DATE_FORMAT(aDate,format)Figure11.16Formattingtheoutputoftime. 132DatabaseDesignManual:usingMySQLforWindowsaDateisanydateobjectorstringthatcanbeconvertedintoadate.formatisastringthatcontainstherequiredpartsofthedate,usingallofthepreviouslymentionedtimesymbolswiththeadditionofthefollowing:•%M–fulltextofmonthname•%W–dayoftheweek,writteninfull•%D–dayofthemonthwithaddedtext(i.e.1st,2nd,3rd)•%Y–theyear,using4digits•%y–theyear,using2digits•%a–abbreviateddayoftheweek•%d–dayofthemonthwithleadingzero•%e–standarddayofthemonth•%m–monthnumberwithleadingzero•%c–standardmonthnumber•%b–monthnameabbreviatedtothreecharacters•%j–dayofyear•%w–dayoftheweekinnumbers,startingwithSunday=0Wecandemonstratemostoftheseinonequeryasfollows:SELECTCURRENT_DATEAScurrentdate,DATE_FORMAT(NOW(),“%W,%D%M,%Y.DayNumber:%j.%a%d-%m-%y”)Icouldhaveincludedtimepartsinthatqueryaswell,butthentheresultsmayhavenotfittedinFigure11.17.Thequeryhasuseddifferentdateparts,punctuationspacingandtext,toformatthedateinourchosenway.Althoughitisquitehardtorememberallofthesymbolsforformattingdatesandtime–andtherearemoredescribedintheon-lineman-ual–onewayisasfollows.D,MandYareeasilyrememberedasDay,MonthandYear.Iftheyarewrittenincapitalsthenitisthefullun-abbreviatedversionofthedatepart.Ifyouwritetheminlowercase,d,mandy,yougettheshortenedformatforthepart.Don’tforgettoincludethepercentsign(%)infrontofanyofthesymbols.Figure11.17Formattingthedatefunction. Chapter11•WorkingwithDatesandTimes133WorkingwithDatesDatescanbeveryproblematicincomputersystems.Forinstance,dependingonyourloca-tion,thedatemaybeformattedindifferentways,thedatemaybestoredindifferentways–whichpromptedalltheworryabouttheyear2000bug,anddatesmaybeincorrectlyunder-stood,forexample:01-02-01couldbeunderstoodas:•1February2001•1February1(year1)•2January2001•2January1,etc.Sowhenusingdatesyouneedtomakeadecisionabouthowyouwanttostorethemandthenstaywithit,tosaveerrorsbeingbuiltintoyourdatabytheuseofwronglyunderstooddata.Onecolleagueofminealwaysstoresdatesasanumberofdaysfromyear0,usingsomethinglikeTO_DAYS()tocalculatethis.Youcanthenuseotherfunctionstoformattheoutputofthedatetowhateveryouwantandavoidanyproblems.Anotherissueistheinputofdatesonwebpages.Againausercanenteradateinmanydifferentways.OnewayIhaveseentoavoidthisistohaveaseriesofdropdownboxesfortheday,monthandyear,asshowninFigure11.18.Thispreventstheuserfromenteringthewrongdatabutwillnotstopthementeringdatessuchas31February,whichdoesnotexist.JavaScriptfunctionsareavailabletopickdatesfromcalendarsandpersonallyIhavefoundthesetobemostsuccessfulforavoidinginputerrors.AnexampleofoneisshowninFigure11.19.Insteadofclickingdirectlyonadatefield,thisismarkedreadonlyandyouhavetoclickonaniconthatinvokesthecalendarpopup.Onceyouselectthedatethepopupvanishesandthedateisstoredasyouwantittobeintheread-onlyfield.Figure11.18Enteringdatesthroughdropdowns. 134DatabaseDesignManual:usingMySQLforWindowsFigure11.19Selectingdatesfromacalendar.CommonDateQueriesDatequeriescanbeverycomplicatedtocreate,andifyouarestrugglingwithone,gethelp!Abriefsearchontheinternetcanrevealthatsomeonehasalreadywrittenaquerythatifnotofimmediateusemaywellputyouonthecorrectpathagain.Also,remembertheon-lineMySQLmanualthatinstalledalongwithMySQL.Thiscon-tainsmoreexamplesofcommonquerieswhichcanbeveryuseful.Oneofnoteisamethodtocalculateages,whichisnotreproducedagainhere,butcanbefoundundertheDateandTimeFunctionssectionofthemanual(Section6.3.4intheMySQL4.11alphamanual). SecuringtheDatabase12ProtectingValuableDataSofarinthisbookwehavebeenusingourdatabaseasasingleuser.Wearelogginginasarootuserwithnorestrictionsonwhatwedotoourdata.Wecancreatetablesanddatabaseswheneverwewant,andwecandeletedataandwholetablesanddatabasesjustasquickly,withnorestrictions.Thisisfineforasingleuserwhotakesresponsibilityforhisactions,butwhatifmanypeopleareusingthedata?MySQLisamulti-usersystem,somanypeoplecanaccessthedataatonetime.Ifyouhaveeverusedanymulti-usersystemwhereeveryonehasthehighestlevelofaccessthenyouwillknowthatevenwhenthereisnomaliciousactivity,filesdisappear,mistakesaremade,anddataandtimearelost.Whenyougivesomeoneaccesstoyourdata,youshouldthereforerestrictthemtotheminimumlevelofaccessthatispossibleforthemtoperformtheirtask.Thatwayanydamagethattheymaycauseislimited.SoMySQLprovidesamethodofcreatingandmaintaininguseraccounts,andthengrantingspecificlevelsofaccesstotheseusers.Thischapterwilldescribesomeofthemethodsofcreatinguserssothatthedatawillbeprotected.TocreateauserwithinMySQLyouGRANTthemaccesstoaresource.Atthattimeyouprovideastringtoidentifythatusertothesystem(theusername)andastringtoauthen-ticatethatuser.TheUserTableUsersareactuallystoredwithinatablewithinMySQL.Tolookatthetable,makesurethatyouareinthemysqldatabase(therestofthechaptersusethemysqlfastdatabase)byselect-ingitfromthedatabasedropdownortyping:usemysqlThemysqldatabasecontainsthesystemtableswhichMySQLneedstofunction.Theusertableisonetablewithinthisdatabase.Wecreatedalloftheothertablesinthisbookinaseparatedatabasesothatwecouldclearlydistinguishourtablesandsystemtablesfromeachother.Youcanviewtheusertablebyexecutingthefollowingquery:describeuser135 136DatabaseDesignManual:usingMySQLforWindowsFigure12.1Formatoftheusersystemtable.Figure12.1showstheformatofthistable.Notethattherearerowsfortheusernameandpassword,aswellasrowsforspecificaccessprivileges.Thecontentofthetableisrevealedbythefollowing:SELECT*FROMUSERFigure12.2showssomeofthecontentsofthetable.Youwillseethatduringtheproduc-tionofthisbooktherehasonlyreallybeenoneusercreated,withotherrootusersthataresystemgenerated.Youwillalsoseethattherootuserinthisinstancedoesnothaveapasswordandthepasswordfortheusercalledmafiuisnottheonethatyoumayhavebeentypinginthrough-outthebook.Forsecurity,MySQLencryptsapasswordbeforeitgetsstoredwithinatable.Youcanseethebenefitofthiswhenotheruserscanaccesstheusertableandvieweachother’spasswords.Whenyougrantaprivilegetoauser,iftheuserdoesnotalreadyexistitwillcreateanentryintheusertable.Iftheyalreadyexistthentheprivilegewillbeaddedtotheexistinguser. Chapter12•SecuringtheDatabase137Figure12.2Contentoftheusersystemtable.CreatingaUserTocreateanewuserthen,andgivethemthegreatestpossibleaccess,wewoulduse:GRANTALLPRIVILEGESON*.*TOMary@localhostIDENTIFIEDby“yeti”Thatdoesnotimmediatelyproduceanyresults,butifyouselecteverythingfromtheUSERtableagainyouwillseetheresultsinFigure12.3.NoticethatwenowhaveaMaryentryinthetable,withascrambledpassword.Figure12.3Thenewuserintheusertable. 138DatabaseDesignManual:usingMySQLforWindowsConnectingasaDifferentUserBecauseweareusingtheGUIloggedinasroot,wewillusethecommandlineMySQLmon-itortologinasthisuser.Openupadosbox,andtypethefollowing:c:cdmysqlbintogetyouintothedirectorywiththeMySQLexecutables.Normally,toruntheMySQLmonitoryouwouldjusttypemysql.However,thislogsinastherootuserwithfullaccess.WeneedtotellthemonitortologinasournewMaryuser.Dothisbytyping:mysql—user=Mary—password=yetiNowweneedtoconnecttoadatabasewithinMySQL.Type:connectmysqlYoushouldnowbeconnectedtothedatabase,andallthatremainsistohavealookatoneofthetableswithinthedatabase.Typethefollowingtoviewtheusertable:SELECT*FROMuser;RememberthatwhenyouareintheMySQLmonitoryouneedtoaddthesemicolonattheendofthelinetomakethequeryexecute.Wemayhavegotoutofthehabitofthiswhileusingthegraphicalclient.Ifyouforgettoaddthesemicolonyoucanjustaddittothenewlinetotriggerthequerythatyoutypedonthepreviousline.Figure12.4showsalloftheaboveinactionincludingaforgottensemicolon.ThetableoutputinFigure12.4isabitmessywhichiswhyyoumaybegladthatwenor-mallyusethegraphicalclient.Youcan,however,seethatwehavenowloggedinwithournewuserandexaminedatable.WewillnowdenytheMaryuseraccesstothistableusingthegraphicalclientagain.Switchbacktothis,butleavethedosboxopensothatwecanreturntoitinafewmoments.RestrictingaUser’sAccessInthegraphicalclient,loggedinasroot,type:REVOKEALLPRIVILEGESON*.*FROMMary@localhostThereisnopointinselectivelydenyingaccesstoatablewhentheuserhasaccesstoeverything,sotheaboveremovestheaccessanythingprivilegeontheaccount.Backatthecommandprompt,ifyouselecteverythingfromtheusertableyouwillstillgetthetablereturned.ThisisbecausetheREVOKEcommandjustissuedisaglobalcom-mand;itissayingrevokeMary’saccesstoeverything.Changestoglobalprivilegesonly Chapter12•SecuringtheDatabase139Figure12.4Loggingintothemonitorwithournewuser.comeintoeffectwhenauserlogsin.AsMarywasloggedinwhenwechangedherprivi-leges,shestillcanaccesseverything.Tologout,atthecommandprompttype:exitandthentype:mysql—user=Mary—password=yetitologinagain.Nowchangetothemysqldatabaseandselecteverythingfromtheusertableagainasfollows:usemysql;SELECT*fromuser; 140DatabaseDesignManual:usingMySQLforWindowsFigure12.5Marynowhasnoaccesstotheusertable.Figure12.5showsthatMarynolongerhastheaccessthatsheoncehad.Insteadofreturningthetable,MySQLjustreturns:Error1142:selectcommanddeniedtouser:‘Mary@127.0.0.1’fortable‘user’Ifyouarewonderingaboutthenumber127.0.0.1,thisisjusttheIP(InternetProtocol)numberthathasbeenallocatedforlocalhost.Eventhoughtheusertablehasencryptedpasswords,itisbestnottogivenormalusersaccesstoit.Whengivensamplepasswordsanduseraccounts,itispossibletousesoftwaretoolstoobtainthepassword.Asyoualreadyknowthepasswordsinthistableandbookthereislittlepointintryingtohidethem,sowewillgiveMaryaccesstoviewthecontentsofthattableagain.Inthegraphicalclient,type:GRANTSELECTONmysql.userTOMary@localhostBackontheMySQLmonitor,trytoselectfromtheusertableagain.Thistimeyoushouldgetthewholetableback.Noticethatbecausethisisatable-levelprivilege,thistimechangestotheuser’saccessbecameeffectiveimmediately.Nowlet’sbemoremalicious.OnMary’slogin,typethefollowing:DELETEFROMuserWHEREuser=’mafiu’;Marywillgettheerrormessage:ERROR1142:deletecommanddeniedtouser:‘Mary@127.0.0.1’fortable‘user’ThisiswheretheGRANTfunctionbecomesveryuseful.IfMarystillhadallprivileges,shewouldhavejustdeletedauser.AswehadonlygivenheraccesstotheusertableforSELECTing,aDELETEquerythrewanerror,savingtheday.Thesafestruleistogiveausertheminimalaccessrequiredforthemtocompletetheirtasks. Chapter12•SecuringtheDatabase141ToseewhathashappenedtotheusersystemtableaftertheseGRANTandREVOKEcommands,issuethefollowingqueryinthegraphicalclient:SELECT*FROMuserWHEREuser=‘Mary’Noticethatalthoughtheusertableisasystemtable,itisstillatablethatyoucanapplyanyqueryto,aslongasyouhavetheaccessrights.AlthoughwerevokedtheALLPRIVI-LEGESright,thereisstillanentryforMaryintheusertable,aswecanseebylookingatFigure12.6.YouwillseefromthesmallselectionofprivilegecolumnsinFigure12.6thatthesecolumnshavenowallbeensettoN.Ifyouhavethisonyourscreen,scrollacrossandyouwillseethatalloftheothercolumnshavebeensettoNaswell.Maryhasthereforelostherglobalrightstoeverything.Ifthisisthecase,howdoesthesystemknowthatshehasaccesstotheusertabletodoSELECTqueries?Thetables_privTableAccessrightstoaspecifictablearestoredwithinthetables_privtable.Thistablehasslightlydifferentpropertiesfromtheusertable.Figure12.7describesthistable,whichyoumayliketocomparewiththeusertableshowninFigure12.1.Youwillnoticethatinsteadofhavingcolumnsspecificallyforeachprivilege,thistablehasacolumnwhereyoucanexplicitlysetwhichkeywordswillworkonthespecifiedtable:tables_priv.IfweSELECTeverythingfromthistablenow,theresultsareasshowninFigure12.8.NoticethatMaryhasanentrythatallowshertoview(SELECT)theusertable.Italsoshowsyouwhograntedthataccess,inthegrantorcolumn,usefulwhenyouaretrackingdownaproblemwithaccess.WhatwillhappenifweallowMarysomefurtheraccesstotheusertable,sayallowinghertoinsertanewuser?Thefollowingscriptwillallowthis:GRANTINSERTonmysql.userTOMary@localhostFigure12.6Mary’snewentryintheusertable. 142DatabaseDesignManual:usingMySQLforWindowsFigure12.7DESCRIBEtables_priv.Figure12.8Mary’sentryinthetables_privtable.Figure12.9showswhathappensifweviewthetables_privtableafterrunningtheGRANTqueryabove.IfyoucompareitwithFigure12.8youwillseethatinsteadofaddinganotherrowfortheINSERTprivilegeithasappendedthefirstentry.Thetables_privcol-umnnowreadsSelectandInsert.TheGrantTablesTheuserandtables_privtablesareknownasthegranttables.Thesetablescontrolevery-one’saccesstoyourMySQLserver.Therearefivegranttablesinall,asshowninTable12.1.Allofthesetablesarestoredwithinthemysqldatabase,soyoucanrefertothemdirectlywhenusingGRANTorREVOKEbyaddingmysql.tothefrontofthetablename,i.e.mysql.db,mysql.tables_priv,mysql.columns_priv,etc.Generally,youdonotneedtoknowwhatisgoingonwithinallofthesetables,aslongasyouunderstandhowtheGRANTandREVOKEquerieswork.However,itisusefultoreal-izethatMySQLusesitsowntablestostoretheprivilegesin,andyoucanaccesstheseusingthenormalquerymechanisms. Chapter12•SecuringtheDatabase143Figure12.9Mary’samendedentryintables_priv.Table12.1TheMySQLgranttables.TablenameUsesuserStorestheglobalprivilegesforthewholeserver(examinedinFigure12.1).dbStorestheprivilegesforaspecificdatabaseontheserver(examinedinFigure12.10).hostStoresdatabase-specificprivilegesforthespecifiedhost.tables_privStorestheprivilegesforaccesstoagiventablewithinaspecificdatabase(examinedinFigure12.7).columns_privStorestheprivilegesforaccesstoagivencolumnwithinaspecifictablewithinadatabase.Figure12.10Thedbtable. 144DatabaseDesignManual:usingMySQLforWindowsFLUSHPRIVILEGESThereisonepointthatyouneedtobearinmindthough.WhenyouusetheGRANTandREVOKEqueries,thesystemrealizeswhatyouaredoingandsomakesanyaccesschangesimmediately.Somechanges,suchasglobalprivileges,willtakeeffectthenexttimetheuserconnectstotheserver.However,ifyouusenormalSQLqueriestochangeauser’sprivi-leges,thesystemjusttreatstheseasnormaldatabasequeries.Theuser’saccessrightshow-everdonotchangeimmediately.Tomaketheserverrealizethatyouhavejustcommittedsomeprivilegechanges,youhavetotypethefollowing:FLUSHPRIVILEGESThisalertstheMySQLservertoaprivilegechangeandmakesitreloadthegranttables.GRANTGRANTisusedtogiveaprivilegetoauser.Ifdetailsofthatuserdonotexistinanyofthegranttablesthenanewuseriscreated.GRANTcanthereforebeusedtocreateusers.GRANTtakesthefollowingformat:GRANTprivilegeONsystemobjectTOusernameIDENTIFIEDBY‘password’Intheabove:•usernameandpasswordarethecredentialsthattheuserneedstoconnecttothedata-base.Notethatyouneedthequotemarksaroundthepasswordstring.TheIDENTIFIEDBYclauseisoptional,butcanbeusedtochangethepasswordofauserthatalreadyexists.IfthisisomittedwhenGRANTingaprivilegetoauserthatdoesn’texist,theuseriscreatedwithoutapassword.•privilegeistheactionthatyouaregoingtoallowtheusertoperform.Table12.2showssomeoftheprivilegesthatareavailable.•systemobjectiswhattheuserwillbeperformingthataccesson.Itisusuallyusedwiththefollowingformat:database.tableso*.*referstoalltablesinalldatabasesonthisserver.REVOKEREVOKEisusedtoremoveagivenaprivilegefromauser.REVOKEwillfailiftheuserhasnotbeenpreviouslygrantedtheprivilege.REVOKEtakesthefollowingformat:REVOKEprivilegeONsystemobjectFROMusername Chapter12•SecuringtheDatabase145Table12.2SomeprivilegestousewithGRANT.PrivilegeAllowstheuserto:ALLPRIVILEGESUseallprivilegesavailableonthegivenobjectALTERChangetabledatastructureswithALTERTABLECREATECreatetablesCREATETEMPORARYTABLESCreatetemporarytablesDELETERemoverowsfromtableswithDELETEDROPRemovetableswithDROPEXECUTERunastoredprocedure,howeverstoredproceduresarenotimplementedinthecurrentversionofMySQLFILEReadandwritefilesINDEXCreateandremoveindexesINSERTInsertdataintotablesLOCKTABLESLOCKtablesthattheyhavetheSELECTprivilegeonRELOADUsetheFLUSHcommandSELECTRetrievedatafromtablesusingSELECTSHUTDOWNShutdowntheMySQLserverUPDATEChangethecontentsoftableswithUPDATEInthecode:•privilegeistheactionthatyouarerevokingfromtheuserandusesTable12.2inthesamewayastheGRANTcommand.•systemobjectiswhattheuserisbeingdeniedaccessto.IfyourevokeaprivilegesuccessfullyyougetnofeedbackfromtheMySQLserver.However,ifyourevokeaprivilegethatauserdoesnothave,forexample:REVOKESELECTONmysql.tables_privFROMMary@localhostyouwillgettheerror:Thereisnosuchgrantdefinedforuser‘Mary’onhost‘localhost’ontable‘user’IfyouREVOKEallprivilegesthathavebeenpreviouslygiventoauser,thisdoesnotdeletethatuserfromtheusertable,buttheywillhavenofurtheraccesstothesystemuntilGRANTedmoreprivileges.Theuserwillstayvaliduntiltheyareexplicitlydeletedfromtheusertable.RestrictingUsersBearinmindthatusersofyourMySQLdatabasewillveryprobablybedifferentfromanyusersthatauthenticatetoawebsystemthatyouarebuilding.YourMySQLtablewillprob- 146DatabaseDesignManual:usingMySQLforWindowsablyhaveyouradministrativeuser,root,whichisusedforcreatingyourtablesandanymanualadministrativetasks,andageneralwebuser,say,web_anonymous,thatisusedbythewebservertoaccessthetables.Dependingonwhatyourwebsystemdoes,youwillgivetheanonymoususerlimitedprivileges,suchas:GRANTSELECTONmysqlfast.webpageTOweb_anonymous@localhost;GRANTINSERTONmysqlfast.logTOweb_anonymous@localhost;GRANTINSERT,SELECTONmysqlfast.cookiesTOweb_anonymous@localhost;GRANTINSERT,SELECTONmysqlfast.visitorbookTOweb_anonymous@localhost;Thesequerieswillallowthewebserverto:•retrievethecontentsofwebpages,•addentriestothelogtablewhenapageisviewed,•checktheexistenceofacookieandcreateanewoneifnecessary,and•createnewvisitorbookentriesandviewexistingones.YouwillseefromthechaptersthatfollowthewaythatyouembedtheusercredentialsintothescriptinglanguagethatyouareusingtoaccessMySQLandbuildthewebpageswith.Ifyouonlyallowtheweb_anonymoususertheselimitedprivileges,thereislittlethatthewebservercandotodamageyourdata.Forinstance,withthesetofprivilegesabove,itwouldbeimpossibleforthatusertoremoveawebpage,i.e.DELETEFROMwebpageWHEREtitle=‘Home’astheuserdoesnothaveDELETEaccessonthewebpagetable.Unlessyouwroteasectionofthewebsitetoeditanddeletewebpages,youwouldhavetologinasrootandmanuallydeletethepageusingSQLtoremovethehomepageifrequired.OtherPasswordsAlthoughtheexampleswehavebeenusinginthisbookhavereferredtoafictionalwebsite,manysitesusedatabasestoauthenticateusers.Forinstance,searchengineslikeYahooallowyounotjusttosearch,butalsotologintothemtogainmorefacilities.Itislikelythat Chapter12•SecuringtheDatabase147yourusername,passwordandmanyotherthingsthatyouenterintothesitearebeingstoredbehindthescenesinadatabase.Ifyouaredesigningsuchasystemyoushouldgivesomethoughttohowyouaregoingtostoreyourpasswords.Thetemptationistostorethemasplaintextstrings,butthatisnotthesafestwayofdoingit.IfyoulookbackatFigure12.6youwillrememberthatSQLautomaticallyencryptedthepasswordsothatitwasnotstoredinanobviouswaywithintheusertable.Soifyouarecreatingyourownusertableforyourwebsite,MySQLhasprovidedanencryptfunctionthatallowsyoutocodeyourpasswordsbeforeyoustoretheminthetable.ThisfunctioniscalledPASSWORD(),andisusedasfollows:PASSWORD(‘password_string’)Thefunctionwillreturnanencryptedversionofthepasswordstring,whichcanthenbeinsertedintoyourdatabasetable.Forexample,thefollowinginsertstatementwouldinsertanencryptedpasswordintoatablecontainingyourwebusers:INSERTINTOwebuser(username,password,email)VALUES(‘mountainman’,PASSWORD(‘bigfoot’),’someone@nowhere.com’)Inreality,ofcourse,insteadofrunningthequeryasabove,youwouldhaveinsertedvari-ablesobtainedfromwebformfieldsintothetable,andyouwouldhaveencryptedthevari-ableusingPASSWORD,notjustthetypedtext.Interestingly,thereisnoDECRYPTfunction.Thisistostopyoudoing:SELECTuser,DECRYPT(password)FROMmysql.userThisobviouslywouldbeahugesecurityrisk.Sohowdoyouchecktoseeifsomeonehasenteredthesamepasswordthatisinthedatabase?Well,everytimethatyouencryptthesametextstringwithPASSWORD,italwaysproducesthesameencryptedstring.Forinstance,runthefollowingquery:SELECTPASSWORD(‘yeti’),passwordFROMuserThisquerywillencryptthewordyeti,andoutputitalongwithallofthepasswordstringsstoredintheusertable.Figure12.11showstheresults.YoumayrememberwhenweGRANTedsomerightstotheMaryuser,weidentifiedherbythepasswordyeti.LookattheresultsinFigure12.11andyoushouldseethatthe6throwdowncontainsthesamestringinbothcolumns.Soyoucancompareencryptedstringstoseeiftheymatch.Ifanexistingusertypedhispasswordintoawebpage,wecanencryptitanduseitinawhereclausetocheckthatthepasswordtypedisthesameastheonestored.Forexample,thefollowing: 148DatabaseDesignManual:usingMySQLforWindowsFigure12.11Passwordencryption.SELECT*FROMmysql.userWHEREuser=”Mary”andpassword=PASSWORD(‘yeti’)willgivetheresultsshowninFigure12.12,returningonerow,provingthattheuserandpasswordarecorrect.However,ifyouranthefollowing:SELECT*FROMmysql.userWHEREuser=”Mary”andpassword=PASSWORD(‘abominable’)thequerywouldproducenoresults,astheMaryuser’spasswordisnotabominable,theencryptedversionisnotthesameasthestoredencryptedversion,theythereforedonotmatchandsothepasswordisprovedtobeincorrect.Tocheckforavalidusernameandpasswordcombinationinyourscriptinglanguage,simplycheckifyouhavemorethan0recordsreturnedfromthequery.Again,ifthiswasbeingusedonawebpagewewouldbeFigure12.12Matchingencryptedpasswords. Chapter12•SecuringtheDatabase149passingformvariablesintotheSQLstatementasopposedtohardcodingthemaswehaveinthequeriesabove.Intheaboveexampleweusedthemysql.usertabletodemonstrateencryptedpasswordchecking.Onaproductionsystem,however,itwouldbebesttoimplementaseparatetableinaseparatedatabasetostoreyourusercredentials.Thisisaverysimplewayofsecuringyourstoredpasswords,butisnottheonlysecurityconsiderationthatyoumayneedtotakewhenauthenticatinguserstowebpages.Ifyourapplicationneedstobesecure,thenconsiderimplementingasecurewebconnectionaswellastheencryptedpasswords,asinourexamplethepasswordwouldbesentinplaintextfromthewebformtotheMySQLserver.Onceitisthereandencrypteditissafer,butifinterceptedonthewaywillstillcauseabreachofyoursecuritysystem. Thispageintentionallyleftblank OptimizingMySQL13TablesGetBiggerAllofthetablesthatwehavecreatedduringthecourseofthisbookhavehadlessthan10rows.WehavealreadytalkedabouthowMySQLisaveryfastwayofhandlingdata,butwhenweareusingsuchsmallamountsofdatathereislittleopportunitytotestitsspeed.However,ifwewerestoringthelogofaverypopularwebsiteindatabasetables,the30000hitsthatsitemayreceiveinadaywouldsoongatherahugeamountofdata.Assoonasweneedtoprocesslargeamountsofdatawecanintroducedelaysintooursystems.Tomini-mizethesedelaysandtomakeourqueryprocessingasfastaspossible,weneedtogivethoughttohowwellourqueriesarewrittenandwhatwecandotoimprovetheirspeed.HowWellDoesItRun?InChapter7,wedealtwithjoiningtablestogether.Thiscanbeacomplexprocessthatcanbeveryprocessorintensiveonlargetables.Youmayrememberthatwelookedatthefol-lowingscript:SELECTlog.idaslogid,webpage.titleASpagetitle,log.browser,log.datecreatedASlogdate,log.ipnumber,log.referringpage,cookies.datecreatedAScookiecreatedFROMlog,webpage,cookiesWHEREwebpage.id=log.webpageidANDcookies.cookieid=log.cookieidIfyourememberthatchapter,thisscriptlookedateachentryinthelogtable,showedthenameofthepagetheywerelookingat,andshowedthefirstdatethatthatcookieuserviewedthesite.Figure13.1showsthatqueryinaction.Withthelimitednumberofrowsthatwehaveinthetablesinvolved,thequeryproducessevenrowsofresults.LookbackatthescriptthatgeneratedtheresultsinFigure13.1.Althoughtheresultsarequitesmall,thescriptisreasonablycomplex.Butwhatisactuallygoingonbehindthe151 152DatabaseDesignManual:usingMySQLforWindowsFigure13.1Theoutputfromajoinofthreetables.scenes?Ifweweretorunthesamescriptwithalogtablethatcontained1000000rows,wouldthatscriptrunquicklyorcouldwemakeitrunmoreefficiently?ExaminingQueriesIfyouhavejustenteredtheabovequeryintotheMySQLclient,clickthecursortothestartofthequeryandtypethewordEXPLAINinfrontofthequery,asshowninFigure13.2.EXPLAINcanberunonatableoronaquery.Runningitonatable,asfollows:EXPLAINtablenameFigure13.2Explainingaquery. Chapter13•OptimizingMySQL153givesthesameresultsasdescribingatableasfollows:DESCRIBEtablenameEXPLAINcomesintoitsownwhenyouapplyittoaqueryaswehavedoneinFigure13.2.SimplyaddthekeywordEXPLAINinfrontofthequerythatyouwishtoanalyse.WhenweEXPLAINourthreetablejoinquerywegettheresultsshowninFigure13.3.WhenyouEXPLAINaquery,theMySQLserverdoesnotactuallyrunthequery,butgivesanapproximationofwhatitwouldhavetodowereyoutorunit.Thismeansthatifyouwereabouttorunaqueryonahugeamountofdata,youcouldEXPLAINthequeryfirsttomakesurethatitwouldruninthebestway.ThefirstcolumnoftheEXPLAIN,table,showsthetablesthatthequerywillhavetoref-erencetoshowtheresults.Inthiscaseitisthreetables,webpage,logandcookies.OfcoursewedonotactuallyneedtouseEXPLAINtotellusthis,buttherestofthecolumnsareofmoreuse!Thesecondcolumn,type,signifiesthetypeofjointhatMySQLwillhavetodotoprocesstherow.ThelogtablewillusetheALLjoin,andthewebpageandcookiestableswillusetheeq_refjoin.ThefullsetofjoinsthatcanappearhereareshowninTable13.1.Thistablerunsfromtheslowestandmostprocess-intensivejointothefastest,mostefficientone.ThethirdcolumnintheEXPLAINoutput,possible_keys,showsthekeysthatMySQLhopestousetoexecutethequery.ThiscolumniscalledpossiblebecausethequerymightTable13.1JointypesusedbytheEXPLAINfacility.JointypeExplanationALLThequerywillhavetoscanallrowsofthetabletocompletethisquery.indexTheindexofthistableisusedtocompletethisquery.rangeAnindexisusedtoselectarangeofrowsfromthistableforscanningbythequery.refIfthejoincannotmatchoneuniquerow,asetofrowsfromthistableisreadforeverysetofrowsthatmatchthejoinconditionoftheothertables.eq_refOnerowfromthistableisreadforeverysetofrowsfromtheothertableswhenasinglerowcanbematchedbythejoin.constIfthetableonlyhasonerow,itisonlyreadfromonce.systemIfthetableonlyhasonerowandisreadfromonceandisalsoasystemtable,thisjointypeisnotedbytheEXPLAIN.Figure13.3Sampleexplainresults. 154DatabaseDesignManual:usingMySQLforWindowsnotactuallyuseallofthekeysinthesearch.Thekeysareindependentoftheorderofthetables,sosomekeysmayneveractuallybeprocessed.IfoneofthesecolumnsisNULL,youmaybeabletooptimizethequeryfurtherbyaddinganindextothetable.Wewilldothatlaterinthesectiononindexes.ThefourthcolumnintheEXPLAINoutput,key,showsthekeythatMySQLactuallyusedtoperformthequery.IfnokeyisusedthentheentryisNULL.IfthereisaPRIMARYentryforacolumninthepossible_keyscolumnwhichisNULLinthiscolumnthentheremaybeawayofoptimizingthequeryfurther.Theoptimizetablesectionwillshowhowtodothis.ThefifthcolumnintheEXPLAINoutput,key_len,givesusthelengthofthekeythatwasused.YoucanusethiscolumntoseeifMySQLisusingmorethanonecolumnwhenamul-tiplecolumnkeyexists.ThesixthcolumnintheEXPLAINoutput,ref,showswhichcolumnswereusedbythekeyjoin.YouwillnoticefromFigure13.3thatthelogrowcontainsaNULLforthiscolumn,showingthatnokeywasusedforthispartofthejoin.TheseventhcolumnintheEXPLAINoutput,rows,showstheestimatednumberofrowsthatthequerywillusetofulfilthejoin.Thisisnotnecessarilythenumberofrowsthatthequerywillreturn,butthenumberthatthequerywillneedtolookattoexecute.Ifyoumul-tiplyalloftherowvaluestogether,yougetanestimateofthetotalnumberofrowsexam-inedbythequery.Thenextsectionwillexaminethisfurther.ThelastcolumnintheEXPLAINoutput,extra,givesyouanyextrainformationabouthowthejoinwillbecarriedout.Table13.2showswhatyoumightfindinthiscolumn.Table13.2Possibleentriesintheextracolumn.EntryExplanationDistinctAssoonasthefirstmatchisfoundnomoresearchingwillbedonewithinthistable.NotexistsAnoptimizedLEFTJOINwillbeusedandassoonasamatchingcolumnisfoundnomorerowswillbeexaminedinthetable.RangecheckedforeachrecordMySQLtriestofindanindexforeachrowinthesetofrowsfromtheprecedingtablesandusesthisindexifavailabletoobtaintherows.UsingfilesortThistakestwopassesasithastosorttherowstoretrievetheminthesecondpassbutneedstoworkouthowtosorttheserowsoutinthefirstpass.UsingindexTheindexaloneisusedtoretreivetherequiredjoindatawithoutanyoftherowsbeingscanned.UsingtemporaryThequeryneedstocreateatemporarytabletoholdtheresultswhileprocessing.IfyoudoaGROUPBYonadifferentsetofcolumnsthananORDERBYthiswilloccur.UsingwhereAwhereclauseisusedtoselecttherowsrequiredbythequery.LookingatCross-joinsAgainYoumayrememberthatinChapter7wetalkedaboutperformingacrossjoinwhichpro-ducedalotofrowsfromtableswithonlyafewrows.Wecandemonstratethisbyrunningthefollowing:SELECT*FROMlog,cookies,webpage,visitorbook Chapter13•OptimizingMySQL155Figure13.4Aninefficientcrossjoin.Figure13.4showstheoutputofcrossjoiningthelog,cookies,webpageandvisitorbooktablestogether.Ifyouareperformingacrossjoinwithoutknowledgeofthenumberofrowsinyourtables,itisusefultouseEXPLAINandviewtherowscolumntoestimatethenum-berofrowsyoumaygetreturned.Figure13.5showstheEXPLAINrunningonthisqueryasfollows:EXPLAINSELECT*FROMlog,cookies,webpage,visitorbookAllofrowscolumnvaluesmultipliedtogether(7×6×6×7)comesto1764,whichisthesameasthenumberofrowsreturnedinFigure13.4.Wehavealreadydiscussedhowcrossjoinsareinefficient;todemonstrate,wewillrunthequeryabovewithaWHEREclausetorestrictthedatathatweareobtainingfromthequeryasfollows:SELECT*FROMwebpage,log,cookies,visitorbookWHEREcookies.cookieid>3Figure13.5EXPLAINonthecrossjoin. 156DatabaseDesignManual:usingMySQLforWindowsFigure13.6EXPLAINonarestrictedcrossjoin.Ifyouruntheabovescript,youwillgetapproximately800rowsback.Youwouldthere-foreassumethataddingtheWHEREclausemadethequerymoreefficient.However,run-ningEXPLAINonthequerygivestheresultsshowninFigure13.6.Youwillnoticethatalthoughtheactualqueryonlyreturnedaround800rows,runningtheEXPLAINshowsthat1764rowsneedtobeexaminedinordertoexecutethequery,soitisjustasinefficientasthecrossjoinbeforetheWHERErestriction.Section3.2.1oftheonlineMySQLmanualthatcomeswiththeproductgivesathoroughwalk-throughofusingEXPLAINtooptimizeacomplexquery.IndexingWhenwelookedatthepossible_keyscolumnoftheEXPLAINoutput,IsaidthatifwehadaNULLinthiscolumnthequerycouldbemademoreefficientbyusinganindex.Anindexisadatastructurethatwillspeeduptheretrievalofrowswhenappliedtoacertainsearchcriterion.Inotherwords,ifyouaresearchingforsomethinginatable,thesearchwillgen-erallybequickerifanindexexistsforthecolumnthatthedataiscontainedin.Forinstance,sayyouhavealogdatabasefulloftensofthousandsofrecordswhichcon-tainsdataforawholeyearofwebaccesses.Thedataisstoredinasortedform,asdataisinsertedintothetableinchronologicalorder;soallofthedatesareascending.IfyouneedtosearchforallofthepagehitsinJune,theserverhastosequentiallyexaminealloftherowsfortheproceedingmonthsuntilitfindstheentriesforJune.ItprobablyneedstocheckalloftherowscreatedinthemonthsafterJuneaswellifitdoesnotrealizethatthedataisstoredindateorder.Ifweweretoaddanindextothedatecolumninthistable,whensearchingforJune,theMySQLserverwouldexaminetheindexandfindthatthetableissortedandrealizethatitcanstarttheretrievalapproximately60000recordsintothetable.Thismightactuallybehalf-waythroughMay,butthismeansthatmuchfewerrowsneedtobeexaminedbeforefindingtheJuneentries.Then,assoonastheservercomesacrossaJulyentrythesearchcanstop,becauseifthedatesaresorted,nomoreJuneentriescanoccuroncetheJulyonesbegin.Ifthetablecontainsapproximately10000rowspermonth,theindexwillhavesavedtheserverfromlookingatover100000rows,thatis,theJanuarytoMayrowsandtheJulytoDecemberones. Chapter13•OptimizingMySQL157CREATEINDEXTheaboveisanextremelysimpleexplanationofanindex,andtherearemanytypesofindexes.Ifyouwanttounderstandthesemorethenyoushouldobtainoneofthemanydatabasetheorybooksthatareavailable.However,ifyouwanttoquicklyaddanindextoatable,youcandoitwithoutmuchthoughtbyusingthefollowing:CREATEINDEXindexnameONtablename(columnname)IfyouarecreatinganindexonaPRIMARYKEYcolumn,whereallofthedatainthecol-umnwillbeunique,youcancreateauniqueindexasfollows:CREATEUNIQUEINDEXindexnameONtablename(columnname)YoucanalsousetheALTERcommandtoaddanindextoatableasfollows:ALTERTABLEtablenameADDINDEXindexname(columnname)Onallthreeofthescriptsabove,thecolumnnamecanactuallyrefertooneormorecolumns,separatedbycommas,asyoucanhaveanindexthatreferstomultiplecolumns.OneoftheEXPLAINcommandsthatweissuedearliershowedthatthejoincommandwouldnotuseaprimarykeyasapossiblekeyforthejoinonthelogtable.Thiswouldmeanthatwecouldspeedthequeryupbyusinganindexonthelogtable.Ifwewillberunningthisqueryoften,wecanoptimizeitbyaddingtheindex.Thequerywillbeusingtheweb-pageidcolumntojoin,sowewilladdanindextothetableasfollows:ALTERTABLElogADDINDEXlogindex(webpageid)WeneedtoEXPLAINthequeryagaintoseeifthenewindexhasmadeanydifferencetothequery,sorunitagainasfollows:EXPLAINSELECTlog.idaslogid,webpage.titleASpagetitle,log.browser,log.datecreatedASlogdate,log.ipnumber,log.referringpage,cookies.datecreatedAScookiecreatedFROMlog,webpage,cookiesWHEREwebpage.id=log.webpageidANDcookies.cookieid=log.cookieid 158DatabaseDesignManual:usingMySQLforWindowsFigure13.7Optimizingaquerybyaddinganindex.IfyoucomparetheresultsfromthisEXPLAINshowninFigure13.7,withtheresultsfromthepreviousEXPLAINinFigure13.3,youwillnoticethatthelogrownowhastheindexentryinthepossible_keyscolumninsteadofNULL.Soifwewererunningthisqueryonahugesetofdata,theindexwouldoptimizethisquery,andmakeitrunbetter.Indexesnotonlymakeadifferencetoajoin,infacttheycanalsomakeadifferencetoanyretrievalofrowswhereyouarerestrictingtheoutputbyvaluesinacolumn.Iftheindexreferstoacolumnthatisbeingusedbythequery,thentheretrievalwillbequicker.OPTIMIZETABLEAsmentionedwhendiscussingdatatypes,theconstantinsertionanddeletionofrecordsinvariablelengthcolumntypescausesthetabletobecomefragmentedduetoentriesandspacebecomingde-allocatedandre-allocated.Thisfragmentationcancausequeryopera-tionsonthetabletobeslowerthanexpected.Toremedythis,usethefollowing:OPTIMIZETABLEtablenameYoucanoptimizemorethanonetableatoncebyseparatingthetablenameswithcom-mas.OPTIMIZETABLEwilldefragmentthetable’sdatafileandrecoveranywastedspace.Itfirstrepairsanysplitordeletedrowsifitfindsthem,thensortstheindexpagesiftheyareoutoforder,andthenupdatesthetable’sstatisticsiftheindexsortdidnotcompletetherepair.Becauseoptimizingatablecaninvolvemovinglargepartsofthetablearound,MySQLstopsotherprocessesfromchangingthetablewhileitisrunning.Thecomplexdatamanipulationcouldbehinderedbyanotherusertryingtoinsertsomethingintothetableduringtheoperation.Forsomereason,thegraphicalMySQLclientdoesnotruntheOPTIMIZETABLEcom-mand,sotheoutputwhenrunintheMySQLmonitorisshowninFigure13.8.Youwillnoticefromthefigurethatifthetableisalreadyoptimized,MySQLdoesnotattempttooptimizeitagain,aswecanseefromthesecondtimethatwerunthecommandinthefigure.Ifthisisthecase,itletstheuserknowthatthetableisuptodate.TheinsertionordeletionofarowinthetablewillallowyoutoruntheOPTIMIZETABLEcommandagainshouldyourequireit.However,thereislittleneedtorunthiscommandimmediatelyafteryouhavejustrunit. Chapter13•OptimizingMySQL159Figure13.8Optimizingatable.CHECKTABLETheCHECKTABLEcommandallowsyoutocheckoneoraseriesofyourtablesforerrors.Thecommandisusedasfollows:CHECKTABLEtablename,tablename,tablename,…optionsUseasmanytablenamesasyoulikeseparatedwithcommas.optionscanbeoneofthefollowing:•QUICKdoesnotcheckforwronglinks.•FASTjustcheckstableswhichhavenotbeenclosedinthecorrectway.•CHANGEDchecksrowsasaboveandtablesthathavechangedsincethelasttimethatCHECKTABLEwasrun.•MEDIUMcheckstherowsinthetableforcorrectdeletedlinks,generatesachecksumfortherowsandverifiesthisfromthekeychecksums.•EXTENDEDensuresthatthetableis100%correctandcantakesometimetorun.TheCHECKTABLEcommandwillreturnatablewiththeresultsofthecheckinit.AsampleoutputforsomeofthetableswehaveusedinthisbookisshowninFigure13.9.Youwillnoticethattheoutputprovidesthenameofthetable,theoperation,whichisalwayscheck,amessagetypeandthetextofthemessage.Themessagetypecanbeeither:•status•error•info,or•warning. 160DatabaseDesignManual:usingMySQLforWindowsFigure13.9Checkingtables.ThelastrowforeachtablenameshouldalwaysbeastatusmessagereadingOK.Ifitdoesnotthentheremaybesomeproblemswiththetable,andsoyoushouldrunaREPAIRTABLEtotryandremedythisfault.REPAIRTABLEAswehavejustexplained,afterrunningaCHECKTABLEyoumaywellhavesomeerrorsintheoutput.AlsoiftheMySQLserverhasbeenperformingstrangely,orunexpectedlybeencrashing,thismaybeduetoerrorssomewhereinthetable’sdata.Ifthisisthecase,aftercheckingyourtable,youmayneedtorunREPAIRTABLEonittocorrectanyprob-lems.Youdothisusingthefollowingcommand:REPAIRTABLEtablename,tablename,tablename,…optionsAsyoucangetmanyrowsoffeedbackfromatablerepair,itisbesttoonlyrepaironetableatatime.Theoptionscanbe:•QUICKonlytriestorepairtheindextreeofthetable.•EXTENDEDcreatestheindexesrowbyrow.•USE_FRMrecreatesthetablefromthe.frmfileinthetablesdirectory.YouwillgetasimilartableofresultsfromREPAIRTABLEasyoudidwithCHECKTABLE.IfthelastrowofresultsdoesnotshowanOKmessage,youneedtotryandrepairMySQLfromthecommandline,notthroughthemonitor.Todothis,startadossession,changethedirectorytothedirectorywhereyourdatabaseisstored,i.e.cdmysqldatamysqlfast Chapter13•OptimizingMySQL161Figure13.10Repairingatablewithmyisamchk-o.andthentype:c:mysqldatabinmyisamchk–otablenametablenameisthenameofthetablethatyouaretryingtorepair.ThisprocessandtheresultscanbeseeninFigure13.10.ANALYZETABLETheANALYZETABLEcommandisanothertooltoallowyoutooptimizeyourMySQLtables,whichisusedasfollows:ANALYZETABLEtablename,tablename,tablename,…ANALYZETABLEwillexaminethekeydistributionwithinthetableandstoretheresults.Thesekeysareusedwhenyouarejoiningthetablewithothers,andifthekeydistributionisstoredthisway,itwillmakesomejoinsrunquicker.Again,thiswillgenerateatableofresultssimilartothoseshowninFigure13.9. Thispageintentionallyleftblank SharingMySQLdatawithMyODBC14It’sNotJustForMySQLAsyouprobablyrealizedbeforeyouboughtthisbook,databasesareimmenselyusefulthings.Theycanprovidereal-timestatisticsofwebservers,storethewholecontentsofawebsiteorproductcatalogueforeasyupdating,providemanagementinformation,producemailshotstoclients,andthelistgoeson.JustbecauseallofyourdataisstoredinMySQLdoesnotmeanthatthatiswhereithastostay.OneofthebeautiesofMySQListhatyoucansharethedatabetweennotjustmultipleusersbutmultipleapplicationsaswell.AcommonuseofMySQListopowerdatabase-drivenPHPwebsites,butthatisnottheendofthematter.AlthoughPHPandMySQLareoftenseenworkingtogether,donotthinkthatyouareconfinedtousingjustPHPforMySQLwebsites.MySQLhaveprovidedstandarddatabasedriverswhichallowyoutouseMySQLdatawithanyprogramthatcantalktothesedrivers.WhileyourMySQLdatabaseisservingyourwebserver,thereisnothingtostopitalsoprovidingdataobtainedfromyourwebsitetopopulatemail-shotsinMicrosoftWord,ordrivingotherwebapplicationtoolslikeASPandColdFusion.MoreStandardsIfyouhavebeeninthecomputingindustryforsometime,youmayremembertheprob-lemsthatusedtooccurwhenyouweresettingprintersup.BeforeWindows,everypersonwhowrotesoftwarehadtocustomizetheirsoftwaretomakeitcompatiblewiththeirpro-grams.Buyingaprintercouldoftenmeanhoursofconfiguringandguessingtogetittoworkwithyourprogram.Thischangedwhenprinterdriversstartedtobeused.Youthenmadeyoursoftwaretalktoavirtualprinterdevice,anditwouldprintwithouthavingtomakeanyrealchangestoyoursoftware.Alltheprintermanufacturerswouldneedtodoistoproducedriverstomaketheirprinterworkwiththevirtualdrivers,asopposedtodriv-ersforeverypieceofsoftwarethatneededtoprint.Thesameconceptofdrivershasbeenappliedtomanyaspectsofcomputing,andonWindowssystems,databasescanbeusedbyotherprogramsbymeansofODBCdrivers.OpenDatabaseConnectivityOpenDatabaseConnectivity(orODBC)isamethodofsharingdatabetweendatabasesandotherapplications.ThisisdonebymeansofODBCdriverswhichhavebeenwrittenbythe163 164DatabaseDesignManual:usingMySQLforWindowsdatabasevendors,andtheabilitytocommunicatewiththatdriverinSQLbythesoftwarethatneedstousethedatabase.AswellasbeingusedonLinuxandwithPHP,MySQLisabletointerfacetoothersystemsusingODBC.ODBCisamethodofallowingaprogramtotalktoadatabase.Allthepro-gramhastodoistalktotheODBCdriver,whichhandlesanyspecificrequirementsofthedatabaseserver.NomatterwhattheDBMS,aslongasthecorrectODBCdriverexistsforitthentheapplicationcantalktoit.DataSourcesTheODBCconnectionbetweenyourspecificdatabaseandthesoftwarethatistalkingtoitiscalledadatasource.Youwillusuallyrefertothatdatasourcebyitsname.Alldatabasequeriesaresenttothedatasource,whichusestheODBCdrivertotalktothedatabaseserver.Dataobtainedfromtherequestisthenpassedbacktotheclientsoftware.Youcanhavemanydatasourcesontheclientmachine,andsomeofthesourcescanpointtodatabaseserversonremotemachines.Todifferentiatethough,theymustallhaveuniquenames.ObtainingMyODBCTheODBCdriverthatMySQLusesiscalledMyODBC.Thiscanbeobtainedfromthedown-loadssectionofthewww.MySQL.comwebsitewhichcancurrentlybefoundatthefollow-inglink:http://www.mysql.com/downloads/index.htmlFigure14.1showsthepageattheaboveURL.IfyouscrolldownyouwillseethelinktoMyODBCundertheOfficialAPIssection.Thereareusuallythreetypesofreleasesunderthissection,acurrentworkingrelease,anoldreleaseandareleasethatisbeingtested.Itisusuallybesttogoforthecurrent,stablereleasewhichinthefigureisproductionrelease3.51.ClickontheConnector/ODBC3.51linkandyourwebbrowserwilltakeyoutoanotherpagewhichallowsyoutoselecttheoperatingsystemthatyouwillbeusingMyODBCwithandfinallydownloadthedriver.InstallingMyODBCDriversAfterdownloading,doubleclickontheMyODBC.exefilethatyoujustobtainedtostartinstallation.YouwillbepresentedwithascreenasshowninFigure14.2.Afterreadingthemessages,clickonNextonthescreenshowninFigure14.2.TherearenootheroptionstochooseinthissetupsoyoucanjustclickNextonthefollowingscreensunlessyouwanttoreadtheothermessagesthatyouaregiven.WhenyougettothescreenshowninFigure14.3,youhavecompletedinstallingtheMyODBCdrivers. Chapter14•SharingMySQLdatawithMyODBC165Figure14.1DownloadingMyODBC.Figure14.2InstallingMyODBC. 166DatabaseDesignManual:usingMySQLforWindowsFigure14.3SuccessfulinstallationofMyODBC.SettingUpaDataSourceThenextstepinusingMyODBCissettingupyourdatasource.ThiscanbedonedirectlyinWindowsbyusingthedatasourcecontrolincontrolpanels.SelectControlPanelfromyourstartmenu.DependingonyourversionofWindows,youmayseetheDataSourcesorODBCcontrolstraightaway.InWindowsXPyoufirstneedtoopenuptheAdministrativeToolssectionofthecontrolpaneltofindthecorrecticon.TheiconisshowninFigure14.4.DoubleclickonthedatasourceorODBCiconinyourcontrolpanel.ThedatasourcecontrolisshowninFigure14.5.Youwillnoticethattheremayalreadybesomedatasourcessetup.Youcanhavetwodif-ferenttypesofdatasource,auserdatasourceandasystemdatasource.Theuserdatasourceisonlyavailabletothecurrentuserwhoisloggedin,whereasthesystemdatasourceisavailabletoanyonewhoisusingthemachine.Althoughtherearesomesecurityissueswithusingasystemdatasource,thisisthetypethatwewillbeusingforourexample.ClickontheSystemDSNtabandclickonAdd.Youwillthenbepresentedwithalistofdrivers,asshowninFigure14.6,whichwillvarydependingonwhatsoftwareyouhavepreviouslyinstalledonyourmachine.ScrolldownuntilyoufindtheMySQLentrythatcorrespondstothedriversyouhavejustinstalledandclickonit.Next,clickontheFinishbutton.ThisdoesnotactuallyfinishthedatasourcesetupbuttakesyoutotheoptionsforthenewdatasourceasshowninFigure14.7.IfyouareusinganearlierversionofMyODBC,thismaywelllookdifferent.Fillinthefieldsinthiscontrolasshowninthefigure,referringtothefollowing:•TheDataSourceNamewillbethenamethatyourefertothedatasourcewithwithinyourcode. Chapter14•SharingMySQLdatawithMyODBC167Figure14.4RunningODBCsetup.Figure14.5Thedatasourcecontrolpanel. 168DatabaseDesignManual:usingMySQLforWindowsFigure14.6SelectingtheMyODBCdriver.Figure14.7MyODBCdatasourceoptions.•TheDescriptionfieldisnotusedwithinyourcode,soitisnotthatimportant;however,ifyouaresettingupthisdatasourceonasystemthathasotherdatasources,giveitameaningfuldescription.•TheHost/ServerNamefieldneedstocontainthenameofthemachinethatyourMySQLserverisrunningon.Ifyoujusthavethisonyourlocalmachine,youcanuselocalhost. Chapter14•SharingMySQLdatawithMyODBC169Ifyouareconnectingtoaremotemachinewiththeserveronthenspecifyitusingitsdomainname,i.e.mysql.domain.com.YoucanalsousetheIPnumberofthemachine,i.e.192.168.1.100•TheDatabaseNamefieldisusedtoselecttheMySqldatabasethatyouareconnectingto.Forthisexamplewewillusethesystemmysqldatabase.ThisissafeifyouarejustusingyourMySQLdatabasetorunthroughtheexamplesinthisbook,butifyouareonaliveserverwithotherusersIwouldrecommendcreatinganewdatabasetoconnectyourdatasourcetoforsecurityreasons.•TheUserandPasswordfieldsaretheusercredentialsthatMyODBCusestoconnecttotheMySQLserver.Youcanuseanyvalidusernameandpassword,butagainforthesamereasonsasaboveitmaybebettertouseauserthathasbeenspecificallycreatedforODBCMySQLaccess.Itwouldbeabadideatouseyourrootaccount,asthismeansthatthedatasource,ifitcouldconnecttothesystemmysqldatabase,couldhavefullaccesstoviewandchangeallofyourMySQLdata.•Unlessyouhavechangeditfromthedefaultsettings,leavethePortfieldatthedefaultsettingof3306.•TheSQLcommandonconnectfieldisusedtoautomaticallyrunacommandassoonasthedatasourcemakesaconnectiontotheMySQLserver.Leavethisblankforourexam-ple.IfyouareusingtheODBCsourcetoconnecttoaserveronanothermachine,theusermusthaverightstoconnectfromthismachineinthehostcolumnoftheusertable.Forinstance,ifthemachinewiththeMyODBCconnectionhadtheIPnumber192.168.1.1,andtheMySQLserverhadthenumber192.168.1.100,thefollowingwouldapply:•Ausercreatedwiththefollowingcommandswouldnotbeabletoconnect:GRANTSELECTONmysql.*TOremoteuser@192.168.1.100;GRANTSELECTONmysql.*TOremoteuser@localhost;•Ausercreatedwiththefollowingcommandswouldbeabletoconnect:GRANTSELECTONmysql.*TOremoteuser@*;GRANTSELECTONmysql.*TOremoteuser@192.168.1.1;IfyouareusingadedicatedwebserveranddedicatedMySQLserverforyourwebsiteonseparateboxes,itisbesttoclearlydefinethedatasourceusersothatitcanonlyconnectfromthespecificwebserversthatrequireitbyspecifyingtheservernameorIPaddressandnotusingwildcardsforthehostentries.ThiswouldsecureyoursetuptostopremoteusersconnectingtoyourMySQLserverfromothercomputers.Rememberthatifthedatasourceisbeingusedbyawebserver,andauseronanothercomputer,say192.168.1.200,connectstothatwebserver,thedatasourceconnectiontotheMySQLserverwillcomefromtheweb-server,notthemachine192.168.1.200.Onversion3.3.1ofMyODBCyoualsohaveabuttonthatallowsyoutotestyourconnec-tiontothedatasource.Itiseasiertotesttheconnectionnow,atthiscontrol,ratherthantry- 170DatabaseDesignManual:usingMySQLforWindowsFigure14.8SomeMyODBCdatasourceerrors.ingtodebugconnectionproblemsintheprogramthatisaccessingthedatasource.Figure14.8showsthreeofthepossibleresponsesfromclickingontheTestDataSourcebutton.ThefirsterrorinFigure14.8showsthetypeoferrorthatyoumaygetifyouhavespeci-fiedthehostnameoftheMySQLserverincorrectly.Thelasterrorshowninthefigurecanoccuriftheusernameorpasswordisincorrect,oriftheuserdoesnothaveaccessrightsfromthemachinewherethedatasourceistryingtoconnectfrom.Onceyourdatasourcehastestedcorrectly,youwillbeabletorefertoitanduseitinotherprogramsthatcancommunicatewithdatasources.ExampleConnectionsTodemonstratehowtousetheMyODBCdatasourcefromotherprograms,wewillshowexamplesofitsusewithASPandwithColdFusion.ASP,orActiveServerPagesisatechnologythatallowsyoutocreatedynamicwebpagesbymeansofscriptsthatrunonaserver.ASPwasfirstimplementedbyMicrosoftwhenitcamebuiltintoitswebserver,IISwhichcomesbundledaspartofmanyofMicrosoft’smodernoperatingsystems.ColdFusionwasoriginallyproducedbyAllaireCorporationwhohavenowmergedwithMacromedia,Inc.ColdFusionagainisaserversidescriptinglanguagewhichallowsthecre-ationofdynamicwebpagesonyourserverbymeansoftagsthatyouinsertintoHTMLcode.ThefollowingtwoexampleswillshowhowyoucanuseMyODBCtoconnectthesetwoproductsbacktoyourMySQLservertoutilizeyourdata. Chapter14•SharingMySQLdatawithMyODBC171ItshouldbementionedatthispointthatIamsimplygoingtoshowhowtoconnecttothedatasourceandprocesssomeofthedatathatisreturned.TheseexampleswillbarelyscratchthesurfaceofusingASPandColdFusionwhichareimmenselypowerfulproductsintheirownright.Ifyouwishtoinvestigatethesefurther,theyarecoveredinotherbookspublishedbySpringer.MySQLandASPThereareseveralwaysinwhichyoucanconnectyourASPscripttoaMyODBCdatasource.ThissectionwillshowonewayusingtheMyTestDataSourcedatasourcethatwecreatedearlierinthischapterusingVBSCRIPT.AwebpagethatisgoingtobeprocessedbyASPwillresidesomewhereinyourwebspaceofyourwebserverandhavetheextension.asp,forexample:c:inetpubwwwrootmyexample.aspTosendthisfiletotheASPinterpreter,ithastoberequestedthroughthewebserverusinganinternetbrowserbygoingtothefollowinglocation:http://localhost/myexample.aspThe.aspfileisacollectionofASPcodeandHTMLtags.SomeoftheASPcodewillitselfoutputHTMLcode.Thefollowingcode,whenexecutedthroughanASPserver,willconnecttoourdatasourceandoutputsomeofitsdata:<%@LANGUAGE=”VBSCRIPT”%><%setMyODBCConnection=Server.CreateObject(“ADODB.Connection”)setMyRecordSet=Server.CreateObject(“ADODB.Recordset”)MyODBCConnection.Open“DSN=MyTestDataSource”MyRecordSet.ActiveConnection=MyODBCConnectionMyRecordSet.Open“SELECT*FROMuserWHEREuser=’mafiu’”%><%whilenotMyRecordSet.EOFresponse.write“”&MyRecordSet.fields(0)&“”&MyRecordSet.fields(1)&“”MyRecordSet.MoveNext 172DatabaseDesignManual:usingMySQLforWindowswend%>
<%MyRecordSet.CloseMyRecordSet.Open“SELECT*FROMuserWHEREuser!=’mafiu’”whilenotMyRecordSet.EOFresponse.write“”&MyRecordSet(“Host”)&“”&MyRecordSet(“User”)&“”MyRecordSet.MoveNextwend%>TheoutputofthiscodeisshowninFigure14.9.Letusexaminethiscodebrieflytoseewhateachpartdoesandhowitisused.Tobegin,wehavetotellASPwhichscriptinglanguagewewillbeusingtodefineourpage;thisisdonewiththefollowingcommand:<%@LANGUAGE=”VBSCRIPT”%>ThisletstheserverknowthatwewillbeusingVBSCRIPTwithinthispage.Alltextwithinthe<%…%>tagsisprocessedbyASP,allothertextwithinthefileisoutputasstraightHTMLtothebrowser.ThereforewecanstartbysettingupabasicHTMLpagewiththefollowing:Figure14.9ASPaccessingtheMySQLusertable. Chapter14•SharingMySQLdatawithMyODBC173ThisjustdefinesthepageasHTMLforthebrowserandsetsthepagetitletobedis-played.NowforsomeASP,assignifiedbythe<%:<%setMyODBCConnection=Server.CreateObject(“ADODB.Connection”)ThiscreatesanobjectthatallowsforaconnectiontoadatabaseandcallsitMyODBCConnection.Thisobjectcanbeusedelsewherebysendingitcommandsbyappendingthemtothenameoftheobject.Weseethisinusefurtherdownthepage.Nextwedefineanotherobjectthathandlessetsofrecordsusingthefollowing:setMyRecordSet=Server.CreateObject(“ADODB.Recordset”)ThistimewehavecalledtheobjectMyRecordSet.Nowwehavecreatedtwoobjectsneededfortheconnectiontothedatabase,wecanstartsendingcommandstothem.ThefirstofthesecommandsconnectstotheMyTestDataSourcebysendingtheopencommandtotheMyODBCConnectionobject:MyODBCConnection.Open“DSN=MyTestDataSource”Nowthatthisisopen,wecanconnectourRecordSetobjecttotheopendatasource:MyRecordSet.ActiveConnection=MyODBCConnectionThenfinallyinthispartoftheASPscript,wecanactuallysendanSQLcommandtothedatasourcewith:MyRecordSet.Open“SELECT*FROMuserWHEREuser=’mafiu’”%>Thispagewillcreateatablethathasarowforeveryrowreturnedfromthequery.WedothisbyloopingthroughtherowsreturnedbytherecordsetinASP.However,weonlywantthelooptorunontherowsofthetableitself,sotheactualdefinitionofthetableisoutsidetheloop,instandardHTMLasfollows:Nowfortheloop.ForeachrowofthetablewewanttooutputanHTMLtablerowasfol-lows:HostcolumnUsercolumn 174DatabaseDesignManual:usingMySQLforWindowsTheRecordSetobjecthasacommandthatreturnsatrueifyouhavereachedtheendoftherecordset.SwitchingtoASPagain,wesetupawhileloopthatwillkeepgoinguntiltheendoftherecordsetisreached:<%whilenotMyRecordSet.EOFTooutputHTMLfromwithintheASPscript,weusetheresponse.writecommand.Youusethiscommandbysendingastringtoitinquotes,orafunctionorobjectthatreturnsastring.Asthenextlineneedsastringmadeupofbothtextandtheoutputofthequery,wecanturnthemallintoonestringbyusingthe&operator.Thescriptcontinues:response.write“”&MyRecordSet.fields(0)&“”&MyRecordSet.fields(1)&“”Thereareseveralwaysofaccessingthecolumnsthatarereturnedbythequery.Onthisoccasionwesendthefieldscommandtotherecordset,withthenumberofthefieldspeci-fiedbyusing:MyRecordSet.fields(0)Thisselectsthefirstcolumnreturnedbythequery,whichinthisexampleisthehostcol-umn.Field2istheusercolumn.Onthisoccasionthefieldsthatarereturnedareintheorderwhichtheywerecreated,asweusedthewildcard(*)intheselectquerytoobtainthem.However,ifwehadsentthequery:SELECTuser,hostFROMuser;thenthecolumnswouldbereturnedintheselectorder.Bearthisinmindwhenyouareusingthefieldscommandwiththerecordset,asyoumaygetunexpectedresultsfromnotspecifyingtheexactcolumnname.Nowthatwehaveoutputthetablerowforthisrowoftherecordset,wecanmovetothenextrecordinthesetbyusingthefollowing:MyRecordSet.MoveNextThisdoesnotendthewhileloop,justmovestothenextrecord.Totriggertheendofthewhileloopweuse:wend%>Thiswillreturntothetopofthewhileloopandiftheendoftherecordsethasnotbeenreachedrepeattheprocessingforthenextrowintheset.Ifweareattheendoftheset,the Chapter14•SharingMySQLdatawithMyODBC175%>showswehavefinishedprocessingtheASPforthemoment,sowecancompletethetablebyclosingthetabletag:Nextwewillperformanotherquerywhichwillselectadifferentsetofrecords,butwewillaccessthefieldsspecificallyasopposedtousingtheirnumberasabove.Firstweaddalinebreakandthenopenatabletagagain.ThenwecanstartanewbatchofASPcode:
<%Beforewecansendanewquerytotherecordset,wehavetoclosetheexistingrecordsetwiththefollowing:MyRecordSet.CloseThenwesendthenewqueryandstartanewwhileloop:MyRecordSet.Open“SELECT*FROMuserWHEREuser!=’mafiu’”whilenotMyRecordSet.EOFAgain,weoutputthetabledata.Thistime,however,wespecifythecolumnnameofthequerydirectlybyusingMyRecordSet(“columnname”)asfollows:response.write“”&MyRecordSet(“Host”)&“”&MyRecordSet(“User”)&“”Wemovetothenextrecordandendtheloopinthesamewayasbefore:MyRecordSet.MoveNextwend%>Andfinally,wesendtheclosingtagsfortheHTMLscript.Thissectionmayhaveleftyouratherconfused,aswearereallydescribingaprogram-minglanguagetowhichawholebookshouldbededicated.ThiswillhavegivenyouatasterofhowtouseASPwiththeMyODBCsource.IfyouhaveaccesstoawebserverwithASP,you 176DatabaseDesignManual:usingMySQLforWindowsmaywishtotryandusethisfiletoaccessotherdataorqueriestogiveyourselfmoreexpe-rience.Insummary,youconnecttoadatasourceinASPby:•DefininganODBCConnectionobject.•DefiningaRecordSetobject.•OpeningthedatasourcethroughtheODBCConnectionObject.•LinkingtheRecordSettotheODBCConnection.•Sendingthequerytothedatabasethroughtherecordset.•Processingtherecordsetinaloop,outputtingcolumnsbynameorbytheirindex.•Ifyouneedtosendanewquerytothesameconnection,closetherecordsetandre-openitwiththenewquery.•ASPscriptisdefinedwithinan.aspfilebysurroundingitwith<%…%>.AnyothertextwithinthefileisoutputdirectlyasHTML.TheconnectiondetailsdescribedinthissectionarenotspecifictoMySQLandMyODBC;aslongasyouhavethecorrectdatasourcenameandthesametables,thiswillworkwithanODBCdatasourcefromanydatabasevendor.MySQLandColdFusionConnectingtoadatasourceinColdFusionisdonebyusingColdFusionMarkupLanguagetags(CMFL)embeddedwithinHTMLcode.WhentheColdFusionserverencountersCMFLtagsitprocessestheresultsandreturnsthemastexttobesendbacktothewebserver.ColdFusionallowsyoutosetupadatasourceremotelybyusingitsremoteadministra-torwebsite.AlthoughwehavealreadysetupadatasourceusingtheMicrosoftControlPanel,wewilldemonstratehowyoucandothisthroughColdFusion.ThenextsectionwilluseColdFusionMXrunningonlocalhostasademonstration.CreatingaDataSourceinColdFusionTheColdFusionMXadministratorisaccessedbypointinganinternetbrowseratthefol-lowinglocation:http://localhost/CFIDE/AdministratorAfterenteringyourpassword,youarepresentedwithamenuwhichhasaDataSourceslink.Clickingonthiswillgiveyouaccesstothedatasourcesetuppage.PartofthispageisshowninFigure14.10.TypeinthenameofthedatasourceyouwishtocreateonthepageshowninFigure14.10.ThenselecttheMySQLdriverfromthedrivermenu.Onceselected,clicktheAddbuttontotakeyoutothepageshowninFigure14.11.Figure14.11showsthedialoguethatallowsyoutospecifythedetailsfortheMyODBCconnection.YoucanfillthisoutusingthesamedetailsandwarningsthatweusedinthesectiononsettingupthedatasourcewiththeMicrosoftODBCcontrolpanel.Onceyousubmitthisform,thesourceisimmediatelytestedbyColdFusion.Ifyougetapagesuchas Chapter14•SharingMySQLdatawithMyODBC177Figure14.10AddingadatasourceinColdFusionMX.Figure14.11SpecifyingthedatasourceinColdFusionMX. 178DatabaseDesignManual:usingMySQLforWindowsFigure14.12Adatasourceconnectionerror.theoneshowninFigure14.12,youhaveanerrorinyoursettings,sousetheBackbuttononyourbrowserandcheckthatyouhaveenteredeverythingcorrectlyandthattheuserhasthecorrectaccessfromthemachinethattheColdFusionserverisrunningon.IfallisworkingcorrectlywiththedatasourcethenyouwillseeapagesimilartoFigure14.13.Ifthisistheonlydatasourceonthesystem,youshouldseetheOKafteryourdatasourcenametoshowthatitisverified.Ifyouhaveotherdatasourcesinstalled,youmayhavetohuntaroundabitforit.AccessingtheDataSourceinColdFusionAwebpagethatisgoingtobeprocessedbyColdFusionwillresidesomewhereinyourwebspaceofyourwebserverandhavetheextension.cfm,forexample:c:inetpubwwwrootindex.cfmThisfileisoftenreferredtoasaColdFusiontemplatefile.TosendthisfiletotheColdFusionserverithastoberequestedthroughthewebserverusinganinternetbrowserbygoingtothefollowinglocation:http://localhost/index.cfm Chapter14•SharingMySQLdatawithMyODBC179Figure14.13Averifieddatasource.ThetemplatefileisacollectionofCFMLcodeandHTMLtags.TheCFMLtagscontrolsendingofdatatothedatasourceandcontroltheoutputoftheHTMLcode.Thefollowingtemplate,whenexecutedthroughaColdFusionserver,willsendaquerytoourdatasourceandoutputsomeoftheresults:SELECT*FROMuser#USER#@#HOST#
TheresultofrunningthistemplateisshowninFigure14.14.Wewillexaminethecontentsofthistemplatetoshowwhatishappening.ThetemplatebeginswithstandardHTMLwhichispassedstraightbacktothewebserver:ThisdefinesthefileasHTMLandsetsthetitleofthepage.ThencomesthefirstCFMLtag:SELECT*FROMuser 180DatabaseDesignManual:usingMySQLforWindowsFigure14.14DownloadingMyODBC.ThisistheCFQUERYtag.Eachqueryneedsaname,asyoucanusemanyquerieswithinatemplate.Thequeryalsoneedsthenameofthedatasourcethatitistoconnectto.ThetextinsidethetagistheactualSQLquery.UnlikeourASPexampleearlier,allofthecodeforconnectingtothedatasourceandsendingthequeryishiddenfromthewebdeveloper.Toaccesstheresultsofthequery,weusethetagwithaqueryattributethatreferstothenamespecifiedintheearlier:inthisformwillloopthroughthecodeinsideitforeveryrowofthequeryresults.Iftherearenorowsreturned,processingcontinuesfromafterthetag.Insideourtagweusethefollowing:#USER#@#HOST#
Figure14.15DownloadingMyODBC. Chapter14•SharingMySQLdatawithMyODBC181ColdFusionidentifiescolumnnamesandvariableswithinitstagsbysurroundingthemwithhashes.Soforeachrowinthequery,thelineabovewilloutputtheusercolumnvalue,an@sign,thehostcolumnvalueandalinebreakHTMLtag.WethenclosethetagandcompletetherestoftheHTMLpage:AsthisisaloteasiertofollowthantheASPexample,duetothesimplicityofthetags,wewillre-writethetemplatesothattheoutputistabulatedbetter.Thistemplatewillthenreadasfollows:SELECT*FROMuser

Username	Host
#USER#	#HOST#

TheresultsoftheaboveareshowninFigure14.15.Youwillnoticehow,aswhenwelookedatASP,wecreatedtheHTMLoutsideoftheloop,andthenloopedthrougheachrowofthetableusingtheCFOUTPUTassociatedwiththequery.Insummary,toconnectanduseMyODBCdatawithinColdFusionyou:•SendtheSQLquerytothedatasourcebywrappingitinatagwhichyouname.•Usetheresultsbywrappingthecolumnnamesyouwishtouseinhashes,andusingthesenamesandotherHTMLtagswithinatagthatreferstothequeryname.AswithASP,thisdemonstrationcanbeusedtoconnectColdFusiontoaMyODBCdatasourceonitsserver,ortoanyotherODBCdatasourcethatisavailable.ItisnotMyODBCspecific. Thispageintentionallyleftblank MySQLwithPHP,ApacheandPerl15IndigoPerlAsyouprobablyrealizebynow,MySQLisapowerfulproductwhichwilltakealongtimetoexplainandmaster.AtthisstageinthebookIhopeyouwillbesomewaytounderstandinghowtouseSQLandMySQL.MySQLshowsitstruepowerwhencombinedwithotherpro-gramssuchasPHPandtheApachewebserver.OneverycommonuseofthiscombinationisPHP-Nuke,whichisshowninFigure15.1.PHP-NukeisasystemutilizingPHPandMySQLthatenablesyoutosetupapowerful,multi-user,contentmanagedwebsiteveryeasily.Ofcourse,ifyouwishtocustomizetheseFigure15.1PHP-Nukeinuseontheirownwebsite.183 184DatabaseDesignManual:usingMySQLforWindowsopensourceprogramsyouneedtoknowalittleaboutthesoftwarethatisrunningbehindthescenes.ThePHPscriptinglanguage,PerlandtheApachewebserveraresimilarlycomplexprod-ucts,andIcoulddevoteabooktoeachofthem.Evendetailingtheirsetupwouldbetoomuchforasinglechapterofthisbook.ToenablemetodemonstratehowtouseMySQLwiththeseproducts,IwashappytodiscoverasystemcalledIndigoPerlfromIndigoStarsoftware.Thiscanbedownloadedfrom:http://www.indigostar.com/indigoperl.htmIndigoPerlcombinesPHP,ApacheandtheirversionofPerlasasinglepreconfiguredproductforwindows.Figure15.2showsthedownloadpageforIndigoPerl.Youwillneedtodownloadthezipfiletousetheexamplesinthischapter.Unzipyourdownloadedfiletoatemporarydirectory,thenrunthesetup.batfilewhichwillruninadosboxasshowninFigure15.3.Afterselectingthedefaultoptionforthepro-gramlocation,thesoftwarewilltakeseveralminutestoinstallitself.Afterinstallationyouwillbepromptedtoreboot.Afterrebootyouwillseethenewtell-tale,showninFigure15.4,inthesystemtraytoletyouknowthattheApachewebserverisinstalled.Figure15.2ObtainingIndigoPerl. Chapter15•MySQLwithPHP,ApacheandPerl185Figure15.3IndigoPerlsetuprunning.Figure15.4Apachewebservericon.IftheiconshowsagreenarrowthenApacheisrunning.Ifitshowsaredsquarethenthewebserveriscurrentlystopped.Tostartthewebserver,doubleclickontheiconandclickthestartbuttoninthedialogueboxthatappears.Finally,toverifythatyourApacheserverissetupcorrectly,openupawebbrowserandgotothefollowinglocation:http://localhostFigure15.5showsthedefaultwebpagethatApacheinstallstoletyouknowthatalliswell.ThenicethingaboutIndigoPerlisthatitwillseamlesslyintegratewiththeMySQLinstallationthatyouhaveusedintherestofthisbook.Toverifythis,youwillnoticethatatthebottomofthepageareseveraltestscripts.Thelastscriptallowsyoutotestaconnec-tiontoMySQL,soclickonthisandyoushouldberewardedwiththescreeninFigure15.6.ThisscriptcreatesatestMySQLtableandinsertsacoupleofrows,whichitthenretrieves.Althoughthisprovesthatitworks,itdoesnotreallyshowhowtouseMySQLfromPHPsowewillnowwriteourownPHPscripttoaccessourdatabase. 186DatabaseDesignManual:usingMySQLforWindowsFigure15.5Apacheserverdefaultwebpage.AQuickPHPScriptPHPisascriptinglanguagethatyoucanembedintoHTMLdocuments.TheHTMLdocu-mentissavedwiththeextension.phptoindicatetothewebserverthatitneedstopassthefiletothePHPserver.Savethefollowingtoatextfile: Chapter15•MySQLwithPHP,ApacheandPerl187Figure15.6ResultoftestingconnectiontoMySQL.CopytheabovefileintotheApacherootdirectory,whichinthisinstallationwillbe:c:indigoperlapachehtdocsOnceitisthererenameittothefollowing:test.phpYoucannowviewthisinabrowser,whichwillneedtobepointedat:http://localhost/test.phpYoushouldseeawebpagewiththewordsItworksonit,andnothingelse.IfyouseethePHPcodeandtheprevioustestsworked,thenyoumaynotbelookingatthefilethroughthewebserver,oryoumayhaveincorrectlynamedthefile.InsideaPHPdocument,allHTMLtagsareprocessedasnormal,butanythingsur-roundedbyisclassedasPHPcodeandpassedtothePHPserver.Theprintcommandsimplyoutputsthestringthatyouprovide.AllPHPcommandsareterminatedwithasemicolon.AccessingMySQLMySQLisaccessedinPHPthroughaseriesoffunctionswhoseresultsarestoredinvari-ables.Youneedtogothroughthefollowingstagestoaccessyourdata:•Connecttothedatabase•Runthequery•Dealwiththeresults•Cleartheresults•Closetheconnectiontothedatabase. 188DatabaseDesignManual:usingMySQLforWindowsConnectingtotheDatabaseToconnecttothedatabaseyouusethemysql_connectfunctionasfollows:$connectionname=mysql_connect(“server”,“username”,“password”);$connectionnameisavariablethatholdstheMySQLconnection.Whenyouwishtoaccessthisdatabaselateroninthescript,youwillusethisvariabletoreferenceit.serveristhehostnameoftheserver.usernameandpasswordarethecredentialsforaccessingtheMySQLserver.Youcanseewhyitwaspreviouslysuggestedthatyouhaveaseparateuserdefinedforaccessingyourdataviatheweb.Youwouldnotwanttohavetoembedyouradministratorcredentialsineverywebpagethatyoucreated.ToquicklysetupausertogainaccesstoMySQLfromPHPonyourlocalmachine,openuptheMySQLGUIandrunthefollowing:GRANTSELECTONmysqlfast.*TOphpuser@localhostIDENTIFIEDBY“abominable”ThiswillcreateaspecificusertousePHPandgivethemaccessfromlocalhosttothemysqlfastdatabase.TheywillhaveSELECTaccessonly,sowillnotbeabletochangeany-thing.Thisisbyfarthesafestwayofgrantingwebaccesstoadatabase;onlygivetheusertheminimumaccesstocompletethetaskthattheyneedtoperform.Youcanfollowthiscommandwiththeorkeywordthatthenallowsyoutoexecutecodeifthecommandfails.Wewon’tdemonstratethisherebutonproductionpagesitisalwaysbesttocheckforerrorconditionswhenyouarescriptingtomakeyourwebpagesmoreresilient.InourPHPscriptwecanthenusethefollowingtoconnecttotheMySQLserver:$myConnection=mysql_connect(“localhost”,“phpuser”,“abominable”);SelectingtheDatabaseOnceyouhaveconnectedtotheserver,PHPgivesyouacommandthatallowsyoutoselectthedata,mysql_select_db.Thiscommandisusedasfollows:mysql_select_db(“databasename”,connection);databasenameisthenameofthedatabasethatwewillbeusing.Yourusermusthaverightstoaccessthedatabase.connectionisthevariablethatyouassignedtheconnectiontointheprevioustask.IfyouareonlyusingoneconnectionyoucanomitthisattributeasPHPwilldefaulttothelastconnectionmade.SendingaQueryWiththeconnectionready,wecannowsendthequerytotheconnection.PHPusesthefunctionmysql_queryasfollows: Chapter15•MySQLwithPHP,ApacheandPerl189$results=mysql_query(“query”);queryisastringcontainingthequerywewishtoexecute.$resultsisavariablethatstorestheresultssetthatthefunctionreturns.Sotosendaquerythatselectseverythingfromourlogtable,wewouldissuethecom-mand:$myResults=mysql_query(“SELECTwebpageid,browser,ipnumberFROMLOG”);Theaboveworksfineifyouareusingastaticquery,suchastheoneabove,becauseitiseasytoinsertthequerystringintothefunction.However,ifyouwereexecutingadynamicquery,suchassearchingforawordthatwasstoredinavariablethathadbeentypedinfromawebpage,itisbesttostorethequeryinastring,whichyoucanbuildupasrequiredfirst,andtheninsertthisquerystringintothefunctionasfollows:$searchterm=“Mozilla”;$query=“SELECT*FROMLogWHEREBrowserLIKE‘$searchterm%’”;$myResults=mysql_query($query);Nowtheresultsofthequeryhavesuccessfullybeenstoredinaresultsset,wecanbegintoprocesstheseresults.IfyouwererunninganINSERTqueryyouwouldsenditusingmysql_queryandnothavetoworryaboutprocessingtheresults.AccessingtheRecordsWehavenowstoredtherecordsinarecordsetvariable.Thereareseveralwaysinwhichyoucanobtaintheresultsfromthisset.Wewillusemysql_fetch_rowasfollows:$variable=mysql_fetch_row($resultsSet)Thevariablethatthisfunctionreturnsisanindexedarray,startingat0,foreverycellinthecurrentrowoftheresultsset.Ifweweretorunthefollowingonourresults:$row=mysql_fetch_row($resultsSet);Thefollowingwouldoutputallofthecolumnsinthecurrentrow:print“Row:$row[0],$row[1],$row[3]
”;Theindexorderinthearraycorrespondstotheorderofthecolumnsasspecifiedinthequery,orthecreationorderofthecolumnsintheCREATETABLEcommandif*wasused.Ifmysql_fetch_rowisusedasecondtimeonthesameresultsset,itwillreturnthenextrowintheset,orFALSEiftherearenomorerows.Ideally,inuseyouwillwanttoprogramsomecontrolstructurearoundtheoutputtohandlemorethanonerow.Thefollowingwilloutputalloftheresultsintherecordsetinatableusingado...whileloop: 190DatabaseDesignManual:usingMySQLforWindowsprint“”;do{$row=mysql_fetch_row($myResults);print“”;}while($row);print“

WebPageID	Browser	IPNumber
$row[0]	$row[1]	$row[2]

”;ClearingtheRecordSetAfterwehavefinishedwiththerecordsetweneedtodosomehousekeeping.Therecordsetmaycontainhundredsofrowsifaccessingalargedatabase,whichmayhangaroundtyingupsystemresourcesifyouleavethemthere.Thiscouldhavemajorimplicationsiflotsofpeoplewerelookingatthewebpageatonetime.Whenyouhavefinishedprocessingyourrecordsetyoucanusemysql_free_resultasfollows:mysql_free_result($resultsSet);ClosingtheConnectionFinally,wehavetoclosetheconnectiontothedatabase.Althoughthishappensautomati-callyoncethescriptfinishesprocessing,closingitearlierwillfreetheresourcesthatitholdsforusebyotherprocesses.Toclosetheconnectionuse:mysql_close($myConnection);TestingtheScriptWenowhaveallthatweneedtoconnecttothedatabase,executethequeryandprocesstheresults.Hereareallofthepartsputtogether:WebPageIDBrowserIPNumber”; Chapter15•MySQLwithPHP,ApacheandPerl191do{$row=mysql_fetch_row($myResults);print“$row[0]$row[1]$row[2]”;}while($row);print“”;mysql_free_result($resultsSet);mysql_close($myConnection);?>Ihaveputalittleerrortrappinginthescriptsothatifyouwanttotryafewdifferentqueriesyoucandosoandgetsomefeedbackifthereareanyproblems.ThisscriptcanbeseenrunninginFigure15.7.WorkingwithPerlPerlisaninterpretedprogramminglanguagethatyoucanusetowriteCGIscripts.Likesomeoftheotherapplicationswehavejustdiscussed,PerlaccessesdatabasesthroughasetofdriverfunctionsthatareknownasDBI(DatabaseInterface).Perlscriptsresideinacgi-bindirectoryofyourwebserver,soinourexampleinstallationoursarestoredat:C:indigoperlapachecgi-binFigure15.6hasalreadyshownoneofthescriptsinthisdirectoryrunningwhenwepointedthebrowserat:http://localhost/cgi-bin/mysqlsamp2.plFigure15.7TheworkingPHPscript. 192DatabaseDesignManual:usingMySQLforWindowsYouwouldnormallyneedtoinstalltheDBIdriversforMySQLtogetPerlworkingwithMySQL,buttheIndigoPerlinstallationhasalreadydonethisforus.Aswehavedonepreviously,wewillnowcreateaPerlfileinthecgi-bindirectoryaboveandshowthevariousstepsneededtoconnecttoyourdatabaseinPerl.Perlfilesareidenti-fiedwiththe.plextension.Createablanktextfileinthecgidirectoryasfollows:C:indigoperlapachecgi-binmytest.plYoucanthendropthisfileintoNotepadandbegintobuildupthePerlscript.DefiningaPerlandHTMLDocumentAlthoughthe.plextensionandthepositionofthefileinthewebrootindicatestoApachethatthefileistobehandledbythePerlandCGIsystem,wehavetolettheCGIprocessorknowthatthescriptitisusingisPerl.Thisisindicatedbythefollowinglineatthestartofthefile:#!perlThehashsymbolinPerlcanbeusedtoinsertcommentsintoascript,ortotemporarilydisablecodefortesting.Inthisoccasion,however,thiscommandwillworkeventhoughitlooksasifitiscommentedout.Asalreadymentioned,PerlusesaDBItoconnecttoadatabase.WeneedtoletPerlknowthatwewillbeusingthisinterfacewiththecommand:useDBI;Sometextssaythatthiscommandcanbeomitted,andtheDBIwillbeinvokedautomat-icallyasitisusedlaterinthescript.However,inthisparticularimplementation,youdohavetoletActivePerlknowthatitwillbeusingtheDBIinadvance.Youwillalsonoticethatthiscommandisfollowedbyasemicolon.ThisisthestandardterminationforacommandinPerl.WecannowbegintogetPerltosetupoutputtoanHTMLpage,andbeginbuildingthispage.Wedothiswiththefollowing:print“Content-type:text/html ”;print“ ”;Youwillnoticethatwehaveusedthecommandprint.Thiscommandwilloutputwhatfollowsittoanoutputstream.AsthisisbeingcalledbyawebbrowserthestreamshouldbebacktoApache.Inotherapplicationswehaveusedinthisbookwehavejuststartedthewebpagewith“ ”;my$dbh=DBI->connect(‘DBI:mysqlPP:mysqlfast:host=localhost’,phpuser,abominable);my$sth=$dbh->prepare(‘SELECTtitle,datecreated,ipnumberfromwebpage,logWHERElog.webpageid=webpage.id’);$sth->execute()||quit();while(@myarray=$sth->fetchrow_array()){my$pagetitle=$myarray[0];my$ipnumber=$myarray[1];my$datecreated=$myarray[2];print“$datecreated$pagetitle$ipnumber
”;}print”

---

Ok”;print” ”;CopingwithErrorsIfyouhavetypedthescriptinandtestedit,youwillhopefullyseethescreenshowninFigure15.8.Ifyouhavemadeatypingerror,youmayhaveascreenasshowninFigure15.9.Ifyourcodeisworkingcorrectlybutyouwanttotryandbreakthecode,trychangingthepasswordintheconnectstringandtryingagain. 196DatabaseDesignManual:usingMySQLforWindowsFigure15.9Anerrorhasoccurred.AllthatyoucantellfromthescreeninFigure15.9isthatsomethinghasgonewrong.Perlitselfisnotaseasytousewhenerrorsoccurassomeoftheothersystemswehaveusedinthisbook,andsotogetsomefeedbackwehavetodosomeworkasaprogrammer.Firstwehavetoredirectanyerrorsthatmaybegeneratedbacktothestandardoutputstreamasfol-lows:BEGIN{$|=1;open(STDERR,“>&STDOUT”);}Nextwehavetooutputanerrorafteracommandthatmaycontainanerrorhasexe-cuted.TheDBIhasanoptiontodothisbyusingthefollowingcodeaftertheconnect()functionbeforethesemicolon:ordie“ ConnectError:“.$dbh->errstr;Thisforcestheprocessingofthescripttostopifanerrorisencountered.Youcansendamessagetotheerrorstreamatthistimeandincludetheactualerrormessageusingtheerrstrcommand.ReformattingtheOutputWhilewearemodifyingthisscriptwewilltidyuptheoutputofthequeryslightlybyput-tingtheresultsinatableasfollows:print“”;print“DateAccessedPageTitle:IPNumber”;while(@myarray=$sth->fetchrow_array()){my$pagetitle=$myarray[0]; Chapter15•MySQLwithPHP,ApacheandPerl197my$ipnumber=$myarray[1];my$datecreated=$myarray[2];print”$datecreated$pagetitle$ipnumber”;}print“”;Theabovealsoputsthecolumnnamesinaheaderrow.Noticethattherowdefinitionandcolumnnamesareoutsideoftheloop,andtherecurringrowsareontheinside.Hereisthewholemodifiedscriptwiththeerrorhandlingandreformattingincluded:#!perlBEGIN{$|=1;open(STDERR,“>&STDOUT”);}useDBI;print“Content-type:text/html ”;print“ ”;my$dbh=DBI->connect(‘DBI:mysqlPP:mysqlfast:host=localhost’,phpuser,abominable)ordie“ ConnectError:“.$dbh->errstr;my$sth=$dbh->prepare(‘SELECTtitle,datecreated,ipnumberfromwebpage,logWHERElog.webpageid=webpage.id’)ordie“ PrepareError:“.$dbh->errstr;$sth->execute()||quit();print“”;print“DateAccessedPageTitle:IPNumber”;while(@myarray=$sth->fetchrow_array()){my$pagetitle=$myarray[0];my$ipnumber=$myarray[1];my$datecreated=$myarray[2];print”$datecreated$pagetitle$ipnumber”;}print“”;print”

---

Ok”;print” ”;IfyourunthisyouwillseetheresultsshowninFigure15.10.However,ifyoumadeatypingerrorwhileenteringyourscript,youmayhavegotdiffer-entresults.Hopefully,theresultswillgiveyouanindicationofwhattheerrorwas. 198DatabaseDesignManual:usingMySQLforWindowsFigure15.10Moreerrorcheckingandneaterformatting.Figure15.11Errorcheckingatwork.Figure15.11showshowourerror-handlingcodehasgivenusameaningfulerrorwhenthepasswordwasincorrectontheconnectstring.Althoughthisdoesnotexactlypinpointtheproblem,thediagnosticsgivenarecloseenoughforyoutoestablishwhereaboutstheerroroccurredandsomeclueastocorrectingit. Makinglifeeasier16OtherToolsInthefinalchapterofthisbookIthoughtIwouldintroduceyoutotwomoretoolsthatcanmakeusingMySQLeasier.IdidnotmentiontheseearlierbecausebyusingthesetoolsfromthestartyouwouldnothavebeenabletolearnasmuchofthebasicSQLthatisrequiredtoreallyuseMySQLwell.Theseprogramsare:•MySQLControlCentre,thenewgraphicalconsoleforMySQLfromthedevelopers.•PHPMyAdmin,aweb-basedinterfacetoAdministerMySQLfunctions.MySQLControlCentreMySQLControlCentrecanbedownloadedfreefromtheGraphicalclientssectionoftheMySQL.comdownloadsarea:http://www.mysql.com/downloads/Thecontrolcentreisdistributedasazipfilewhichwhenuncompressedgivesyouasetupprogramtorun.Thesetupisreasonablystandardbutalsoinstallsvariouslanguagepacksthatyoumaywishtodeselecttosavespaceifnotneeded.Onceinstalledandrunning,youwillseethescreenshowninFigure16.1.TheleftsidehasastandardmenutreewhereyoucanselectanyoftheMySQLobjects.ThecurrentlyselectedobjectinthefigureistheLogtable.Whenyouselectatable,thepanelontheright-handsideofthescreenwillgiveyousomeinformationaboutthattable,similartoifyouhadexecutedaDESCRIBELOGcommand.Thisscreengivesyouaquickoverviewofthewholesystem.Thecontrolcentreshowsitspowerwhenyourightclickonanobject.Figure16.2showsthecontextmenuthatappearswhenyourightclickontheLogtable.HereyouaregivenamenuwhichallowsyoutousetheLogtabletogreatextent.SelectingtheEditTableoptionfromthecontextmenurevealsthescreenshowninFigure16.3.Hereyoucanchangethedesignofyourtablewiththegraphicalinterface.YouwillnotethatinthefiguretheInsertRowiconisjustabouttobeselected.ThisallowsyoutoaddanewcolumntotheexistingtablewithouthavingtoremembertheALTERTABLEsyntax.InthiscasethenewcolumnwouldbeaddedbeforetheBrowsercolumn.199 200DatabaseDesignManual:usingMySQLforWindowsFigure16.1ThemainMySQLControlCentrescreen.Figure16.2Atable'scontextmenu.Belowthelistofcolumnnamesarethepropertiesfortheobjectselected.InthefigurewehavejustselectedtheTableProperties.Thisoutlinestheattributesofthetableandevenallowsyoutochangetabletypesfromadropdownlist.Eachindividualcolumnhasproper-tiesthatyoucanalsochangeifrequired.UndertheseoptionsisastatuswindowthatgivesyouanymessagesthattheMySQLserversendswhileyouarenavigatingyourdatabase.Figure16.4showsthescreenyouaretakentoifyouselectoneoftheOpenTableoptionsfromthecontextmenu.Thistimeyouhaveascreenthatshowstheresultsofthequery.Inthefigure,wehavejustpressedtheSQLbuttonwhichhasopenedawindowthatcontainstheselectquerythattheControlCentreusedtogeneratetheresultsset.AWHEREclausewasthenaddedtotheselectstatementandtheExecuteQuerybutton(theexclamation Chapter16•MakingLifeEasier201Figure16.3Alteringthedesignofyourtable.Figure16.4Lotsofinformationaboutyourrunningquery. 202DatabaseDesignManual:usingMySQLforWindowsmark)clicked.Thequerythenrunsandtheresultspaneisupdated.Youcanseeinthefig-urethatthestatuswindowcontainstherowsreturnedforthefirstandsecondqueriesthatwererun,andothertabsonthiswindowallowyoutoreturntopreviousqueriesandevenEXPLAINthequeryasitruns.WeusedtheMySQLGUIinthisbookbecauseitstableoutputwasclearanduncluttered.AlthoughtheControlCentrescreenpresentsuswithalotmoreinformation,italsohasonehiddenfunctionthattheGUIdidnothave.Ifyouclickononeofthecellsintheresultspane,andthecellcontainsasinglecolumnvalue,youcaneditthecontentsofthecell.ThisallowsyoutochangeyourdatawithoutusingINSERTorUPDATEqueries,althoughthisiswhattheMySQLserverdoesinthebackground.Itisquitecommontouseaselectstate-menttomatchaprimarykeyintheControlCentreinterface,andtheneditsomeoftheothercolumnsbyclickingintheresultspane.Ifthecelltobechangedisalargecolumntype,likeatexttype,youwillgetthevalueappearinginanotherwindowtoedit.Youwillalsoseewhyintroducingyoutothisearliermayhavestoppedyoufromlearn-ingSQL,asthistoolsimplifiesdatabaseaccessconsiderably.However,youstillneedtouseyourknowledgeofSQLtogetthemostfromthissortoftool.YouwillalsorealizethatyoustillneedSQLskillstoembeditcorrectlyinsidescriptinglanguages.TheMySQLControlCentreisamuchsuperiorproducttotheGUI,whichexplainswhydevelopmentofthelatterhasceased.TheControlCentreisalsosimilartotheGraphicaltoolsthatareprovidedwithOracle,MicrosoftSQLserverandothers,andreallyprovesthatMySQLhasnowcomeofageandcancompetewiththeseproducts.Figure16.5PHPMyAdminmainpage. Chapter16•MakingLifeEasier203PHPMyAdminPHPMyAdminisaseriesofscriptsthatyoucanuseinconjunctionwithApacheandPHPtoadministeryourwebserver.AswehavealreadysettheseprogramsupwithIndigoPerl,thePHPMyAdminsetupisverysimple.Thiscancurrentlybeobtainedasadownloadfrom:http://www.phpmyadmin.netYoumayalsofindthecollectionbydoingaGooglesearch.Oncedownloadedandunzipped,youneedtocopythefilestothefollowingdirectoryinyourwebrootdirectory:c:indigoperlapachehtdocsphpmyadminYoucanthenimmediatelyaccessthistoolbypointingyourwebbrowseratthefollowingaddress:http://localhost/phpmyadmin/index.phpFigure16.5showsthefrontpageoftheadminwebsite.Becausethistooliswebbased,itmeansthatifyourMySQLinstallationiswebenabled,youcanaccessitfromanymachinewithawebbrowserandinternetconnection.YoudonotFigure16.6Listingthetablesinadatabase. 204DatabaseDesignManual:usingMySQLforWindowsFigure16.7Queryresultsviatheweb.needaspecificclientrunningtoenableyoutoadministeryourdatabases.Thisfreedomobviouslycomeswithsomeproblemssuchassecurityissues.TheREADMEfileincludedwiththepackageexplainshowtopasswordprotectPHPMyAdmintobegintosecureyourinstallation.YoucanseefromthescreenshowninFigure16.5thatyoucanselectadatabasetoworkwithorcreateanewone.Youcanalsoseethatourmysqlfastdatabasethatwecreatedinthisbookisinthelistofdatabases.SelectingthisdatabasegivesthecomplexscreenshowninFigure16.6.Thisscreenlistsallofthetablesthatareinmymysqlfastdatabase.YoucanaccessallpartsofyourMySQLdatafromthisinterface.OneusefulpartistheSQLtab.ClickingonthiswillallowyoutoenteraSQLquerydirectlyintothewebinterface.Figure16.7showstheresultsofthefollowingquery:SELECT*FROMLog;YouwillseethatFigure16.7showstheresultsofthequery,aswellasgivingyoutheoptionstogoandeditsomeofthedatathathasbeenreturned.PHPMyAdminisagreattoolthatIwasverypleasedtodiscover.Aslongasyouconsiderthepossiblesecurityimplicationsofusingit,youwillfinditanotherinvaluableaidinyouruseofMySQL. Index3302error5B404error5basicjoins73BETWEEN63ABIGINTdatatype20accessbarrier2BinaryLargeObjects27accessrights18BITdatatype22ADD91,93BLOBdatatype27ADDDATE()127BOOLdatatype21addingauser137boolean21AdministrativeTools166aggregatefunctionsCavg()115cascadedelete37count107CFOUTPUT180max()111CFQUERY180min()112CGI192sum()113CHANGE94Aliases61CHARdatatype25ALLPRIVILEGES141CHECKTABLE159ALTERTABLE90checkingpasswords147ALTERTABLEADD91clearacolumn'scells89ALTERTABLECHANGE94clientserver6ALTERTABLEDROP95Close175ALTERTABLEMODIFY95CMFL176ALTERTABLERENAME93ColdFusion170,176ANALYZETABLE161ColdFusionMyODBCexample178AND63columns4ANSISQL927,97CONCAT65Apache183condition58Apachelogs3conditions59AS42,62,113connectingtotheservice13ASC69Connector/ODBC164ASP170,171contentmanaged183ASPMyODBCexample171contextmenu12attributes4controlaccess2,6autoupdatedatedatatype27controlpanel166auto-generated46controllingaccess138,145auto-generating47cookietable35,36AUTONUMBER111cookies63AUTO_INCREMENT39,47count()107avg()115CREATEDATABASE38CreatefromSelectStatement42CREATEINDEX157205 206DatabaseDesignManual:usingMySQLforWindowsCREATEORREPLACE42,100DATE_FORMAT()131CREATETABLE39,103DATE_SUB()127CREATEUNIQUEINDEX157DAYNAME()120creatingauser137DAYOFMONTH()122credentials11,12,144,149,169,188DAYOFWEEK()121crossjoin74DAYOFYEAR()122CURRENT_DATE118DBI191CURRENT_TIME118,131DBMS2,6DECIMALdatatype22DDECRYPT147dataDEFAULT39import49DELETE140importing52DELETEFROM103,105restricting58DESC69datasource164,176describe14,39,91,135,199databaseefficiency33die196databaselist38differentiatingcolumns62DatabaseManagementSystem2DISTINCT57datatypeDocsdirectory11TIME29DOUBLE(PRECISION)datatype24YEAR29DROP95datatypesDROPTABLE105BIGINT20DUPLICATEKEYUPDATE100BIT22BLOB27EBOOLEAN21encryption147CHAR25enforcereferentialintegrity37DATE28ENUM31DATETIME27equals58,60DECIMAL22equi-join75,81definition19errstr196DOUBLE(PRECISION)24executequery16,45enum31execute()194fixedlength26EXPLAIN152,156FLOAT23EXTENDED160INT20extra154LONGBLOB27LONGTEXT26FMEDIUMBLOB27fetchrow_array()194MEDIUMINT20FIELDSTERMINATED53MEDIUMTEXT26FLOATdatatype23REAL24floatingpointnumbers23SMALLINT20FLUSHPRIVILEGES144TEXT26foreignkey35,40TINYBLOB27formdataTINYINT20dropdown31TINYTEXT26lists30VARCHAR25select30variablelength26formattingintegers20DATEdatatype28formattingoutput67DATEdatatypes27fragmentedtables26,158datesFROM55autoupdating27FROM_DAYS()130DATETIMEdatatype27fuzzymatching60DATE_ADD()125 Index207GLGRANT135,141,144LEFTJOIN81,111GRANTexamples146,169LIKE43,60GRANTSELECT140,169listgranttables142dropdown31GROUPBY108LOADDATA52LOCAL53Hlocalhost18,140,169,176,185HAVING115logtable40helpfiles11logindetails11HOUR()124LONGBLOBdatatype27HTMLformdataLONGTEXTdatatype26dropdown31lists30Mmanualfileslocation11Imanual_toc.html11identifyingredundantdata36max()111IFNOTEXISTS41MEDIUMBLOBdatatype27IGNORE...LINES53MEDIUMINTdatatype20importdata49MEDIUMTEXTdatatype26IN70mergeresults66index96metadata7indexing156meta-data4INFILE52MicrosoftODBC176innerjoin75,79min()112InnoDB37MINUTE()125INSERT45,202MODIFY95fromquery48MONTH()119fromscript49MONTHNAME()120INSERT<#208>AllColumns47movedtemporarily5INSERT<#208>MultipleRows46MoveNext174INTdatatype20Mozilla33integerdatatyperanges20multipleaggregates113integers20my193my.ini13JMyISAM37joinmyisamchk161threetables151MyODBC164joiningmultipletables73MyODBCexamples170joinsMySQLcross74configurationfile13equi-75,81connectingtotheservice13examining151defaultdirectory9inner75,79directorystructure10left81Docsdirectory11ON80downloading9outer81installing9restrictingequi-joins79interpreter13right83onlinemanual11UNION85service11start-upfile12Ktabletypes37MySQLControlCentre199key154mysqlbin11key_len154mysqlfast204 208DatabaseDesignManual:usingMySQLforWindowsmysqlfastdatabase3query7MySQLGUI9,202QUICK160firsttimelogon15quicktour17RmysqlPP193REALdatatype24mysql_close190record5mysql_connect188recordset190mysql_fetch_row189RECORDSTERMINATED53mysql_query188RecordSet174mysql_select_db188recreateinformation35redundantdata33Nref154NOT63REFERENCES40NOTBETWEEN63referentialintegrity36,42,83NOTIN71relationaltheory4,5NOTNULL39relations5NOW()117relationships5NULL37RENAME93REPAIRTABLE160OREPLACE87,98ODBC163,176response.write174ON80,144restrictingaccess135,138ONDUPLICATEKEYUPDATE100restrictingusers145onlineMySQLmanual11REVOKE138,141,144OpenDatabaseConnectivity163RIGHTJOIN83operators59root12OPTIMIZETABLE158row5or188rows154ORDERBY68outerjoin81Sscriptprocessor6PSECOND()125password15,138,144SELECTpasswordhandling146*55PASSWORD()147tobuildnewtable49patternmatching60SELECT*55percentsign61SELECTAS61Perl184,191SELECTcolumnnames56PHP6,98,183,186SELECTDISTINCT57,109PHP-Nuke183SELECTWHERE58PHPMyAdmin203self-defining3possible_keys153,156,158semicolon8precision22setprepare()193definition30primarykey34,40,47,93,96storing30duplicates47SETdatatype30unique49Settingupadatasource166privilege144Showme12protectingdata135SMALLINT92SMALLINTdatatype20Qsource51QUARTER()124SQL7queriesSQLstandards7examining152SQL917start-upfile12 Index209storingcurrency22tuple5storingdecimalnumbers22TYPE42storinginformation19storingintegers20Ustoringlargenumbers23underscore61Storinglargeobjects27UNION85storingtext26uniqueprimarykey49stringtruncation25UNSIGNED21strings24,25UPDATE87,97,99,202StructuredQueryLanguage7USE38SUBDATE()128user137subquery71usercredentials11,146sum()113usertable14,135,149SystemDSN166USE_FRM160systemtray12VTVALUES45tablealreadyexists41VARCHAR94tablemaintenance26VARCHARdatatype25tablesVBSCRIPT172cookies50visitorbooktable92log53visitorbook65Wwebpage45webdatabases2webpage248webpagetable39tables_privtable141webserverstatuscodes5telltale12websitelog33TestConnection169WEEK()123TEXT94WEEKDAY()121TEXTdatatype26WHERE58,79,103.SeeSeethreetablejoin151wildcards60TIMEdatatype29winmysqladmin.exe11TIMESTAMPdatatype27winmysqlgui.exe15TIME_FORMAT()130TINYBLOBdatatype27YTINYINTdatatype20YEARdatatype29TINYTEXTdatatype26YEAR()119TO_DAYS()128,133YEARWEEK()123