Although the green Resource network is not recommended to use the command to delete things in the database table, but these delete commands are always useful places.
When it comes to deleting a table's data, the most important thing you can remember is the delete
However, we do database development, read database data. It's less for the other two brothers.
Now to introduce the other two brothers, are deleted table data, but it is also very easy to understand
Boss------Drop
Infestation Occasion: Drop table TB--TB represents the name of the datasheet, hereinafter
Trick: delete content and definition, free space. Simply put the whole table out. It is impossible to add data later, unless you add a table,
For example: A class is a table, the student is the data in the table, the student's job is to define
drop table class, which is to remove the entire class. The students and the job are gone.
For example, the following Testschool database has two tables [Classes] and [Teacher] Tables
After you execute the following code
Classes table is clear, all right! Removed very violently, as the eldest was well deserved
Dick-----truncate.
Infested occasions: TRUNCATE TABLE TB
Trick: delete content, free space without deleting the definition. Unlike the drop, he just clears the table data, and he's more gentle.
It is also a class, he only removes all the students. The class is still in, the job is still in, if the new student can enter, also can assign the position
Delete Content is easy to understand, do not delete the definition is also easy to understand, is the retention of the table data structure
The image above shows: delete content
After executing the statement, you find that the structure of the datasheet is still in, that is, not deleting the definition
As for freeing up space, look at the following two pictures. You'll understand.
Right: ID column identity column, because the row data was deleted before, so there is an identity column discontinuity (which reflects delete delete is not free space)
After TRUNCATE TABLE Teacher, add three more data
Right: The same ID is the identity column, found in the insertion of data, the identity column is continuous (embodies the truncate delete is free space)
Note: Truncate can not delete row data, to delete the table empty
Third-----Delete
Haunt occasions: Delete Table TB-although it is also the data to delete the entire table, but the process is painful (system row by row, truncate less efficient)
Or
Delete Table TB Where condition
Trick: delete Content does not delete the definition, does not free space. Among the three brothers, one of the easiest to bully
And then there's no detail about the delete, and we all know it.
A small summary of truncate:
TRUNCATE TABLE is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table.
However, truncate is faster than delete and uses less system and transaction log resources.
The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted. So the delete operation can be roll back
1, truncate on all kinds of tables, whether large or small are very fast. If a rollback command delete is revoked, the truncate is not revoked.
2, truncate is a DDL language, like all other DDL languages, he will be implicitly submitted, not to truncate use rollback command.
3, TRUNCATE will reset the high-level line and all indexes. When you fully browse the entire table and index, the table after the truncate operation is much faster than the table after the delete operation.
4, TRUNCATE cannot trigger any delete trigger.
5, when the table is emptied after the table and table index is reset to the initial size, and delete is not.
6, can not empty the parent table