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