Learning Mysql

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) 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 inset sec)
Let’s consider all this in detail. The CREATE TABLE
statement has three major sections. The CREATE TABLE
statement, which is followed by the table name to create. In this example, it’s artist. 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 and artist_name CHAR(128)
default NULL. We’ll discuss these in a moment. 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

Download 4.24 Mb.

Share with your friends: