Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the differences between Drop, Delete and Truncate in the database?" friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "delete statements in the database Drop, Delete, Truncate what are the differences?"

Introduction to database deletion statements:

Delete: used to delete rows in a table (Note: you can delete a row or delete all rows without deleting the table (that is, the structure, attributes, and index of the table are complete).

Syntax: delete a row: Delete From table name Where column name = value

Delete all rows: Delete From table name or Delete * From table name

Drop: used to delete the table (Note: the structure, attributes, and indexes of the table will also be deleted. )

Syntax: Drop Table table name

Truncate: used to delete the data in the table (Note: delete only the data in the table, not the table itself, which is equivalent to the Delete statement without writing the Where clause)

Syntax: Truncate Table table name

Comparison of the similarities and differences of delete statements in the database:

Similarities:

1.truncate, delete without where clause, and drop can delete data in a table.

2.truncate and delete delete only the table data retention table structure

Differences:

1. Truncate table tablename

Delete table content release table space preserve table structure (that is, delete only the data in the table, not the table itself. Just as the Delete statement does not write the where clause), it does not use transaction processing, that is, it has nothing to do with transactions

The truncate statement frees space to minextents extent by default

Unless using reuse storage;truncate will reset the high waterline (back to the beginning).

2. Delete table tablename [where clause]

Delete belongs to data manipulation language (DML). Transactions cannot be committed automatically. Commit submission is required.

This operation will be put into rollback segement, and the transaction will not take effect until it is committed.

If there is a corresponding trigger, it will be triggered during execution.

The delete statement does not affect the extent occupied by the table, and the high waterline (high watermark) maintains the original position.

3. Drop table tablename

Drop belongs to the data definition language (DDL), which can automatically commit transactions

The drop statement deletes the dependent constraints (constrain), trigger (trigger), index (index) of the table structure [delete table data and delete table structure]

Stored procedures / functions that depend on the table are retained but become invalid.

The drop statement frees up all the space occupied by the table.

The speed of deleting data, generally speaking: drop > truncate > delete

Where to use:

When you no longer need the watch, use drop

When you still want to keep the table, but delete all records, use truncate

When you want to delete some records (always with a where clause), use delete.

Note:

For tables with primary and foreign key relationships, truncate cannot be used, but delete statements without where clauses should be used. Because truncate is not recorded in the log, triggers cannot be activated.

At this point, I believe that everyone on the "delete statements in the database Drop, Delete, Truncate what are the differences" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.