SQL中delete,truncate和drop的区别

相同点：

          1.drop,truncate和不带where字句的delete都会删除表中的数据。

          2.drop和truncate都是DDL语句，执行后自动隐式提交。

不同点：

           1.truncate和delete只删除数据不删除表的结构（定义），而drop语句将删除整个表（结构和数据）。

            2.delete语句执行删除过程时是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志文件中保存以便进行回滚操作。

              truncate则是一次性从表中删除所有的数据并且不把单独的删除操作记录计入日志文件中保存，删除行是不能回复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

            3.表和索引所占的空间：

                                               当表被truncate之后，这个表和索引所占用的空间会恢复到初始的大小；

                                                当表被delete之后，这个表不会减少表或索引所站用的空间；

                                                 当表被drop之后，这个表所占用的空间全部释放过掉。

              4.应用范围：

                                    truncate只能用于table和cluster（存储table的一种方法）;

                                    delete可以是table或这view；

                                     drop则是可以删除任何结构。

              5.truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),                   触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。       

三者总结：

         delete：

                   1.delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的删除操作记录在日志文件中，以便以后进行回滚操作；

                   2.delete可以根据删除表中满足条件的数据，如果不指定where语句，则删除表中所有数据。

                  3.delete会触发触发器；

            truncate：

                      1.truncate是DDL，会隐式提交，不会触发触发器；

                       2.truncate删除数据时，不会将每一行的删除数据记录在日志文件中，而是将数据页的释放记录在日志文件中，所以删除速度快于delete；

                       3.truncate操作只能针对与cluster和table两种数据对象。

         drop：

                    1.drop是DDL，会隐式提交，不能回滚，不会触发触发器。

                     2.drop语句是删除表结构以及所有数据，并将所占用的空间全部释放；

                     3.drop将删除标的所有依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留，但是变为invalid状态。

          总结：

                 1、在速度上，一般来说，drop> truncate > delete。

                  2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

                   3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

                     如果想删除表，当然用drop； 

                     如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

                      如果和事务有关，或者想触发trigger，还是用delete；