MySQL Drop Table Statement literally drops the existing table from a database. I mean, MySQL drop table removes or delete all the records in a given table along with the table structure or definition.
NOTE: You should be careful, while using this statement. You might loose your data, and you have to recreate the same table on your own.
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 Example
In this example, we are going to Drop empdetails table that we shown above.
DROP TABLE sqltest.empdetails;
Let me try to run the select statement against the table that we deleted before. You can see the response, Error Code: 1146
If you try to remove or drop table that doesn’t exist, MySQL will throw an error. For instance, let me drop the already removed table I.e., empdetails.
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;
As you see, 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;
Drop Table using Workbench
If you can access the MySQL Workbench then it was easy to delete any table. First, right-click on the table that you want to delete i.e., dim customer. Next, select the Drop Table.. option
This will open a Drop Table pop up window
If you want, you can review the query generated by selecting the Review SQL or else, simply click the Drop Now button.
Let us see the tables under the sqltest schema.
MySQL Drop Multiple Tables
In order to drop more than one table, you have to separate those table names using comma. This example drops multiple tables such as dim currency and department3 .
DROP TABLE IF EXISTS sqltest.dimcurrency, sqltest.department3;
You can see from the above screenshot, we successfully deleted or dropped two tables.