MySQL Cookbook, 2nd Edition

Solutions & Examples for Database Developers and DBAs

Larger Cover

Solutions & Examples for Database Developers and DBAs

By Paul DuBois

Publisher: O'Reilly Media

Released: November 2006

Pages: 980

Along with MySQL's popularity has come a flood of questions about solving specific problems, and that's where this Cookbook is essential. Designed as a handy resource when you need quick solutions or techniques, the book offers dozens of short, focused pieces of code and hundreds of worked-out examples for programmers of all levels who don't have the time (or expertise) to solve MySQL problems from scratch.

The new edition covers MySQL 5.0 and its powerful new features, as well as the older but still widespread MySQL 4.1. One major emphasis of this book is how to use SQL to formulate queries for particular kinds of questions, using the mysql client program included in MySQL distributions. The other major emphasis is how to write programs that interact with the MySQL server through an API. You'll find plenty of examples using several language APIs in multiple scenarios and situations, including the use of Ruby to retrieve and format data. There are also many new examples for using Perl, PHP, Python, and Java as well.

Other recipes in the book teach you to:

Access data from multiple tables at the same time
Use SQL to select, sort, and summarize rows
Find matches or mismatches between rows in two tables
Determine intervals between dates or times, including age calculations
Store images into MySQL and retrieve them for display in web pages
Get LOAD DATA to read your data files properly or find which values in the file are invalid
Use strict mode to prevent entry of bad data into your database
Copy a table or a database to another server
Generate sequence numbers to use as unique row identifiers
Create database events that execute according to a schedule
And a lot more

MySQL Cookbook doesn't attempt to develop full-fledged, complex applications. Instead, it's intended to assist you in developing applications yourself by helping you get past problems that have you stumped.

Chapter 1 Using the mysql Client Program
1. Introduction
2. Setting Up a MySQL User Account
  1. Problem
  2. Solution
  3. Discussion
3. Creating a Database and a Sample Table
  1. Problem
  2. Solution
  3. Discussion
4. Starting and Stopping mysql
  1. Problem
  2. Solution
  3. Discussion
5. Specifying Connection Parameters Using Option Files
  1. Problem
  2. Solution
  3. Discussion
6. Protecting Option Files from Other Users
  1. Problem
  2. Solution
  3. Discussion
7. Mixing Command-Line and Option File Parameters
  1. Problem
  2. Solution
  3. Discussion
8. What to Do if mysql Cannot Be Found
  1. Problem
  2. Solution
  3. Discussion
9. Issuing SQL Statements
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
10. Canceling a Partially Entered Statement
  1. Problem
  2. Solution
  3. Discussion
11. Repeating and Editing SQL Statements
  1. Problem
  2. Solution
  3. Discussion
12. Using Auto-Completion for Database and Table Names
  1. Problem
  2. Solution
  3. Discussion
13. Telling mysql to Read Statements from a File
  1. Problem
  2. Solution
  3. Discussion
14. Telling mysql to Read Statements from Other Programs
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
15. Entering an SQL One-Liner
  1. Problem
  2. Solution
  3. Discussion
16. Using Copy and Paste as a mysql Input Source
  1. Problem
  2. Solution
  3. Discussion
17. Preventing Query Output from Scrolling off the Screen
  1. Problem
  2. Solution
  3. Discussion
18. Sending Query Output to a File or to a Program
  1. Problem
  2. Solution
  3. Discussion
19. Selecting Tabular or Tab-Delimited Query Output Format
  1. Problem
  2. Solution
  3. Discussion
20. Specifying Arbitrary Output Column Delimiters
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
21. Producing HTML or XML Output
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
22. Suppressing Column Headings in Query Output
  1. Problem
  2. Solution
  3. Discussion
23. Making Long Output Lines More Readable
  1. Problem
  2. Solution
  3. Discussion
24. Controlling mysql’s Verbosity Level
  1. Problem
  2. Solution
  3. Discussion
25. Logging Interactive mysql Sessions
  1. Problem
  2. Solution
  3. Discussion
26. Creating mysql Scripts from Previously Executed Statements
  1. Problem
  2. Solution
  3. Discussion
27. Using User-Defined Variables in SQL Statements
  1. Problem
  2. Solution
  3. Discussion
28. Numbering Query Output Lines
  1. Problem
  2. Solution
  3. Discussion
29. Using mysql as a Calculator
  1. Problem
  2. Solution
  3. Discussion
30. Using mysql in Shell Scripts
  1. Problem
  2. Solution
  3. Discussion
    1. Writing shell scripts under Unix
    2. Writing shell scripts under Windows
Chapter 2 Writing MySQL-Based Programs
1. Introduction
  1. Assumptions
  2. MySQL Client API Architecture
2. Connecting, Selecting a Database, and Disconnecting
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
    5. Java
3. Checking for Errors
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
    5. Java
4. Writing Library Files
  1. Problem
  2. Solution
  3. Discussion
    1. Choosing a library file installation location
    2. Setting library file access privileges
    3. Perl
    4. Ruby
    5. PHP
    6. Python
    7. Java
5. Issuing Statements and Retrieving Results
  1. Problem
  2. Solution
  3. Discussion
    1. SQL statement categories
    2. Perl
    3. Ruby
    4. PHP
    5. Python
    6. Java
6. Handling Special Characters and NULL Values in Statements
  1. Problem
  2. Solution
  3. Discussion
    1. Using placeholders
    2. Using a quoting function
    3. Perl
    4. Ruby
    5. PHP
    6. Python
    7. Java
7. Handling Special Characters in Identifiers
  1. Problem
  2. Solution
  3. Discussion
8. Identifying NULL Values in Result Sets
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
    5. Java
9. Techniques for Obtaining Connection Parameters
  1. Problem
  2. Solution
  3. Discussion
    1. Getting parameters from the command line
    2. Getting parameters from option files
10. Conclusion and Words of Advice
Chapter 3 Selecting Data from Tables
1. Introduction
2. Specifying Which Columns to Select
  1. Problem
  2. Solution
  3. Discussion
3. Specifying Which Rows to Select
  1. Problem
  2. Solution
  3. Discussion
4. Giving Better Names to Query Result Columns
  1. Problem
  2. Solution
  3. Discussion
5. Using Column Aliases to Make Programs Easier to Write
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
6. Combining Columns to Construct Composite Values
  1. Problem
  2. Solution
  3. Discussion
7. WHERE Clauses and Column Aliases
  1. Problem
  2. Solution
  3. Discussion
8. Debugging Comparison Expressions
  1. Problem
  2. Solution
  3. Discussion
9. Removing Duplicate Rows
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
10. Working with NULL Values
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
11. Writing Comparisons Involving NULL in Programs
  1. Problem
  2. Solution
  3. Discussion
12. Sorting a Result Set
  1. Problem
  2. Solution
  3. Discussion
13. Using Views to Simplify Table Access
  1. Problem
  2. Solution
  3. Discussion
14. Selecting Data from More Than One Table
  1. Problem
  2. Solution
  3. Discussion
15. Selecting Rows from the Beginning or End of a Result Set
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
16. Selecting Rows from the Middle of a Result Set
  1. Problem
  2. Solution
  3. Discussion
17. Choosing Appropriate LIMIT Values
  1. Problem
  2. Solution
  3. Discussion
18. What to Do When LIMIT Requires the Wrong Sort Order
  1. Problem
  2. Solution
  3. Discussion
19. Calculating LIMIT Values from Expressions
  1. Problem
  2. Solution
  3. Discussion
Chapter 4 Table Management
1. Introduction
2. Cloning a Table
  1. Problem
  2. Solution
  3. Discussion
3. Saving a Query Result in a Table
  1. Problem
  2. Solution
  3. Discussion
4. Creating Temporary Tables
  1. Problem
  2. Solution
  3. Discussion
5. Checking or Changing a Table’s Storage Engine
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
6. Generating Unique Table Names
  1. Problem
  2. Solution
  3. Discussion
Chapter 5 Working with Strings
1. Introduction
2. String Properties
3. Choosing a String Data Type
  1. Problem
  2. Solution
  3. Discussion
4. Setting the Client Connection Character Set Properly
  1. Problem
  2. Solution
  3. Discussion
5. Writing String Literals
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
6. Checking a String’s Character Set or Collation
  1. Problem
  2. Solution
  3. Discussion
7. Changing a String’s Character Set or Collation
  1. Problem
  2. Solution
  3. Discussion
8. Converting the Lettercase of a String
  1. Problem
  2. Solution
  3. Discussion
9. Converting the Lettercase of a Stubborn String
  1. Problem
  2. Solution
  3. Discussion
10. Controlling Case Sensitivity in String Comparisons
  1. Problem
  2. Solution
  3. Discussion
11. Pattern Matching with SQL Patterns
  1. Problem
  2. Solution
  3. Discussion
12. Pattern Matching with Regular Expressions
  1. Problem
  2. Solution
  3. Discussion
13. Controlling Case Sensitivity in Pattern Matching
  1. Problem
  2. Solution
  3. Discussion
14. Breaking Apart or Combining Strings
  1. Problem
  2. Solution
  3. Discussion
15. Searching for Substrings
  1. Problem
  2. Solution
  3. Discussion
16. Using FULLTEXT Searches
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
17. Using a FULLTEXT Search with Short Words
  1. Problem
  2. Solution
  3. Discussion
18. Requiring or Excluding FULLTEXT Search Words
  1. Problem
  2. Solution
  3. Discussion
19. Performing Phrase Searches with a FULLTEXT Index
  1. Problem
  2. Solution
  3. Discussion
Chapter 6 Working with Dates and Times
1. Introduction
2. Choosing a Temporal Data Type
  1. Problem
  2. Solution
  3. Discussion
3. Changing MySQL’s Date Format
  1. Problem
  2. Solution
  3. Discussion
4. Setting the Client Time Zone
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
5. Determining the Current Date or Time
  1. Problem
  2. Solution
  3. Discussion
6. Using TIMESTAMP to Track Row Modification Times
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
7. Extracting Parts of Dates or Times
  1. Problem
  2. Solution
  3. Discussion
    1. Decomposing dates or times using component-extraction functions
    2. Decomposing dates or times using formatting functions
    3. Decomposing dates or times using string functions
8. Synthesizing Dates or Times from Component Values
  1. Problem
  2. Solution
  3. Discussion
9. Converting Between Temporal Data Types and Basic Units
  1. Problem
  2. Solution
  3. Discussion
    1. Converting between times and seconds
    2. Converting between dates and days
    3. Converting between date-and-time values and seconds
10. Calculating the Interval Between Two Dates or Times
  1. Problem
  2. Solution
  3. Discussion
    1. Calculating intervals with temporal-difference functions
    2. Calculating intervals using basic units
    3. Time interval calculation using basic units
    4. Date or date-and-time interval calculation using basic units
11. Adding Date or Time Values
  1. Problem
  2. Solution
  3. Discussion
    1. Adding temporal values using temporal-addition functions or operators
    2. Adding temporal values using basic units
12. Calculating Ages
  1. Problem
  2. Solution
  3. Discussion
13. Shifting a Date-and-Time Value to a Different Time Zone
  1. Problem
  2. Solution
  3. Discussion
14. Finding the First Day, Last Day, or Length of a Month
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
15. Calculating Dates by Substring Replacement
  1. Problem
  2. Solution
  3. Discussion
16. Finding the Day of the Week for a Date
  1. Problem
  2. Solution
  3. Discussion
17. Finding Dates for Any Weekday of a Given Week
  1. Problem
  2. Solution
  3. Discussion
18. Performing Leap Year Calculations
  1. Problem
  2. Solution
  3. Discussion
    1. Determining whether a date occurs in a leap year
    2. Using leap year tests for year-length calculations
    3. Using leap year tests for month-length calculations
19. Canonizing Not-Quite-ISO Date Strings
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
20. Treating Dates or Times as Numbers
  1. Problem
  2. Solution
  3. Discussion
21. Forcing MySQL to Treat Strings as Temporal Values
  1. Problem
  2. Solution
  3. Discussion
22. Selecting Rows Based on Their Temporal Characteristics
  1. Problem
  2. Solution
  3. Discussion
    1. Comparing dates to one another
    2. Comparing times to one another
    3. Comparing dates to calendar days
Chapter 7 Sorting Query Results
1. Introduction
2. Using ORDER BY to Sort Query Results
  1. Problem
  2. Solution
  3. Discussion
3. Using Expressions for Sorting
  1. Problem
  2. Solution
  3. Discussion
4. Displaying One Set of Values While Sorting by Another
  1. Problem
  2. Solution
  3. Discussion
5. Controlling Case Sensitivity of String Sorts
  1. Problem
  2. Solution
  3. Discussion
6. Date-Based Sorting
  1. Problem
  2. Solution
  3. Discussion
7. Sorting by Calendar Day
  1. Problem
  2. Solution
  3. Discussion
8. Sorting by Day of Week
  1. Problem
  2. Solution
  3. Discussion
9. Sorting by Time of Day
  1. Problem
  2. Solution
  3. Discussion
10. Sorting Using Substrings of Column Values
  1. Problem
  2. Solution
  3. Discussion
11. Sorting by Fixed-Length Substrings
  1. Problem
  2. Solution
  3. Discussion
12. Sorting by Variable-Length Substrings
  1. Problem
  2. Solution
  3. Discussion
13. Sorting Hostnames in Domain Order
  1. Problem
  2. Solution
  3. Discussion
14. Sorting Dotted-Quad IP Values in Numeric Order
  1. Problem
  2. Solution
  3. Discussion
15. Floating Values to the Head or Tail of the Sort Order
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
16. Sorting in User-Defined Orders
  1. Problem
  2. Solution
  3. Discussion
17. Sorting ENUM Values
  1. Problem
  2. Solution
  3. Discussion
Chapter 8 Generating Summaries
1. Introduction
2. Summarizing with COUNT⁠(⁠ ⁠ ⁠)
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
3. Summarizing with MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠)
  1. Problem
  2. Solution
  3. Discussion
4. Summarizing with SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠)
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
5. Using DISTINCT to Eliminate Duplicates
  1. Problem
  2. Solution
  3. Discussion
6. Finding Values Associated with Minimum and Maximum Values
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
7. Controlling String Case Sensitivity for MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠)
  1. Problem
  2. Solution
  3. Discussion
8. Dividing a Summary into Subgroups
  1. Problem
  2. Solution
  3. Discussion
9. Summaries and NULL Values
  1. Problem
  2. Solution
  3. Discussion
10. Selecting Only Groups with Certain Characteristics
  1. Problem
  2. Solution
  3. Discussion
11. Using Counts to Determine Whether Values Are Unique
  1. Problem
  2. Solution
  3. Discussion
12. Grouping by Expression Results
  1. Problem
  2. Solution
  3. Discussion
13. Categorizing Noncategorical Data
  1. Problem
  2. Solution
  3. Discussion
14. Controlling Summary Display Order
  1. Problem
  2. Solution
  3. Discussion
15. Finding Smallest or Largest Summary Values
  1. Problem
  2. Solution
  3. Discussion
16. Date-Based Summaries
  1. Problem
  2. Solution
  3. Discussion
17. Working with Per-Group and Overall Summary Values Simultaneously
  1. Problem
  2. Solution
  3. Discussion
18. Generating a Report That Includes a Summary and a List
  1. Problem
  2. Solution
  3. Discussion
Chapter 9 Obtaining and Using Metadata
1. Introduction
2. Obtaining the Number of Rows Affected by a Statement
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
    5. Java
3. Obtaining Result Set Metadata
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
    5. Java
4. Determining Whether a Statement Produced a Result Set
  1. Problem
  2. Solution
  3. Discussion
5. Using Metadata to Format Query Output
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
6. Listing or Checking Existence of Databases or Tables
  1. Problem
  2. Solution
  3. Discussion
7. Accessing Table Column Definitions
  1. Problem
  2. Solution
  3. Discussion
    1. Using INFORMATION_SCHEMA to get table structure
    2. Using SHOW COLUMNS to get table structure
    3. Using CREATE TABLE to get table structure
8. Getting ENUM and SET Column Information
  1. Problem
  2. Solution
  3. Discussion
9. Using Table Structure Information in Applications
  1. Problem
  2. Solution
  3. Discussion
    1. Displaying column lists
    2. Interactive record editing
    3. Mapping column definitions onto web page elements
    4. Adding elements to ENUM or SET column definitions
    5. Selecting all except certain columns
10. Getting Server Metadata
  1. Problem
  2. Solution
  3. Discussion
11. Writing Applications That Adapt to the MySQL Server Version
  1. Problem
  2. Solution
  3. Discussion
12. Determining the Default Database
  1. Problem
  2. Solution
  3. Discussion
13. Monitoring the MySQL Server
  1. Problem
  2. Solution
  3. Discussion
14. Determining Which Storage Engines the Server Supports
  1. Problem
  2. Solution
  3. Discussion
Chapter 10 Importing and Exporting Data
1. Introduction
  1. General Import and Export Issues
  2. File Formats
  3. Notes on Invoking Shell Commands
2. Importing Data with LOAD DATA and mysqlimport
  1. Problem
  2. Solution
  3. Discussion
3. Specifying the Datafile Location
  1. Problem
  2. Solution
  3. Discussion
4. Specifying the Structure of the Datafile
  1. Problem
  2. Solution
  3. Discussion
5. Dealing with Quotes and Special Characters
  1. Problem
  2. Solution
  3. Discussion
6. Importing CSV Files
  1. Problem
  2. Solution
  3. Discussion
7. Reading Files from Different Operating Systems
  1. Problem
  2. Solution
  3. Discussion
8. Handling Duplicate Key Values
  1. Problem
  2. Solution
  3. Discussion
9. Obtaining Diagnostics About Bad Input Data
  1. Problem
  2. Solution
  3. Discussion
10. Skipping Datafile Lines
  1. Problem
  2. Solution
  3. Discussion
11. Specifying Input Column Order
  1. Problem
  2. Solution
  3. Discussion
12. Preprocessing Input Values Before Inserting Them
  1. Problem
  2. Solution
  3. Discussion
13. Ignoring Datafile Columns
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
14. Exporting Query Results from MySQL
  1. Problem
  2. Solution
  3. Discussion
    1. Exporting with the SELECT ... INTO OUTFILE statement
    2. Using the mysql client to export data
  4. See Also
15. Exporting Tables as Text Files
  1. Problem
  2. Solution
  3. Discussion
16. Exporting Table Contents or Definitions in SQL Format
  1. Problem
  2. Solution
  3. Discussion
17. Copying Tables or Databases to Another Server
  1. Problem
  2. Solution
  3. Discussion
18. Writing Your Own Export Programs
  1. Problem
  2. Solution
  3. Discussion
19. Converting Datafiles from One Format to Another
  1. Problem
  2. Solution
  3. Discussion
20. Extracting and Rearranging Datafile Columns
  1. Problem
  2. Solution
  3. Discussion
21. Using the SQL Mode to Control Bad Input Data Handling
  1. Problem
  2. Solution
  3. Discussion
22. Validating and Transforming Data
  1. Problem
  2. Solution
  3. Discussion
    1. Writing an input-processing loop
    2. Putting common tests in libraries
23. Using Pattern Matching to Validate Data
  1. Problem
  2. Solution
  3. Discussion
24. Using Patterns to Match Broad Content Types
  1. Problem
  2. Solution
  3. Discussion
25. Using Patterns to Match Numeric Values
  1. Problem
  2. Solution
  3. Discussion
26. Using Patterns to Match Dates or Times
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
27. Using Patterns to Match Email Addresses or URLs
  1. Problem
  2. Solution
  3. Discussion
28. Using Table Metadata to Validate Data
  1. Problem
  2. Solution
  3. Discussion
29. Using a Lookup Table to Validate Data
  1. Problem
  2. Solution
  3. Discussion
    1. Issue individual statements
    2. Construct a hash from the entire lookup table
    3. Use a hash as a cache of already-seen lookup values
30. Converting Two-Digit Year Values to Four-Digit Form
  1. Problem
  2. Solution
  3. Discussion
31. Performing Validity Checking on Date or Time Subparts
  1. Problem
  2. Solution
  3. Discussion
32. Writing Date-Processing Utilities
  1. Problem
  2. Solution
  3. Discussion
33. Using Dates with Missing Components
  1. Problem
  2. Solution
  3. Discussion
34. Importing Non-ISO Date Values
  1. Problem
  2. Solution
  3. Discussion
35. Exporting Dates Using Non-ISO Formats
  1. Problem
  2. Solution
  3. Discussion
36. Importing and Exporting NULL Values
  1. Problem
  2. Solution
  3. Discussion
37. Guessing Table Structure from a Datafile
  1. Problem
  2. Solution
  3. Discussion
38. Exchanging Data Between MySQL and Microsoft Access
  1. Problem
  2. Solution
  3. Discussion
39. Exchanging Data Between MySQL and Microsoft Excel
  1. Problem
  2. Solution
  3. Discussion
40. Exporting Query Results as XML
  1. Problem
  2. Solution
  3. Discussion
41. Importing XML into MySQL
  1. Problem
  2. Solution
  3. Discussion
42. Epilogue
Chapter 11 Generating and Using Sequences
1. Introduction
2. Creating a Sequence Column and Generating Sequence Values
  1. Problem
  2. Solution
  3. Discussion
3. Choosing the Data Type for a Sequence Column
  1. Problem
  2. Solution
  3. Discussion
4. The Effect of Row Deletions on Sequence Generation
  1. Problem
  2. Solution
  3. Discussion
5. Retrieving Sequence Values
  1. Problem
  2. Solution
  3. Discussion
    1. Using LAST_INSERT_ID⁠(⁠ ⁠ ⁠) to obtain AUTO_INCREMENT values
    2. Using API-specific methods to obtain AUTO_INCREMENT values
    3. Server-side and client-side sequence value retrieval compared
6. Renumbering an Existing Sequence
  1. Problem
  2. Solution
  3. Discussion
7. Extending the Range of a Sequence Column
  1. Problem
  2. Solution
  3. Discussion
8. Reusing Values at the Top of a Sequence
  1. Problem
  2. Solution
  3. Discussion
9. Ensuring That Rows Are Renumbered in a Particular Order
  1. Problem
  2. Solution
  3. Discussion
10. Starting a Sequence at a Particular Value
  1. Problem
  2. Solution
  3. Discussion
11. Sequencing an Unsequenced Table
  1. Problem
  2. Solution
  3. Discussion
12. Using an AUTO_INCREMENT Column to Create Multiple Sequences
  1. Problem
  2. Solution
  3. Discussion
13. Managing Multiple Simultaneous AUTO_INCREMENT Values
  1. Problem
  2. Solution
  3. Discussion
14. Using AUTO_INCREMENT Values to Relate Tables
  1. Problem
  2. Solution
  3. Discussion
15. Using Sequence Generators as Counters
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
16. Generating Repeating Sequences
  1. Problem
  2. Solution
  3. Discussion
17. Numbering Query Output Rows Sequentially
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
Chapter 12 Using Multiple Tables
1. Introduction
2. Finding Rows in One Table That Match Rows in Another
  1. Problem
  2. Solution
  3. Discussion
3. Finding Rows with No Match in Another Table
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
4. Comparing a Table to Itself
  1. Problem
  2. Solution
  3. Discussion
5. Producing Master-Detail Lists and Summaries
  1. Problem
  2. Solution
  3. Discussion
6. Enumerating a Many-to-Many Relationship
  1. Problem
  2. Solution
  3. Discussion
7. Finding Rows Containing Per-Group Minimum or Maximum Values
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
8. Computing Team Standings
  1. Problem
  2. Solution
  3. Discussion
9. Using a Join to Fill or Identify Holes in a List
  1. Problem
  2. Solution
  3. Discussion
10. Calculating Successive-Row Differences
  1. Problem
  2. Solution
  3. Discussion
11. Finding Cumulative Sums and Running Averages
  1. Problem
  2. Solution
  3. Discussion
12. Using a Join to Control Query Output Order
  1. Problem
  2. Solution
  3. Discussion
13. Combining Several Result Sets in a Single Query
  1. Problem
  2. Solution
  3. Discussion
14. Identifying and Removing Mismatched or Unattached Rows
  1. Problem
  2. Solution
  3. Discussion
15. Performing a Join Between Tables in Different Databases
  1. Problem
  2. Solution
  3. Discussion
16. Using Different MySQL Servers Simultaneously
  1. Problem
  2. Solution
  3. Discussion
17. Referring to Join Output Column Names in Programs
  1. Problem
  2. Solution
  3. Discussion
Chapter 13 Statistical Techniques
1. Introduction
2. Calculating Descriptive Statistics
  1. Problem
  2. Solution
  3. Discussion
3. Per-Group Descriptive Statistics
  1. Problem
  2. Solution
  3. Discussion
4. Generating Frequency Distributions
  1. Problem
  2. Solution
  3. Discussion
5. Counting Missing Values
  1. Problem
  2. Solution
  3. Discussion
6. Calculating Linear Regressions or Correlation Coefficients
  1. Problem
  2. Solution
  3. Discussion
7. Generating Random Numbers
  1. Problem
  2. Solution
  3. Discussion
8. Randomizing a Set of Rows
  1. Problem
  2. Solution
  3. Discussion
9. Selecting Random Items from a Set of Rows
  1. Problem
  2. Solution
  3. Discussion
10. Assigning Ranks
  1. Problem
  2. Solution
  3. Discussion
Chapter 14 Handling Duplicates
1. Introduction
2. Preventing Duplicates from Occurring in a Table
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
3. Dealing with Duplicates When Loading Rows into a Table
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
4. Counting and Identifying Duplicates
  1. Problem
  2. Solution
  3. Discussion
5. Eliminating Duplicates from a Table
  1. Problem
  2. Solution
  3. Discussion
    1. Removing duplicates using table replacement
    2. Removing duplicates by adding an index
    3. Removing duplicates of a particular row
6. Eliminating Duplicates from a Self-Join Result
  1. Problem
  2. Solution
  3. Discussion
Chapter 15 Performing Transactions
1. Introduction
2. Choosing a Transactional Storage Engine
  1. Problem
  2. Solution
  3. Discussion
3. Performing Transactions Using SQL
  1. Problem
  2. Solution
  3. Discussion
4. Performing Transactions from Within Programs
  1. Problem
  2. Solution
  3. Discussion
5. Using Transactions in Perl Programs
  1. Problem
  2. Solution
  3. Discussion
6. Using Transactions in Ruby Programs
  1. Problem
  2. Solution
  3. Discussion
7. Using Transactions in PHP Programs
  1. Problem
  2. Solution
  3. Discussion
8. Using Transactions in Python Programs
  1. Problem
  2. Solution
  3. Discussion
9. Using Transactions in Java Programs
  1. Problem
  2. Solution
  3. Discussion
10. Using Alternatives to Transactions
  1. Problem
  2. Solution
  3. Discussion
    1. Grouping statements using locks
    2. Rewriting statements to avoid transactions
Chapter 16 Using Stored Routines, Triggers, and Events
1. Introduction
2. Creating Compound-Statement Objects
  1. Problem
  2. Solution
  3. Discussion
3. Using a Stored Function to Encapsulate a Calculation
  1. Problem
  2. Solution
  3. Discussion
4. Using a Stored Procedure to Return Multiple Values
  1. Problem
  2. Solution
  3. Discussion
5. Using a Trigger to Define Dynamic Default Column Values
  1. Problem
  2. Solution
  3. Discussion
6. Simulating TIMESTAMP Properties for Other Date and Time Types
  1. Problem
  2. Solution
  3. Discussion
7. Using a Trigger to Log Changes to a Table
  1. Problem
  2. Solution
  3. Discussion
8. Using Events to Schedule Database Actions
  1. Problem
  2. Solution
  3. Discussion
Chapter 17 Introduction to MySQL on the Web
1. Introduction
2. Basic Principles of Web Page Generation
  1. Problem
  2. Solution
  3. Discussion
3. Using Apache to Run Web Scripts
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
4. Using Tomcat to Run Web Scripts
  1. Problem
  2. Solution
  3. Discussion
    1. Installing the mcb application
    2. Installing the JDBC driver
    3. Installing the JSTL distribution
    4. Writing JSP pages with JSTL
    5. Writing a MySQL script using JSP and JSTL
5. Encoding Special Characters in Web Output
  1. Problem
  2. Solution
  3. Discussion
    1. General encoding principles
    2. Encoding special characters using web APIs
Chapter 18 Incorporating Query Results into Web Pages
1. Introduction
2. Displaying Query Results as Paragraph Text
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
3. Displaying Query Results as Lists
  1. Problem
  2. Solution
  3. Discussion
    1. Ordered lists
    2. Unordered lists
    3. Definition lists
    4. Unmarked lists
    5. Nested lists
  4. See Also
4. Displaying Query Results as Tables
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
5. Displaying Query Results as Hyperlinks
  1. Problem
  2. Solution
  3. Discussion
6. Creating a Navigation Index from Database Content
  1. Problem
  2. Solution
  3. Discussion
    1. Creating a single-page navigation index
    2. Creating a multiple-page navigation index
  4. See Also
7. Storing Images or Other Binary Data
  1. Problem
  2. Solution
  3. Discussion
    1. Storing images with LOAD_FILE⁠(⁠ ⁠ ⁠)
    2. Storing images using a script
  4. See Also
8. Retrieving Images or Other Binary Data
  1. Problem
  2. Solution
  3. Discussion
9. Serving Banner Ads
  1. Problem
  2. Solution
  3. Discussion
10. Serving Query Results for Download
  1. Problem
  2. Solution
  3. Discussion
11. Using a Template System to Generate Web Pages
  1. Problem
  2. Solution
  3. Discussion
    1. Using page template for web page generation in Ruby
    2. Using Smarty for web page generation in PHP
Chapter 19 Processing Web Input with MySQL
1. Introduction
2. Writing Scripts That Generate Web Forms
  1. Problem
  2. Solution
  3. Discussion
    1. Perl
    2. Ruby
    3. PHP
    4. Python
    5. Java
  4. See Also
3. Creating Single-Pick Form Elements from Database Content
  1. Problem
  2. Solution
  3. Discussion
4. Creating Multiple-Pick Form Elements from Database Content
  1. Problem
  2. Solution
  3. Discussion
5. Loading a Database Record into a Form
  1. Problem
  2. Solution
  3. Discussion
6. Collecting Web Input
  1. Problem
  2. Solution
  3. Discussion
    1. Web input extraction conventions
7. Validating Web Input
  1. Problem
  2. Solution
  3. Discussion
8. Storing Web Input in a Database
  1. Problem
  2. Solution
  3. Discussion
  4. See Also
9. Processing File Uploads
  1. Problem
  2. Solution
  3. Discussion
    1. Uploads in Perl
    2. Uploads in PHP
    3. Uploads in Python
10. Performing Searches and Presenting the Results
  1. Problem
  2. Solution
  3. Discussion
11. Generating Previous-Page and Next-Page Links
  1. Problem
  2. Solution
  3. Discussion
    1. Paged displays with previous-page and next-page links
    2. Paged displays with links to each page
12. Generating Click to Sort Table Headings
  1. Problem
  2. Solution
  3. Discussion
13. Web Page Access Counting
  1. Problem
  2. Solution
  3. Discussion
14. Web Page Access Logging
  1. Problem
  2. Solution
  3. Discussion
15. Using MySQL for Apache Logging
  1. Problem
  2. Solution
  3. Discussion
    1. Setting up database logging
    2. Analyzing the logfile
    3. Other logging issues
Chapter 20 Using MySQL-Based Web Session Management
1. Introduction
  1. Session Management Issues
2. Using MySQL-Based Sessions in Perl Applications
  1. Problem
  2. Solution
  3. Discussion
    1. Installing Apache::Session
    2. The Apache::Session interface
    3. A sample application
    4. Session expiration
3. Using MySQL-Based Storage in Ruby Applications
  1. Problem
  2. Solution
  3. Discussion
4. Using MySQL-Based Storage with the PHP Session Manager
  1. Problem
  2. Solution
  3. Discussion
    1. The PHP session management interface
    2. Specifying a user-defined storage module
5. Using MySQL for Session-Backing Store with Tomcat
  1. Problem
  2. Solution
  3. Discussion
    1. The Servlet and JSP Session Interface
    2. A sample JSP session application
    3. Telling Tomcat to save session records in MySQL
    4. Session expiration in Tomcat
    5. Session tracking in Tomcat

Appendix Obtaining MySQL Software
1. Obtaining Sample Source Code and Data
2. Obtaining MySQL and Related Software
  1. MySQL
  2. Perl Support
  3. Ruby Support
  4. PHP Support
  5. Python Support
  6. Java Support
  7. Web Servers
Appendix Executing Programs from the Command Line
1. Setting Environment Variables
  1. Setting the PATH Variable on Unix
  2. Setting the PATH Variable on Windows
2. Executing Programs
  1. Executing Perl, Ruby, PHP, or Python Scripts
  2. Compiling and Executing Java Programs
Appendix JSP and Tomcat Primer
1. Servlet and JavaServer Pages Overview
  1. JSP Pages: An Alternative to Servlets
  2. Custom Actions and Tag Libraries
2. Setting Up a Tomcat Server
  1. Installing a Tomcat Distribution
  2. Starting and Stopping Tomcat
3. Tomcat’s Directory Structure
  1. Application Directories
  2. Configuration and Control Directories
  3. Class Directories
  4. Operational Directories
4. Restarting Applications Without Restarting Tomcat
5. Web Application Structure
6. Elements of JSP Pages
  1. Scripting Elements
  2. JSP Directives
  3. Action Elements
  4. Using a Tag Library
  5. Implicit JSP Objects
  6. Levels of Scope in JSP Pages
Appendix References
1. MySQL Resources
2. Perl Resources
3. Ruby Resources
4. PHP Resources
5. Python Resources
6. Java Resources
7. Other Resources
Colophon

Title:

MySQL Cookbook, 2nd Edition

By:

Paul DuBois

Publisher:

O'Reilly Media

Formats:

Print
Ebook
Safari Books Online

Print:

November 2006

Ebook:

June 2009

Pages:

980

Print ISBN:

978-0-596-52708-2

| ISBN 10:

0-596-52708-X

Ebook ISBN:

978-0-596-15862-0

| ISBN 10:

0-596-15862-9

Description

Table of Contents

Product Details

About the Author

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 Stopping mysql

Specifying Connection Parameters Using Option Files

Protecting Option Files from Other Users

Mixing Command-Line and Option File Parameters

What to Do if mysql Cannot Be Found

Issuing SQL Statements

Canceling a Partially Entered Statement

Repeating and Editing SQL Statements

Using Auto-Completion for Database and Table Names

Telling mysql to Read Statements from a File

Telling mysql to Read Statements from Other Programs

Entering an SQL One-Liner

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 or XML Output

Suppressing Column Headings in Query Output

Making Long Output Lines More Readable

Controlling mysql’s Verbosity Level

Logging Interactive mysql Sessions

Creating mysql Scripts from Previously Executed Statements

Using User-Defined Variables in SQL Statements

Numbering Query Output Lines

Using mysql as a Calculator

Using mysql in Shell Scripts

Chapter 2 Writing MySQL-Based Programs

Introduction

Connecting, Selecting a Database, and Disconnecting

Checking for Errors

Writing Library Files

Issuing 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 to Select

Specifying Which Rows to Select

Giving Better Names to Query Result Columns

Using Column Aliases to Make Programs Easier to Write

Combining Columns to Construct Composite Values

WHERE Clauses and Column Aliases

Debugging Comparison Expressions

Removing Duplicate Rows

Working with NULL Values

Writing Comparisons Involving NULL in Programs

Sorting a Result Set

Using Views to Simplify Table Access

Selecting Data from More Than One Table

Selecting Rows from the Beginning or End of a Result Set

Selecting Rows from the Middle of a Result Set

Choosing Appropriate LIMIT Values

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

Checking or Changing a Table’s Storage Engine

Generating Unique Table Names

Chapter 5 Working with Strings

Introduction

String Properties

Choosing a String Data Type

Setting the Client Connection Character Set Properly

Writing String Literals

Checking a String’s Character Set or Collation

Changing a String’s Character Set or Collation

Converting the Lettercase of a String

Converting the Lettercase of a Stubborn String

Controlling Case Sensitivity in String Comparisons