SQL Command Types (DQL, DML, DDL, TCL, DCL)
SQL commands are categorized based on their purpose in managing database structures and data. These categories help organize SQL instructions into logical groups:
+-------------------+-------------------------------------------------+
| Command Category | Common Commands |
+-------------------+-------------------------------------------------+
| DDL (Definition) | CREATE, ALTER, DROP, RENAME, TRUNCATE |
| DML (Manipulation)| INSERT, UPDATE, DELETE |
| DQL (Query) | SELECT |
| TCL (Transaction) | COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION |
| DCL (Control) | GRANT, REVOKE |
+-------------------+-------------------------------------------------+
1. DDL – Data Definition Language
Used to define and modify the structure of database objects (tables, views, schemas).
Examples:
CREATE TABLE employees (...);
ALTER TABLE employees ADD COLUMN salary INT;
DROP TABLE employees;
2. DML – Data Manipulation Language
Used to modify the data within existing tables.
Examples:
INSERT INTO employees (...);
UPDATE employees SET age = 30 WHERE id = 1;
DELETE FROM employees WHERE id = 5;
3. DQL – Data Query Language
Used to retrieve data from one or more tables.
Examples:
SELECT * FROM employees;
SELECT name, department FROM employees WHERE age > 30;
4. TCL – Transaction Control Language
Used to manage transactions in the database.
Examples:
BEGIN;
UPDATE employees SET department = 'Sales' WHERE id = 4;
COMMIT;
5. DCL – Data Control Language
Used to control access to data in the database.
Examples:
GRANT SELECT ON employees TO user123;
REVOKE SELECT ON employees FROM user123;
Each category plays a specific role in database operations. Understanding them helps you write more effective, secure, and organized SQL code.