Try sorting these five statements into groups before reading on: CREATE TABLE, UPDATE, GRANT, COMMIT, TRUNCATE. If you put TRUNCATE next to DELETE, you have made the most common mistake in this topic, and it matters for more than viva marks: it changes whether you can undo it. SQL statements are grouped by what they act on: the structure of the database, the data inside it, who may use it, and transactions.
| Group | Full form | Acts on | MySQL statements | Can be rolled back? |
|---|---|---|---|---|
| DDL | Data Definition Language | Structure: databases, tables, indexes, views | CREATE, ALTER, DROP, TRUNCATE, RENAME | No, each causes an implicit commit |
| DML | Data Manipulation Language | Rows of data | INSERT, UPDATE, DELETE, REPLACE (and SELECT, see below) | Yes, inside a transaction (InnoDB) |
| DCL | Data Control Language | Users and permissions | GRANT, REVOKE (plus CREATE USER, CREATE ROLE) | No, implicit commit |
| TCL | Transaction Control Language | Transactions | START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT | Not applicable |
Some textbooks list SELECT separately as DQL (Data Query Language). The MySQL manual groups it with the data manipulation statements. Either answer is accepted if you explain it.
DDL: defining structure
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(40) NOT NULL,
duration_weeks INT
);
ALTER TABLE courses ADD COLUMN mode VARCHAR(10) DEFAULT 'online';
DESCRIBE courses;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| course_id | int | NO | PRI | NULL | |
| title | varchar(40) | NO | NULL | ||
| duration_weeks | int | YES | NULL | ||
| mode | varchar(10) | YES | online |
DDL changes what the table is. DROP TABLE courses; would remove the table and all its rows; RENAME TABLE courses TO course; renames it. Table design is covered step by step in how to create a table in MySQL.
DML: working with rows
INSERT INTO courses (course_id, title, duration_weeks) VALUES
(1,'SQL Basics',4), (2,'Power BI',6), (3,'Python for Analysts',8);
UPDATE courses SET mode = 'hybrid' WHERE course_id = 2;
DELETE FROM courses WHERE course_id = 3;
SELECT * FROM courses;
| course_id | title | duration_weeks | mode |
|---|---|---|---|
| 1 | SQL Basics | 4 | online |
| 2 | Power BI | 6 | hybrid |
Rows inserted without a mode picked up the default 'online' defined by the DDL earlier. The full syntax and safety habits for these three statements are in INSERT, UPDATE and DELETE in MySQL.
TCL: the undo button for DML, and why DDL ignores it
This experiment shows the practical difference between the groups. First a DML delete inside a transaction:
START TRANSACTION;
DELETE FROM courses; -- DML
ROLLBACK;
SELECT COUNT(*) FROM courses;
| COUNT(*) |
|---|
| 2 |
Both rows are back. Now the same idea with TRUNCATE, which is DDL:
START TRANSACTION;
TRUNCATE TABLE courses; -- DDL: commits implicitly
ROLLBACK;
SELECT COUNT(*) FROM courses;
| COUNT(*) |
|---|
| 0 |
The rows are gone. TRUNCATE drops and re-creates the table internally, resets AUTO_INCREMENT, and commits on its own, so ROLLBACK has nothing to undo. The same happens with CREATE, ALTER and DROP, and even with GRANT. Details on COMMIT, ROLLBACK and SAVEPOINT are in COMMIT and ROLLBACK in MySQL, and the three ways of removing data are compared in DELETE vs TRUNCATE vs DROP.
DCL: who can do what
MySQL 8 added roles, which make permissions easier to manage: give privileges to a role once, then give the role to people.
CREATE ROLE 'analyst_ro';
GRANT SELECT ON college.* TO 'analyst_ro';
CREATE USER 'meena'@'localhost' IDENTIFIED BY 'An0ther#Pass';
GRANT 'analyst_ro' TO 'meena'@'localhost';
SET DEFAULT ROLE 'analyst_ro' TO 'meena'@'localhost';
SHOW GRANTS FOR 'meena'@'localhost' USING 'analyst_ro';
| Grants for meena@localhost |
|---|
| GRANT USAGE ON *.* TO `meena`@`localhost` |
| GRANT SELECT ON `college`.* TO `meena`@`localhost` |
| GRANT `analyst_ro`@`%` TO `meena`@`localhost` |
USAGE means "can log in, nothing more"; the SELECT comes through the role. Taking it away is one statement:
REVOKE 'analyst_ro' FROM 'meena'@'localhost';
Without SET DEFAULT ROLE, a granted role is not active when the user logs in, which confuses many people the first time. Users, roles and privilege levels are explained in MySQL user management and privileges.
Common exam and interview mix-ups
- "TRUNCATE is DML because it removes rows." It is DDL in MySQL: implicit commit, no
WHERE, no row-by-row delete triggers. - "DELETE without WHERE is the same as TRUNCATE." The result looks the same, but
DELETEcan be rolled back, fires triggers and keeps theAUTO_INCREMENTcounter. - "ROLLBACK undoes everything since I connected." It undoes only uncommitted DML in the current transaction. With autocommit on (the default), each statement is already committed.
- Mixing DDL into a data transaction. An
ALTER TABLEhalfway through silently commits the earlier changes.
For a compact list of all these commands with syntax, keep the MySQL commands cheat sheet open while you practise.