Shulou(Shulou.com)05/31 Report--

This article mainly introduces the sql delete table command how to write, the article is very detailed, has a certain reference value, interested friends must read it!

Sql delete table commands: 1, drop, delete content and definition, free space; 2, truncate, delete content, release space but not delete definition; 3, delete statement is used to delete rows in the table; 4, truncate table delete all rows in the table, but the table structure and its columns, constraints, indexes remain unchanged.

Sql command to delete the table:

1. Drop (delete table): delete content and definition to free up space. To put it simply, get rid of the whole watch. It is impossible to add new data in the future unless you add a new table.

The drop statement removes the constraint (constrain) on which the structure of the table is dependent (constrain), the trigger (trigger) index (index); stored procedures / functions that depend on the table will be retained, but their state will change to: invalid.

2. Truncate (emptying the data in the table): delete the content, free up space without deleting the definition (retain the data structure of the table). Unlike drop, it just empties the table data.

Note: truncate cannot delete row data. If you want to delete it, you have to empty the table.

3. Delete (delete data from the table): the delete statement is used to delete rows from the table. The delete statement performs a delete by deleting a row from the table each time, while saving the deletion of that row in the log as a transaction record

To perform a rollback operation.

Truncate and delete without where: delete data only, not table structure (definition)

4. Truncate table deletes all rows in the table, but the table structure and its columns, constraints, indexes, etc., remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to keep the identity count value, use delete instead.

Use the drop table statement if you want to delete the table definition and its data.

5. Instead of using truncate table for tables referenced by foreign key constraints, you should use a delete statement without a where clause. Because truncate table is recorded in the log, it cannot activate the trigger.

6. Execution speed, generally speaking: drop > truncate > delete.

7. The delete statement is the database operation language (dml). This operation will not take effect until the transaction is committed in rollback segement. If there is a corresponding trigger, it will be triggered when it is executed.

Truncate and drop are database definition languages (ddl). The operation takes effect immediately. The original data is not put into rollback segment, cannot be rolled back, and the operation does not trigger trigger.

The above is all the contents of the article "how to write the command to delete the table in sql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.