MySQL Cookbook 2nd Edition

Description

Along with MySQL's popularity has come a flood of questions about solving specific problems, and that's where this Cookbook is essential. Designed as a handy resource when you need quick solutions or techniques, the book offers dozens of short, focused pieces of code and hundreds of worked-out examples for programmers of all levels who don't have the time (or expertise) to solve MySQL problems from scratch.

 The new edition covers MySQL 5.0 and its powerful new features, as well as the older but still widespread MySQL 4.1. One major emphasis of this book is how to use SQL to formulate queries for particular kinds of questions, using the mysql client program included in MySQL distributions. The other major emphasis is how to write programs that interact with the MySQL server through an API. You'll find plenty of examples using several language APIs in multiple scenarios and situations, including the use of Ruby to retrieve and format data. There are also many new examples for using Perl, PHP, Python, and Java as well.

 Other recipes in the book teach you to:

* Access data from multiple tables at the same time
* Use SQL to select, sort, and summarize rows
* Find matches or mismatches between rows in two tables
* Determine intervals between dates or times, including age calculations
* Store images into MySQL and retrieve them for display in web pages
* Get LOAD DATA to read your data files properly or find which values in the file are invalid
* Use strict mode to prevent entry of bad data into your database
* Copy a table or a database to another server
* Generate sequence numbers to use as unique row identifiers
* Create database events that execute according to a schedule
* And a lot more

 MySQL Cookbook doesn't attempt to develop full-fledged, complex applications. Instead, it's intended to assist you in developing applications yourself by helping you get past problems that have you stumped.

CONTENTS:

Preface
* Who This Book Is For
* What’s in This Book
* MySQL APIs Used in This Book
* Conventions Used in This Book
* The MySQL Cookbook Companion Web Site
* Version and Platform Notes
* Upgrade Note for First Edition Readers
* Additional Resources
* Using Code Examples
* Safari(R) Enabled
* How to Contact Us
* AcknowledgmentsChapter 1: Using the mysql Client Program
* Introduction
* Setting Up a MySQL User Account
* Creating a Database and a Sample Table
* Starting and Stopping mysql
* Specifying Connection Parameters Using Option Files
* Protecting Option Files from Other Users
* Mixing Command-Line and Option File Parameters
* What to Do if mysql Cannot Be Found
* Issuing SQL Statements
* Canceling a Partially Entered Statement
* Repeating and Editing SQL Statements
* Using Auto-Completion for Database and Table Names
* Telling mysql to Read Statements from a File
* Telling mysql to Read Statements from Other Programs
* Entering an SQL One-Liner
* Using Copy and Paste as a mysql Input Source
* Preventing Query Output from Scrolling off the Screen
* Sending Query Output to a File or to a Program
* Selecting Tabular or Tab-Delimited Query Output Format
* Specifying Arbitrary Output Column Delimiters
* Producing HTML or XML Output
* Suppressing Column Headings in Query Output
* Making Long Output Lines More Readable
* Controlling mysql’s Verbosity Level
* Logging Interactive mysql Sessions
* Creating mysql Scripts from Previously Executed Statements
* Using User-Defined Variables in SQL Statements
* Numbering Query Output Lines
* Using mysql as a Calculator
* Using mysql in Shell Scripts
* Problem
* Solution
* DiscussionChapter 2: Writing MySQL-Based Programs
* Introduction
* Connecting, Selecting a Database, and Disconnecting
* Problem
* Solution
* DiscussionChecking for Errors
* Problem
* Solution
* DiscussionWriting Library Files
* Problem
* Solution
* DiscussionIssuing Statements and Retrieving Results
* Problem
* Solution
* DiscussionHandling Special Characters and NULL Values in Statements
* Problem
* Solution
* DiscussionHandling Special Characters in IdentifiersIdentifying NULL Values in Result Sets
* Problem
* Solution
* DiscussionTechniques for Obtaining Connection Parameters
* Problem
* Solution
* DiscussionConclusion and Words of AdviceChapter 3: Selecting Data from Tables
* Introduction
* Specifying Which Columns to Select
* Specifying Which Rows to Select
* Giving Better Names to Query Result Columns
* Using Column Aliases to Make Programs Easier to Write
* Combining Columns to Construct Composite Values
* WHERE Clauses and Column Aliases
* Debugging Comparison Expressions
* Removing Duplicate Rows
* Working with NULL Values
* Writing Comparisons Involving NULL in Programs
* Sorting a Result Set
* Using Views to Simplify Table Access
* Selecting Data from More Than One Table
* Selecting Rows from the Beginning or End of a Result Set
* Selecting Rows from the Middle of a Result Set
* Choosing Appropriate LIMIT Values
* What to Do When LIMIT Requires the Wrong Sort Order
* Calculating LIMIT Values from ExpressionsChapter 4: Table Management
* Introduction
* Cloning a Table
* Saving a Query Result in a Table
* Creating Temporary Tables
* Checking or Changing a Table’s Storage Engine
* Generating Unique Table NamesChapter 5: Working with Strings
* Introduction
* String Properties
* Choosing a String Data Type
* Setting the Client Connection Character Set Properly
* Writing String Literals
* Checking a String’s Character Set or Collation
* Changing a String’s Character Set or Collation
* Converting the Lettercase of a String
* Converting the Lettercase of a Stubborn String
* Controlling Case Sensitivity in String Comparisons
* Pattern Matching with SQL Patterns
* Pattern Matching with Regular Expressions
* Controlling Case Sensitivity in Pattern Matching
* Breaking Apart or Combining Strings
* Searching for Substrings
* Using FULLTEXT Searches
* Using a FULLTEXT Search with Short Words
* Requiring or Excluding FULLTEXT Search Words
* Performing Phrase Searches with a FULLTEXT IndexChapter 6: Working with Dates and Times
* Introduction
* Choosing a Temporal Data Type
* Changing MySQL’s Date Format
* Setting the Client Time Zone
* Determining the Current Date or Time
* Using TIMESTAMP to Track Row Modification Times
* Extracting Parts of Dates or Times
* Problem
* Solution
* DiscussionSynthesizing Dates or Times from Component ValuesConverting Between Temporal Data Types and Basic Units
* Problem
* Solution
* DiscussionCalculating the Interval Between Two Dates or Times
* Problem
* Solution
* DiscussionAdding Date or Time Values
* Problem
* Solution
* DiscussionCalculating 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 Calculations
* Problem
* Solution
* DiscussionCanonizing Not-Quite-ISO Date StringsTreating Dates or Times as NumbersForcing MySQL to Treat Strings as Temporal ValuesSelecting Rows Based on Their Temporal Characteristics
* Problem
* Solution
* DiscussionChapter 7: Sorting Query Results
* Introduction
* Using ORDER BY to Sort Query Results
* Using Expressions for Sorting
* Displaying One Set of Values While Sorting by Another
* Controlling Case Sensitivity of String Sorts
* Date-Based Sorting
* Sorting by Calendar Day
* Sorting by Day of Week
* Sorting by Time of Day
* Sorting Using Substrings of Column Values
* Sorting by Fixed-Length Substrings
* Sorting by Variable-Length Substrings
* Sorting Hostnames in Domain Order
* Sorting Dotted-Quad IP Values in Numeric Order
* Floating Values to the Head or Tail of the Sort Order
* Sorting in User-Defined Orders
* Sorting ENUM ValuesChapter 8: Generating Summaries
* Introduction
* Summarizing with COUNT?(?????)
* Summarizing with MIN?(?????) and MAX?(?????)
* Summarizing with SUM?(?????) and AVG?(?????)
* Using DISTINCT to Eliminate Duplicates
* Finding Values Associated with Minimum and Maximum Values
* Controlling String Case Sensitivity for MIN?(?????) and MAX?(?????)
* Dividing a Summary into Subgroups
* Summaries and NULL Values
* Selecting Only Groups with Certain Characteristics
* Using Counts to Determine Whether Values Are Unique
* Grouping by Expression Results
* Categorizing Noncategorical Data
* Controlling Summary Display Order
* Finding Smallest or Largest Summary Values
* Date-Based Summaries
* Working with Per-Group and Overall Summary Values Simultaneously
* Generating a Report That Includes a Summary and a ListChapter 9: Obtaining and Using Metadata
* Introduction
* Obtaining the Number of Rows Affected by a Statement
* Problem
* Solution
* DiscussionObtaining Result Set Metadata
* Problem
* Solution
* DiscussionDetermining Whether a Statement Produced a Result SetUsing Metadata to Format Query OutputListing or Checking Existence of Databases or TablesAccessing Table Column Definitions
* Problem
* Solution
* DiscussionGetting ENUM and SET Column InformationUsing Table Structure Information in Applications
* Problem
* Solution
* DiscussionGetting 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 Data
* Introduction
* Importing Data with LOAD DATA and mysqlimport
* Specifying the Datafile Location
* Specifying the Structure of the Datafile
* Dealing with Quotes and Special Characters
* Importing CSV Files
* Reading Files from Different Operating Systems
* Handling Duplicate Key Values
* Obtaining Diagnostics About Bad Input Data
* Skipping Datafile Lines
* Specifying Input Column Order
* Preprocessing Input Values Before Inserting Them
* Ignoring Datafile Columns
* Exporting Query Results from MySQL
* Problem
* Solution
* Discussion
* See 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 Data
* Problem
* Solution
* DiscussionUsing 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 Data
* Problem
* Solution
* DiscussionConverting 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 Sequences
* Introduction
* Creating a Sequence Column and Generating Sequence Values
* Choosing the Data Type for a Sequence Column
* The Effect of Row Deletions on Sequence Generation
* Retrieving Sequence Values
* Problem
* Solution
* DiscussionRenumbering 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 Tables
* Introduction
* Finding Rows in One Table That Match Rows in Another
* Finding Rows with No Match in Another Table
* Comparing a Table to Itself
* Producing Master-Detail Lists and Summaries
* Enumerating a Many-to-Many Relationship
* Finding Rows Containing Per-Group Minimum or Maximum Values
* Computing Team Standings
* Using a Join to Fill or Identify Holes in a List
* Calculating Successive-Row Differences
* Finding Cumulative Sums and Running Averages
* Using a Join to Control Query Output Order
* Combining Several Result Sets in a Single Query
* Identifying and Removing Mismatched or Unattached Rows
* Performing a Join Between Tables in Different Databases
* Using Different MySQL Servers Simultaneously
* Referring to Join Output Column Names in ProgramsChapter 13: Statistical Techniques
* Introduction
* Calculating Descriptive Statistics
* Per-Group Descriptive Statistics
* Generating Frequency Distributions
* Counting Missing Values
* Calculating Linear Regressions or Correlation Coefficients
* Generating Random Numbers
* Randomizing a Set of Rows
* Selecting Random Items from a Set of Rows
* Assigning RanksChapter 14: Handling Duplicates
* Introduction
* Preventing Duplicates from Occurring in a Table
* Dealing with Duplicates When Loading Rows into a Table
* Counting and Identifying Duplicates
* Eliminating Duplicates from a Table
* Problem
* Solution
* DiscussionEliminating Duplicates from a Self-Join ResultChapter 15: Performing Transactions
* Introduction
* Choosing a Transactional Storage Engine
* Performing Transactions Using SQL
* Performing Transactions from Within Programs
* Using Transactions in Perl Programs
* Using Transactions in Ruby Programs
* Using Transactions in PHP Programs
* Using Transactions in Python Programs
* Using Transactions in Java Programs
* Using Alternatives to Transactions
* Problem
* Solution
* DiscussionChapter 16: Using Stored Routines, Triggers, and Events
* Introduction
* Creating Compound-Statement Objects
* Using a Stored Function to Encapsulate a Calculation
* Using a Stored Procedure to Return Multiple Values
* Using a Trigger to Define Dynamic Default Column Values
* Simulating TIMESTAMP Properties for Other Date and Time Types
* Using a Trigger to Log Changes to a Table
* Using Events to Schedule Database ActionsChapter 17: Introduction to MySQL on the Web
* Introduction
* Basic Principles of Web Page Generation
* Using Apache to Run Web Scripts
* Problem
* Solution
* DiscussionUsing Tomcat to Run Web Scripts
* Problem
* Solution
* DiscussionEncoding Special Characters in Web Output
* Problem
* Solution
* DiscussionChapter 18: Incorporating Query Results into Web Pages
* Introduction
* Displaying Query Results as Paragraph Text
* Displaying Query Results as Lists
* Problem
* Solution
* Discussion
* See AlsoDisplaying Query Results as TablesDisplaying Query Results as HyperlinksCreating a Navigation Index from Database Content
* Problem
* Solution
* Discussion
* See AlsoStoring Images or Other Binary Data
* Problem
* Solution
* Discussion
* See AlsoRetrieving Images or Other Binary DataServing Banner AdsServing Query Results for DownloadUsing a Template System to Generate Web Pages
* Problem
* Solution
* DiscussionChapter 19: Processing Web Input with MySQL
* Introduction
* Writing Scripts That Generate Web Forms
* Problem
* Solution
* Discussion
* See AlsoCreating Single-Pick Form Elements from Database ContentCreating Multiple-Pick Form Elements from Database ContentLoading a Database Record into a FormCollecting Web Input
* Problem
* Solution
* DiscussionValidating Web InputStoring Web Input in a DatabaseProcessing File Uploads
* Problem
* Solution
* DiscussionPerforming Searches and Presenting the ResultsGenerating Previous-Page and Next-Page Links
* Problem
* Solution
* DiscussionGenerating Click to Sort Table HeadingsWeb Page Access CountingWeb Page Access LoggingUsing MySQL for Apache Logging
* Problem
* Solution
* DiscussionChapter 20: Using MySQL-Based Web Session Management
* Introduction
* Using MySQL-Based Sessions in Perl Applications
* Problem
* Solution
* DiscussionUsing MySQL-Based Storage in Ruby ApplicationsUsing MySQL-Based Storage with the PHP Session Manager
* Problem
* Solution
* DiscussionUsing MySQL for Session-Backing Store with Tomcat
* Problem
* Solution
* DiscussionObtaining MySQL Software
* Obtaining Sample Source Code and Data
* Obtaining MySQL and Related SoftwareExecuting Programs from the Command Line
* Setting Environment Variables
* Executing ProgramsJSP and Tomcat Primer
* Servlet and JavaServer Pages Overview
* Setting Up a Tomcat Server
* Tomcat’s Directory Structure
* Restarting Applications Without Restarting Tomcat
* Web Application Structure
* Elements of JSP PagesReferencesColophon