Difference Between DDL And DML Command In DBMS - Explained!

In SQL, we have different types of languages for different sets of operations, like, DDL, DML, DCL, TCL, etc., which help effective human interaction with the DBMS. Let's discuss each of them in detail and the differences between them.

Note to Reader - SQL is not case-sensitive, all the commands in this article are written in uppercase, for better understanding.

What is DDL?

DDL stands for Data Definition Language, which is used to define the table or database schema. It enables secure and efficient data access. Example - Adding a constraint to a column.

Types of DDL Commands

1. CREATE - This command is used to create a database or table.

Syntax - CREATE DATABASE database_name;

CREATE TABLE tablename ( column1 datatype, column2 datatype, column3 type...);

Example -

Output -

2. ALTER - To change the table structure, like, add, modify or delete a table column. This command can also be used to add or drop a constraint.

Syntax - ALTER TABLE tablename ADD column_name type;

ALTER TABLE tablename DROP COLUMN column_name;

Example -

Output -

3. DROP - The DROP command is used to delete the entire definition and the data in the table. All the columns and records are erased. The data cannot be retrieved, once the command is executed.

Syntax -

DROP TABLE table_name;

DROP DATABASE database_name;

Example -

4. TRUNCATE - All the rows/records of the table can be deleted at once, but the table definition is preserved. Unlike the DROP command, the entire table is not deleted. TRUNCATE command is faster than DELETE command as all the records are deleted.

Example -

What is DML?

DML stands for Data Manipulation Language. It is responsible for all the modifications within the records in a database. The table structure cannot be manipulated using DML. The database can always be rolled back to its previous state after the execution of a DML command.

Consider the below table and records. Let's use DML commands to modify the table entries.

Base Table:

Types of DML Commands

1. INSERT - INSERT Command adds new records/data objects into the base table.

Example -

Output - New record has been added.

2. UPDATE - This command is used to change or modify current records in a table. SET and WHERE clauses are followed by the UPDATE command.

Syntax -

UPDATE table_name

SET column_name = value

WHERE condition;

Example for UPDATE statement -

Output - The marks of Yash (roll_no 125) is updated from 76 to 87.

3. DELETE - DELETE command is used to delete a row or a set of rows in a table. A WHERE clause is followed by a DELETE command.

DROP and TRUNCATE commands cannot be rolled back, as the changes are permanently saved in the database, but in the case of DELETE command, the previous state of the database table can be obtained.

Syntax -

DELETE FROM table_name

WHERE condition;

Example -

1. To Delete a Single Record -

Output - The record with roll_no 134 is deleted.

2. To Delete Multiple Records -

Output - The records of Yash and Rita are deleted as their marks are below 80.

Note - The SELECT statement is often confused to be a part of DML commands, but it is a part of DQL (Data Query Language. SELECT statement is used for the filtration of records, based on a certain condition.)

DDL vs DML

The key differences between DDL and DML are -

DDL

DDL stands for Data Definition Language
DDL commands can only be used to define the database table structure
It is a declarative method
They are not followed by the WHERE clause
Database Objects cannot be altered
It cannot be classified further
DCL commands such as the ROLLBACK command, cannot be applied here
Changes are applied to the entire database or table
Basic commands in DDL are - CREATE, DROP, TRUNCATE, RENAME, etc.,

DML

DML stands for Data Manipulation Language
DML statements are used to modify or update the base table
It is an imperative method
DML commands are followed by the WHERE clause
The database schema cannot be altered
It is subdivided into Procedural DML and Non-Procedural DML
Changes can be reversed using the rollback command
Changes are only applicable to one or more records
Basic commands in DML are - UPDATE, INSERT, MERGE, DELETE, etc.,

In short, DDL commands are used to define the structure of tables, and DML is used to modify the data in the table. Both commands enable easier user interaction with the database.

You might also be interested in reading: