Book description
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.
Ideal for beginners and professional database and web developers, this updated third edition covers powerful features in MySQL 5.6 (and some in 5.7). The book focuses on programming APIs in Python, PHP, Java, Perl, and Ruby. With more than 200+ recipes, youâ??ll learn how to:
- Use the mysql client and write MySQL-based programs
- Create, populate, and select data from tables
- Store, retrieve, and manipulate strings
- Work with dates and times
- Sort query results and generate summaries
- Use stored routines, triggers, and scheduled events
- Import, export, validate, and reformat data
- Perform transactions and work with statistics
- Process web input, and generate web content from query results
- Use MySQL-based web session management
- Provide security and server administration
Publisher resources
Table of contents
- Preface
-
1. Using the mysql Client Program
- Introduction
- 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
-
2. Writing MySQL-Based Programs
- Introduction
- 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
-
3. Selecting Data from Tables
- Introduction
- 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
- 4. Table Management
-
5. Working with Strings
- Introduction
- 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
-
6. Working with Dates and Times
- Introduction
- 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
-
7. Sorting Query Results
- Introduction
- 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
-
8. Generating Summaries
- Introduction
- 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
-
9. Using Stored Routines, Triggers, and Scheduled Events
- Introduction
- 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
-
10. Working with Metadata
- Introduction
- 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
-
11. Importing and Exporting Data
- Introduction
- 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
-
12. Validating and Reformatting Data
- Introduction
- 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
- Epilogue
-
13. Generating and Using Sequences
- Introduction
- 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
-
14. Using Joins and Subqueries
- Introduction
- 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
-
15. 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
- Calculating Successive-Row Differences
- Finding Cumulative Sums and Running Averages
- Assigning Ranks
- Computing Team Standings
- 16. Handling Duplicates
-
17. Performing Transactions
- Introduction
- 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
- 18. Introduction to MySQL on the Web
-
19. Generating Web Content from Query Results
- Introduction
- 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
-
20. Processing Web Input with MySQL
- Introduction
- 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
- 21. Using MySQL-Based Web Session Management
- 22. Server Administration
-
23. Security
- Introduction
- 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
- Index
- Colophon
- Copyright
Product information
- Title: MySQL Cookbook, 3rd Edition
- Author(s):
- Release date: August 2014
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9781449374020
You might also like
book
MySQL Cookbook, 2nd Edition
Along with MySQL's popularity has come a flood of questions about solving specific problems, and that's …
book
MySQL Cookbook, 4th Edition
For MySQL, the price of popularity comes with a flood of questions from users on how …
book
MySQL, 5th Edition
MySQL, Fifth Edition by Paul DuBois The definitive guide to using, programming and administering MySQL 5.5 …
book
Learning MySQL, 2nd Edition
Get a comprehensive overview on how to set up and design an effective database with MySQL. …