MySQL RENAME TRUNCATE and DROP TABLE Statements in PHP

Introduction

This article shows how to use the "RENAME TABLE", "TRUNCATE TABLE" and "DROP TABLE" statements.

Now I will describe each and every one separately.

How to rename table name

The "RENAME TABLE" statement is used to change a table name without modifying its column definitions or the data that's stored in the table. You can simply write the old table name after the "rename table" statement and then write "to" and then can write "new tableName".

Syntax

RENAME TABLE old_tablename to newtablename

Example

Before Rename Table Name



After Rename Table Name

The following query ("RENAME TABLE employee to McnEmp") shown in the image shows how to rename a table in PHP:



How to Truncate a table

You can use the "TRUNCATE TABLE" statement to delete all of the data from a table without deleting column definitions of the table.

Syntax

TRUNCATE TABLE tablename

Example

The following query ("TRUNCATE TABLE McnEmp") shown in the image shows how to truncate a table from MySQL. You can also see that when you execute the "TRUNCATE TABLE McnEmp" query, the description of the table "McnEmp" has not been deleted (in other words the table data was deleted without deleting the column definitions of the table), but if you try to show its data, then the table will show you an empty data set.


How to Drop a table

You can use the "DROP TABLE" statement to delete all data from a table and also delete the definition of the table including constraints of the table. If you try to drop a table from MySQL, then it first checks that no other table dependd on it. If any does then MySQL does not allow the deletion.

Syntax

DROP TABLE tablename

Example

The following query ("DROP TABLE McnEmp") shown in the image shows how to drop a table from MySQL.



You can write all of code above with PHP. I wrote an example to rename, truncate and drop a table in PHP.

Example

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
// Rename command
$rename="RENAME TABLE seller to vendor";
if (!mysql_query($rename,$con))
{
die('Error: ' . mysql_error());
}
print "<h2>Table has been renamed.</h2>";
// Truncate command
$truncate="TRUNCATE TABLE vendor";
if (!mysql_query($truncate,$con))
{
die('Error: ' . mysql_error());
}
print "<h2>Table has been Truncated.</h2>";

//Drop command
$drop="DROP TABLE vendor";
if (!mysql_query($drop,$con))
{
die('Error: ' . mysql_error());
}
print "<h2>Table has been Dropped.</h2>";
mysql_close($con);
?>

Output



Difference between the delete, truncate and drop commands

Delete Command

The Delete command removes the rows from a table based on the condition that we provide with a WHERE clause.

• DELETE can be used with or without a WHERE clause

• DELETE activates triggers.

• DELETE can be rolled back.

• DELETE is a DML Command.

• DELETE does not reset the identity of the table.

Truncate command

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

• TRUNCATE can not be rolled back.

• TRUNCATE is a DDL Command.

• TRUNCATE resets the identity of the table.

Drop command

Removes a table definition and all data, indexes, triggers, constraints, and permission specifications for that table. Any view or Stored Procedure that references the

dropped table must be explicitly dropped by using the DROP VIEW or DROP PROCEDURE statement.

Summary

The RENAME TABLE statement changes the name of an existing table. The TRUNCATE TABLE statement deletes all data from a table without deleting the 
defination for the table. The DROP TABLE statement deletes a table from the current database.