What are the DDL, DML and DCL commands in SQL?
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
CREATEcommand.
- Data Manipulation Language (DML): It is used to read and modify data within the database. For example, the
SELECTcommand.
- Data Control Language (DCL): It is used to control access to the data within the database. For example, the
GRANTcommand.
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;
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 tableINSERT INTO studentVALUES(1, "Patience Kavira", 36, "Biology"),(2, "Sarah Masika", 3, "Maths"),(3, "Abel Lifaefi", 31, "Maths");#-- 2. select all data from studentSELECT name, age, departmentFROM student;
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.
