Learning MySQL

Creating Tables

This section covers topics on table structures. We show you how to:

• Create tables, through introductory examples

• Choose names for tables and table-related structures

• Understand and choose column types

• Understand and choose keys and indexes

• Use the proprietary MySQL AUTO_INCREMENT feature

When you finish this section, you’ll have completed all of the basic material on creating database structures; the remainder of this chapter covers the sample music database used in the book, and how to alter and remove existing structures.

Basics

For our examples in this section, we’ll assume that the database music hasn’t been created. If you want to follow the examples, and you have already loaded the database, you can drop it for this section and reload it later; dropping it removes the database, tables, and all of the data, but the original is easy to restore by following the steps in Chapter 2. Here’s how you drop it temporarily:

mysql> DROP DATABASE music;
Query OK, 4 rows affected (0.06 sec)

The DROP statement is discussed further at the end of this chapter in Deleting Structures.”

To begin, create the database music using the statement:

mysql> CREATE DATABASE music;
Query OK, 1 row affected (0.00 sec)

Then select the database with:

mysql> USE music;
Database changed

We’re now ready to begin creating the tables that’ll hold our data. Let’s create a table to hold artist details. Here’s the statement that we use:

mysql> CREATE TABLE artist (
    -> artist_id SMALLINT(5) NOT NULL DEFAULT ...