The DELETE command is used to delete rows that are no longer required from the database tables. It deletes the whole row from the table. The DELETE command can delete more than one row from a table in a single query. This proves to be advantages when removing large numbers of rows from a database table.
Once a row has been deleted, it cannot be recovered. It is therefore strongly recommended to make database backups before deleting any data from the database. This can allow we to restore the database and view the data later on should it be required.
Syntax:
DELETE FROM table_name WHERE condition
- First, specify the table from which we delete data.
- Second, use a condition to specify which rows to delete in the WHERE clause. If the row matches the condition, it will be deleted.
Notice that the WHERE clause is optional. If we omit the WHERE clause, the DELETE statement will delete all rows in the table
Another Way of Deleting the Table
The TRUNCATE TABLE statement is used to remove all records from a table in MySQL. It performs the same function as a DELETE statement without a WHERE clause.
Syntax:
TRUNCATE TABLE table_name
1.When we truncate a table, the AUTO_INCREMENT counters on the table will be reset.
2.MySQL truncates the table by dropping and creating the table. Thus, the DELETE triggers for the table do not fire during the truncation
In MySQL, truncating a table is a fast way to clear out records from a table if we don’t need to worry about rolling back.
Let’s look at an example of how to use the TRUNCATE TABLE statement in MySQL.
For Example:
TRUNCATE TABLE products
will truncate the table called customers and remove all the records from the table. It would be equivalent to the following DELETE statment in MySQL:
DELETE FROM products
Both of these statements would result in all data from the customers table being deleted. The main difference between the two is that we can roll back the DELETE statement if we choose, but we can’t roll back the TRUNCATE TABLE statement.