MySQL Cookbook, 4th Edition

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

The MySQL database management system is popular for many reasons. It’s fast, and it’s easy to set up, use, and administer. It runs under many varieties of Unix and Windows, and MySQL-based programs can be written in many languages.

MySQL’s popularity raises the need to address questions its users have about how to solve specific problems. That is the purpose of MySQL Cookbook: to serve as a handy resource to which you can turn for quick solutions or techniques for attacking particular types of questions that come up when you use MySQL. Naturally, because it’s a cookbook, it contains recipes: straightforward instructions you can follow rather than develop your own code from scratch. It’s written using a problem-and-solution format designed to be extremely practical and to make the contents easy to read and assimilate. It contains many short sections, each describing how to write a query, apply a technique, or develop a script to solve a problem of limited and specific scope. This book doesn’t develop full-fledged, complex applications. Instead, it assists you in developing such applications yourself by helping you get past problems that have you stumped.

For example, a common question is, How can I deal with quotes and special characters in data values when I’m writing queries? That’s not difficult, but figuring out how to do it is frustrating when you’re not sure where to start. This book demonstrates what to do; it shows you where to begin and how to proceed from there. This knowledge will serve you repeatedly because after you see what’s involved, you’ll be able to apply the technique to any kind of data, such as text, images, sound or video clips, news articles, compressed files, or PDF documents. Another common question is, Can I access data from multiple tables at the same time? The answer is Yes, and it’s easy to do because it’s just a matter of knowing the proper SQL syntax. But it’s not always clear how until you see examples, which this book gives you. Other techniques that you’ll learn from this book include how to:

• Use SQL to select, sort, and summarize rows
• Find matches or mismatches between tables
• Perform transactions
• Determine intervals between dates or times, including age calculations
• Identify or remove duplicate rows
• Use LOAD DATA to read your datafiles properly or find which values in the file are invalid
• Use CHECK constraints to prevent entry of bad data into your database
• Generate sequence numbers to use as unique row identifiers
• Use a view as a virtual table
• Write stored procedures and functions, set up triggers that activate to perform specific data-handling operations when you insert or update table rows, and use the Event Scheduler to run queries on a schedule
• Setup replication
• Manage user accounts
• Control server logging

One part of using MySQL is understanding how to communicate with the server—that is, how to use SQL, the language in which queries are formulated. Therefore, one major emphasis of this book is using SQL to formulate queries that answer particular kinds of questions. One helpful tool for learning and using SQL is the mysql client program that is included in MySQL distributions. You can use client interactively to send SQL statements to the server and see the results. This is extremely useful because it provides a direct interface to SQL; so useful, in fact, that the first chapter is devoted to mysql.

But the ability to issue SQL queries alone is not enough. Information extracted from a database often requires further processing or presentation in a particular way. What if you have queries with complex interrelationships, such as when you need to use the results of one query as the basis for others? What if you need to generate a specialized report with very specific formatting requirements? These problems bring us to the other major emphasis of the book—how to write programs that interact with the MySQL server through an application programming interface (API). When you know how to use MySQL from within the context of a programming language, you gain other ways to exploit MySQL’s capabilities:

• You can save query results and reuse them later.
• You have full access to the expressive power of a general-purpose programming language. This enables you to make decisions based on success or failure of a query, or on the content of the rows that are returned, and then tailor the actions taken accordingly.
• You can format and display query results however you like. If you’re writing a command-line script, you can generate plain text. If it’s a web-based script, you can generate an HTML table. If it’s an application that extracts information for transfer to some other system, you might generate a datafile expressed in XML or JSON.

Combining SQL with a general-purpose programming language gives you an extremely flexible framework for issuing queries and processing their results. Programming languages increase your capability to perform complex database operations. But that doesn’t mean this book is complex. It keeps things simple, showing how to construct small building blocks using techniques that are easy to understand and easily mastered.

We’ll leave it to you to combine these techniques in your own programs, which you can do to produce arbitrarily complex applications. After all, the genetic code is based on only four nucleic acids, but these basic elements have been combined to produce the astonishing array of biological life we see all around us. Similarly, there are only 12 notes in the scale, but in the hands of skilled composers, they are interwoven to produce a rich and endless variety of music. In the same way, when you take a set of simple recipes, add your imagination, and apply them to the database programming problems you want to solve, you can produce applications that perhaps are not works of art, but are certainly useful and will help you and others be more productive. MySQL Cookbook, 4th Edition download free in pdf published by Oreilly Media, Author by Sveta Smirnova, Alkin Tezuysal, released in August 2022 (Early Access).