DuBois - MySQL Cookbook

The MySQL database management system has gained a large following in recent years. This has been true especially in the Linux and open source communities, but MySQL has an increasing foothold in the commercial sector as well. MySQL is well liked for several reasons: its fast, and its easy to set up, use, and administer. MySQL runs under many varieties of Unix and Windows, and MySQL-based programs can be written in many languages. Historically, MySQL has been especially popular for constructing database-backed web sites that involve dynamic content generation. Moreover, with the introduction of features in MySQL 5.0 such as views, triggers, and stored procedures and functions, the penetration of MySQL into other areas of application development is on the upswing.

With MySQLs popularity comes the need to address the questions posed by its users about how to solve specific problems. That is the purpose of MySQL Cookbook . Its designed to serve as a handy resource to which you can turn when you need quick solutions or techniques for attacking particular types of questions that come up when you use MySQL. Naturally, because its a cookbook, it contains recipes: straightforward instructions you can follow rather than develop your own code from scratch. Its 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 doesnt attempt to develop full-fledged, complex applications. Instead, its intended to assist 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 Im writing queries? Thats not difficult, but figuring out how to do it is frustrating when youre 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 whats involved, youll 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 its easy to do because its just a matter of knowing the proper SQL syntax. But its not always clear how until you see examples, which this book gives you. Other things that youll learn from this book include:

One part of knowing how to use MySQL is understanding how to communicate with the serverthat is, how to use SQL, the language through which queries are formulated. Therefore, one major emphasis of this book is on 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. By using this client interactively, you can send SQL statements to the server and see the results. This is extremely useful because it provides a direct interface to SQL. The mysql client is so useful, in fact, that the entire first chapter is devoted to it.

But the ability to issue SQL queries alone is not enough. Information extracted from a database often needs to be processed further or presented in a particular way to be useful. 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? Or 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 bookhow 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 the ability to exploit MySQLs capabilities in the following ways:

When you combine SQL with a general purpose programming language, you have an extremely flexible framework for issuing queries and processing their results. Programming languages increase your capabilities by giving you a great deal of additional power to perform complex database operations. This doesnt mean this book is complicated, though. It keeps things simple, showing how to construct small building blocks by using techniques that are easy to understand and easily mastered.

Ill 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 can be 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.