| Drop、Truncate和Delete究竟怎么删除 | 您所在的位置：网站首页 › 删除除了delete还有什么 › Drop、Truncate和Delete究竟怎么删除 |
Drop、Truncate和Delete究竟怎么删除
|
作者：江夏 | 知乎：www.zhihu.com/people/1024… | GitHub：github.com/JiangXia-10… | CSDN：blog.csdn.net/qq_4115394… | 掘金：juejin.cn/user/651387… | 公众号：1024笔记 本文大概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=张飞 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 studentstudent表中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。 Drop也属于DDL，Drop的翻译过来就是扔掉，所以当使用Drop的时候就是将整个表都扔掉，不要了，也只有不用的表，才会使用Drop进行删除。不仅仅表内的数据会被清空，而且表的定义，表的结构以及表的约束，触发器，索引也不复存在，这个世界上再也没有这个表的任何信息了，该表所占用的空间也会被全部释放。 好比你换了一位房东租房子，结果又欠费了，长期不交，房东又要赶你走，这位房东直接连你带房子都不要了，如果又有一位房客找他租这样的房子，他需要重新新建一个和这个一模一样的房子。 --删除student表 drop table student执行完Drop语句，在数据库中表的目录找不到student表，如果再执行insert语句往表中插入数据则会报错。 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不仅清空表的数据，而且删除表的结构，释放表的内存空间，如果还想得到一样的表，只能新建。 历史推荐： Spring注解(三)：@scope设置组件作用域 Spring常用注解大全，值得你的收藏！！！ Spring注解(七)：使用@Value对Bean进行属性赋值 SpringBoot开发Restful风格的接口实现CRUD功能 Spring注解(六)：Bean的生命周期中自定义初始化和销毁方法的四种方式 |
【本文地址】