---

DELETE

1. DELETE is a Data Manipulation Language (DML) Command.
2. We can use where condition for remove specific rows(records).
3. DELETE is slower than TRUNCATE. it's maintain logs.
4. Rollback is possible in Delete command.
5. No triggers will be fired.
6. Every row is locked for deletion in table using a row lock.

Syntax of a SQL DELETE Statement

//without where clause, removed all records
DELETE FROM table_name;

//with where clause, removed specific records
DELETE FROM table_name WHERE Condition;

TRUNCATE

1. TRUNCATE is a Data Definition Language ( DDL) Command.
2. TRUNCATE is used to remove all rows from the Table.
3. Truncate is faster compared to delete, it does not maintain transaction log.
4. Rollback is not possible (autocommit).
5. No triggers will be fired.
6. Truncate Table locks the table(entire table) and page.

Syntax of a SQL TRUNCATE Statement

//Removed all records
TRUNCATE TABLE table_name;

DROP

1. DROP is a Data Definition Language( DDL) Command.
2. DROP command removes all records,indexes,structure and privileges permanently.
3. No DML triggers will be fired.
4. Rollback is not possible.

Syntax of a SQL TRUNCATE Statement

DROP TABLE table_name;

What is difference between delete truncate and drop commands in mysql ?

	DELETE	TRUNCATE	DROP
Command Type	DML	DDL	DDL
Rollback Transactions	Can be Rolledback	No	No
Permanent Delete	Does not remove record permanently	Remove the record permanently	Remove all records,indexes,structure and privileges permanently
Trigger	Trigger is fired	No	No
Performance	Slower than Truncate	Faster than Delete	Quick but some complications
Can we use where clause	Yes	No	No
Syntax	DELETE FROM table_name; DELETE FROM table_name WHERE Condition;	TRUNCATE TABLE table_name;	DROP TABLE table_name;

---

Related

find the nth highest salary in mysql without limit
How to find greater than average salary records in MySQL
how to find duplicate records in MySQL query
How to use second index forcefully in MySQL
How to get total number of rows in MySQL table with and without count