Learning MySQL

The Sample Music Database

We’ve used the music database extensively in this and the previous chapter, so you’re already familiar with its structure. This section explains the steps we took to express our sample music database as SQL statements for loading into MySQL. It also lists the complete SQL statements used to create the structures, which you’ll find a useful reference for discussions in later chapters.

Let’s begin by discussing how we structured the file that contains the SQL statements. You can download the file music.sql from the book’s web site. We created the table using the monitor, and created the file from the output of one of MySQL’s commands for dumping SQL, and then edited it for readability. You’ll find more about how to dump SQL statements to a file in Chapter 10.

The music.sql file is structured as follows:

1. Drop the database if it exists, and then create it.

2. Use the database.

3. Create the tables.

4. Insert the data.

This structure allows you to reload the database—using the SOURCE command discussed in Chapter 3—at any time without having to worry about whether the database, tables, or data exist. Loading the file just wipes the database and starts again. Of course, in a production environment, always ensure your backups are reasonably up-to-date before commencing a restore operation that involves dropping tables or deleting existing data.

The first three lines of the file carry out the first two steps:

DROP DATABASE IF EXISTS music;
CREATE DATABASE music;
USE music;

The ...