Databases Reference
In-Depth Information
mysql>
DESC artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Let's examine the table structure more closely. As you'd expect from the ER model in
Figure 5-1, the
artist
table contains two columns,
artist_id
and
artist_name
. The
other information in the output shows the types of the columns—an integer of length
5 for
artist_id
and a character string of length 128 for
artist_name
—and whether the
column is allowed to be
NULL
(empty), whether it's part of a key, and the default value
for it. You'll notice that the
artist_id
has
PRI
in the
Key
column, meaning it's part of
the primary key for the table. Don't worry about the details; all that's important right
now is the column names,
artist_id
and
artist_name
.
We'll now explore the other three tables. Here are the
SHOW COLUMNS
statements you
need to type:
mysql>
SHOW COLUMNS FROM album;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| album_name | char(128) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int(3) | | PRI | 0 | |
| track_name | char(128) | YES | | NULL | |
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| time | decimal(5,2) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)
mysql>
SHOW COLUMNS FROM played;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| artist_id | int(5) | | PRI | 0 | |
| album_id | int(4) | | PRI | 0 | |
| track_id | int(3) | | PRI | 0 | |
| played | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
+-----------+-----------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)
