MySQL Cookbook - Brossura

Dubois, Paul

Brossura

ISBN 10: 0596001452 ISBN 13: 9780596001452

Casa editrice: O'Reilly, 2002

Riassunto

While MySQL has turned up among high profile users such as Yahoo!, NASA and the U.S. Census Bureau, the rising popularity of this open source database is especially keen among users with little database experience. These days, even a small organization or web site has uses for a database, and MySQL is an obvious choice. Affordable and easy to use, MySQL packs the power, speed and efficiency that enable it to rival expensive, proprietary database solutions. Yet, even if you know the basics, anyone without practical MySQL experience--novices and skilled DBAs alike--might stumble over common database-related tasks. Fortunately, there's a sensible shortcut.MySQL Cookbook provides a unique problem-and-solution format that offers practical examples for everyday programming dilemmas. For every problem addressed in the book, there's a worked-out solution or "recipe"--short, focused pieces of code that you can insert directly into your applications. But MySQL Cookbook is more than a collection of cut-and-paste code. You also get explanations of how and why the code works, so you can learn to adapt the techniques to similar situations.The book covers a lot of ground. Solutions for typical MySQL dilemmas range from simple ways to find all records that contain a given string, to more difficult problems, such as finding matching/non-matching records in two tables. Whether you use MySQL on Unix, Linux, Windows or the Mac OS X platform, the book will show you how to:

• Import data from external sources
• Export data for use by external programs
• Access MySQL from your web server
• Use scripts with MySQL to read queries from a file
• Access MySQL from within client programs that use Perl, PHP, Java, Python and other languages
• Construct queries that solve commonly-occurring questions
• Interact with the server

This learn-as-you-go resource will help users of all levels exploit MySQL more fully. MySQL Cookbook supplies you with an armory of ready-made techniques for specific problems so that, even if you're an experienced MySQL user, you don't have to write everything from scratch.

Su questo libro

L'autore:

is one of the primary contributors to the MySQL Reference Manual, a renowned online manual that has supported MySQL administrators and database developers for years, now available in an attractive paper format from the O'Reilly Community Press. He is also the author of Using csh & tcsh and Software Portability with imake by O'Reilly, as well as MySQL and MySQL and Perl for the Web by New Riders.

Contenuti:

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 Resour...

Su questa edizione

• Editore: O'Reilly
• Data di pubblicazione: 2002
• ISBN 10: 0596001452
• ISBN 13: 9780596001452
• Rilegatura: Copertina flessibile
• Numero di pagine: 990