MySQL Cookbook. Solutions for Database Developers and Administrators. 3rd Edition

Spis treści ebooka

• MySQL Cookbook
• Preface
  • Who This Book Is For
  • Whats in This Book
  • MySQL APIs Used in This Book
  • Version and Platform Notes
  • Conventions Used in This Book
  • The MySQL Cookbook Companion Website
  • Obtaining MySQL and Related Software
  • Using Code Examples
  • Safari Books Online
  • How to Contact Us
  • Acknowledgments
• 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
  • Introduction
  • 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
• 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 Strings 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 MySQLs 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
  • Introduction
  • 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
• 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
  • Introduction
  • Basic Principles of Web Page Generation
  • Using Apache to Run Web Scripts
  • Using Tomcat to Run Web Scripts
  • Encoding Special Characters in Web Output
• 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
  • Introduction
  • 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
• 22. Server Administration
  • Introduction
  • Configuring the Server
  • Managing the Plug-In Interface
  • Controlling Server Logging
  • Rotating or Expiring Logfiles
  • Rotating Log Tables or Expiring Log Table Rows
  • Monitoring the MySQL Server
  • Creating and Using Backups
• 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

pokaż cały spis treści

ukryj recenzje