What are the SQL delete statements?

sql delete statement: 1. The delete statement is used to delete rows in the table; 2. drop [delete table] deletes content and definitions and releases space; 3. truncate [clears data in the table], deletes content , frees up space but does not delete the definition.

The operating environment of this article: Windows 7 system, SQL Server 2016 version, Dell G3 computer.

sql delete statement:

1. The delete statement is used to delete rows in the table. The delete statement executes the deletion process by deleting one row from the table at a time, and at the same time recording the deletion of the row in the log as a transaction.

Syntax: DELETE FROM table name WHERE column name = value ,

For example: delete the student information named Zhang Sanfeng in the student table-delete from studentwhere name='Zhang Sanfeng';

2. drop (delete table): delete content and definitions , free up 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 (constrain) and trigger (trigger) index (index) that the table structure depends on; the stored procedures/functions that depend on the table will be retained, but their status will become :invalid.

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

Extended information:

In database operations, deleting tables and deleting table data are often used. In practical applications, The difference between the three is clear.

When you no longer need the table, use drop;

When you still want to keep the table but delete all records, use truncate;

When you When you want to delete some records (always with a WHERE clause), use delete.

Comparison between truncate and delete:

1. Truncate table is functionally the same as the delete statement without a WHERE clause: both delete all rows in the table.

2. Truncate is faster than delete and uses less system and transaction log resources.

3. The table after truncate operation is much faster than the table after Delete operation.

Related learning recommendations: SQL video tutorial

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