MySQL Cookbook
- MySQL Cookbook
- Table of Contents
- Copyright
- Preface
- Chapter 1. Using the mysql Client Program
- Section 1.0. Introduction
- Recipe 1.1. Setting Up a MySQL User Account
- Recipe 1.2. Creating a Database and a Sample Table
- Recipe 1.3. Starting and Stopping mysql
- Recipe 1.4. Specifying Connection Parameters Using Option Files
- Recipe 1.5. Protecting Option Files from Other Users
- Recipe 1.6. Mixing Command-Line and Option File Parameters
- Recipe 1.7. What to Do if mysql Cannot Be Found
- Recipe 1.8. Issuing SQL Statements
- Recipe 1.9. Canceling a Partially Entered Statement
- Recipe 1.10. Repeating and Editing SQL Statements
- Recipe 1.11. Using Auto-Completion for Database and Table Names
- Recipe 1.12. Telling mysql to Read Statements from a File
- Recipe 1.13. Telling mysql to Read Statements from Other Programs
- Recipe 1.14. Entering an SQL One-Liner
- Recipe 1.15. Using Copy and Paste as a mysql Input Source
- Recipe 1.16. Preventing Query Output from Scrolling off the Screen
- Recipe 1.17. Sending Query Output to a File or to a Program
- Recipe 1.18. Selecting Tabular or Tab-Delimited Query Output Format
- Recipe 1.19. Specifying Arbitrary Output Column Delimiters
- Recipe 1.20. Producing HTML or XML Output
- Recipe 1.21. Suppressing Column Headings in Query Output
- Recipe 1.22. Making Long Output Lines More Readable
- Recipe 1.23. Controlling mysql s Verbosity Level
- Recipe 1.24. Logging Interactive mysql Sessions
- Recipe 1.25. Creating mysql Scripts from Previously Executed Statements
- Recipe 1.26. Using User-Defined Variables in SQL Statements
- Recipe 1.27. Numbering Query Output Lines
- Recipe 1.28. Using mysql as a Calculator
- Recipe 1.29. Using mysql in Shell Scripts
- Chapter 2. Writing MySQL-Based Programs
- Section 2.0. Introduction
- Recipe 2.1. Connecting, Selecting a Database, and Disconnecting
- Recipe 2.2. Checking for Errors
- Recipe 2.3. Writing Library Files
- Recipe 2.4. Issuing Statements and Retrieving Results
- Recipe 2.5. Handling Special Characters and NULL Values in Statements
- Recipe 2.6. Handling Special Characters in Identifiers
- Recipe 2.7. Identifying NULL Values in Result Sets
- Recipe 2.8. Techniques for Obtaining Connection Parameters
- Recipe 2.9. Conclusion and Words of Advice
- Chapter 3. Selecting Data from Tables
- Section 3.0. Introduction
- Recipe 3.1. Specifying Which Columns to Select
- Recipe 3.2. Specifying Which Rows to Select
- Recipe 3.3. Giving Better Names to Query Result Columns
- Recipe 3.4. Using Column Aliases to Make Programs Easier to Write
- Recipe 3.5. Combining Columns to Construct Composite Values
- Recipe 3.6. WHERE Clauses and Column Aliases
- Recipe 3.7. Debugging Comparison Expressions
- Recipe 3.8. Removing Duplicate Rows
- Recipe 3.9. Working with NULL Values
- Recipe 3.10. Writing Comparisons Involving NULL in Programs
- Recipe 3.11. Sorting a Result Set
- Recipe 3.12. Using Views to Simplify Table Access
- Recipe 3.13. Selecting Data from More Than One Table
- Recipe 3.14. Selecting Rows from the Beginning or End of a Result Set
- Recipe 3.15. Selecting Rows from the Middle of a Result Set
- Recipe 3.16. Choosing Appropriate LIMIT Values
- Recipe 3.17. What to Do When LIMIT Requires the Wrong Sort Order
- Recipe 3.18. Calculating LIMIT Values from Expressions
- Chapter 4. Table Management
- Section 4.0. Introduction
- Recipe 4.1. Cloning a Table
- Recipe 4.2. Saving a Query Result in a Table
- Recipe 4.3. Creating Temporary Tables
- Recipe 4.4. Checking or Changing a Table s Storage Engine
- Recipe 4.5. Generating Unique Table Names
- Chapter 5. Working with Strings
- Section 5.0. Introduction
- Recipe 5.1. String Properties
- Recipe 5.2. Choosing a String Data Type
- Recipe 5.3. Setting the Client Connection Character Set Properly
- Recipe 5.4. Writing String Literals
- Recipe 5.5. Checking a String s Character Set or Collation
- Recipe 5.6. Changing a String s Character Set or Collation
- Recipe 5.7. Converting the Lettercase of a String
- Recipe 5.8. Converting the Lettercase of a Stubborn String
- Recipe 5.9. Controlling Case Sensitivity in String Comparisons
- Recipe 5.10. Pattern Matching with SQL Patterns
- Recipe 5.11. Pattern Matching with Regular Expressions
- Recipe 5.12. Controlling Case Sensitivity in Pattern Matching
- Recipe 5.13. Breaking Apart or Combining Strings
- Recipe 5.14. Searching for Substrings
- Recipe 5.15. Using FULLTEXT Searches
- Recipe 5.16. Using a FULLTEXT Search with Short Words
- Recipe 5.17. Requiring or Excluding FULLTEXT Search Words
- Recipe 5.18. Performing Phrase Searches with a FULLTEXT Index
- Chapter 6. Working with Dates and Times
- Section 6.0. Introduction
- Recipe 6.1. Choosing a Temporal Data Type
- Recipe 6.2. Changing MySQL s Date Format
- Recipe 6.3. Setting the Client Time Zone
- Recipe 6.4. Determining the Current Date or Time
- Recipe 6.5. Using TIMESTAMP to Track Row Modification Times
- Recipe 6.6. Extracting Parts of Dates or Times
- Recipe 6.7. Synthesizing Dates or Times from Component Values
- Recipe 6.8. Converting Between Temporal Data Types and Basic Units
- Recipe 6.9. Calculating the Interval Between Two Dates or Times
- Recipe 6.10. Adding Date or Time Values
- Recipe 6.11. Calculating Ages
- Recipe 6.12. Shifting a Date-and-Time Value to a Different Time Zone
- Recipe 6.13. Finding the First Day, Last Day, or Length of a Month
- Recipe 6.14. Calculating Dates by Substring Replacement
- Recipe 6.15. Finding the Day of the Week for a Date
- Recipe 6.16. Finding Dates for Any Weekday of a Given Week
- Recipe 6.17. Performing Leap Year Calculations
- Recipe 6.18. Canonizing Not-Quite-ISO Date Strings
- Recipe 6.19. Treating Dates or Times as Numbers
- Recipe 6.20. Forcing MySQL to Treat Strings as Temporal Values
- Recipe 6.21. Selecting Rows Based on Their Temporal Characteristics
- Chapter 7. Sorting Query Results
- Section 7.0. Introduction
- Recipe 7.1. Using ORDER BY to Sort Query Results
- Recipe 7.2. Using Expressions for Sorting
- Recipe 7.3. Displaying One Set of Values While Sorting by Another
- Recipe 7.4. Controlling Case Sensitivity of String Sorts
- Recipe 7.5. Date-Based Sorting
- Recipe 7.6. Sorting by Calendar Day
- Recipe 7.7. Sorting by Day of Week
- Recipe 7.8. Sorting by Time of Day
- Recipe 7.9. Sorting Using Substrings of Column Values
- Recipe 7.10. Sorting by Fixed-Length Substrings
- Recipe 7.11. Sorting by Variable-Length Substrings
- Recipe 7.12. Sorting Hostnames in Domain Order
- Recipe 7.13. Sorting Dotted-Quad IP Values in Numeric Order
- Recipe 7.14. Floating Values to the Head or Tail of the Sort Order
- Recipe 7.15. Sorting in User-Defined Orders
- Recipe 7.16. Sorting ENUM Values
- Chapter 8. Generating Summaries
- Section 8.0. Introduction
- Recipe 8.1. Summarizing with COUNT ( )
- Recipe 8.2. Summarizing with MIN ( ) and MAX ( )
- Recipe 8.3. Summarizing with SUM ( ) and AVG ( )
- Recipe 8.4. Using DISTINCT to Eliminate Duplicates
- Recipe 8.5. Finding Values Associated with Minimum and Maximum Values
- Recipe 8.6. Controlling String Case Sensitivity for MIN ( ) and MAX ( )
- Recipe 8.7. Dividing a Summary into Subgroups
- Recipe 8.8. Summaries and NULL Values
- Recipe 8.9. Selecting Only Groups with Certain Characteristics
- Recipe 8.10. Using Counts to Determine Whether Values Are Unique
- Recipe 8.11. Grouping by Expression Results
- Recipe 8.12. Categorizing Noncategorical Data
- Recipe 8.13. Controlling Summary Display Order
- Recipe 8.14. Finding Smallest or Largest Summary Values
- Recipe 8.15. Date-Based Summaries
- Recipe 8.16. Working with Per-Group and Overall Summary Values Simultaneously
- Recipe 8.17. Generating a Report That Includes a Summary and a List
- Chapter 9. Obtaining and Using Metadata
- Section 9.0. Introduction
- Recipe 9.1. Obtaining the Number of Rows Affected by a Statement
- Recipe 9.2. Obtaining Result Set Metadata
- Recipe 9.3. Determining Whether a Statement Produced a Result Set
- Recipe 9.4. Using Metadata to Format Query Output
- Recipe 9.5. Listing or Checking Existence of Databases or Tables
- Recipe 9.6. Accessing Table Column Definitions
- Recipe 9.7. Getting ENUM and SET Column Information
- Recipe 9.8. Using Table Structure Information in Applications
- Recipe 9.9. Getting Server Metadata
- Recipe 9.10. Writing Applications That Adapt to the MySQL Server Version
- Recipe 9.11. Determining the Default Database
- Recipe 9.12. Monitoring the MySQL Server
- Recipe 9.13. Determining Which Storage Engines the Server Supports
- Chapter 10. Importing and Exporting Data
- Section 10.0. Introduction
- Recipe 10.1. Importing Data with LOAD DATA and mysqlimport
- Recipe 10.2. Specifying the Datafile Location
- Recipe 10.3. Specifying the Structure of the Datafile
- Recipe 10.4. Dealing with Quotes and Special Characters
- Recipe 10.5. Importing CSV Files
- Recipe 10.6. Reading Files from Different Operating Systems
- Recipe 10.7. Handling Duplicate Key Values
- Recipe 10.8. Obtaining Diagnostics About Bad Input Data
- Recipe 10.9. Skipping Datafile Lines
- Recipe 10.10. Specifying Input Column Order
- Recipe 10.11. Preprocessing Input Values Before Inserting Them
- Recipe 10.12. Ignoring Datafile Columns
- Recipe 10.13. Exporting Query Results from MySQL
- Recipe 10.14. Exporting Tables as Text Files
- Recipe 10.15. Exporting Table Contents or Definitions in SQL Format
- Recipe 10.16. Copying Tables or Databases to Another Server
- Recipe 10.17. Writing Your Own Export Programs
- Recipe 10.18. Converting Datafiles from One Format to Another
- Recipe 10.19. Extracting and Rearranging Datafile Columns
- Recipe 10.20. Using the SQL Mode to Control Bad Input Data Handling
- Recipe 10.21. Validating and Transforming Data
- Recipe 10.22. Using Pattern Matching to Validate Data
- Recipe 10.23. Using Patterns to Match Broad Content Types
- Recipe 10.24. Using Patterns to Match Numeric Values
- Recipe 10.25. Using Patterns to Match Dates or Times
- Recipe 10.26. Using Patterns to Match Email Addresses or URLs
- Recipe 10.27. Using Table Metadata to Validate Data
- Recipe 10.28. Using a Lookup Table to Validate Data
- Recipe 10.29. Converting Two-Digit Year Values to Four-Digit Form
- Recipe 10.30. Performing Validity Checking on Date or Time Subparts
- Recipe 10.31. Writing Date-Processing Utilities
- Recipe 10.32. Using Dates with Missing Components
- Recipe 10.33. Importing Non-ISO Date Values
- Recipe 10.34. Exporting Dates Using Non-ISO Formats
- Recipe 10.35. Importing and Exporting NULL Values
- Recipe 10.36. Guessing Table Structure from a Datafile
- Recipe 10.37. Exchanging Data Between MySQL and Microsoft Access
- Recipe 10.38. Exchanging Data Between MySQL and Microsoft Excel
- Recipe 10.39. Exporting Query Results as XML
- Recipe 10.40. Importing XML into MySQL
- Recipe 10.41. Epilogue
- Chapter 11. Generating and Using Sequences
- Section 11.0. Introduction
- Recipe 11.1. Creating a Sequence Column and Generating Sequence Values
- Recipe 11.2. Choosing the Data Type for a Sequence Column
- Recipe 11.3. The Effect of Row Deletions on Sequence Generation
- Recipe 11.4. Retrieving Sequence Values
- Recipe 11.5. Renumbering an Existing Sequence
- Recipe 11.6. Extending the Range of a Sequence Column
- Recipe 11.7. Reusing Values at the Top of a Sequence
- Recipe 11.8. Ensuring That Rows Are Renumbered in a Particular Order
- Recipe 11.9. Starting a Sequence at a Particular Value
- Recipe 11.10. Sequencing an Unsequenced Table
- Recipe 11.11. Using an AUTO_INCREMENT Column to Create Multiple Sequences
- Recipe 11.12. Managing Multiple Simultaneous AUTO_INCREMENT Values
- Recipe 11.13. Using AUTO_INCREMENT Values to Relate Tables
- Recipe 11.14. Using Sequence Generators as Counters
- Recipe 11.15. Generating Repeating Sequences
- Recipe 11.16. Numbering Query Output Rows Sequentially
- Chapter 12. Using Multiple Tables
- Section 12.0. Introduction
- Recipe 12.1. Finding Rows in One Table That Match Rows in Another
- Recipe 12.2. Finding Rows with No Match in Another Table
- Recipe 12.3. Comparing a Table to Itself
- Recipe 12.4. Producing Master-Detail Lists and Summaries
- Recipe 12.5. Enumerating a Many-to-Many Relationship
- Recipe 12.6. Finding Rows Containing Per-Group Minimum or Maximum Values
- Recipe 12.7. Computing Team Standings
- Recipe 12.8. Using a Join to Fill or Identify Holes in a List
- Recipe 12.9. Calculating Successive-Row Differences
- Recipe 12.10. Finding Cumulative Sums and Running Averages
- Recipe 12.11. Using a Join to Control Query Output Order
- Recipe 12.12. Combining Several Result Sets in a Single Query
- Recipe 12.13. Identifying and Removing Mismatched or Unattached Rows
- Recipe 12.14. Performing a Join Between Tables in Different Databases
- Recipe 12.15. Using Different MySQL Servers Simultaneously
- Recipe 12.16. Referring to Join Output Column Names in Programs
- Chapter 13. Statistical Techniques
- Section 13.0. Introduction
- Recipe 13.1. Calculating Descriptive Statistics
- Recipe 13.2. Per-Group Descriptive Statistics
- Recipe 13.3. Generating Frequency Distributions
- Recipe 13.4. Counting Missing Values
- Recipe 13.5. Calculating Linear Regressions or Correlation Coefficients
- Recipe 13.6. Generating Random Numbers
- Recipe 13.7. Randomizing a Set of Rows
- Recipe 13.8. Selecting Random Items from a Set of Rows
- Recipe 13.9. Assigning Ranks
- Chapter 14. Handling Duplicates
- Section 14.0. Introduction
- Recipe 14.1. Preventing Duplicates from Occurring in a Table
- Recipe 14.2. Dealing with Duplicates When Loading Rows into a Table
- Recipe 14.3. Counting and Identifying Duplicates
- Recipe 14.4. Eliminating Duplicates from a Table
- Recipe 14.5. Eliminating Duplicates from a Self-Join Result
- Chapter 15. Performing Transactions
- Section 15.0. Introduction
- Recipe 15.1. Choosing a Transactional Storage Engine
- Recipe 15.2. Performing Transactions Using SQL
- Recipe 15.3. Performing Transactions from Within Programs
- Recipe 15.4. Using Transactions in Perl Programs
- Recipe 15.5. Using Transactions in Ruby Programs
- Recipe 15.6. Using Transactions in PHP Programs
- Recipe 15.7. Using Transactions in Python Programs
- Recipe 15.8. Using Transactions in Java Programs
- Recipe 15.9. Using Alternatives to Transactions
- Chapter 16. Using Stored Routines, Triggers, and Events
- Section 16.0. Introduction
- Recipe 16.1. Creating Compound-Statement Objects
- Recipe 16.2. Using a Stored Function to Encapsulate a Calculation
- Recipe 16.3. Using a Stored Procedure to Return Multiple Values
- Recipe 16.4. Using a Trigger to Define Dynamic Default Column Values
- Recipe 16.5. Simulating TIMESTAMP Properties for Other Date and Time Types
- Recipe 16.6. Using a Trigger to Log Changes to a Table
- Recipe 16.7. Using Events to Schedule Database Actions
- Chapter 17. Introduction to MySQL on the Web
- Section 17.0. Introduction
- Recipe 17.1. Basic Principles of Web Page Generation
- Recipe 17.2. Using Apache to Run Web Scripts
- Recipe 17.3. Using Tomcat to Run Web Scripts
- Recipe 17.4. Encoding Special Characters in Web Output
- Chapter 18. Incorporating Query Results into Web Pages
- Section 18.0. Introduction
- Recipe 18.1. Displaying Query Results as Paragraph Text
- Recipe 18.2. Displaying Query Results as Lists
- Recipe 18.3. Displaying Query Results as Tables
- Recipe 18.4. Displaying Query Results as Hyperlinks
- Recipe 18.5. Creating a Navigation Index from Database Content
- Recipe 18.6. Storing Images or Other Binary Data
- Recipe 18.7. Retrieving Images or Other Binary Data
- Recipe 18.8. Serving Banner Ads
- Recipe 18.9. Serving Query Results for Download
- Recipe 18.10. Using a Template System to Generate Web Pages
- Chapter 19. Processing Web Input with MySQL
- Section 19.0. Introduction
- Recipe 19.1. Writing Scripts That Generate Web Forms
- Recipe 19.2. Creating Single-Pick Form Elements from Database Content
- Recipe 19.3. Creating Multiple-Pick Form Elements from Database Content
- Recipe 19.4. Loading a Database Record into a Form
- Recipe 19.5. Collecting Web Input
- Recipe 19.6. Validating Web Input
- Recipe 19.7. Storing Web Input in a Database
- Recipe 19.8. Processing File Uploads
- Recipe 19.9. Performing Searches and Presenting the Results
- Recipe 19.10. Generating Previous-Page and Next-Page Links
- Recipe 19.11. Generating Click to Sort Table Headings
- Recipe 19.12. Web Page Access Counting
- Recipe 19.13. Web Page Access Logging
- Recipe 19.14. Using MySQL for Apache Logging
- Chapter 20. Using MySQL-Based Web Session Management
- Section 20.0. Introduction
- Recipe 20.1. Using MySQL-Based Sessions in Perl Applications
- Recipe 20.2. Using MySQL-Based Storage in Ruby Applications
- Recipe 20.3. Using MySQL-Based Storage with the PHP Session Manager
- Recipe 20.4. Using MySQL for Session-Backing Store with Tomcat
- Appendix A. Obtaining MySQL Software
- Obtaining Sample Source Code and Data
- Obtaining MySQL and Related Software
- Appendix B. Executing Programs from the Command Line
- Setting Environment Variables
- Executing Programs
- Appendix C. JSP and Tomcat Primer
- Servlet and JavaServer Pages Overview
- Setting Up a Tomcat Server
- Tomcat s Directory Structure
- Restarting Applications Without Restarting Tomcat
- Web Application Structure
- Elements of JSP Pages
- Appendix D. References
- Recipe MySQL Resources
- Recipe Perl Resources
- Recipe Ruby Resources
- Recipe PHP Resources
- Recipe Python Resources
- Recipe Java Resources
- Recipe Other Resources
- Colophon
- Index
- SYMBOL
- A
- B
- C
- D
- E
- F
- G
- H
- I
- J
- K
- L
- M
- N
- O
- P
- Q
- R
- S
- T
- U
- V
- W
- X
- Y