Difference Between DDL and DML

Key Difference – DDLvs DML

Adatabaseis 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.Tablesconsist of rows and columns. A row is a record, and a column is a field. The tables are connected using constraints such asprimary keys and foreign keys.Relational Database Management Systemsare used to store, retrieve and manipulate data. Some of them areMSSQL,Oracle,MySQL. The language which is using to perform operations on relational databases is calledStructured Query Language (SQL).Data Definition Language(DDL) andData Manipulation Language(DML) are subcategories of SQL. Thekey differencebetween DDL and DML is thatDDL用于databas的结构变化e while DML is used to manage the data in the database.

CONTENTS

1.Overview and Key Difference
2.What is DDL
3.What is DML
4.Similarities Between DDL and DML
5.Side by Side Comparison – DDL vs DML in Tabular Form
6.Summary

What is DDL?

DDL stands forData Definition Language. This language is used to change the structure of the database. Create, Alter, Drop,Truncateare some DDL commands.

Examples of DDL Commands

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

Below statement willcreate a databasenamed “employee”.

create database employee;

Below statement willdelete the existing databaseemployee.

drop database employee;

Below DDL statement is used tocreate a table.

create table tbl_employee

(

id int not null,

firstName varchar(30),

department varchar(30),

primary key(id)

);

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

An example toadd a new columnpayment to the table tbl_employee is as follows.

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

Below statement can be used todrop 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 thetruncate 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 forData 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)

语句用于以下insert values into the tabletbl_employee.

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

语句用于以下delete the record.Delete commandcan 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;

Theupdate commandgiven 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.

Summary -DDLvs 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 hereDifference Between DDL and DML

Reference:

1.Point, Tutorials. “SQL Overview. ,Tutorials Point, 15 Aug. 2017.Available here
2.Point, Tutorials. “T-SQL – DML Statements.”,Tutorials Point, 31 Dec. 1969.Available here

Image Courtesy:

1.’SQL’By Bagok – Own work,(CC BY-SA 3.0)viaCommons Wikimedia