MySQL Drop Database Tutorial and Examples

When we don’t need a database, we can delete the database. In MySQL, the DROP DATABASE statement is used to drop a database.

Note: The DROP DATABASE statement will permanently delete the database and all tables in the database, please proceed with caution. You should back up the database before you drop it.

DROP DATABASE syntax

This is the syntax for the DROP DATABASE statement:

DROP {DATABASE | SCHEMA} [IF EXISTS] database_name;

Here:

• DROP DATABASE is the same as DROP SCHEMA.
• Specify the name of the database to delete after the DROP DATABASE keyword.
• The option IF EXISTS is used to avoid errors when dropping a database that does not exist. It is optional.

The DROP DATABASE statement returns the number of tables it dropped.

DROP DATABASE Examples

We created a database with the name newdb in the previous chapter and now we will drop it by following the steps below.

1. Log in to the MySQL database as the root user:

   mysql -u root -p
   

   Enter the password of the root user.

   Note: You can also log in as any other user with permissions to create databases.

2. List all databases in the current server using the following statement:

   SHOW DATABASES;
   

   +-----------------------+
   | Database              |
   +-----------------------+
   | information_schema    |
   | mysql                 |
   | newdb                 |
   | performance_schema    |
   | sakila                |
   | sys                   |
   +-----------------------+

3. Drop the database using the following statement:

   DROP DATABASE newdb;
   

   Query OK, 0 rows affected (0.00 sec)

   Here, 0 rows affected indicates that there are no tables in the newdb database.

4. Verify that whether the deletion was successful by looking at the list of databases on the current server:

   SHOW DATABASES;
   

   +-----------------------+
   | Database              |
   +-----------------------+
   | information_schema    |
   | mysql                 |
   | performance_schema    |
   | sakila                |
   | sys                   |
   +-----------------------+
   5 rows in set (0.01 sec)

   As can be seen from the output, there is no database nameed newdb in the list.

Conclusion

In this article, we discussed dropping a database using the DROP DATABASEstatement . The main points of this article are as follows:

• DROP DATABASE is the same as DROP SCHEMA.
• Specify the name of the database to delete after the DROP DATABASE keyword.
• The option IF EXISTS is used to avoid errors when dropping a database that does not exist. It is optional.

Always be aware that the DROP DATABASE statement physically drops the database and all tables in the database. This operation cannot be revoked, please operate with caution.

If