MySQL Cookbook 2nd Edition

Describes the features and functions of MySQL 5.0 along with short code pieces and programming examples.

DuBois provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe"--short, focused pieces of code that readers can insert directly into their applications.

DuBois provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe"--short, focused pieces of code that readers can insert directly into their applications.

PrefaceWho This Book Is ForWhats in This BookMySQL APIs Used in This BookConventions Used in This BookThe MySQL Cookbook Companion Web SiteVersion and Platform NotesUpgrade Note for First Edition ReadersAdditional ResourcesUsing Code ExamplesSafari® EnabledHow to Contact UsAcknowledgmentsChapter 1: Using the mysql Client ProgramIntroductionSetting Up a MySQL User AccountCreating a Database and a Sample TableStarting and Stopping mysqlSpecifying Connection Parameters Using Option FilesProtecting Option Files from Other UsersMixing Command-Line and Option File ParametersWhat to Do if mysql Cannot Be FoundIssuing SQL StatementsCanceling a Partially Entered StatementRepeating and Editing SQL StatementsUsing Auto-Completion for Database and Table NamesTelling mysql to Read Statements from a FileTelling mysql to Read Statements from Other ProgramsEntering an SQL One-LinerUsing Copy and Paste as a mysql Input SourcePreventing Query Output from Scrolling off the ScreenSending Query Output to a File or to a ProgramSelecting Tabular or Tab-Delimited Query Output FormatSpecifying Arbitrary Output Column DelimitersProducing HTML or XML OutputSuppressing Column Headings in Query OutputMaking Long Output Lines More ReadableControlling mysqls Verbosity LevelLogging Interactive mysql SessionsCreating mysql Scripts from Previously Executed StatementsUsing User-Defined Variables in SQL StatementsNumbering Query Output LinesUsing mysql as a CalculatorUsing mysql in Shell ScriptsProblemSolutionDiscussionChapter 2: Writing MySQL-Based ProgramsIntroductionConnecting, Selecting a Database, and DisconnectingProblemSolutionDiscussionChecking for ErrorsProblemSolutionDiscussionWriting Library FilesProblemSolutionDiscussionIssuing Statements and Retrieving ResultsProblemSolutionDiscussionHandling Special Characters and NULL Values in StatementsProblemSolutionDiscussionHandling Special Characters in IdentifiersIdentifying NULL Values in Result SetsProblemSolutionDiscussionTechniques for Obtaining Connection ParametersProblemSolutionDiscussionConclusion and Words of AdviceChapter 3: Selecting Data from TablesIntroductionSpecifying Which Columns to SelectSpecifying Which Rows to SelectGiving Better Names to Query Result ColumnsUsing Column Aliases to Make Programs Easier to WriteCombining Columns to Construct Composite ValuesWHERE Clauses and Column AliasesDebugging Comparison ExpressionsRemoving Duplicate RowsWorking with NULL ValuesWriting Comparisons Involving NULL in ProgramsSorting a Result SetUsing Views to Simplify Table AccessSelecting Data from More Than One TableSelecting Rows from the Beginning or End of a Result SetSelecting Rows from the Middle of a Result SetChoosing Appropriate LIMIT ValuesWhat to Do When LIMIT Requires the Wrong Sort OrderCalculating LIMIT Values from ExpressionsChapter 4: Table ManagementIntroductionCloning a TableSaving a Query Result in a TableCreating Temporary TablesChecking or Changing a Tables Storage EngineGenerating Unique Table NamesChapter 5: Working with StringsIntroductionString PropertiesChoosing a String Data TypeSetting the Client Connection Character Set ProperlyWriting String LiteralsChecking a Strings Character Set or CollationChanging a Strings Character Set or CollationConverting the Lettercase of a StringConverting the Lettercase of a Stubborn StringControlling Case Sensitivity in String ComparisonsPattern Matching with SQL PatternsPattern Matching with Regular ExpressionsControlling Case Sensitivity in Pattern MatchingBreaking Apart or Combining StringsSearching for SubstringsUsing FULLTEXT SearchesUsing a FULLTEXT Search with Short WordsRequiring or Excluding FULLTEXT Search WordsPerforming Phrase Searches with a FULLTEXT IndexChapter 6: Working with Dates and TimesIntroductionChoosing a Temporal Data TypeChanging MySQLs Date FormatSetting the Client Time ZoneDetermining the Current Date or TimeUsing TIMESTAMP to Track Row Modification TimesExtracting Parts of Dates or TimesProblemSolutionDiscussionSynthesizing Dates or Times from Component ValuesConverting Between Temporal Data Types and Basic UnitsProblemSolutionDiscussionCalculating the Interval Between Two Dates or TimesProblemSolutionDiscussionAdding Date or Time ValuesProblemSolutionDiscussionCalculating AgesShifting a Date-and-Time Value to a Different Time ZoneFinding the First Day, Last Day, or Length of a MonthCalculating Dates by Substring ReplacementFinding the Day of the Week for a DateFinding Dates for Any Weekday of a Given WeekPerforming Leap Year CalculationsProblemSolutionDiscussionCanonizing Not-Quite-ISO Date StringsTreating Dates or Times as NumbersForcing MySQL to Treat Strings as Temporal ValuesSelecting Rows Based on Their Temporal CharacteristicsProblemSolutionDiscussionChapter 7: Sorting Query ResultsIntroductionUsing ORDER BY to Sort Query ResultsUsing Expressions for SortingDisplaying One Set of Values While Sorting by AnotherControlling Case Sensitivity of String SortsDate-Based SortingSorting byyyyyy Calendar DaySorting by Day of WeekSorting by Time of DaySorting Using Substrings of Column ValuesSorting by Fixed-Length SubstringsSorting by Variable-Length SubstringsSorting Hostnames in Domain OrderSorting Dotted-Quad IP Values in Numeric OrderFloating Values to the Head or Tail of the Sort OrderSorting in User-Defined OrdersSorting ENUM ValuesChapter 8: Generating SummariesIntroductionSummarizing with COUNT?(?????)Summarizing with MIN?(?????) and MAX?(?????)Summarizing with SUM?(?????) and AVG?(?????)Using DISTINCT to Eliminate DuplicatesFinding Values Associated with Minimum and Maximum ValuesControlling String Case Sensitivity for MIN?(?????) and MAX?(?????)Dividing a Summary into SubgroupsSummaries and NULL ValuesSelecting Only Groups with Certain CharacteristicsUsing Counts to Determine Whether Values Are UniqueGrouping by Expression ResultsCategorizing Noncategorical DataControlling Summary Display OrderFinding Smallest or Largest Summary ValuesDate-Based SummariesWorking with Per-Group and Overall Summary Values SimultaneouslyGenerating a Report That Includes a Summary and a ListChapter 9: Obtaining and Using MetadataIntroductionObtaining the Number of Rows Affected by a StatementProblemSolutionDiscussionObtaining Result Set MetadataProblemSolutionDiscussionDetermining Whether a Statement Produced a Result SetUsing Metadata to Format Query OutputListing or Checking Existence of Databases or TablesAccessing Table Column DefinitionsProblemSolutionDiscussionGetting ENUM and SET Column InformationUsing Table Structure Information in ApplicationsProblemSolutionDiscussionGetting Server MetadataWriting Applications That Adapt to the MySQL Server VersionDetermining the Default DatabaseMonitoring the MySQL ServerDetermining Which Storage Engines the Server SupportsChapter 10: Importing and Exporting DataIntroductionImporting Data with LOAD DATA and mysqlimportSpecifying the Datafile LocationSpecifying the Structure of the DatafileDealing with Quotes and Special CharactersImporting CSV FilesReading Files from Different Operating SystemsHandling Duplicate Key ValuesObtaining Diagnostics About Bad Input DataSkipping Datafile LinesSpecifying Input Column OrderPreprocessing Input Values Before Inserting ThemIgnoring Datafile ColumnsExporting Query Results from MySQLProblemSolutionDiscussionSee AlsoExporting Tables as Text FilesExporting Table Contents or Definitions in SQL FormatCopying Tables or Databases to Another ServerWriting Your Own Export ProgramsConverting Datafiles from One Format to AnotherExtracting and Rearranging Datafile ColumnsUsing the SQL Mode to Control Bad Input Data HandlingValidating and Transforming DataProblemSolutionDiscussionUsing Pattern Matching to Validate DataUsing Patterns to Match Broad Content TypesUsing Patterns to Match Numeric ValuesUsing Patterns to Match Dates or TimesUsing Patterns to Match Email Addresses or URLsUsing Table Metadata to Validate DataUsing a Lookup Table to Validate DataProblemSolutionDiscussionConverting Two-Digit Year Values to Four-Digit FormPerforming Validity Checking on Date or Time SubpartsWriting Date-Processing UtilitiesUsing Dates with Missing ComponentsImporting Non-ISO Date ValuesExporting Dates Using Non-ISO FormatsImporting and Exporting NULL ValuesGuessing Table Structure from a DatafileExchanging Data Between MySQL and Microsoft AccessExchanging Data Between MySQL and Microsoft ExcelExporting Query Results as XMLImporting XML into MySQLEpilogueChapter 11: Generating and Using SequencesIntroductionCreating a Sequence Column and Generating Sequence ValuesChoosing the Data Type for a Sequence ColumnThe Effect of Row Deletions on Sequence GenerationRetrieving Sequence ValuesProblemSolutionDiscussionRenumbering an Existing SequenceExtending the Range of a Sequence ColumnReusing Values at the Top of a SequenceEnsuring That Rows Are Renumbered in a Particular OrderStarting a Sequence at a Particular ValueSequencing an Unsequenced TableUsing an AUTO_INCREMENT Column to Create Multiple SequencesManaging Multiple Simultaneous AUTO_INCREMENT ValuesUsing AUTO_INCREMENT Values to Relate TablesUsing Sequence Generators as CountersGenerating Repeating SequencesNumbering Query Output Rows SequentiallyChapter 12: Using Multiple TablesIntroductionFinding Rows in One Table That Match Rows in AnotherFinding Rows with No Match in Another TableComparing a Table to ItselfProducing Master-Detail Lists and SummariesEnumerating a Many-to-Many RelationshipFinding Rows Containing Per-Group Minimum or Maximum ValuesComputing Team StandingsUsing a Join to Fill or Identify Holes in a ListCalculating Successive-Row DifferencesFinding Cumulative Sums and Running AveragesUsing a Join to Control Query Output OrderCombining Several Result Sets in a Single QueryIdentifying and Removing Mismatched or Unattached RowsPerforming a Join Between Tables in Different DatabasesUsing Different MySQL Servers SimultaneouslyReferring to Join Output Column Names in ProgramsChapter 13: Statistical TechniquesIntroductionCalculating Descriptive StatisticsPer-Group Descriptive StatisticsGenerating Frequency DistributionsCounting Missing ValuesCalculating Linear Regressions or Correlation CoefficientsGenerating Random NumbersRandomizing a Set of RowsSelecting Random Items from a Set of RowsAssigning RanksChapter 14: Handling DuplicatesIntroductionPreventing Duplicates from Occurring in a TableDealing with Duplicates When Loading Rows into a TableCounting and Identifying DuplicatesEliminating Duplicates from a TableProblemSolutionDiscussionEliminating Duplicates from a Self-Join ResultChapter 15: Performing TransactionsIntroductionChoosing a Transactional Storage EnginePerforming Transactions Using SQLPerforming Transactions from Within ProgramsUsing Transactions in Perl ProgramsUsing Transactions in Ruby ProgramsUsing Transactions in PHP ProgramsUsing Transactions in Python ProgramsUsing Transactions in Java ProgramsUsing Alternatives to TransactionsProblemSolutionDiscussionChapter 16: Using Stored Routines, Triggers, and EventsIntroductionCreating Compound-Statement ObjectsUsing a Stored Function to Encapsulate a CalculationUsing a Stored Procedure to Return Multiple ValuesUsing a Trigger to Define Dynamic Default Column ValuesSimulating TIMESTAMP Properties for Other Date and Time TypesUsing a Trigger to Log Changes to a TableUsing Events to Schedule Database ActionsChapter 17: Introduction to MySQL on the WebIntroductionBasic Principles of Web Page GenerationUsing Apache to Run Web ScriptsProblemSolutionDiscussionUsing Tomcat to Run Web ScriptsProblemSolutionDiscussionEncoding Special Characters in Web OutputProblemSolutionDiscussionChapter 18: Incorporating Query Results into Web PagesIntroductionDisplaying Query Results as Paragraph TextDisplaying Query Results as ListsProblemSolutionDiscussionSee AlsoDisplaying Query Results as TablesDisplaying Query Results as HyperlinksCreating a Navigation Index from Database ContentProblemSolutionDiscussionSee AlsoStoring Images or Other Binary DataProblemSolutionDiscussionSee AlsoRetrieving Images or Other Binary DataServing Banner AdsServing Query Results for DownloadUsing a Template System to Generate Web PagesProblemSolutionDiscussionChapter 19: Processing Web Input with MySQLIntroductionWriting Scripts That Generate Web FormsProblemSolutionDiscussionSee AlsoCreating Single-Pick Form Elements from Database ContentCreating Multiple-Pick Form Elements from Database ContentLoading a Database Record into a FormCollecting Web InputProblemSolutionDiscussionValidating Web InputStoring Web Input in a DatabaseProcessing File UploadsProblemSolutionDiscussionPerforming Searches and Presenting the ResultsGenerating Previous-Page and Next-Page LinksProblemSolutionDiscussionGenerating Click to Sort Table HeadingsWeb Page Access CountingWeb Page Access LoggingUsing MySQL for Apache LoggingProblemSolutionDiscussionChapter 20: Using MySQL-Based Web Session ManagementIntroductionUsing MySQL-Based Sessions in Perl ApplicationsProblemSolutionDiscussionUsing MySQL-Based Storage in Ruby ApplicationsUsing MySQL-Based Storage with the PHP Session ManagerProblemSolutionDiscussionUsing MySQL for Session-Backing Store with TomcatProblemSolutionDiscussionObtaining MySQL SoftwareObtaining Sample Source Code and DataObtaining MySQL and Related SoftwareExecuting Programs from the Command LineSetting Environment VariablesExecuting ProgramsJSP and Tomcat PrimerServlet and JavaServer Pages OverviewSetting Up a Tomcat ServerTomcats Directory StructureRestarting Applications Without Restarting TomcatWeb Application StructureElements of JSP PagesReferencesColophon