home movie radio music chord lyrics book game Dictionary clip
HOME HAND MADE RADIO SHOP CHORD LYRICS BOOKS GAME Dictionary Clip
I l@ve RuBoard Previous Section Next Section

   
•  Table of Contents
•  Index
•  Reviews
•  Examples Examples2
•  Reader Reviews
•  Errata
MySQL Cookbook
By Paul DuBois
   
Publisher : O'Reilly
Pub Date : October 2002
ISBN : 0-596-00145-2
Pages : 1022


    Copyright
    Preface
      MySQL APIs Used in This Book
      Who This Book Is For
      What's in This Book
      Platform Notes
      Conventions Used in This Book
      The Companion Web Site
      Comments and Questions
      Additional Resources
      Acknowledgments
   
    Chapter 1.  Using the mysql Client Program
      Section 1.1.  Introduction
      Section 1.2.  Setting Up a MySQL User Account
      Section 1.3.  Creating a Database and a Sample Table
      Section 1.4.  Starting and Terminating mysql
      Section 1.5.  Specifying Connection Parameters by Using Option Files
      Section 1.6.  Protecting Option Files
      Section 1.7.  Mixing Command-Line and Option File Parameters
      Section 1.8.  What to Do if mysql Cannot Be Found
      Section 1.9.  Setting Environment Variables
      Section 1.10.  Issuing Queries
      Section 1.11.  Selecting a Database
      Section 1.12.  Canceling a Partially Entered Query
      Section 1.13.  Repeating and Editing Queries
      Section 1.14.  Using Auto-Completion for Database and Table Names
      Section 1.15.  Using SQL Variables in Queries
      Section 1.16.  Telling mysql to Read Queries from a File
      Section 1.17.  Telling mysql to Read Queries from Other Programs
      Section 1.18.  Specifying Queries on the Command Line
      Section 1.19.  Using Copy and Paste as a mysql Input Source
      Section 1.20.  Preventing Query Output from Scrolling off the Screen
      Section 1.21.  Sending Query Output to a File or to a Program
      Section 1.22.  Selecting Tabular or Tab-Delimited Query Output Format
      Section 1.23.  Specifying Arbitrary Output Column Delimiters
      Section 1.24.  Producing HTML Output
      Section 1.25.  Producing XML Output
      Section 1.26.  Suppressing Column Headings in Query Output
      Section 1.27.  Numbering Query Output Lines
      Section 1.28.  Making Long Output Lines More Readable
      Section 1.29.  Controlling mysql's Verbosity Level
      Section 1.30.  Logging Interactive mysql Sessions
      Section 1.31.  Creating mysql Scripts from Previously Executed Queries
      Section 1.32.  Using mysql as a Calculator
      Section 1.33.  Using mysql in Shell Scripts
   
    Chapter 2.  Writing MySQL-Based Programs
      Section 2.1.  Introduction
      Section 2.2.  Connecting to the MySQL Server, Selecting a Database, and Disconnecting
      Section 2.3.  Checking for Errors
      Section 2.4.  Writing Library Files
      Section 2.5.  Issuing Queries and Retrieving Results
      Section 2.6.  Moving Around Within a Result Set
      Section 2.7.  Using Prepared Statements and Placeholders in Queries
      Section 2.8.  Including Special Characters and NULL Values in Queries
      Section 2.9.  Handling NULL Values in Result Sets
      Section 2.10.  Writing an Object-Oriented MySQL Interface for PHP
      Section 2.11.  Ways of Obtaining Connection Parameters
      Section 2.12.  Conclusion and Words of Advice
   
    Chapter 3.  Record Selection Techniques
      Section 3.1.  Introduction
      Section 3.2.  Specifying Which Columns to Display
      Section 3.3.  Avoiding Output Column Order Problems When Writing Programs
      Section 3.4.  Giving Names to Output Columns
      Section 3.5.  Using Column Aliases to Make Programs Easier to Write
      Section 3.6.  Combining Columns to Construct Composite Values
      Section 3.7.  Specifying Which Rows to Select
      Section 3.8.  WHERE Clauses and Column Aliases
      Section 3.9.  Displaying Comparisons to Find Out How Something Works
      Section 3.10.  Reversing or Negating Query Conditions
      Section 3.11.  Removing Duplicate Rows
      Section 3.12.  Working with NULL Values
      Section 3.13.  Negating a Condition on a Column That Contains NULL Values
      Section 3.14.  Writing Comparisons Involving NULL in Programs
      Section 3.15.  Mapping NULL Values to Other Values for Display
      Section 3.16.  Sorting a Result Set
      Section 3.17.  Selecting Records from the Beginning or End of a Result Set
      Section 3.18.  Pulling a Section from the Middle of a Result Set
      Section 3.19.  Choosing Appropriate LIMIT Values
      Section 3.20.  Calculating LIMIT Values from Expressions
      Section 3.21.  What to Do When LIMIT Requires the "Wrong" Sort Order
      Section 3.22.  Selecting a Result Set into an Existing Table
      Section 3.23.  Creating a Destination Table on the Fly from a Result Set
      Section 3.24.  Moving Records Between Tables Safely
      Section 3.25.  Creating Temporary Tables
      Section 3.26.  Cloning a Table Exactly
      Section 3.27.  Generating Unique Table Names
   
    Chapter 4.  Working with Strings
      Section 4.1.  Introduction
      Section 4.2.  Writing Strings That Include Quotes or Special Characters
      Section 4.3.  Preserving Trailing Spaces in String Columns
      Section 4.4.  Testing String Equality or Relative Ordering
      Section 4.5.  Decomposing or Combining Strings
      Section 4.6.  Checking Whether a String Contains a Substring
      Section 4.7.  Pattern Matching with SQL Patterns
      Section 4.8.  Pattern Matching with Regular Expressions
      Section 4.9.  Matching Pattern Metacharacters Literally
      Section 4.10.  Controlling Case Sensitivity in String Comparisons
      Section 4.11.  Controlling Case Sensitivity in Pattern Matching
      Section 4.12.  Using FULLTEXT Searches
      Section 4.13.  Using a FULLTEXT Search with Short Words
      Section 4.14.  Requiring or Excluding FULLTEXT Search Words
      Section 4.15.  Performing Phrase Searches with a FULLTEXT Index
   
    Chapter 5.  Working with Dates and Times
      Section 5.1.  Introduction
      Section 5.2.  Changing MySQL's Date Format
      Section 5.3.  Telling MySQL How to Display Dates or Times
      Section 5.4.  Determining the Current Date or Time
      Section 5.5.  Decomposing Dates and Times Using Formatting Functions
      Section 5.6.  Decomposing Dates or Times Using Component-Extraction Functions
      Section 5.7.  Decomposing Dates or Times Using String Functions
      Section 5.8.  Synthesizing Dates or Times Using Formatting Functions
      Section 5.9.  Synthesizing Dates or Times Using Component-Extraction Functions
      Section 5.10.  Combining a Date and a Time into a Date-and-Time Value
      Section 5.11.  Converting Between Times and Seconds
      Section 5.12.  Converting Between Dates and Days
      Section 5.13.  Converting Between Date-and-Time Values and Seconds
      Section 5.14.  Adding a Temporal Interval to a Time
      Section 5.15.  Calculating Intervals Between Times
      Section 5.16.  Breaking Down Time Intervals into Components
      Section 5.17.  Adding a Temporal Interval to a Date
      Section 5.18.  Calculating Intervals Between Dates
      Section 5.19.  Canonizing Not-Quite-ISO Date Strings
      Section 5.20.  Calculating Ages
      Section 5.21.  Shifting Dates by a Known Amount
      Section 5.22.  Finding First and Last Days of Months
      Section 5.23.  Finding the Length of a Month
      Section 5.24.  Calculating One Date from Another by Substring Replacement
      Section 5.25.  Finding the Day of the Week for a Date
      Section 5.26.  Finding Dates for Days of the Current Week
      Section 5.27.  Finding Dates for Weekdays of Other Weeks
      Section 5.28.  Performing Leap Year Calculations
      Section 5.29.  Treating Dates or Times as Numbers
      Section 5.30.  Forcing MySQL to Treat Strings as Temporal Values
      Section 5.31.  Selecting Records Based on Their Temporal Characteristics
      Section 5.32.  Using TIMESTAMP Values
      Section 5.33.  Recording a Row's Last Modification Time
      Section 5.34.  Recording a Row's Creation Time
      Section 5.35.  Performing Calculations with TIMESTAMP Values
      Section 5.36.  Displaying TIMESTAMP Values in Readable Form
   
    Chapter 6.  Sorting Query Results
      Section 6.1.  Introduction
      Section 6.2.  Using ORDER BY to Sort Query Results
      Section 6.3.  Sorting Subsets of a Table
      Section 6.4.  Sorting Expression Results
      Section 6.5.  Displaying One Set of Values While Sorting by Another
      Section 6.6.  Sorting and NULL Values
      Section 6.7.  Controlling Case Sensitivity of String Sorts
      Section 6.8.  Date-Based Sorting
      Section 6.9.  Sorting by Calendar Day
      Section 6.10.  Sorting by Day of Week
      Section 6.11.  Sorting by Time of Day
      Section 6.12.  Sorting Using Substrings of Column Values
      Section 6.13.  Sorting by Fixed-Length Substrings
      Section 6.14.  Sorting by Variable-Length Substrings
      Section 6.15.  Sorting Hostnames in Domain Order
      Section 6.16.  Sorting Dotted-Quad IP Values in Numeric Order
      Section 6.17.  Floating Specific Values to the Head or Tail of the Sort Order
      Section 6.18.  Sorting in User-Defined Orders
      Section 6.19.  Sorting ENUM Values
   
    Chapter 7.  Generating Summaries
      Section 7.1.  Introduction
      Section 7.2.  Summarizing with COUNT( )
      Section 7.3.  Summarizing with MIN( ) and MAX( )
      Section 7.4.  Summarizing with SUM( ) and AVG( )
      Section 7.5.  Using DISTINCT to Eliminate Duplicates
      Section 7.6.  Finding Values Associated with Minimum and Maximum Values
      Section 7.7.  Controlling String Case Sensitivity for MIN( ) and MAX( )
      Section 7.8.  Dividing a Summary into Subgroups
      Section 7.9.  Summaries and NULL Values
      Section 7.10.  Selecting Only Groups with Certain Characteristics
      Section 7.11.  Determining Whether Values are Unique
      Section 7.12.  Grouping by Expression Results
      Section 7.13.  Categorizing Non-Categorical Data
      Section 7.14.  Controlling Summary Display Order
      Section 7.15.  Finding Smallest or Largest Summary Values
      Section 7.16.  Date-Based Summaries
      Section 7.17.  Working with Per-Group and Overall Summary Values Simultaneously
      Section 7.18.  Generating a Report That Includes a Summary and a List
   
    Chapter 8.  Modifying Tables with ALTER TABLE
      Section 8.1.  Introduction
      Section 8.2.  Dropping, Adding, or Repositioning a Column
      Section 8.3.  Changing a Column Definition or Name
      Section 8.4.  The Effect of ALTER TABLE on Null and Default Value Attributes
      Section 8.5.  Changing a Column's Default Value
      Section 8.6.  Changing a Table Type
      Section 8.7.  Renaming a Table
      Section 8.8.  Adding or Dropping Indexes
      Section 8.9.  Eliminating Duplicates by Adding an Index
      Section 8.10.  Using ALTER TABLE to Normalize a Table
   
    Chapter 9.  Obtaining and Using Metadata
      Section 9.1.  Introduction
      Section 9.2.  Obtaining the Number of Rows Affected by a Query
      Section 9.3.  Obtaining Result Set Metadata
      Section 9.4.  Determining Presence or Absence of a Result Set
      Section 9.5.  Formatting Query Results for Display
      Section 9.6.  Getting Table Structure Information
      Section 9.7.  Getting ENUM and SET Column Information
      Section 9.8.  Database-Independent Methods of Obtaining Table Information
      Section 9.9.  Applying Table Structure Information
      Section 9.10.  Listing Tables and Databases
      Section 9.11.  Testing Whether a Table Exists
      Section 9.12.  Testing Whether a Database Exists
      Section 9.13.  Getting Server Metadata
      Section 9.14.  Writing Applications That Adapt to the MySQL Server Version
      Section 9.15.  Determining the Current Database
      Section 9.16.  Determining the Current MySQL User
      Section 9.17.  Monitoring the MySQL Server
      Section 9.18.  Determining Which Table Types the Server Supports
   
    Chapter 10.  Importing and Exporting Data
      Section 10.1.  Introduction
      Section 10.2.  Importing Data with LOAD DATA and mysqlimport
      Section 10.3.  Specifying the Datafile Location
      Section 10.4.  Specifying the Datafile Format
      Section 10.5.  Dealing with Quotes and Special Characters
      Section 10.6.  Importing CSV Files
      Section 10.7.  Reading Files from Different Operating Systems
      Section 10.8.  Handling Duplicate Index Values
      Section 10.9.  Getting LOAD DATA to Cough Up More Information
      Section 10.10.  Don't Assume LOAD DATA Knows More than It Does
      Section 10.11.  Skipping Datafile Lines
      Section 10.12.  Specifying Input Column Order
      Section 10.13.  Skipping Datafile Columns
      Section 10.14.  Exporting Query Results from MySQL
      Section 10.15.  Exporting Tables as Raw Data
      Section 10.16.  Exporting Table Contents or Definitions in SQL Format
      Section 10.17.  Copying Tables or Databases to Another Server
      Section 10.18.  Writing Your Own Export Programs
      Section 10.19.  Converting Datafiles from One Format to Another
      Section 10.20.  Extracting and Rearranging Datafile Columns
      Section 10.21.  Validating and Transforming Data
      Section 10.22.  Validation by Direct Comparison
      Section 10.23.  Validation by Pattern Matching
      Section 10.24.  Using Patterns to Match Broad Content Types
      Section 10.25.  Using Patterns to Match Numeric Values
      Section 10.26.  Using Patterns to Match Dates or Times
      Section 10.27.  Using Patterns to Match Email Addresses and URLs
      Section 10.28.  Validation Using Table Metadata
      Section 10.29.  Validation Using a Lookup Table
      Section 10.30.  Converting Two-Digit Year Values to Four-Digit Form
      Section 10.31.  Performing Validity Checking on Date or Time Subparts
      Section 10.32.  Writing Date-Processing Utilities
      Section 10.33.  Using Dates with Missing Components
      Section 10.34.  Performing Date Conversion Using SQL
      Section 10.35.  Using Temporary Tables for Data Transformation
      Section 10.36.  Dealing with NULL Values
      Section 10.37.  Guessing Table Structure from a Datafile
      Section 10.38.  A LOAD DATA Diagnostic Utility
      Section 10.39.  Exchanging Data Between MySQL and Microsoft Access
      Section 10.40.  Exchanging Data Between MySQL and Microsoft Excel
      Section 10.41.  Exchanging Data Between MySQL and FileMaker Pro
      Section 10.42.  Exporting Query Results as XML
      Section 10.43.  Importing XML into MySQL
      Section 10.44.  Epilog
   
    Chapter 11.  Generating and Using Sequences
      Section 11.1.  Introduction
      Section 11.2.  Using AUTO_INCREMENT To Set Up a Sequence Column
      Section 11.3.  Generating Sequence Values
      Section 11.4.  Choosing the Type for a Sequence Column
      Section 11.5.  The Effect of Record Deletions on Sequence Generation
      Section 11.6.  Retrieving Sequence Values
      Section 11.7.  Determining Whether to Resequence a Column
      Section 11.8.  Extending the Range of a Sequence Column
      Section 11.9.  Renumbering an Existing Sequence
      Section 11.10.  Reusing Values at the Top of a Sequence
      Section 11.11.  Ensuring That Rows Are Renumbered in a Particular Order
      Section 11.12.  Starting a Sequence at a Particular Value
      Section 11.13.  Sequencing an Unsequenced Table
      Section 11.14.  Using an AUTO_INCREMENT Column to Create Multiple Sequences
      Section 11.15.  Managing Multiple SimultaneousAUTO_INCREMENT Values
      Section 11.16.  Using AUTO_INCREMENT Valuesto Relate Tables
      Section 11.17.  Using Single-Row Sequence Generators
      Section 11.18.  Generating Repeating Sequences
      Section 11.19.  Numbering Query Output Rows Sequentially
   
    Chapter 12.  Using Multiple Tables
      Section 12.1.  Introduction
      Section 12.2.  Combining Rows in One Table with Rows in Another
      Section 12.3.  Performing a Join Between Tables in Different Databases
      Section 12.4.  Referring to Join Output Column Names in Programs
      Section 12.5.  Finding Rows in One Table That Match Rows in Another
      Section 12.6.  Finding Rows with No Match in Another Table
      Section 12.7.  Finding Rows Containing Per-Group Minimum or Maximum Values
      Section 12.8.  Computing Team Standings
      Section 12.9.  Producing Master-Detail Lists and Summaries
      Section 12.10.  Using a Join to Fill in Holes in a List
      Section 12.11.  Enumerating a Many-to-Many Relationship
      Section 12.12.  Comparing a Table to Itself
      Section 12.13.  Calculating Differences Between Successive Rows
      Section 12.14.  Finding Cumulative Sums and Running Averages
      Section 12.15.  Using a Join to Control Query Output Order
      Section 12.16.  Converting Subselects to Join Operations
      Section 12.17.  Selecting Records in Parallel from Multiple Tables
      Section 12.18.  Inserting Records in One Table That Include Values from Another
      Section 12.19.  Updating One Table Based on Values in Another
      Section 12.20.  Using a Join to Create a Lookup Table from Descriptive Labels
      Section 12.21.  Deleting Related Rows in Multiple Tables
      Section 12.22.  Identifying and Removing Unattached Records
      Section 12.23.  Using Different MySQL Servers Simultaneously
   
    Chapter 13.  Statistical Techniques
      Section 13.1.  Introduction
      Section 13.2.  Calculating Descriptive Statistics
      Section 13.3.  Per-Group Descriptive Statistics
      Section 13.4.  Generating Frequency Distributions
      Section 13.5.  Counting Missing Values
      Section 13.6.  Calculating Linear Regressions or Correlation Coefficients
      Section 13.7.  Generating Random Numbers
      Section 13.8.  Randomizing a Set of Rows
      Section 13.9.  Selecting Random Items from a Set of Rows
      Section 13.10.  Assigning Ranks
   
    Chapter 14.  Handling Duplicates
      Section 14.1.  Introduction
      Section 14.2.  Preventing Duplicates from Occurring in a Table
      Section 14.3.  Dealing with Duplicates at Record-Creation Time
      Section 14.4.  Counting and Identifying Duplicates
      Section 14.5.  Eliminating Duplicates from a Query Result
      Section 14.6.  Eliminating Duplicates from a Self-Join Result
      Section 14.7.  Eliminating Duplicates from a Table
   
    Chapter 15.  Performing Transactions
      Section 15.1.  Introduction
      Section 15.2.  Verifying Transaction Support Requirements
      Section 15.3.  Performing Transactions Using SQL
      Section 15.4.  Performing Transactions from Within Programs
      Section 15.5.  Using Transactions in Perl Programs
      Section 15.6.  Using Transactions in PHP Programs
      Section 15.7.  Using Transactions in Python Programs
      Section 15.8.  Using Transactions in Java Programs
      Section 15.9.  Using Alternatives to Transactions
   
    Chapter 16.  Introduction to MySQL on the Web
      Section 16.1.  Introduction
      Section 16.2.  Basic Web Page Generation
      Section 16.3.  Using Apache to Run Web Scripts
      Section 16.4.  Using Tomcat to Run Web Scripts
      Section 16.5.  Encoding Special Characters in Web Output
   
    Chapter 17.  Incorporating Query Resultsinto Web Pages
      Section 17.1.  Introduction
      Section 17.2.  Displaying Query Results as Paragraph Text
      Section 17.3.  Displaying Query Results as Lists
      Section 17.4.  Displaying Query Results as Tables
      Section 17.5.  Displaying Query Results as Hyperlinks
      Section 17.6.  Creating a Navigation Index from Database Content
      Section 17.7.  Storing Images or Other Binary Data
      Section 17.8.  Retrieving Images or Other Binary Data
      Section 17.9.  Serving Banner Ads
      Section 17.10.  Serving Query Results for Download
   
    Chapter 18.  Processing Web Input with MySQL
      Section 18.1.  Introduction
      Section 18.2.  Creating Forms in Scripts
      Section 18.3.  Creating Single-Pick Form Elements from Database Content
      Section 18.4.  Creating Multiple-Pick Form Elements from Database Content
      Section 18.5.  Loading a Database Record into a Form
      Section 18.6.  Collecting Web Input
      Section 18.7.  Validating Web Input
      Section 18.8.  Using Web Input to Construct Queries
      Section 18.9.  Processing File Uploads
      Section 18.10.  Performing Searches and Presenting the Results
      Section 18.11.  Generating Previous-Page and Next-Page Links
      Section 18.12.  Generating "Click to Sort" Table Headings
      Section 18.13.  Web Page Access Counting
      Section 18.14.  Web Page Access Logging
      Section 18.15.  Using MySQL for Apache Logging
   
    Chapter 19.  Using MySQL-Based Web Session Management
      Section 19.1.  Introduction
      Section 19.2.  Using MySQL-Based Sessions in Perl Applications
      Section 19.3.  Using MySQL-Based Storage with the PHP Session Manager
      Section 19.4.  Using MySQL for Session BackingStore with Tomcat
   
    Appendix A.  Obtaining MySQL Software
      Section A.1.  Obtaining Sample Source Code and Data
      Section A.2.  Obtaining MySQL and Related Software
   
    Appendix B.  JSP and Tomcat Primer
      Section B.1.  Servlet and JavaServer Pages Overview
      Section B.2.  Setting Up a Tomcat Server
      Section B.3.  Web Application Structure
      Section B.4.  Elements of JSP Pages
   
    Appendix C.  References
      Section C.1.  MySQL Resources
      Section C.2.  Perl Resources
      Section C.3.  PHP Resources
      Section C.4.  Python Resources
      Section C.5.  Java Resources
      Section C.6.  Apache Resources
      Section C.7.  Other Resources
   
    Colophon
    Index
I l@ve RuBoard Previous Section Next Section