What are the differences between DDL, DML and DCL in SQL | developerIndian

5/8/2022

#ddl and dml commands #dcl command #dcl in sql #sql_dml_ddl_tcl_dcl

Go Back

Differences Between DDL, DML, and DCL in SQL | DeveloperIndian

Introduction

SQL (Structured Query Language) is used for managing and manipulating relational databases. It consists of various subcategories, including DDL (Data Definition Language), DML (Data Manipulation Language), TCL (Transaction Control Language), and DCL (Data Control Language). Understanding these SQL classifications is crucial for database developers and administrators.

#ddl and dml commands #dcl command #dcl in sql #sql_dml_ddl_tcl_dcl

What is DDL (Data Definition Language)?

DDL commands define the structure of database objects, such as tables, schemas, indexes, and views. These commands primarily deal with the schema of a database.

Common DDL Commands:

  • CREATE: Used to create database objects (tables, views, indexes, etc.).
  • ALTER: Modifies the structure of an existing database object.
  • DROP: Deletes database objects permanently.
  • RENAME: Renames an existing database object.

Example of DDL – Creating a Table:

CREATE TABLE Developer (
  location CHAR(20),
  language CHAR(15),
  mark NUMERIC(12,2)
);

What is DML (Data Manipulation Language)?

DML commands handle data within database tables. These commands allow users to retrieve, insert, update, and delete records in a database.

Common DML Commands:

  • SELECT: Retrieves data from a table.
  • INSERT: Adds new records to a table.
  • UPDATE: Modifies existing data within a table.
  • DELETE: Removes records from a table without deleting its structure.

Example of DML – Selecting Data:

SELECT location
FROM Developer
WHERE Developer.location = 'India';

What is TCL (Transaction Control Language)?

TCL commands manage transactions in a database. Transactions allow multiple SQL commands to be executed together as a single unit.

Common TCL Commands:

  • COMMIT: Saves all changes made in the current transaction permanently.
  • ROLLBACK: Restores the database to its last committed state.
  • SAVEPOINT: Creates a temporary point in a transaction to which you can later roll back.

Example of TCL – Committing Transactions:

COMMIT;
SET autocommit = 0; -- Turns off auto-commit mode

What is DCL (Data Control Language)?

DCL commands control access to data within a database by granting or revoking user permissions.

Common DCL Commands:

  • GRANT: Provides specific privileges to users.
  • REVOKE: Removes assigned privileges from users.

Example of DCL – Rolling Back a Transaction:

ROLLBACK;

Conclusion

Understanding the differences between DDL, DML, TCL, and DCL is essential for efficient database management.

  • DDL focuses on defining the database structure.
  • DML is used for data manipulation within tables.
  • TCL controls database transactions.
  • DCL manages user access and security.

By mastering these SQL categories, developers can perform efficient database operations, ensuring data integrity and security.

Table of content