How to delete a DATABASE in MySQL

MySQL – Delete Database

Following are some of the possible scenarios during which we might need to delete a database in MySQL :

• Database cleanup
• Testing the recovery scenario if a database is deleted unexpectedly.

Now we shall learn how to delete a database in MySQL.

Following is the syntax of SQL query to delete a DATABASE.

DROP DATABASE <database_name>;

In our previous tutorial of creating a database, we have created “students” database. Now we shall delete that database using the following SQL query.

DROP DATABASE students;

Example 1 – Delete DATABASE in MySQL

In this example, we delete or drop database named students.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| students           |
| sys                |
+--------------------+
5 rows in set (0.03 sec)

mysql> DROP DATABASE students;
Query OK, 0 rows affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Deleting a database also deletes all the data(tables, views, etc.)

Note : Make sure you have the necessary permissions to delete the table. By default root user has permissions to delete the table. If you are logged in as root, you may delete the database without any hassle.

For deleting multiple databases at a time, there is no straight forward SQL query. Of course there are workarounds, but deleting databases one at a time is recommended as one might avoid deleting a database that is not intended to delete.