Difference Between DDL and DML

Key Difference – DDL vs DML

A database is used to store data. There are various types of databases. One common database type is Relational Databases. In these databases, data is stored in tables. Tables consist of rows and columns. A row is a record, and a column is a field. The tables are connected using constraints such as primary keys and foreign keys. Relational Database Management Systems are used to store, retrieve and manipulate data. Some of them are MSSQL, Oracle, MySQL. The language which is using to perform operations on relational databases is called  Structured Query Language (SQL). Data Definition Language (DDL) and Data Manipulation Language (DML) are subcategories of SQL. The key difference between DDL and DML is that DDL is used to change the structure of the database while DML is used to manage the data in the database.

What is DDL?

DDL stands for Data Definition Language. This language is used to change the structure of the database. Create, Alter, Drop, Truncate are some DDL commands.

Figure 01: SQL

Examples of DDL Commands

Refer the following  DDL examples written in TSQL (MSSQL server);

Below statement will create a database named “employee”.

create database employee;

Below statement will delete the existing database employee.

drop database employee;

Below DDL statement is used to create a table.

create table tbl_employee

(

id int not null,

firstName varchar(30),

department varchar(30),

primary key(id)

);

Alter command can be used to add columns, modify existing columns and to drop columns.

An example to add a new column payment to the table tbl_employee is as follows.

            alter table tbl_employee add payment numeric (4,2);

Below statement can be used to drop table.

            drop table tbl_employee;

It is also possible to keep the table structure and to delete the details in the table. This can be done using the truncate command. It can delete all the records in the table and also it will clear the data in the memory. So, it is not possible to roll back the operation.

            truncate table tbl_employee;

What is DML?

DML stands for Data Manipulation Language. DML is used to manage the data in the database. Common DML commands are:  insert, delete, update.

Examples of DML Commands

Following are some DML examples written using TSQL (MSSQL server)

Below statement is used to insert values into the table tbl_employee.

Insert into tbl_employee (id, firstName, department) values (1, “Ann”, “Human Resources”);

Below statement is used to delete the record. Delete command can delete data in the table but it does not completely delete from memory. Therefore, it is possible to roll back the operation.

delete from tbl_employee where id=1;

The update command given below is used to modify a particular row.

update tbl_employee set department = ‘Accounting’ where id=1;

What is the Similarity Between DDL and DML?

◉ Both are types of Structured Query Language (SQL).

What is the Difference Between DDL and DML?

DDL vs DML
DDL is a type of SQL which is using to change the structure of the database.	DML is a type of SQL which is using to manage the data in the database.
Commit
DDL statements cannot be rolled back.	DML statements can be rolled back.
Commands
Create, alter, drop, truncate etc. falls into DDL.	Insert, update, delete etc. falls into DML.
Method of Operation
DDL statements effects the whole table.	DML effects one or more rows.

Source: differencebetween.com