MySQL Drop Table

MySQL drop table for beginners and professionals with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

next → ← prev

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:

mysql> DROP TABLE  table_name;
OR,
mysql> DROP TABLE  schema_name.table_name;

The full syntax of DROP TABLE statement in MySQL is:

DROP [ TEMPORARY ] TABLE [ IF EXISTS ] table_name [ RESTRICT | CASCADE ];

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

Parameter Name	Description
TEMPORARY	It is an optional parameter that specifies to delete the temporary tables only.
table_name	It specifies the name of the table which we are going to remove from the database.
IF EXISTS	It is optional, which is used with the DROP TABLE statement to remove the tables only if it exists in the database.
RESTRICT and CASCADE	Both 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:

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

mysql> DROP TABLE  orders;

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

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

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:

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:

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:

DROP TABLE IF EXISTS table_name1, table_name2, table, ......., table_nameN;

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.

Next TopicMySQL Temporary Table

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com