sql( DELETE, TRUNCATE AND DROP)

 

DIFFERANCE BETWEEN DELETE,TRUNCATE AND DROP

5 Different Types of SQL Commands

1. DDL or Data Definition Language.
2. DQL or Data Query Language.
3. DML or Data Manipulation Language.
4. DCL or Data Control Language.
5. TCL or Transaction Control Language.

DDL command

A data control language (DCL) is a syntax similar to a computer programming language used to control access to data stored in a database (authorization). In particular, it is a component of Structured Query Language (SQL). Data Control Language is one of the logical group in SQL Commands.

DML command

DML is an abbreviation for Data Manipulation Language. Represents a collection of programming languages explicitly used to make changes to the database, such as: CRUD operations to create, read, update and delete data. Using INSERT, SELECT, UPDATE, and DELETE commands.

1.DELETE

The MySQL DELETE statement is used to delete a single record or multiple records from a table in MySQL.e careful when deleting records in a table! Notice the WHERE clause in the DELETE statement. The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!

create  schema stdmarks;

use stdmarks;

create table stddet( id int primary key   ,name varchar(20),percentage decimal(4,2));

desc stddet;

alter table stddet modify id   int auto_increment;

           

            .insert into  stddet (name ,percentage) values("Aakash",74.89),("Santhosh",88.98),(" siva",                      86.80);

            select * from stddet;

     

 

              delete from stddet where id =3;

               delete  from   stddet ;

___________________________________________________________________________________

2.TRUNCATE

TRUNCATE is faster than DELETE as it is a DDL (Data Definition Language) operation rather than a DML (Data Manipulation Language) operation. TRUNCATE is essentially a drop and recreate operation for the table, making it more efficient for large tables.

Unlike DELETE, TRUNCATE cannot be rolled back. Once the TRUNCATE statement is executed, the data is permanently removed from the table.

SYNTAX:

                 

TRUNCATE TABLE table_name;

(i.e)

use constraints;

create table con_det( name varchar (20) not null, age int not null, nationality varchar(20) );

desc con_det;

insert into   con_det (name ,age, nationality)value("Aakash",21,"indian");

insert into con_det  ( name,AGE,nationality)values( "siva",21,"indian");

select * from con_det; 

truncate table  con_det;

______________________________________________________________________________

3.DROP

The DROP DATABASE statement is used to delete a MySQL database and all of its tables and data.Be extremely cautious when using this command, as it irreversibly deletes the entire database.

 

   

create database droppro;

use droppro;

create table droptab(name varchar(20), age int , gender char(2));

insert into  droptab values("Aakash",21,"M"),("Siva",21,"M"),("Santhosh",20,"M");

select * from droptab;

  

          

         alter table droptab drop age;

         

 drop table droptab;

NOTE:

Table 'droppro.droptab' doesn't exist

.