You are previewing MySQL Cookbook, 3rd Edition.
O'Reilly logo
MySQL Cookbook, 3rd Edition

Book Description

MySQL’s popularity has brought a flood of questions about how to solve specific problems, and that’s where this cookbook is essential. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations. Ideal for beginners and professional database and web developers, this updated third edition covers powerful features in MySQL 5.6 (and some in 5.7).

Table of Contents

  1. Preface
    1. Who This Book Is For
    2. What’s in This Book
    3. MySQL APIs Used in This Book
    4. Conventions Used in This Book
    5. The MySQL Cookbook Companion Web Site
    6. Version and Platform Notes
    7. Upgrade Note for Second Edition Readers
    8. Additional Resources
    9. Using Code Examples
    10. Safari® Books Online
    11. How to Contact Us
    12. Acknowledgments
  2. 1. Using the mysql Client Program
    1. Introduction
    2. Setting Up a MySQL User Account
    3. Creating a Database and a Sample Table
    4. Specifying mysql Command Options
    5. What to Do if mysql Cannot Be Found
    6. Executing SQL Statements Interactively
    7. Reading SQL Statements from a File or Program
    8. Controlling mysql Output Destination and Format
    9. Using User-Defined Variables in SQL Statements
  3. 2. Writing MySQL-Based Programs
    1. Introduction
    2. Connecting, Selecting a Database, and Disconnecting
    3. Checking for Errors
    4. Writing Library Files
    5. Issuing Statements and Retrieving Results
    6. Handling Special Characters and NULL Values in Statements
    7. Handling Special Characters in Identifiers
    8. Identifying NULL Values in Result Sets
    9. Techniques for Obtaining Connection Parameters
    10. Conclusion and Words of Advice
  4. 3. Selecting Data from Tables
    1. Introduction
    2. Specifying Which Columns and Rows to Select
    3. Controlling Column Names in Query Results
    4. Combining Columns to Construct Composite Values
    5. Debugging Comparison Expressions
    6. Removing Duplicate Rows
    7. Working with NULL Values
    8. Writing Comparisons Involving NULL in Programs
    9. Sorting a Query Result
    10. Using Views to Simplify Table Access
    11. Selecting Data from Multiple Tables
    12. Selecting Rows from the Beginning, End, or Middle of a Query Result
    13. What to Do When LIMIT Requires the <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="quote">&#8220;<span class="quote">Wrong</span>&#8221;</span> Sort Order Sort Order
    14. Calculating LIMIT Values from Expressions
  5. 4. Table Management
    1. Introduction
    2. Cloning a Table
    3. Saving a Query Result in a Table
    4. Creating Temporary Tables
    5. Checking or Changing a Table’s Storage Engine
    6. Generating Unique Table Names
  6. 5. Working with Strings
    1. Introduction
    2. String Properties
    3. Choosing a String Data Type
    4. Setting the Client Connection Character Set
    5. Writing String Literals
    6. Checking or Changing a String’s Character Set or Collation
    7. Converting the Lettercase of a String
    8. Controlling Case Sensitivity in String Comparisons
    9. Pattern Matching with SQL Patterns
    10. Pattern Matching with Regular Expressions
    11. Controlling Case Sensitivity in Pattern Matching
    12. Breaking Apart or Combining Strings
    13. Searching for Substrings
    14. Using Full-Text Searches
    15. Using a Full-Text Search with Short Words
    16. Requiring or Prohibiting Full-Text Search Words
    17. Performing Full-Text Phrase Searches
  7. 6. Working with Dates and Times
    1. Introduction
    2. Choosing a Temporal Data Type
    3. Changing MySQL’s Date Format
    4. Setting the Client Time Zone
    5. Shifting Temporal Values Between Time Zones
    6. Determining the Current Date or Time
    7. Using TIMESTAMP or DATETIME to Track Row Modification Times
    8. Extracting Parts of Dates or Times
    9. Synthesizing Dates or Times from Component Values
    10. Converting Between Temporal Values and Basic Units
    11. Calculating Intervals Between Dates or Times
    12. Adding Date or Time Values
    13. Calculating Ages
    14. Finding the First Day, Last Day, or Length of a Month
    15. Calculating Dates by Substring Replacement
    16. Finding the Day of the Week for a Date
    17. Finding Dates for Any Weekday of a Given Week
    18. Performing Leap Year Calculations
    19. Canonizing Not-Quite-ISO Date Strings
    20. Treating Dates or Times as Numbers
    21. Treating Strings as Temporal Values
    22. Selecting Rows Based on Their Temporal Characteristics
  8. 7. Sorting Query Results
    1. Introduction
    2. Using ORDER BY to Sort Query Results
    3. Using Expressions for Sorting
    4. Displaying One Set of Values While Sorting by Another
    5. Controlling Case Sensitivity of String Sorts
    6. Date-Based Sorting
    7. Sorting by Calendar Day
    8. Sorting by Day of Week
    9. Sorting by Time of Day
    10. Sorting by Substrings of Column Values
    11. Sorting by Fixed-Length Substrings
    12. Sorting by Variable-Length Substrings
    13. Sorting Hostnames in Domain Order
    14. Sorting Dotted-Quad IP Values in Numeric Order
    15. Floating Values to the Head or Tail of the Sort Order
    16. Defining a Custom Sort Order
    17. Sorting ENUM Values
  9. 8. Generating Summaries
    1. Introduction
    2. Summarizing with COUNT()
    3. Creating a View to Simplify Using a Summary
    4. Summarizing with MIN() and MAX()
    5. Summarizing with SUM() and AVG()
    6. Using DISTINCT to Eliminate Duplicates
    7. Finding Values Associated with Minimum and Maximum Values
    8. Controlling String Case Sensitivity for MIN() and MAX()
    9. Dividing a Summary into Subgroups
    10. Summaries and NULL Values
    11. Selecting Only Groups with Certain Characteristics
    12. Using Counts to Determine Whether Values Are Unique
    13. Grouping by Expression Results
    14. Summarizing Noncategorical Data
    15. Controlling Summary Display Order
    16. Finding Smallest or Largest Summary Values
    17. Date-Based Summaries
    18. Working with Per-Group and Overall Summary Values Simultaneously
    19. Generating a Report That Includes a Summary and a List
  10. 9. Working with Metadata
    1. Introduction
    2. Determining the Number of Rows Affected by a Statement
    3. Obtaining Result Set Metadata
    4. Determining Whether a Statement Produced a Result Set
    5. Using Metadata to Format Query Output
    6. Listing or Checking Existence of Databases or Tables
    7. Accessing Table Column Definitions
    8. Getting ENUM and SET Column Information
    9. Using Table Structure Information in Applications
    10. Getting Server Metadata
    11. Writing Applications That Adapt to the MySQL Server Version
    12. Determining the Default Database
    13. Monitoring the MySQL Server
  11. 10. Importing and Exporting Data
    1. Introduction
    2. Importing Data with LOAD DATA and mysqlimport
    3. Specifying the Datafile Location
    4. Specifying Column and Line Delimiters
    5. Dealing with Quotes and Special Characters
    6. Importing CSV Files
    7. Reading Files from Different Operating Systems
    8. Handling Duplicate Key Values
    9. Obtaining Diagnostics About Bad Input Data
    10. Skipping Datafile Lines
    11. Specifying Input Column Order
    12. Preprocessing Input Values Before Inserting Them
    13. Ignoring Datafile Columns
    14. Exporting Query Results from MySQL
    15. Exporting Tables as Text Files
    16. Exporting Table Contents or Definitions in SQL Format
    17. Copying Tables or Databases to Another Server
    18. Writing Your Own Export Programs
    19. Converting Datafiles from One Format to Another
    20. Extracting and Rearranging Datafile Columns
    21. Using the SQL Mode to Control Bad Input Data Handling
    22. Validating and Transforming Data
    23. Using Pattern Matching to Validate Data
    24. Using Patterns to Match Broad Content Types
    25. Using Patterns to Match Numeric Values
    26. Using Patterns to Match Dates or Times
    27. Using Patterns to Match Email Addresses or URLs
    28. Using Table Metadata to Validate Data
    29. Using a Lookup Table to Validate Data
    30. Converting Two-Digit Year Values to Four-Digit Form
    31. Performing Validity Checking on Date or Time Subparts
    32. Writing Date-Processing Utilities
    33. Using Dates with Missing Components
    34. Importing Non-ISO Date Values
    35. Exporting Dates Using Non-ISO Formats
    36. Importing and Exporting NULL Values
    37. Guessing Table Structure from a Datafile
    38. Exchanging Data Between MySQL and Microsoft Excel
    39. Exporting Query Results as XML
    40. Importing XML into MySQL
    41. Epilogue
  12. 11. Generating and Using Sequences
    1. Introduction
    2. Creating a Sequence Column and Generating Sequence Values
    3. Choosing the Definition for a Sequence Column
    4. The Effect of Row Deletions on Sequence Generation
    5. Retrieving Sequence Values
    6. Renumbering an Existing Sequence
    7. Extending the Range of a Sequence Column
    8. Reusing Values at the Top of a Sequence
    9. Ensuring That Rows Are Renumbered in a Particular Order
    10. Sequencing an Unsequenced Table
    11. Managing Multiple Auto-Increment Values Simultaneously
    12. Using Auto-Increment Values to Associate Tables
    13. Using Sequence Generators as Counters
    14. Generating Repeating Sequences
  13. 12. Using Multiple Tables
    1. Introduction
    2. Finding Matches Between Tables
    3. Finding Mismatches Between Tables
    4. Joining Tables from Different Databases
    5. Comparing a Table to Itself
    6. Eliminating Duplicates from a Self-Join Result
    7. Producing Master-Detail Lists and Summaries
    8. Enumerating a Many-to-Many Relationship
    9. Finding Per-Group Minimum or Maximum Values
    10. Using a Join to Fill or Identify Holes in a List
    11. Using a Join to Control Query Output Order
    12. Identifying and Removing Mismatched or Unattached Rows
    13. Referring to Join Output Column Names in Programs
  14. 13. Statistical Techniques
    1. Introduction
    2. Calculating Descriptive Statistics
    3. Per-Group Descriptive Statistics
    4. Generating Frequency Distributions
    5. Counting Missing Values
    6. Calculating Linear Regressions or Correlation Coefficients
    7. Generating Random Numbers
    8. Randomizing a Set of Rows
    9. Selecting Random Items from a Set of Rows
    10. Calculating Successive-Row Differences
    11. Finding Cumulative Sums and Running Averages
    12. Assigning Ranks
    13. Computing Team Standings
  15. 14. Handling Duplicates
    1. Introduction
    2. Preventing Duplicates from Occurring in a Table
    3. Dealing with Duplicates When Loading Rows into a Table
    4. Counting and Identifying Duplicates
    5. Eliminating Duplicates from a Table
  16. 15. Performing Transactions
    1. Introduction
    2. Choosing a Transactional Storage Engine
    3. Performing Transactions Using SQL
    4. Performing Transactions from Within Programs
    5. Using Transactions in Perl Programs
    6. Using Transactions in Ruby Programs
    7. Using Transactions in PHP Programs
    8. Using Transactions in Python Programs
    9. Using Transactions in Java Programs
  17. 16. Using Stored Routines, Triggers, and Scheduled Events
    1. Introduction
    2. Creating Compound-Statement Objects
    3. Using a Stored Function to Encapsulate a Calculation
    4. Using a Stored Procedure to <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="quote">&#8220;<span class="quote">Return</span>&#8221;</span> Multiple Values Multiple Values
    5. Using a Trigger to Define Dynamic Default Column Values
    6. Simulating TIMESTAMP Properties for Other Date and Time Types
    7. Using a Trigger to Log Changes to a Table
    8. Using Events to Schedule Database Actions
  18. 17. Introduction to MySQL on the Web
    1. Introduction
    2. Basic Principles of Web Page Generation
    3. Using Apache to Run Web Scripts
    4. Using Tomcat to Run Web Scripts
    5. Encoding Special Characters in Web Output
  19. 18. Generating Web Content from Query Results
    1. Introduction
    2. Displaying Query Results as Paragraphs
    3. Displaying Query Results as Lists
    4. Displaying Query Results as Tables
    5. Displaying Query Results as Hyperlinks
    6. Creating Navigation Indexes from Database Content
    7. Storing Images or Other Binary Data
    8. Serving Images or Other Binary Data
    9. Serving Banner Ads
    10. Serving Query Results for Download
  20. 19. Processing Web Input with MySQL
    1. Introduction
    2. Writing Scripts That Generate Web Forms
    3. Creating Single-Pick Form Elements from Database Content
    4. Creating Multiple-Pick Form Elements from Database Content
    5. Loading a Database Record into a Form
    6. Collecting Web Input
    7. Validating Web Input
    8. Storing Web Input in a Database
    9. Processing File Uploads
    10. Performing Searches and Presenting the Results
    11. Generating Previous-Page and Next-Page Links
    12. Generating <span xmlns="http://www.w3.org/1999/xhtml" xmlns:epub="http://www.idpf.org/2007/ops" xmlns:m="http://www.w3.org/1998/Math/MathML" xmlns:pls="http://www.w3.org/2005/01/pronunciation-lexicon" xmlns:ssml="http://www.w3.org/2001/10/synthesis" xmlns:svg="http://www.w3.org/2000/svg" class="quote">&#8220;<span class="quote">Click to Sort</span>&#8221;</span> Table Headings Table Headings
    13. Web Page Access Counting
    14. Web Page Access Logging
    15. Using MySQL for Apache Logging
  21. 20. Using MySQL-Based Web Session Management
    1. Introduction
    2. Using MySQL-Based Sessions in Perl Applications
    3. Using MySQL-Based Storage in Ruby Applications
    4. Using MySQL-Based Storage with the PHP Session Manager
    5. Using MySQL for Session-Backing Store with Tomcat
  22. A. Obtaining MySQL Software
    1. Obtaining Sample Source Code and Data
    2. Obtaining MySQL and Related Software
  23. B. Executing Programs from the Command Line
    1. Setting Environment Variables
    2. Executing Programs
  24. C. References
    1. MySQL Resources
    2. Perl Resources
    3. Ruby Resources
    4. PHP Resources
    5. Python Resources
    6. Java Resources
    7. Other Resources
  25. Index
  26. About the Author
  27. Colophon
  28. Copyright