Explain the difference between DELETE, DROP, and TRUNCATE.

DELETE removes chosen rows, TRUNCATE TABLE empties the whole table at once, and DROP TABLE removes the table itself along with its data.

• DELETE removes the rows its WHERE clause matches, fires ON DELETE triggers and can be rolled back. It is slower because it deletes rows one by one.
• TRUNCATE drops and re-creates the table, which is much faster than DELETE on large tables. In MySQL it causes an implicit commit, so it cannot be rolled back. It does not fire ON DELETE triggers, and it resets the AUTO_INCREMENT counter.
• DROP removes the table definition, all its data and its triggers. It also causes an implicit commit and is very fast.

For InnoDB, TRUNCATE fails if a foreign key in another table references the table.

-- DELETE specific rows
DELETE FROM users WHERE age < 18;

-- TRUNCATE all data
TRUNCATE TABLE users;

-- DROP entire table
DROP TABLE users;