Difference Between DDL and DML
A database system contains a list of records. The SQL commands are categorized as DML, DDL, DQL, and DCL. Here, we will discuss DML and DDL, the difference between DDL and DML. DDL(Data Definition Language), DML(Data Manipulation Language). The difference between DDL and DML is that DDL focuses on structuring the database, whereas DML focus on manipulating the database.
Scope of Article
- This article defines DDL and DML and explains the commands used in DDL and DML.
- We will also discuss commands used in DDL, DML along with the syntax.
- This articles also show the difference between DDL and DML.
DDL stands for Data Definition Language. The DDL commands help us to define the structure of the database or schema and other database objects. DDL commands are auto-committed, which means the changes made in the database using DDL commands are saved permanently.
The commands that come under DDL language are:
Why DDL language?
- DDL allows us to create and structure our database.
- DDL improves the security efficiency of the data access.
- Multiple users can work on the same database.
1. CREATE STATEMENT
This statement is used to create the database and its objects like creating tables, functions, views, etc.
2. ALTER STATEMENT
This statement is used to alter the existing structure/objects in the database.
Syntax for adding a column in the existing table:
3. TRUNCATE STATEMENT
Truncate statement is used to remove the data from the table, as well as it removes the entire data including objects, structure, and continues to have the table as an object in the database.
4. DROP STATEMENT
It is used for deleting a database object permanently.
It will delete the table sales completely, if we want the sales again, we have to re-create it using the CREATE statement.
5. RENAME STATEMENT
This statement is used to rename the database and its objects.
DML stands for Data Manipulation Language. As its name says, it manipulates the existing data in the database. The changes made by DML commands aren't saved permanently. In DML, we can roll back our changes. Hence, DML commands aren't auto committed.
The commands that come under DML language are:
Why DML language:
- It helps us to manipulate the data.
- It helps us to specify which data to be manipulated.
1. SELECT STATEMENT
This statement is used to retrieve data from the database objects like tables.
Here, we extract 'ID' column from table 'ALT_CUSTOMER_ID' which was changed while renaming our 'CUSTOMER_ID' table.
2. INSERT STATEMENT
This statement is used to insert the data into rows of the table.
3. UPDATE STATEMENT
It allows us to update the existing data of the tables.
4. DELETE STATEMENT
This is similar to the truncate statement, in the truncate statement, it will delete all the records in the table and only leave with an empty table, whereas in the delete statement, it can either delete all the records or any number of records.
In this statement, it will delete only that record that has 'ID' = 2.
In the above statement, we have not mentioned any condition, so it will delete all the existing records in the entire table.
Difference between DDL and DML
|DDL stands for Data Definition Language.||DML stands for Data Manipulation Language.|
|It helps us to define the structure of the database.||It helps us to manage the data.|
|It affects the whole table.||It only affects the specified row(s) of the table.|
|Changes are permanent.||We can roll back our changes in the DML language.|
|It helps in defining the fields or columns of the tables.||It helps us in defining rows or records of the tables.|
|They are auto-committed (changes made are permanent).||They are not auto-committed (changes are not permanent).|
|They don't have further classification.||They are classified into procedural and non-procedural.|
|Commands used: CREATE, ALTER, TRUNCATE, RENAME, DROP.||Commands used: SELECT, UPDATE, DELETE, INSERT.|
- The SQL commands are divided into four categories DML, DDL, DCL, and DQL.
- In this article, we have discussed DDL (Data Definition Language) and DML (Data Manipulation Language). DDL help to define the structure of the database whereas DML helps to manage the data.
- Commands used in DDL are CREATE, ALTER, TRUNCATE, RENAME, DROP.
- Commands used in DML are SELECT, INSERT, UPDATE, DELETE.
- DDL is auto-committed, whereas DML isn't auto-committed.
- In DML, we can revert the changes that have been made in tables, but in DDL we can't revert the changes.