MySQL Cookbook – Paul DuBois – 1st Edition


MySQL’s popularity has brought a flood of questions about how to solve specific problems, and that’s where this cookbook is essential. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations for programmers who don’t have the time (or expertise) to solve MySQL problems from scratch.

View more

  • Chapter 1: Using the mysql Client Program
    Setting Up a MySQL User Account
    Creating a Database and a Sample Table
    What to Do if mysql Cannot Be Found
    Specifying mysql Command Options
    Executing SQL Statements Interactively
    Executing SQL Statements Read from a File or Program
    Controlling mysql Output Destination and Format
    Using User-Defined Variables in SQL Statements

    Chapter 2: Writing MySQL-Based Programs
    Connecting, Selecting a Database, and Disconnecting
    Checking for Errors
    Writing Library Files
    Executing Statements and Retrieving Results
    Handling Special Characters and NULL Values in Statements
    Handling Special Characters in Identifiers
    Identifying NULL Values in Result Sets
    Techniques for Obtaining Connection Parameters
    Conclusion and Words of Advice

    Chapter 3: Selecting Data from Tables
    Specifying Which Columns and Rows to Select
    Naming Query Result Columns
    Sorting Query Results
    Removing Duplicate Rows
    Working with NULL Values
    Writing Comparisons Involving NULL in Programs
    Using Views to Simplify Table Access
    Selecting Data from Multiple Tables
    Selecting Rows from the Beginning, End, or Middle of Query Results
    What to Do When LIMIT Requires the Wrong Sort Order
    Calculating LIMIT Values from Expressions

    Chapter 4: Table Management
    Cloning a Table
    Saving a Query Result in a Table
    Creating Temporary Tables
    Generating Unique Table Names
    Checking or Changing a Table Storage Engine
    Copying a Table Using mysqldump

    Chapter 5: Working with Strings
    String Properties
    Choosing a String Data Type
    Setting the Client Connection Character Set
    Writing String Literals
    Checking or Changing a String's Character Set or Collation
    Converting the Lettercase of a String
    Controlling Case Sensitivity in String Comparisons
    Pattern Matching with SQL Patterns
    Pattern Matching with Regular Expressions
    Breaking Apart or Combining Strings
    Searching for Substrings
    Using Full-Text Searches
    Using a Full-Text Search with Short Words
    Requiring or Prohibiting Full-Text Search Words
    Performing Full-Text Phrase Searches

    Chapter 6: Working with Dates and Times
    Choosing a Temporal Data Type
    Using Fractional Seconds Support
    Changing MySQL's Date Format
    Setting the Client Time Zone
    Shifting Temporal Values Between Time Zones
    Determining the Current Date or Time
    Using TIMESTAMP or DATETIME to Track Row-Modification Times
    Extracting Parts of Dates or Times
    Synthesizing Dates or Times from Component Values
    Converting Between Temporal Values and Basic Units
    Calculating Intervals Between Dates or Times
    Adding Date or Time Values
    Calculating Ages
    Finding the First Day, Last Day, or Length of a Month
    Calculating Dates by Substring Replacement
    Finding the Day of the Week for a Date
    Finding Dates for Any Weekday of a Given Week
    Performing Leap-Year Calculations
    Canonizing Not-Quite-ISO Date Strings
    Selecting Rows Based on Temporal Characteristics

    Chapter 7: Sorting Query Results
    Using ORDER BY to Sort Query Results
    Using Expressions for Sorting
    Displaying One Set of Values While Sorting by Another
    Controlling Case Sensitivity of String Sorts
    Date-Based Sorting
    Sorting by 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 Values to the Head or Tail of the Sort Order
    Defining a Custom Sort Order
    Sorting ENUM Values

    Chapter 8: Generating Summaries
    Basic Summary Techniques
    Creating a View to Simplify Using a Summary
    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
    Using Counts to Determine Whether Values Are Unique
    Grouping by Expression Results
    Summarizing Noncategorical Data
    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

    Chapter 9: Using Stored Routines, Triggers, and Scheduled Events
    Creating Compound-Statement Objects
    Using Stored Functions to Encapsulate Calculations
    Using Stored Procedures to Return Multiple Values
    Using Triggers to Implement Dynamic Default Column Values
    Using Triggers to Simulate Function-Based Indexes
    Simulating TIMESTAMP Properties for Other Date and Time Types
    Using Triggers to Log Changes to a Table
    Using Events to Schedule Database Actions
    Writing Helper Routines for Executing Dynamic SQL
    Handling Errors Within Stored Programs
    Using Triggers to Preprocess or Reject Data

    Chapter 10: Working with Metadata
    Determining the Number of Rows Affected by a Statement
    Obtaining Result Set Metadata
    Determining Whether a Statement Produced a Result Set
    Using Metadata to Format Query Output
    Listing or Checking Existence of Databases or Tables
    Accessing Table Column Definitions
    Getting ENUM and SET Column Information
    Getting Server Metadata
    Writing Applications That Adapt to the MySQL Server Version

    Chapter 11: Importing and Exporting Data
    Importing Data with LOAD DATA and mysqlimport
    Importing CSV Files
    Exporting Query Results from MySQL
    Importing and Exporting NULL Values
    Writing Your Own Data Export Programs
    Converting Datafiles from One Format to Another
    Extracting and Rearranging Datafile Columns
    Exchanging Data Between MySQL and Microsoft Excel
    Exporting Query Results as XML
    Importing XML into MySQL
    Guessing Table Structure from a Datafile

    Chapter 12: Validating and Reformatting Data
    Using the SQL Mode to Reject Bad Input Values
    Validating and Transforming Data
    Using Pattern Matching to Validate Data
    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 or URLs
    Using Table Metadata to Validate Data
    Using a Lookup Table to Validate Data
    Converting Two-Digit Year Values to Four-Digit Form
    Performing Validity Checking on Date or Time Subparts
    Writing Date-Processing Utilities
    Importing Non-ISO Date Values
    Exporting Dates Using Non-ISO Formats

    Chapter 13: Generating and Using Sequences
    Creating a Sequence Column and Generating Sequence Values
    Choosing the Definition for a Sequence Column
    The Effect of Row Deletions on Sequence Generation
    Retrieving Sequence Values
    Renumbering an Existing Sequence
    Extending the Range of a Sequence Column
    Reusing Values at the Top of a Sequence
    Ensuring That Rows Are Renumbered in a Particular Order
    Sequencing an Unsequenced Table
    Managing Multiple Auto-Increment Values Simultaneously
    Using Auto-Increment Values to Associate Tables
    Using Sequence Generators as Counters
    Generating Repeating Sequences

    Chapter 14: Using Joins and Subqueries
    Finding Matches Between Tables
    Finding Mismatches Between Tables
    Identifying and Removing Mismatched or Unattached Rows
    Comparing a Table to Itself
    Producing Master-Detail Lists and Summaries
    Enumerating a Many-to-Many Relationship
    Finding Per-Group Minimum or Maximum Values
    Using a Join to Fill or Identify Holes in a List
    Using a Join to Control Query Sort Order
    Referring to Join Output Column Names in Programs

    Chapter 15: Statistical Techniques
    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
    Calculating Successive-Row Differences
    Finding Cumulative Sums and Running Averages
    Assigning Ranks
    Computing Team Standings

    Chapter 16: Handling Duplicates
    Preventing Duplicates from Occurring in a Table
    Dealing with Duplicates When Loading Rows into a Table
    Counting and Identifying Duplicates
    Eliminating Duplicates from a Table

    Chapter 17: Performing Transactions
    Choosing a Transactional Storage Engine
    Performing Transactions Using SQL
    Performing Transactions from Within Programs
    Using Transactions in Perl Programs
    Using Transactions in Ruby Programs
    Using Transactions in PHP Programs
    Using Transactions in Python Programs
    Using Transactions in Java Programs

    Chapter 18: Introduction to MySQL on the Web
    Basic Principles of Web Page Generation
    Using Apache to Run Web Scripts
    Using Tomcat to Run Web Scripts
    Encoding Special Characters in Web Output

    Chapter 19: Generating Web Content from Query Results
    Displaying Query Results as Paragraphs
    Displaying Query Results as Lists
    Displaying Query Results as Tables
    Displaying Query Results as Hyperlinks
    Creating Navigation Indexes from Database Content
    Storing Images or Other Binary Data
    Serving Images or Other Binary Data
    Serving Banner Ads
    Serving Query Results for Download

    Chapter 20: Processing Web Input with MySQL
    Writing Scripts That Generate Web Forms
    Creating Single-Pick Form Elements from Database Content
    Creating Multiple-Pick Form Elements from Database Content
    Loading Database Content into a Form
    Collecting Web Input
    Validating Web Input
    Storing Web Input in a Database
    Processing File Uploads
    Performing Web-Based Database Searches
    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

    Chapter 21: Using MySQL-Based Web Session Management

    Using MySQL-Based Sessions in Perl Applications
    Using MySQL-Based Storage in Ruby Applications
    Using MySQL-Based Storage with the PHP Session Manager
    Using MySQL for Session-Backing Store with Tomcat

    Chapter 22: Server Administration
    Configuring the Server
    Managing the Plugin Interface
    Controlling Server Logging
    Rotating or Expiring Logfiles
    Rotating Log Tables or Expiring Log Table Rows
    Monitoring the MySQL Server
    Creating and Using Backups

    Chapter 23: Security
    Understanding the mysql.user Table
    Managing User Accounts
    Implementing a Password Policy
    Checking Password Strength
    Expiring Passwords
    Assigning Yourself a New Password
    Resetting an Expired Password
    Finding and Fixing Insecure Accounts
    Disabling Use of Accounts with Pre-4.1 Passwords
    Finding and Removing Anonymous Accounts
    Modifying Any Host and Many Host Accounts
  • Citation

Download now MySQL Cookbook

Type of file
Download RAR
Download PDF
File size
1068 pag.
2 mb

Leave us a comment

No Comments

Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x