MySQL DROP Table

MYSQL uses a Drop Table statement to delete the existing table. This statement removes the complete data of a table along with the whole structure or definition permanently from the database. So, you must be very careful while removing the table because we cannot recover the lost data after deleting it.

Syntax

The following are the syntax to remove the table in MySQL:

The full syntax of DROP TABLE statement in MySQL is:

The above syntax used many parameters or arguments. Let us discuss each in detail:

Parameter NameDescription
TEMPORARYIt is an optional parameter that specifies to delete the temporary tables only.
table_nameIt specifies the name of the table which we are going to remove from the database.
IF EXISTSIt is optional, which is used with the DROP TABLE statement to remove the tables only if it exists in the database.
RESTRICT and CASCADEBoth are optional parameters that do not have any impact or effect on this statement. They are included in the syntax for future versions of MySQL.

NOTE: It is to be noted that you must have a DROP privileges to execute the DROP TABLE statement in the MySQL.

Example

This example specifies how we can drop an existing table from the database. Suppose our database contains a table "orders" as shown in the image below:

MySQL DROP Table

To delete the above table, we need to run the following statement:

It will remove the table permanently. We can also check the table is present or not as shown in the below output:

MySQL DROP Table

If we try to delete a table that does not exist in the database, we will get an error message as given below:

MySQL DROP Table

If we use the IF EXISTS clause with the DROP TABLE statement, MySQL gives the warning message which can be shown in the below output:

MySQL DROP Table

How to DROP table in Workbench

1. To delete a table, you need to choose the table, right-click on it, and select the Drop Table option. The following screen appears:

MySQL DROP Table

2. Select Drop Now option in the popup window to delete the table from the database instantly.

MySQL DROP Multiple Table

Sometimes we want to delete more than one table from the database. In that case, we have to use the table names and separate them by using the comma operator. The following statement can be used to remove multiple tables:

MySQL TRUNCATE Table vs. DROP Table

You can also use the DROP TABLE command to delete the complete table, but it will remove complete table data and structure both. You need to re-create the table again if you have to store some data. But in the case of TRUNCATE TABLE, it removes only table data, not structure. You don't need to re-create the table again because the table structure already exists.






Latest Courses