MySQL Cookbook – Paul DuBois – 1st Edition

Chapter 1: Using the mysql Client Program
Introduction
Setting Up a MySQL User Account
Creating a Database and a Sample Table
What to Do if mysql Cannot Be Found
Specifying mysql Command Options
Executing SQL Statements Interactively
Executing SQL Statements Read from a File or Program
Controlling mysql Output Destination and Format
Using User-Defined Variables in SQL Statements

Chapter 2: Writing MySQL-Based Programs
Introduction
Connecting, Selecting a Database, and Disconnecting
Checking for Errors
Writing Library Files
Executing Statements and Retrieving Results
Handling Special Characters and NULL Values in Statements
Handling Special Characters in Identifiers
Identifying NULL Values in Result Sets
Techniques for Obtaining Connection Parameters
Conclusion and Words of Advice

Chapter 3: Selecting Data from Tables
Introduction
Specifying Which Columns and Rows to Select
Naming Query Result Columns
Sorting Query Results
Removing Duplicate Rows
Working with NULL Values
Writing Comparisons Involving NULL in Programs
Using Views to Simplify Table Access
Selecting Data from Multiple Tables
Selecting Rows from the Beginning, End, or Middle of Query Results
What to Do When LIMIT Requires the Wrong Sort Order
Calculating LIMIT Values from Expressions

Chapter 4: Table Management
Introduction
Cloning a Table
Saving a Query Result in a Table
Creating Temporary Tables
Generating Unique Table Names
Checking or Changing a Table Storage Engine
Copying a Table Using mysqldump

Chapter 5: Working with Strings
Introduction
String Properties
Choosing a String Data Type
Setting the Client Connection Character Set
Writing String Literals
Checking or Changing a String's Character Set or Collation
Converting the Lettercase of a String
Controlling Case Sensitivity in String Comparisons
Pattern Matching with SQL Patterns
Pattern Matching with Regular Expressions
Breaking Apart or Combining Strings
Searching for Substrings
Using Full-Text Searches
Using a Full-Text Search with Short Words
Requiring or Prohibiting Full-Text Search Words
Performing Full-Text Phrase Searches

Chapter 6: Working with Dates and Times
Introduction
Choosing a Temporal Data Type
Using Fractional Seconds Support
Changing MySQL's Date Format
Setting the Client Time Zone
Shifting Temporal Values Between Time Zones
Determining the Current Date or Time
Using TIMESTAMP or DATETIME to Track Row-Modification Times
Extracting Parts of Dates or Times
Synthesizing Dates or Times from Component Values
Converting Between Temporal Values and Basic Units
Calculating Intervals Between Dates or Times
Adding Date or Time Values
Calculating Ages
Finding the First Day, Last Day, or Length of a Month
Calculating Dates by Substring Replacement
Finding the Day of the Week for a Date
Finding Dates for Any Weekday of a Given Week
Performing Leap-Year Calculations
Canonizing Not-Quite-ISO Date Strings
Selecting Rows Based on Temporal Characteristics

Chapter 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 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
Defining a Custom Sort Order
Sorting ENUM Values

Chapter 8: Generating Summaries
Introduction
Basic Summary Techniques
Creating a View to Simplify Using a Summary
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
Summarizing Noncategorical Data
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 List

Chapter 9: Using Stored Routines, Triggers, and Scheduled Events
Introduction
Creating Compound-Statement Objects
Using Stored Functions to Encapsulate Calculations
Using Stored Procedures to Return Multiple Values
Using Triggers to Implement Dynamic Default Column Values
Using Triggers to Simulate Function-Based Indexes
Simulating TIMESTAMP Properties for Other Date and Time Types
Using Triggers to Log Changes to a Table
Using Events to Schedule Database Actions
Writing Helper Routines for Executing Dynamic SQL
Handling Errors Within Stored Programs
Using Triggers to Preprocess or Reject Data

Chapter 10: Working with Metadata
Introduction
Determining the Number of Rows Affected by a Statement
Obtaining Result Set Metadata
Determining Whether a Statement Produced a Result Set
Using Metadata to Format Query Output
Listing or Checking Existence of Databases or Tables
Accessing Table Column Definitions
Getting ENUM and SET Column Information
Getting Server Metadata
Writing Applications That Adapt to the MySQL Server Version

Chapter 11: Importing and Exporting Data
Introduction
Importing Data with LOAD DATA and mysqlimport
Importing CSV Files
Exporting Query Results from MySQL
Importing and Exporting NULL Values
Writing Your Own Data Export Programs
Converting Datafiles from One Format to Another
Extracting and Rearranging Datafile Columns
Exchanging Data Between MySQL and Microsoft Excel
Exporting Query Results as XML
Importing XML into MySQL
Guessing Table Structure from a Datafile

Chapter 12: Validating and Reformatting Data
Introduction
Using the SQL Mode to Reject Bad Input Values
Validating and Transforming Data
Using Pattern Matching to Validate Data
Using Patterns to Match Broad Content Types
Using Patterns to Match Numeric Values
Using Patterns to Match Dates or Times
Using Patterns to Match Email Addresses or URLs
Using Table Metadata to Validate Data
Using a Lookup Table to Validate Data
Converting Two-Digit Year Values to Four-Digit Form
Performing Validity Checking on Date or Time Subparts
Writing Date-Processing Utilities
Importing Non-ISO Date Values
Exporting Dates Using Non-ISO Formats
Epilogue

Chapter 13: Generating and Using Sequences
Introduction
Creating a Sequence Column and Generating Sequence Values
Choosing the Definition for a Sequence Column
The Effect of Row Deletions on Sequence Generation
Retrieving Sequence Values
Renumbering an Existing Sequence
Extending the Range of a Sequence Column
Reusing Values at the Top of a Sequence
Ensuring That Rows Are Renumbered in a Particular Order
Sequencing an Unsequenced Table
Managing Multiple Auto-Increment Values Simultaneously
Using Auto-Increment Values to Associate Tables
Using Sequence Generators as Counters
Generating Repeating Sequences

Chapter 14: Using Joins and Subqueries
Introduction
Finding Matches Between Tables
Finding Mismatches Between Tables
Identifying and Removing Mismatched or Unattached Rows
Comparing a Table to Itself
Producing Master-Detail Lists and Summaries
Enumerating a Many-to-Many Relationship
Finding Per-Group Minimum or Maximum Values
Using a Join to Fill or Identify Holes in a List
Using a Join to Control Query Sort Order
Referring to Join Output Column Names in Programs

Chapter 15: 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
Calculating Successive-Row Differences
Finding Cumulative Sums and Running Averages
Assigning Ranks
Computing Team Standings

Chapter 16: 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

Chapter 17: 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

Chapter 18: Introduction to MySQL on the Web
Introduction
Basic Principles of Web Page Generation
Using Apache to Run Web Scripts
Using Tomcat to Run Web Scripts
Encoding Special Characters in Web Output

Chapter 19: Generating Web Content from Query Results
Introduction
Displaying Query Results as Paragraphs
Displaying Query Results as Lists
Displaying Query Results as Tables
Displaying Query Results as Hyperlinks
Creating Navigation Indexes from Database Content
Storing Images or Other Binary Data
Serving Images or Other Binary Data
Serving Banner Ads
Serving Query Results for Download

Chapter 20: Processing Web Input with MySQL
Introduction
Writing Scripts That Generate Web Forms
Creating Single-Pick Form Elements from Database Content
Creating Multiple-Pick Form Elements from Database Content
Loading Database Content into a Form
Collecting Web Input
Validating Web Input
Storing Web Input in a Database
Processing File Uploads
Performing Web-Based Database Searches
Generating Previous-Page and Next-Page Links
Generating Click to Sort Table Headings
Web Page Access Counting
Web Page Access Logging
Using MySQL for Apache Logging

Chapter 21: Using MySQL-Based Web Session Management
Introduction
Using MySQL-Based Sessions in Perl Applications
Using MySQL-Based Storage in Ruby Applications
Using MySQL-Based Storage with the PHP Session Manager
Using MySQL for Session-Backing Store with Tomcat

Chapter 22: Server Administration
Introduction
Configuring the Server
Managing the Plugin Interface
Controlling Server Logging
Rotating or Expiring Logfiles
Rotating Log Tables or Expiring Log Table Rows
Monitoring the MySQL Server
Creating and Using Backups

Chapter 23: Security
Introduction
Understanding the mysql.user Table
Managing User Accounts
Implementing a Password Policy
Checking Password Strength
Expiring Passwords
Assigning Yourself a New Password
Resetting an Expired Password
Finding and Fixing Insecure Accounts
Disabling Use of Accounts with Pre-4.1 Passwords
Finding and Removing Anonymous Accounts
Modifying Any Host and Many Host Accounts