MySQL Cookbook

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