SQL is not just one thing — it has five categories of commands, each with a different job.
Save
Complete lesson & earn 250 PX
DDL builds structure, DML handles data, DCL controls access, TCL manages transactions. Know which bucket each command lives in.
EXERCISE
2DML commands work with the data inside your tables — reading it, adding it, changing it, and deleting it. This is what you will use 90% of the time.
Save
The four DML commands:
-- SELECT: read data (most used command in all of SQL)
SELECT name, salary
FROM employees
WHERE department = 'Engineering';
-- INSERT: add new rows
INSERT INTO employees (name, department, salary)
VALUES ('Priya Sharma', 'Engineering', 75000);
-- UPDATE: change existing rows
UPDATE employees
SET salary = 80000
WHERE name = 'Priya Sharma';
-- DELETE: remove rows
DELETE FROM employees
WHERE name = 'Priya Sharma';
SELECT is special:
SELECT is technically DML but is sometimes listed separately as DQL (Data Query Language) because it never changes data — it only reads it. In practice, most people just call it all DML.
DML and transactions:
Unlike DDL, DML changes can be wrapped in a transaction and rolled back:
START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
-- If something goes wrong:
ROLLBACK; -- undo both updates
-- If everything is correct:
COMMIT; -- save both updates permanently
💡 Key Insight: SELECT is the most important command to master. Even after years of SQL work, 80% of what you write day-to-day is SELECT queries.
EXERCISE
1DDL commands define and modify the structure of your database — the tables, columns, and constraints. These changes are permanent and often irreversible.
DDL commands build and change structure:
-- CREATE: build a new table
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
hired_at DATE
);
-- ALTER: modify an existing table
ALTER TABLE employees ADD COLUMN email VARCHAR(150); -- add a column
ALTER TABLE employees MODIFY COLUMN name VARCHAR(200); -- change column type
ALTER TABLE employees DROP COLUMN email; -- remove a column
ALTER TABLE employees RENAME TO staff; -- rename the table
-- DROP: delete the table entirely (data and structure, no recovery)
DROP TABLE employees;
-- TRUNCATE: delete all rows but keep the table structure
TRUNCATE TABLE employees;
The danger of DDL:
DDL commands in MySQL are auto-committed — they cannot be rolled back:
-- This is instant and permanent:
customers;
Save
EXERCISE
3DCL controls who can do what in a database. TCL controls when changes become permanent. Both are essential for real-world multi-user systems.
DCL — Data Control Language:
DCL manages user permissions — who can read, write, or administer which tables:
-- GRANT: give a user permission
GRANT SELECT ON company_db.employees TO 'analyst'@'localhost';
-- Now the analyst user can SELECT from employees, but nothing else
GRANT SELECT, INSERT, UPDATE ON company_db.* TO 'app_user'@'%';
-- app_user can read and write all tables, from any host
-- REVOKE: take a permission away
REVOKE INSERT ON company_db.employees FROM 'analyst'@'localhost';
TCL — Transaction Control Language:
TCL controls when data changes are finalised:
-- COMMIT: make all changes in the transaction permanent
COMMIT;
-- ROLLBACK: undo all changes back to the last COMMIT
ROLLBACK;
-- SAVEPOINT: create a named checkpoint within a transaction
SAVEPOINT before_salary_update;
UPDATE employees SET salary = salary * 1.10;
-- Oops, that affected the wrong rows:
before_salary_update;
Save
💡 Key Insight: Always double-check you are connected to the right database before running DROP or TRUNCATE. Run on test first. Many careers have been impacted by running DDL on production by mistake.
Quick summary of all command types:
| Type | Commands | Job |
|---|---|---|
| DDL | CREATE, ALTER, DROP, TRUNCATE | Build/change structure |
| DML | SELECT, INSERT, UPDATE, DELETE | Work with data |
| DCL | GRANT, REVOKE | Control access |
| TCL | COMMIT, ROLLBACK, SAVEPOINT | Manage transactions |
💡 Key Insight: As a developer or analyst, you will mostly use DML. But knowing DCL and TCL matters for production work where data safety and security are non-negotiable.