Working with Database Structures - Learning MySQL - page 182

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 Struc-
tures.”
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 0,
-> artist_name CHAR(128) DEFAULT NULL,
-> PRIMARY KEY (artist_id)
-> );
Query OK, 0 rows affected (0.06 sec)
Don't panic: even though MySQL reports that zero rows were affected, it's definitely
created the table:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| artist |
+-----------------+
1 row in set (0.00 sec)
Let's consider all this in detail. The CREATE TABLE statement has three major sections:
1. The CREATE TABLE statement, which is followed by the table name to create. In this
example, it's artist .
2. A list of one or more columns to add to the table. In this example, we've added
two: artist_id SMALLINT(5) NOT NULL DEFAULT 0 and artist_name CHAR(128)
default NULL . We'll discuss these in a moment.
3. Optional key definitions. In this example, we've defined a single key: PRIMARY KEY
(artist_id) . We'll discuss keys and indexes in detail later in this section.
Notice that the CREATE TABLE component is followed by an opening parenthesis that's
matched by a closing parenthesis at the end of the statement. Notice also that the other