How to delete rows in MySQL

1. MySQL row deletion syntax

In MySQL, the syntax for deleting rows is as follows:

DELETE FROM table_name WHERE condition(s);

Among them, table_name is the name of the table to delete rows, condition( s) To filter out the conditions for rows to be deleted, multiple conditions can be connected using AND or OR.

For example, to delete the row with the middle school number 1001 in the table named student, you can use the following statement:

DELETE FROM student WHERE s_id = 1001;

2. Notes on deleting rows in MySQL

1. Use with caution, the deletion operation is irreversible.

Be sure to confirm before performing the deletion operation, because the deletion operation cannot be undone. You need to make sure that the data to be deleted is correct. Generally speaking, it is best to use the SELECT statement to check whether the data to be deleted is as expected, and then execute the DELETE command.

SELECT * FROM student WHERE s_id = 1001;
DELETE FROM student WHERE s_id = 1001;

2. The conditions in the WHERE clause must be unique.

If the conditions in the WHERE clause are not unique, multiple rows of data will be deleted. In order to avoid accidentally deleting data, you need to select appropriate filtering conditions when using the DELETE command based on the characteristics and requirements of the data.

DELETE FROM student WHERE s_name = '张三';

3. The deletion operation will trigger related constraints and triggers.

When the deletion operation involves foreign key constraints and triggers, the system will automatically trigger the relevant constraints and triggers and perform integrity checks. If integrity rules are violated during the deletion operation, the deletion operation will be terminated and relevant error information will be returned.

4. The deletion operation will occupy system resources and affect performance.

When performing a large number of deletion operations, system resources may be occupied, affecting system performance and user experience. In order to improve deletion efficiency and reduce resource usage, the most appropriate deletion strategy should be selected based on the specific situation.

3. Example applications of deleting rows in MySQL

The following introduces some example applications of deleting rows in MySQL.

1. Delete empty tables

If a table no longer has valid data, you can use the DELETE command to delete all rows to clear the table. For example, to delete all rows in the table named student, you can use the following statement:

DELETE FROM student;

2. Delete part of the data that meets the conditions

Use Filtering using the WHERE clause can delete table data that meets specific conditions. Use the following statement to delete all male student data from the table named "student":

DELETE FROM student WHERE s_gender = 'M';

3. Delete large amounts of data

In order to avoid excessive use of system resources, a large amount of data in the table to be deleted can be deleted in batches. For example, to delete all failed student data in the table named student by deleting 1,000 rows at a time, you can use the following statement:

WHILE (SELECT COUNT(*) FROM student WHERE s_score < 60) > 0
DO
    DELETE FROM student WHERE s_score < 60 LIMIT 1000;
END WHILE;

4. Delete duplicate data in the table

If there is duplicate data in the table, you can use the DELETE and ORDER BY statements to delete the duplicate data. You can use the following statement to delete duplicate student data in the table "student"

DELETE FROM student
WHERE s_id NOT IN
(
    SELECT MAX(s_id) FROM student GROUP BY s_name, s_gender, s_age
);

The above is the detailed content of How to delete rows in MySQL. For more information, please follow other related articles on the PHP Chinese website!