Mysql的数据表删除操作

1. 删除没有被关联的数据表

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_dept2          |
| tb_emp3           |
| tb_emp4           |
| tb_emp5           |
| tb_emp6           |
| tb_emp8           |
| tb_empl           |
| tb_employ2        |
+-------------------+
9 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS tb_emp5,emp6;
Query OK, 0 rows affected, 1 warning (0.22 sec)

mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| tb_dept1          |
| tb_dept2          |
| tb_emp3           |
| tb_emp4           |
| tb_emp6           |
| tb_emp8           |
| tb_empl           |
| tb_employ2        |
+-------------------+
8 rows in set (0.00 sec)

mysql>

2. 删除被其他表关联的主表

mysql> CREATE TABLE tb_dept2
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(22),
    -> location  VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.40 sec)

mysql> CREATE TABLE tb_emp
    -> (
    -> id       INT(11) PRIMARY KEY,
    -> name    VARCHAR(25),
    -> deptId   INT(11),
    -> salary   FLOAT,
    -> CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept2(id)
    -> );
Query OK, 0 rows affected (0.30 sec)

mysql> show create table tb_emp;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                                                  |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_emp | CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptId`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table tb_dept2;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>
mysql> alter table tb_emp drop foreign key fk_emp_dept;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table tb_dept2;
Query OK, 0 rows affected (0.09 sec)

mysql>