Header menu

_________________________________________________________________________________

Thursday, 6 February 2014

Explain difference between delete, truncate and drop command in Mysql.

 DELETE

DELETE command in mysql is used to remove rows from a table. We can use where clause  to only remove some rows. If we do not  specify any where clause , all rows will be removed.DELETE operations can be rolled back.
 Syntax : DELETE FROM table_name WHERE column_name=value;

TRUNCATE

TRUNCATE command in mysql removes all rows from the table. The operation cannot be rolled back and no triggers will be fired.  TRUNCATE is faster than DELETE.
Syntax:  TRUNCATE TABLE table_name;

DROP

The DROP command in  mysql removes the table from the database. All the table rows, indexes and privileges will  be removed. No triggers will be fired and the operation cannot be rolled back.
Syntax:  Drop TABLE table_name;

Point to note :
 DELETE operations can be rolled back on the other hand  DROP and TRUNCATE operations cannot be rolled back.