Simply understand the differences in the principles of common SQL delete statements

This article mainly introduces the differences between the SQL delete statements DROP, TRUNCATE, and DELETE to help you better understand and learn SQL statements. Interested friends can learn more about it.

DROP:

DROP TABLE test;

Delete the test table and free up space, deleting all data in the test table.

TRUNCATE:

TRUNCATE test;

Delete the contents of the test table and free up space, but do not delete the table definition. The table structure remains.

DELETE:

1. Delete specified data

Delete the data whose age is 30 and whose country is US in the test table

DELETE FROM test WHERE age=30 AND country='US';

2. Delete the entire table

Delete all contents of the test table, keep the table definition, and do not release space.

DELETE FROM test or TRUNCATE test;

The truncate table command will quickly delete all records in a table but retain the table structure. This quick deletion is different from the delete from table command which deletes all table records. The data deleted by the delete command will be stored in the system rollback segment. When necessary, the data can be rolled back and restored, while the data deleted by the truncate command cannot be restored.

Similarities

Truncate, delete without a where clause, and drop will delete data in the table.

Differences:

1. Truncate and delete only delete data but not the table structure (definition). The drop statement will delete the constraints, triggers, and indexes that the table structure depends on; the stored procedures/functions that depend on the table will be retained but become invalid.

2. The delete statement is a DML statement. This operation will be placed in the rollback segment and will take effect only after the transaction is committed. If there is a corresponding trigger, it will be triggered when it is executed. Truncate and drop are DDL operations. The operation takes effect immediately. The original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger the trigger.

3. The delete statement does not affect the extent occupied by the table, and the high watermark remains in its original position. Obviously, the drop statement releases all the space occupied by the table. By default, the truncate statement releases space to minextents extents, unless reuse storage is used; truncate will reset the high watermark (back to the beginning).

4. Speed: Generally speaking: drop > truncate > delete.

5. Safety: Use drop and truncate with caution, especially when there is no backup. Otherwise it will be too late to cry.
at last

In usage, if you want to delete some data rows, use delete, and be sure to include the where clause. The rollback segment must be large enough.

If you want to delete a table, of course use drop.

You want to keep the table but delete all the data. If it is not related to the transaction, just use truncate. If it is related to a transaction, or if you want to trigger a trigger, use delete.

If you need to defragment the table, you can use truncate followed by reuse storage, and then re-import/insert the data.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.