Summary: in this tutorial, you will learn how to drop/delete/remove a database from the MySQL server using the MySQL
DROP DATABASE command.
DROP DATABASE statement is used to drop/delete/remove a database from the MySQL server. This command will remove the database along with all the tables, indexes, and constraints permanently. Therefore, you need to be very careful with this command as this will remove all the data available in the database.
You can drop/delete/remove a database from MySQL mainly in two ways:
- MySQL Command Line Client
- MySQL Workbench
Dropping a MySQL database using Command Line Client
DROP DATABASE statement allows us to drop a database from the MySQL server permanently. This will delete all the tables and other objects from that database. Therefore, you need to be very careful while 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 after the
DROP DATABASE keywords.
If you try to drop a database that does not exist, MySQL will show an error.
You can bypass the error by using
IF EXISTS after the
DROP DATABASE keywords. In this case, MySQL will terminate the statement without issuing any error. This is helpful when you work with MySQL databases using shell script.
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;
First, log in to the MySQL server Command Line Client tool using
mysql -u root -p
It will ask you for the
Enter password: *******
You give the correct
root password of the MySQL server and press enter. If everything goes well then you will see
mysql> on the screen.
Second, list all the available databases in MySQL as below.
This gives the following output:
+--------------------+ | Database | +--------------------+ | companydb | | customerdb | | information_schema | | mysql | | performance_schema | | sys | | world | +--------------------+ 7 rows in set (0.01 sec)
Third, drop the
customerdb database from MySQL using the
DROP DATABASE statement.
DROP DATABASE customerdb;
Query OK, 0 rows affected (0.08 sec)
MySQL returned zero affected rows indicating that the
customerdb database has no tables.
Dropping Database using MySQL Workbench
First, launch MySQL Workbench and login into MySQL server.
Second, right-click on the database that you want to remove, for example,
testDB and click on the
Drop Schema... option.
Third, MySQL Workbench displays a dialog to confirm the deletion.
If you choose Review SQL, the SQL statement which is going to be executed will be displayed. If you choose Drop Now, it’ll delete the database immediately.
To be safe, let’s choose Review SQL:
For the safer side, we should choose the 1st option that is 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
testDB is an empty database, the number of affected rows is zero here.