Deleting Data ​

To delete records from a database table, the DELETE statement is used.

Basic Syntax ​

The basic syntax for the DELETE statement is:

DELETE FROM <table_name> WHERE ...;

Example ​

To delete the record with id = 1 from the students table:

-- Delete record with id = 1:
DELETE FROM students WHERE id = 1;
-- Check the result:
SELECT * FROM students;

Deleting Multiple Records ​

Similar to UPDATE, you can delete multiple records at once using a condition that matches several rows:

-- Delete records with id = 5, 6, 7:
DELETE FROM students WHERE id >= 5 AND id <= 7;
-- Check the result:
SELECT * FROM students;

No Matching Records ​

If the WHERE clause does not match any records, the DELETE statement will not produce an error, and no records will be deleted:

-- Attempt to delete record with id = 999:
DELETE FROM students WHERE id = 999;
-- Check the result:
SELECT * FROM students;

Caution with WHERE Clause ​

Be very careful, as a DELETE statement without a WHERE clause will remove all records from the table:

DELETE FROM students;

It's advisable to run a SELECT statement first to confirm the intended records will be deleted.

MySQL Behavior ​

In MySQL, the DELETE statement returns the number of affected rows as well as the number of rows that matched the WHERE condition:

mysql> DELETE FROM students WHERE id = 1;
Query OK, 1 row affected

Summary ​

Using DELETE, you can efficiently remove one or more records from a table, but always verify the WHERE conditions to avoid unintentional deletions.