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
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.