MySQL Cookbook. 4th Edition

Spis treści ebooka

• Foreword
• Preface
  • Who This Book Is For
  • Whats 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
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
• 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 Pythons 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 Strings 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 MySQLs 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 Threads 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
• Index

pokaż cały spis treści

ukryj recenzje