MySQL Drop Table 4

MySQL Drop Table

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 one along with the structure or definition. 

The basic syntax behind this MySQL Drop Table Statement is

DROP TABLE TableName;
Or 
DROP TABLE Schema_name.TableName;

For this demonstration, we are using the below

Employee Rows 1

MySQL Drop Table Example

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

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

DROP TABLE sqltest.empdetails;
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

Select Statement to show the rows 3

If you try to remove the one that doesn’t exist, it throws an error. For instance, let me delete the already removed one, 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;
MySQL Drop Table If exists 10

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

Using Command Prompt

You can use the MySQL Drop Table statement from the command prompt or Terminal to eliminate it. Here, we are deleting the employee details 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;
MySQL Drop Table 4

Using Workbench

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

MySQL Drop Table 5

It opens the following pop up window.

MySQL Drop Table 6

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

MySQL Drop Table 7

Let us see the list of them under the sqltest schema.

Available Tables ina a schema or Database 8

MySQL Drop Multiple Tables

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

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

We successfully deleted the two.

MySQL Drop Multiple Table 11