DROP DATABASE

Use this MySQL statement to delete a given database along with all its tables and data.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

DROP {DATABASE|SCHEMA} [IF EXISTS] database

Explanation

Use this MySQL statement to delete a given database along with all 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 in MySQL.

Examples

Here is an example of this MySQL statement's use:

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

The number of tables in MySQL that have been 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've attempted to drop the database, but were unsuccessful because of a foreign file located in the database's directory at the filesystem level. The tables were all dropped as indicated from the results of the SHOW TABLES statement, but the database remains. After manually deleting the foreign file, we run the MySQL statement,DROP DATABASE again:

DROP DATABASE IF EXISTS test;
Query OK, 0 rows affected (0.43 sec)

DROP DATABASE test;

ERROR 1008 (HY000): Can't drop database 'test'; 
database doesn't exist

This time the statement was successful, as indicated by our extra attempt without the IF EXISTS flag. No tables were dropped by the second attempt because they were all deleted on the first attempt, so the number of rows affected was 0.

If a database is dropped, any user privileges specific to the database (e.g., privileges listed in the db table of the mysql database) are not automatically deleted. Therefore, if a database is later created with the same name, those user privileges will apply to the new database, a potential security risk.