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

Have one to sell? Sell yours here
Tell the Publisher!
I'd like to read this book on Kindle

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 Comparison; 10.23 Validation by Pattern Matching; 10.24 Using Patterns to Match Broad Content Types; 10.25 Using Patterns to Match Numeric Values; 10.26 Using Patterns to Match Dates or Times; 10.27 Using Patterns to Match Email Addresses and URLs; 10.28 Validation Using Table Metadata; 10.29 Validation Using a Lookup Table; 10.30 Converting Two-Digit Year Values to Four-Digit Form; 10.31 Performing Validity Checking on Date or Time Subparts; 10.32 Writing Date-Processing Utilities; 10.33 Using Dates with Missing Components; 10.34 Performing Date Conversion Using SQL; 10.35 Using Temporary Tables for Data Transformation; 10.36 Dealing with NULL Values; 10.37 Guessing Table Structure from a Datafile; 10.38 A LOAD DATA Diagnostic Utility; 10.39 Exchanging Data Between MySQL and Microsoft Access; 10.40 Exchanging Data Between MySQL and Microsoft Excel; 10.41 Exchanging Data Between MySQL and FileMaker Pro; 10.42 Exporting Query Results as XML; 10.43 Importing XML into MySQL; 10.44 Epilog; Chapter 11: Generating and Using Sequences; 11.1 Introduction; 11.2 Using AUTO_INCREMENT To Set Up a Sequence Column; 11.3 Generating Sequence Values; 11.4 Choosing the Type for a Sequence Column; 11.5 The Effect of Record Deletions on Sequence Generation; 11.6 Retrieving Sequence Values; 11.7 Determining Whether to Resequence a Column; 11.8 Extending the Range of a Sequence Column; 11.9 Renumbering an Existing Sequence; 11.10 Reusing Values at the Top of a Sequence; 11.11 Ensuring That Rows Are Renumbered in a Particular Order; 11.12 Starting a Sequence at a Particular Value; 11.13 Sequencing an Unsequenced Table; 11.14 Using an AUTO_INCREMENT Column to Create Multiple Sequences; 11.15 Managing Multiple SimultaneousAUTO_INCREMENT Values; 11.16 Using AUTO_INCREMENT Valuesto Relate Tables; 11.17 Using Single-Row Sequence Generators; 11.18 Generating Repeating Sequences; 11.19 Numbering Query Output Rows Sequentially; Chapter 12: Using Multiple Tables; 12.1 Introduction; 12.2 Combining Rows in One Table with Rows in Another; 12.3 Performing a Join Between Tables in Different Databases; 12.4 Referring to Join Output Column Names in Programs; 12.5 Finding Rows in One Table That Match Rows in Another; 12.6 Finding Rows with No Match in Another Table; 12.7 Finding Rows Containing Per-Group Minimum or Maximum Values; 12.8 Computing Team Standings; 12.9 Producing Master-Detail Lists and Summaries; 12.10 Using a Join to Fill in Holes in a List; 12.11 Enumerating a Many-to-Many Relationship; 12.12 Comparing a Table to Itself; 12.13 Calculating Differences Between Successive Rows; 12.14 Finding Cumulative Sums and Running Averages; 12.15 Using a Join to Control Query Output Order; 12.16 Converting Subselects to Join Operations; 12.17 Selecting Records in Parallel from Multiple Tables; 12.18 Inserting Records in One Table That Include Values from Another; 12.19 Updating One Table Based on Values in Another; 12.20 Using a Join to Create a Lookup Table from Descriptive Labels; 12.21 Deleting Related Rows in Multiple Tables; 12.22 Identifying and Removing Unattached Records; 12.23 Using Different MySQL Servers Simultaneously; Chapter 13: Statistical Techniques; 13.1 Introduction; 13.2 Calculating Descriptive Statistics; 13.3 Per-Group Descriptive Statistics; 13.4 Generating Frequency Distributions; 13.5 Counting Missing Values; 13.6 Calculating Linear Regressions or Correlation Coefficients; 13.7 Generating Random Numbers; 13.8 Randomizing a Set of Rows; 13.9 Selecting Random Items from a Set of Rows; 13.10 Assigning Ranks; Chapter 14: Handling Duplicates; 14.1 Introduction; 14.2 Preventing Duplicates from Occurring in a Table; 14.3 Dealing with Duplicates at Record-Creation Time; 14.4 Counting and Identifying Duplicates; 14.5 Eliminating Duplicates from a Query Result; 14.6 Eliminating Duplicates from a Self-Join Result; 14.7 Eliminating Duplicates from a Table; Chapter 15: Performing Transactions; 15.1 Introduction; 15.2 Verifying Transaction Support Requirements; 15.3 Performing Transactions Using SQL; 15.4 Performing Transactions from Within Programs; 15.5 Using Transactions in Perl Programs; 15.6 Using Transactions in PHP Programs; 15.7 Using Transactions in Python Programs; 15.8 Using Transactions in Java Programs; 15.9 Using Alternatives to Transactions; Chapter 16: Introduction to MySQL on the Web; 16.1 Introduction; 16.2 Basic Web Page Generation; 16.3 Using Apache to Run Web Scripts; 16.4 Using Tomcat to Run Web Scripts; 16.5 Encoding Special Characters in Web Output; Chapter 17: Incorporating Query Resultsinto Web Pages; 17.1 Introduction; 17.2 Displaying Query Results as Paragraph Text; 17.3 Displaying Query Results as Lists; 17.4 Displaying Query Results as Tables; 17.5 Displaying Query Results as Hyperlinks; 17.6 Creating a Navigation Index from Database Content; 17.7 Storing Images or Other Binary Data; 17.8 Retrieving Images or Other Binary Data; 17.9 Serving Banner Ads; 17.10 Serving Query Results for Download; Chapter 18: Processing Web Input with MySQL; 18.1 Introduction; 18.2 Creating Forms in Scripts; 18.3 Creating Single-Pick Form Elements from Database Content; 18.4 Creating Multiple-Pick Form Elements from Database Content; 18.5 Loading a Database Record into a Form; 18.6 Collecting Web Input; 18.7 Validating Web Input; 18.8 Using Web Input to Construct Queries; 18.9 Processing File Uploads; 18.10 Performing Searches and Presenting the Results; 18.11 Generating Previous-Page and Next-Page Links; 18.12 Generating “Click to Sort” Table Headings; 18.13 Web Page Access Counting; 18.14 Web Page Access Logging; 18.15 Using MySQL for Apache Logging; Chapter 19: Using MySQL-Based Web Session Management; 19.1 Introduction; 19.2 Using MySQL-Based Sessions in Perl Applications; 19.3 Using MySQL-Based Storage with the PHP Session Manager; 19.4 Using MySQL for Session BackingStore with Tomcat; Obtaining MySQL Software; Obtaining Sample Source Code and Data; Obtaining MySQL and Related Software; JSP and Tomcat Primer; Servlet and JavaServer Pages Overview; Setting Up a Tomcat Server; Web Application Structure; Elements of JSP Pages; References; MySQL Resources; Perl Resources; PHP Resources; Python Resources; Java Resources; Apache Resources; Other Resources; Colophon;

‹  Return to Product Overview