Op werkdagen voor 23:00 besteld, morgen in huis Gratis verzending vanaf €20

MySQL Cookbook

Solutions for Database Developers and Administrators

Paperback Engels 2022 9781492093169
Verkooppositie 3935Hoogste positie: 3935
Verwachte levertijd ongeveer 8 werkdagen


For MySQL, the price of popularity comes with a flood of questions from users on how to solve specific data-related issues. That's where this cookbook comes in. 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 resolve MySQL problems from scratch.

In this updated fourth edition, authors Sveta Smirnova and Alkin Tezuysal provide more than 200 recipes that cover powerful features in both MySQL 5.7 and 8.0. Beginners as well as professional database and web developers will dive into topics such as MySQL Shell, MySQL replication, and working with JSON.

You'll learn how to:

- Connect to a server, issue queries, and retrieve results
- Retrieve data from the MySQL Server
- Store, retrieve, and manipulate strings
- Work with dates and times
- Sort query results and generate summaries
- Assess the characteristics of a dataset
- Write stored functions and procedures
- Use stored routines, triggers, and scheduled events
- Perform basic MySQL administration tasks
- Understand MySQL monitoring fundamentals


Aantal pagina's:886
Hoofdrubriek:IT-management / ICT


Wees de eerste die een lezersrecensie schrijft!

Geef uw waardering

Zeer goed Goed Voldoende Matig Slecht

Over Sveta Smirnova

Sveta Smirnova is a Principle Technical Support Engineer in the Bugs Verification Group of the MySQL Support Group at Oracle.

Andere boeken door Sveta Smirnova


Who This Book Is For
What’s in This Book
MySQL APIs Used in This Book
Version and Platform Notes
Conventions Used in This Book
The MySQL Cookbook Companion GitHub Repository
Obtaining MySQL and Related Software
Using Code Examples
O’Reilly Online Learning
How to Contact Us

1. Using the mysql Client Program
1.0. Introduction
1.1. Setting Up a MySQL User Account
1.2. Creating a Database and a Sample Table
1.3. Finding mysql Client
1.4. Specifying mysql Command Options
1.5. Executing SQL Statements Interactively
1.6. Executing SQL Statements Read from a File or Program
1.7. Controlling mysql Output Destination and Format
1.8. Using User-Defined Variables in SQL Statements
1.9. Customizing a mysql Prompt
1.10. Using External Programs
1.11. Filtering and Processing Output

2. Using MySQL Shell
2.0. Introduction
2.1. Connecting to MySQL Server with MySQL Shell
2.2. Selecting the Protocol
2.3. Selecting SQL, JavaScript, or Python Mode
2.4. Running SQL Session
2.5. Running SQL in JavaScript Mode
2.6. Running SQL in Python Mode
2.7. Working with Tables in JavaScript Mode
2.8. Working with Tables in Python Mode
2.9. Working with Collections in JavaScript Mode
2.10. Working with Collections in Python Mode
2.11. Controlling the Output Format
2.12. Running Reports with MySQL Shell
2.13. Using MySQL Shell Utilities
2.14. Using the Admin API to Automate Replication Management
2.15. Working with JavaScript Objects
2.16. Filling Test Data Using Python’s Data Science Modules
2.17. Reusing Your Scripts for MySQL Shell

3. MySQL Replication
3.0. Introduction
3.1. Configuring Basic Replication Between One Source and One Replica
3.2. Position-Based Replication in the New Installation Environment
3.3. Setting Up a Position-Based Replica of a MySQL Installation that Is Already in Use
3.4. Setting Up GTID-Based Replication
3.5. Configuring a Binary Log Format
3.6. Using Replication Filters
3.7. Rewriting a Database on the Replica
3.8. Using a Multithreaded Replica
3.9. Setting Up Circular Replication
3.10. Using Multisource Replication
3.11. Using a Semisynchronous Replication Plug-In
3.12. Using Group Replication
3.13. Storing Replication Credentials Securely
3.14. Using TLS (SSL) for Replication
3.15. Replication Troubleshooting
3.16. Using Processlist to Understand Replication Performance
3.17. Setting Up Automated Replication

4. Writing MySQL-Based Programs
4.0. Introduction
4.1. Connecting, Selecting a Database, and Disconnecting
4.2. Checking for Errors
4.3. Writing Library Files
4.4. Executing Statements and Retrieving Results
4.5. Handling Special Characters and NULL Values in Statements
4.6. Handling Special Characters in Identifiers
4.7. Identifying NULL Values in Result Sets
4.8. Obtaining Connection Parameters
4.9. Resetting the profile Table

5. Selecting Data from Tables
5.0. Introduction
5.1. Specifying Which Columns and Rows to Select
5.2. Naming Query Result Columns
5.3. Sorting Query Results
5.4. Removing Duplicate Rows
5.5. Working with NULL Values
5.6. Writing Comparisons Involving NULL in Programs
5.7. Using Views to Simplify Table Access
5.8. Selecting Data from Multiple Tables
5.9. Selecting Rows from the Beginning, End, or Middle of Query Results
5.10. What to Do When LIMIT and the Final Result Require a Different Sort Order
5.11. Calculating LIMIT Values from Expressions
5.12. Combining Two or More SELECT Results
5.13. Selecting Results of Subqueries

6. Table Management
6.0. Introduction
6.1. Cloning a Table
6.2. Saving a Query Result in a Table
6.3. Creating Temporary Tables
6.4. Generating Unique Table Names
6.5. Checking or Changing a Table Storage Engine
6.6. Copying a Table Using mysqldump
6.7. Copying an InnoDB Table Using Transportable Tablespaces
6.8. Copying a MyISAM Table Using an sdi File

7. Working with Strings
7.0. Introduction
7.1. String Properties
7.2. Choosing a String Data Type
7.3. Setting the Client Connection Character Set
7.4. Writing String Literals
7.5. Checking or Changing a String’s Character Set or Collation
7.6. Converting the Lettercase of a String
7.7. Comparing String Values
7.8. Converting Between Decimal, Octal, and Hexadecimal Formats
7.9. Converting Between ASCII, BIT, and Hexadecimal Formats
7.10. Pattern Matching with SQL Patterns
7.11. Pattern Matching with Regular Expressions
7.12. Reversing the String Content
7.13. Searching for Substrings
7.14. Breaking Apart or Combining Strings
7.15. Using Full-Text Searches
7.16. Using a Full-Text Search with Short Words
7.17. Requiring or Prohibiting Full-Text Search Words
7.18. Performing Full-Text Phrase Searches

8. Working with Dates and Times
8.0. Introduction
8.1. Choosing a Temporal Data Type
8.2. Using Fractional Seconds Support
8.3. Changing MySQL’s Date Format
8.4. Setting the Client Time Zone
8.5. Setting the Server Time Zone
8.6. Shifting Temporal Values Between Time Zones
8.7. Determining the Current Date or Time
8.8. Using TIMESTAMP or DATETIME to Track Row-Modification Times
8.9. Extracting Parts of Dates or Times
8.10. Synthesizing Dates or Times from Component Values
8.11. Converting Between Temporal Values and Basic Units
8.12. Calculating Intervals Between Dates or Times
8.13. Adding Date or Time Values
8.14. Calculating Ages
8.15. Finding the First Day, Last Day, or Length of a Month
8.16. Finding the Day of the Week for a Date
8.17. Finding Dates for Any Weekday of a Given Week
8.18. Canonizing Not-Quite-ISO Date Strings
8.19. Selecting Rows Based on Temporal Characteristics

9. Sorting Query Results
9.0. Introduction
9.1. Using ORDER BY to Sort Query Results
9.2. Using Expressions for Sorting
9.3. Displaying One Set of Values While Sorting by Another
9.4. Controlling Case Sensitivity of String Sorts
9.5. Sorting in Temporal Order
9.6. Sorting by Substrings of Column Values
9.7. Sorting by Fixed-Length Substrings
9.8. Sorting by Variable-Length Substrings
9.9. Sorting Hostnames in Domain Order
9.10. Sorting Dotted-Quad IP Values in Numeric Order
9.11. Floating Values to the Head or Tail of the Sort Order
9.12. Defining a Custom Sort Order
9.13. Sorting ENUM Values

10. Generating Summaries
10.0. Introduction
10.1. Summarizing with COUNT()
10.2. Summarizing with MIN() and MAX()
10.3. Summarizing with SUM() and AVG()
10.4. Using DISTINCT to Eliminate Duplicates
10.5. Creating a View to Simplify Using a Summary
10.6. Finding Values Associated with Minimum and Maximum Values
10.7. Controlling String Case Sensitivity for MIN() and MAX()
10.8. Dividing a Summary into Subgroups
10.9. Handling NULL Values with Aggregate Functions
10.10. Selecting Only Groups with Certain Characteristics
10.11. Using Counts to Determine Whether Values Are Unique
10.12. Grouping by Expression Results
10.13. Summarizing Noncategorical Data
10.14. Finding Smallest or Largest Summary Values
10.15. Producing Date-Based Summaries
10.16. Working with Per-Group and Overall Summary Values Simultaneously
10.17. Generating a Report that Includes a Summary and a List
10.18. Generating Summaries from Temporary Result Sets

11. Using Stored Routines, Triggers, and Scheduled Events
11.0. Introduction
11.1. Creating Compound-Statement Objects
11.2. Using Stored Functions to Simplify Calculations
11.3. Using Stored Procedures to Produce Multiple Values
11.4. Using Triggers to Log Changes to a Table
11.5. Using Events to Schedule Database Actions
11.6. Writing Helper Routines for Executing Dynamic SQL
11.7. Detecting “No More Rows” Conditions Using Condition Handlers
11.8. Catching and Ignoring Errors with Condition Handlers
11.9. Raising Errors and Warnings
11.10. Logging Errors by Accessing the Diagnostic Area
11.11. Using Triggers to Preprocess or Reject Data

12. Working with Metadata
12.0. Introduction
12.1. Determining the Number of Rows Affected by a Statement
12.2. Obtaining Result Set Metadata
12.3. Listing or Checking the Existence of Databases or Tables
12.4. Listing or Checking the Existence of Views
12.5. Accessing Table Column Definitions
12.6. Getting ENUM and SET Column Information
12.7. Getting Server Metadata
12.8. Writing Applications That Adapt to the MySQL Server Version
12.9. Getting Child Tables That Reference a Specific Table via Foreign Key Constraints
12.10. Listing Triggers
12.11. Listing Stored Routines and Scheduled Events
12.12. Listing Installed Plug-Ins
12.13. Listing Character Sets and Collations
12.14. Listing CHECK Constraints

13. Importing and Exporting Data
13.0. Introduction
13.1. Importing Data with LOAD DATA and mysqlimport
13.2. Specifying Column and Line Delimiters
13.3. Dealing with Quotes and Special Characters
13.4. Handling Duplicate Key Values
13.5. Obtaining Diagnostics About Bad Input Data
13.6. Skipping Datafile Lines
13.7. Specifying Input Column Order
13.8. Preprocessing Input Values Before Inserting Them
13.9. Ignoring Datafile Columns
13.10. Importing CSV Files
13.11. Exporting Query Results from MySQL
13.12. Importing and Exporting NULL Values
13.13. Exporting Data in SQL Format
13.14. Importing SQL Data
13.15. Exporting Query Results as XML
13.16. Importing XML into MySQL
13.17. Importing Data in JSON Format
13.18. Importing Data from MongoDB
13.19. Exporting Data in JSON Format
13.20. Guessing Table Structure from a Datafile

14. Validating and Reformatting Data
14.0. Introduction
14.1. Using the SQL Mode to Reject Bad Input Values
14.2. Using CHECK Constraints to Reject Invalid Values
14.3. Using Triggers to Reject Input Values
14.4. Writing an Input-Processing Loop
14.5. Putting Common Tests in Libraries
14.6. Using Pattern Matching to Validate Data
14.7. Using Patterns to Match Broad Content Types
14.8. Using Patterns to Match Numeric Values
14.9. Using Patterns to Match Dates or Times
14.10. Using Patterns to Match Email Addresses or URLs
14.11. Using Table Metadata to Validate Data
14.12. Using a Lookup Table to Validate Data
14.13. Converting Two-Digit Year Values to Four-Digit Form
14.14. Performing Validity Checking on Date or Time Subparts
14.15. Writing Date-Processing Utilities
14.16. Importing Non-ISO Date Values
14.17. Exporting Dates Using Non-ISO Formats
14.18. Preprocessing and Importing a File

15. Generating and Using Sequences
15.0. Introduction
15.1. Generating a Sequence with AUTO_INCREMENT Columns
15.2. Choosing the Data Type for a Sequence Column
15.3. Deleting Rows Without Changing a Sequence
15.4. Retrieving Sequence Values
15.5. Renumbering an Existing Sequence
15.6. Extending the Range of a Sequence Column
15.7. Reusing Values at the Top of a Sequence
15.8. Ensuring That Rows Are Renumbered in a Particular Order
15.9. Sequencing an Unsequenced Table
15.10. Managing Multiple Auto-Increment Values Simultaneously
15.11. Using Auto-Increment Values to Associate Tables
15.12. Using Sequence Generators as Counters
15.13. Generating Repeating Sequences
15.14. Using Custom Increment Values
15.15. Using Window Functions to Number Rows in the Result Set
15.16. Generating Series with Recursive CTEs
15.17. Creating and Storing Custom Sequences

16. Using Joins and Subqueries
16.0. Introduction
16.1. Finding Matches Between Tables
16.2. Finding Mismatches Between Tables
16.3. Identifying and Removing Mismatched or Unattached Rows
16.4. Comparing a Table to Itself
16.5. Producing Candidate-Detail Lists and Summaries
16.6. Enumerating a Many-to-Many Relationship
16.7. Finding Per-Group Minimum or Maximum Values
16.8. Using a Join to Fill or Identify Holes in a List
16.9. Using a Join to Control Query Sort Order
16.10. Joining Results of Multiple Queries
16.11. Referring to Join Output Column Names in Programs

17. Statistical Techniques
17.0. Introduction
17.1. Calculating Descriptive Statistics
17.2. Calculating Descriptive Statistics for Groups
17.3. Generating Frequency Distributions
17.4. Counting Missing Values
17.5. Calculating Linear Regressions or Correlation Coefficients
17.6. Generating Random Numbers
17.7. Randomizing a Set of Rows
17.8. Selecting Random Items from a Set of Rows
17.9. Calculating Successive-Row Differences
17.10. Finding Cumulative Sums and Running Averages
17.11. Assigning Ranks
17.12. Computing Team Standings

18. Handling Duplicates
18.0. Introduction
18.1. Preventing Duplicates from Occurring in a Table
18.2. Having More Than One Unique Key in the Table
18.3. Dealing with Duplicates When Loading Rows into a Table
18.4. Counting and Identifying Duplicates
18.5. Eliminating Duplicates from a Table

19. Working with JSON
19.0. Introduction
19.1. Choosing the Right Data Type
19.2. Inserting JSON Values
19.3. Validating JSON
19.4. Formatting JSON Values
19.5. Extracting Values from JSON
19.6. Searching Inside JSON
19.7. Inserting New Elements into a JSON Document
19.8. Updating JSON
19.9. Removing Elements from JSON
19.10. Merging Two or More JSON Documents into One
19.11. Creating JSON from Relational Data
19.12. Converting JSON into Relational Format
19.13. Investigating JSON
19.14. Working with JSON in MySQL as a Document Store

20. Performing Transactions
20.0. Introduction
20.1. Choosing a Transactional Storage Engine
20.2. Performing Transactions Using SQL
20.3. Performing Transactions from Within Programs
20.4. Performing Transactions in Perl Programs
20.5. Performing Transactions in Ruby Programs
20.6. Performing Transactions in PHP Programs
20.7. Performing Transactions in Python Programs
20.8. Performing Transactions in Go Programs
20.9. Using Context-Aware Functions to Handle Transactions in Go
20.10. Performing Transactions in Java Programs

21. Query Performance
21.0. Introduction
21.1. Creating Indexes
21.2. Creating a Surrogate Primary Key
21.3. Maintaining Indexes
21.4. Deciding When a Query Can Use an Index
21.5. Deciding the Order for Multiple Column Indexes
21.6. Using Ascending and Descending Indexes
21.7. Using Function-Based Indexes
21.8. Using Indexes on Generated Columns with JSON Data
21.9. Using Full Text Indexes
21.10. Utilizing Spatial Indexes and Geographical Data
21.11. Creating and Using Histograms
21.12. Writing Performant Queries

22. Server Administration
22.0. Introduction
22.1. Configuring the Server
22.2. Managing the Plug-In Interface
22.3. Controlling Server Logging
22.4. Rotating or Expiring Logfiles
22.5. Rotating Log Tables or Expiring Log Table Rows
22.6. Configuring Storage Engines

23. Monitoring the MySQL Server
23.0. Introduction
23.1. Why Monitor the MySQL Server?
23.2. Discovering Sources of MySQL Monitoring Information
23.3. Checking Server Uptime and Progress
23.4. Troubleshooting Server Start Problems
23.5. Determining the IO Utilization of the MySQL Server
23.6. Determining MySQL Thread’s CPU Utilization
23.7. Determining if MySQL Has Reached Its Connection Limits
23.8. Verifying That the Buffer Pool Is Sized Properly
23.9. Finding Information About the Storage Engine
23.10. Using the Error Log File to Troubleshoot MySQL Server Crashes
23.11. Slow Query Log File
23.12. Monitoring with the General Query Log
23.13. Using the Binary Log to Identify Changes

24. Security
24.0. Introduction
24.1. Understanding the mysql.user Table
24.2. Managing User Accounts
24.3. Implementing a Password Policy
24.4. Checking Password Strength
24.5. Expiring Passwords
24.6. Assigning Yourself a New Password
24.7. Resetting an Expired Password
24.8. Finding and Removing Anonymous Accounts
24.9. Modifying “Any Host” and “Many Host” Accounts
24.10. Using TLS (SSL)
24.11. Using Roles
24.12. Using Views to Secure Data Access
24.13. Using Stored Routines to Secure Data Modifications

About the Authors

Managementboek Top 100


Populaire producten



        MySQL Cookbook