Tutorial Gateway

MySQL Drop Table

by

MySQL Drop Table Statement literally drops the existing table from a database. I mean, MySQL drop table removes or deletes all the records in a given table along with the table structure or definition. 

MySQL Drop Table Syntax

The basic syntax behind this MySQL Drop Table Statement is

DROP TABLE table_name;
Or 
DROP TABLE Schema_name.table_name;

For this demonstration, we are using the below table

MySQL Drop Table 1

MySQL Drop Table Example

In this example, we are going to Drop the empdetails table that we have shown above.

NOTE: You should be careful while using this Drop Table statement. You might lose your data, and you have to recreate the same MySQL table on your own.

DROP TABLE sqltest.empdetails;

OUTPUT

MySQL Drop Table 2

Let me try to run the select statement against the table that we deleted before. You can see the response, Error Code: 1146

MySQL Drop Table 3

If you try to remove or drop the table that doesn’t exist, MySQL throws an error. For instance, let me drop the already removed table, i.e., empdetails.

MySQL Drop Table If exists 9

From the above screenshot, you can see the error. To avoid this, we can use the IF EXISTS parameter.

DROP TABLE IF EXISTS sqltest.empdetails;

OUTPUT

MySQL Drop Table If exists 10

As you see, the above code is giving you a warning with a meaningful message.

Drop Table using Command Prompt

You can delete or drop tables from the command prompt or Terminal. Here, we are dropping the employee details table, and then trying to select records from it. As you can see, it was throwing an error.

DROP TABLE sqltest.employeedetails;

SELECT * FROM sqltest.employeedetails;

OUTPUT

MySQL Drop Table 4

Drop Table using Workbench

If you can access the MySQL Workbench, it was easy to delete any table. First, right-click on the table that you want to delete. i.e., the dim customer. Next, select the Drop Table.. option

MySQL Drop Table 5

It opens a Drop Table pop up window

MySQL Drop Table 6

If you want, you can review the query generated by selecting the Review SQL or else, click the Drop Now button.

MySQL Drop Table 7

Let us see the tables under the sqltest schema.

MySQL Drop Table 8

MySQL Drop Multiple Tables

To drop more than one table, you have to separate those table names using a comma. This example drops multiple tables, such as dim currency and department3.

DROP TABLE IF EXISTS sqltest.dimcurrency, sqltest.department3;

OUTPUT

MySQL Drop Multiple Table 11

You can see from the above screenshot, we successfully deleted or dropped two tables.