Sql 简明教程

SQL - DROP Table

SQL 为数据库提供了完全删除现有表的命令。一旦发布 SQL DROP 命令，就无法找回该表，包括其中的数据，因此在生产系统中发布此命令前要谨慎。

SQL provides command to DROP an existing table completely in a database. Once SQL DROP command is issued then there is no way back to recover the table including its data, so be careful before issuing this command in production system.

The SQL DROP Table Statement

SQL DROP TABLE 语句是个数据定义语言 (DDL) 命令，用于移除表的定义及其数据、索引、触发器、约束和权限规范（如果有）。

The SQL DROP TABLE statement is a Data Definition Language (DDL) command that is used to remove a table’s definition, and its data, indexes, triggers, constraints and permission specifications (if any).

Note −

You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.
If the table is partitioned, the statement removes the table definition, all its partitions, all data stored in those partitions, and all partition definitions.
To drop a table in a database, one must require ALTER permission on the said table and CONTROL permissions on the table schema.
Even though it is a data definition language command, it is different from TRUNCATE TABLE statement as the DROP statement completely frees the table from the memory.
DROP TABLE causes an implicit commit, except when used with the TEMPORARY keyword.

Syntax

此 DROP TABLE 语句的基本语法如下 −

The basic syntax of this DROP TABLE statement is as follows −

DROP TABLE table_name;

Example

假设我们使用 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表，如下所示 −

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS(
   ID          INT NOT NULL,
   NAME        VARCHAR (20) NOT NULL,
   AGE         INT NOT NULL,
   ADDRESS     CHAR (25),
   SALARY      DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

让我们使用 DESC 命令首先验证 CUSTOMERS 表，然后会从数据库中删除它 −

Let us first verify the CUSTOMERS table using the DESC command then we will delete it from the database −

DESC Table

如果表创建成功，DESC 命令会显示表的结构，如下所示 −

If the table is created successfully the DESC command displays the structure of the table as shown below −

Field

Type

Null

Key

Default

Extra

int(11)

PRI

NULL

NAME

varchar(20)

NULL

AGE

int(11)

NULL

ADDRESS

char(25)

YES

NULL

SALARY

decimal(18,2)

YES

NULL

这意味着 CUSTOMERS 表在数据库中可用，因此现在我们丢弃它，如下所示。

This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below.

DROP TABLE CUSTOMERS;

Output

输出显示如下 −

The output is displayed as follows −

Query OK, 0 rows affected (0.001 sec)

Verification

现在，要验证表是否确实被删除，可以使用 DESC CUSTOMERS 命令，如下所示 −

Now, to verify if the table is actually dropped, you can use the DESC CUSTOMERS command as shown −

DESC CUSTOMERS;

显示了以下错误 −

Following error is displayed −

ERROR 1146 (42S02): Table 'tutorials.CUSTOMERS' doesn't exist

The IF EXISTS Clause

在删除表之前，不必总是检查它是否存在于数据库中，而可以使用 DROP TABLE 语句中的 IF EXISTS 子句。

Instead of always checking if the table exists or not in a database before dropping it, you can use the IF EXISTS clause in the DROP TABLE statement.

当在 DROP TABLE 查询中指定此子句时，它将自动检查指定的表是否在当前数据库中存在，然后在存在的情况下删除它。如果表不存在于数据库中，则将忽略该查询。

This clause, when specified in the DROP TABLE query, will automatically check whether the table exists in the current database and then drops it, if yes. If the table does not exist in the database, the query will be ignored.

Syntax

以下是 DROP TABLE IF EXISTS 的基本语法 −

Following is the basic syntax of DROP TABLE IF EXISTS −

DROP TABLE [IF EXISTS] table_name;

Example

如果您尝试删除数据库中不存在的表（不使用 IF EXISTS 子句），如下所示 −

If you try to drop a table that doesn’t exist in the database, without using the IF EXISTS clause, as shown below −

DROP TABLE CUSTOMERS;

将生成一个错误：

An error will be generated −

ERROR 1051 (42S02): Unknown table 'tutorials.CUSTOMERS'

如果您与 DROP TABLE 语句一起使用 IF EXISTS 子句，如下所示，则会删除指定的表，如果具有给定名称的表不存在，则将忽略该查询。

If you use the IF EXISTS clause along with the DROP TABLE statement as shown below, the specified table will be dropped and if a table with the given name, doesn’t exist the query will be ignored.

但是，如果您尝试使用 IF EXISTS 子句删除数据库中不存在的表，如下所示 −

But if you try to drop a table that does not exist in a database, using the IF EXISTS clause, as shown below −

DROP TABLE IF EXISTS CUSTOMERS;

该查询将被忽略，并显示以下输出：

The query will be ignored with the following output displayed −

Query OK, 0 rows affected, 1 warning (0.001 sec)

DROP - TEMPORARY TABLE

您可以将 TEMPORARY 关键字与 DROP TABLE 语句一起包含在内，它只删除 TEMPORARY 表。包括 TEMPORARY 关键字是防止意外删除非 TEMPORARY 表的有效方法。

You can include TEMPORARY keyword with DROP TABLE statement which will drop only TEMPORARY tables. Including the TEMPORARY keyword is a good way to prevent accidentally dropping non-TEMPORARY tables.

Syntax

DROP TEMPORARY TABLE TEMP_TABLE;

Example

以下是一删除临时表 CUSTOMERS 的示例。

Following is an example to delete a temporary table CUSTOMERS.

DROP TEMPORARY TABLE CUSTOMERS;