MySQL Cookbook, 3rd Edition

MySQL Cookbook, 3rd Edition

by Paul DuBois
Released August 2014
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781449374020

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

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 for programmers who donâ??t have the time (or expertise) to solve MySQL problems from scratch.

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). The book focuses on programming APIs in Python, PHP, Java, Perl, and Ruby. With more than 200+ recipes, youâ??ll learn how to:

  • Use the mysql client and write MySQL-based programs
  • Create, populate, and select data from tables
  • Store, retrieve, and manipulate strings
  • Work with dates and times
  • Sort query results and generate summaries
  • Use stored routines, triggers, and scheduled events
  • Import, export, validate, and reformat data
  • Perform transactions and work with statistics
  • Process web input, and generate web content from query results
  • Use MySQL-based web session management
  • Provide security and server administration

Publisher resources

View/Submit Errata

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. Version and Platform Notes
    5. Conventions Used in This Book
    6. The MySQL Cookbook Companion Website
    7. Obtaining MySQL and Related Software
    8. Using Code Examples
    9. Safari® Books Online
    10. How to Contact Us
    11. 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. What to Do if mysql Cannot Be Found
    5. Specifying mysql Command Options
    6. Executing SQL Statements Interactively
    7. Executing SQL Statements Read 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. Executing 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. Naming Query Result Columns
    4. Sorting Query Results
    5. Removing Duplicate Rows
    6. Working with NULL Values
    7. Writing Comparisons Involving NULL in Programs
    8. Using Views to Simplify Table Access
    9. Selecting Data from Multiple Tables
    10. Selecting Rows from the Beginning, End, or Middle of Query Results
    11. What to Do When LIMIT Requires the “Wrong” Sort Order
    12. 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. Generating Unique Table Names
    6. Checking or Changing a Table Storage Engine
    7. Copying a Table Using mysqldump
  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. Breaking Apart or Combining Strings
    12. Searching for Substrings
    13. Using Full-Text Searches
    14. Using a Full-Text Search with Short Words
    15. Requiring or Prohibiting Full-Text Search Words
    16. Performing Full-Text Phrase Searches
  7. 6. Working with Dates and Times
    1. Introduction
    2. Choosing a Temporal Data Type
    3. Using Fractional Seconds Support
    4. Changing MySQL’s Date Format
    5. Setting the Client Time Zone
    6. Shifting Temporal Values Between Time Zones
    7. Determining the Current Date or Time
    8. Using TIMESTAMP or DATETIME to Track Row-Modification Times
    9. Extracting Parts of Dates or Times
    10. Synthesizing Dates or Times from Component Values
    11. Converting Between Temporal Values and Basic Units
    12. Calculating Intervals Between Dates or Times
    13. Adding Date or Time Values
    14. Calculating Ages
    15. Finding the First Day, Last Day, or Length of a Month
    16. Calculating Dates by Substring Replacement
    17. Finding the Day of the Week for a Date
    18. Finding Dates for Any Weekday of a Given Week
    19. Performing Leap-Year Calculations
    20. Canonizing Not-Quite-ISO Date Strings
    21. Selecting Rows Based on 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 Substrings of Column Values
    8. Sorting by Fixed-Length Substrings
    9. Sorting by Variable-Length Substrings
    10. Sorting Hostnames in Domain Order
    11. Sorting Dotted-Quad IP Values in Numeric Order
    12. Floating Values to the Head or Tail of the Sort Order
    13. Defining a Custom Sort Order
    14. Sorting ENUM Values
  9. 8. Generating Summaries
    1. Introduction
    2. Basic Summary Techniques
    3. Creating a View to Simplify Using a Summary
    4. Finding Values Associated with Minimum and Maximum Values
    5. Controlling String Case Sensitivity for MIN() and MAX()
    6. Dividing a Summary into Subgroups
    7. Summaries and NULL Values
    8. Selecting Only Groups with Certain Characteristics
    9. Using Counts to Determine Whether Values Are Unique
    10. Grouping by Expression Results
    11. Summarizing Noncategorical Data
    12. Finding Smallest or Largest Summary Values
    13. Date-Based Summaries
    14. Working with Per-Group and Overall Summary Values Simultaneously
    15. Generating a Report That Includes a Summary and a List
  10. 9. Using Stored Routines, Triggers, and Scheduled Events
    1. Introduction
    2. Creating Compound-Statement Objects
    3. Using Stored Functions to Encapsulate Calculations
    4. Using Stored Procedures to “Return” Multiple Values
    5. Using Triggers to Implement Dynamic Default Column Values
    6. Using Triggers to Simulate Function-Based Indexes
    7. Simulating TIMESTAMP Properties for Other Date and Time Types
    8. Using Triggers to Log Changes to a Table
    9. Using Events to Schedule Database Actions
    10. Writing Helper Routines for Executing Dynamic SQL
    11. Handling Errors Within Stored Programs
    12. Using Triggers to Preprocess or Reject Data
  11. 10. 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. Getting Server Metadata
    10. Writing Applications That Adapt to the MySQL Server Version
  12. 11. Importing and Exporting Data
    1. Introduction
    2. Importing Data with LOAD DATA and mysqlimport
    3. Importing CSV Files
    4. Exporting Query Results from MySQL
    5. Importing and Exporting NULL Values
    6. Writing Your Own Data Export Programs
    7. Converting Datafiles from One Format to Another
    8. Extracting and Rearranging Datafile Columns
    9. Exchanging Data Between MySQL and Microsoft Excel
    10. Exporting Query Results as XML
    11. Importing XML into MySQL
    12. Guessing Table Structure from a Datafile
  13. 12. Validating and Reformatting Data
    1. Introduction
    2. Using the SQL Mode to Reject Bad Input Values
    3. Validating and Transforming Data
    4. Using Pattern Matching to Validate Data
    5. Using Patterns to Match Broad Content Types
    6. Using Patterns to Match Numeric Values
    7. Using Patterns to Match Dates or Times
    8. Using Patterns to Match Email Addresses or URLs
    9. Using Table Metadata to Validate Data
    10. Using a Lookup Table to Validate Data
    11. Converting Two-Digit Year Values to Four-Digit Form
    12. Performing Validity Checking on Date or Time Subparts
    13. Writing Date-Processing Utilities
    14. Importing Non-ISO Date Values
    15. Exporting Dates Using Non-ISO Formats
    16. Epilogue
  14. 13. 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
  15. 14. Using Joins and Subqueries
    1. Introduction
    2. Finding Matches Between Tables
    3. Finding Mismatches Between Tables
    4. Identifying and Removing Mismatched or Unattached Rows
    5. Comparing a Table to Itself
    6. Producing Master-Detail Lists and Summaries
    7. Enumerating a Many-to-Many Relationship
    8. Finding Per-Group Minimum or Maximum Values
    9. Using a Join to Fill or Identify Holes in a List
    10. Using a Join to Control Query Sort Order
    11. Referring to Join Output Column Names in Programs
  16. 15. 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
  17. 16. 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
  18. 17. 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
  19. 18. 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
  20. 19. 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
  21. 20. 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 Database Content into a Form
    6. Collecting Web Input
    7. Validating Web Input
    8. Storing Web Input in a Database
    9. Processing File Uploads
    10. Performing Web-Based Database Searches
    11. Generating Previous-Page and Next-Page Links
    12. Generating “Click to Sort” Table Headings
    13. Web Page Access Counting
    14. Web Page Access Logging
    15. Using MySQL for Apache Logging
  22. 21. 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
  23. 22. Server Administration
    1. Introduction
    2. Configuring the Server
    3. Managing the Plug-In Interface
    4. Controlling Server Logging
    5. Rotating or Expiring Logfiles
    6. Rotating Log Tables or Expiring Log Table Rows
    7. Monitoring the MySQL Server
    8. Creating and Using Backups
  24. 23. Security
    1. Introduction
    2. Understanding the mysql.user Table
    3. Managing User Accounts
    4. Implementing a Password Policy
    5. Checking Password Strength
    6. Expiring Passwords
    7. Assigning Yourself a New Password
    8. Resetting an Expired Password
    9. Finding and Fixing Insecure Accounts
    10. Disabling Use of Accounts with Pre-4.1 Passwords
    11. Finding and Removing Anonymous Accounts
    12. Modifying “Any Host” and “Many Host” Accounts
  25. Index
  26. Colophon
  27. Copyright

Product information

  • Title: MySQL Cookbook, 3rd Edition
  • Author(s): Paul DuBois
  • Release date: August 2014
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781449374020