What is the DROP DATABASE statement in MySQL?

DROP DATABASE is a command used to delete all the tables, relevant files with different extensions, and all the tables and files that may have been created during execution. When you use the DROP DATABASE command on linked databases, then both the original and the linked are deleted.

The DROP DATABASE statement also removes all the files with different extensions that may have been created by MySQL itself.

Relevant files with the following extensions are deleted:

.frm, .BAK, .DAT, .HSH, .MRG, .MYD, .MYI, .TRG, .TRN, .db, .ibd, .ndb, .par, db.opt

However, if some files are still not deleted from the directory, they will have to be deleted manually by calling the DROP DATABASE statement again.

Using DROP DATABASE

We have created three new databases DB1, DB2, DB3. In SQL, we use the following command to view the databases present in our directory.

SHOW DATABASES;

+--------------+
| Database     |
+--------------+
| DB1          |
| DB2          |  
| DB3          |
+--------------+

This can be seen in the SQL directory below:

Now, we want to delete all the files of database DB3. This can easily be done by writing:

DROP DATABASE DB3;

This command deletes database DB3. Now, when you view the databases in the directory, you can confirm that DB3 has been deleted.

SHOW DATABASES;

+--------------+
| Database     |
+--------------+
| DB1          |
| DB2          |  
+--------------+