Why DML operations modify data in MySQL - Why It Works This Way

Overview - Why DML operations modify data

What is it?

DML stands for Data Manipulation Language, which includes commands that change the data inside a database. These commands let you add new data, change existing data, or remove data. The main DML commands are INSERT, UPDATE, and DELETE. They directly modify the contents of database tables.

Why it matters

Without DML operations, databases would only store static data that never changes. Real-world applications need to add new records, update information, or delete outdated data constantly. DML operations solve this by providing a way to safely and efficiently change data, keeping databases useful and up-to-date.

Where it fits

Before learning why DML modifies data, you should understand what a database and tables are, and basic SQL SELECT queries to read data. After this, you can learn about transaction control and how DML changes are saved or undone, and then explore database performance and indexing.

Mental Model

Core Idea

DML operations are the tools that let you change the actual data stored in a database, making it dynamic and useful.

Think of it like...

Think of a database table like a spreadsheet. DML commands are like the actions you take to add new rows, edit cells, or delete rows in that spreadsheet.

┌─────────────┐       ┌───────────────┐       ┌───────────────┐
│   INSERT    │──────▶│ Add new rows  │
└─────────────┘       └───────────────┘
      │
      ▼
┌─────────────┐       ┌───────────────┐       ┌───────────────┐
│   UPDATE    │──────▶│ Change values │
└─────────────┘       └───────────────┘
      │
      ▼
┌─────────────┐       ┌───────────────┐       ┌───────────────┐
│   DELETE    │──────▶│ Remove rows   │
└─────────────┘       └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Database Tables

Concept: Learn what a database table is and how data is organized in rows and columns.

A database table is like a grid with rows and columns. Each row is a record, and each column is a field or attribute. For example, a table named 'Customers' might have columns like 'ID', 'Name', and 'Email'. Each row holds data for one customer.

Result

You can picture data as organized in tables, ready to be read or changed.

Understanding tables as structured grids helps you see why we need special commands to add, change, or remove rows.

2

Foundation

Introduction to SQL Commands

Concept: Learn the basic SQL commands to interact with data, focusing on reading data first.

SQL lets you talk to databases. The SELECT command reads data from tables without changing it. For example, 'SELECT * FROM Customers;' shows all customers. This is the first step before learning how to change data.

Result

You can retrieve data from tables to see what is stored.

Knowing how to read data is essential before learning how to modify it.

3

Intermediate

INSERT Adds New Data Rows

🤔Before reading on: do you think INSERT can change existing rows or only add new ones? Commit to your answer.

Concept: The INSERT command adds new rows to a table without affecting existing data.

Using INSERT, you specify which table to add data to and provide values for each column. For example: 'INSERT INTO Customers (ID, Name, Email) VALUES (1, "Alice", "[e-mail]");' adds a new customer. Existing rows stay unchanged.

Result

A new row appears in the table with the given data.

Understanding that INSERT only adds new data helps prevent accidental overwriting of existing information.

4

Intermediate

UPDATE Changes Existing Data

🤔Before reading on: does UPDATE change all rows or only specific ones? Commit to your answer.

Concept: UPDATE modifies data in existing rows based on conditions you specify.

UPDATE lets you change one or more columns in rows that match a condition. For example: 'UPDATE Customers SET Email = "[e-mail]" WHERE ID = 1;' changes the email of the customer with ID 1. Rows not matching the condition stay the same.

Result

Selected rows have their data changed as specified.

Knowing UPDATE targets specific rows prevents unintended data changes.

5

Intermediate

DELETE Removes Data Rows

🤔Before reading on: does DELETE remove data permanently or just hide it? Commit to your answer.

Concept: DELETE removes rows from a table based on conditions, permanently deleting data.

DELETE removes rows that match a condition. For example: 'DELETE FROM Customers WHERE ID = 1;' removes the customer with ID 1. If no condition is given, all rows are deleted, which is usually dangerous.

Result

Rows matching the condition are removed from the table.

Understanding DELETE permanently removes data helps you use it carefully to avoid data loss.

6

Advanced

How DML Changes Affect Database State

🤔Before reading on: do you think DML changes are immediately permanent or can be undone? Commit to your answer.

Concept: DML operations change the database state but can be controlled using transactions to commit or rollback changes.

When you run INSERT, UPDATE, or DELETE, the database changes data but these changes are part of a transaction. You can commit to save changes permanently or rollback to undo them if needed. This control ensures data integrity and safety.

Result

Data changes are applied or undone based on transaction control.

Knowing that DML changes are transactional explains how databases maintain consistency and recover from errors.

7

Expert

Internal Mechanisms of DML Modifications

🤔Before reading on: do you think DML commands rewrite entire tables or only affect targeted rows? Commit to your answer.

Concept: DML commands modify data by changing only the affected rows using internal storage structures and logging for recovery.

Databases store data in pages and use indexes to find rows quickly. When a DML command runs, only the relevant pages and rows are updated. The database logs these changes in a transaction log to allow rollback and crash recovery. This efficient mechanism avoids rewriting the whole table.

Result

Only targeted data changes, with logs ensuring durability and consistency.

Understanding the internal selective update and logging mechanism reveals why DML operations are efficient and reliable even in large databases.

Under the Hood

DML operations work by locating the specific rows to change using indexes or scanning, then modifying data pages in memory. Changes are recorded in a transaction log before being applied to ensure durability and allow rollback. The database engine manages locks to prevent conflicts and maintains consistency during concurrent access.

Why designed this way?

This design balances efficiency and safety. Changing only affected rows avoids costly full-table rewrites. Logging changes enables recovery from crashes or errors. Locking prevents data corruption when multiple users modify data simultaneously. Alternatives like rewriting entire tables or no logging were rejected due to poor performance or risk of data loss.

┌───────────────┐
│  DML Command  │
└──────┬────────┘
       │
       ▼
┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Locate Rows   │──────▶│ Modify Pages  │──────▶│ Write Log     │
└───────────────┘       └───────────────┘       └───────────────┘
       │                      │                      │
       ▼                      ▼                      ▼
┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│ Lock Rows     │       │ Update Cache  │       │ Enable Rollback│
└───────────────┘       └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does UPDATE without a WHERE clause change all rows or none? Commit to your answer.

Common Belief: UPDATE without WHERE only changes some rows or does nothing.

Reality: UPDATE without WHERE changes every row in the table.

Why it matters: Accidentally running UPDATE without WHERE can overwrite all data, causing major data loss.

Quick: Does DELETE remove data permanently immediately or can it be undone? Commit to your answer.

Common Belief: DELETE permanently removes data instantly with no way to recover.

Reality: DELETE changes can be undone if inside a transaction that is not yet committed.

Why it matters: Knowing this prevents panic and allows safe testing and error recovery.

Quick: Does INSERT overwrite existing rows if the primary key matches? Commit to your answer.

Common Belief: INSERT will overwrite existing rows if keys match.

Reality: INSERT fails with a duplicate key error; it never overwrites existing rows.

Why it matters: Understanding this avoids confusion and errors when adding data.

Quick: Does DML always lock the entire table during operations? Commit to your answer.

Common Belief: DML commands always lock the whole table, blocking all other access.

Reality: Modern databases use row-level locking, locking only affected rows to allow concurrency.

Why it matters: Knowing this explains how databases handle many users efficiently.

Expert Zone

1

DML operations interact closely with transaction isolation levels, affecting visibility and locking behavior in subtle ways.

2

The physical storage format and indexing strategy can greatly influence DML performance and locking granularity.

3

Some databases optimize bulk DML operations differently, using techniques like minimal logging or batch processing.

When NOT to use

DML is not suitable for schema changes or defining database structure; use DDL (Data Definition Language) instead. For read-only reporting, avoid DML to prevent unnecessary locks and overhead. For very large bulk data loads, specialized bulk import tools may be more efficient than standard DML.

Production Patterns

In production, DML commands are often wrapped in transactions to ensure atomicity. Batch updates use WHERE clauses carefully to limit impact. Soft deletes (marking rows as inactive) are common to avoid data loss. Auditing triggers or logging track DML changes for compliance.

Connections

Transactions

DML operations are the main actions controlled by transactions to ensure data integrity.

Understanding how DML fits into transactions helps grasp how databases keep data consistent even with many users.

Version Control Systems

Both DML in databases and version control manage changes to data over time with the ability to undo or commit changes.

Seeing DML as a form of controlled data change connects database operations to software development practices.

Inventory Management

DML operations mirror real-world inventory actions: adding stock (INSERT), updating quantities (UPDATE), and removing items (DELETE).

Relating DML to everyday inventory helps understand why modifying data is essential for accurate records.

Common Pitfalls

#1 Running UPDATE without WHERE clause changes all rows unintentionally.

Wrong approach:

UPDATE Customers SET Email = '[e-mail]';

Correct approach:

UPDATE Customers SET Email = '[e-mail]' WHERE ID = 5;

Root cause:

Forgetting to specify which rows to update causes the command to apply to the entire table.

#2 Using DELETE without WHERE clause removes all data from the table.

Wrong approach:

DELETE FROM Orders;

Correct approach:

DELETE FROM Orders WHERE OrderDate < '2023-01-01';

Root cause:

Omitting the condition leads to deleting every row, often by accident.

#3 Trying to INSERT a row with a duplicate primary key causes an error.

Wrong approach:

INSERT INTO Users (ID, Name) VALUES (1, 'Bob'); -- when ID 1 exists

Correct approach:

INSERT INTO Users (ID, Name) VALUES (2, 'Bob');

Root cause:

Not checking for existing keys before inserting leads to constraint violations.

Key Takeaways

DML operations are essential commands that let you add, change, or remove data in database tables.

INSERT adds new rows, UPDATE changes existing rows based on conditions, and DELETE removes rows permanently.

DML changes are controlled by transactions, allowing safe commit or rollback to maintain data integrity.

Understanding how DML works internally reveals why it is efficient and reliable even with many users.

Careful use of WHERE clauses in UPDATE and DELETE is critical to avoid unintended data loss.