Why DML operations modify data in MySQL - Visual Breakdown

Concept Flow - Why DML operations modify data

Start with existing data

↓

Execute DML command

↓

Check command type

↓

Add new

↓

rows

↓

Data in table is modified

↓

End

DML commands (INSERT, UPDATE, DELETE) change the data in a table by adding, changing, or removing rows.

Execution Sample

MySQL

INSERT INTO users (id, name) VALUES (1, 'Alice');
UPDATE users SET name = 'Bob' WHERE id = 1;
DELETE FROM users WHERE id = 1;

This sequence adds a user, changes the user's name, then removes the user from the table.

Execution Table

Step	Command	Action	Affected Rows	Table Data After
1	INSERT INTO users (id, name) VALUES (1, 'Alice');	Add new row with id=1, name='Alice'	1	[{"id":1, "name":"Alice"}]
2	UPDATE users SET name = 'Bob' WHERE id = 1;	Change name to 'Bob' where id=1	1	[{"id":1, "name":"Bob"}]
3	DELETE FROM users WHERE id = 1;	Remove row where id=1	1	[]
4	No more commands	Execution ends	0	[]

💡 All commands executed, data modified accordingly, no more commands to run.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3
users table	[]	[{"id":1, "name":"Alice"}]	[{"id":1, "name":"Bob"}]	[]

Key Moments - 2 Insights

Why does the UPDATE command change data only for rows matching the condition?

Because UPDATE modifies only rows that meet the WHERE condition, as shown in execution_table step 2 where only the row with id=1 is changed.

Does DELETE remove all rows or only some?

DELETE removes only rows matching the WHERE condition. In step 3, only the row with id=1 is removed, leaving the table empty.

Visual Quiz - 3 Questions

Look at the execution_table at step 2, what is the name of the user after the UPDATE command?

A Bob

B No name

C Alice

D NULL

💡 Hint

Check the 'Table Data After' column in execution_table row for step 2.

At which step does the users table become empty?

A After Step 2

B After Step 3

C After Step 1

D Never

💡 Hint

Look at the 'Table Data After' column in execution_table for each step.

If the DELETE command had no WHERE clause, what would happen to the table?

A Only one row deleted

B No rows deleted

C All rows deleted

D Error occurs

💡 Hint

DELETE without WHERE removes all rows from the table.

Concept Snapshot

DML commands modify data in tables:
- INSERT adds new rows
- UPDATE changes existing rows matching conditions
- DELETE removes rows matching conditions
These commands change the table's data immediately.

Full Transcript

DML operations like INSERT, UPDATE, and DELETE modify data in database tables. INSERT adds new rows, UPDATE changes existing rows based on conditions, and DELETE removes rows based on conditions. Each command changes the table's data, which you can see step-by-step in the execution table. For example, inserting a user adds a row, updating changes that row's data, and deleting removes it. Understanding these steps helps you see how data changes in real time.