Drop、Truncate和Delete究竟怎么删除

本文大概2176字，读完共需25分钟前言

在数据库种有三个对表中数据进行删除的语法，分别是Drop、Truncate和Delete。关于它们的区别和相同的地方也是在面试的过程中经常遇到的。平时也是用了就用了，哪个用的习惯就用哪个。不过既然都是删除为什么还要有三个不同的语句呢？说明它们之间肯定有着不一样的地方。邓爷爷说过实践是检验真理的唯一标准。这里就通过实际的例子总结一下它们之间的一些异同点，也加深一下自己的印象。

准备

首先需要新建一个student表：

  --新建一个student表  
create table student(      
  id int AUTO_INCREMENT not null PRIMARY KEY,   
  name varchar(20) null,   
  course varchar(20) null,   
  grade numeric null   
 )

接着往student表中插入一些数据：

--数据库插入数据 一次性插入多行多列 格式为
--INSERT INTO table (字段1, 字段2,字段3) VALUES (值1,值2,值3),(值1,值2,值3),...;    
INSERT INTO student (name,course,grade) VALUES ("张飞","语文",90),("刘备","数学",70),("关羽","历史",25),("张云","英语",13)

?查询student表中的数据如下：

--查询数据   
 select * from student

准备工作已经完成，下面就通过对student表分别使用Drop、Truncate和Delete语法来进行实际操作，来看看它们之间究竟有何区别。

1.Delete

首先我们都知道delete属于DML(数据操纵语言)，在使用Delete是删除表中的数据，但不删除表的结构，并不影响表所占用的空间，表中的数据删除之后还可以通过Insert语句往表里插入数据，这就好比你欠房租了，房东把你赶了出来，你人走了，但是房子还在，可以租给其他人住。

Delete的语法结构：

DELETE FROM 表名称 WHERE 列名称 = 值

在Delete语句后面可以加where 条件语句，来删除指定的数据，如果不加where条件的话则删除整张表中的数据。

--增加where条件，删除姓名为张飞的学生   
 delete  from  student where name=张飞
?--不增加where条件   
 delete  from  student

student表中的所有数据都被删除了。

此时如果再执行insert into语句，可以发现自增主键id是从5开始自增的，所以并没有对之前的id进行归1。

接着再新建一个和student表列一样的redo中间表：

--新建redo
create table redo(      
  id int AUTO_INCREMENT not null PRIMARY KEY,   
  name varchar(20) null,   
  course varchar(20) null,   
  grade numeric null   
 )

对于student表设置一个 delete触发器，在student表中的任意数据被删除之前执行此触发器，将被删除的数据insert到redo中间表中：

--触发器
create trigger deletestudent before delete on student for each row
begin
insert into redo(id,name,course,grade)
values(old.id,old.name,old.course,old.grade);
end;

接下来执行Delete语句，删除id为1的学生

delete from student where id=1

再分别查询student表和redo表中的数据:

SELECT * from student

select * from redo

student表中id=1的数据确实被删除了，并且insert到了redo表中，说明在进行delete操作时deletestudent触发器被触发了。

2.Truncate

Truncate属于DDL(数据定义语言)，Truncate也是删除表中的数据，同样也不删除表的结构，功能和Delete语句不加where条件一样，清空所有的表中的数据。虽然功能一样，但是Truncate语句如果想加where条件也是不行的，如果能加where条件不就和Delete一样了吗。所以使用truncate,则是删除所有的数据了。那么除了一个能加where条件一个不能加where条件外，Truncate和Delete还有什么区别嘛？还是你付不起房租，房东要把你赶出去，如果房东使用Delete方法赶走你，他需要先把你赶走，再把你的锅扔出去，再把你的碗扔出去，再把你的衣服扔出去........。但是如果使用Truncate方式他只需要你将所有的东西打包好，然后把你赶走就行。

所以truncate执行起来的速度也比delete快很多，且使用的系统和事务日志资源少，Truncate操作并不会触发触发器。

-- 清空student表，不能加where条件
truncate table student 

此时如果再执行insert into语句，可以发现id是从1开始自增的，所以truncate语句能够对自增的主键进行归1。

3.Drop

Drop也属于DDL，Drop的翻译过来就是扔掉，所以当使用Drop的时候就是将整个表都扔掉，不要了，也只有不用的表，才会使用Drop进行删除。不仅仅表内的数据会被清空，而且表的定义，表的结构以及表的约束，触发器，索引也不复存在，这个世界上再也没有这个表的任何信息了，该表所占用的空间也会被全部释放。

好比你换了一位房东租房子，结果又欠费了，长期不交，房东又要赶你走，这位房东直接连你带房子都不要了，如果又有一位房客找他租这样的房子，他需要重新新建一个和这个一模一样的房子。

 --删除student表  
  drop table student

执行完Drop语句，在数据库中表的目录找不到student表，如果再执行insert语句往表中插入数据则会报错。

关于Delete、Truncate和Drop的总结

Delete和Truncate的相似性：它们都是只删除表中的数据，不删除表的结构。

Delete和Truncate的区别：Delete语句能够对添加where条件的数据进行指定删除，则Truncate是直接清空整张表，所以truncate的执行速度也比delete快。另外 Truncate只能对表起作用，而Delete不仅可以删除表还可以删除视图中的数据。另外Truncate能够对自增的主键进行归1，而Delete则不能进行归1。

Delete属于DML(数据操纵语言)，对于表中数据的删除是逐条删除的,Delete语句每次删除一行，并在事务日志中为所删除的每行记录一项，当事务被提交之后才生效，所以Delete语句能够进行回滚操作。如果有对应的触发器，则在执行的时候还会触发触发器。

Truncate属于DDL，通过释放存储表数据所用的数据页来删除数据，所以对于数据的操作是一次性清空所有的数据，并且只在事务日志中记录页的释放，Truncate语句的删除操作会被隐式提交如何删除数据，不能够回滚操作，删了就是删了，并且也不会触发触发器。

Truncate和Drop的相似性：它们都是DDL(数据定义语言)，并且它们的删除操作都是会被隐式提交的，所以都不能够进行回滚操作，也不会触发触发器。

Truncate和Drop的区别：Truncate只是清空表的数据，保留表的结构，能够重新对表进行其他操作，但Drop不仅清空表的数据，而且删除表的结构，释放表的内存空间，如果还想得到一样的表，只能新建。

相关书籍推荐

这里只是对数据库中的Delete、Truncate和Drop的使用做一个比较和总结。在实际的开发中数据库可以说是每个程序员必备的技能了，只有熟练的掌握了数据库才能更好的开发提高开发效率。如果想要学习数据库的知识，可以关注我的公众号【1024笔记】，免费获取除了数据库之外的学习资源，涵盖了视频、文档和笔面试题。

另外有一些不错的数据库相关的书籍也值得推荐：

历史推荐：

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!