MySQL in a Nutshell, 2nd Edition

Name

DROP DATABASE

Synopsis

DROP {DATABASE|SCHEMA} [IF EXISTS] database

Use this statement to delete a given database along with all of its tables and data. The addition of the IF EXISTS flag suppresses an error message if the database does not already exist. You must have the DROP privilege for the database to be able to delete it. Here is an example of this statement’s use:

DROP DATABASE IF EXISTS test;
Query OK, 6 rows affected (0.42 sec)

The number of tables deleted is returned in the rows affected count. If the database doesn’t exist or if there are other files in the database’s filesystem directory, an error message will be displayed. The tables will be deleted if other files exist, but the foreign file and the directory for the database won’t be removed. They will have to be deleted manually at the command line using a filesystem command such as rm in Unix or del in Windows. Here’s an example in which a foreign file is found in the database directory when dropping a database:

DROP DATABASE IF EXISTS test;

ERROR 1010 (HY000): 
Error dropping database (can't rmdir './test/', errno: 17)

SHOW TABLES FROM test;
Empty set (0.00 sec)

SHOW DATABASES LIKE 'test';

+-----------------+
| Database (test) |
+-----------------+
| test            | 
+-----------------+

In this example, we attempt to drop the database, but we are unsuccessful because of a foreign file located in the database’s directory at the filesystem level. The tables are all dropped as indicated from the results of the SHOW TABLES statement, ...