See more Amazon Details

Contents Listing

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