Basic SQL - Learning MySQL - page 137

The structure of the music database is straightforward; it's the simplest of our three
sample databases. Let's use the MySQL monitor to explore it. If you haven't already,
start the monitor using the instructions in “Loading the Sample Databases” in Chap-
ter 3. To choose the music database as your current database, type the following:
mysql> USE music;
Database changed
mysql>
You can check that this is the active database by typing in the SELECT DATABASE( );
command:
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| music |
+------------+
1 row in set (0.00 sec)
mysql>
Now, let's explore what tables make up the music database using the SHOW TABLES
statement:
mysql> SHOW TABLES;
+-----------------+
| Tables_in_music |
+-----------------+
| album |
| artist |
| played |
| track |
+-----------------+
4 rows in set (0.01 sec)
MySQL reports that there are four tables, which map exactly to the four entities in
Figure 5-1. The SHOW statement is discussed in more detail later in “Exploring Databases
and Tables with SHOW and mysqlshow.”
So far, there have been no surprises. Let's find out more about each of the tables that
make up the music database. First, let's use the SHOW COLUMNS statement to explore the
artist table:
mysql> SHOW COLUMNS FROM 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)
The DESCRIBE keyword is identical to SHOW COLUMNS FROM , and can be abbreviated to just
DESC , so we can write the previous query as follows: