Difference between the delete, truncate and drop in mysql

DELETE in MySql:

                     1. The mysql command 'DELETE' is used to delete space allocated by mysql server and the structure of table remains same.

                     2. It is removed rows.

                     3. It can be used by either 'WHERE' clause or without it in mysql.

                     4. The data can be roll backed, if it is removed by 'DELETE' mysql command. If the transaction is used which is yet committed before delete the row, then you can roll backed again.

The mysql query for delete paricular row:

                   DELETE FROM table_name WHERE column_name = condition

Consider the following example:
                     

                  table1 is name of this table. We are going to delete particular row in mysql using 'DELETE' mysql command. The mysql query as follows as:

                 DELETE FROM table1 WHERE id = 3

Now you'll get output like this:

                 You can delete whole rows in table. But the structure of table is present in mysql database.
The mysql query for delete table is:

                DELETE FROM table1    

                 Now the mysql table values are deleted from mysql database. Suppose you click table1 in mysql database, then you'll get output in database like this:

TRUNCATE in MySql: 

                  1. Remove rows from mysql table but the structure of table remains same.

                  2. The data cannot be roll backed if it is deleted by 'TRUNCATE' mysql command.

                  3. It can be used by only without 'WHERE' clause in mysql.

The mysql query for 'TRUNCATE' as follows as:

                  TRUNCATE TABLE table_name

DROP in MySql: 

                 1.Remove whole table from mysql database.

                 2. The data cannot be roll backed if it is dropped by 'DROP' mysql command.

The mysql query for 'DROP' as follows as:

                  DROP TABLE table_name 

Consider following example. The tables in databases like this:

Now drop a table 'table1' using 'DROP' mysql command.

              DROP TABLE table1 

Now you'll get output like this:

                Now the table 'table1' removed from mysql table list
Related Post:

distinct multiple columns in mysql
Order by multiple columns in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
how to use order by before group by in mysql
Primary key in mysql