Dialect note: Transaction and DDL behaviour differs by database. MySQL implicitly commits many DDL statements, while systems with transactional DDL may allow some schema changes to be rolled back.
DCL (Data Control Language) + TCL (Transaction Control Language)
1. DCL — Data Control Language
Purpose:
Used to manage permissions, security, and access control on databases, tables, and other objects.
DCL includes:
- GRANT
- REVOKE
- CREATE USER
- DROP USER
1.1 GRANT
Gives specific permissions to a user.
Theory:
Allows a user to perform operations such as SELECT, INSERT, DELETE, UPDATE, etc.
Syntax:
GRANT privilege_name ON database.table TO 'username'@'host';Example:
GRANT SELECT, INSERT ON retail_db.customers TO 'john'@'localhost';1.2 REVOKE
Removes previously granted permissions.
Theory:
Used to prevent a user from performing certain actions.
Syntax:
REVOKE privilege_name ON database.table FROM 'username'@'host';Example:
REVOKE INSERT ON retail_db.customers FROM 'john'@'localhost';1.3 CREATE USER
Creates a new database user.
Syntax:
CREATE USER 'username'@'host' IDENTIFIED BY 'password';Example:
CREATE USER 'john'@'localhost' IDENTIFIED BY 'john123';1.4 DROP USER
Deletes a database user completely.
Syntax:
DROP USER 'username'@'host';Example:
DROP USER 'john'@'localhost';1.5 information_schema
Theory:
A system database that stores metadata about:
- Databases
- Tables
- Columns
- Views
- Privileges
- Constraints
Example:
SELECT * FROM information_schema.tables;Useful for understanding the structure of your database system.
2. TCL — Transaction Control Language
Purpose:
Controls DML operations (INSERT, UPDATE, DELETE) by grouping them into logical transactions.
TCL includes:
- COMMIT
- ROLLBACK
2.1 Transactions
Theory:
A transaction is a sequence of one or more DML operations treated as a single logical unit.
All changes must succeed, otherwise they can be rolled back.
Example scenario:
-
Deduct money from customer account
-
Add money to merchant account
Both must succeed or both must fail.
2.2 COMMIT
Permanently saves all changes made during the transaction.
Syntax:
COMMIT;Example:
UPDATE customers SET customer_city='Austin' WHERE customer_id=1;
COMMIT;After commit → changes cannot be undone.
2.3 ROLLBACK
Undo all changes since the last COMMIT.
Syntax:
ROLLBACK;Example:
DELETE FROM customers WHERE customer_id = 1;
ROLLBACK;The row comes back because changes are not committed yet.
2.4 Auto-Commit
Theory:
- Most SQL environments automatically commit every DML statement.
- DDL statements (CREATE, DROP, ALTER) are always auto-committed and cannot be rolled back.
To check or disable auto-commit (MySQL example):
SET autocommit = 0;Now you must manually use COMMIT or ROLLBACK.