In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
This article mainly introduces what is the difference between the SQL command delete and truncate, the article is very detailed, has a certain reference value, interested friends must read it!
Both the delete and truncate commands can be used to delete data (records) in SQL, so what's the difference between them?
What is the delete command?
The delete command helps delete records from tables in the database. [video tutorial recommendation: MySQL tutorial]
Example: the following is a student table with three fields: student_id, name, marks
You can use SQL's delete command to delete all records in the student table:
Delete from student
You can also delete specific records in the student table:
Delete from student where student_id = 2
The above SQL command deletes the record with student_id 2. After the statement is executed, the table will contain only two records: records with student_id 1 and 3.
What is the truncate command?
The truncate command helps delete all records from the table. It also removes the table structure from the database. Therefore, when the truncate command is executed on a particular table, it also initializes the primary key.
The following SQL command deletes all records in the student table. In addition, it removes the table structure from the database.
Truncate table student
What is the difference between the delete and truncate commands?
1. Command type
Delete is a data manipulation language (DML) command; truncate is a data definition language (DDL) command.
2. Function
The delete command deletes single, multiple, or all records from the table according to the specified SQL statement, while the truncate command deletes all records and table structures from the database.
3. Where clause
The delete command supports the WHERE clause, and you can use the where clause with DELETE to filter and delete specific records, while the truncate command does not support the WHERE clause.
4. Lock
The delete command uses row-level locking, and each row in the table is locked for deletion; the truncate command uses table-level locking, locking the entire table to delete all records.
5. Indexed view
The delete command can be used with indexed views, while the truncate command cannot be used with indexed views.
6. Execution speed
Because the delete command maintains logs, it is slow. However, because the truncate command maintains the fewest log records in the transaction log, it executes faster.
7. Table structure
The delete command does not affect the table structure, while the truncate command removes the table structure from the database.
8. Transaction space
The delete command uses more transaction space than the truncate command.
These are all the differences between the SQL command delete and truncate. 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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.