Oreilly MySQL Cookbook.pdf

MySQL Cookbook

By Paul DuBois

Publisher

: O'Reilly

Pub Date

: October 2002

Preface

The MySQL database management system has become quite popular in recent years. This has

been true especially in the Linux and open source communities, but MySQL's presence in the

commercial sector now is increasing as well. It is well liked for several reasons: MySQL is fast,

and it's easy to set up, use, and administrate. MySQL runs under many varieties of Unix and

Windows, and MySQL-based programs can be written in many languages. MySQL is especially

heavily used in combination with a web server for constructing database-backed web sites that

involve dynamic content generation.

With MySQL's rise in 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 . It's 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 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 attempt to

develop full-fledged applications. Instead, it's 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 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, PDF files, or

word processing documents. Another common question is, "Can I access tables from two

databases 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 hard to do until you see how; this book will

show you. Other things that you'll learn from this book include:

• How to use SQL to select, sort, and summarize records.

• How to find matches or mismatches between records in two tables.

• How to perform a transaction.

• How to determine intervals between dates or times, including age calculations.

•

How to remove duplicate records.

•

How to store images into MySQL and retrieve them for display in web pages.

•

How to convert the legal values of an ENUM column into radio buttons in a web page,

or the values of a SET column into checkboxes.

•

How to get LOAD DATA to read your datafiles properly, or find out which values in the

file are bad.

• How to use pattern matching techniques to cope with mismatches between the CCYY-

MM-DD date format that MySQL uses and dates in your datafiles.

• How to copy a table or a database to another server.

• How to resequence a sequence number column, and why you really don't want to.

One part of knowing how to use MySQL is understanding how to communicate with the

server—that 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? SQL by itself has little facility for making these

kinds of choices, which makes it difficult to use decision-based logic to determine which

queries to execute. Or what if you need to generate a specialized report with very specific

formatting requirements? This too is difficult to achieve using just SQL. 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 the ability to exploit

MySQL's capabilities in the following ways:

• You can remember the result from a query and use it at a later time.

• You can make decisions based on success or failure of a query, or on the content of

the rows that are returned. Difficulties in implementing control flow disappear when

using an API because the host language provides facilities for expressing decision-

based logic: if-then-else constructs, while loops, subroutines, and so forth.

•

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 write a datafile expressed in XML.

When you combine SQL with a general purpose programming language and a MySQL client

API, you have an extremely flexible framework for issuing queries and processing their results.

Programming languages increase your expressive capabilities by giving you a great deal of

additional power to perform complex database operations. This doesn't 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.

I'll leave it to you to combine these techniques within 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 that

are perhaps not works of art, but certainly applications that are useful and that will help you

and others be more productive.

MySQL APIs Used in This Book

MySQL programming interfaces exist for many languages, including (in alphabetical order) C,

C++, Eiffel, Java, Pascal, Perl, PHP, Python, Ruby, Smalltalk, and Tcl. [] Given this fact, writing

a MySQL cookbook presents an author with something of a challenge. Clearly the book should

provide recipes for doing many interesting and useful things with MySQL, but which API or

APIs should the book use? Showing an implementation of every recipe in every language

would result either in covering very few recipes or in a very, very large book! It would also

result in a lot of redundancy when implementations in different languages bear a strong

resemblance to each other. On the other hand, it's worthwhile taking advantage of multiple

languages, because one language often will be more suitable than another for solving a

particular type of problem.

[] To see what APIs are currently available, visit the development portal at the

MySQL web site, located at http://www.mysql.com/portal/development/html/ .

To resolve this dilemma, I've picked a small number of APIs from among those that are

available and used them to write the recipes in this book. This limits its scope to a manageable

number of APIs while allowing some latitude to choose from among them. The primary APIs

covered here are:

Perl

Using the DBI module and its MySQL-specific driver

PHP

Using its set of built-in MySQL support functions

Python

Using the DB-API module and its MySQL-specific driver

Java

Using a MySQL-specific driver for the Java Database Connectivity (JDBC) interface

Why these languages? Perl and PHP were easy to pick. Perl is arguably the most widely used

language on the Web, and it became so based on certain strengths such as its text-processing

capabilities. In particular, it's very popular for writing MySQL programs. PHP also is widely

deployed, and its use is increasing steadily. One of PHP's strengths is the ease with which you

can use it to access databases, making it a natural choice for MySQL scripting. Python and

Java are not as popular as Perl or PHP for MySQL programming, but each has significant

numbers of followers. In the Java community in particular, MySQL seems to be making strong

inroads among developers who use JavaServer Pages (JSP) technology to build database-

backed web applications. (An anecdotal observation: After I wrote MySQL (New Riders),

Python and Java were the two languages not covered in that book that readers most often

said they would have liked to have seen addressed. So here they are!)

I believe these languages taken together reflect pretty well the majority of the existing user

base of MySQL programmers. If you prefer some language not shown here, you can still use

this book, but be sure to pay careful attention to Chapter 2 , to familiarize yourself with the

book's primary API languages. Knowing how database operations are performed with the APIs

used here will help you understand the recipes in later chapters so that you can translate them

into languages not discussed.

Who This Book Is For

This book should be useful for anybody who uses MySQL, ranging from novices who want to

use a database for personal reasons, to professional database and web developers. The book

should also appeal to people who do not now use MySQL, but would like to. For example, it

should be useful to beginners who want to learn about databases but realize that Oracle isn't

the best choice for that.

If you're relatively new to MySQL, you'll probably find lots of ways to use it here that you

hadn't thought of. If you're more experienced, you'll probably be familiar with many of the

problems addressed here, but you may not have had to solve them before and should find the

book a great timesaver; take advantage of the recipes given in the book and use them in your

own programs rather than figuring out how to write the code from scratch.

The book also can be useful for people who aren't even using MySQL. You might suppose that

because this is a MySQL cookbook and not a PostgreSQL cookbook or an InterBase cookbook

that it won't apply to databases other than MySQL. To some extent that's true, because some

of the SQL constructs are MySQL-specific. On the other hand, many of the queries are

standard SQL that is portable to many other database engines, so you should be able to use

them with little or no modification. And several of our programming language interfaces

provide database-independent access methods; you use them the same way regardless of

which database you're connecting to.

The material ranges from introductory to advanced, so if a recipe describes techniques that

seem obvious to you, skip it. Or if you find that you don't understand a recipe, it may be best

to set it aside for a while and come back to it later, perhaps after reading some of the

preceding recipes.

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: