Delete statements in a database comparison of the same points and different points of drop, delete, truncate

Delete statement drop, delete, truncate of the same point and different points in the database (for example)

The database Delete statements are described separately:
Delete: Used to delete rows in a table (note: You can delete a row, or you can delete all rows without deleting the table (meaning that the structure, properties, indexes 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 a table (note: The structure, properties, and indexes of the table are also deleted.) ）
Syntax: Drop table name
Truncate: Used to delete data within a table (note: Delete only the data within the table, not 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 in the deleted statement in the database:
Same point:
1.truncate and delete without a WHERE clause, and drop can delete data within a table
2.truncate and delete only delete table data preserving table structure
Different points:
1. TRUNCATE TABLE TableName
Delete Table content frees the table space to preserve the table structure (that is, delete only the data within the table, not the table itself.) Equivalent to the DELETE statement does not write the WHERE clause), and does not use transactions that are not transaction-independent

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

2. Delete Table TableName [WHERE clause]
Delete belongs to data manipulation language (DML), cannot commit transactions automatically, commit commits are required
This operation will be put into rollback segement, after the transaction is committed;
If there is a corresponding trigger, the execution time will be triggered.
The DELETE statement does not affect the extent occupied by the table, and the high waterline (hi Watermark) remains in its original position.

3. DROP TABLE TableName
Drop belongs to the data definition language (DDL) to automatically commit transactions;
The drop statement deletes the structure of the table by the dependent constraint (constrain), trigger (trigger), index (index) "Delete table data and delete table structure";
Stored procedures/functions that depend on the table are preserved, but become invalid states.

The drop statement frees all the space occupied by the table.
The speed at which data is deleted, in general: drop> truncate > Delete

Use occasions:
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 some records (always with a WHERE clause), use Delete.
Attention:
For a table with a primary foreign key relationship, you cannot use truncate and you should use a DELETE statement without a WHERE clause, because the truncate is not recorded in the log and the trigger cannot be activated;

Delete statements in a database comparison of the same points and different points of drop, delete, truncate