Learning MySQL (2007)

Preface

Database management systems are the electronic filing cabinets that help individuals and organizations to manage the mass of information they process each day. With a well-designed database, information can be easily stored, updated, accessed, and collated. For example, a freight company can use a database to record data associated with each shipment, such as the sender and recipient, origin and destination, dispatch and delivery time, current location, and shipping fee. Some of this information needs to be updated as the shipment progresses. The current status of a shipment can be read off the database at any time, and data on all shipments can also be summarized into regular reports.

The Web has inspired a new generation of database use. It’s now very easy to develop and publish multi-user applications that don’t require any custom software to be installed on each user’s computer. Adding a database to a web application allows information to be automatically collected and used. For example, a customer can visit an online shopping site, see what’s in stock, place an order, submit payment information, and track the order until the goods are delivered. He can also place advance orders for goods that aren’t available, and submit reviews and participate in discussions on items he has purchased. If all goes well, the site’s staff doesn’t need to intervene in any of these actions; the less staff intervention required during normal operation, the more scalable the application is to large numbers of users. The staff are then free to do more productive tasks, such as monitoring sales and stock in real time, and designing special promotions based on product sales.

Both authors of this book have always been interested in using computers as a tool to make things faster, more efficient, and more effective. Over the past few years we’ve repeatedly found that the MySQL database management system—and the PHP and Perl programming languages—provide a perfect platform for serious applications such as managing research records and marking student assignments, and not-so-serious ones like running the office sweepstakes. On the way, we’ve learned a lot of lessons that we’d like to pass on; this book contains the tips that we think most readers will find useful on a daily basis.

Who This Book Is for

This book is primarily for people who don’t know much about deploying and using an actual database-management system, or about developing applications that use a database. We provide a readable introduction to relational databases, the MySQL database management system, the Structured Query Language (SQL), and the PHP and Perl programming languages. We also cover some quite advanced material that will be of interest even to experienced database users. Readers with some exposure to these topics should be able to use this book to expand their repertoire and deepen their understanding of MySQL in particular, and database techniques in general.

What’s in the Book

The book is divided into six main parts:

1. Introduction

2. Using MySQL

3. Advanced Topics

4. Web Database Applications with PHP

5. Interacting with MySQL using Perl

6. Appendix

Let’s look at how the individual chapters are laid out.

Introduction

We first provide some context for the book in Chapter 1, where we describe how MySQL and web database applications fit into the domain of information management tools and technologies.

In Chapter 2, we explain how you can configure the software required for this book on different operating systems. This chapter provides far more detail than most books because we know that it’s hard to learn MySQL if you can’t first get it up and running.

Chapter 3 introduces the standard text-based interface to the MySQL server. Through this interface, you can control almost every aspect of the database server and the databases on it.

Using MySQL

Before we dive into creating and using databases, we look at proper database design in Chapter 4. You’ll learn how to determine the features that your database must have, and how the information items in your database relate to each other.

In Chapter 5, we explore how to read data from an existing MySQL database and how to store data in it.

In Chapter 6, we explain how to create a new MySQL database and how to modify an existing one.

Chapter 7 covers more advanced operations such as using nested queries and using different MySQL database engines.

Chapter 8 continues the advanced operations theme; in this chapter, you’ll find a discussion of importing and exporting data, and peeking under the hood to see how the MySQL server processes a given query.

In any serious application, you’ll need to prevent unauthorized data access and manipulation. In Chapter 9, we look at how MySQL authenticates users and how you can allow or disallow access to data or database operations.

Advanced Topics

Data stored on a computer can be lost due to hardware failure, theft, or other incidents such as fire or flood. If you need your database, you’ll save yourself a lot of hair-pulling by setting up regular and complete backups of your database structure and data. In Chapter 10, we introduce techniques that can help you easily recover from a data loss or corrupted database.

MySQL is highly configurable; in Chapter 11, we describe how you can use configuration files to modify the behavior of the MySQL server and associated programs.

In Chapter 12, we introduce several ways to customize your MySQL server and your application database for improved performance. Small speedups for frequently used queries can markedly improve the overall performance of your system.

Web Database Applications with PHP

In Chapter 13, we examine how web database applications work.

Chapter 14 follows with an introduction to the PHP programming language and a discussion of how PHP can be used to access and manipulate data in a MySQL database.

In Chapter 15, we walk through the design of a wedding gift registry to illustrate the process of developing a full-fledged web database application.

Interacting with MySQL Using Perl

In Chapter 16, we present an easy-to-follow introduction to the powerful Perl programming language.

We continue in Chapter 17 by using the Perl DBI module to connect to a MySQL database to store and read information, and to import and export data.

We conclude this part in Chapter 18 by using the Perl CGI module to create dynamic web pages that can interact with a MySQL database.

Appendix

The Appendix contains all the source code for the wedding gift registry developed in Chapter 15. You can download this source code, and much more, from the book’s web site.

Conventions Used in This Book

This book uses the following typographical conventions:

Italic

Indicates nomenclature that we’ve not previously used. Also used for emphasis and to indicate files and directories.

Constant Width

Indicates commands and command options, usernames, and hostnames. Also used to show the command output, and the contents of text and program files.

Constant Width Bold

Used in examples to indicate commands or other text that should be typed literally by the user.

Constant Width Italic

Indicates text that you should replace with your own values—for example, your own name or password. When this appears as part of text that you should type in, it is shown as Constant Width Italic Bold .

#, $

Used in some examples as the root shell prompt (#) and as the user prompt ($) under the Bourne or bash shell. Unless stated otherwise, instructions in such examples can be used with little modification from the Windows command prompt.

C:\>

Used in some examples as the Windows command prompt.

TIP

Signifies a tip, suggestion, or general note.

WARNING

Indicates a warning or caution.

Resources

Each chapter finishes with a list of books and web sites that contain further information on the topics covered. The book also has a companion web site at http://www.learningmysql.com that contains links to useful resources, frequently asked questions (FAQs), and the example code and data used in this book. It’s probably a good idea to have a quick look at the web site now so that you know what’s there; it could save you a lot of searching and typing!