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.
Introduction to the MySQL DROP DATABASE statement
The DROP DATABASE
statement drops all tables in the database and deletes the database permanently. Therefore, you need to be very careful when using this statement.
The following shows the syntax of the DROP DATABASE
statement:
DROP DATABASE [IF EXISTS] database_name;
Code language: SQL (Structured Query Language) (sql)
In this statement, you specify the name of the database which you want to delete after the DROP DATABASE
keywords.
If you drop a database that does not exist, MySQL will issue an error.
To prevent an error from occurring if you delete a non-existing database, you can use the IF EXISTS
option. In this case, MySQL will terminate the statement without issuing any error.
The DROP DATABASE
statement returns the number of tables it deleted.
In MySQL, the schema is the synonym for the database. Therefore, you can use them interchangeably:
DROP SCHEMA [IF EXISTS] database_name;
Code language: SQL (Structured Query Language) (sql)
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:
mysql -u root -p
Code language: Shell Session (shell)
Type the password for the root
user and press Enter.
Enter password: ********
Code language: Shell Session (shell)
Second, display all the databases using the SHOW DATABASES
statement:
SHOW DATABASES;
Code language: SQL (Structured Query Language) (sql)
Output:
+--------------------+
| Database |
+--------------------+
| classicmodels |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb2 |
+--------------------+
7 rows in set (0.00 sec)
Code language: Shell Session (shell)
Third, drop the testdb
database by issuing the DROP DATABASE
statement:
DROP DATABASE testdb;
Code language: SQL (Structured Query Language) (sql)
Output:
Query OK, 0 rows affected (0.03 sec)
Code language: Shell Session (shell)
MySQL returned zero affected rows indicating that the testdb
database has no tables.
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’ll see the SQL statement that will be executed. If you choose Drop Now, it’ll delete the database 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.
Summary
- Use the MySQL
DROP DATABASE
statement to delete a database.