mysql 学习删除表

mysql 学习删除表

1．  删除一个没有外键约束的表

语法：drop table [if  exists] 表1,表2,…表n;

N指的是要删除的表名，后面可以同时删除多个表，如果要删除的表不存在mysql会提示错误ERROR 1051 (42S02): Unknown table 'ww' 参数if exists 用于在删除前判断表是否存在，如果表不存在加上if exists sql语句可以顺利执行，但是会发出warning

删除数据表tb_emp1;

mysql> show tables;

+-------------------+

| Tables_in_test_db |

+-------------------+

| tb_dept1          |

| tb_deptment       |

| tb_emp1           |

| tb_emp2           |

| tb_emp3           |

| tb_emp4           |

| tb_emp5           |

+-------------------+

7 rows in set (0.00 sec)

mysql> drop table tb_emp1;

Query OK, 0 rows affected (0.00 sec)

mysql> show tables;

+-------------------+

| Tables_in_test_db |

+-------------------+

| tb_dept1          |

| tb_deptment       |

| tb_emp2           |

| tb_emp3           |

| tb_emp4           |

| tb_emp5           |

+-------------------+

6 rows in set (0.00 sec)

mysql>

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

mysql> show create table tb_emp3 \G;

*************************** 1. row ***************************

       Table: tb_emp3

Create Table: CREATE TABLE `tb_emp3` (

  `id` int(11) NOT NULL,

  `name` varchar(25) DEFAULT NULL,

  `location` varchar(50) 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_deptment` (`d

tid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql>

删除有关键约束的主表db_deptment

mysql> drop table tb_deptment;

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constrai

nt fails

mysql>

因为有外键约束删除会报错，所以主表不能删除

删除时应该解除关联字表的外键约束

mysql> alter table tb_emp3 drop foreign key fk_emp_dept;

Query OK, 0 rows affected (0.16 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb_emp3 \G;

*************************** 1. row ***************************

       Table: tb_emp3

Create Table: CREATE TABLE `tb_emp3` (

  `id` int(11) NOT NULL,

  `name` varchar(25) DEFAULT NULL,

  `location` varchar(50) DEFAULT NULL,

  `deptid` int(11) DEFAULT NULL,

  `salary` float DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `fk_emp_dept` (`deptid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

再次删除表成功

mysql> drop  table tb_deptment;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

+-------------------+

| Tables_in_test_db |

+-------------------+

| tb_dept1          |

| tb_emp2           |

| tb_emp3           |

| tb_emp4           |

| tb_emp5           |

+-------------------+

5 rows in set (0.00 sec)

mysql>