A.1. Obtaining Sample Source Code and Data
The examples in this book are based on source code and sample data from three distributions. Two of these (the recipes and mcb-kjv distributions) are available at the MySQL Cookbook companion web site, which you can visit at this address:
http://www.kitebird.com/mysql-cookbook/
The recipes distribution is the primary source of examples. It's available as a compressed tar file (recipes.tar.gz) or as a ZIP file (recipes.zip). Either distribution format when unpacked will create a directory named recipes.
The recipes distribution contains programs as shown in the book, but in many cases also includes implementations in additional languages. For example, a recipe shown in the book using Python may be available on the site in Perl, PHP, or Java as well. This may save you some translation effort should you wish to convert a program as shown in the book to a different language. I expect to update the distribution from time to time, so you may want to check back occasionally for updates. If you would like to contribute a translation for inclusion into the distribution, let me know by sending email to mysql-cookbook@kitebird.com.
The Kitebird site also provides access to the mcb-kjv distribution, which contains the text of the King James Version of the Bible, formatted suitably for loading into MySQL. It's used in Chapter 4, as the source of a reasonably large body of of text for examples that demonstrate FULLTEXT searches, and occasionally elsewhere in the book. This distribution is provided separately from the recipes distribution due to its size. It's available as a compressed tar file (mcb-kjv.tar.gz) or as a ZIP file (mcb-kjv.zip). Either distribution format when unpacked will create a directory named mcb-kjv.
The mcb-kjv distribution is derived from the KJV text available at the Unbound Bible site at Biola University (http://unbound.biola.edu). The distribution includes notes that describe how it differs from the one available at Biola.
Text files in the recipes and mcb-kjv distributions were created with tabstops set every four spaces. If a file doesn't seem to line up properly when you view it in your editor, try setting your tabstops similarly. For printing, you can run files through expand -4. For example, if you normally print a file like this:
% pr filename | lpr
Try this instead:
% expand -4 filename | pr | lpr
The third source of information used for examples in this book is the baseball database produced by The Baseball Archive. If you want to try out the examples that use this database, visit the Archive's web site at:
http://baseball1.com/
Examples in this book use Version 4.5 of the baseball database, which is available in Access 2000, Access 97, and comma-delimited text file formats. See the baseball1 directory of the recipes distribution for instructions that describe how to load the comma-delimited format into MySQL. (See also Recipe 10.37.)
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
- Enterprise Application Integration: New Solutions for a Solved Problem or a Challenging Research Field?
- The Effects of an Enterprise Resource Planning System (ERP) Implementation on Job Characteristics – A Study using the Hackman and Oldham Job Characteristics Model
- Distributed Data Warehouse for Geo-spatial Services
- Data Mining for Business Process Reengineering
- Development of Interactive Web Sites to Enhance Police/Community Relations
