Register Login

Difference between DDL and DML

20 Sep 2019 2:13 pm || 0

A collection of related data is called a database. Database Management System is the most popular way to handle these databases. A relational database management system is used to store data in the form of tables. SQL (Structured Query Language) is the language through which the users can interact with the database systems.

In this post, we will focus on the difference between DDL and DML.

DML vs. DDL

SQL commands are used to perform operations on the database such as create, insert, delete, or drop data tables. These commands are categorized into different categories. They are DDL, DQL, DCL, and DML.

Below is a comparison chart to help to understand the difference between DDL and DML.  

Basis of comparison DDL DML
Basic function This language is used for describing the structure of a database. This language is used for modifying and fetching data from a database.
Categorization The DDL commands cannot be classified further. The DML commands can be divided into Procedural DML and Declarative DML. 
Commonly used commands The commands under DDL are Create, Drop, Truncate, Alter, and Rename. The commands under DDL are Select, Update, Delete, and Insert.
Rollback The statements cannot be rolled back. Rollback is possible for the DML statements.
Where clause There is no such use of the Where clause. The Where clause is used for specifying conditions while manipulating data in a database.
Effect These commands affect the complete database. These commands will affect only one or more records in the table.

Types of SQL Commands

Developed by IBM way back in 1970, SQL is the standard programming language for handling databases. It has different types of commands to handle database queries. The major types are:

DDL

DDL stands for Data Definition Language. The various commands in the language are used for defining the structure or schema of a database. The SQL commands that fall under DDL are:

  • Create
  • Alter
  • Drop
  • Comment
  • Truncate
  • Rename

DML

DML stands for Data Manipulation Language. The commands in this language are used for modifying or altering the contents of the database. The SQL commands within DML are as follows:

  • Insert
  • Delete
  • Select
  • Update

TCL (Transaction Control Language)

The commands that come under this language can only use the DML commands for its operations. You can manage the transactions of the database using these commands. Additionally, you can group Query statements into logical statements. The commands that fall under this category are:

  • Commit
  • Rollback
  • Savepoint

DCL (Data Control Language)

This language provides commands that you can use to control the data access within a database. These commands are used to offer or take back privileges from other users of the database:

  • Grant
  • Revoke

DQL (Data Query Language)

Data queries can be performed within the schema objects using DQL. These commands used for pulling data from databases. The fetching of data and its corresponding results will depend upon the conditions provided by the user. The only command that comes under DQL is:

  • Select

The following sections will help you get a clear idea about the difference between DDL and DML.

What is DDL?

DDL is used to develop the structure of the database objects and the database itself. The changes made by the commands are saved permanently within the database. This is because the commands are all auto committed. The database objects DDL handles include views, tables, schemas, and indexes.

They are considered a subset of SQL. The commands under DDL are as follows:

Create

This command is used to create a new table within the database. It also creates objects within the database. The syntax for this command is:

CREATE TABLE <tablename>

( column_name 1 data type,
column_name 2 data type,
.
.
.
column_name 10 data type
);

Drop

You can use this command to remove or drop an entire table or database. The command destroys the structure of the table and the data within it. The syntax is as follows:

DROP TABLE < name_of_table>;

OR

DROP DATABASE <name_of_database>;

Truncate

This command is used for deleting all the records from a table. The spaces assigned for the records are also removed. But, this command does not destroy the structure of the database. If this command is used, the primary key of the table is initialized. The syntax is:

TRUNCATE table_name;

Rename

The Rename command is used for renaming the name of an existing table. The syntax is as follows:

RENAME old_name to new_name;

Comment

Comments can be written in SQL in three ways:

Single line comment – These comments start and end within the same line. The syntax is:

--this is a single line comment

Multi-line comment – These comments can occupy multiple lines. The syntax is:

/* This is a multi-line comment
about a Select statement*/

Alter

This command is used for altering the structure of the table within a database. This can be used to add, delete, or modify the columns of a table. The syntax is:

1.ALTER TABLE <table_name>

ADD <column_name datatype>;

2.ALTER TABLE <table_name>

DROP COLUMN <column_name datatype>;

3.ALTER TABLE <table_name>

MODIFY <column_name column_type>;

What is DML?

DML is used for manipulating and managing the data within a database. It allows you to select, delete, modify, and insert information. The commands in DML are not auto committed. So, the modifications made within the database are not saved permanently. Therefore, performing rollback operations is possible.

There are two types of DML. They are Procedural DML and Declarative DML. In Procedural DML, the process of retrieving data is defined. But in the case of Declarative DML, which particular data needs to be obtained, is described. It is easier for any user to use Declarative DML as they can only specify what type of data they require.

The different type of DML commands are as follows:

Select

This command is used for fetching some specific data from the database. A condition can also be mentioned for retrieving data from the database. The result may be in the form of one or more tables.

The syntax is:

SELECT * FROM employee

where Salary > 50,000;

Here, * is the universal selector that indicates all rows and columns matching the criteria has to be selected.

The command has some option clauses. They are Where, Order By, Group By, As, and Having.

Insert

You can use this command to add some data to an existing table. The syntax is as follows:

INSERT INTO <table_name> (`column_name A` <datatype>, `column_name B` <datatype> <database_name>)

VALUES (`value 1`, `value 2`);

Update

The Update command is used for updating and modifying the current records in a table. Using this command, you can alter the data for one or more records. The syntax for the command is:

UPDATE Employee

SET salary = 78,000

WHERE emp_name='Bruce';

Here, Employee is the table name, and salary is the name of the column. The condition is specified using the emp_name.

Delete

The Delete command is used for deleting specific records from a table. This removal will be based on a certain condition. The command can delete all records matching the specified condition. The syntax is as follows:

DELETE FROM Students

WHERE Stud_ID = '001';

If we do not provide the Where condition, all the records in the table will be deleted.

Conclusion

For handling records in any database, both DDL and DML are necessary. When handling an extensive database consisting of multiple records, you need to use them properly. While filtering data from a table, make sure you have mentioned the condition properly in the Where clause. If you do not specify a condition, the entire table will be deleted.

For making any structural modifications, the commands mentioned under DDL will come in handy.