MySQL Cookbook by Paul DuBois Publisher: O'Reilly Pub Date: October 2002 ISBN: 0-596-00145-2 Pages: 1022
Copyright Preface MySQL APIs Used in This Book Who This Book Is For What's in This Book Platform Notes Conventions Used in This Book The Companion Web Site Comments and Questions Additional Resources Acknowledgments Chapter 1. Using the mysql Client Program Section 1.1. Introduction Section 1.2. Setting Up a MySQL User Account Section 1.3. Creating a Database and a Sample Table Section 1.4. Starting and Terminating mysql Section 1.5. Specifying Connection Parameters by Using Option Files Section 1.6. Protecting Option Files Section 1.7. Mixing Command-Line and Option File Parameters Section 1.8. What to Do if mysql Cannot Be Found Section 1.9. Setting Environment Variables Section 1.10. Issuing Queries Section 1.11. Selecting a Database Section 1.12. Canceling a Partially Entered Query Section 1.13. Repeating and Editing Queries Section 1.14. Using Auto-Completion for Database and Table Names Section 1.15. Using SQL Variables in Queries Section 1.16. Telling mysql to Read Queries from a File Section 1.17. Telling mysql to Read Queries from Other Programs Section 1.18. Specifying Queries on the Command Line Section 1.19. Using Copy and Paste as a mysql Input Source Section 1.20. Preventing Query Output from Scrolling off the Screen Section 1.21. Sending Query Output to a File or to a Program Section 1.22. Selecting Tabular or Tab-Delimited Query Output Format Section 1.23. Specifying Arbitrary Output Column Delimiters Section 1.24. Producing HTML Output Section 1.25. Producing XML Output Section 1.26. Suppressing Column Headings in Query Output Section 1.27. Numbering Query Output Lines Section 1.28. Making Long Output Lines More Readable Section 1.29. Controlling mysql's Verbosity Level Section 1.30. Logging Interactive mysql Sessions Section 1.31. Creating mysql Scripts from Previously Executed Queries Section 1.32. Using mysql as a Calculator Section 1.33. Using mysql in Shell Scripts Chapter 2. Writing MySQL-Based Programs Section 2.1. Introduction Section 2.2. Connecting to the MySQL Server, Selecting a Database, and Disconnecting Section 2.3. Checking for Errors Section 2.4. Writing Library Files Section 2.5. Issuing Queries and Retrieving Results Section 2.6. Moving Around Within a Result Set Section 2.7. Using Prepared Statements and Placeholders in Queries Section 2.8. Including Special Characters and NULL Values in Queries Section 2.9. Handling NULL Values in Result Sets Section 2.10. Writing an Object-Oriented MySQL Interface for PHP Section 2.11. Ways of Obtaining Connection Parameters Section 2.12. Conclusion and Words of Advice Chapter 3. Record Selection Techniques Section 3.1. Introduction Section 3.2. Specifying Which Columns to Display Section 3.3. Avoiding Output Column Order Problems When Writing Programs Section 3.4. Giving Names to Output Columns Section 3.5. Using Column Aliases to Make Programs Easier to Write Section 3.6. Combining Columns to Construct Composite Values Section 3.7. Specifying Which Rows to Select Section 3.8. WHERE Clauses and Column Aliases Section 3.9. Displaying Comparisons to Find Out How Something Works Section 3.10. Reversing or Negating Query Conditions Section 3.11. Removing Duplicate Rows Section 3.12. Working with NULL Values Section 3.13. Negating a Condition on a Column That Contains NULL Values Section 3.14. Writing Comparisons Involving NULL in Programs Section 3.15. Mapping NULL Values to Other Values for Display Section 3.16. Sorting a Result Set Section 3.17. Selecting Records from the Beginning or End of a Result Set Section 3.18. Pulling a Section from the Middle of a Result Set Section 3.19. Choosing Appropriate LIMIT Values Section 3.20. Calculating LIMIT Values from Expressions Section 3.21. What to Do When LIMIT Requires the "Wrong" Sort Order Section 3.22. Selecting a Result Set into an Existing Table Section 3.23. Creating a Destination Table on the Fly from a Result Set Section 3.24. Moving Records Between Tables Safely Section 3.25. Creating Temporary Tables Section 3.26. Cloning a Table Exactly Section 3.27. Generating Unique Table Names Chapter 4. Working with Strings Section 4.1. Introduction Section 4.2. Writing Strings That Include Quotes or Special Characters Section 4.3. Preserving Trailing Spaces in String Columns Section 4.4. Testing String Equality or Relative Ordering Section 4.5. Decomposing or Combining Strings Section 4.6. Checking Whether a String Contains a Substring Section 4.7. Pattern Matching with SQL Patterns Section 4.8. Pattern Matching with Regular Expressions Section 4.9. Matching Pattern Metacharacters Literally Section 4.10. Controlling Case Sensitivity in String Comparisons Section 4.11. Controlling Case Sensitivity in Pattern Matching Section 4.12. Using FULLTEXT Searches Section 4.13. Using a FULLTEXT Search with Short Words Section 4.14. Requiring or Excluding FULLTEXT Search Words Section 4.15. Performing Phrase Searches with a FULLTEXT Index Chapter 5. Working with Dates and Times Section 5.1. Introduction Section 5.2. Changing MySQL's Date Format Section 5.3. Telling MySQL How to Display Dates or Times Section 5.4. Determining the Current Date or Time Section 5.5. Decomposing Dates and Times Using Formatting Functions Section 5.6. Decomposing Dates or Times Using Component-Extraction Functions Section 5.7. Decomposing Dates or Times Using String Functions Section 5.8. Synthesizing Dates or Times Using Formatting Functions Section 5.9. Synthesizing Dates or Times Using Component-Extraction Functions Section 5.10. Combining a Date and a Time into a Date-and-Time Value Section 5.11. Converting Between Times and Seconds Section 5.12. Converting Between Dates and Days Section 5.13. Converting Between Date-and-Time Values and Seconds Section 5.14. Adding a Temporal Interval to a Time Section 5.15. Calculating Intervals Between Times Section 5.16. Breaking Down Time Intervals into Components Section 5.17. Adding a Temporal Interval to a Date Section 5.18. Calculating Intervals Between Dates Section 5.19. Canonizing Not-Quite-ISO Date Strings Section 5.20. Calculating Ages Section 5.21. Shifting Dates by a Known Amount Section 5.22. Finding First and Last Days of Months Section 5.23. Finding the Length of a Month Section 5.24. Calculating One Date from Another by Substring Replacement Section 5.25. Finding the Day of the Week for a Date Section 5.26. Finding Dates for Days of the Current Week Section 5.27. Finding Dates for Weekdays of Other Weeks Section 5.28. Performing Leap Year Calculations Section 5.29. Treating Dates or Times as Numbers Section 5.30. Forcing MySQL to Treat Strings as Temporal Values Section 5.31. Selecting Records Based on Their Temporal Characteristics Section 5.32. Using TIMESTAMP Values Section 5.33. Recording a Row's Last Modification Time Section 5.34. Recording a Row's Creation Time Section 5.35. Performing Calculations with TIMESTAMP Values Section 5.36. Displaying TIMESTAMP Values in Readable Form Chapter 6. Sorting Query Results Section 6.1. Introduction Section 6.2. Using ORDER BY to Sort Query Results Section 6.3. Sorting Subsets of a Table Section 6.4. Sorting Expression Results Section 6.5. Displaying One Set of Values While Sorting by Another Section 6.6. Sorting and NULL Values Section 6.7. Controlling Case Sensitivity of String Sorts Section 6.8. Date-Based Sorting Section 6.9. Sorting by Calendar Day Section 6.10. Sorting by Day of Week Section 6.11. Sorting by Time of Day Section 6.12. Sorting Using Substrings of Column Values Section 6.13. Sorting by Fixed-Length Substrings Section 6.14. Sorting by Variable-Length Substrings Section 6.15. Sorting Hostnames in Domain Order Section 6.16. Sorting Dotted-Quad IP Values in Numeric Order Section 6.17. Floating Specific Values to the Head or Tail of the Sort Order Section 6.18. Sorting in User-Defined Orders Section 6.19. Sorting ENUM Values Chapter 7. Generating Summaries Section 7.1. Introduction Section 7.2. Summarizing with COUNT( ) Section 7.3. Summarizing with MIN( ) and MAX( ) Section 7.4. Summarizing with SUM( ) and AVG( ) Section 7.5. Using DISTINCT to Eliminate Duplicates Section 7.6. Finding Values Associated with Minimum and Maximum Values Section 7.7. Controlling String Case Sensitivity for MIN( ) and MAX( ) Section 7.8. Dividing a Summary into Subgroups Section 7.9. Summaries and NULL Values Section 7.10. Selecting Only Groups with Certain Characteristics Section 7.11. Determining Whether Values are Unique Section 7.12. Grouping by Expression Results Section 7.13. Categorizing Non-Categorical Data Section 7.14. Controlling Summary Display Order Section 7.15. Finding Smallest or Largest Summary Values Section 7.16. Date-Based Summaries Section 7.17. Working with Per-Group and Overall Summary Values Simultaneously Section 7.18. Generating a Report That Includes a Summary and a List Chapter 8. Modifying Tables with ALTER TABLE Section 8.1. Introduction Section 8.2. Dropping, Adding, or Repositioning a Column Section 8.3. Changing a Column Definition or Name Section 8.4. The Effect of ALTER TABLE on Null and Default Value Attributes Section 8.5. Changing a Column's Default Value Section 8.6. Changing a Table Type Section 8.7. Renaming a Table Section 8.8. Adding or Dropping Indexes Section 8.9. Eliminating Duplicates by Adding an Index Section 8.10. Using ALTER TABLE to Normalize a Table Chapter 9. Obtaining and Using Metadata Section 9.1. Introduction Section 9.2. Obtaining the Number of Rows Affected by a Query Section 9.3. Obtaining Result Set Metadata Section 9.4. Determining Presence or Absence of a Result Set Section 9.5. Formatting Query Results for Display Section 9.6. Getting Table Structure Information Section 9.7. Getting ENUM and SET Column Information Section 9.8. Database-Independent Methods of Obtaining Table Information Section 9.9. Applying Table Structure Information Section 9.10. Listing Tables and Databases Section 9.11. Testing Whether a Table Exists Section 9.12. Testing Whether a Database Exists Section 9.13. Getting Server Metadata Section 9.14. Writing Applications That Adapt to the MySQL Server Version Section 9.15. Determining the Current Database Section 9.16. Determining the Current MySQL User Section 9.17. Monitoring the MySQL Server Section 9.18. Determining Which Table Types the Server Supports Chapter 10. Importing and Exporting Data Section 10.1. Introduction Section 10.2. Importing Data with LOAD DATA and mysqlimport Section 10.3. Specifying the Datafile Location Section 10.4. Specifying the Datafile Format Section 10.5. Dealing with Quotes and Special Characters Section 10.6. Importing CSV Files Section 10.7. Reading Files from Different Operating Systems Section 10.8. Handling Duplicate Index Values Section 10.9. Getting LOAD DATA to Cough Up More Information Section 10.10. Don't Assume LOAD DATA Knows More than It Does Section 10.11. Skipping Datafile Lines Section 10.12. Specifying Input Column Order Section 10.13. Skipping Datafile Columns Section 10.14. Exporting Query Results from MySQL Section 10.15. Exporting Tables as Raw Data Section 10.16. Exporting Table Contents or Definitions in SQL Format Section 10.17. Copying Tables or Databases to Another Server Section 10.18. Writing Your Own Export Programs Section 10.19. Converting Datafiles from One Format to Another Section 10.20. Extracting and Rearranging Datafile Columns Section 10.21. Validating and Transforming Data Section 10.22. Validation by Direct Comparison Section 10.23. Validation by Pattern Matching Section 10.24. Using Patterns to Match Broad Content Types Section 10.25. Using Patterns to Match Numeric Values Section 10.26. Using Patterns to Match Dates or Times Section 10.27. Using Patterns to Match Email Addresses and URLs Section 10.28. Validation Using Table Metadata Section 10.29. Validation Using a Lookup Table Section 10.30. Converting Two-Digit Year Values to Four-Digit Form Section 10.31. Performing Validity Checking on Date or Time Subparts Section 10.32. Writing Date-Processing Utilities Section 10.33. Using Dates with Missing Components Section 10.34. Performing Date Conversion Using SQL Section 10.35. Using Temporary Tables for Data Transformation Section 10.36. Dealing with NULL Values Section 10.37. Guessing Table Structure from a Datafile Section 10.38. A LOAD DATA Diagnostic Utility Section 10.39. Exchanging Data Between MySQL and Microsoft Access Section 10.40. Exchanging Data Between MySQL and Microsoft Excel Section 10.41. Exchanging Data Between MySQL and FileMaker Pro Section 10.42. Exporting Query Results as XML Section 10.43. Importing XML into MySQL Section 10.44. Epilog Chapter 11. Generating and Using Sequences Section 11.1. Introduction Section 11.2. Using AUTO_INCREMENT To Set Up a Sequence Column Section 11.3. Generating Sequence Values Section 11.4. Choosing the Type for a Sequence Column Section 11.5. The Effect of Record Deletions on Sequence Generation Section 11.6. Retrieving Sequence Values Section 11.7. Determining Whether to Resequence a Column Section 11.8. Extending the Range of a Sequence Column Section 11.9. Renumbering an Existing Sequence Section 11.10. Reusing Values at the Top of a Sequence Section 11.11. Ensuring That Rows Are Renumbered in a Particular Order Section 11.12. Starting a Sequence at a Particular Value Section 11.13. Sequencing an Unsequenced Table Section 11.14. Using an AUTO_INCREMENT Column to Create Multiple Sequences Section 11.15. Managing Multiple SimultaneousAUTO_INCREMENT Values Section 11.16. Using AUTO_INCREMENT Valuesto Relate Tables Section 11.17. Using Single-Row Sequence Generators Section 11.18. Generating Repeating Sequences Section 11.19. Numbering Query Output Rows Sequentially Chapter 12. Using Multiple Tables Section 12.1. Introduction Section 12.2. Combining Rows in One Table with Rows in Another Section 12.3. Performing a Join Between Tables in Different Databases Section 12.4. Referring to Join Output Column Names in Programs Section 12.5. Finding Rows in One Table That Match Rows in Another Section 12.6. Finding Rows with No Match in Another Table Section 12.7. Finding Rows Containing Per-Group Minimum or Maximum Values Section 12.8. Computing Team Standings Section 12.9. Producing Master-Detail Lists and Summaries Section 12.10. Using a Join to Fill in Holes in a List Section 12.11. Enumerating a Many-to-Many Relationship Section 12.12. Comparing a Table to Itself Section 12.13. Calculating Differences Between Successive Rows Section 12.14. Finding Cumulative Sums and Running Averages Section 12.15. Using a Join to Control Query Output Order Section 12.16. Converting Subselects to Join Operations Section 12.17. Selecting Records in Parallel from Multiple Tables Section 12.18. Inserting Records in One Table That Include Values from Another Section 12.19. Updating One Table Based on Values in Another Section 12.20. Using a Join to Create a Lookup Table from Descriptive Labels Section 12.21. Deleting Related Rows in Multiple Tables Section 12.22. Identifying and Removing Unattached Records Section 12.23. Using Different MySQL Servers Simultaneously Chapter 13. Statistical Techniques Section 13.1. Introduction Section 13.2. Calculating Descriptive Statistics Section 13.3. Per-Group Descriptive Statistics Section 13.4. Generating Frequency Distributions Section 13.5. Counting Missing Values Section 13.6. Calculating Linear Regressions or Correlation Coefficients Section 13.7. Generating Random Numbers Section 13.8. Randomizing a Set of Rows Section 13.9. Selecting Random Items from a Set of Rows Section 13.10. Assigning Ranks Chapter 14. Handling Duplicates Section 14.1. Introduction Section 14.2. Preventing Duplicates from Occurring in a Table Section 14.3. Dealing with Duplicates at Record-Creation Time Section 14.4. Counting and Identifying Duplicates Section 14.5. Eliminating Duplicates from a Query Result Section 14.6. Eliminating Duplicates from a Self-Join Result Section 14.7. Eliminating Duplicates from a Table Chapter 15. Performing Transactions Section 15.1. Introduction Section 15.2. Verifying Transaction Support Requirements Section 15.3. Performing Transactions Using SQL Section 15.4. Performing Transactions from Within Programs Section 15.5. Using Transactions in Perl Programs Section 15.6. Using Transactions in PHP Programs Section 15.7. Using Transactions in Python Programs Section 15.8. Using Transactions in Java Programs Section 15.9. Using Alternatives to Transactions Chapter 16. Introduction to MySQL on the Web Section 16.1. Introduction Section 16.2. Basic Web Page Generation Section 16.3. Using Apache to Run Web Scripts Section 16.4. Using Tomcat to Run Web Scripts Section 16.5. Encoding Special Characters in Web Output Chapter 17. Incorporating Query Resultsinto Web Pages Section 17.1. Introduction Section 17.2. Displaying Query Results as Paragraph Text Section 17.3. Displaying Query Results as Lists Section 17.4. Displaying Query Results as Tables Section 17.5. Displaying Query Results as Hyperlinks Section 17.6. Creating a Navigation Index from Database Content Section 17.7. Storing Images or Other Binary Data Section 17.8. Retrieving Images or Other Binary Data Section 17.9. Serving Banner Ads Section 17.10. Serving Query Results for Download Chapter 18. Processing Web Input with MySQL Section 18.1. Introduction Section 18.2. Creating Forms in Scripts Section 18.3. Creating Single-Pick Form Elements from Database Content Section 18.4. Creating Multiple-Pick Form Elements from Database Content Section 18.5. Loading a Database Record into a Form Section 18.6. Collecting Web Input Section 18.7. Validating Web Input Section 18.8. Using Web Input to Construct Queries Section 18.9. Processing File Uploads Section 18.10. Performing Searches and Presenting the Results Section 18.11. Generating Previous-Page and Next-Page Links Section 18.12. Generating "Click to Sort" Table Headings Section 18.13. Web Page Access Counting Section 18.14. Web Page Access Logging Section 18.15. Using MySQL for Apache Logging Chapter 19. Using MySQL-Based Web Session Management Section 19.1. Introduction Section 19.2. Using MySQL-Based Sessions in Perl Applications Section 19.3. Using MySQL-Based Storage with the PHP Session Manager Section 19.4. Using MySQL for Session BackingStore with Tomcat Appendix A. Obtaining MySQL Software Section A.1. Obtaining Sample Source Code and Data Section A.2. Obtaining MySQL and Related Software Appendix B. JSP and Tomcat Primer Section B.1. Servlet and JavaServer Pages Overview Section B.2. Setting Up a Tomcat Server Section B.3. Web Application Structure Section B.4. Elements of JSP Pages Appendix C. References Section C.1. MySQL Resources Section C.2. Perl Resources Section C.3. PHP Resources Section C.4. Python Resources Section C.5. Java Resources Section C.6. Apache Resources Section C.7. Other Resources Colophon Index
