DDL, DML, DCL, TCL and DQL

DDL, DML, and DCL are three basic concepts in database management. They are important for understanding how databases work and how to manipulate data within them. In this blog, we will explore what DDL, DML, and DCL mean, their differences, and how they are used in database management.

DDL (Data Definition Language)

DDL stands for Data Definition Language. It is a set of SQL commands that are used to define and manipulate the structure of a database. DDL commands are used to create, alter, and drop database objects such as tables, indexes, views, and stored procedures. DDL commands include:

• CREATE: used to create a new database object such as a table, view, or stored procedure.
• ALTER: used to modify the structure of an existing database object such as a table or view.
• DROP: used to delete an existing database object such as a table or view.
• TRUNCATE: used to delete all data in a table without deleting the table structure.

DML (Data Manipulation Language)

DML stands for Data Manipulation Language. It is a set of SQL commands that are used to manipulate the data within a database. DML commands are used to insert, update, delete, and retrieve data from tables.

DML commands include:

• INSERT: used to insert new data into a table.
• UPDATE: used to modify existing data in a table.
• DELETE: used to delete data from a table.
• SELECT: used to retrieve data from one or more tables.

DCL (Data Control Language)

DCL stands for Data Control Language. It is a set of SQL commands that are used to control the access and privileges of users within a database. DCL commands are used to grant or revoke permissions to users, roles, and groups.

DCL commands include:

• GRANT: Is used to give permissions to a user or group to perform a specific operation such as SELECT, INSERT, UPDATE, or DELETE on a database object such as a table or view.
• REVOKE: Is used to remove permissions from a user or group to perform a specific operation on a database object.
• DENY: Is used to deny a user or group permission to perform a specific operation on a database object.

TCL (Transaction Control Language)

TCL commands are used to manage transactions in the database. Some common TCL commands include COMMIT and ROLLBACK. A transaction is a sequence of database operations that are executed as a single unit of work. The purpose of using transactions is to ensure data consistency and integrity. TCL commands are used to initiate and control transactions. Here are some examples of TCL commands:

• COMMIT - Saves all changes made in the transaction to the database.

• ROLLBACK - Reverses all changes made in the transaction and restores the database to its state before the transaction started.

• SAVEPOINT - Marks a point in the transaction at which a partial rollback can be performed.

• SET TRANSACTION - Defines characteristics of a transaction such as isolation level and read/write access mode.

DQL (Data Query Language)

DQL commands are used to query or retrieve data from the database. The most common DQL command is SELECT. The SELECT command is used to query the database and retrieve data from one or more tables. Here are some examples of DQL commands:

• SELECT - Retrieves data from one or more tables.

• FROM - Specifies the table from which to retrieve data.

• WHERE - Specifies the conditions that must be met for a row to be included in the result set.

• ORDER BY - Specifies the order in which the results should be sorted.

• GROUP BY - Groups the results by one or more columns.

DQL commands can also include joins, subqueries, and aggregate functions such as SUM, COUNT, AVG, MAX, and MIN. The result of a DQL command is a set of rows that match the specified criteria.