Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
In database management, DDL (data definition language) and DML (data manipulation language) are two important concepts. Both of these are used to create and manage databases, but they differ in their functions and usages. In this blog, we will discuss the difference between DDL and DML.
A database is a collection or list of data stored in a computer electronically. Database Management System (DBMS) is the most common way to access and manage these databases. SQL is needed to interact with these databases. In this blog, we will explain the difference between DDL and DML. But, before starting our main topic, let’s understand what SQL is.
SQL is an acronym for Structured Query Language. It lets you access and manipulate databases. SQL commands are used to create tables, insert data, update data, etc. These SQL commands are categorised into DDL, DML, and DCL.
SQL is a query language frequently used in all types of applications. Programmers use it as it integrates well with different programming languages. We can embed SQL queries to build high-performing data processing applications.
What is Data Definition Language (DDL)?
DDL stands for Data Definition Language. It is used to define and create the structure of the databases. We can perform different actions like create, alter, drop, truncate, etc.
The following list explains these commands in brief :
CREATE: used to create databases.
ALTER: used to make changes in the structure of databases.
TRUNCATE: used to delete all data of the table in the databases but not the table.
RENAME: To rename databases.
DROP: To drop objects from a database, such as tables.
The first syntax statement is for creating databases. The following is to create tables in the database. You can add a comma after every column statement to create a table with multiple columns. Don’t add a comma at the end of the last statement.
For example,
CREATE DATABASE Music;
The above SQL query creates a database named Music in the SQL database.
Let’s create a table named songs in the database.
CREATE TABLE Songs
(
Name varchar(200),
Artist varchar(200),
Current_Ranking int
);
Output:
The above query created an empty table named “Songs” in our database.
ALTER
ALTER command allows you to alter the structure of the database.
Syntax:
ALTER TABLE TABLE_NAME
ADD COLUMN_NAME DATATYPE;
For example,
ALTER TABLE Songs
ADD Album varchar(20);
Output:
The above command adds a column name “Album” of varchar data type in the Songs table.
DROP
DROP statements are used to remove database instances.
For example,
DROP DATABASE Music;
This SQL command removes the Music database from SQL database. Let’s see how to remove the Songs table we created above.
DROP TABLE Songs;
What is Data Manipulation Language (DML)?
DML stands for data manipulation language. It is a programming language that gives users the access to modify the data stored in the database. It is used to get data from the database and make changes. This is the main difference between ddl and dml.
Following are some of the actions we perform with DML:
SELECT
INSERT
DELETE
UPDATE, etc.
It is further classified into Procedural and Non - Procedural DMLs.
Examples of DML in SQL
Following are some examples of DDL commands with their syntaxes:
INSERT
INSERT command is used to add one or more rows of data into a table in the SQL database.
Syntax:
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);
In this section of the blog, we will compare the two languages we learned in detail until now. Now it's time to discuss the difference between ddl and dml.
DDL
DML
DDL stands for Data Definition Language.
DML stands for Data Manipulation Language.
It is used to create the structure of the database objects.
It is used to modify the data in the databases.
It is not further classified.
It is further classified into Procedural and Non - Procedural DMLs.
It only affects the structure of database instances.
It affects the content or the data stored in the database.
The changes made with DDL are permanent i.e. they are auto-committed.
The changes made with DML are not permanent i.e. they are not auto-committed.
There is no use of the WHERE clause in DDL.
It uses the WHERE clause while modifying the data.
Examples of DDL commands are CREATE, ALTER, RENAME, etc.
Examples of DML commands are UPDATE, SELECT, INSERT, etc.
DDL stands for Data Definition Language, and DML stands for Data Manipulation Language. DDL is used to create the structure of the database whereas DML is used to change the data stored in the databases.
What are the types of DML?
DML is classified into two types: Procedural (or Low Level) and Non-Procedural (or High Level). Procedural DML clearly mentions which and how the data needs to be accessed. Non-Procedural DML does not state how and when to access the data.
What is the difference between TRUNCATE and DROP commands?
DROP is used to remove the whole table or database with its data. Whereas TRUNCATE is used to only remove the data stored in that table or database, not affecting the structure of our database object.
Conclusion
We hope this article was insightful and you learned something new. In this blog, we learned in detail about two of the database languages. We learned about DDL(data definition language) and DML(data manipulation language). We also discussed their syntax with some examples. In the end, we saw the difference between ddl and dml.
If you want to learn about database languages, do visit Database Languages.