Why to delete the database?

A database always created for purpose either it may be as per requirement or it may be created while we are learning. If the database is created wrongly or by mistakenly with some other name or clear the entire database after backup, it should be deleted.

DROP DATABASE statement fulfils this purpose.

How to delete the database?

DROP DATABSE is used to delete or drop the database that is already existing. The syntax for database deletion is -

database_name - Specifies the existing database name.

If optional IF EXISTS keywords included while dropping a database, the database server takes no action if no database exists with the specified name.

In a DROP DATABASE operation, the database server gets a lock on each table in the database and holds the locks until the entire operation is complete.

Points to Note –

There are some most important points to keep in mind before executing DROP DATABASE are -

• Before removing the database, make sure that it was communicated to all the stakeholders and shutdown connections that are connecting to the database.
• Be cautious while deleting the database, because deleting database deletes all the database objects in the database and cannot restore back.
• In real-time project, as a developer we do not have access to delete the database. There is separate team called database admin team to perform all DDL transactions.

Consider we are going to delete the existing database for employee's name emptdb. The code for deleting the database is -

DROP DATABSE emptdb; 

After executing the above query, emptdb gets deleted. As we discussed earlier, we must have admin privileges to delete the database.

Verify the Database

Once the database is successfully deleted, we can verify the database deleted or not by using below statement –

SHOW DATABASES [LIKE pattern]; 

• pattern - Specifies the partial string of the database name.

Consider above example, we can verify the database deletion by using the below query –

SHOW DATABASES LIKE "emp%"; 

Once the above query executed, it displays the results like below -

+--------------------+ 
| Database           |
+--------------------+
|                    | 
+--------------------+
0 rows in set (0.00 sec) 

What happen if try to delete the non-existing database?

When try to drop a database that does not exist, system throws the error: "Can't drop database 'database-name'; database doesn't exist."