MySQL DROP Database
We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE command. It will delete the database along with all the tables, indexes, and constraints permanently. Therefore, we should have to be very careful while removing the database in MySQL because we will lose all the data available in the database. If the database is not available in the MySQL server, the DROP DATABASE statement throws an error.
MySQL allows us to drop/delete/remove a database mainly in two ways:
- MySQL Command Line Client
- MySQL Workbench
MySQL Command Line Client
We can drop an existing database in MySQL by using the DROP DATABASE statement with the below syntax:
In MySQL, we can also use the below syntax for deleting the database. It is because the schema is the synonym for the database, so we can use them interchangeably.
Parameter Explanation
The parameter descriptions of the above syntax are as follows:
| Parameter | Description |
|---|---|
| database_name | It is the name of an existing database that we want to delete from the server. It should be unique in the MySQL server instance. |
| IF EXISTS | It is optional. It is used to prevent from getting an error while removing a database that does not exist. |
Example
Let us understand how to drop a database in MySQL with the help of an example. Open the MySQL console and write down the password, if we have set during installation. Now we are ready to delete a database.
Next, use the SHOW DATABASES statement to see all available database in the server:
Suppose we want to remove a database named “mytestdb_copy”. Execute the below statement:
Now we can verify that either our database is removed or not by executing the following query. It will look like this:
From the above, we can see that the database “mytestdb_copy” is removed successfully.
Note: All the database names, table names, and table field names are case sensitive. We must have to use proper names while giving any SQL command.
DROP Database using MySQL Workbench
To drop a database using this tool, we first need to launch the MySQL Workbench and log in with the username and password to the MySQL server. It will show the following screen:
Now do the following steps for database deletion:
1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created databases. If we want to delete a database, right-click the database that you want to remove, for example, testdb_copy under the Schema menu and select Drop Schema option, as shown in the following screen.
When we click the Drop Schema option, MySQL Workbench displays a dialog box to confirm the deletion process. If we select Review SQL, it will produce the SQL statement that will be executed. And if we choose Drop Now option, the database will be deleted permanently.
If we want the safe deletion of the database, it is required to choose the Review SQL option. Once we sure, click the Execute button to execute the statement. The below screen explains it more clearly:
Once we click the execute button, MySQL will return the below message indicating that the database is dropped successfully. Since the database testdb_copy is an empty database, the number of affected rows is zero.
If we verify the schemas tab, we will not find the testdb_copy database on the list anymore.