MySQL DROP database

Summary: in this tutorial, you will learn how to drop/delete/remove a database from the MySQL server using the MySQL DROP DATABASE command.

The 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

The 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.

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;

First, log in to the MySQL server Command Line Client tool using root user.

mysql -u root -p

It will ask you for the root password.

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.

SHOW DATABASES;

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;

Output:

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.