This article explains the use and difference of DELETE, TRUNCATE and DROP statements of MySQL.

1. DELETE : DELETE command is used to delete all or specific records from MySQL table. The records delete using DELETE can be rollback. This command also remove the table space allocated to table by server but the table structure remains same.This command can not reset identity of table and also does not update auto_increment value for table.

Note : DELETE command returns the number of deleted rows.

Example: Let consider we have an employee table, which stores the records of employees.

1. Delete all records.

<?php 
  $query = "DELETE FROM `employee` ";
?>

The above query delete all records from employee table.

2. Delete specific records : To delete specific records, DELETE can be used with WHERE clause.

<?php 
  $query = "DELETE FROM `employee` WHERE id = 12 ";
?>

The above query delete only one row which has id = 12.

2. TRUNCATE : TRUNCATE command also used to delete all records from a MySQL table. TRUNCATE command first drop the table and create it again, so the table structure remains same and it also resets the identity of table. TRUNCATE command executes faster than DELETE command and auto_increment value also sets to 1. The records deleted by truncate can not be ROLLBACK and it also does not return number of rows deleted from table like DELETE.

Example :

<?php
  $query = "TRUNCATE TABLE `employee`";
?>

The above query removes all the rows of employee table.

3. DROP : DROP command is used to delete table from database. Unlike DELETE and TRUNCATE, the table structure also deleted by DROP command. The table and records removed by DROP can not be ROLLBACK.

Example :

<?php
  $query = "DROP TABLE `employee`";
?>

The above query delete all the rows as well as employee table.

Note : DROP command can also be used to delete database from server.

Syntax : DROP DATABASE Database_Name