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.
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,
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.|
|DDL statements cannot be rolled back.||DML statements can be rolled back.|
|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.|
Summary -DDL vs DML
Relational Database is a common database type. Structured Query Language (SQL) is used to perform operations such as storing, manipulating and retrieving data from relational databases. SQL has main three subcategories. They are DDL, DML and DCL. The difference between DDL and DML is that DDL is used to change the structure of the database and DML is used to manage the data in the database.
Download the PDF Version of DDL vs DML
You can download PDF version of this article and use it for offline purposes as per citation note. Please download PDF version here Difference Between DDL and DML