Learning MySQL

Deleting Structures

In the previous section, we showed how you can delete columns and rows from a database; now we’ll describe how to remove databases and tables.

Dropping Databases

Removing, or dropping, a database is straightforward. Here’s how you drop the music database:

mysql> DROP DATABASE music;
Query OK, 4 rows affected (0.01 sec)

The number of rows returned in the response is the number of tables removed. You should take care when dropping a database, since all its tables, indexes, and columns are deleted, as are all the associated disk-based files and directories that MySQL uses to maintain them.

If a database doesn’t exist, trying to drop it causes MySQL to report an error. Let’s try dropping the music database again:

mysql> DROP DATABASE music;
ERROR 1008 (HY000): Can't drop database 'music'; database doesn't exist

You can avoid the error, which is useful when including the statement in a script, by using the IF EXISTS phrase:

mysql> DROP DATABASE IF EXISTS music;
Query OK, 0 rows affected, 1 warning (0.00 sec)

You can see that a warning is reported, since the music database has already been dropped. You can always check what the warning was with the SHOW WARNINGS statement, which has been available since MySQL 4.1.0:

mysql> SHOW WARNINGS; +-------+------+-----------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------+ | Note | 1008 | Can't drop database 'music'; database doesn't exist ...