Muutke küpsiste eelistusi

MySQL Cookbook: Solutions for Database Developers and Administrators 4th New edition [Pehme köide]

,
  • Formaat: Paperback / softback, 850 pages, kõrgus x laius: 232x178 mm
  • Ilmumisaeg: 16-Aug-2022
  • Kirjastus: O'Reilly Media
  • ISBN-10: 1492093165
  • ISBN-13: 9781492093169
Teised raamatud teemal:
  • Pehme köide
  • Hind: 84,99 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Tavahind: 99,99 €
  • Säästad 15%
  • Raamatu kohalejõudmiseks kirjastusest kulub orienteeruvalt 2-4 nädalat
  • Kogus:
  • Lisa ostukorvi
  • Tasuta tarne
  • Tellimisaeg 2-4 nädalat
  • Lisa soovinimekirja
MySQL Cookbook: Solutions for Database Developers and Administrators 4th New editionSuurem pilt
  • Formaat: Paperback / softback, 850 pages, kõrgus x laius: 232x178 mm
  • Ilmumisaeg: 16-Aug-2022
  • Kirjastus: O'Reilly Media
  • ISBN-10: 1492093165
  • ISBN-13: 9781492093169
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781492093169.html
Märksõnad:

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

Foreword xv
Preface xvii
1 Using the mysql Client Program
1(32)
1.0 Introduction
1(1)
1.1 Setting Up a MySQL User Account
2(3)
1.2 Creating a Database and a Sample Table
5(2)
1.3 Finding mysql Client
7(1)
1.4 Specifying mysql Command Options
8(6)
1.5 Executing SQL Statements Interactively
14(2)
1.6 Executing SQL Statements Read from a File or Program
16(2)
1.7 Controlling mysql Output Destination and Format
18(5)
1.8 Using User-Defined Variables in SQL Statements
23(3)
1.9 Customizing a mysql Prompt
26(2)
1.10 Using External Programs
28(1)
1.11 Filtering and Processing Output
29(4)
2 Using MySQL Shell
33(54)
2.0 Introduction
33(1)
2.1 Connecting to MySQL Server with MySQL Shell
34(3)
2.2 Selecting the Protocol
37(3)
2.3 Selecting SQL, JavaScript, or Python Mode
40(1)
2.4 Running SQL Session
41(1)
2.5 Running SQL in JavaScript Mode
42(2)
2.6 Running SQL in Python Mode
44(2)
2.7 Working with Tables in JavaScript Mode
46(3)
2.8 Working with Tables in Python Mode
49(2)
2.9 Working with Collections in JavaScript Mode
51(3)
2.10 Working with Collections in Python Mode
54(2)
2.11 Controlling the Output Format
56(4)
2.12 Running Reports with MySQL Shell
60(3)
2.13 Using MySQL Shell Utilities
63(4)
2.14 Using the Admin API to Automate Replication Management
67(2)
2.15 Working with JavaScript Objects
69(4)
2.16 Filling Test Data Using Python's Data Science Modules
73(9)
2.17 Reusing Your Scripts for MySQL Shell
82(5)
3 MySQL Replication
87(66)
3.0 Introduction
87(2)
3.1 Configuring Basic Replication Between One Source and One Replica
89(2)
3.2 Position-Based Replication in the New Installation Environment
91(1)
3.3 Setting Up a Position-Based Replica of a MySQL Installation that Is Already in Use
92(2)
3.4 Setting Up GTID-Based Replication
94(4)
3.5 Configuring a Binary Log Format
98(2)
3.6 Using Replication Filters
100(5)
3.7 Rewriting a Database on the Replica
105(2)
3.8 Using a Multithreaded Replica
107(2)
3.9 Setting Up Circular Replication
109(2)
3.10 Using Multisource Replication
111(4)
3.11 Using a Semisynchronous Replication Plug-In
115(3)
3.12 Using Group Replication
118(5)
3.13 Storing Replication Credentials Securely
123(1)
3.14 Using TLS (SSL) for Replication
124(2)
3.15 Replication Troubleshooting
126(14)
3.16 Using Processlist to Understand Replication Performance
140(4)
3.17 Setting Up Automated Replication
144(9)
4 Writing MySQL-Based Programs
153(86)
4.0 Introduction
153(4)
4.1 Connecting, Selecting a Database, and Disconnecting
157(15)
4.2 Checking for Errors
172(9)
4.3 Writing Library Files
181(15)
4.4 Executing Statements and Retrieving Results
196(14)
4.5 Handling Special Characters and NULL Values in Statements
210(10)
4.6 Handling Special Characters in Identifiers
220(1)
4.7 Identifying NULL Values in Result Sets
221(6)
4.8 Obtaining Connection Parameters
227(10)
4.9 Resetting the profile Table
237(2)
5 Selecting Data from Tables
239(26)
5.0 Introduction
239(1)
5.1 Specifying Which Columns and Rows to Select
240(2)
5.2 Naming Query Result Columns
242(4)
5.3 Sorting Query Results
246(1)
5.4 Removing Duplicate Rows
247(1)
5.5 Working with NULL Values
248(3)
5.6 Writing Comparisons Involving NULL in Programs
251(1)
5.7 Using Views to Simplify Table Access
252(1)
5.8 Selecting Data from Multiple Tables
253(2)
5.9 Selecting Rows from the Beginning, End, or Middle of Query Results
255(3)
5.10 What to Do When LIMIT and the Final Result Require a Different Sort Order
258(1)
5.11 Calculating LIMIT Values from Expressions
259(1)
5.12 Combining Two or More SELECT Results
260(2)
5.13 Selecting Results of Subqueries
262(3)
6 Table Management
265(16)
6.0 Introduction
265(1)
6.1 Cloning a Table
265(1)
6.2 Saving a Query Result in a Table
266(3)
6.3 Creating Temporary Tables
269(2)
6.4 Generating Unique Table Names
271(1)
6.5 Checking or Changing a Table Storage Engine
272(1)
6.6 Copying a Table Using mysqldump
273(3)
6.7 Copying an InnoDB Table Using Transportable Tablespaces
276(2)
6.8 Copying a MyISAM Table Using an sdi File
278(3)
7 Working with Strings
281(48)
7.0 Introduction
281(1)
7.1 String Properties
282(4)
7.2 Choosing a String Data Type
286(2)
7.3 Setting the Client Connection Character Set
288(3)
7.4 Writing String Literals
291(3)
7.5 Checking or Changing a String's Character Set or Collation
294(2)
7.6 Converting the Lettercase of a String
296(2)
7.7 Comparing String Values
298(3)
7.8 Converting Between Decimal, Octal, and Hexadecimal Formats
301(1)
7.9 Converting Between ASCII, BIT, and Hexadecimal Formats
302(3)
7.10 Pattern Matching with SQL Patterns
305(3)
7.11 Pattern Matching with Regular Expressions
308(5)
7.12 Reversing the String Content
313(1)
7.13 Searching for Substrings
314(1)
7.14 Breaking Apart or Combining Strings
315(3)
7.15 Using Full-Text Searches
318(5)
7.16 Using a Full-Text Search with Short Words
323(2)
7.17 Requiring or Prohibiting Full-Text Search Words
325(2)
7.18 Performing Full-Text Phrase Searches
327(2)
8 Working with Dates and Times
329(52)
8.0 Introduction
329(1)
8.1 Choosing a Temporal Data Type
330(2)
8.2 Using Fractional Seconds Support
332(3)
8.3 Changing MySQL's Date Format
335(4)
8.4 Setting the Client Time Zone
339(2)
8.5 Setting the Server Time Zone
341(1)
8.6 Shifting Temporal Values Between Time Zones
342(2)
8.7 Determining the Current Date or Time
344(1)
8.8 Using TIMESTAMP or DATETIME to Track Row-Modification Times
345(3)
8.9 Extracting Parts of Dates or Times
348(4)
8.10 Synthesizing Dates or Times from Component Values
352(2)
8.11 Converting Between Temporal Values and Basic Units
354(4)
8.12 Calculating Intervals Between Dates or Times
358(4)
8.13 Adding Date or Time Values
362(6)
8.14 Calculating Ages
368(1)
8.15 Finding the First Day, Last Day, or Length of a Month
369(2)
8.16 Finding the Day of the Week for a Date
371(1)
8.17 Finding Dates for Any Weekday of a Given Week
372(3)
8.18 Canonizing Not-Quite-ISO Date Strings
375(1)
8.19 Selecting Rows Based on Temporal Characteristics
376(5)
9 Sorting Query Results
381(36)
9.0 Introduction
381(1)
9.1 Using ORDER BY to Sort Query Results
382(3)
9.2 Using Expressions for Sorting
385(2)
9.3 Displaying One Set of Values While Sorting by Another
387(3)
9.4 Controlling Case Sensitivity of String Sorts
390(4)
9.5 Sorting in Temporal Order
394(3)
9.6 Sorting by Substrings of Column Values
397(1)
9.7 Sorting by Fixed-Length Substrings
398(3)
9.8 Sorting by Variable-Length Substrings
401(4)
9.9 Sorting Hostnames in Domain Order
405(3)
9.10 Sorting Dotted-Quad IP Values in Numeric Order
408(2)
9.11 Floating Values to the Head or Tail of the Sort Order
410(2)
9.12 Defining a Custom Sort Order
412(1)
9.13 Sorting ENUM Values
413(4)
10 Generating Summaries
417(38)
10.0 Introduction
417(2)
10.1 Summarizing with COUNT()
419(2)
10.2 Summarizing with MIN() and MAX()
421(1)
10.3 Summarizing with SUM() and AVG()
422(2)
10.4 Using DISTINCT to Eliminate Duplicates
424(2)
10.5 Creating a View to Simplify Using a Summary
426(1)
10.6 Finding Values Associated with Minimum and Maximum Values
426(3)
10.7 Controlling String Case Sensitivity for MIN() and MAX()
429(1)
10.8 Dividing a Summary into Subgroups
430(4)
10.9 Handling NULL Values with Aggregate Functions
434(3)
10.10 Selecting Only Groups with Certain Characteristics
437(1)
10.11 Using Counts to Determine Whether Values Are Unique
438(1)
10.12 Grouping by Expression Results
439(1)
10.13 Summarizing Noncategorical Data
440(3)
10.14 Finding Smallest or Largest Summary Values
443(2)
10.15 Producing Date-Based Summaries
445(1)
10.16 Working with Per-Group and Overall Summary Values Simultaneously
446(3)
10.17 Generating a Report that Includes a Summary and a List
449(3)
10.18 Generating Summaries from Temporary Result Sets
452(3)
11 Using Stored Routines, Triggers, and Scheduled Events
455(26)
11.0 Introduction
455(3)
11.1 Creating Compound-Statement Objects
458(2)
11.2 Using Stored Functions to Simplify Calculations
460(2)
11.3 Using Stored Procedures to Produce Multiple Values
462(1)
11.4 Using Triggers to Log Changes to a Table
463(3)
11.5 Using Events to Schedule Database Actions
466(2)
11.6 Writing Helper Routines for Executing Dynamic SQL
468(2)
11.7 Detecting "No More Rows" Conditions Using Condition Handlers
470(2)
11.8 Catching and Ignoring Errors with Condition Handlers
472(1)
11.9 Raising Errors and Warnings
473(1)
11.10 Logging Errors by Accessing the Diagnostic Area
474(4)
11.11 Using Triggers to Preprocess or Reject Data
478(3)
12 Working with Metadata
481(38)
12.0 Introduction
481(2)
12.1 Determining the Number of Rows Affected by a Statement
483(3)
12.2 Obtaining Result Set Metadata
486(10)
12.3 Listing or Checking the Existence of Databases or Tables
496(1)
12.4 Listing or Checking the Existence of Views
497(1)
12.5 Accessing Table Column Definitions
498(5)
12.6 Getting ENUM and SET Column Information
503(2)
12.7 Getting Server Metadata
505(2)
12.8 Writing Applications That Adapt to the MySQL Server Version
507(2)
12.9 Getting Child Tables That Reference a Specific Table via Foreign Key Constraints
509(1)
12.10 Listing Triggers
510(1)
12.11 Listing Stored Routines and Scheduled Events
511(2)
12.12 Listing Installed Plug-Ins
513(1)
12.13 Listing Character Sets and Collations
514(3)
12.14 Listing CHECK Constraints
517(2)
13 Importing and Exporting Data
519(36)
13.0 Introduction
519(4)
13.1 Importing Data with LOAD DATA and mysqlimport
523(4)
13.2 Specifying Column and Line Delimiters
527(2)
13.3 Dealing with Quotes and Special Characters
529(1)
13.4 Handling Duplicate Key Values
530(1)
13.5 Obtaining Diagnostics About Bad Input Data
531(2)
13.6 Skipping Datafile Lines
533(1)
13.7 Specifying Input Column Order
534(1)
13.8 Preprocessing Input Values Before Inserting Them
534(2)
13.9 Ignoring Datafile Columns
536(1)
13.10 Importing CSV Files
537(1)
13.11 Exporting Query Results from MySQL
538(2)
13.12 Importing and Exporting NULL Values
540(2)
13.13 Exporting Data in SQL Format
542(2)
13.14 Importing SQL Data
544(1)
13.15 Exporting Query Results as XML
545(1)
13.16 Importing XML into MySQL
546(1)
13.17 Importing Data in JSON Format
547(2)
13.18 Importing Data from MongoDB
549(1)
13.19 Exporting Data in JSON Format
550(1)
13.20 Guessing Table Structure from a Datafile
551(4)
14 Validating and Reformatting Data
555(40)
14.0 Introduction
555(1)
14.1 Using the SQL Mode to Reject Bad Input Values
556(2)
14.2 Using CHECK Constraints to Reject Invalid Values
558(2)
14.3 Using Triggers to Reject Input Values
560(2)
14.4 Writing an Input-Processing Loop
562(1)
14.5 Putting Common Tests in Libraries
563(2)
14.6 Using Pattern Matching to Validate Data
565(3)
14.7 Using Patterns to Match Broad Content Types
568(1)
14.8 Using Patterns to Match Numeric Values
568(2)
14.9 Using Patterns to Match Dates or Times
570(4)
14.10 Using Patterns to Match Email Addresses or URLs
574(1)
14.11 Using Table Metadata to Validate Data
575(3)
14.12 Using a Lookup Table to Validate Data
578(3)
14.13 Converting Two-Digit Year Values to Four-Digit Form
581(1)
14.14 Performing Validity Checking on Date or Time Subparts
582(2)
14.15 Writing Date-Processing Utilities
584(5)
14.16 Importing Non-ISO Date Values
589(1)
14.17 Exporting Dates Using Non-ISO Formats
590(2)
14.18 Preprocessing and Importing a File
592(3)
15 Generating and Using Sequences
595(40)
15.0 Introduction
595(1)
15.1 Generating a Sequence with AUTO_INCREMENT Columns
596(3)
15.2 Choosing the Data Type for a Sequence Column
599(2)
15.3 Deleting Rows Without Changing a Sequence
601(2)
15.4 Retrieving Sequence Values
603(4)
15.5 Renumbering an Existing Sequence
607(3)
15.6 Extending the Range of a Sequence Column
610(1)
15.7 Reusing Values at the Top of a Sequence
610(1)
15.8 Ensuring That Rows Are Renumbered in a Particular Order
611(1)
15.9 Sequencing an Unsequenced Table
612(2)
15.10 Managing Multiple Auto-Increment Values Simultaneously
614(1)
15.11 Using Auto-Increment Values to Associate Tables
615(2)
15.12 Using Sequence Generators as Counters
617(4)
15.13 Generating Repeating Sequences
621(1)
15.14 Using Custom Increment Values
622(2)
15.15 Using Window Functions to Number Rows in the Result Set
624(2)
15.16 Generating Series with Recursive CTEs
626(3)
15.17 Creating and Storing Custom Sequences
629(6)
16 Using Joins and Subqueries
635(40)
16.0 Introduction
635(1)
16.1 Finding Matches Between Tables
636(8)
16.2 Finding Mismatches Between Tables
644(5)
16.3 Identifying and Removing Mismatched or Unattached Rows
649(3)
16.4 Comparing a Table to Itself
652(4)
16.5 Producing Candidate-Detail Lists and Summaries
656(4)
16.6 Enumerating a Many-to-Many Relationship
660(3)
16.7 Finding Per-Group Minimum or Maximum Values
663(3)
16.8 Using a Join to Fill or Identify Holes in a List
666(3)
16.9 Using a Join to Control Query Sort Order
669(2)
16.10 Joining Results of Multiple Queries
671(2)
16.11 Referring to Join Output Column Names in Programs
673(2)
17 Statistical Techniques
675(36)
17.0 Introduction
675(1)
17.1 Calculating Descriptive Statistics
676(3)
17.2 Calculating Descriptive Statistics for Groups
679(2)
17.3 Generating Frequency Distributions
681(3)
17.4 Counting Missing Values
684(2)
17.5 Calculating Linear Regressions or Correlation Coefficients
686(2)
17.6 Generating Random Numbers
688(2)
17.7 Randomizing a Set of Rows
690(3)
17.8 Selecting Random Items from a Set of Rows
693(1)
17.9 Calculating Successive-Row Differences
694(2)
17.10 Finding Cumulative Sums and Running Averages
696(5)
17.11 Assigning Ranks
701(3)
17.12 Computing Team Standings
704(7)
18 Handling Duplicates
711(18)
18.0 Introduction
711(1)
18.1 Preventing Duplicates from Occurring in a Table
712(2)
18.2 Having More Than One Unique Key in the Table
714(1)
18.3 Dealing with Duplicates When Loading Rows into a Table
715(5)
18.4 Counting and Identifying Duplicates
720(4)
18.5 Eliminating Duplicates from a Table
724(5)
19 Working with JSON
729(32)
19.0 Introduction
729(1)
19.1 Choosing the Right Data Type
730(1)
19.2 Inserting JSON Values
731(1)
19.3 Validating JSON
732(3)
19.4 Formatting JSON Values
735(1)
19.5 Extracting Values from JSON
736(2)
19.6 Searching Inside JSON
738(1)
19.7 Inserting New Elements into a JSON Document
739(2)
19.8 Updating JSON
741(1)
19.9 Removing Elements from JSON
742(1)
19.10 Merging Two or More JSON Documents into One
742(4)
19.11 Creating JSON from Relational Data
746(2)
19.12 Converting JSON into Relational Format
748(2)
19.13 Investigating JSON
750(2)
19.14 Working with JSON in MySQL as a Document Store
752(9)
20 Performing Transactions
761(18)
20.0 Introduction
761(1)
20.1 Choosing a Transactional Storage Engine
762(1)
20.2 Performing Transactions Using SQL
763(2)
20.3 Performing Transactions from Within Programs
765(2)
20.4 Performing Transactions in Perl Programs
767(2)
20.5 Performing Transactions in Ruby Programs
769(1)
20.6 Performing Transactions in PHP Programs
770(1)
20.7 Performing Transactions in Python Programs
771(1)
20.8 Performing Transactions in Go Programs
771(1)
20.9 Using Context-Aware Functions to Handle Transactions in Go
772(4)
20.10 Performing Transactions in Java Programs
776(3)
21 Query Performance
779(38)
21.0 Introduction
779(2)
21.1 Creating Indexes
781(2)
21.2 Creating a Surrogate Primary Key
783(1)
21.3 Maintaining Indexes
784(2)
21.4 Deciding When a Query Can Use an Index
786(1)
21.5 Deciding the Order for Multiple Column Indexes
787(2)
21.6 Using Ascending and Descending Indexes
789(3)
21.7 Using Function-Based Indexes
792(2)
21.8 Using Indexes on Generated Columns with JSON Data
794(3)
21.9 Using Full Text Indexes
797(2)
21.10 Utilizing Spatial Indexes and Geographical Data
799(4)
21.11 Creating and Using Histograms
803(6)
21.12 Writing Performant Queries
809(8)
22 Server Administration
817(16)
22.0 Introduction
817(1)
22.1 Configuring the Server
817(3)
22.2 Managing the Plug-In Interface
820(2)
22.3 Controlling Server Logging
822(4)
22.4 Rotating or Expiring Logfiles
826(2)
22.5 Rotating Log Tables or Expiring Log Table Rows
828(1)
22.6 Configuring Storage Engines
829(4)
23 Monitoring the MySQL Server
833(34)
23.0 Introduction
833(1)
23.1 Why Monitor the MySQL Server?
834(6)
23.2 Discovering Sources of MySQL Monitoring Information
840(4)
23.3 Checking Server Uptime and Progress
844(1)
23.4 Troubleshooting Server Start Problems
845(1)
23.5 Determining the IO Utilization of the MySQL Server
846(3)
23.6 Determining MySQL Threads CPU Utilization
849(2)
23.7 Determining if MySQL Has Reached Its Connection Limits
851(2)
23.8 Verifying That the Buffer Pool Is Sized Properly
853(3)
23.9 Finding Information About the Storage Engine
856(3)
23.10 Using the Error Log File to Troubleshoot MySQL Server Crashes
859(2)
23.11 Slow Query Log File
861(2)
23.12 Monitoring with the General Query Log
863(2)
23.13 Using the Binary Log to Identify Changes
865(2)
24 Security
867(24)
24.0 Introduction
867(1)
24.1 Understanding the mysql. user Table
868(1)
24.2 Managing User Accounts
869(3)
24.3 Implementing a Password Policy
872(2)
24.4 Checking Password Strength
874(1)
24.5 Expiring Passwords
875(1)
24.6 Assigning Yourself a New Password
876(1)
24.7 Resetting an Expired Password
877(1)
24.8 Finding and Removing Anonymous Accounts
878(1)
24.9 Modifying "Any Host" and "Many Host" Accounts
879(1)
24.10 Using TLS (SSL)
880(3)
24.11 Using Roles
883(2)
24.12 Using Views to Secure Data Access
885(2)
24.13 Using Stored Routines to Secure Data Modifications
887(4)
Index 891
Sveta Smirnova is a MySQL Support Engineer at Percona. Her main professional interests are problem-solving, working with tricky issues and bugs, and teaching others how to deal with MySQL issues, bugs and gotchas effectively. In addition to authoring "MySQL Troubleshooting" and "JSON UDF Functions for MySQL", Sveta has spoken at many events, including Fosdem, Percona Live, and Oracle Open World. Alkin Tezuysal is a Senior Technical Manager at Percona. He has extensive experience in enterprise relational databases, working in various sectors for large corporations. With more than 20 years of industry experience, Alkin has acquired skills for managing large projects from the ground up to production.