Difference between delete, truncate and drop statements of MySQL

In MySQL table if i have to modify and delete then we have to use some statements on table. But these commands are different to each other and his working so different.Thus I have to  explain the differences between DELETE ,DROP and TRUNCATE statements of MySQL.

For Example : Use the following table 'student' and apply these commands.

DELETE statement features :

The DELETE statement deletes table rows and returns number of rows deleted.we can delete selected no of records from table using DELETE command.DELETE statement has two modifiers namely LOW_PRIORITY and QUICK.

• When we have to use DELETE Command then the data deleted can be retrieved when you ROLLBACK .
• QUICK modifier is specified then the table handler does not merge index leaves during delete, this may cause to speed up certain kind of deletes.
• LIMIT clause can also be used to set a limit on the number of rows to be deleted.
• A DELETE is issued with no WHERE clause, all rows are deleted.
• ORDER BY clause can be used in DELETE statement. In this case, the rows are deleted in the specified order.
  
  

Note :   With LOW_PRIORITY keyword, DELETE execution is delayed until no other clients are reading from the table and If WHERE clause is specified then rows are deleted satisfying the given conditions and finally returns the number of rows deleted.
With no  WHERE clause  in AUTOCOMMIT mode, DELETE  works as TRUNCATE and does not return affected rows.

For Example :

TRUNCATE statement features :

TRUNCATE TABLE differs from DELETE in the following ways :

• TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one.
• Once u use TRUNCATE command then u cannot retrieve the data again from the table.
• TRUNCATE is not transaction-safe; an error occurs when attempting one in the course of an active transaction or active table lock.
• TRUNCATE removes all the rows from the Table.
• TRUNCATE does not return number of deleted rows.
• TRUNCATE  functionality is an Oracle SQL extension adopted in MySQL.
• TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.

When TRUNCATE is not transaction-safe,an error occurs when attempting one in the course of an active transaction or active table lock.With TRUNCATE TABLE, a table can be re-created (as an empty table) when only the table format file tbl_name.frm is valid, and its data or index files have become corrupted.With TRUNCATE TABLE, the table handler reset the AUTO_INCREMENT value starting from the beginning. This is true even for MyISAM and InnoDB engines.

Note: In case of partitioned tables, TRUNCATE TABLE drops the data and index files and re-creates but preserves the partition definitions (.par) file.TRUNCATE TABLE works similar to DELETE if there are foreign key constraints that reference the table; otherwise it simply drops and re-creates the table. The AUTO_INCREMENT value is reset by TRUNCATE TABLE even ignoring  foreign key constraint, if there is.

For Example :

Note : After truncate this table do not contains any fields but it show in our database by using the following command.

DROP Statement Features :

Drop - deletes the data as well as structure.

The difference between DROP and DELETE table is that, after executing delete statement the contents of table are removed but the structure remains same, but in case of DROP statement both the contents and structure are removed.

Syntax :

mysql>DROP TABLE table_name;