Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Use of Delete, Truncate and Drop commands in MySQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 169
    Comment on it

    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.


    id first_name last_name city
    1 Amit Kumar Raipur
    3 Rajiv Saxena Delhi
    12 Deepak Singh Dehradun
    15 Rahul Rawat Mumbai

    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


 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: