How to delete data from the table in the database?

Methods for deleting data in a database table: 1. Use the "drop table table name" statement to delete; 2. Use the "truncate table table name" statement to delete; 3. Use "delete from table name where column name = value" statement is deleted.

(Recommended tutorial: mysql video tutorial)

There are three ways to delete data in the database table:

1、drop table table name

eg:

drop table  dbo.Sys_Test

2、truncate table table name

eg:

truncate  table dbo.Sys_Test

3、delete from table name where column name = value

eg:

delete from dbo.Sys_Test where test='test'

drop, truncate, delete difference

1. Drop (delete table): delete content and definitions to 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 (constrain), 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.

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. Instead, a 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 only take effect after 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 operations take effect immediately. The original data is not placed in the rollback segment, cannot be rolled back, and the operation does not trigger the trigger.

Related recommendations: php training

The above is the detailed content of How to delete data from the table in the database?. For more information, please follow other related articles on the PHP Chinese website!