Mysql: What is DDL and DML

Can you please help me understand from scratch about DDL & DML?

 Answers

DDL is Data Definition Language : it is used to define data structures.

For example, with SQL, it would be instructions such as create table, alter table, ...

DML is Data Manipulation Language : it is used to manipulate data itself.

For example, with SQL, it would be instructions such as insert, update, delete, ...

---

More information see here: MySQL What is DDL, DML and DCL?, the original is as follows:

DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

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

DML

DML is short name of Data Manipulation Language which deals with data manipulation, and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in database.

• SELECT – retrieve data from the a database
• INSERT – insert data into a table
• UPDATE – updates existing data within a table
• DELETE – Delete all records from a database table
• MERGE – UPSERT operation (insert or update)
• CALL – call a PL/SQL or Java subprogram
• EXPLAIN PLAN – interpretation of the data access path
• LOCK TABLE – concurrency Control

DCL

DCL is short name of Data Control Language which includes commands such as GRANT, and mostly concerned with rights, permissions and other controls of the database system.

• GRANT – allow users access privileges to database
• REVOKE – withdraw users access privileges given by using the GRANT command

TCL

TCL is short name of Transaction Control Language which deals with transaction within a database.

• COMMIT – commits a Transaction
• ROLLBACK – rollback a transaction in case of any error occurs
• SAVEPOINT – to rollback the transaction making points within groups
• SET TRANSACTION – specify characteristics for the transaction

---

DDL is Data Definition Language : Specification notation for defining the database schema. It works on Schema level.

DDL commands are:

create,drop,alter,rename,truncate

For example:

create table account ( account-number char(10), balance integer);

DML is Data Manipulation Language .It is used for accessing and manipulating the data.

DML commands are:

select,insert,delete,update,call

For example :

select account_number from account;

---

In layman terms suppose you want to build a house, what do you do.

DDL

1. Build from scratch
2. Rennovate it
3. Destroy the older one and recreate it from scratch

that is

1. CREATE
2. ALTER
3. DROP & CREATE

DML

People come/go inside/from your house

1. SELECT
2. DELETE
3. UPDATE
4. TRUNCATE

DCL

You want to control the people what part of the house they are allowed to access and kind of access.

1. GRANT PERMISSION

---

In simple words.

DDL(Data definition language): will work on structure of data. define the data structures.

DML (data manipulation language): will work on data. manipulates the data itself

---

DDL stands for Data Definition Language. DDL is used for defining structure of the table such as create a table or adding a column to table and even drop and truncate table. DML stands for Data Manipulation Language. As the name suggest DML used for manipulating the data of table. There are some commands in DML such as insert and delete.

---

DDL

Create,Alter,Drop of (Databases,Tables,Keys,Index,Views,Functions,Stored Procedures)

DML

Insert ,Delete,Update,Truncate of (Tables)

---

Data definition language(DDL) allows you to CREATE, ALTER, TRUNCATE and DELETE database objects such as schema, tables, view, sequence etc.

Data manipulation language makes user able to access and manipulate data. It is used to perform following operations.

Insert data into database Retrieve data from the database Update data in the database Delete data from the database