A comparison of the same and different points of the deletion statement drop, delete, truncate in the database (instance description) _ Database Other

Separate description of database deletion statements:
Delete: For deleting rows in a table (note: You can delete a row, or you can delete all rows without deleting the table (that is, the table's structure, properties, index Integrity)
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: For deleting a table (note: The structure, properties, and indexes of the table will also be deleted.) ）
Syntax: Drop table name
Truncate: Used to delete data in a table (note: Delete only the data in the table, do not delete the table itself, equivalent to the DELETE statement does not write the WHERE clause)
Syntax: Truncate table name

comparison of the same and different points of deletion statements in the database:

Same point:

1.truncate and delete with no WHERE clause, and drop can remove data from the table
2.truncate and delete Delete only table Data retention table structure

different points:

1. TRUNCATE TABLE TableName

Delete Table content release table space reserved table structure (that is, delete only the data in the table, do not delete the table itself.) Equivalent to a DELETE statement that does not write a WHERE clause), and does not use transaction-Independent

The TRUNCATE statement by default frees up space to minextents extent,
Unless the reuse storage;truncate is used, the high watermark is reset (back to the beginning).

2. Delete Table TableName [WHERE clause]

Delete belongs to data manipulation language (DML), cannot commit transaction automatically, commits commit
This operation will be placed in the rollback segement, after the transaction is committed;
If there is a corresponding trigger, the execution will be triggered.
The DELETE statement does not affect the extent that the table occupies, and the high watermark (watermark) keeps the original position fixed

3. DROP TABLE TableName

Drop belongs to the data definition language (DDL) and can automatically commit transactions;
The DROP statement deletes the table structure by deleting the structure of the table (constrain), triggers (trigger), indexing (index) "Deleting table data";
Stored procedures/functions that depend on the table are preserved, but become invalid states.
The drop statement frees up all of the space occupied by the table.
The speed at which data is deleted, generally: drop> truncate > Delete

Use occasion:

When you no longer need the table, use drop;
When you still want to keep the table, but to delete all records, use truncate;
When you want to delete a partial record (always with a WHERE clause), use the delete.

Note:
For tables that have a primary foreign key relationship, you cannot use truncate instead of a DELETE statement with a WHERE clause, and you cannot activate a trigger because TRUNCATE is not logged in the log

Author Han Co-min