Working with Database Structures - Learning MySQL - page 221

Removing Tables
Removing tables is as easy as removing a database. Let's create and remove a table from
the music database:
mysql> CREATE TABLE temp (temp INT(3), PRIMARY KEY (temp));
Query OK, 0 rows affected (0.00 sec)
mysql> DROP TABLE temp;
Query OK, 0 rows affected (0.00 sec)
Don't worry: the 0 rows affected message is misleading. You'll find the table is defi-
nitely gone.
You can use the IF EXISTS phrase to prevent errors. Let's try dropping the temp table
again:
mysql> DROP TABLE IF EXISTS temp;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Again, you can investigate the warning indicates with the SHOW WARNINGS statement:
mysql> SHOW WARNINGS;
+-------+------+----------------------+
| Level | Code | Message |
+-------+------+----------------------+
| Note | 1051 | Unknown table 'temp' |
+-------+------+----------------------+
1 row in set (0.00 sec)
You can drop more than one table in a single statement by separating table names with
commas:
mysql> DROP TABLE IF EXISTS temp, temp1, temp2;
Query OK, 0 rows affected, 3 warnings (0.00 sec)
You can see three warnings because none of these tables existed.
Exercises
All exercises here concern the music database. You'll find that the CREATE TABLE state-
ments in “The Sample Music Database” are a useful reference.
1. You've decided to store more information about artists and albums. Specifically,
for artists, you want to store the names of people who have worked with the artist
(for example, vocalists, guitarists, trumpeters, and drummers), when they began
working with the artist, and when they stopped working with the artist (if they
have done so).
For albums, you want to store the name of the album producer, when the album
was released, and where the album was recorded. Design tables or columns that
can store this information, and explain the advantages and disadvantages of your