How we can Difference between Delete, Truncate and Drop in mysql

Difference between Delete, Truncate and Drop…


Difference between Delete, Truncate and Drop: –Hi readers here we are going to Difference between Delete, Truncate and Drop in Mysql. Further, we are providing a simple example in readable formats to delete a record from a table using the form in the PHP script to make you more clear about deleting records in a table using MySQL procedural functions.

EXPLAIN Difference between Delete, Truncate and Drop

Delete Statement

Delete is a DML statement which is mainly used to delete data from the table.

Delete statement is used to delete records from the table and not the structure.

Delete statement is also used with Where clause to delete specific records only.

Delete from table_name
where field1=value1;

Truncate Statement

Truncate is a DDL command which cannot be rolled back like Delete statement.

Truncate statement is used to delete records from the table Permanently and not the structure. Truncate statement cannot be used with Where clause to remove specific records.

In the case of Truncate, triggers don’t get fired but in DML commands(e.g.Delete) triggers get fired.

Truncate table table_name;

Drop Statement

A drop is a DDL command which cannot be rolled back like Delete statement.

Drop statement is generally used to delete the entire row as well as the structure, i.e, all the table’s row, indexes, privileges also removed along with the table itself.

Also no DML triggers will be fired.

Drop table table_name;


Simple Example to Delete Records using the form:

<!Doctype html>
<html>
<head><title>Difference between Delete, Truncate and Drop </title>
<head>
<body>
<form action="" method="post">
enter DatabaseName:
<input type="text" name="dbname"><br><br>
enter Table name:
<input type="text" name="table"><br><br>
Enter id:
<input type="text" name="id"><br><br>
<input type="submit" value="Delete records in selected Table">
</form>
<!--end of form-->

<!--php code begins-->
<?php
if($_POST)
{

//storing values of input type into variable
 $dbname=$_POST['dbname'];
 $table=$_POST['table'];
 $id=$_POST['id'];
 
 //create a connection
 $host="localhost";
 $username="root";
 $password="";
 $conn=mysqli_connect($host,$username,$password,$dbname);
 if(!$conn)
 {
 die("Error in establishing connection: ". mysqli_connect_error());
 }
 echo "connection established successfully";
 
 //Delete a record from given table
 $sql="Delete from $table where id=$id";
 $delete=mysqli_query($conn,$sql);
 if(!$delete)
 {
 die("Errors while deleting records : ". mysqli_error());
 }
 echo "<br>Record successfully deleted in $table of id=$id";
 
 //close the connection
 mysqli_close($conn);
}


?>
<!--php code ends-->
</body>
</html>



Output:

Enter table, database and table name whose field has to be deleted:

Difference between Delete, Truncate and Drop        Difference between Delete, Truncate and Drop


Result after Record deleted—

Difference between Delete, Truncate and Drop
Difference between Delete, Truncate and Drop


I hope you would like this tutorial of how we can Difference between Delete, Truncate and Drop command of Mysql and also the simple example of delete records using PHP script!

Leave a Comment