MySQL DROP DATABASE

 MySQL DROP DATABASE

Summary: in this tutorial, you will learn how to use the MySQL DROP DATABASE statement to delete an existing database in the server.

The DROP DATABASE statement drops all tables in the database and deletes the database permanently. Therefore, you should be very careful when using this statement.

The following shows the syntax of the DROP DATABASE statement:

DROP DATABASE [IF EXISTS] database_name;

In this statement, you specify the name of the database which you want to delete.

If you try to drop a database that does not exist, MySQL will issue an error.

To prevent an error from occurring if you delete a database that does not exist, you can use the IF EXISTS option. In this case, MySQL terminates the statement without issuing any error.

The DROP DATABASE the statement returns the number of tables that were deleted.

In MySQL, the schema is the synonym for the database, therefore, you can use them interchangeably:

DROP SCHEMA [IF EXISTS] database_name;

In the next section, we will use the testdb and testdb2 created in the CREATE DATABASE tutorial. If you do not have these databases available, you can follow the previous tutorial to create them.

MySQL DROP DATABASE using MySQL program example

First, log in to the MySQL Server using the root user. Note that you can use your own database user instead of the root user.

>mysql -u root -p
Enter password: ********

Type the password for the root user and press Enter.

Second, use the SHOW DATABASES statement to view all existing databases in the server:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
| testdb2            |
+--------------------+
7 rows in set (0.00 sec)

Third, issue the DROP DATABASE statement:

mysql> DROP DATABASE testdb;
Query OK, 0 rows affected (0.03 sec)

MySQL returned zero affected rows. It means that the testdb database has notable.

MySQL DROP DATABASE using MySQL Workbench

First, launch the MySQL workbench and log in to the MySQL Server.

Second, right-click the database that you want to remove for example testdb2, and choose the Drop Schema... option.

Third, MySQL Workbench displays a dialog to confirm the deletion. If you choose Review SQL, you will see the SQL statement that will be executed. In case you choose Drop Now, the database will be removed immediately.

To be safe, let’s choose Review SQL:

Fourth, once you are sure that the SQL statement is going to drop the right database, you can click the Execute button to execute the statement.

MySQL returns the following output indicating that the database is dropped successfully. Because the testdb2 is an empty database, the number of affected rows is zero.

If you view the schemas pane, you will see that the testdb2 is not on the list anymore.

In this tutorial, you have learned how to use the MySQL DROP DATABASE statement to remove an existing database in the server.