DML (INSERT, UPDATE, DELETE) in DBMS Theory - Full Explanation

Introduction

Imagine you have a list of contacts on paper, and you want to add new friends, change phone numbers, or remove old contacts. Managing data in a database works similarly, and DML commands help you do these tasks easily.

Explanation

INSERT

INSERT adds new data into a database table. You specify the table and the values for each column you want to add. This is like writing a new contact on your list.

INSERT is used to add new records to a table.

UPDATE

UPDATE changes existing data in a table. You choose which rows to change by setting conditions, then specify the new values. This is like correcting a phone number on your contact list.

UPDATE modifies existing records based on conditions.

DELETE

DELETE removes data from a table. You select which rows to delete using conditions. This is like erasing a contact you no longer need from your list.

DELETE removes records from a table based on conditions.

Real World Analogy

Think of a notebook where you keep your friends' contact details. When you meet a new friend, you write their info down (INSERT). If they change their phone number, you cross out the old one and write the new number (UPDATE). If you lose touch, you erase their details (DELETE).

INSERT → Writing a new friend's contact details in your notebook

UPDATE → Changing a friend's phone number in your notebook

DELETE → Erasing a friend's contact details from your notebook

Diagram

Diagram

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│   INSERT    │──────▶│   UPDATE    │──────▶│   DELETE    │
│ Add records │       │ Modify data │       │ Remove data │
└─────────────┘       └─────────────┘       └─────────────┘

This diagram shows the three main DML operations: adding, modifying, and removing data.

Key Facts

DML → Data Manipulation Language commands used to manage data in database tables.

INSERT → Adds new rows of data into a table.

UPDATE → Changes existing data in one or more rows based on conditions.

DELETE → Removes rows of data from a table based on conditions.

WHERE clause → A condition used in UPDATE and DELETE to specify which rows to affect.

Code Example

DBMS Theory

CREATE TABLE contacts (id INT, name TEXT, phone TEXT);
INSERT INTO contacts (id, name, phone) VALUES (1, 'Alice', '12345');
UPDATE contacts SET phone = '67890' WHERE id = 1;
DELETE FROM contacts WHERE id = 1;

Common Confusions

Believing DELETE removes the entire table instead of just rows.

Believing DELETE removes the entire table instead of just rows. DELETE removes only selected rows; to remove the whole table structure, DROP TABLE is used.

Thinking UPDATE adds new rows if no matching rows exist.

Thinking UPDATE adds new rows if no matching rows exist. UPDATE only changes existing rows; it does not add new rows. Use INSERT to add new data.

Assuming INSERT can modify existing rows.

Assuming INSERT can modify existing rows. INSERT only adds new rows; it cannot change existing data.

Summary

DML commands let you add, change, and remove data in database tables.

INSERT adds new records, UPDATE changes existing ones, and DELETE removes records.

Using conditions with UPDATE and DELETE helps target specific rows safely.