An existing table which is no longer required in an application can be deleted. Deletion of a table is permanent and a deleted table cannot be un-deleted. MySQL Drop table statement is used for this operation. You can delete or drop more than one table with single MySQL Drop table statement.
Dropping table means deleting the structure and data stored in the table(s). For a partitioned table, Drop Statement removes table structure, partitions, data stored in all the defined partitions, along with all the information about partitions of the table to be dropped. If you have created triggers with the table, Drop statement will delete them as well.
Syntax- MySQL Drop statement
DROP TABLE [IF EXISTS] tablename-1 [,tablename-2,…]
DROP, TABLE- keywords in MySQL Drop statement
IF EXISTS- Optional. If a table name given in the table list does not exist then drop statement will display an error message “ERROR 1051 (42S02): Unknown table database.table”. IF EXISTS option will inhibit this error message and display warning message of the DROP statement like “Query OK, 0 rows affected, 1 warning (0.15 sec)”.
Tablename-1, tablename2,…- specify one or more table names separated by commas.
A user can drop a table only if she has “DROP” privilege on it. A user can drop a table if she has created it or the owner of the table has given “DROP” privilege to her on a table. The user must have “DROP” privilege on all the table names given in the DROP statement.
Examples
This statement will try to delete a non-existing table without “IF EXISTS” option and display the error message.
DROP TABLE abc;
This DROP statement will try to delete a non-existing table with “IF EXISTS” option and display the warning message.
DROP TABLE IF EXISTS abc;
This statement will delete an existing table using “IF EXISTS” option and display the message after execution of statement.
DROP TABLE IF EXISTS temp;
Points to Remember – MySQL DROP TABLE Statement
- To delete multiple tables with one DROP statement, mention all the table names separated by commas.
- Table structure and all data rows will be completely deleted from the database.
- IF EXISTS option can be used in the statement to prevent error message appearing while dropping non-existent tables.
Read more about MySQL Drop Statement here