Difference Between DDL and DML

By Mona Kumari|Updated : June 14th, 2022

Difference Between DDL and DML: The DDL (Data definition language) and data manipulation language (DML) form the database language. The essential difference between DDL and DML is that DML (data manipulation language) is used to access, modify or retrieve the data from the database, whereas DDL (data definition language) is used to specify the database schema database structure.

In this article, we have provided the difference between DDL and DML. Although the SQL is a very complex language, to reduce its complexity, it can be sub-categorized into 5 sub-languages that are as follows:

  • DDL (Data Definition Language)
  • DML (Data Maniplication Language)
  • DCL (Data Control Language)
  • DQL (Data Query Language)
  • TCL (Transaction Control Language)

Further, let us discuss the difference between DDL and DML in the sections below.

Table of Content

Difference Between DDL and DML

We have provided the important difference between DDL and DML in DBMS. It is an important topic in the GATE CSE syllabus. In the table given below, the difference between DDL and DML is based on various parameters such as uses, effect, commands, full form, classification, etc.

Key Difference Between DDL and DML

DDL

DML

DDL is the acronym for the data definition language.

DML is the acronym for the data manipulation language.

DDL is used to create database schema and also define constraints as well.

DML is used to delete, update and update data in the database.

DDL commands affect the whole table of the database.

DML commands affect the one or two rows of the table. 

The DDL language is used to change the structure of the database. 

This language is used to manage the data in the database.

DDL does not have further classification.

DML is further classified as procedural DML and non-procedural DML

DDL has basically defined the column(attributes) of the table.

DML adds or updates the row of the table. These rows are called tuples.

DDL statement cannot be rolled back.

DML statement can be rolled back.

Also, check the difference between DBMS and RDBMS.

What is DDL?

Before knowing the difference between DDL and DML, let us discuss DDL. A DDL(Data Definition Language) is a computer language. Basically, it is a component of SQL used to create and modify the structure of database objects in a database. Therefore, it is also known as Data Description Language. The different DDL commands are as follows:

CREATE

This command is used to create the new database table.

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:

CREATE TABLE Doctor(Name VARCHAR3(30), Email VARCHAR3(100), DOB DATE);

DROP

This statement is used to drop the existing table or a database.

Syntax:

DROP TABLE;

Example:

DROP TABLE EMPLOYEE;

ALTER

This command is used to change the database's configuration or alter the structure of the database. This change could involve either changing the characteristics of an existing attribute or adding a new one.

Syntax:

To add a new column to the table, 

use ALTER TABLE table_name ADD column_name COLUMN-definition; 

To modify an existing column in the table, 

use ALTER TABLE table_name CHANGE column name COLUMN-definition.

ALTER TABLE MODIFY(COLUMN DEFINITION....);

TRUNCATE

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

What is DML? 

Before knowing the difference between DDL and DML, let us discuss DML. The database is modified using DML commands. It is in charge of all database changes of some kind. Data Manipulation Language (DML) is a programming language for manipulating data. It's used to get data out of a database and manipulate it. It responds to user requests. The different DML commands are as follows:

INSERT

The INSERT argument is a SQL query. It is used to insert the data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME (column1, column2, col3,.... col N) VALUES (value1, value2, value3, .... valueN);

Or

INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);

Example:

INSERT INTO gradeup (Author, Subject) VALUES ("Peter", "DBMS");

UPDATE

This command is used to update or change the value of a table column.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

Example:

UPDATE students SET User_Name = 'Peter' WHERE Student_Id = '4'

DELETE

It's used to get rid of one or more rows in a table.

Syntax:

DELETE FROM table_name [WHERE condition];

Example:

DELETE FROM Gradeup WHERE Author="Peter";

Further, let us see some other related articles.

Comments

write a comment

FAQs on Difference Between DDL and DML

  • The difference between DDL and DML is that DDL is used to create database schema and define constraints, whereas DML is used to delete, update, and update data in the database.

  • The difference between DDL and DML regarding classification is that DDL does not have further classification. On the other hand, DML is further classified as:

    • Procedural DML
    • Non-procedural DML
  • SQL is a very complex language, to reduce its complexity, it can be sub-categorized into 5 sub-languages that are as follows:

    • DDL (Data Definition Language)
    • DML (Data Maniplication Language)
    • DCL (Data Control Language)
    • DQL (Data Query Language)
    • TCL (Transaction Control Language)
  • This command is used to change the database's configuration or alter the structure of the database. This change could involve either changing the characteristics of an existing attribute or adding a new one.

    Syntax:

    To add a new column to the table, 

    use ALTER TABLE table_name ADD column_name COLUMN-definition; 

    To modify an existing column in the table, 

    use ALTER TABLE table_name CHANGE column name COLUMN-definition.

    ALTER TABLE MODIFY(COLUMN DEFINITION....);

  • TRUNCATE

    The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

    Syntax:

    TRUNCATE TABLE table_name;

    Example:

    TRUNCATE TABLE EMPLOYEE;

    DELETE

    It's used to get rid of one or more rows in a table.

    Syntax:

    DELETE FROM table_name [WHERE condition];

    Example:

    DELETE FROM Gradeup WHERE Author="Peter";

Follow us for latest updates