PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites

10 Jan 2014

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.

delete in mysql


The mysql query for delete paricular row:

                   DELETE FROM table_name WHERE column_name = condition

Consider the following example:
                     
Delete query in mysql

                  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:

delete particular row in mysql using delete command

                 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:

delete table in mysql


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


Truncate mysql command

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.


Drop table in mysql

The mysql query for 'DROP' as follows as:

                  DROP TABLE table_name 

Consider following example. The tables in databases like this:
Drop table in mysql database

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

              DROP TABLE table1 

Now you'll get output like this:
mysql drop

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