Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of mysql using commands to delete data, which has a certain reference value, and friends who need it can refer to it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

In mysql, you can use the "DELETE FROM [WHERE clause] [ORDER BY clause] [LIMIT clause] command to delete data from a single table, or you can use" TRUNCATE [TABLE] table name to delete all data in the data table and empty the table.

Use the DELETE statement to delete data from a single table

In MySQL, you can use the DELETE statement to delete one or more rows of data from a table.

The syntax format is:

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

Delete all the data in the tb_courses_new table, enter the SQL statement and the execution result is shown below.

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) use the TRUNCATE statement to delete data from the data table

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

Create the new table tb_student_course, insert the data and query it. The SQL statement and run result are as follows:

Mysql > CREATE TABLE `tb_student_ room` (- > `id` int (4) NOT NULL AUTO_INCREMENT,-> `name` varchar (25) NOT NULL,-> PRIMARY KEY (`id`)->); Query OK, 0 rows affected (0.04 sec) mysql > INSERT INTO tb_student_course (name) VALUES ('Java'), (' MySQL'), ('Python'); Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > SELECT * FROM tb_student_course +-+-- +-+ | id | name | +-+-+ | 1 | Java | | 2 | MySQL | | 3 | Python | +-+-+ 3 rows in set (0.00 sec)

Use the TRUNCATE statement to clear the records in the tb_student_course table, and the SQL statement and run results are as follows:

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

Thank you for reading this article carefully. I hope it will be helpful for everyone to share the content of mysql's command to delete data. At the same time, I also hope that you will support us, pay attention to the industry information channel, and find out if you encounter problems. Detailed solutions are waiting for you to learn!

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.