MySQL Cookbook

ISBN: 059652708X
EAN: 2147483647

Year: 2005
Pages: 412

Authors: Paul DuBois

BUY ON AMAZON

Using the mysql Client Program

• Introduction
• Setting Up a MySQL User Account
• Creating a Database and a Sample Table
• Starting and Terminating mysql
• Specifying Connection Parameters by Using Option Files
• Protecting Option Files
• Mixing Command-Line and Option File Parameters
• What to Do if mysql Cannot Be Found
• Setting Environment Variables
• Issuing Queries
• Selecting a Database
• Canceling a Partially Entered Query
• Repeating and Editing Queries
• Using Auto-Completion for Database and Table Names
• Using SQL Variables in Queries
• Telling mysql to Read Queries from a File
• Telling mysql to Read Queries from Other Programs
• Specifying Queries on the Command Line
• 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 Output
• Producing XML Output
• Suppressing Column Headings in Query Output
• Numbering Query Output Lines
• Making Long Output Lines More Readable
• Controlling mysqls Verbosity Level
• Logging Interactive mysql Sessions
• Creating mysql Scripts from Previously Executed Queries
• Using mysql as a Calculator
• Using mysql in Shell Scripts

Writing MySQL-Based Programs

• Introduction
• Connecting to the MySQL Server, Selecting a Database, and Disconnecting
• Checking for Errors
• Writing Library Files
• Issuing Queries and Retrieving Results
• Moving Around Within a Result Set
• Using Prepared Statements and Placeholders in Queries
• Including Special Characters and NULL Values in Queries
• Handling NULL Values in Result Sets
• Writing an Object-Oriented MySQL Interface for PHP
• Ways of Obtaining Connection Parameters
• Conclusion and Words of Advice

Record Selection Techniques

• Introduction
• Specifying Which Columns to Display
• Avoiding Output Column Order Problems When Writing Programs
• Giving Names to Output Columns
• Using Column Aliases to Make Programs Easier to Write
• Combining Columns to Construct Composite Values
• Specifying Which Rows to Select
• WHERE Clauses and Column Aliases
• Displaying Comparisons to Find Out How Something Works
• Reversing or Negating Query Conditions
• Removing Duplicate Rows
• Working with NULL Values
• Negating a Condition on a Column That Contains NULL Values
• Writing Comparisons Involving NULL in Programs
• Mapping NULL Values to Other Values for Display
• Sorting a Result Set
• Selecting Records from the Beginning or End of a Result Set
• 18 Pulling a Section from the Middle of a Result Set
• Choosing Appropriate LIMIT Values
• Calculating LIMIT Values from Expressions
• What to Do When LIMIT Requires the Wrong Sort Order
• Selecting a Result Set into an Existing Table
• Creating a Destination Table on the Fly from a Result Set
• Moving Records Between Tables Safely
• Creating Temporary Tables
• Cloning a Table Exactly
• Generating Unique Table Names

Working with Strings

• Introduction
• Writing Strings That Include Quotes or Special Characters
• Preserving Trailing Spaces in String Columns
• Testing String Equality or Relative Ordering
• Decomposing or Combining Strings
• Checking Whether a String Contains a Substring
• Pattern Matching with SQL Patterns
• Pattern Matching with Regular Expressions
• Matching Pattern Metacharacters Literally
• Controlling Case Sensitivity in String Comparisons
• Controlling Case Sensitivity in Pattern Matching
• Using FULLTEXT Searches
• Using a FULLTEXT Search with Short Words
• Requiring or Excluding FULLTEXT Search Words
• Performing Phrase Searches with a FULLTEXT Index

Working with Dates and Times

• Introduction
• Changing MySQLs Date Format
• Telling MySQL How to Display Dates or Times
• Determining the Current Date or Time
• Decomposing Dates and Times Using Formatting Functions
• Decomposing Dates or Times Using Component-Extraction Functions
• Decomposing Dates or Times Using String Functions
• Synthesizing Dates or Times Using Formatting Functions
• Synthesizing Dates or Times Using Component-Extraction Functions
• Combining a Date and a Time into a Date-and-Time Value
• Converting Between Times and Seconds
• Converting Between Dates and Days
• Converting Between Date-and-Time Values and Seconds
• Adding a Temporal Interval to a Time
• Calculating Intervals Between Times
• Breaking Down Time Intervals into Components
• Adding a Temporal Interval to a Date
• Calculating Intervals Between Dates
• Canonizing Not-Quite-ISO Date Strings
• Calculating Ages
• Shifting Dates by a Known Amount
• Finding First and Last Days of Months
• Finding the Length of a Month
• Calculating One Date from Another by Substring Replacement
• Finding the Day of the Week for a Date
• Finding Dates for Days of the Current Week
• Finding Dates for Weekdays of Other Weeks
• Performing Leap Year Calculations
• Treating Dates or Times as Numbers
• Forcing MySQL to Treat Strings as Temporal Values
• Selecting Records Based on Their Temporal Characteristics
• Using TIMESTAMP Values
• Recording a Rows Last Modification Time
• Recording a Rows Creation Time
• Performing Calculations with TIMESTAMP Values
• Displaying TIMESTAMP Values in Readable Form

Sorting Query Results

• Introduction
• Using ORDER BY to Sort Query Results
• Sorting Subsets of a Table
• Sorting Expression Results
• Displaying One Set of Values While Sorting by Another
• Sorting and NULL Values
• Controlling Case Sensitivity of String Sorts
• Date-Based Sorting
• Sorting by Calendar Day
• Sorting by Day of Week
• Sorting by Time of Day
• Sorting Using Substrings of Column Values
• Sorting by Fixed-Length Substrings
• Sorting by Variable-Length Substrings
• Sorting Hostnames in Domain Order
• Sorting Dotted-Quad IP Values in Numeric Order
• Floating Specific Values to the Head or Tail of the Sort Order
• Sorting in User-Defined Orders
• Sorting ENUM Values

Generating Summaries

• Introduction
• Summarizing with COUNT( )
• Summarizing with MIN( ) and MAX( )
• Summarizing with SUM( ) and AVG( )
• Using DISTINCT to Eliminate Duplicates
• Finding Values Associated with Minimum and Maximum Values
• Controlling String Case Sensitivity for MIN( ) and MAX( )
• Dividing a Summary into Subgroups
• Summaries and NULL Values
• Selecting Only Groups with Certain Characteristics
• Determining Whether Values are Unique
• Grouping by Expression Results
• Categorizing Non-Categorical Data
• Controlling Summary Display Order
• Finding Smallest or Largest Summary Values
• Date-Based Summaries
• Working with Per-Group and Overall Summary Values Simultaneously
• Generating a Report That Includes a Summary and a List

Modifying Tables with ALTER TABLE

• Introduction
• Dropping, Adding, or Repositioning a Column
• Changing a Column Definition or Name
• The Effect of ALTER TABLE on Null and Default Value Attributes
• Changing a Columns Default Value
• Changing a Table Type
• Renaming a Table
• Adding or Dropping Indexes
• Eliminating Duplicates by Adding an Index
• Using ALTER TABLE to Normalize a Table

Obtaining and Using Metadata

• Introduction
• Obtaining the Number of Rows Affected by a Query
• Obtaining Result Set Metadata
• Determining Presence or Absence of a Result Set
• Formatting Query Results for Display
• Getting Table Structure Information
• Getting ENUM and SET Column Information
• Database-Independent Methods of Obtaining Table Information
• Applying Table Structure Information
• Listing Tables and Databases
• Testing Whether a Table Exists
• Testing Whether a Database Exists
• Getting Server Metadata
• Writing Applications That Adapt to the MySQL Server Version
• Determining the Current Database
• Determining the Current MySQL User
• Monitoring the MySQL Server
• Determining Which Table Types the Server Supports

Importing and Exporting Data

• Introduction
• Importing Data with LOAD DATA and mysqlimport
• Specifying the Datafile Location
• Specifying the Datafile Format
• Dealing with Quotes and Special Characters
• Importing CSV Files
• Reading Files from Different Operating Systems
• Handling Duplicate Index Values
• Getting LOAD DATA to Cough Up More Information
• Dont Assume LOAD DATA Knows More than It Does
• Skipping Datafile Lines
• Specifying Input Column Order
• Skipping Datafile Columns
• Exporting Query Results from MySQL
• Exporting Tables as Raw Data
• Exporting Table Contents or Definitions in SQL Format
• Copying Tables or Databases to Another Server
• Writing Your Own Export Programs
• Converting Datafiles from One Format to Another
• Extracting and Rearranging Datafile Columns
• Validating and Transforming Data
• Validation by Direct Comparison
• Validation by Pattern Matching
• Using Patterns to Match Broad Content Types
• Using Patterns to Match Numeric Values
• Using Patterns to Match Dates or Times
• Using Patterns to Match Email Addresses and URLs
• Validation Using Table Metadata
• Validation Using a Lookup Table
• Converting Two-Digit Year Values to Four-Digit Form
• Performing Validity Checking on Date or Time Subparts
• Writing Date-Processing Utilities
• Using Dates with Missing Components
• Performing Date Conversion Using SQL
• Using Temporary Tables for Data Transformation
• Dealing with NULL Values
• Guessing Table Structure from a Datafile
• A LOAD DATA Diagnostic Utility
• Exchanging Data Between MySQL and Microsoft Access
• Exchanging Data Between MySQL and Microsoft Excel
• Exchanging Data Between MySQL and FileMaker Pro
• Exporting Query Results as XML
• Importing XML into MySQL
• Epilog

Generating and Using Sequences

• Introduction
• Using AUTO_INCREMENT To Set Up a Sequence Column
• Generating Sequence Values
• Choosing the Type for a Sequence Column
• The Effect of Record Deletions on Sequence Generation
• Retrieving Sequence Values
• Determining Whether to Resequence a Column
• Extending the Range of a Sequence Column
• Renumbering an Existing Sequence
• Reusing Values at the Top of a Sequence
• Ensuring That Rows Are Renumbered in a Particular Order
• Starting a Sequence at a Particular Value
• Sequencing an Unsequenced Table
• Using an AUTO_INCREMENT Column to Create Multiple Sequences
• Managing Multiple SimultaneousAUTO_INCREMENT Values
• Using AUTO_INCREMENT Valuesto Relate Tables
• Using Single-Row Sequence Generators
• Generating Repeating Sequences
• Numbering Query Output Rows Sequentially

Using Multiple Tables

• Introduction
• Combining Rows in One Table with Rows in Another
• Performing a Join Between Tables in Different Databases
• Referring to Join Output Column Names in Programs
• Finding Rows in One Table That Match Rows in Another
• Finding Rows with No Match in Another Table
• Finding Rows Containing Per-Group Minimum or Maximum Values
• Computing Team Standings
• Producing Master-Detail Lists and Summaries
• Using a Join to Fill in Holes in a List
• Enumerating a Many-to-Many Relationship
• Comparing a Table to Itself
• Calculating Differences Between Successive Rows
• Finding Cumulative Sums and Running Averages
• Using a Join to Control Query Output Order
• Converting Subselects to Join Operations
• Selecting Records in Parallel from Multiple Tables
• Inserting Records in One Table That Include Values from Another
• Updating One Table Based on Values in Another
• Using a Join to Create a Lookup Table from Descriptive Labels
• Deleting Related Rows in Multiple Tables
• Identifying and Removing Unattached Records
• Using Different MySQL Servers Simultaneously

Statistical Techniques

• Introduction
• Calculating Descriptive Statistics
• Per-Group Descriptive Statistics
• Generating Frequency Distributions
• Counting Missing Values
• Calculating Linear Regressions or Correlation Coefficients
• Generating Random Numbers
• Randomizing a Set of Rows
• Selecting Random Items from a Set of Rows
• Assigning Ranks

Handling Duplicates

• Introduction
• Preventing Duplicates from Occurring in a Table
• Dealing with Duplicates at Record-Creation Time
• Counting and Identifying Duplicates
• Eliminating Duplicates from a Query Result
• Eliminating Duplicates from a Self-Join Result
• Eliminating Duplicates from a Table

Performing Transactions

• Introduction
• Verifying Transaction Support Requirements
• Performing Transactions Using SQL
• Performing Transactions from Within Programs
• Using Transactions in Perl Programs
• Using Transactions in PHP Programs
• Using Transactions in Python Programs
• Using Transactions in Java Programs
• Using Alternatives to Transactions

Introduction to MySQL on the Web

• Introduction
• Basic Web Page Generation
• Using Apache to Run Web Scripts
• Using Tomcat to Run Web Scripts
• Encoding Special Characters in Web Output

Incorporating Query Resultsinto Web Pages

• Introduction
• Displaying Query Results as Paragraph Text
• Displaying Query Results as Lists
• Displaying Query Results as Tables
• Displaying Query Results as Hyperlinks
• Creating a Navigation Index from Database Content
• Storing Images or Other Binary Data
• Retrieving Images or Other Binary Data
• Serving Banner Ads
• Serving Query Results for Download

Processing Web Input with MySQL

• Introduction
• Creating Forms in Scripts
• Creating Single-Pick Form Elements from Database Content
• Creating Multiple-Pick Form Elements from Database Content
• Loading a Database Record into a Form
• Collecting Web Input
• Validating Web Input
• Using Web Input to Construct Queries
• Processing File Uploads
• Performing Searches and Presenting the Results
• Generating Previous-Page and Next-Page Links
• Generating Click to Sort Table Headings
• Web Page Access Counting
• Web Page Access Logging
• Using MySQL for Apache Logging

Using MySQL-Based Web Session Management

• Introduction
• Using MySQL-Based Sessions in Perl Applications
• Using MySQL-Based Storage with the PHP Session Manager
• Using MySQL for Session BackingStore with Tomcat

Appendix A. Obtaining MySQL Software

• Appendix A. Obtaining MySQL Software
• A.1. Obtaining Sample Source Code and Data
• A.2. Obtaining MySQL and Related Software

Appendix B. JSP and Tomcat Primer

• Appendix B. JSP and Tomcat Primer
• B.1. Servlet and JavaServer Pages Overview
• B.2. Setting Up a Tomcat Server
• B.3. Web Application Structure
• B.4. Elements of JSP Pages

Appendix C. References

• C.1. MySQL Resources
• C.2. Perl Resources
• C.3. PHP Resources
• C.4. Python Resources
• C.5. Java Resources