SQL Truncate Drop Rename

Welcome to another tutorial, here you will learn about the various DDL commands as they are used to re-define the tables.

TRUNCATE command

The Truncate command is typically used to remove all the records in a table. However, this command will not destroy the structure of the table. When you use the Truncate command on a table its (auto-increment) primary key is automatically initialized. This can be done with the syntax shown below;

TRUNCATE TABLE table_name

Example:

TRUNCATE TABLE student;

The query above will delete all the records from the table student.

In DML commands, you will learn about the Delete command which is also more or less the same as the Truncate command. Also, you will learn about the difference between the two in that tutorial.

DROP command

The Drop command is used to remove a table from the database completely. The Drop command will also destroy the table structure and the data stored in the database. This can be done with the syntax shown below;

DROP TABLE table_name

Example:

DROP TABLE student;

The query above will completely delete the ‘Student’ table. You can also use Databases to delete the complete database. For instance, to drop a database;

DROP DATABASE MyDatabase;

The query above will drop the database with the name ‘MyDatabase’ from the system.

RENAME query

The Rename command is typically used in setting a new name for any existing table. This can be done with the syntax shown below;

RENAME TABLE old_table_name to new_table_name

Example:

RENAME TABLE student to users;

The query above will rename the table ‘student’ to ‘users’.