MySQL Cookbook: Solutions for Database Developers and Administrators / Edition 3
by Paul DuBoisView All Available Formats & Editions
ISBN-10: 1449374026
ISBN-13: 9781449374020
Pub. Date: 08/23/2014
Publisher: O'Reilly Media, Incorporated
MySQL’s popularity has brought a flood of questions about how to solve specific problems, and that’s where this cookbook is essential. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations for programmers who don’t have the
Overview
MySQL’s popularity has brought a flood of questions about how to solve specific problems, and that’s where this cookbook is essential. When you need quick solutions or techniques, this handy resource provides scores of short, focused pieces of code, hundreds of worked-out examples, and clear, concise explanations for programmers who don’t have the time (or expertise) to solve MySQL problems from scratch.
Ideal for beginners and professional database and web developers, this updated third edition covers powerful features in MySQL 5.6 (and some in 5.7). The book focuses on programming APIs in Python, PHP, Java, Perl, and Ruby. With more than 200+ recipes, you’ll learn how to:
- Use the mysql client and write MySQL-based programs
- Create, populate, and select data from tables
- Store, retrieve, and manipulate strings
- Work with dates and times
- Sort query results and generate summaries
- Use stored routines, triggers, and scheduled events
- Import, export, validate, and reformat data
- Perform transactions and work with statistics
- Process web input, and generate web content from query results
- Use MySQL-based web session management
- Provide security and server administration
Product Details
- ISBN-13:
- 9781449374020
- Publisher:
- O'Reilly Media, Incorporated
- Publication date:
- 08/23/2014
- Pages:
- 866
- Sales rank:
- 1,304,448
- Product dimensions:
- 7.00(w) x 9.10(h) x 1.60(d)
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
Average Review: