What are the sql commands to delete tables?

SQL commands to delete tables: 1. drop, delete content and definitions, and release space; 2. truncate, delete content, release space but not delete definitions; 3. delete statement is used to delete rows in the table; 4. Truncate table deletes all rows in the table, but the table structure and its columns, constraints, indexes, etc. remain unchanged.

sql command to delete table:

1. drop (delete table): delete content and definition, release space. To put it simply, delete the entire table. It is impossible to add data in the future unless a new table is added.

The drop statement will delete the constraints and trigger indexes that the table structure depends on; the stored procedures/functions that depend on the table will be retained, but their status will become :invalid.

2. Truncate (clear the data in the table): delete content and release space but do not delete the definition (retain the data structure of the table). Different from drop, it just clears the table data.

Note: truncate cannot delete row data. To delete, the table must be cleared.

3. delete (delete data in the table): The delete statement is used to delete rows in the table. The deletion process of the delete statement is to delete one row from the table at a time, and at the same time, the deletion operation of the row is saved in the log as a transaction record

for rollback operation.

truncate and delete without where: only delete the data, but not the structure (definition) of the table

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

If you want to delete the table definition and its data, use the drop table statement.​

5. For tables referenced by foreign key constraints, truncate table cannot be used, but the delete statement without a where clause should be used. Since truncate table is logged, it cannot activate triggers.

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

7. The delete statement is a database operation language (dml). This operation will be placed in the rollback segment and will not take effect until the transaction is submitted; if there is a corresponding trigger, it will be triggered during execution.

Truncate and Drop are database definition languages ​​(DDL). The operation takes effect immediately. The original data does not put it in the rollback segment and cannot roll back. The operation does not trigger the Trigger.

Related learning recommendations: SQL video tutorial

The above is the detailed content of What are the sql commands to delete tables?. For more information, please follow other related articles on the PHP Chinese website!