Altering Tables ( Emptying Tables )

To remove records from a table without removing the table itself, use the DELETE or TRUNCATE TABLE statement. Either of the following statements completely empties the named table:  

DELETE FROM t; 

TRUNCATE TABLE t; 

DELETE takes an optional WHERE clause that identifies which records to remove. This is useful when only a given subset of records from a table need to be deleted. The following statement removes only those records from t that have a status column value of 'expired', in order by the id column and output limited to 4 rows:  

DELETE FROM t WHERE status = 'expired' ORDER BY id LIMIT 4; 

TRUNCATE also will reset the metadata held within the format files of the table being truncated ( .frm ) such as the AUTO_INCREMENT starting value.