MySQL (Managing and Using MySQL, 2nd Edition)

Anyone can relate to the gap in usefulness between piles of paper on a desktop and those same papers organized in a filing cabinet. The former is a mess. If you actually want a particular paper, you need to dig through the piles to find it. If you are lucky, you will just happen to start with the pile in which the desired paper is located. It could, however, require you to go through each piece of paper in each pile to find what you are seeking. With a filing cabinet, however, you know exactly where to look and will fumble through just a few papers to find your goal.

Relational databases have been acting as the electronic filing cabinets for the voluminous and complex data storage needs of large companies for over two decades. A relational database is simply the only tool capable of structuring most data so that it is actually usable—not just piles of bits on a hard drive. Until recently, you were simply out of luck if you wanted to build an application backed by a robust database on a small budget. Your choices were quite simple: shell out thousands—or even tens or hundreds of thousands—of dollars on Oracle or Sybase or build the application against "toy" databases such as Access and FileMakerPro. Thanks to such databases as mSQL, PostgreSQL, and MySQL, however, you now have a variety of choices that suit different needs. This book, of course, is the story of MySQL.[1]

1.1. Relational Databases

In the simplest terms, a database is a collection of data. An example of a nonelectronic database is the public library. The library stores books, periodicals, and other documents. When you need to locate some data at the library, you search through the card catalog or the periodicals index, or maybe you even ask the librarian. Those unsorted piles of papers on your desk also form a database. This database can potentially work, because the size of the database is incredibly small. A stack of papers certainly would not work with a larger set of data, such as the collections in the library. The library would still be a database without the card catalog, periodicals index, and librarian; it would just be an unusable database. A database generally requires some sort of organization to be of value. The paper pile, for example, is of greater value when organized into filing cabinets. So, restating our definition, a database is an organized collection of data.

The library and the stack of papers have many similarities. They are both databases of documents. It makes no sense, however, to combine them because your papers are interesting only to you, and the library contains documents of general interest. Both databases have specific purposes and are organized according to those purposes. We will therefore amend our definition a bit further: a database is a collection of data that is organized and stored according to some purpose.

Traditional paper-based databases have many disadvantages. They require a tremendous amount of physical space. Libraries occupy entire buildings, and searching a library is relatively slow. Anyone who has spent time in a library knows that it can consume a nontrivial amount of time to find the information you seek. Libraries are also tedious to maintain, and an inordinate amount of time is spent keeping the catalogs and shelves consistent. Electronic storage of a database helps to address these issues.

MySQL is not a database, per se. It is computer software that enables you to create, maintain, and manage electronic databases. This category of software is known as a database management system (DBMS). A DBMS acts as a broker between the physical database and the users of that database.

When you began managing electronic information, you almost certainly used a flat file, such as a spreadsheet. The filesystem file is the electronic version of the pile of papers on your desk. You likely came to the conclusion that this sort of ad hoc electronic database did not meet your needs anymore. A DBMS is the logical next step for your database needs, and MySQL is the first stepping stone into the world of relational DBMSs.

A relational database is a special kind of database that organizes data into tables and represents relationships among those tables. These relationships enable you to combine data from multiple tables to provide different "views" of that data. Table 1-1 describes a table that might appear in a library's database.

Table 1-1. A table of books

ISBN	Title	Author
0-446-67424-9	L.A. Confidential	James Ellroy
0-201-54239-X	An Introduction to Database Systems	C.J. Date
0-87685-086-7	Post Office	Charles Bukowski
0-941423-38-7	The Man with the Golden Arm	Nelson Algren

Tables Table 1-2 and Table 1-3 are two tables that might appear in an NBA database.

Table 1-2. A table of NBA teams

Team #	Name	Coach
1	Sacramento Kings	Rick Adelman
2	Minnesota Timberwolves	Flip Saunders
3	L.A. Lakers	Phil Jackson
4	Portland Trailblazers	Mike Dunleavy

Table 1-3. A table of NBA players

Name	Position	Team #
Vlade Divac	Center	1
Kevin Garnett	Forward	2
Kobe Bryant	Guard	3
Rasheed Wallace	Forward	4
Damon Stoudamire	Guard	4
Shaquille O'Neal	Center	3

We will get into the specifics about tables later on, but you should note a few things about these examples. Each table has a name, several columns, and rows containing data for each of the columns. A relational database represents all of your data in tables just like this and provides you with retrieval operations that generate new tables from existing ones. Consequently, the user sees the entire database in the form of tables.

Also note that the "Team #" column appears in both tables. It encodes a relationship between a player and a team. By linking the "Team #" columns you can determine that Vlade Divac plays for the Sacramento Kings. You could also figure out all the players on the Portland Trailblazers. This linking of tables is called a relational join , or join for short.

A DBMS for a relational system is often called a relational database management system (RDBMS). MySQL is an RDBMS.

Chapter 1. MySQL

Contents:

1.1. Relational Databases

Table 1-1. A table of books

Table 1-2. A table of NBA teams

Table 1-3. A table of NBA players