Databases & SQL · Class 12 · Inserting & Changing Data

MySQLDDL & DML⏱️ 9 min read

DDL and DML Commands

You have now written every command this chapter teaches. They fall into two groups, and the paper asks you which group a command belongs to. The answer is always the same question: does it change the shape of the table, or what is written inside it?

1Two jobs, two groups

Think of the attendance register in your class. Someone ruled the columns, wrote the headings and decided how many there are. Every day after that, a teacher writes names and marks in those columns, and sometimes rubs one out.

Those are two different jobs on the same register. SQL splits its commands the same way.

The two groups

Group	Full name	The job	Commands
DDL	Data Definition Language	Rules the columns — creates and changes the structure itself	create, alter, drop, truncate
DML	Data Manipulation Language	Writes in the columns — puts rows in, changes them, takes them out	insert, update, delete

The one-line test

Ask what is left behind. If the table itself is different afterwards — a new column, a new data type, no table at all — the command is DDL. If the table is exactly as it was and only the rows are different, it is DML.

2The DDL commands, with examples

A school is starting a sports club and needs somewhere to keep the members. Nothing exists yet, so the first commands are all DDL.

create database — DDL

mysql> create database sportsday;

Query OK, 1 row affected (0.01 sec)

mysql> use sportsday;

Database changed

Now the table. create table names the columns and their data types, and that list is the structure.

create table — DDL

mysql> create table club (

-> id int primary key,

-> name varchar(20),

-> game varchar(15)

-> );

Query OK, 0 rows affected (0.04 sec)

mysql> desc club;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

0 rows affected is worth reading. The command did its job perfectly and touched no rows at all, because there are none. That is what a DDL command looks like: it works on the table, not on the data.

3The DML commands, with examples

The register is ruled. Now people join the club, and every command from here is DML.

insert — DML

mysql> insert into club values (1, "Anjali", "Badminton");

Query OK, 1 row affected (0.01 sec)

mysql> insert into club values (2, "Ravi", "Chess"), (3, "Pema", "Football");

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from club;

+----+--------+-----------+

| id | name | game |

+----+--------+-----------+

| 1 | Anjali | Badminton |

| 2 | Ravi | Chess |

| 3 | Pema | Football |

+----+--------+-----------+

3 rows in set (0.00 sec)

Pema changes her game. update changes values in rows that are already there.

update — DML

mysql> update club set game = "Chess" where name = "Pema";

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from club;

+----+--------+-----------+

| id | name | game |

+----+--------+-----------+

| 1 | Anjali | Badminton |

| 2 | Ravi | Chess |

| 3 | Pema | Chess |

+----+--------+-----------+

3 rows in set (0.00 sec)

Anjali leaves the club. delete takes her whole row out.

delete — DML

mysql> delete from club where id = 1;

Query OK, 1 row affected (0.00 sec)

mysql> select * from club;

+----+------+-------+

| id | name | game |

+----+------+-------+

| 2 | Ravi | Chess |

| 3 | Pema | Chess |

+----+------+-------+

2 rows in set (0.00 sec)

Three commands, three different effects on the rows — and the table has the same three columns it was created with. Nothing DML does can change that.

4The pair that gets confused: alter and update

Both of these change the table in ordinary English, which is why the question is worth asking. alter adds a column, so every row gets a new box. update writes in a box that is already there.

alter — DDL, even though the rows look different

mysql> alter table club add house varchar(10) default "Blue";

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from club;

+----+------+-------+-------+

+----+------+-------+-------+

+----+------+-------+-------+

2 rows in set (0.00 sec)

Read the status line, not the table

Every row now shows Blue, so it is tempting to call this DML. The status line says 0 rows affected: MySQL changed the structure and gave the new column a default, and no row was edited. alter is DDL, always — even alter table … add primary key, which is the same command doing the same kind of job.

5Emptying a table and removing it

delete with no where takes out every row. It is still DML, and the proof is that the table is still standing afterwards.

delete with no where — the table survives

mysql> delete from club;

Query OK, 2 rows affected (0.00 sec)

mysql> select * from club;

Empty set (0.01 sec)

mysql> desc club;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | NO | PRI | NULL | |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

drop is the DDL twin of that. It removes the structure too, and the proof is the error you get when you ask for the table afterwards.

drop — DDL

mysql> drop table club;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from club;

ERROR 1146 (42S02): Table 'sportsday.club' doesn't exist

Three of them, side by side

truncate table t; also empties a table and keeps it, but it is grouped with DDL because of how it does it — it drops the table and builds it again. Deleting Rows sets all three out in one table, which is the form the question usually takes.

6Answering “differentiate between DDL and DML”

This is a two-mark question, and two marks means two differences, each with an example. One sentence each is enough.

DDL

Defines or changes the structure of the database and its tables. Commands: create, alter, drop, truncate. Example: alter table club add house varchar(10);

DML

Changes the data stored inside the tables. Commands: insert, update, delete. Example: update club set game = "Chess" where name = "Pema";

And where select sits

select reads data and changes nothing, so many books give it a group of its own, DQL — Data Query Language. Others count it as DML. Both answers are accepted; say what select does and you have the mark.