Difference Between DDL And DML In DBMS (With Examples)

What Is Data Definition Language (DDL)?

Data definition or data description language is a syntax used to establish and modify the structure of objects in a database by dealing by dealing with descriptions of the database schema. DDL statements are similar to a computer programming language for defining structures, especially data schemas. DDL statements create, modify and remove database objects such as tables, indexes and users. Basic commands present in DDL are CREATE, ALTER, DROP, TRUNCATE, RENAME etc.

DDL Commands

• CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).
• DROP – is used to delete objects from the database.
• ALTER-is used to alter the structure of the database.
• TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.
• COMMENT –is used to add comments to the data dictionary.
• RENAME –is used to rename an object existing in the database.

Facts About Data Definition Language (DDL)

• Data Definition Language (DDL) is a type of SQL command that helps to define the schema of the Database.
• DDL statements are used to create database, schema, constraints, users, tables etc.
• The DDL commands do not have further classification.
• DDL statements operate on the entire table.
• Basic commands present in DDL are CREATE, ALTER, DROP, TRUNCATE, RENAME etc.
• While using the DDL statements, the changes made by them cannot be rolled back. So there is need to run COMMIT or ROLLBACK.
• DDL is a declarative method.
• DDL command affects the entire database or the table.
• The DDL statements do not have a WHERE clause to filter the data.
• DDL statements get executed in their transaction and so get committed instantly as the changes made by each of these statements are permanent.

What Is Data Manipulation Language (DML)?

Data Manipulation Language (DML) is a computer programming language including commands permitting users to manipulate data in a database. This manipulation involves inserting data into database tables, retrieving existing data from existing tables and modifying existing data. DML is mostly incorporated in SQL databases. The functional capability of DML is organized in manipulation commands like SELECT, UPDATE, INSERT INTO and DELETE FROM.

DML Commands

• SELECT: This command is used to retrieve rows from a table. The syntax is SELECT [column name(s)] from [table name] where [conditions]. SELECT is the most widely used DML command in SQL.
• UPDATE: This command modifies data of one or more records. An update command syntax is UPDATE [table name] SET [column name = value] where [condition].
• INSERT: This command adds one or more records to a database table. The insert command syntax is INSERT INTO [table name] [column(s)] VALUES [value(s)].
• DELETE: This command removes one or more records from a table according to specified conditions. Delete command syntax is DELETE FROM [table name] where [condition].

Facts About Data Manipulation Language (DML)

• Data Manipulation Language (DML) is a type of SQL command that helps to retrieve and manage data in relational databases.
• DML statement is used to insert, update or delete the records.
• The DML commands can be divided into Procedural DML and Declarative DML.
• The DML statements operate on rows.
• Basic commands present in DML are INSERT, UPDATE, DELETE, SELECT, MERGE etc.
• In DML, statements, the COMMIT and ROLLBACK commands should be run to confirm the changes.
• DML is an imperative method.
• DML command affects one or more records in a table.
• The DML statements use WHERE clause to filter the data.
• The DML statements operate by modifying the data of the database objects, so these statements get executed according to the rules of the transaction.

Also Read: Difference Between Dynamic And Static SQL

Difference Between DDL And DML In Tabular Form

Also Read: Difference Between Normalization And Denormalization In SQL