MySQL DML Commands — INSERT, UPDATE, DELETE & SELECT Guide (2026)

Introduction#

Why This Matters#

Data Manipulation Language (DML) commands are the most frequently written SQL statements in any application. Every web form submission triggers an INSERT, every profile update calls UPDATE, every search page runs a SELECT, and every record removal fires a DELETE. Knowing the exact syntax, edge cases, and best practices for each command directly affects how reliable and secure your application is.

These four commands also appear in virtually every SQL interview. A solid understanding of WHERE conditions, LIKE pattern matching, and ORDER BY will distinguish a skilled developer from someone who only knows the basics.

Sample Table Setup#

CREATE TABLE students (
    admno  INT(5)       PRIMARY KEY,
    name   VARCHAR(30)  NOT NULL,
    class  VARCHAR(10),
    fees   DECIMAL(8,2) DEFAULT 0.00
);

INSERT INTO — Adding New Records#

INSERT INTO tablename (fieldnames) VALUES (values);

Rules for values:

• String and date values must be enclosed in single or double quotes.
• Date values must be in YYYY-MM-DD format.
• Use the keyword NULL (without quotes) to insert an absent value.

-- Insert all columns in table order
INSERT INTO students VALUES (120, 'Aman Kumar', 'XII', 15000.00);
 
-- Insert specific columns (class defaults to NULL)
INSERT INTO students (admno, name) VALUES (121, 'Sanjay Kumar');
 
-- Insert NULL explicitly
INSERT INTO students VALUES (122, 'Priya Sharma', NULL, NULL);

To insert multiple rows in one statement (more efficient than separate INSERTs):

INSERT INTO students VALUES
    (123, 'Rahul Singh', 'XI', 12000.00),
    (124, 'Meena Verma', 'X',  10000.00);

UPDATE — Modifying Existing Records#

UPDATE tablename SET fieldname = newValue WHERE condition;

The WHERE clause is optional — omitting it updates every row in the table.

-- Change a specific student's name
UPDATE students SET name = 'Ashish Kumar' WHERE name = 'Aman Kumar';
 
-- Set all fees to a fixed value
UPDATE students SET fees = 5000;
 
-- Increase all fees by 500
UPDATE students SET fees = fees + 500;
 
-- Increase fees by 5%
UPDATE students SET fees = fees + fees * 5 / 100;
 
-- Update multiple columns at once
UPDATE students SET class = 'XII', fees = 18000 WHERE admno = 120;

DELETE FROM — Removing Records#

DELETE FROM tablename WHERE condition;

Without a WHERE clause, all rows are deleted (but the table structure remains).

-- Delete a specific student
DELETE FROM students WHERE admno = 122;
 
-- Delete students with fees above 15000
DELETE FROM students WHERE fees > 15000;
 
-- Delete all rows (table structure preserved; use TRUNCATE for speed)
DELETE FROM students;

TRUNCATE TABLE students is faster than DELETE FROM students on large tables because it bypasses row-by-row logging and resets AUTO_INCREMENT counters.

SELECT — Retrieving Data#

SELECT * | fieldnames | DISTINCT fieldname
FROM tablename
WHERE condition
ORDER BY fieldname ASC | DESC;

-- All columns, all rows
SELECT * FROM students;
 
-- Specific columns
SELECT admno, name FROM students;
 
-- Distinct class values
SELECT DISTINCT class FROM students;
 
-- Column alias
SELECT name AS "Student Name", fees AS "Annual Fees" FROM students;

WHERE Clause Conditions#

Relational operators:

SELECT * FROM students WHERE fees > 10000;
SELECT * FROM students WHERE name = 'Rahul Singh';
SELECT * FROM students WHERE fees != 5000;

BETWEEN — range of values (inclusive):

-- Equivalent to: WHERE fees >= 10000 AND fees <= 15000
SELECT * FROM students WHERE fees BETWEEN 10000 AND 15000;
SELECT * FROM students WHERE fees NOT BETWEEN 10000 AND 15000;

IN — match a list of values:

-- Equivalent to: WHERE class = 'XI' OR class = 'XII'
SELECT * FROM students WHERE class IN ('XI', 'XII');
SELECT * FROM students WHERE class NOT IN ('IX', 'X');

IS NULL / IS NOT NULL:

-- Rows where class has not been entered
SELECT * FROM students WHERE class IS NULL;
 
-- Rows where fees have been entered
SELECT * FROM students WHERE fees IS NOT NULL;

LIKE — pattern matching:

% matches any sequence of zero or more characters. _ matches exactly one character.

-- Name starts with 'A'
SELECT * FROM students WHERE name LIKE 'A%';
 
-- Name ends with 'Singh'
SELECT * FROM students WHERE name LIKE '%Singh';
 
-- Name contains 'Kumar' anywhere
SELECT * FROM students WHERE name LIKE '%Kumar%';
 
-- Second character is 'a'
SELECT * FROM students WHERE name LIKE '_a%';
 
-- Second-to-last character is 'a'
SELECT * FROM students WHERE name LIKE '%a_';

ORDER BY — Sorting Results#

-- Ascending (default)
SELECT * FROM students ORDER BY name;
 
-- Descending
SELECT * FROM students ORDER BY fees DESC;
 
-- Sort by multiple columns
SELECT * FROM students ORDER BY class ASC, fees DESC;

Common Mistakes#

• Omitting WHERE in UPDATE or DELETE. This modifies or removes every row. Always double-check.
• Using = NULL instead of IS NULL. WHERE class = NULL never matches anything; SQL requires IS NULL.
• Quoting NULL. Writing VALUES(122, 'Priya', 'NULL') stores the string "NULL", not an absent value.
• Wrong date format in INSERT. MySQL expects 'YYYY-MM-DD'; '25-01-2026' will fail or store incorrectly.
• Forgetting quotes around string values. WHERE name = Rahul causes a column-not-found error.
• Using LIKE '%term' on a large table without an index. Leading wildcards disable index usage; consider full-text search instead.

Best Practices#

• Always include a WHERE clause in UPDATE and DELETE unless you intentionally want to affect all rows.
• Wrap critical UPDATE/DELETE statements in a transaction (START TRANSACTION ... COMMIT) so you can ROLLBACK if something goes wrong.
• Use LIMIT on UPDATE and DELETE when targeting a subset of a large table to reduce lock duration.
• Prefer IS NULL / IS NOT NULL over workarounds like IFNULL(column, '') = ''.
• For bulk inserts use multi-row INSERT INTO ... VALUES (...), (...) instead of looping individual inserts.
• Use parameterised queries in application code to prevent SQL injection — never interpolate user input directly into SQL strings.

Key Takeaways#

• INSERT INTO adds new rows; string and date values must be quoted, and NULL (unquoted) inserts an absent value.
• UPDATE modifies existing rows; omitting WHERE updates every row in the table — always verify the condition first.
• DELETE FROM removes rows matching the WHERE condition; without WHERE, all rows are deleted while the table structure remains.
• SELECT retrieves data; DISTINCT removes duplicate values; AS creates column aliases in the output.
• BETWEEN val1 AND val2 is inclusive of both boundary values and is equivalent to >= val1 AND <= val2.
• IN (list) is a concise alternative to multiple OR conditions on the same column.
• IS NULL and IS NOT NULL are the only correct operators for testing absent values — = NULL always returns false.
• LIKE pattern matching uses % for any sequence of characters and _ for exactly one character.