MySQL DELETE Statement
MySQL DELETE Statement
The DELETE statement in MySQL is used to remove existing records from a table. It is commonly use with the WHERE clause to delete specific rows.
Syntax
table_name: The name of the table from which you want to delete records.WHERE: Specifies which record(s) should be deleted.
Important Warning
If you omit the WHERE clause, all records in the table will be deleted, but the table structure remains intact.
Example:
Deletes all rows in the customers table.
Example Table: students
| id | name | age | city |
|---|---|---|---|
| 1 | John | 20 | New York |
| 2 | Emma | 21 | Chicago |
| 3 | Raj | 22 | Mumbai |
Example 1: Delete a Single Record
Deletes the record where student id = 1.
Example 2: Delete Multiple Records Based on Condition
Deletes all students whose age is greater than 21.
Example 3: Delete Records with NULL Values
Removes all rows where the city value is NULL.
Reset Auto Increment (Optional)
After many deletes, you may want to reset the auto‐increment counter:
DELETE vs TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Deletes specific rows | Yes | No |
| Can use WHERE | Yes | No |
| Deletes all rows | Yes | Yes |
| Faster performance | No | Yes |
| Resets auto-increment | No | Yes |
Example of TRUNCATE:
Summary
Use DELETE to remove data from a table.
Always use a WHERE clause unless you want to delete all records.
DELETE does not remove the table structure.
