Books & Videos

Table of Contents

  1. Chapter 1 Using the mysql Client Program

    1. Introduction

    2. Setting Up a MySQL User Account

    3. Creating a Database and a Sample Table

    4. Starting and Terminating mysql

    5. Specifying Connection Parameters by Using Option Files

    6. Protecting Option Files

    7. Mixing Command-Line and Option File Parameters

    8. What to Do if mysql Cannot Be Found

    9. Setting Environment Variables

    10. Issuing Queries

    11. Selecting a Database

    12. Canceling a Partially Entered Query

    13. Repeating and Editing Queries

    14. Using Auto-Completion for Database and Table Names

    15. Using SQL Variables in Queries

    16. Telling mysql to Read Queries from a File

    17. Telling mysql to Read Queries from Other Programs

    18. Specifying Queries on the Command Line

    19. Using Copy and Paste as a mysql Input Source

    20. Preventing Query Output from Scrolling off the Screen

    21. Sending Query Output to a File or to a Program

    22. Selecting Tabular or Tab-Delimited Query Output Format

    23. Specifying Arbitrary Output Column Delimiters

    24. Producing HTML Output

    25. Producing XML Output

    26. Suppressing Column Headings in Query Output

    27. Numbering Query Output Lines

    28. Making Long Output Lines More Readable

    29. Controlling mysql’s Verbosity Level

    30. Logging Interactive mysql Sessions

    31. Creating mysql Scripts from Previously Executed Queries

    32. Using mysql as a Calculator

    33. Using mysql in Shell Scripts

  2. Chapter 2 Writing MySQL-Based Programs

    1. Introduction

    2. Connecting to the MySQL Server, Selecting a Database, and Disconnecting

    3. Checking for Errors

    4. Writing Library Files

    5. Issuing Queries and Retrieving Results

    6. Moving Around Within a Result Set

    7. Using Prepared Statements and Placeholders in Queries

    8. Including Special Characters and NULL Values in Queries

    9. Handling NULL Values in Result Sets

    10. Writing an Object-Oriented MySQL Interface for PHP

    11. Ways of Obtaining Connection Parameters

    12. Conclusion and Words of Advice

  3. Chapter 3 Record Selection Techniques

    1. Introduction

    2. Specifying Which Columns to Display

    3. Avoiding Output Column Order Problems When Writing Programs

    4. Giving Names to Output Columns

    5. Using Column Aliases to Make Programs Easier to Write

    6. Combining Columns to Construct Composite Values

    7. Specifying Which Rows to Select

    8. WHERE Clauses and Column Aliases

    9. Displaying Comparisons to Find Out How Something Works

    10. Reversing or Negating Query Conditions

    11. Removing Duplicate Rows

    12. Working with NULL Values

    13. Negating a Condition on a Column That Contains NULL Values

    14. Writing Comparisons Involving NULL in Programs

    15. Mapping NULL Values to Other Values for Display

    16. Sorting a Result Set

    17. Selecting Records from the Beginning or End of a Result Set

    18. Pulling a Section from the Middle of a Result Set

    19. Choosing Appropriate LIMIT Values

    20. Calculating LIMIT Values from Expressions

    21. What to Do When LIMIT Requires the “Wrong” Sort Order

    22. Selecting a Result Set into an Existing Table

    23. Creating a Destination Table on the Fly from a Result Set

    24. Moving Records Between Tables Safely

    25. Creating Temporary Tables

    26. Cloning a Table Exactly

    27. Generating Unique Table Names

  4. Chapter 4 Working with Strings

    1. Introduction

    2. Writing Strings That Include Quotes or Special Characters

    3. Preserving Trailing Spaces in String Columns

    4. Testing String Equality or Relative Ordering

    5. Decomposing or Combining Strings

    6. Checking Whether a String Contains a Substring

    7. Pattern Matching with SQL Patterns

    8. Pattern Matching with Regular Expressions

    9. Matching Pattern Metacharacters Literally

    10. Controlling Case Sensitivity in String Comparisons

    11. Controlling Case Sensitivity in Pattern Matching

    12. Using FULLTEXT Searches

    13. Using a FULLTEXT Search with Short Words

    14. Requiring or Excluding FULLTEXT Search Words

    15. Performing Phrase Searches with a FULLTEXT Index

  5. Chapter 5 Working with Dates and Times

    1. Introduction

    2. Changing MySQL’s Date Format

    3. Telling MySQL How to Display Dates or Times

    4. Determining the Current Date or Time

    5. Decomposing Dates and Times Using Formatting Functions

    6. Decomposing Dates or Times Using Component-Extraction Functions

    7. Decomposing Dates or Times Using String Functions

    8. Synthesizing Dates or Times Using Formatting Functions

    9. Synthesizing Dates or Times Using Component-Extraction Functions

    10. Combining a Date and a Time into a Date-and-Time Value

    11. Converting Between Times and Seconds

    12. Converting Between Dates and Days

    13. Converting Between Date-and-Time Values and Seconds

    14. Adding a Temporal Interval to a Time

    15. Calculating Intervals Between Times

    16. Breaking Down Time Intervals into Components

    17. Adding a Temporal Interval to a Date

    18. Calculating Intervals Between Dates

    19. Canonizing Not-Quite-ISO Date Strings

    20. Calculating Ages

    21. Shifting Dates by a Known Amount

    22. Finding First and Last Days of Months

    23. Finding the Length of a Month

    24. Calculating One Date from Another by Substring Replacement

    25. Finding the Day of the Week for a Date

    26. Finding Dates for Days of the Current Week

    27. Finding Dates for Weekdays of Other Weeks

    28. Performing Leap Year Calculations

    29. Treating Dates or Times as Numbers

    30. Forcing MySQL to Treat Strings as Temporal Values

    31. Selecting Records Based on Their Temporal Characteristics

    32. Using TIMESTAMP Values

    33. Recording a Row’s Last Modification Time

    34. Recording a Row’s Creation Time

    35. Performing Calculations with TIMESTAMP Values

    36. Displaying TIMESTAMP Values in Readable Form

  6. Chapter 6 Sorting Query Results

    1. Introduction

    2. Using ORDER BY to Sort Query Results

    3. Sorting Subsets of a Table

    4. Sorting Expression Results

    5. Displaying One Set of Values While Sorting by Another

    6. Sorting and NULL Values

    7. Controlling Case Sensitivity of String Sorts

    8. Date-Based Sorting

    9. Sorting by Calendar Day

    10. Sorting by Day of Week

    11. Sorting by Time of Day

    12. Sorting Using Substrings of Column Values

    13. Sorting by Fixed-Length Substrings

    14. Sorting by Variable-Length Substrings

    15. Sorting Hostnames in Domain Order

    16. Sorting Dotted-Quad IP Values in Numeric Order

    17. Floating Specific Values to the Head or Tail of the Sort Order

    18. Sorting in User-Defined Orders

    19. Sorting ENUM Values

  7. Chapter 7 Generating Summaries

    1. Introduction

    2. Summarizing with COUNT( )

    3. Summarizing with MIN( ) and MAX( )

    4. Summarizing with SUM( ) and AVG( )

    5. Using DISTINCT to Eliminate Duplicates

    6. Finding Values Associated with Minimum and Maximum Values

    7. Controlling String Case Sensitivity for MIN( ) and MAX( )

    8. Dividing a Summary into Subgroups

    9. Summaries and NULL Values

    10. Selecting Only Groups with Certain Characteristics

    11. Determining Whether Values are Unique

    12. Grouping by Expression Results

    13. Categorizing Non-Categorical Data

    14. Controlling Summary Display Order

    15. Finding Smallest or Largest Summary Values

    16. Date-Based Summaries

    17. Working with Per-Group and Overall Summary Values Simultaneously

    18. Generating a Report That Includes a Summary and a List

  8. Chapter 8 Modifying Tables with ALTER TABLE

    1. Introduction

    2. Dropping, Adding, or Repositioning a Column

    3. Changing a Column Definition or Name

    4. The Effect of ALTER TABLE on Null and Default Value Attributes

    5. Changing a Column’s Default Value

    6. Changing a Table Type

    7. Renaming a Table

    8. Adding or Dropping Indexes

    9. Eliminating Duplicates by Adding an Index

    10. Using ALTER TABLE to Normalize a Table

  9. Chapter 9 Obtaining and Using Metadata

    1. Introduction

    2. Obtaining the Number of Rows Affected by a Query

    3. Obtaining Result Set Metadata

    4. Determining Presence or Absence of a Result Set

    5. Formatting Query Results for Display

    6. Getting Table Structure Information

    7. Getting ENUM and SET Column Information

    8. Database-Independent Methods of Obtaining Table Information

    9. Applying Table Structure Information

    10. Listing Tables and Databases

    11. Testing Whether a Table Exists

    12. Testing Whether a Database Exists

    13. Getting Server Metadata

    14. Writing Applications That Adapt to the MySQL Server Version

    15. Determining the Current Database

    16. Determining the Current MySQL User

    17. Monitoring the MySQL Server

    18. Determining Which Table Types the Server Supports

  10. Chapter 10 Importing and Exporting Data

    1. Introduction

    2. Importing Data with LOAD DATA and mysqlimport

    3. Specifying the Datafile Location

    4. Specifying the Datafile Format

    5. Dealing with Quotes and Special Characters

    6. Importing CSV Files

    7. Reading Files from Different Operating Systems

    8. Handling Duplicate Index Values

    9. Getting LOAD DATA to Cough Up More Information

    10. Don’t Assume LOAD DATA Knows More than It Does

    11. Skipping Datafile Lines

    12. Specifying Input Column Order

    13. Skipping Datafile Columns

    14. Exporting Query Results from MySQL

    15. Exporting Tables as Raw Data

    16. Exporting Table Contents or Definitions in SQL Format

    17. Copying Tables or Databases to Another Server

    18. Writing Your Own Export Programs

    19. Converting Datafiles from One Format to Another

    20. Extracting and Rearranging Datafile Columns

    21. Validating and Transforming Data

    22. Validation by Direct Comparison

    23. Validation by Pattern Matching

    24. Using Patterns to Match Broad Content Types

    25. Using Patterns to Match Numeric Values

    26. Using Patterns to Match Dates or Times

    27. Using Patterns to Match Email Addresses and URLs

    28. Validation Using Table Metadata

    29. Validation Using a Lookup Table

    30. Converting Two-Digit Year Values to Four-Digit Form

    31. Performing Validity Checking on Date or Time Subparts

    32. Writing Date-Processing Utilities

    33. Using Dates with Missing Components

    34. Performing Date Conversion Using SQL

    35. Using Temporary Tables for Data Transformation

    36. Dealing with NULL Values

    37. Guessing Table Structure from a Datafile

    38. A LOAD DATA Diagnostic Utility

    39. Exchanging Data Between MySQL and Microsoft Access

    40. Exchanging Data Between MySQL and Microsoft Excel

    41. Exchanging Data Between MySQL and FileMaker Pro

    42. Exporting Query Results as XML

    43. Importing XML into MySQL

    44. Epilog

  11. Chapter 11 Generating and Using Sequences

    1. Introduction

    2. Using AUTO_INCREMENT To Set Up a Sequence Column

    3. Generating Sequence Values

    4. Choosing the Type for a Sequence Column

    5. The Effect of Record Deletions on Sequence Generation

    6. Retrieving Sequence Values

    7. Determining Whether to Resequence a Column

    8. Extending the Range of a Sequence Column

    9. Renumbering an Existing Sequence

    10. Reusing Values at the Top of a Sequence

    11. Ensuring That Rows Are Renumbered in a Particular Order

    12. Starting a Sequence at a Particular Value

    13. Sequencing an Unsequenced Table

    14. Using an AUTO_INCREMENT Column to Create Multiple Sequences

    15. Managing Multiple SimultaneousAUTO_INCREMENT Values

    16. Using AUTO_INCREMENT Valuesto Relate Tables

    17. Using Single-Row Sequence Generators

    18. Generating Repeating Sequences

    19. Numbering Query Output Rows Sequentially

  12. Chapter 12 Using Multiple Tables

    1. Introduction

    2. Combining Rows in One Table with Rows in Another

    3. Performing a Join Between Tables in Different Databases

    4. Referring to Join Output Column Names in Programs

    5. Finding Rows in One Table That Match Rows in Another

    6. Finding Rows with No Match in Another Table

    7. Finding Rows Containing Per-Group Minimum or Maximum Values

    8. Computing Team Standings

    9. Producing Master-Detail Lists and Summaries

    10. Using a Join to Fill in Holes in a List

    11. Enumerating a Many-to-Many Relationship

    12. Comparing a Table to Itself

    13. Calculating Differences Between Successive Rows

    14. Finding Cumulative Sums and Running Averages

    15. Using a Join to Control Query Output Order

    16. Converting Subselects to Join Operations

    17. Selecting Records in Parallel from Multiple Tables

    18. Inserting Records in One Table That Include Values from Another

    19. Updating One Table Based on Values in Another

    20. Using a Join to Create a Lookup Table from Descriptive Labels

    21. Deleting Related Rows in Multiple Tables

    22. Identifying and Removing Unattached Records

    23. Using Different MySQL Servers Simultaneously

  13. Chapter 13 Statistical Techniques

    1. Introduction

    2. Calculating Descriptive Statistics

    3. Per-Group Descriptive Statistics

    4. Generating Frequency Distributions

    5. Counting Missing Values

    6. Calculating Linear Regressions or Correlation Coefficients

    7. Generating Random Numbers

    8. Randomizing a Set of Rows

    9. Selecting Random Items from a Set of Rows

    10. Assigning Ranks

  14. Chapter 14 Handling Duplicates

    1. Introduction

    2. Preventing Duplicates from Occurring in a Table

    3. Dealing with Duplicates at Record-Creation Time

    4. Counting and Identifying Duplicates

    5. Eliminating Duplicates from a Query Result

    6. Eliminating Duplicates from a Self-Join Result

    7. Eliminating Duplicates from a Table

  15. Chapter 15 Performing Transactions

    1. Introduction

    2. Verifying Transaction Support Requirements

    3. Performing Transactions Using SQL

    4. Performing Transactions from Within Programs

    5. Using Transactions in Perl Programs

    6. Using Transactions in PHP Programs

    7. Using Transactions in Python Programs

    8. Using Transactions in Java Programs

    9. Using Alternatives to Transactions

  16. Chapter 16 Introduction to MySQL on the Web

    1. Introduction

    2. Basic Web Page Generation

    3. Using Apache to Run Web Scripts

    4. Using Tomcat to Run Web Scripts

    5. Encoding Special Characters in Web Output

  17. Chapter 17 Incorporating Query Resultsinto Web Pages

    1. Introduction

    2. Displaying Query Results as Paragraph Text

    3. Displaying Query Results as Lists

    4. Displaying Query Results as Tables

    5. Displaying Query Results as Hyperlinks

    6. Creating a Navigation Index from Database Content

    7. Storing Images or Other Binary Data

    8. Retrieving Images or Other Binary Data

    9. Serving Banner Ads

    10. Serving Query Results for Download

  18. Chapter 18 Processing Web Input with MySQL

    1. Introduction

    2. Creating Forms in Scripts

    3. Creating Single-Pick Form Elements from Database Content

    4. Creating Multiple-Pick Form Elements from Database Content

    5. Loading a Database Record into a Form

    6. Collecting Web Input

    7. Validating Web Input

    8. Using Web Input to Construct Queries

    9. Processing File Uploads

    10. Performing Searches and Presenting the Results

    11. Generating Previous-Page and Next-Page Links

    12. Generating “Click to Sort” Table Headings

    13. Web Page Access Counting

    14. Web Page Access Logging

    15. Using MySQL for Apache Logging

  19. Chapter 19 Using MySQL-Based Web Session Management

    1. Introduction

    2. Using MySQL-Based Sessions in Perl Applications

    3. Using MySQL-Based Storage with the PHP Session Manager

    4. Using MySQL for Session BackingStore with Tomcat

  1. Appendix Obtaining MySQL Software

    1. Obtaining Sample Source Code and Data

    2. Obtaining MySQL and Related Software

  2. Appendix JSP and Tomcat Primer

    1. Servlet and JavaServer Pages Overview

    2. Setting Up a Tomcat Server

    3. Web Application Structure

    4. Elements of JSP Pages

  3. Appendix References

    1. MySQL Resources

    2. Perl Resources

    3. PHP Resources

    4. Python Resources

    5. Java Resources

    6. Apache Resources

    7. Other Resources

  4. Colophon