What is the difference between TRUNCATE, DELETE and DROP statements?

Published by jamie on

TRUNCATE

TRUNCATE Command is a Data Definition Language operation. It is used to remove all the records from a table. It deletes all the records from an existing table but not the table itself. The structure or schema of the table is preserved.

Truncate command marks the table for deallocation. This operation removes all the data from a table bypassing a number of constraints enforced on the table. MySQL does not allow the users to truncate the table which is referenced as FOREIGN KEY in another table.

 

DELETE

The DELETE statement in SQL is a Data Manipulation Language(DML) Command. It is used to delete existing records from an existing table. We can delete a single record or multiple records depending on the condition specified in the query.

The DELETE statement scans every row before deleting it. Thus it is slower as compared to TRUNCATE command. If we want to delete all the records of a table, it is preferable to use TRUNCATE in place of DELETE as the former is faster than the latter.

The DELETE command returns the number of records that were deleted by its execution.

 

DROP

DROP statement is a Data Definition Language(DDL) Command which is used to delete existing database objects. It can be used to delete databases, tables, views, triggers, etc.

A DROP statement in SQL removes a component from a relational database management system (RDBMS).

Unlike TRUNCATE which only deletes the data of the tables, the DROP command deletes the data of the table as well as removes the entire schema/structure of the table from the database.

Categories: Backend DevelopmentDatabaseMySQL
Tags:

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

Related Posts

Java

Multithreading in Java and Spring Boot

1. Creating Threads in Java 1.1 Extending the Thread Class public class MyThread extends Thread { public void run() { // Code to be executed in the new thread for (int i = 0; i Read more…

hibernate

Hibernate in Spring Boot – Quick Start

Set up dependency <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> Configuring JPA and Hibernate  in application.properties file spring.datasource.url=jdbc:mysql://localhost:3306/dbname spring.datasource.username=root spring.datasource.password=password spring.jpa.hibernate.ddl-auto=update or application.yml spring: application: name: demoapp datasource: url: jdbc:mysql://localhost:3306/dbname username: root password: password driver-class-name: com.mysql.cj.jdbc.Driver jpa: hibernate: Read more…

Docker

Docker VII: DockerFile

DockerFile Reference: https://docs.docker.com/reference/dockerfile/ Best practices for Dockerfile instructions: https://docs.docker.com/develop/develop-images/instructions/

Table of Contents

← Catalogue