What are DDL and DML?

Question

I have heard the terms DDL and DML in reference to databases, but I don't understand what they are.

What are they and how do they relate to SQL?

Yancy

Answer 1

The following is adapted from here MySQL What is DDL, DML and DCL?:

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; also, 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 one or more tables.
• INSERT – insert data into a table.
• UPDATE – updates existing data within a table.
• DELETE – delete all records from a 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 – a point inside a transaction that allows rollback state to what it was at the time of the savepoint.
• SET TRANSACTION – specify characteristics for the transaction.

Razorbill

you might want to expand you answer a little in any future case that link would become unavailable – Alms

Sometimes you'll also see commands such as SELECT / SHOW / EXPLAIN / HELP split off from DML and grouped under DQL, with the Q from Query -- because they don't really manipulate anything. – Taggart

TRUNCATE should be DML – Matabele

Thanks friend, I'm starting and it's the best answer I found on the internet, God bless you. – Neille

@PeteAlvin, no it shouldn't: TRUNCATE basically drops & re-CREATEs a table (resetting auto-incrementing IDs), and does not use transaction logs (unlike DML) => it won't work if other tables (even empty ones) reference that table at all. – Gregorio

Answer 2

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, ...

Tuff

DESCRIBE is DDL or DML? – Psychopathology

@TomJMuthirenthi I don't think DESCRIBE fits into any of the two. It just returns it's own representation of how a Table/DB is structured. You can achieve the same thing with e.g. CREATE but not as "human-readable" – Flabbergast

If SELECT is considered DML, then DESCRIBE should be considered DDL. – Neuroticism

Answer 3

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

DDL commands are:

create,drop,alter,rename

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 :

update account set balance = 1000 where account_number = 01;

Susan

@isapir There are three general ways you can “manipulate” data in SQL: (1)You can change which results are presented. (2)You can change how the results are presented or organized (in terms of things like sort order, etc). (3)You can change the underlying data. In the first case, you can change which results are presented by using different WHERE clauses, JOIN predicates, etc, in a SELECT statement, ie- "select * from account ;" will give a much different result (assuming account has more than 1 row) than select * from account where balance = 1000; – Susan

When you select the data in different formats, you modify the presentation of the data for that query, and not the data itself. The M in DML stands for Manipulation, or in laymen terms Modification, of the data. SELECT does not modify the data. Alternatively, TRUNCATE does, and is therefore a DML instruction and NOT a DDL one. – Russo

Answer 4

DDL, Data Definition Language

• Create and modify the structure of database object in a database.
• These database object may have the Table, view, schema, indexes....etc

e.g.:

• CREATE, ALTER, DROP, TRUNCATE, COMMIT, etc.

DML, Data Manipulation Language

DML statement are affect on table. So that is the basic operations we perform in a table.

• Basic crud operation are perform in table.
• These crud operation are perform by the SELECT, INSERT, UPDATE, etc.

Below Commands are used in DML:

• INSERT, UPDATE, SELECT, DELETE, etc.

Elias

Answer 5

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

DDL i.e Data Definition Language

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
4. TRUNCATE

DML i.e. Data Manipulation Language

People come/go inside/from your house

1. SELECT
2. DELETE
3. UPDATE

DCL i.e. Data Control Language

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

1. GRANT PERMISSION

Ruelle

I wonder how we can build and use houses, without using such abbreviations? :P – Strawberry

TRUNCATE is DDL not DML. That may not seem logical until you stop and look at how it's implemented and the permissions needed. It's basically syntactic shorthand for a DROP followed by a CREATE using the same original schema. You need DROP privileges to run it, and you cannot TRUNCATE if FOREIGN KEY constraints are defined, even if there are no entries pointing to it, meaning a DELETE without a WHERE clause would succeed. – Tittle

@RichardSmith highly appreciate your comment, clarifying it anyone who reads this answer. And after I read this comment, it seems pretty logical that TRUNCATE is DDL – Ruelle

Answer 6

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

---

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

Visit this site for more info: http://blog.sqlauthority.com/2008/01/15/sql-server-what-is-dml-ddl-dcl-and-tcl-introduction-and-examples/

Enlightenment

Answer 7

DDL = Data Definition Language, any commands that provides structure and other information about your data

DML = Data Manipulation Language, there's only 3 of them, INSERT, UPDATE, DELETE. 4, if you will count SELECT * INTO x_tbl from tbl of MSSQL (ANSI SQL: CREATE TABLE x_tbl AS SELECT * FROM tbl)

Poleyn

Answer 8

DDL is Data Definition Language: Just think you are defining the DB. So we use CREATE,ALTER TRUNCATE commands.
DML is after defining we are Manipulating the data. So we use SELECT,INSERT, UPDATE, DELETE command.

Remember DDL commands are auto-committed. You don't need to use COMMIT statements.
DML (Data Manipulation Language) commands need to be commited/rolled back.

Mudcat

Answer 9

DDL

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

DML

Insert ,Delete,Update,Truncate of (Tables)

Possessory

Answer 10

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

Weald

Answer 11

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.

Necessaries

Answer 12

DDL: Change the schema

DML: Change the data

Seems specific to MySQL limitations (rails's source code)

Siliculose

Answer 13

A different response on DML may be helpful for those investigating dbt, a widely used open source tool for deployment of data transformations. (See More detail here.)

dbt facilitates "select" statements but not other DML commands. See, e.g. "Why can't I just write DML in my transformations?"

Septa