SQL - DROP Database

The SQL DROP DATABASE statement is used to delete an existing database along with all the data such as tables, views, indexes, stored procedures, and constraints.

SQL DROP Database Statement

Following are the important points to remember before you delete an existing database −

• Make sure you have taken proper backup of the database before you delete it.
• Make sure no other application is connected and using this database.
• Make sure you have the necessary privilege to delete the database. Usually an admin can delete the databaase.

Syntax

Following is the syntax to delete a database in SQL −

 DROP DATABASE DatabaseName; 

Here, the DatabaseName is the name of the database that you want to delete. A database name is always unique within the RDBMS.

Example

First of all, let us create multiple databases into database system using the following SQL queries −

 CREATE DATABASE testDB1; CREATE DATABASE testDB2; CREATE DATABASE testDB3; CREATE DATABASE testDB4; 

Let us verify whether the databases are created or not using the following query −

 SHOW DATABASES; 

This will list down all the available databases:

Now, let us try to delete the testDB1 database using the SQL DROP DATABASE statement −

 DROP DATABASE testDB1; 

Once we have deleted the testDB1 database, we can verify whether it is deleted or not using the SQL SHOW DATABASES statement −

 SHOW DATABASES; 

This will list down all the available databases:

That's it! we have successfully deleted a database in SQL.

SQL DROP DATABASE IF EXISTS Statement

The SQL DROP DATABASE IF EXISTS statement includes a condition to check whether the database exists before trying to delete it. If the database does not exist in the database system, the "DROP DATABASE IF EXISTS" statement does not raise an error, but it simply terminates without taking any action.

Syntax

Following is the syntax of the DROP DATABASE IF EXISTS statement in SQL −

 DROP DATABASE IF EXISTS DatabaseName; 

Here, the DatabaseName is the name of the database that you want to delete.

Example

Let us try to delete an existing database testDB2 in the database system using the following SQL statement −

 DROP DATABASE IF EXISTS testDB2; 

When we execute the above SQL statement, the output is obtained as follows −

 Query OK, 0 rows affected, 3 warnings (0.024 sec) 

Dropping the Database that doesn't Exist

Let us try to drop a database testDB2 that doesn't exist in the database system using the following SQL statement −

 DROP DATABASE IF EXISTS testDB2; 

When we execute the above SQL statement, the output is obtained as follows −

 Query OK, 0 rows affected, 1 warning (0.000 sec) 

Deleting Multiple Databases

You can drop multiple databases using the SQL DROP DATABASE statement as follows:

 DROP DATABASE testDB3, testDB4;