Mysql Cookbook

by DuBois, Paul

• ISBN13: 9780596527082
• ISBN10: 059652708X
• Edition: 2nd
• Format: Paperback
• Copyright: 2006-11-01
• Publisher: Oreilly & Associates Inc

Summary

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. More than a collection of cut-and- paste code, this book explanation how and why the code works, so you can learn to adapt the techniques to similar situations.

Table of Contents

Preface	xiii
Using the mysql Client Program	1	(37)
Introduction	1	(1)
Setting Up a MySQL User Account	2	(2)
Creating a Database and a Sample Table	4	(2)
Starting and Stopping mysql	6	(2)
Specifying Connection Parameters Using Option Files	8	(2)
Protecting Option Files from Other Users	10	(1)
Mixing Command-Line and Option File Parameters	11	(1)
What to Do if mysql Cannot Be Found	12	(1)
Issuing SQL Statements	13	(1)
Canceling a Partially Entered Statement	14	(1)
Repeating and Editing SQL Statements	15	(1)
Using Auto-Completion for Database and Table Names	16	(1)
Telling mysql to Read Statements from a File	17	(3)
Telling mysql to Read Statements from Other Programs	20	(1)
Entering an SQL One-Liner	21	(1)
Using Copy and Paste as a mysql Input Source	22	(1)
Preventing Query Output from Scrolling off the Screen	22	(2)
Sending Query Output to a File or to a Program	24	(1)
Selecting Tabular or Tab-Delimited Query Output Format	25	(1)
Specifying Arbitrary Output Column Delimiters	26	(1)
Producing HTML or XML Output	27	(2)
Suppressing Column Headings in Query Output	29	(1)
Making Long Output Lines More Readable	30	(1)
Controlling mysql's Verbosity Level	31	(1)
Logging Interactive mysql Sessions	32	(1)
Creating mysql Scripts from Previously Executed Statements	33	(1)
Using User-Defined Variables in SQL Statements	33	(3)
Numbering Query Output Lines	36	(1)
Using mysql as a Calculator	37	(1)
Using mysql in Shell Scripts	38	(93)
Writing MySQL-Based Programs	45	(1)
Introduction	45	(5)
Connecting, Selecting a Database, and Disconnecting	50	(14)
Checking for Errors	64	(8)
Writing Library Files	72	(13)
Issuing Statements and Retrieving Results	85	(15)
Handling Special Characters and NULL Values in Statements	100	(12)
Handling Special Characters in Identifiers	112	(1)
Identifying NULL Values in Result Sets	113	(4)
Techniques for Obtaining Connection Parameters	117	(12)
Conclusion and Words of Advice	129	(2)
Selecting Data from Tables	131	(30)
Introduction	131	(2)
Specifying Which Columns to Select	133	(1)
Specifying Which Rows to Select	134	(1)
Giving Better Names to Query Result Columns	135	(3)
Using Column Aliases to Make Programs Easier to Write	138	(1)
Combining Columns to Construct Composite Values	139	(1)
Where Clauses and Column Aliases	140	(1)
Debugging Comparison Expressions	141	(1)
Removing Duplicate Rows	142	(1)
Working with NULL Values	143	(2)
Writing Comparisons Involving NULL in Programs	145	(1)
Sorting a Result Set	146	(2)
Using Views to Simplify Table Access	148	(1)
Selecting Data from More Than One Table	149	(2)
Selecting Rows from the Beginning or End of a Result Set	151	(2)
Selecting Rows from the Middle of a Result Set	153	(2)
Choosing Appropriate Limit Values	155	(3)
What to Do When Limit Requires the ``Wrong'' Sort Order	158	(1)
Calculating Limit Values from Expressions	159	(2)
Table Management	161	(10)
Introduction	161	(1)
Cloning a Table	161	(1)
Saving a Query Result in a Table	162	(3)
Creating Temporary Tables	165	(2)
Checking or Changing a Table's Storage Engine	167	(1)
Generating Unique Table Names	168	(3)
Working with Strings	171	(42)
Introduction	171	(1)
String Properties	172	(3)
Choosing a String Data Type	175	(3)
Setting the Client Connection Character Set Properly	178	(1)
Writing String Literals	179	(3)
Checking a String's Character Set or Collation	182	(1)
Changing a String's Character Set or Collation	183	(2)
Converting the Lettercase of a String	185	(1)
Converting the Lettercase of a ``Stubborn'' String	186	(2)
Controlling Case Sensitivity in String Comparisons	188	(3)
Pattern Matching with SQL Patterns	191	(3)
Pattern Matching with Regular Expressions	194	(4)
Controlling Case Sensitivity in Pattern Matching	198	(2)
Breaking Apart or Combining Strings	200	(3)
Searching for Substrings	203	(1)
Using FullText Searches	203	(5)
Using a FullText Search with Short Words	208	(1)
Requiring or Excluding FullText Search Words	209	(2)
Performing Phrase Searches with a FullText Index	211	(2)
Working with Dates and Times	213	(60)
Introduction	213	(1)
Choosing a Temporal Data Type	214	(2)
Changing MySQL's Date Format	216	(4)
Setting the Client Time Zone	220	(2)
Determining the Current Date or Time	222	(1)
Using Timestamp to Track Row Modification Times	223	(3)
Extracting Parts of Dates or Times	226	(6)
Synthesizing Dates or Times from Component Values	232	(2)
Converting Between Temporal Data Types and Basic Units	234	(4)
Calculating the Interval Between Two Dates or Times	238	(5)
Adding Date or Time Values	243	(5)
Calculating Ages	248	(5)
Shifting a Date-and-Time Value to a Different Time Zone	253	(1)
Finding the First Day, Last Day, or Length of a Month	254	(3)
Calculating Dates by Substring Replacement	257	(1)
Finding the Day of the Week for a Date	258	(1)
Finding Dates for Any Weekday of a Given Week	259	(3)
Performing Leap Year Calculations	262	(3)
Canonizing Not-Quite-ISO Date Strings	265	(1)
Treating Dates or Times as Numbers	266	(2)
Forcing MySQL to Treat Strings as Temporal Values	268	(1)
Selecting Rows Based on Their Temporal Characteristics	269	(4)
Sorting Query Results	273	(40)
Introduction	273	(1)
Using Order by to Sort Query Results	274	(4)
Using Expressions for Sorting	278	(2)
Displaying One Set of Values While Sorting by Another	280	(3)
Controlling Case Sensitivity of String Sorts	283	(3)
Date-Based Sorting	286	(2)
Sorting by Calendar Day	288	(2)
Sorting by Day of Week	290	(1)
Sorting by Time of Day	291	(1)
Sorting Using Substrings of Column Values	292	(1)
Sorting by Fixed-Length Substrings	293	(2)
Sorting by Variable-Length Substrings	295	(5)
Sorting Hostnames in Domain Order	300	(2)
Sorting Dotted-Quad IP Values in Numeric Order	302	(2)
Floating Values to the Head or Tail of the Sort Order	304	(4)
Sorting in User-Defined Orders	308	(1)
Sorting ENUM Values	309	(4)
Generating Summaries	313	(40)
Introduction	313	(2)
Summarizing with Count()	315	(3)
Summarizing with Min() and Max()	318	(1)
Summarizing with Sum() and Avg()	319	(2)
Using Distinct to Eliminate Duplicates	321	(2)
Finding Values Associated with Minimum and Maximum Values	323	(2)
Controlling String Case Sensitivity for Min() and Max()	325	(2)
Dividing a Summary into Subgroups	327	(3)
Summaries and NULL Values	330	(3)
Selecting Only Groups with Certain Characteristics	333	(1)
Using Counts to Determine Whether Values Are Unique	334	(1)
Grouping by Expression Results	335	(1)
Categorizing Noncategorical Data	336	(4)
Controlling Summary Display Order	340	(2)
Finding Smallest or Largest Summary Values	342	(2)
Date-Based Summaries	344	(2)
Working with Per-Group and Overall Summary Values Simultaneously	346	(3)
Generating a Report That Includes a Summary and a List	349	(4)
Obtaining and Using Metadata	353	(42)
Introduction	353	(2)
Obtaining the Number of Rows Affected by a Statement	355	(2)
Obtaining Result Set Metadata	357	(10)
Determining Whether a Statement Produced a Result Set	367	(1)
Using Metadata to Format Query Output	368	(4)
Listing or Checking Existence of Databases or Tables	372	(2)
Accessing Table Column Definitions	374	(7)
Getting ENUM and SET Column Information	381	(2)
Using Table Structure Information in Applications	383	(5)
Getting Server Metadata	388	(1)
Writing Applications That Adapt to the MySQL Server Version	389	(1)
Determining the Default Database	390	(1)
Monitoring the MySQL Server	391	(2)
Determining Which Storage Engines the Server Supports	393	(2)
Importing and Exporting Data	395	(86)
Introduction	395	(4)
Importing Data with Load Data and mysqlimport	399	(2)
Specifying the Datafile Location	401	(2)
Specifying the Structure of the Datafile	403	(2)
Dealing with Quotes and Special Characters	405	(1)
Importing CSV Files	406	(1)
Reading Files from Different Operating Systems	407	(1)
Handling Duplicate Key Values	408	(1)
Obtaining Diagnostics About Bad Input Data	408	(2)
Skipping Datafile Lines	410	(1)
Specifying Input Column Order	411	(1)
Preprocessing Input Values Before Inserting Them	412	(1)
Ignoring Datafile Columns	413	(2)
Exporting Query Results from MySQL	415	(2)
Exporting Tables as Text Files	417	(1)
Exporting Table Contents or Definitions in SQL Format	418	(2)
Copying Tables or Databases to Another Server	420	(2)
Writing Your Own Export Programs	422	(4)
Converting Datafiles from One Format to Another	426	(1)
Extracting and Rearranging Datafile Columns	427	(3)
Using the SQL Mode to Control Bad Input Data Handling	430	(2)
Validating and Transforming Data	432	(3)
Using Pattern Matching to Validate Data	435	(3)
Using Patterns to Match Broad Content Types	438	(1)
Using Patterns to Match Numeric Values	439	(2)
Using Patterns to Match Dates or Times	441	(4)
Using Patterns to Match Email Addresses or URLs	445	(1)
Using Table Metadata to Validate Data	446	(3)
Using a Lookup Table to Validate Data	449	(3)
Converting Two-Digit Year Values to Four-Digit Form	452	(1)
Performing Validity Checking on Date or Time Subparts	453	(3)
Writing Date-Processing Utilities	456	(5)
Using Dates with Missing Components	461	(1)
Importing Non-ISO Date Values	462	(1)
Exporting Dates Using Non-ISO Formats	463	(1)
Importing and Exporting NULL Values	464	(2)
Guessing Table Structure from a Datafile	466	(3)
Exchanging Data Between MySQL and Microsoft Access	469	(1)
Exchanging Data Between MySQL and Microsoft Excel	470	(2)
Exporting Query Results as XML	472	(4)
Importing XML into MySQL	476	(2)
Epilogue	478	(3)
Generating and Using Sequences	481	(36)
Introduction	481	(1)
Creating a Sequence Column and Generating Sequence Values	482	(3)
Choosing the Data Type for a Sequence Column	485	(2)
The Effect of Row Deletions on Sequence Generation	487	(3)
Retrieving Sequence Values	490	(4)
Renumbering an Existing Sequence	494	(2)
Extending the Range of a Sequence Column	496	(1)
Reusing Values at the Top of a Sequence	497	(1)
Ensuring That Rows Are Renumbered in a Particular Order	498	(1)
Starting a Sequence at a Particular Value	499	(1)
Sequencing an Unsequenced Table	500	(2)
Using an Auto_Increment Column to Create Multiple Sequences	502	(5)
Managing Multiple Simultaneous Auto_Increment Values	507	(1)
Using Auto_Increment Values to Relate Tables	508	(3)
Using Sequence Generators as Counters	511	(3)
Generating Repeating Sequences	514	(2)
Numbering Query Output Rows Sequentially	516	(1)
Using Multiple Tables	517	(66)
Introduction	517	(1)
Finding Rows in One Table That Match Rows in Another	518	(8)
Finding Rows with No Match in Another Table	526	(5)
Comparing a Table to Itself	531	(5)
Producing Master-Detail Lists and Summaries	536	(3)
Enumerating a Many-to-Many Relationship	539	(5)
Finding Rows Containing Per-Group Minimum or Maximum Values	544	(4)
Computing Team Standings	548	(6)
Using a Join to Fill or Identify Holes in a List	554	(5)
Calculating Successive-Row Differences	559	(2)
Finding Cumulative Sums and Running Averages	561	(4)
Using a Join to Control Query Output Order	565	(2)
Combining Several Result Sets in a Single Query	567	(5)
Identifying and Removing Mismatched or Unattached Rows	572	(3)
Performing a Join Between Tables in Different Databases	575	(1)
Using Different MySQL Servers Simultaneously	576	(3)
Referring to Join Output Column Names in Programs	579	(4)
Statistical Techniques	583	(24)
Introduction	583	(1)
Calculating Descriptive Statistics	584	(3)
Per-Group Descriptive Statistics	587	(2)
Generating Frequency Distributions	589	(3)
Counting Missing Values	592	(2)
Calculating Linear Regressions or Correlation Coefficients	594	(2)
Generating Random Numbers	596	(2)
Randomizing a Set of Rows	598	(3)
Selecting Random Items from a Set of Rows	601	(1)
Assigning Ranks	602	(5)
Handling Duplicates	607	(20)
Introduction	607	(1)
Preventing Duplicates from Occurring in a Table	608	(2)
Dealing with Duplicates When Loading Rows into a Table	610	(4)
Counting and Identifying Duplicates	614	(4)
Eliminating Duplicates from a Table	618	(4)
Eliminating Duplicates from a Self-Join Result	622	(5)
Performing Transactions	627	(16)
Introduction	627	(1)
Choosing a Transactional Storage Engine	628	(2)
Performing Transactions Using SQL	630	(1)
Performing Transactions from Within Programs	631	(3)
Using Transactions in Perl Programs	634	(2)
Using Transactions in Ruby Programs	636	(1)
Using Transactions in PHP Programs	637	(1)
Using Transactions in Python Programs	638	(1)
Using Transactions in Java Programs	639	(1)
Using Alternatives to Transactions	640	(3)
Using Stored Routines, Triggers, and Events	643	(18)
Introduction	643	(2)
Creating Compound-Statement Objects	645	(2)
Using a Stored Function to Encapsulate a Calculation	647	(2)
Using a Stored Procedure to ``Return'' Multiple Values	649	(1)
Using a Trigger to Define Dynamic Default Column Values	650	(3)
Simulating Timestamp Properties for Other Date and Time Types	653	(2)
Using a Trigger to Log Changes to a Table	655	(3)
Using Events to Schedule Database Actions	658	(3)
Introduction to MySQL on the Web	661	(36)
Introduction	661	(2)
Basic Principles of Web Page Generation	663	(4)
Using Apache to Run Web Scripts	667	(11)
Using Tomcat to Run Web Scripts	678	(10)
Encoding Special Characters in Web Output	688	(9)
Incorporating Query Results into Web Pages	697	(64)
Introduction	697	(1)
Displaying Query Results as Paragraph Text	698	(2)
Displaying Query Results as Lists	700	(12)
Displaying Query Results as Tables	712	(5)
Displaying Query Results as Hyperlinks	717	(4)
Creating a Navigation Index from Database Content	721	(5)
Storing Images or Other Binary Data	726	(7)
Retrieving Images or Other Binary Data	733	(3)
Serving Banner Ads	736	(2)
Serving Query Results for Download	738	(3)
Using a Template System to Generate Web Pages	741	(20)
Processing Web Input with MySQL	761	(80)
Introduction	761	(3)
Writing Scripts That Generate Web Forms	764	(3)
Creating Single-Pick Form Elements from Database Content	767	(16)
Creating Multiple-Pick Form Elements from Database Content	783	(5)
Loading a Database Record into a Form	788	(5)
Collecting Web Input	793	(11)
Validating Web Input	804	(1)
Storing Web Input in a Database	805	(3)
Processing File Uploads	808	(7)
Performing Searches and Presenting the Results	815	(3)
Generating Previous-Page and Next-Page Links	818	(4)
Generating ``Click to Sort'' Table Headings	822	(5)
Web Page Access Counting	827	(4)
Web Page Access Logging	831	(2)
Using MySQL for Apache Logging	833	(8)
Using MySQL-Based Web Session Management	841	(34)
Introduction	841	(4)
Using MySQL-Based Sessions in Perl Applications	845	(5)
Using MySQL-Based Storage in Ruby Applications	850	(4)
Using MySQL-Based Storage with the PHP Session Manager	854	(11)
Using MySQL for Session-Backing Store with Tomcat	865	(10)
Obtaining MySQL Software	875	(6)
Executing Programs from the Command Line	881	(8)
JSP and Tomcat Primer	889	(28)
References	917	(4)
Index	921