book

MySQL Cookbook, 3rd Edition

Name: MySQL Cookbook, 3rd Edition
Author: Paul DuBois
ISBN: 9781449374020

by Paul DuBois

August 2014

Intermediate to advanced

866 pages

24h 4m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Preface
Who This Book Is ForWhat’s in This BookMySQL APIs Used in This BookVersion and Platform NotesConventions Used in This BookThe MySQL Cookbook Companion WebsiteObtaining MySQL and Related SoftwareUsing Code ExamplesSafari® Books OnlineHow to Contact UsAcknowledgments
1. Using the mysql Client Program
IntroductionSetting Up a MySQL User AccountCreating a Database and a Sample TableWhat to Do if mysql Cannot Be FoundSpecifying mysql Command OptionsExecuting SQL Statements InteractivelyExecuting SQL Statements Read from a File or ProgramControlling mysql Output Destination and FormatUsing User-Defined Variables in SQL Statements
2. Writing MySQL-Based Programs
IntroductionConnecting, Selecting a Database, and DisconnectingChecking for ErrorsWriting Library FilesExecuting Statements and Retrieving ResultsHandling Special Characters and NULL Values in StatementsHandling Special Characters in IdentifiersIdentifying NULL Values in Result SetsTechniques for Obtaining Connection ParametersConclusion and Words of Advice
3. Selecting Data from Tables
IntroductionSpecifying Which Columns and Rows to SelectNaming Query Result ColumnsSorting Query ResultsRemoving Duplicate RowsWorking with NULL ValuesWriting Comparisons Involving NULL in ProgramsUsing Views to Simplify Table AccessSelecting Data from Multiple TablesSelecting Rows from the Beginning, End, or Middle of Query ResultsWhat to Do When LIMIT Requires the “Wrong” Sort OrderCalculating LIMIT Values from Expressions
4. Table Management
IntroductionCloning a TableSaving a Query Result in a TableCreating Temporary TablesGenerating Unique Table NamesChecking or Changing a Table Storage EngineCopying a Table Using mysqldump
5. Working with Strings
IntroductionString PropertiesChoosing a String Data TypeSetting the Client Connection Character SetWriting String LiteralsChecking or Changing a String’s Character Set or CollationConverting the Lettercase of a StringControlling Case Sensitivity in String ComparisonsPattern Matching with SQL PatternsPattern Matching with Regular ExpressionsBreaking Apart or Combining StringsSearching for SubstringsUsing Full-Text SearchesUsing a Full-Text Search with Short WordsRequiring or Prohibiting Full-Text Search WordsPerforming Full-Text Phrase Searches
6. Working with Dates and Times
IntroductionChoosing a Temporal Data TypeUsing Fractional Seconds SupportChanging MySQL’s Date FormatSetting the Client Time ZoneShifting Temporal Values Between Time ZonesDetermining the Current Date or TimeUsing TIMESTAMP or DATETIME to Track Row-Modification TimesExtracting Parts of Dates or TimesSynthesizing Dates or Times from Component ValuesConverting Between Temporal Values and Basic UnitsCalculating Intervals Between Dates or TimesAdding Date or Time ValuesCalculating AgesFinding the First Day, Last Day, or Length of a MonthCalculating Dates by Substring ReplacementFinding the Day of the Week for a DateFinding Dates for Any Weekday of a Given WeekPerforming Leap-Year CalculationsCanonizing Not-Quite-ISO Date StringsSelecting Rows Based on Temporal Characteristics
7. Sorting Query Results
IntroductionUsing ORDER BY to Sort Query ResultsUsing Expressions for SortingDisplaying One Set of Values While Sorting by AnotherControlling Case Sensitivity of String SortsDate-Based SortingSorting by Substrings of Column ValuesSorting by Fixed-Length SubstringsSorting by Variable-Length SubstringsSorting Hostnames in Domain OrderSorting Dotted-Quad IP Values in Numeric OrderFloating Values to the Head or Tail of the Sort OrderDefining a Custom Sort OrderSorting ENUM Values
8. Generating Summaries
IntroductionBasic Summary TechniquesCreating a View to Simplify Using a SummaryFinding Values Associated with Minimum and Maximum ValuesControlling String Case Sensitivity for MIN() and MAX()Dividing a Summary into SubgroupsSummaries and NULL ValuesSelecting Only Groups with Certain CharacteristicsUsing Counts to Determine Whether Values Are UniqueGrouping by Expression ResultsSummarizing Noncategorical DataFinding Smallest or Largest Summary ValuesDate-Based SummariesWorking with Per-Group and Overall Summary Values SimultaneouslyGenerating a Report That Includes a Summary and a List
9. Using Stored Routines, Triggers, and Scheduled Events
IntroductionCreating Compound-Statement ObjectsUsing Stored Functions to Encapsulate CalculationsUsing Stored Procedures to “Return” Multiple ValuesUsing Triggers to Implement Dynamic Default Column ValuesUsing Triggers to Simulate Function-Based IndexesSimulating TIMESTAMP Properties for Other Date and Time TypesUsing Triggers to Log Changes to a TableUsing Events to Schedule Database ActionsWriting Helper Routines for Executing Dynamic SQLHandling Errors Within Stored ProgramsUsing Triggers to Preprocess or Reject Data

10. Working with Metadata
IntroductionDetermining the Number of Rows Affected by a StatementObtaining Result Set MetadataDetermining Whether a Statement Produced a Result SetUsing Metadata to Format Query OutputListing or Checking Existence of Databases or TablesAccessing Table Column DefinitionsGetting ENUM and SET Column InformationGetting Server MetadataWriting Applications That Adapt to the MySQL Server Version
11. Importing and Exporting Data
IntroductionImporting Data with LOAD DATA and mysqlimportImporting CSV FilesExporting Query Results from MySQLImporting and Exporting NULL ValuesWriting Your Own Data Export ProgramsConverting Datafiles from One Format to AnotherExtracting and Rearranging Datafile ColumnsExchanging Data Between MySQL and Microsoft ExcelExporting Query Results as XMLImporting XML into MySQLGuessing Table Structure from a Datafile
12. Validating and Reformatting Data
IntroductionUsing the SQL Mode to Reject Bad Input ValuesValidating and Transforming DataUsing Pattern Matching to Validate DataUsing Patterns to Match Broad Content TypesUsing Patterns to Match Numeric ValuesUsing Patterns to Match Dates or TimesUsing Patterns to Match Email Addresses or URLsUsing Table Metadata to Validate DataUsing a Lookup Table to Validate DataConverting Two-Digit Year Values to Four-Digit FormPerforming Validity Checking on Date or Time SubpartsWriting Date-Processing UtilitiesImporting Non-ISO Date ValuesExporting Dates Using Non-ISO FormatsEpilogue
13. Generating and Using Sequences
IntroductionCreating a Sequence Column and Generating Sequence ValuesChoosing the Definition for a Sequence ColumnThe Effect of Row Deletions on Sequence GenerationRetrieving Sequence ValuesRenumbering an Existing SequenceExtending the Range of a Sequence ColumnReusing Values at the Top of a SequenceEnsuring That Rows Are Renumbered in a Particular OrderSequencing an Unsequenced TableManaging Multiple Auto-Increment Values SimultaneouslyUsing Auto-Increment Values to Associate TablesUsing Sequence Generators as CountersGenerating Repeating Sequences
14. Using Joins and Subqueries
IntroductionFinding Matches Between TablesFinding Mismatches Between TablesIdentifying and Removing Mismatched or Unattached RowsComparing a Table to ItselfProducing Master-Detail Lists and SummariesEnumerating a Many-to-Many RelationshipFinding Per-Group Minimum or Maximum ValuesUsing a Join to Fill or Identify Holes in a ListUsing a Join to Control Query Sort OrderReferring to Join Output Column Names in Programs
15. Statistical Techniques
IntroductionCalculating Descriptive StatisticsPer-Group Descriptive StatisticsGenerating Frequency DistributionsCounting Missing ValuesCalculating Linear Regressions or Correlation CoefficientsGenerating Random NumbersRandomizing a Set of RowsSelecting Random Items from a Set of RowsCalculating Successive-Row DifferencesFinding Cumulative Sums and Running AveragesAssigning RanksComputing Team Standings
16. Handling Duplicates
IntroductionPreventing Duplicates from Occurring in a TableDealing with Duplicates When Loading Rows into a TableCounting and Identifying DuplicatesEliminating Duplicates from a Table
17. Performing Transactions
IntroductionChoosing a Transactional Storage EnginePerforming Transactions Using SQLPerforming Transactions from Within ProgramsUsing Transactions in Perl ProgramsUsing Transactions in Ruby ProgramsUsing Transactions in PHP ProgramsUsing Transactions in Python ProgramsUsing Transactions in Java Programs
18. Introduction to MySQL on the Web
IntroductionBasic Principles of Web Page GenerationUsing Apache to Run Web ScriptsUsing Tomcat to Run Web ScriptsEncoding Special Characters in Web Output
19. Generating Web Content from Query Results
IntroductionDisplaying Query Results as ParagraphsDisplaying Query Results as ListsDisplaying Query Results as TablesDisplaying Query Results as HyperlinksCreating Navigation Indexes from Database ContentStoring Images or Other Binary DataServing Images or Other Binary DataServing Banner AdsServing Query Results for Download
20. Processing Web Input with MySQL
IntroductionWriting Scripts That Generate Web FormsCreating Single-Pick Form Elements from Database ContentCreating Multiple-Pick Form Elements from Database ContentLoading Database Content into a FormCollecting Web InputValidating Web InputStoring Web Input in a DatabaseProcessing File UploadsPerforming Web-Based Database SearchesGenerating Previous-Page and Next-Page LinksGenerating “Click to Sort” Table HeadingsWeb Page Access CountingWeb Page Access LoggingUsing MySQL for Apache Logging
21. Using MySQL-Based Web Session Management
IntroductionUsing MySQL-Based Sessions in Perl ApplicationsUsing MySQL-Based Storage in Ruby ApplicationsUsing MySQL-Based Storage with the PHP Session ManagerUsing MySQL for Session-Backing Store with Tomcat
22. Server Administration
IntroductionConfiguring the ServerManaging the Plug-In InterfaceControlling Server LoggingRotating or Expiring LogfilesRotating Log Tables or Expiring Log Table RowsMonitoring the MySQL ServerCreating and Using Backups
23. Security
IntroductionUnderstanding the mysql.user TableManaging User AccountsImplementing a Password PolicyChecking Password StrengthExpiring PasswordsAssigning Yourself a New PasswordResetting an Expired PasswordFinding and Fixing Insecure AccountsDisabling Use of Accounts with Pre-4.1 PasswordsFinding and Removing Anonymous AccountsModifying “Any Host” and “Many Host” Accounts
Index
Colophon
Copyright

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9781449374112Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills