Using the Music Database | 137

mysql> DESC artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows inset 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 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) | | PRI | 0 | |
| album_id | int) | | PRI | 0 | |
| album_name | char) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
3 rows inset sec)
mysql> SHOW COLUMNS FROM track;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| track_id | int) | | PRI | 0 | |
| track_name | char) | YES | | NULL | |
| artist_id | int) | | PRI | 0 | |
| album_id | int) | | PRI | 0 | |
| time | decimal) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
5 rows inset sec)
mysql> SHOW COLUMNS FROM played;
+-----------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-------+
| artist_id | int) | | PRI | 0 | |
| album_id | int) | | PRI | 0 | |
| track_id | int) | | PRI | 0 | |
| played | timestamp | YES | PRI | CURRENT_TIMESTAMP | |
+-----------+-----------+------+-----+-------------------+-------+
4 rows inset sec)

Download 4.24 Mb.

Share with your friends: