Difference Between DDL and DML

DDL

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:

• CREATE
• ALTER
• TRUNCATE
• DROP
• RENAME

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.

Syntax:

Example:

Creating Database

Creating Table

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:

Example:

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.

Syntax:

Example:

4. DROP STATEMENT

It is used for deleting a database object permanently.

Syntax:

Example:

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.

Syntax:

Example:

DML

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:

• SELECT
• INSERT
• UPDATE
• DELETE

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.

Syntax:

Example:

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.

Syntax:

Example:

3. UPDATE STATEMENT

It allows us to update the existing data of the tables.

Syntax:

Example:

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.

Syntax:

Example:

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