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