What is DML?

Data Manipulation Language (DML) is a category of SQL commands used to manage and manipulate the data stored inside database tables. While DDL defines the structure of the database, DML works with the actual data — adding, modifying, retrieving, and deleting records.

DML commands are the most frequently used SQL commands in real-world applications. Every time you sign up on a website, place an order, update your profile, or delete a post — DML commands are running behind the scenes inside MySQL.

Main DML Commands in MySQL

MySQL provides four core DML commands to manage data within tables:

 1. INSERT Command

The INSERT command is used to add new rows of data into a table.

Insert a Single Record

INSERT INTO Students (name, age, city, course)
VALUES ('Rumman Ansari', 25, 'Kolkata', 'Computer Science');

Insert Multiple Records at Once

INSERT INTO Students (name, age, city, course) VALUES
('Aditi Sharma', 22, 'Delhi', 'Data Science'),
('Rohan Mehta', 24, 'Mumbai', 'AI & ML'),
('Priya Singh', 23, 'Bangalore', 'Cybersecurity');

Insert Without Column Names (All Columns)

-- Values must match the exact column order of the table
INSERT INTO Students 
VALUES (105, 'Arjun Verma', 26, 'Pune', 'Cloud Computing');

Insert Data from Another Table

-- Copy data from one table to another
INSERT INTO Students_Backup (name, age, city, course)
SELECT name, age, city, course FROM Students
WHERE city = 'Kolkata';

 2. UPDATE Command

The UPDATE command is used to modify existing records in a table. Always pair it with a WHERE clause to avoid updating all rows.

Update a Single Column

UPDATE Students 
SET age = 26 
WHERE name = 'Rumman Ansari';

Update Multiple Columns

UPDATE Students 
SET city = 'Hyderabad', course = 'Cloud Computing' 
WHERE student_id = 102;

Update Using Expressions

-- Increase everyone's age by 1
UPDATE Students 
SET age = age + 1;

-- Apply discount to product prices
UPDATE Products 
SET price = price * 0.90 
WHERE category = 'Electronics';

Update Using a Condition with JOIN

UPDATE Students s
JOIN Courses c ON s.course_id = c.course_id
SET s.fee = c.fee
WHERE c.course_name = 'Data Science';

 3. DELETE Command

The DELETE command is used to remove existing rows from a table. It can be rolled back if used within a transaction.

Delete a Specific Record

DELETE FROM Students 
WHERE student_id = 101;

Delete Records with Conditions

-- Delete all students from a specific city
DELETE FROM Students 
WHERE city = 'Delhi';

-- Delete records older than a certain date
DELETE FROM Orders 
WHERE order_date < '2023-01-01';

Delete Using JOIN

DELETE s FROM Students s
JOIN Courses c ON s.course_id = c.course_id
WHERE c.course_name = 'Discontinued Course';

Delete All Records (But Keep Structure)

DELETE FROM Students;

 4. SELECT Command

The SELECT command is used to retrieve data from one or more tables. It is the most widely used SQL command.

Select All Columns

SELECT * FROM Students;

Select Specific Columns

SELECT name, city, course FROM Students;

Select with WHERE Clause

SELECT * FROM Students 
WHERE age > 22 AND city = 'Kolkata';

Select with ORDER BY

SELECT * FROM Students 
ORDER BY name ASC;

SELECT * FROM Students 
ORDER BY age DESC;

Select with LIMIT

-- Top 3 oldest students
SELECT * FROM Students 
ORDER BY age DESC 
LIMIT 3;

-- Pagination — Skip 5, fetch next 5
SELECT * FROM Students 
LIMIT 5 OFFSET 5;

Select with DISTINCT

-- Get unique city names
SELECT DISTINCT city FROM Students;

Select with GROUP BY and Aggregate Functions

SELECT city, COUNT(*) AS total_students
FROM Students
GROUP BY city
ORDER BY total_students DESC;

Select with JOIN

SELECT s.name, c.course_name, c.duration
FROM Students s
INNER JOIN Courses c ON s.course_id = c.course_id;

Important DML Operators

DML commands become powerful when combined with operators that help filter and refine data.

Example: Combining DML Operators

SELECT name, city, age 
FROM Students 
WHERE (city IN ('Kolkata', 'Mumbai')) 
  AND (age BETWEEN 20 AND 25) 
  AND (name LIKE 'A%')
ORDER BY age DESC;

DML & Transactions

One of the biggest advantages of DML over DDL is that DML changes can be controlled using transactions. This means you can commit changes permanently or roll back if something goes wrong.

START TRANSACTION;

UPDATE Accounts SET balance = balance - 5000 WHERE acc_id = 1;
UPDATE Accounts SET balance = balance + 5000 WHERE acc_id = 2;

-- If both succeed
COMMIT;

-- If something fails
-- ROLLBACK;

DDL vs DML — Side-by-Side Comparison

Key Characteristics of DML

Real-World Applications of DML

Common Mistakes to Avoid

Best Practices for Using DML