MySQL Cookbook and over one million other books are available for Amazon Kindle. Learn more

Vous voulez voir cette page en français ? Cliquez ici.

Have one to sell? Sell yours here
Start reading MySQL Cookbook on your Kindle in under a minute.

Don't have a Kindle? Get your Kindle here, or download a FREE Kindle Reading App.

MySQL Cookbook [Paperback]

Paul DuBois
4.8 out of 5 stars  See all reviews (8 customer reviews)

Available from these sellers.

‹  Return to Product Overview

Table of Contents

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; 1.1 Introduction; 1.2 Setting Up a MySQL User Account; 1.3 Creating a Database and a Sample Table; 1.4 Starting and Terminating mysql; 1.5 Specifying Connection Parameters by Using Option Files; 1.6 Protecting Option Files; 1.7 Mixing Command-Line and Option File Parameters; 1.8 What to Do if mysql Cannot Be Found; 1.9 Setting Environment Variables; 1.10 Issuing Queries; 1.11 Selecting a Database; 1.12 Canceling a Partially Entered Query; 1.13 Repeating and Editing Queries; 1.14 Using Auto-Completion for Database and Table Names; 1.15 Using SQL Variables in Queries; 1.16 Telling mysql to Read Queries from a File; 1.17 Telling mysql to Read Queries from Other Programs; 1.18 Specifying Queries on the Command Line; 1.19 Using Copy and Paste as a mysql Input Source; 1.20 Preventing Query Output from Scrolling off the Screen; 1.21 Sending Query Output to a File or to a Program; 1.22 Selecting Tabular or Tab-Delimited Query Output Format; 1.23 Specifying Arbitrary Output Column Delimiters; 1.24 Producing HTML Output; 1.25 Producing XML Output; 1.26 Suppressing Column Headings in Query Output; 1.27 Numbering Query Output Lines; 1.28 Making Long Output Lines More Readable; 1.29 Controlling mysql’s Verbosity Level; 1.30 Logging Interactive mysql Sessions; 1.31 Creating mysql Scripts from Previously Executed Queries; 1.32 Using mysql as a Calculator; 1.33 Using mysql in Shell Scripts; Chapter 2: Writing MySQL-Based Programs; 2.1 Introduction; 2.2 Connecting to the MySQL Server, Selecting a Database, and Disconnecting; 2.3 Checking for Errors; 2.4 Writing Library Files; 2.5 Issuing Queries and Retrieving Results; 2.6 Moving Around Within a Result Set; 2.7 Using Prepared Statements and Placeholders in Queries; 2.8 Including Special Characters and NULL Values in Queries; 2.9 Handling NULL Values in Result Sets; 2.10 Writing an Object-Oriented MySQL Interface for PHP; 2.11 Ways of Obtaining Connection Parameters; 2.12 Conclusion and Words of Advice; Chapter 3: Record Selection Techniques; 3.1 Introduction; 3.2 Specifying Which Columns to Display; 3.3 Avoiding Output Column Order Problems When Writing Programs; 3.4 Giving Names to Output Columns; 3.5 Using Column Aliases to Make Programs Easier to Write; 3.6 Combining Columns to Construct Composite Values; 3.7 Specifying Which Rows to Select; 3.8 WHERE Clauses and Column Aliases; 3.9 Displaying Comparisons to Find Out How Something Works; 3.10 Reversing or Negating Query Conditions; 3.11 Removing Duplicate Rows; 3.12 Working with NULL Values; 3.13 Negating a Condition on a Column That Contains NULL Values; 3.14 Writing Comparisons Involving NULL in Programs; 3.15 Mapping NULL Values to Other Values for Display; 3.16 Sorting a Result Set; 3.17 Selecting Records from the Beginning or End of a Result Set; 3.18 Pulling a Section from the Middle of a Result Set; 3.19 Choosing Appropriate LIMIT Values; 3.20 Calculating LIMIT Values from Expressions; 3.21 What to Do When LIMIT Requires the “Wrong” Sort Order; 3.22 Selecting a Result Set into an Existing Table; 3.23 Creating a Destination Table on the Fly from a Result Set; 3.24 Moving Records Between Tables Safely; 3.25 Creating Temporary Tables; 3.26 Cloning a Table Exactly; 3.27 Generating Unique Table Names; Chapter 4: Working with Strings; 4.1 Introduction; 4.2 Writing Strings That Include Quotes or Special Characters; 4.3 Preserving Trailing Spaces in String Columns; 4.4 Testing String Equality or Relative Ordering; 4.5 Decomposing or Combining Strings; 4.6 Checking Whether a String Contains a Substring; 4.7 Pattern Matching with SQL Patterns; 4.8 Pattern Matching with Regular Expressions; 4.9 Matching Pattern Metacharacters Literally; 4.10 Controlling Case Sensitivity in String Comparisons; 4.11 Controlling Case Sensitivity in Pattern Matching; 4.12 Using FULLTEXT Searches; 4.13 Using a FULLTEXT Search with Short Words; 4.14 Requiring or Excluding FULLTEXT Search Words; 4.15 Performing Phrase Searches with a FULLTEXT Index; Chapter 5: Working with Dates and Times; 5.1 Introduction; 5.2 Changing MySQL’s Date Format; 5.3 Telling MySQL How to Display Dates or Times; 5.4 Determining the Current Date or Time; 5.5 Decomposing Dates and Times Using Formatting Functions; 5.6 Decomposing Dates or Times Using Component-Extraction Functions; 5.7 Decomposing Dates or Times Using String Functions; 5.8 Synthesizing Dates or Times Using Formatting Functions; 5.9 Synthesizing Dates or Times Using Component-Extraction Functions; 5.10 Combining a Date and a Time into a Date-and-Time Value; 5.11 Converting Between Times and Seconds; 5.12 Converting Between Dates and Days; 5.13 Converting Between Date-and-Time Values and Seconds; 5.14 Adding a Temporal Interval to a Time; 5.15 Calculating Intervals Between Times; 5.16 Breaking Down Time Intervals into Components; 5.17 Adding a Temporal Interval to a Date; 5.18 Calculating Intervals Between Dates; 5.19 Canonizing Not-Quite-ISO Date Strings; 5.20 Calculating Ages; 5.21 Shifting Dates by a Known Amount; 5.22 Finding First and Last Days of Months; 5.23 Finding the Length of a Month; 5.24 Calculating One Date from Another by Substring Replacement; 5.25 Finding the Day of the Week for a Date; 5.26 Finding Dates for Days of the Current Week; 5.27 Finding Dates for Weekdays of Other Weeks; 5.28 Performing Leap Year Calculations; 5.29 Treating Dates or Times as Numbers; 5.30 Forcing MySQL to Treat Strings as Temporal Values; 5.31 Selecting Records Based on Their Temporal Characteristics; 5.32 Using TIMESTAMP Values; 5.33 Recording a Row’s Last Modification Time; 5.34 Recording a Row’s Creation Time; 5.35 Performing Calculations with TIMESTAMP Values; 5.36 Displaying TIMESTAMP Values in Readable Form; Chapter 6: Sorting Query Results; 6.1 Introduction; 6.2 Using ORDER BY to Sort Query Results; 6.3 Sorting Subsets of a Table; 6.4 Sorting Expression Results; 6.5 Displaying One Set of Values While Sorting by Another; 6.6 Sorting and NULL Values; 6.7 Controlling Case Sensitivity of String Sorts; 6.8 Date-Based Sorting; 6.9 Sorting by Calendar Day; 6.10 Sorting by Day of Week; 6.11 Sorting by Time of Day; 6.12 Sorting Using Substrings of Column Values; 6.13 Sorting by Fixed-Length Substrings; 6.14 Sorting by Variable-Length Substrings; 6.15 Sorting Hostnames in Domain Order; 6.16 Sorting Dotted-Quad IP Values in Numeric Order; 6.17 Floating Specific Values to the Head or Tail of the Sort Order; 6.18 Sorting in User-Defined Orders; 6.19 Sorting ENUM Values; Chapter 7: Generating Summaries; 7.1 Introduction; 7.2 Summarizing with COUNT( ); 7.3 Summarizing with MIN( ) and MAX( ); 7.4 Summarizing with SUM( ) and AVG( ); 7.5 Using DISTINCT to Eliminate Duplicates; 7.6 Finding Values Associated with Minimum and Maximum Values; 7.7 Controlling String Case Sensitivity for MIN( ) and MAX( ); 7.8 Dividing a Summary into Subgroups; 7.9 Summaries and NULL Values; 7.10 Selecting Only Groups with Certain Characteristics; 7.11 Determining Whether Values are Unique; 7.12 Grouping by Expression Results; 7.13 Categorizing Non-Categorical Data; 7.14 Controlling Summary Display Order; 7.15 Finding Smallest or Largest Summary Values; 7.16 Date-Based Summaries; 7.17 Working with Per-Group and Overall Summary Values Simultaneously; 7.18 Generating a Report That Includes a Summary and a List; Chapter 8: Modifying Tables with ALTER TABLE; 8.1 Introduction; 8.2 Dropping, Adding, or Repositioning a Column; 8.3 Changing a Column Definition or Name; 8.4 The Effect of ALTER TABLE on Null and Default Value Attributes; 8.5 Changing a Column’s Default Value; 8.6 Changing a Table Type; 8.7 Renaming a Table; 8.8 Adding or Dropping Indexes; 8.9 Eliminating Duplicates by Adding an Index; 8.10 Using ALTER TABLE to Normalize a Table; Chapter 9: Obtaining and Using Metadata; 9.1 Introduction; 9.2 Obtaining the Number of Rows Affected by a Query; 9.3 Obtaining Result Set Metadata; 9.4 Determining Presence or Absence of a Result Set; 9.5 Formatting Query Results for Display; 9.6 Getting Table Structure Information; 9.7 Getting ENUM and SET Column Information; 9.8 Database-Independent Methods of Obtaining Table Information; 9.9 Applying Table Structure Information; 9.10 Listing Tables and Databases; 9.11 Testing Whether a Table Exists; 9.12 Testing Whether a Database Exists; 9.13 Getting Server Metadata; 9.14 Writing Applications That Adapt to the MySQL Server Version; 9.15 Determining the Current Database; 9.16 Determining the Current MySQL User; 9.17 Monitoring the MySQL Server; 9.18 Determining Which Table Types the Server Supports; Chapter 10: Importing and Exporting Data; 10.1 Introduction; 10.2 Importing Data with LOAD DATA and mysqlimport; 10.3 Specifying the Datafile Location; 10.4 Specifying the Datafile Format; 10.5 Dealing with Quotes and Special Characters; 10.6 Importing CSV Files; 10.7 Reading Files from Different Operating Systems; 10.8 Handling Duplicate Index Values; 10.9 Getting LOAD DATA to Cough Up More Information; 10.10 Don’t Assume LOAD DATA Knows More than It Does; 10.11 Skipping Datafile Lines; 10.12 Specifying Input Column Order; 10.13 Skipping Datafile Columns; 10.14 Exporting Query Results from MySQL; 10.15 Exporting Tables as Raw Data; 10.16 Exporting Table Contents or Definitions in SQL Format; 10.17 Copying Tables or Databases to Another Server; 10.18 Writing Your Own Export Programs; 10.19 Converting Datafiles from One Format to Another; 10.20 Extracting and Rearranging Datafile Columns; 10.21 Validating and Transforming Data; 10.22 Validation by Direct C...

‹  Return to Product Overview