MySQL Cookbook

Larger Cover

MySQL Cookbook

By Paul DuBois

Publisher: O'Reilly Media

Released: October 2002

Pages: 1024

While MySQL has turned up among high profile users such as Yahoo!, NASA and the U.S. Census Bureau, the rising popularity of this open source database is especially keen among users with little database experience. These days, even a small organization or web site has uses for a database, and MySQL is an obvious choice. Affordable and easy to use, MySQL packs the power, speed and efficiency that enable it to rival expensive, proprietary database solutions. Yet, even if you know the basics, anyone without practical MySQL experience--novices and skilled DBAs alike--might stumble over common database-related tasks. Fortunately, there's a sensible shortcut.

MySQL Cookbook provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe"--short, focused pieces of code that you can insert directly into your applications. But MySQL Cookbook is more than a collection of cut-and-paste code. You also get explanations of how and why the code works, so you can learn to adapt the techniques to similar situations.

The book covers a lot of ground. Solutions for typical MySQL dilemmas range from simple ways to find all records that contain a given string, to more difficult problems, such as finding matching/non-matching records in two tables. Whether you use MySQL on Unix, Linux, Windows or the Mac OS X platform, the book will show you how to:

Import data from external sources
Export data for use by external programs
Access MySQL from your web server
Use scripts with MySQL to read queries from a file
Access MySQL from within client programs that use Perl, PHP, Java, Python and other languages
Construct queries that solve commonly-occurring questions
Interact with the server

This learn-as-you-go resource will help users of all levels exploit MySQL more fully. MySQL Cookbook supplies you with an armory of ready-made techniques for specific problems so that, even if you're an experienced MySQL user, you don't have to write everything from scratch.

Chapter 1 Using the mysql Client Program
1. Introduction
2. Setting Up a MySQL User Account
3. Creating a Database and a Sample Table
4. Starting and Terminating mysql
5. Specifying Connection Parameters by Using Option Files
6. Protecting Option Files
7. Mixing Command-Line and Option File Parameters
8. What to Do if mysql Cannot Be Found
9. Setting Environment Variables
10. Issuing Queries
11. Selecting a Database
12. Canceling a Partially Entered Query
13. Repeating and Editing Queries
14. Using Auto-Completion for Database and Table Names
15. Using SQL Variables in Queries
16. Telling mysql to Read Queries from a File
17. Telling mysql to Read Queries from Other Programs
18. Specifying Queries on the Command Line
19. Using Copy and Paste as a mysql Input Source
20. Preventing Query Output from Scrolling off the Screen
21. Sending Query Output to a File or to a Program
22. Selecting Tabular or Tab-Delimited Query Output Format
23. Specifying Arbitrary Output Column Delimiters
24. Producing HTML Output
25. Producing XML Output
26. Suppressing Column Headings in Query Output
27. Numbering Query Output Lines
28. Making Long Output Lines More Readable
29. Controlling mysql’s Verbosity Level
30. Logging Interactive mysql Sessions
31. Creating mysql Scripts from Previously Executed Queries
32. Using mysql as a Calculator
33. Using mysql in Shell Scripts
Chapter 2 Writing MySQL-Based Programs
1. Introduction
2. Connecting to the MySQL Server, Selecting a Database, and Disconnecting
3. Checking for Errors
4. Writing Library Files
5. Issuing Queries and Retrieving Results
6. Moving Around Within a Result Set
7. Using Prepared Statements and Placeholders in Queries
8. Including Special Characters and NULL Values in Queries
9. Handling NULL Values in Result Sets
10. Writing an Object-Oriented MySQL Interface for PHP
11. Ways of Obtaining Connection Parameters
12. Conclusion and Words of Advice
Chapter 3 Record Selection Techniques
1. Introduction
2. Specifying Which Columns to Display
3. Avoiding Output Column Order Problems When Writing Programs
4. Giving Names to Output Columns
5. Using Column Aliases to Make Programs Easier to Write
6. Combining Columns to Construct Composite Values
7. Specifying Which Rows to Select
8. WHERE Clauses and Column Aliases
9. Displaying Comparisons to Find Out How Something Works
10. Reversing or Negating Query Conditions
11. Removing Duplicate Rows
12. Working with NULL Values
13. Negating a Condition on a Column That Contains NULL Values
14. Writing Comparisons Involving NULL in Programs
15. Mapping NULL Values to Other Values for Display
16. Sorting a Result Set
17. Selecting Records from the Beginning or End of a Result Set
18. Pulling a Section from the Middle of a Result Set
19. Choosing Appropriate LIMIT Values
20. Calculating LIMIT Values from Expressions
21. What to Do When LIMIT Requires the “Wrong” Sort Order
22. Selecting a Result Set into an Existing Table
23. Creating a Destination Table on the Fly from a Result Set
24. Moving Records Between Tables Safely
25. Creating Temporary Tables
26. Cloning a Table Exactly
27. Generating Unique Table Names
Chapter 4 Working with Strings
1. Introduction
2. Writing Strings That Include Quotes or Special Characters
3. Preserving Trailing Spaces in String Columns
4. Testing String Equality or Relative Ordering
5. Decomposing or Combining Strings
6. Checking Whether a String Contains a Substring
7. Pattern Matching with SQL Patterns
8. Pattern Matching with Regular Expressions
9. Matching Pattern Metacharacters Literally
10. Controlling Case Sensitivity in String Comparisons
11. Controlling Case Sensitivity in Pattern Matching
12. Using FULLTEXT Searches
13. Using a FULLTEXT Search with Short Words
14. Requiring or Excluding FULLTEXT Search Words
15. Performing Phrase Searches with a FULLTEXT Index
Chapter 5 Working with Dates and Times
1. Introduction
2. Changing MySQL’s Date Format
3. Telling MySQL How to Display Dates or Times
4. Determining the Current Date or Time
5. Decomposing Dates and Times Using Formatting Functions
6. Decomposing Dates or Times Using Component-Extraction Functions
7. Decomposing Dates or Times Using String Functions
8. Synthesizing Dates or Times Using Formatting Functions
9. Synthesizing Dates or Times Using Component-Extraction Functions
10. Combining a Date and a Time into a Date-and-Time Value
11. Converting Between Times and Seconds
12. Converting Between Dates and Days
13. Converting Between Date-and-Time Values and Seconds
14. Adding a Temporal Interval to a Time
15. Calculating Intervals Between Times
16. Breaking Down Time Intervals into Components
17. Adding a Temporal Interval to a Date
18. Calculating Intervals Between Dates
19. Canonizing Not-Quite-ISO Date Strings
20. Calculating Ages
21. Shifting Dates by a Known Amount
22. Finding First and Last Days of Months
23. Finding the Length of a Month
24. Calculating One Date from Another by Substring Replacement
25. Finding the Day of the Week for a Date
26. Finding Dates for Days of the Current Week
27. Finding Dates for Weekdays of Other Weeks
28. Performing Leap Year Calculations
29. Treating Dates or Times as Numbers
30. Forcing MySQL to Treat Strings as Temporal Values
31. Selecting Records Based on Their Temporal Characteristics
32. Using TIMESTAMP Values
33. Recording a Row’s Last Modification Time
34. Recording a Row’s Creation Time
35. Performing Calculations with TIMESTAMP Values
36. Displaying TIMESTAMP Values in Readable Form
Chapter 6 Sorting Query Results
1. Introduction
2. Using ORDER BY to Sort Query Results
3. Sorting Subsets of a Table
4. Sorting Expression Results
5. Displaying One Set of Values While Sorting by Another
6. Sorting and NULL Values
7. Controlling Case Sensitivity of String Sorts
8. Date-Based Sorting
9. Sorting by Calendar Day
10. Sorting by Day of Week
11. Sorting by Time of Day
12. Sorting Using Substrings of Column Values
13. Sorting by Fixed-Length Substrings
14. Sorting by Variable-Length Substrings
15. Sorting Hostnames in Domain Order
16. Sorting Dotted-Quad IP Values in Numeric Order
17. Floating Specific Values to the Head or Tail of the Sort Order
18. Sorting in User-Defined Orders
19. Sorting ENUM Values
Chapter 7 Generating Summaries
1. Introduction
2. Summarizing with COUNT( )
3. Summarizing with MIN( ) and MAX( )
4. Summarizing with SUM( ) and AVG( )
5. Using DISTINCT to Eliminate Duplicates
6. Finding Values Associated with Minimum and Maximum Values
7. Controlling String Case Sensitivity for MIN( ) and MAX( )
8. Dividing a Summary into Subgroups
9. Summaries and NULL Values
10. Selecting Only Groups with Certain Characteristics
11. Determining Whether Values are Unique
12. Grouping by Expression Results
13. Categorizing Non-Categorical Data
14. Controlling Summary Display Order
15. Finding Smallest or Largest Summary Values
16. Date-Based Summaries
17. Working with Per-Group and Overall Summary Values Simultaneously
18. Generating a Report That Includes a Summary and a List
Chapter 8 Modifying Tables with ALTER TABLE
1. Introduction
2. Dropping, Adding, or Repositioning a Column
3. Changing a Column Definition or Name
4. The Effect of ALTER TABLE on Null and Default Value Attributes
5. Changing a Column’s Default Value
6. Changing a Table Type
7. Renaming a Table
8. Adding or Dropping Indexes
9. Eliminating Duplicates by Adding an Index
10. Using ALTER TABLE to Normalize a Table
Chapter 9 Obtaining and Using Metadata
1. Introduction
2. Obtaining the Number of Rows Affected by a Query
3. Obtaining Result Set Metadata
4. Determining Presence or Absence of a Result Set
5. Formatting Query Results for Display
6. Getting Table Structure Information
7. Getting ENUM and SET Column Information
8. Database-Independent Methods of Obtaining Table Information
9. Applying Table Structure Information
10. Listing Tables and Databases
11. Testing Whether a Table Exists
12. Testing Whether a Database Exists
13. Getting Server Metadata
14. Writing Applications That Adapt to the MySQL Server Version
15. Determining the Current Database
16. Determining the Current MySQL User
17. Monitoring the MySQL Server
18. Determining Which Table Types the Server Supports
Chapter 10 Importing and Exporting Data
1. Introduction
2. Importing Data with LOAD DATA and mysqlimport
3. Specifying the Datafile Location
4. Specifying the Datafile Format
5. Dealing with Quotes and Special Characters
6. Importing CSV Files
7. Reading Files from Different Operating Systems
8. Handling Duplicate Index Values
9. Getting LOAD DATA to Cough Up More Information
10. Don’t Assume LOAD DATA Knows More than It Does
11. Skipping Datafile Lines
12. Specifying Input Column Order
13. Skipping Datafile Columns
14. Exporting Query Results from MySQL
15. Exporting Tables as Raw Data
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. Validating and Transforming Data
22. Validation by Direct Comparison
23. Validation by Pattern Matching
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 and URLs
28. Validation Using Table Metadata
29. Validation Using a Lookup Table
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. Performing Date Conversion Using SQL
35. Using Temporary Tables for Data Transformation
36. Dealing with NULL Values
37. Guessing Table Structure from a Datafile
38. A LOAD DATA Diagnostic Utility
39. Exchanging Data Between MySQL and Microsoft Access
40. Exchanging Data Between MySQL and Microsoft Excel
41. Exchanging Data Between MySQL and FileMaker Pro
42. Exporting Query Results as XML
43. Importing XML into MySQL
44. Epilog
Chapter 11 Generating and Using Sequences
1. Introduction
2. Using AUTO_INCREMENT To Set Up a Sequence Column
3. Generating Sequence Values
4. Choosing the Type for a Sequence Column
5. The Effect of Record Deletions on Sequence Generation
6. Retrieving Sequence Values
7. Determining Whether to Resequence a Column
8. Extending the Range of a Sequence Column
9. Renumbering an Existing Sequence
10. Reusing Values at the Top of a Sequence
11. Ensuring That Rows Are Renumbered in a Particular Order
12. Starting a Sequence at a Particular Value
13. Sequencing an Unsequenced Table
14. Using an AUTO_INCREMENT Column to Create Multiple Sequences
15. Managing Multiple SimultaneousAUTO_INCREMENT Values
16. Using AUTO_INCREMENT Valuesto Relate Tables
17. Using Single-Row Sequence Generators
18. Generating Repeating Sequences
19. Numbering Query Output Rows Sequentially
Chapter 12 Using Multiple Tables
1. Introduction
2. Combining Rows in One Table with Rows in Another
3. Performing a Join Between Tables in Different Databases
4. Referring to Join Output Column Names in Programs
5. Finding Rows in One Table That Match Rows in Another
6. Finding Rows with No Match in Another Table
7. Finding Rows Containing Per-Group Minimum or Maximum Values
8. Computing Team Standings
9. Producing Master-Detail Lists and Summaries
10. Using a Join to Fill in Holes in a List
11. Enumerating a Many-to-Many Relationship
12. Comparing a Table to Itself
13. Calculating Differences Between Successive Rows
14. Finding Cumulative Sums and Running Averages
15. Using a Join to Control Query Output Order
16. Converting Subselects to Join Operations
17. Selecting Records in Parallel from Multiple Tables
18. Inserting Records in One Table That Include Values from Another
19. Updating One Table Based on Values in Another
20. Using a Join to Create a Lookup Table from Descriptive Labels
21. Deleting Related Rows in Multiple Tables
22. Identifying and Removing Unattached Records
23. Using Different MySQL Servers Simultaneously
Chapter 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. Assigning Ranks
Chapter 14 Handling Duplicates
1. Introduction
2. Preventing Duplicates from Occurring in a Table
3. Dealing with Duplicates at Record-Creation Time
4. Counting and Identifying Duplicates
5. Eliminating Duplicates from a Query Result
6. Eliminating Duplicates from a Self-Join Result
7. Eliminating Duplicates from a Table
Chapter 15 Performing Transactions
1. Introduction
2. Verifying Transaction Support Requirements
3. Performing Transactions Using SQL
4. Performing Transactions from Within Programs
5. Using Transactions in Perl Programs
6. Using Transactions in PHP Programs
7. Using Transactions in Python Programs
8. Using Transactions in Java Programs
9. Using Alternatives to Transactions
Chapter 16 Introduction to MySQL on the Web
1. Introduction
2. Basic Web Page Generation
3. Using Apache to Run Web Scripts
4. Using Tomcat to Run Web Scripts
5. Encoding Special Characters in Web Output
Chapter 17 Incorporating Query Resultsinto Web Pages
1. Introduction
2. Displaying Query Results as Paragraph Text
3. Displaying Query Results as Lists
4. Displaying Query Results as Tables
5. Displaying Query Results as Hyperlinks
6. Creating a Navigation Index from Database Content
7. Storing Images or Other Binary Data
8. Retrieving Images or Other Binary Data
9. Serving Banner Ads
10. Serving Query Results for Download
Chapter 18 Processing Web Input with MySQL
1. Introduction
2. Creating Forms in Scripts
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. Using Web Input to Construct Queries
9. Processing File Uploads
10. Performing Searches and Presenting the Results
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
Chapter 19 Using MySQL-Based Web Session Management
1. Introduction
2. Using MySQL-Based Sessions in Perl Applications
3. Using MySQL-Based Storage with the PHP Session Manager
4. Using MySQL for Session BackingStore with Tomcat

Appendix Obtaining MySQL Software
1. Obtaining Sample Source Code and Data
2. Obtaining MySQL and Related Software
Appendix JSP and Tomcat Primer
1. Servlet and JavaServer Pages Overview
2. Setting Up a Tomcat Server
3. Web Application Structure
4. Elements of JSP Pages
Appendix References
1. MySQL Resources
2. Perl Resources
3. PHP Resources
4. Python Resources
5. Java Resources
6. Apache Resources
7. Other Resources
Colophon

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. The animal on the cover of MySQL Cookbook is a green anole. These common lizards can be found in the Southeastern United States, the Caribbean, and South America. Green anoles dwell in moist, shady environments, such as trees and shrubs. They subsist on small insects such as crickets, roaches, moths, grubs, and spiders.

Green anoles are slight in build, with narrow heads and long, slender tails that can be twice as long as their bodies. The special padding on their feet enables them to climb, cling, and run on any surface. They range from six to eight inches long. Though, as their name implies, green anoles are usually bright green, their color can change to match their surroundings, varying between gray-brown, brown, and green. Male anoles have pink dewlaps that they extend when courting or to protect their territory. Linley Dolby was the production editor and proofreader for MySQL Cookbook. Colleen Gorman, Jeff Holcomb, Brian Sawyer, and Claire Cloutier provided quality control. John Bickelhaupt wrote the index.

Ellie Volckhausen designed the cover of this book, based on a series design by Edie Freedman. The cover image is a 19th-century engraving from the Dover Pictorial Archive. Emma Colby produced the cover layout with QuarkXPress 4.1 using Adobe's ITC Garamond font.

David Futato designed the interior layout. This book was converted to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The text font is Linotype Birka; the heading font is Adobe Myriad Condensed; and the code font is LucasFont's TheSans Mono Condensed. The tip and warning icons were drawn by Christopher Bing. This colophon was written by Linley Dolby.

Description

Table of Contents

Product Details

About the Author

Colophon

Recommended for You

Chapter 1 Using the mysql Client Program

Introduction

Setting Up a MySQL User Account

Creating a Database and a Sample Table

Starting and Terminating mysql

Specifying Connection Parameters by Using Option Files

Protecting Option Files

Mixing Command-Line and Option File Parameters

What to Do if mysql Cannot Be Found

Setting Environment Variables

Issuing Queries

Selecting a Database

Canceling a Partially Entered Query

Repeating and Editing Queries

Using Auto-Completion for Database and Table Names

Using SQL Variables in Queries

Telling mysql to Read Queries from a File

Telling mysql to Read Queries from Other Programs

Specifying Queries on the Command Line

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 Output

Producing XML Output

Suppressing Column Headings in Query Output

Numbering Query Output Lines

Making Long Output Lines More Readable

Controlling mysql’s Verbosity Level

Logging Interactive mysql Sessions

Creating mysql Scripts from Previously Executed Queries

Using mysql as a Calculator

Using mysql in Shell Scripts

Chapter 2 Writing MySQL-Based Programs

Introduction

Connecting to the MySQL Server, Selecting a Database, and Disconnecting

Checking for Errors

Writing Library Files

Issuing Queries and Retrieving Results

Moving Around Within a Result Set

Using Prepared Statements and Placeholders in Queries

Including Special Characters and NULL Values in Queries

Handling NULL Values in Result Sets

Writing an Object-Oriented MySQL Interface for PHP

Ways of Obtaining Connection Parameters

Conclusion and Words of Advice

Chapter 3 Record Selection Techniques

Introduction

Specifying Which Columns to Display

Avoiding Output Column Order Problems When Writing Programs

Giving Names to Output Columns

Using Column Aliases to Make Programs Easier to Write

Combining Columns to Construct Composite Values

Specifying Which Rows to Select

WHERE Clauses and Column Aliases

Displaying Comparisons to Find Out How Something Works

Reversing or Negating Query Conditions

Removing Duplicate Rows

Working with NULL Values

Negating a Condition on a Column That Contains NULL Values

Writing Comparisons Involving NULL in Programs

Mapping NULL Values to Other Values for Display

Sorting a Result Set

Selecting Records from the Beginning or End of a Result Set

Pulling a Section from the Middle of a Result Set

Choosing Appropriate LIMIT Values

Calculating LIMIT Values from Expressions

What to Do When LIMIT Requires the “Wrong” Sort Order

Selecting a Result Set into an Existing Table

Creating a Destination Table on the Fly from a Result Set

Moving Records Between Tables Safely

Creating Temporary Tables

Cloning a Table Exactly

Generating Unique Table Names

Chapter 4 Working with Strings

Introduction

Writing Strings That Include Quotes or Special Characters

Preserving Trailing Spaces in String Columns

Testing String Equality or Relative Ordering