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

Source: Internet
Author: User
Tags commit table name
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.