Data Manipulation Language (DML)
Data Manipulation Language (DML) is used in SQL (Structured Query Language) to perform operations that modify or manage the data in a database. It includes INSERT, UPDATE, and DELETE commands. Let’s go through these commands in a simple, beginner-friendly way with real-life examples.
1. INSERT Command
The INSERT command is used to add new records (rows) to a table in the database.
Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
Example:
Imagine you have a table called Students with the following structure. You want to add a student record to the table.
INSERT INTO Students (StudentID, Name, Age, Grade)
VALUES (1, 'John Doe', 18, 'A');
After executing this command, the Students table will look like this:
| StudentID | Name | Age | Grade |
|---|---|---|---|
| 1 | John Doe | 18 | A |
2. UPDATE Command
The UPDATE command is used to modify existing records in a table.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
Let's say John Doe's grade needs to be updated from 'A' to 'B'.
UPDATE Students
SET Grade = 'B'
WHERE StudentID = 1;
After executing this command, the Students table will look like this:
| StudentID | Name | Age | Grade |
|---|---|---|---|
| 1 | John Doe | 18 | B |
3. DELETE Command
The DELETE command is used to remove existing records from a table.
Syntax:
DELETE FROM table_name
WHERE condition;
Example:
If you need to remove John Doe's record from the Students table.
DELETE FROM Students
WHERE StudentID = 1;
After executing this command, the Students table will be empty.
Key Points to Remember
- INSERT: Adds new rows to a table.
- UPDATE: Modifies existing rows based on a condition.
- DELETE: Removes rows based on a condition.
Practical Real-World Example
If you’re managing a library database:
- Use INSERT to add details of a new book.
- Use UPDATE to change the availability status of a borrowed book.
- Use DELETE to remove records of outdated or damaged books.