DDL, DML, DCL & TCL commands in SQL with syntax & examples

First of all, let’s define what is DDL, DML, DCL, and TCL in DBMS.

• DDL is Data Definition Language
• DML is Data Manipulation Language
• DCL is Data Control Language
• TCL is Transaction Control Language

As you see from its name it allows to define, manipulate and control data and transactions in SQL language.

It’s four types of SQL sub-languages, that’s why it’s no sense to search for a difference between DDL vs DML or DCL vs TCL.

SQL commands list:

Language	Command List
DDL	CREATE DROP ALTER RENAME TRUNCATE
DML	SELECT INSERT UPDATE DELETE
DCL	GRANT REVOKE
TCL	START TRANSACTION COMMIT ROLLBACK

Keep reading and I’ll explain in details what are DDL, DML, DCL, and TCL with examples.

What is DDL in SQL?

DDL allows you to create SQL statements to make operations with database data structures (schemas, tables etc.).

These are SQL DDL commands list and examples:

CREATE

CREATE statement is used to create a new database, table, index or stored procedure.

Create database example:

CREATE DATABASE explainjava;

Create table example:

CREATE TABLE user (
  id INT(16) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL 
);

DROP

DROP statement allows you to remove database, table, index or stored procedure.

Drop database example:

DROP DATABASE explainjava;

Drop table example:

DROP TABLE user;

ALTER

ALTER is used to modify existing database data structures (database, table).

Alter table example:

ALTER TABLE user ADD COLUMN lastname VARCHAR(255) NOT NULL;

RENAME

RENAME command is used to rename SQL table.

Rename table example:

RENAME TABLE user TO student;

TRUNCATE

TRUNCATE operation is used to delete all table records.

Logically it’s the same as DELETE command.

Differences between DELETE and TRUNCATE commands are:

• TRUNCATE is really faster
• TRUNCATE cannot be rolled back
• TRUNCATE command does not invoke ON DELETE triggers

Example:

TRUNCATE student;

What is DML in SQL?

DML is a Data Manipulation Language, it’s used to build SQL queries to manipulate (select, insert, update, delete etc.) data in the database.

This is DML commands list with examples:

SELECT

SELECT query is used to retrieve a data from SQL tables.

Example:

SELECT * FROM student;

INSERT

INSERT command is used to add new rows into the database table.

Example:

INSERT INTO student (name, lastname) VALUES ('Dmytro', 'Shvechikov');

UPDATE

UPDATE statement modifies records into the table.

Example:

UPDATE student SET name = 'Dima' WHERE lastname = 'Shvechikov';

DELETE

DELETE query removes entries from the table.

Example:

DELETE FROM student WHERE name = 'Dima';

What is DCL in SQL?

DCL a Data Control Language.

Its commands are responsible for access restrictions inside of the database.

Let’s take a look at DCL statements definitions.

GRANT

GRANT command gives permissions to SQL user account.

For example, I want to grant all privileges to ‘explainjava’ database for user ‘dmytro@localhost’.

Let’s create a user first:

CREATE USER 'dmytro'@'localhost' IDENTIFIED BY '123';

Then I can grant all privileges using GRANT statement:

GRANT ALL PRIVILEGES ON explainjava.* TO 'dmytro'@'localhost';

and we have to save changes using FLUSH command:

FLUSH PRIVILEGES;

REVOKE

REVOKE statement is used to remove privileges from user accounts.

Example:

REVOKE ALL PRIVILEGES ON explainjava.* FROM 'dmytro'@'localhost';

and save changes:

FLUSH PRIVILEGES;

What is TCL in SQL?

TCL is a Transaction Control Language.

Its commands are used to manage transactions in SQL databases.

This is TCL commands list:

START TRANSACTION (BEGIN, BEGIN WORK)

START TRANSACTION is used to start a new SQL transaction.

BEGIN and BEGIN WORK are aliases for START TRANSACTION.

Example:

START TRANSACTION;

after that, you’re doing manipulations with a data (insert, update, delete) and at the end, you need to commit a transaction.

COMMIT

As a mentioned above COMMIT command finishes transaction and stores all changes made inside of a transaction.

Example:

START TRANSACTION;
INSERT INTO student (name, lastname) VALUES ('Dmytro', 'Shvechikov');
COMMIT;

ROLLBACK

ROLLBACK statement reverts all changes made in the scope of transaction.

Example:

START TRANSACTION;
INSERT INTO student (name, lastname) VALUES ('Dmytro', 'Shvechikov');
ROLLBACK;

If you still have questions about SQL command types feel free to ask me in comments.