MySQL DROP TABLE

In MySQL DROP TABLE statement is used to drop/delete one or more tables from a Database. It deletes both Data and Table Definition permanently from the Database. So, be cautious before using this command, as this action cannot be undone.

Syntax of MySQL DROP TABLE

Below is the basic syntax of MySQL DROP TABLE statement:

MySQL DROP TABLE Examples

To understand MySQL DROP TABLE statement with examples let us execute the following script to create a demo Database MySQLTutorial with three Tables Customer, Author and Employee.

Above script execution will create a demo Database with Tables as shown in the following Image

mysql-drop-table-demo-database

Example 1: Drop one Table

We can execute a statement like below to drop one table at a time in MySQL. In the below script we are dropping the Customer table.

After executing the above statement if you check in the MySQL Workbench, you will no longer see the Customer Table as shown in the below image:

mysql-drop-table-after-dropping-one-table

Example 2: DROP multiple Tables

Let us execute the following MySQL DROP TABLE statement to drop Author and Employee Table. In order to DROP multiple tables in one shot, we basically need to pass the comma separated table names to the MySQL DROP TABLE statement

After executing the above statement if you look into the MySQL Workbench, you will no longer find the Author and Employee Tables as shown in the below image:

mysql-drop-table-after-dropping-multiple-tables

Example 3: DROP TABLE IF EXISTS Clause

Now, try to DROP the Customer Table which no longer existing by using the following MySQL DROP TABLE statement.

RESULT:

Error Code: 1051. Unknown table ‘mysqltutorial.customer’

From the above results we can see that MySQL raises an exception if we try to DROP a non-existing Table.

To prevent MySQL from raising an exception if Table doesn’t exists then we can use the IF EXISTS clause as shown in the below script. Basically the IF EXISTS clause in the DROP TABLE statement checks whether the Table exists, it will drop the Table if exists otherwise it will continue executing the next statement in the batch without raising any error.

Leave a Reply

Your email address will not be published. Required fields are marked *