MySQL Cookbook: Solutions for Database Developers and Administrators

MySQL Cookbook: Solutions for Database Developers and Administrators

by Paul DuBois


$71.99 $79.99 Save 10% Current price is $71.99, Original price is $79.99. You Save 10%.
View All Available Formats & Editions
Choose Expedited Shipping at checkout for guaranteed delivery by Wednesday, January 23

Product Details

ISBN-13: 9781449374020
Publisher: O'Reilly Media, Incorporated
Publication date: 08/23/2014
Pages: 866
Sales rank: 702,832
Product dimensions: 7.00(w) x 9.10(h) x 1.60(d)

About the Author

Paul DuBois was one of the first contributors to the online MySQL Reference Manual, a renowned documentation project that supported MySQL administrators and database developers in the first few years of MySQL's existence in the late 1990's. Paul went on to write more than six books on MySQL, including the first edition of "MySQL Cookbook". He is also the author of "Using csh & tcsh" and "Software Portability with imake", both by O'Reilly.

Table of Contents

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

Customer Reviews

Most Helpful Customer Reviews

See All Customer Reviews