Types of SQL language commands: DML, DDL, DCL and TCL

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

  • 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 table example:

DROP

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

Drop database example:

Drop table example:

ALTER

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

Alter table example:

RENAME

RENAME command is used to rename SQL table.

Rename table example:

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:

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:

INSERT

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

Example:

UPDATE

UPDATE statement modifies records into the table.

Example:

DELETE

DELETE query removes entries from the table.

Example:

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:

Then I can grant all privileges using GRANT statement:

and we have to save changes using FLUSH command:

REVOKE

REVOKE statement is used to remove privileges from user accounts.

Example:

and save changes:

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:

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:

ROLLBACK

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

Example:

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

1 Star2 Stars3 Stars4 Stars5 Stars
Loading...
Tags:, , ,
Scroll Up