Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

databases
sql
communitycreator

What are the differences between DDL and DML?

NDUKWE CHIDERA K.

A collection of data that is logically related as tables/relations and represents real-world entities is simply known as a database.

There are software systems which are used to manage these databases. They are referred to as Database Management Systems (DBMS).

Common examples include:

  • MySql
  • Oracle
  • PostgreSQL
  • SQLite, and so on.

The activities performed by these DBMS on databases can be categorized into two broad categories. They include

  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)

Data Definition Language

DDL query routines are used to stipulate and spell out what the database and schema structures will be like, the table they contain, and how they are related, usually with the application of some constraints.

Data Manipulation Language

DML are sets of query language routines which are used to manipulate the database, tables, constraints, etc., that have already been created.

We basically apply these routines when we wish to use the already laid-down structures of the database.

DDL vs DML

The table below lists the differences between DDL and DML.

Differences between DDL and DML

DDL

DML

Used to create column/attribute of a table.

Rows/tuples of a relation are added using this language routine.

Sets relationships and constraints between table/relations.

Uses the existing constraints that have already been set, can't create a new one.

Databases, tables and other database rules and structures are set here

Can only add to, read from and remove contents of these defined structures like the tables and databases.

Don't have any subcategories

Have a couple of subcategories which are procedural and non-procedural DML

Most statements here are straightforward with no clauses like the WHERE clause

Makes very wide use of clauses like WHERE

Uses the following command, CREATE, DROP, RENAME and ALTER

The command used are SELECT, INSERT, MERGE, UPDATE, DELETE

Basic Action: CREATION / DEFINITION

Basic Action: MANIPULATION / SORTING

No calculation

Carries out calculations and logics

Some sample DDL and DML SQL statements

1. DDL

  • Defining a new table
CREATE TABLE `students` ( `id` INT(10) NOT NULL AUTO_INCREMENT , `student_name` VARCHAR(200) NOT NULL , `student_email` VARCHAR(200) NOT NULL , `student_addr` VARCHAR(200) NOT NULL , PRIMARY KEY (`id`));

With the above statement, a students table with columns: students, student_name, student_email student_addr will be created.

2. DML

  • Adding to the defined table:
INSERT INTO `students` (`id`, `student_name`, `student_email`, `student_addr`) VALUES ('1', 'marly', 'marlywrong@gmail.com', '3 marly avenue, Wine state');
 

With the INSERT INTO and VALUES command in the sql statement above, the students table earlier created can be manipulated to add some records.

The backticks (`) in the statements above are part of the sql syntax, which are optional and help to differentiate user-defined words from keywords in the statement.

RELATED TAGS

databases
sql
communitycreator
RELATED COURSES

View all Courses

Keep Exploring