Mastering SQL: Clear Guide to DDL, DML, DQL, DCL, and TCL Commands

When discussing database accounts and privileges, many developers and operations engineers are unclear about the distinction between DDL and DML. This article systematically organizes the usage and differences of DDL, DML, DQL, DCL, and TCL in the database domain.

Data Definition Language (DDL)

DDL (Data Definition Language) defines or alters the structure of databases and tables. It includes creating schemas, defining data types, relationships, constraints, indexes, views, stored procedures, and triggers.

Common DDL commands and usage

CREATE

: creates databases, tables, indexes, etc. ALTER: modifies existing database objects. DROP: deletes databases or tables. TRUNCATE: removes all rows from a table without dropping the table. RENAME: renames databases or tables. COMMENT: adds comments to database objects.

Examples:

CREATE DATABASE testDB; -- create a database named testDB
CREATE TABLE Students (ID INT, Name TEXT); -- create a table with ID and Name columns

ALTER TABLE Students ADD Grade INT; -- add a Grade column
ALTER TABLE Students DROP COLUMN Grade; -- remove the Grade column

DROP DATABASE testDB; -- delete the database
DROP TABLE Students; -- delete the table

TRUNCATE TABLE Students; -- delete all rows but keep the table structure

ALTER TABLE Students RENAME TO Pupils; -- rename the table

COMMENT ON COLUMN Students.Name IS 'Student Name'; -- add a comment to the Name column

Data Manipulation Language (DML)

DML (Data Manipulation Language) manages and retrieves data within the database. It includes operations that affect data without changing the database structure.

Common DML commands:

SELECT

INSERT

UPDATE

DELETE

MERGE

CALL

EXPLAIN PLAN

LOCK TABLE

Examples:

SELECT * FROM Students; -- retrieve all rows
SELECT Name, Grade FROM Students; -- retrieve specific columns

INSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18); -- add a new row

UPDATE Students SET Age = 19 WHERE ID = 1; -- modify a row

DELETE FROM Students WHERE ID = 1; -- remove a row

MERGE INTO Students AS Target USING (SELECT ID, Name FROM Enrolled_Students) AS Source ON Target.ID = Source.ID WHEN MATCHED THEN UPDATE SET Name = Source.Name WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (Source.ID, Source.Name); -- upsert operation

CALL Update_Students_Age(18); -- invoke a stored procedure

EXPLAIN PLAN FOR SELECT * FROM Students; -- show execution plan

LOCK TABLE Students IN EXCLUSIVE MODE; -- acquire an exclusive lock on the table

Data Control Language (DCL)

DCL (Data Control Language) controls user access permissions and execution rights on database objects.

Key commands: GRANT: grants privileges. REVOKE: revokes privileges.

Examples:

GRANT SELECT, INSERT, UPDATE ON Students TO user1; -- give user1 specific rights

REVOKE UPDATE ON Students FROM user1; -- remove the UPDATE right from user1

Data Query Language (DQL)

DQL (Data Query Language) is primarily represented by the SELECT statement for retrieving data.

Examples:

Retrieve all data: SELECT * FROM Students; Retrieve specific columns: SELECT Name, Age FROM Students; Filter results: SELECT * FROM Students WHERE Age > 18; Sort results: SELECT * FROM Students ORDER BY Age DESC; Advanced SELECT usage includes aggregation functions, GROUP BY, JOIN, etc.

Transaction Control Language (TCL)

TCL (Transaction Control Language) manages transactions to ensure data integrity and consistency, often used together with DML commands.

Key commands: COMMIT: saves all changes made in the transaction. ROLLBACK: undoes all uncommitted changes. SAVEPOINT: creates a named point within a transaction to which you can roll back.

Examples:

INSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18);
COMMIT; -- persist the insertion

INSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18);
ROLLBACK; -- undo the insertion

SAVEPOINT SP1; -- create a savepoint
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18);
ROLLBACK TO SP1; -- revert to the savepoint without discarding later actions

Summary

The article uses concrete SQL statements to explain the four main SQL language categories—DDL, DML, DQL, DCL—and also introduces the transaction control language TCL, noting that while syntax may vary across database systems, the classification and core functionality remain consistent.