Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to delete data in the data table, many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

MySQL provides the DELETE and TRUNCATE keywords to delete data from the table.

1. DELETE statement

The DELETE statement can delete one or more rows of data from a table. Syntax format:

DELETE FROM [WHERE clause] [ORDER BY clause] [LIMIT clause]

The syntax is as follows:

Specifies the name of the table to delete the data

ORDER BY clause: optional. When you delete, the rows in the table are deleted in the order specified in the clause.

WHERE clause: optional. Indicates that the deletion condition is qualified for the delete operation, and if the clause is omitted, all rows in the table are deleted.

LIMIT clause: optional. Used to tell the server the maximum value of the deleted row before the control command is returned to the client.

Note: when the WHERE condition is not used, all data will be deleted.

Example: delete all data in the table

Mysql > DELETE FROM tb_courses_new;Query OK, 3 rows affected (0.12 sec) mysql > SELECT * FROM tb_courses_new;Empty set (0.00 sec)

2. TRUNCATE statement

The TRUNCATE keyword is used to completely empty a table. The syntax format is as follows:

TRUNCATE [TABLE] Table name

The TABLE keyword can be omitted.

Example: delete all data in a table

Mysql > TRUNCATE TABLE tb_student_course;Query OK, 0 rows affected (0.04 sec) mysql > SELECT * FROM tb_student_course;Empty set (0.00 sec)

(recommended tutorial: mysql video tutorial)

The difference between TRUNCATE and DELETE

Logically, the TRUNCATE statement works the same as the DELETE statement, but in some cases there is a difference in usage.

DELETE is a statement of type DML; TRUNCATE is a statement of type DDL. They are all used to empty the data in the table.

DELETE deletes records row by row, while TRUNCATE deletes the original table directly and recreates a new table that is exactly the same, rather than deleting the data in the table row by row, which is faster than DELETE. Therefore, when you need to delete all data rows in the table, use TRUNCATE statements as much as possible, which can shorten the execution time.

After DELETE deletes data, it can be recovered with event rollback. TRUNCATE does not support transaction rollback, and data cannot be recovered after deletion.

After DELETE deletes data, the system does not reset the counter for the self-increment field; after TRUNCATE clears the table record, the system resets the counter for the self-increment field.

DELETE is more widely used because it can delete part of the data by specifying conditions through the WHERE clause, while TRUNCATE does not support the WHERE clause and can only delete the whole.

DELETE returns the number of rows deleted, but TRUNCATE only returns 0, which doesn't make any sense.

After reading the above, do you have any further understanding of how to delete data from the data table? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.