Syntax:
DROP TABLESPACE tablespace_name
   [ENGINE [=] engine_name]

This statement is used to drop an InnoDB general tablespace that was
created using CREATE TABLESPACE (see [HELP CREATE TABLESPACE]).

All tables must be dropped from the tablespace prior to a DROP
TABLESPACE operation. If the tablespace is not empty, DROP TABLESPACE
returns an error.

tablespace_name is a case-sensitive identifier in MySQL.

ENGINE: Defines the storage engine that uses the tablespace, where
engine_name is the name of the storage engine. Currently, only the
InnoDB storage engine is supported. You do not need to specify ENGINE =
InnoDB if InnoDB is defined as the default storage engine
(default_storage_engine=InnoDB).

DROP TABLESPACE is supported with InnoDB as of MySQL 5.7.6. In earlier
releases, DROP TABLESPACE supports NDB
(http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html), which is
the MySQL Cluster storage engine. DROP TABLESPACE will support NDB
(http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster.html) in MySQL
5.7 when MySQL Cluster is branched from the MyQL 5.7 code base. The
latest version of MySQL Cluster is based on MySQL 5.6.

Notes

o A general InnoDB tablespace is not deleted automatically when the
  last table in the tablespace is dropped. The tablespace must be
  dropped explicitly using DROP TABLESPACE tablespace_name.

o A DROP DATABASE operation can drop tables that belong to a general
  tablespace but it cannot drop the tablespace, even if the operation
  drops all tables that belong to the tablespace. The tablespace must
  be dropped explicitly using DROP TABLESPACE tablespace_name.

o Similar to the system tablespace, truncating or dropping tables
  stored in a general tablespace creates free space internally in the
  general tablespace .ibd data file which can only be used for new
  InnoDB data. Space is not released back to the operating system as it
  is for file-per-table tablespaces.

Example

This example demonstrates how to drop an InnoDB general tablespace. The
general tablespace ts1 is created with a single table. Before dropping
the tablespace, the table must be dropped.

mysql> CREATE TABLESPACE `ts1`
    ->     ADD DATAFILE 'ts1.ibd'
    ->     ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY)
    ->     TABLESPACE ts10
    ->     ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLESPACE ts1;
Query OK, 0 rows affected (0.01 sec)

URL: http://dev.mysql.com/doc/refman/5.7/en/drop-tablespace.html

