SQL – Drop | Delete | Truncate Command – Explanations, Syntax & Examples

When there is creation, there exists destruction. Deleting a database, tables or records is very simple in SQL and is done using DROP/DELETE command. Note that drop and delete are synonyms. 

Drop in SQL

Drop refers to dropping something i.e either database or table. Drop database means deleting a database from SQL Server whereas Drop table refers to deleting a specific table from the database.

Drop Database

Let us see how to drop a database in SQL. For this, DROP DATABASE statement is used. The syntax is as follows:

DROP DATABASE [database_name];

Example:

First, we will check the existing databases using the SHOW DATABASES statement as given below:

SHOW DATABASES;

Output:-
codingeek
test
data
demo

Now we will drop a database say “test” from the sql server. This can be done as follows:

DROP DATABASE test;

This command will drop the “test” database from the server. This can be further checked by executing SHOW DATABASE again.

Output:-
codingeek
data
demo

Drop Table

A database may consist of any number of tables. Dropping a particular table from a database is achieved using DROP TABLE statement. Syntax is as follows:

DROP TABLE [table_name];

Example:

DROP TABLE mytable;

The above command will delete the table “mytable” from the current database say “codingeek“. Since there can be many databases, we can switch from one database to another using USE command as:

 
USE mydb; 

We have switched from codingeek database to “mydb” database. Now the tables in the mydb database is deleted using the above syntax.

SQL Delete

Records in SQL are removed using DELETE statement. one or any number of rows can be deleted in a table.

1) Delete all rows

We can delete all the records in a table using the following syntax:

DELETE * FROM [table_name];

Example:

Let us consider a table “codingeek” which contains 3 rows as follows:

DELETE * FROM codingeek;

Now, the codingeek table will not have any record but the table structure is not deleted.
Note: This will only delete all the existing rows in the table. Don’t confuse with deleting a table because drop table will remove the entire table(including records and table structure) but delete rows will remove only the records(not the structure)

2) Deleting specific rows

It is possible to delete only the specified row from a table based on a specific condition.

Example:

For this purpose, we will consider the following table “fruits” :

Below is the syntax along with the example on deleting only selected rows from the table-

--SYNTAX

DELETE FROM [table_name]
       WHERE condition;

--EXAMPLE

DELETE FROM fruits 
      WHERE vitamins = 'C';

Output:

The above table has deleted 3 rows based on the condition which is VITAMINS = C

TRUNCATE

Truncate in SQL removes all the rows in a table. DELETE without WHERE and TRUNCATE does the same function.

The advantage of Truncate over Delete is, it is faster and uses lesser resources.

Syntax:

TRUNCATE TABLE [table_name];

Example:

 
TRUNCATE TABLE fruits; 

The above example will be deleting all the rows from the table “fruits”.

Keep yourself educated and never stop learning!!
Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding 🙂

Recommended -