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. What to Do if mysql Cannot Be Found

    5. Specifying mysql Command Options

    6. Executing SQL Statements Interactively

    7. Executing SQL Statements Read from a File or Program

    8. Controlling mysql Output Destination and Format

    9. Using User-Defined Variables in SQL Statements

  2. Chapter 2 Writing MySQL-Based Programs

    1. Introduction

    2. Connecting, Selecting a Database, and Disconnecting

    3. Checking for Errors

    4. Writing Library Files

    5. Executing Statements and Retrieving Results

    6. Handling Special Characters and NULL Values in Statements

    7. Handling Special Characters in Identifiers

    8. Identifying NULL Values in Result Sets

    9. Techniques for Obtaining Connection Parameters

    10. Conclusion and Words of Advice

  3. Chapter 3 Selecting Data from Tables

    1. Introduction

    2. Specifying Which Columns and Rows to Select

    3. Naming Query Result Columns

    4. Sorting Query Results

    5. Removing Duplicate Rows

    6. Working with NULL Values

    7. Writing Comparisons Involving NULL in Programs

    8. Using Views to Simplify Table Access

    9. Selecting Data from Multiple Tables

    10. Selecting Rows from the Beginning, End, or Middle of Query Results

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

    12. Calculating LIMIT Values from Expressions

  4. Chapter 4 Table Management

    1. Introduction

    2. Cloning a Table

    3. Saving a Query Result in a Table

    4. Creating Temporary Tables

    5. Generating Unique Table Names

    6. Checking or Changing a Table Storage Engine

    7. Copying a Table Using mysqldump

  5. Chapter 5 Working with Strings

    1. Introduction

    2. String Properties

    3. Choosing a String Data Type

    4. Setting the Client Connection Character Set

    5. Writing String Literals

    6. Checking or Changing a String’s Character Set or Collation

    7. Converting the Lettercase of a String

    8. Controlling Case Sensitivity in String Comparisons

    9. Pattern Matching with SQL Patterns

    10. Pattern Matching with Regular Expressions

    11. Breaking Apart or Combining Strings

    12. Searching for Substrings

    13. Using Full-Text Searches

    14. Using a Full-Text Search with Short Words

    15. Requiring or Prohibiting Full-Text Search Words

    16. Performing Full-Text Phrase Searches

  6. Chapter 6 Working with Dates and Times

    1. Introduction

    2. Choosing a Temporal Data Type

    3. Using Fractional Seconds Support

    4. Changing MySQL’s Date Format

    5. Setting the Client Time Zone

    6. Shifting Temporal Values Between Time Zones

    7. Determining the Current Date or Time

    8. Using TIMESTAMP or DATETIME to Track Row-Modification Times

    9. Extracting Parts of Dates or Times

    10. Synthesizing Dates or Times from Component Values

    11. Converting Between Temporal Values and Basic Units

    12. Calculating Intervals Between Dates or Times

    13. Adding Date or Time Values

    14. Calculating Ages

    15. Finding the First Day, Last Day, or Length of a Month

    16. Calculating Dates by Substring Replacement

    17. Finding the Day of the Week for a Date

    18. Finding Dates for Any Weekday of a Given Week

    19. Performing Leap-Year Calculations

    20. Canonizing Not-Quite-ISO Date Strings

    21. Selecting Rows Based on Temporal Characteristics

  7. Chapter 7 Sorting Query Results

    1. Introduction

    2. Using ORDER BY to Sort Query Results

    3. Using Expressions for Sorting

    4. Displaying One Set of Values While Sorting by Another

    5. Controlling Case Sensitivity of String Sorts

    6. Date-Based Sorting

    7. Sorting by Substrings of Column Values

    8. Sorting by Fixed-Length Substrings

    9. Sorting by Variable-Length Substrings

    10. Sorting Hostnames in Domain Order

    11. Sorting Dotted-Quad IP Values in Numeric Order

    12. Floating Values to the Head or Tail of the Sort Order

    13. Defining a Custom Sort Order

    14. Sorting ENUM Values

  8. Chapter 8 Generating Summaries

    1. Introduction

    2. Basic Summary Techniques

    3. Creating a View to Simplify Using a Summary

    4. Finding Values Associated with Minimum and Maximum Values

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

    6. Dividing a Summary into Subgroups

    7. Summaries and NULL Values

    8. Selecting Only Groups with Certain Characteristics

    9. Using Counts to Determine Whether Values Are Unique

    10. Grouping by Expression Results

    11. Summarizing Noncategorical Data

    12. Finding Smallest or Largest Summary Values

    13. Date-Based Summaries

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

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

  9. Chapter 9 Using Stored Routines, Triggers, and Scheduled Events

    1. Introduction

    2. Creating Compound-Statement Objects

    3. Using Stored Functions to Encapsulate Calculations

    4. Using Stored Procedures to “Return” Multiple Values

    5. Using Triggers to Implement Dynamic Default Column Values

    6. Using Triggers to Simulate Function-Based Indexes

    7. Simulating TIMESTAMP Properties for Other Date and Time Types

    8. Using Triggers to Log Changes to a Table

    9. Using Events to Schedule Database Actions

    10. Writing Helper Routines for Executing Dynamic SQL

    11. Handling Errors Within Stored Programs

    12. Using Triggers to Preprocess or Reject Data

  10. Chapter 10 Working with Metadata

    1. Introduction

    2. Determining the Number of Rows Affected by a Statement

    3. Obtaining Result Set Metadata

    4. Determining Whether a Statement Produced a Result Set

    5. Using Metadata to Format Query Output

    6. Listing or Checking Existence of Databases or Tables

    7. Accessing Table Column Definitions

    8. Getting ENUM and SET Column Information

    9. Getting Server Metadata

    10. Writing Applications That Adapt to the MySQL Server Version

  11. Chapter 11 Importing and Exporting Data

    1. Introduction

    2. Importing Data with LOAD DATA and mysqlimport

    3. Importing CSV Files

    4. Exporting Query Results from MySQL

    5. Importing and Exporting NULL Values

    6. Writing Your Own Data Export Programs

    7. Converting Datafiles from One Format to Another

    8. Extracting and Rearranging Datafile Columns

    9. Exchanging Data Between MySQL and Microsoft Excel

    10. Exporting Query Results as XML

    11. Importing XML into MySQL

    12. Guessing Table Structure from a Datafile

  12. Chapter 12 Validating and Reformatting Data

    1. Introduction

    2. Using the SQL Mode to Reject Bad Input Values

    3. Validating and Transforming Data

    4. Using Pattern Matching to Validate Data

    5. Using Patterns to Match Broad Content Types

    6. Using Patterns to Match Numeric Values

    7. Using Patterns to Match Dates or Times

    8. Using Patterns to Match Email Addresses or URLs

    9. Using Table Metadata to Validate Data

    10. Using a Lookup Table to Validate Data

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

    12. Performing Validity Checking on Date or Time Subparts

    13. Writing Date-Processing Utilities

    14. Importing Non-ISO Date Values

    15. Exporting Dates Using Non-ISO Formats

    16. Epilogue

  13. Chapter 13 Generating and Using Sequences

    1. Introduction

    2. Creating a Sequence Column and Generating Sequence Values

    3. Choosing the Definition for a Sequence Column

    4. The Effect of Row Deletions on Sequence Generation

    5. Retrieving Sequence Values

    6. Renumbering an Existing Sequence

    7. Extending the Range of a Sequence Column

    8. Reusing Values at the Top of a Sequence

    9. Ensuring That Rows Are Renumbered in a Particular Order

    10. Sequencing an Unsequenced Table

    11. Managing Multiple Auto-Increment Values Simultaneously

    12. Using Auto-Increment Values to Associate Tables

    13. Using Sequence Generators as Counters

    14. Generating Repeating Sequences

  14. Chapter 14 Using Joins and Subqueries

    1. Introduction

    2. Finding Matches Between Tables

    3. Finding Mismatches Between Tables

    4. Identifying and Removing Mismatched or Unattached Rows

    5. Comparing a Table to Itself

    6. Producing Master-Detail Lists and Summaries

    7. Enumerating a Many-to-Many Relationship

    8. Finding Per-Group Minimum or Maximum Values

    9. Using a Join to Fill or Identify Holes in a List

    10. Using a Join to Control Query Sort Order

    11. Referring to Join Output Column Names in Programs

  15. Chapter 15 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. Calculating Successive-Row Differences

    11. Finding Cumulative Sums and Running Averages

    12. Assigning Ranks

    13. Computing Team Standings

  16. Chapter 16 Handling Duplicates

    1. Introduction

    2. Preventing Duplicates from Occurring in a Table

    3. Dealing with Duplicates When Loading Rows into a Table

    4. Counting and Identifying Duplicates

    5. Eliminating Duplicates from a Table

  17. Chapter 17 Performing Transactions

    1. Introduction

    2. Choosing a Transactional Storage Engine

    3. Performing Transactions Using SQL

    4. Performing Transactions from Within Programs

    5. Using Transactions in Perl Programs

    6. Using Transactions in Ruby Programs

    7. Using Transactions in PHP Programs

    8. Using Transactions in Python Programs

    9. Using Transactions in Java Programs

  18. Chapter 18 Introduction to MySQL on the Web

    1. Introduction

    2. Basic Principles of Web Page Generation

    3. Using Apache to Run Web Scripts

    4. Using Tomcat to Run Web Scripts

    5. Encoding Special Characters in Web Output

  19. Chapter 19 Generating Web Content from Query Results

    1. Introduction

    2. Displaying Query Results as Paragraphs

    3. Displaying Query Results as Lists

    4. Displaying Query Results as Tables

    5. Displaying Query Results as Hyperlinks

    6. Creating Navigation Indexes from Database Content

    7. Storing Images or Other Binary Data

    8. Serving Images or Other Binary Data

    9. Serving Banner Ads

    10. Serving Query Results for Download

  20. Chapter 20 Processing Web Input with MySQL

    1. Introduction

    2. Writing Scripts That Generate Web Forms

    3. Creating Single-Pick Form Elements from Database Content

    4. Creating Multiple-Pick Form Elements from Database Content

    5. Loading Database Content into a Form

    6. Collecting Web Input

    7. Validating Web Input

    8. Storing Web Input in a Database

    9. Processing File Uploads

    10. Performing Web-Based Database Searches

    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

  21. Chapter 21 Using MySQL-Based Web Session Management

    1. Introduction

    2. Using MySQL-Based Sessions in Perl Applications

    3. Using MySQL-Based Storage in Ruby Applications

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

    5. Using MySQL for Session-Backing Store with Tomcat

  22. Chapter 22 Server Administration

    1. Introduction

    2. Configuring the Server

    3. Managing the Plug-In Interface

    4. Controlling Server Logging

    5. Rotating or Expiring Logfiles

    6. Rotating Log Tables or Expiring Log Table Rows

    7. Monitoring the MySQL Server

    8. Creating and Using Backups

  23. Chapter 23 Security

    1. Introduction

    2. Understanding the mysql.user Table

    3. Managing User Accounts

    4. Implementing a Password Policy

    5. Checking Password Strength

    6. Expiring Passwords

    7. Assigning Yourself a New Password

    8. Resetting an Expired Password

    9. Finding and Fixing Insecure Accounts

    10. Disabling Use of Accounts with Pre-4.1 Passwords

    11. Finding and Removing Anonymous Accounts

    12. Modifying “Any Host” and “Many Host” Accounts