Syntax
------ 
DROP [TEMPORARY] TABLE [IF EXISTS] [/*COMMENT TO SAVE*/]
 tbl_name [, tbl_name] ...
 [WAIT n|NOWAIT]
 [RESTRICT | CASCADE]
 
Description
----------- 
DROP TABLE removes one or more tables. You must have the
DROP privilege
for each table. All table data and the table definition are
removed, as well as triggers associated to the table, so be
careful with this statement! If any of the tables named in
the argument list do
not exist, MariaDB returns an error indicating by name which
non-existing tables
it was unable to drop, but it also drops all of the tables
in the list that do
exist.
 
Important: When a table is dropped, user privileges on the
table are not
automatically dropped. See GRANT.
 
If another connection is using the table, a metadata lock is
active, and this statement will wait until the lock is
released. This is also true for non-transactional tables.
 
Note that for a partitioned table, DROP TABLE permanently
removes the table
definition, all of its partitions, and all of the data which
was stored in
those partitions. It also removes the partitioning
definition (.par) file
associated with the dropped table.
 
For each referenced table, DROP TABLE drops a temporary
table with that name, if it exists. If it does not exist,
and the TEMPORARY keyword is not used, it drops a
non-temporary table with the same name, if it exists. The
TEMPORARY keyword ensures that a non-temporary table will
not accidentally be dropped.
 
Use IF EXISTS to prevent an error from occurring for tables
that do not
exist. A NOTE is generated for each non-existent table when
using
IF EXISTS. See SHOW WARNINGS.
 
If a foreign key references this table, the table cannot be
dropped. In this case, it is necessary to drop the foreign
key first.
 
RESTRICT and CASCADE are allowed to make porting from other
database systems easier. In MariaDB, they do nothing.
 
Since MariaDB 5.5.27, the comment before the tablenames
(that /*COMMENT TO SAVE*/) is stored in the binary log. That
feature can be used by replication tools to send their
internal messages.
 
It is possible to specify table names as db_name.tab_name.
This is useful to delete tables from multiple databases with
one statement. See Identifier Qualifiers for details.
 
The DROP privilege is required to use DROP TABLE on
non-temporary tables. For temporary tables, no privilege is
required, because such tables are only visible for the
current session.
 
Note: DROP TABLE automatically commits the current active
transaction,
unless you use the TEMPORARY keyword.
 
WAIT/NOWAIT
 
Set the lock wait timeout. See WAIT and NOWAIT.
 
DROP TABLE in replication
 
DROP TABLE has the following characteristics in replication:
DROP TABLE without IF EXISTS for tables that don't exist
are not written to the binary log.
Dropping of TEMPORARY tables are prefixed in the log with
TEMPORARY. These drops are only logged when running
statement or mixed mode replication.
One DROP TABLE statement can be logged with up to 3
different DROP statements:
DROP TEMPORARY TABLE
list_of_non_transactional_temporary_tables
DROP TEMPORARY TABLE list_of_transactional_temporary_tables
DROP TABLE list_of_normal_tables
 
Starting from MariaDB 10.0.8, DROP TABLE on the master is
treated on the slave as DROP TABLE IF EXISTS. You can change
that by setting slave-ddl-exec-mode to STRICT.
 
Dropping an Internal #sql-... Table
 
If the mysqld process is killed during an ALTER TABLE you
may find a table named #sql-... in your data directory. In
MariaDB 10.3, InnoDB tables with this prefix will de deleted
automatically during startup.
In MariaDB 10.4 we will ensure that these temporary tables
will always be deleted automatically.
 
If you want to delete one of these tables explicitly you can
do so by using the following syntax:
 
DROP TABLE `#mysql50##sql-...`;
 
When running an ALTER TABLE…ALGORITHM=INPLACE that
rebuilds the table, InnoDB will create an internal #sql-ib
table. For these tables, the .frm file will be called
something else. In order to drop such a table after a server
crash, you must rename the #sql*.frm file to match the
#sql-ib*.ibd file.
 
Dropping All Tables in a Database
 
The best way to drop all tables in a database is by
executing DROP DATABASE, which will drop the database
itself, and all tables in it.
 
However, if you want to drop all tables in the database, but
you also want to keep the database itself and any other
non-table objects in it, then you would need to execute DROP
TABLE to drop each individual table. You can construct these
DROP TABLE commands by querying the TABLES table in the
information_schema database. For example:
 
SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_SCHEMA,
'`.`', TABLE_NAME, '`;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';
 
Examples
-------- 
DROP TABLE Employees, Customers;
 
Notes
 
Beware that DROP TABLE can drop both tables and sequences.
This is mainly done to allow old tools like mysqldump to
work with sequences.
 


URL: https://mariadb.com/kb/en/drop-table/