Mastering SQL: Clear Guide to DDL, DML, DQL, DCL, and TCL Commands
An in‑depth tutorial walks through the five core SQL language families—DDL, DML, DQL, DCL, and TCL—explaining their purposes, key commands, and practical examples, while highlighting differences and usage tips for database schema definition, data manipulation, querying, permission control, and transaction management.
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 columnsALTER TABLE Students ADD Grade INT; -- add a Grade column
ALTER TABLE Students DROP COLUMN Grade; -- remove the Grade columnDROP DATABASE testDB; -- delete the database
DROP TABLE Students; -- delete the tableTRUNCATE TABLE Students; -- delete all rows but keep the table structureALTER TABLE Students RENAME TO Pupils; -- rename the tableCOMMENT ON COLUMN Students.Name IS 'Student Name'; -- add a comment to the Name columnData 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:
SELECTINSERTUPDATEDELETEMERGECALLEXPLAIN PLANLOCK TABLEExamples:
SELECT * FROM Students; -- retrieve all rows
SELECT Name, Grade FROM Students; -- retrieve specific columnsINSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18); -- add a new rowUPDATE Students SET Age = 19 WHERE ID = 1; -- modify a rowDELETE FROM Students WHERE ID = 1; -- remove a rowMERGE 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 operationCALL Update_Students_Age(18); -- invoke a stored procedureEXPLAIN PLAN FOR SELECT * FROM Students; -- show execution planLOCK TABLE Students IN EXCLUSIVE MODE; -- acquire an exclusive lock on the tableData 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 rightsREVOKE UPDATE ON Students FROM user1; -- remove the UPDATE right from user1Data 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 insertionINSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18);
ROLLBACK; -- undo the insertionSAVEPOINT SP1; -- create a savepoint
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Tom', 18);
ROLLBACK TO SP1; -- revert to the savepoint without discarding later actionsSummary
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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
