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 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;
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 the table that doesn’t exist, MySQL throws 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, 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;
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
It opens a Drop Table pop up window
If you want, you can review the query generated by selecting the Review SQL or else, click the Drop Now button.
Let us see the tables under the sqltest schema.
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;
We successfully deleted or dropped two tables.
