Delete:
——-
1. Delete operations can be rollback
2. We can use where clause in delete operation
3. Using delete function we can delete row by row
4. It will not reset the autoincrement field- suppose your last record id of autoincrement field is 4, then after delete operaion next entry will be starting with id 5.
5.Delete will fire trigger(if trigger exists for the particular row deletion)
TRUNCATE:
———
1. Truncate is faster than Delete.
2.Cannot use where conditions in truncate.
3.it will reset the autoincrement field. means after deleting next entry will start with autoincrement field 1.
4. Remove all rows from the table, but structure,columns,constraints,index remains the same.
5.cannot use truncate on a table referenced by foreignkey.
DROP :
——-
if you dont need any table anymore then use the drop command
it will
1. Remove all structures,table,constraints, indexes everything
2. No trigerr fired
3.cannot be rolled back