Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql

What are the DDL, DML and DCL commands in SQL?

Abel Lifaefi Mbula

Overview

SQL (Structured Query Language) has become the de facto standard language for database management systems. It was developed by IBM in the 1970s and adopted as a standard language by ANSI and ISO in 1986 and 1987, respectively.

Note: In this shot, we refer to "statement" and "command" as interchangeable words.

Subdivisions of SQL commands

Depending on the function they perform, SQL commands are subdivided into three categories:

  • Data Definition Language (DDL): It is used to define, change, or drop database objects. For example, the CREATE command.
  • Data Manipulation Language (DML): It is used to read and modify data within the database. For example, the SELECT command.
  • Data Control Language (DCL): It is used to control access to the data within the database. For example, the GRANT command.

Note:

  • DDL is called Schema Definition Language by ANSI.
  • DCL is not an official subdivision by ANSI. It groups these commands as being part of the DDL.

Data definition language

As we saw earlier, commands that fall into this category work directly with objects within the database. Following are the most important commands in this category:

  • CREATE: This helps create an object (database, table).
  • ALTER: This helps alter the existing database or its object structures.
  • DROP: This helps delete an object.
CREATE TABLE student (
  id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(30),
  age INTEGER,
  department VARCHAR(30)
);

DESC student;
Example to use a DDL statement to create a table

The code above creates a table student using the CREATE command.

Data manipulation language

The DML commands help us manipulate the data within the database. They provide the ability to add, delete, update, find, and extract data from the database. These are primarily DML statements that are used by applications, which leverage databases. Following are some important commands in this category:

  • SELECT: This helps select records or data from a table.
  • INSERT: This helps insert data into a table.
  • UPDATE: This helps update existing data.
  • DELETE: This helps delete unwanted records from a table.

In the hands-on exercise below, let's insert some data into our student table and read them.

#-- 1. insert data into student table
INSERT INTO student
VALUES
    (1, "Patience Kavira", 36, "Biology"),
    (2, "Sarah Masika", 3, "Maths"),
    (3, "Abel Lifaefi", 31, "Maths")
;

#-- 2. select all data from student
SELECT name, age, department
FROM student;
Example to use a DML statement to insert data into table

We insert three rows in the table and display all of them using the SELECT command.

Data control language

Commands that fall into this category can be used to grant access to the database, to particular tables, or even to particular columns or rows within a table. These commands are generally used for data security (authentication and authorization).

GRANT permits users to access the database and REVOKE withdraws the permission given by GRANT. These commands fall into this category.

      RELATED TAGS

      sql
      RELATED COURSES

      View all Courses

      Keep Exploring