MySQL数据表操作-删除数据表(DROP TABLE)

基本语法

使用 DROP TABLE 语句可以删除一个或多个数据表，在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

语法格式如下：

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

对语法格式的说明如下：

• 表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

两点注意：

• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
• 表被删除时，用户在该表上的权限不会自动删除。

实例

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| es_diff           |
| t_create_table    |
| temp_as           |
| temp_to           |
| vc_diff           |
+-------------------+
5 rows in set (0.00 sec)

mysql> drop table if exists es_diff, vc_diff;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_create_table    |
| temp_as           |
| temp_to           |
+-------------------+
3 rows in set (0.00 sec)

mysql> drop table t_create_table;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| temp_as           |
| temp_to           |
+-------------------+
2 rows in set (0.00 sec)

删除被其它表关联的主表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

删除父表有以下两种方法：

• 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
• 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。（推荐）

推荐第二种方法，取消关联表的外键约束并删除主表

1. 创建关联表

mysql> CREATE TABLE test_db.foreign_key_father (
    -> id BIGINT auto_increment NOT NULL,
    -> name varchar(100) NULL,
    -> sex INT NULL,
    -> CONSTRAINT foreign_key_father_PK PRIMARY KEY (id)
    -> )
    -> ENGINE=InnoDB
    -> DEFAULT CHARSET=utf8mb4
    -> COLLATE=utf8mb4_general_ci
    -> COMMENT='';
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE test_db.foreign_key_child (
    -> idno BIGINT auto_increment NOT NULL,
    -> sex INT NULL,
    -> name varchar(100) NULL,
    -> salary FLOAT NULL,
    -> CONSTRAINT foreign_key_child_PK PRIMARY KEY (idno),
    -> CONSTRAINT foreign_key_child_FK FOREIGN KEY (idno) REFERENCES test_db.foreign_key_father(id)
    -> )
    -> ENGINE=InnoDB
    -> DEFAULT CHARSET=utf8mb4
    -> COLLATE=utf8mb4_general_ci
    -> COMMENT='';
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+--------------------+
| Tables_in_test_db  |
+--------------------+
| foreign_key_child  |
| foreign_key_father |
| temp_as            |
| temp_to            |
+--------------------+
4 rows in set (0.01 sec)

mysql> show create table foreign_key_child \G
*************************** 1. row ***************************
       Table: foreign_key_child
Create Table: CREATE TABLE `foreign_key_child` (
  `idno` bigint(20) NOT NULL AUTO_INCREMENT,
  `sex` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`idno`),
  CONSTRAINT `foreign_key_child_ibfk_1` FOREIGN KEY (`idno`) REFERENCES `foreign_key_father` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

foreign_key_father 为父表，foreign_key_child 为子表，存在名称为 foreign_key_child_ibfk_1 的外键约束，foreign_key_child 表的 idno 字段关联 foreign_key_father 表的 id 字段

2. 删除主表

mysql> drop table if exists foreign_key_father;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

报错，提示主表在存在外键约束时，不能被直接删除

3. 取消外键约束

mysql> alter table foreign_key_child drop foreign key foreign_key_child_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. 删除主表

mysql> drop table if exists foreign_key_father;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| foreign_key_child |
| temp_as           |
| temp_to           |
+-------------------+
3 rows in set (0.00 sec)