SQL CRUD Operations (MySQL)

Introduction

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations for persistent data storage. In MySQL, these map directly to INSERT, SELECT, UPDATE, and DELETE statements. This tutorial covers all of them, plus MySQL-specific variants like REPLACE, INSERT IGNORE, and ON DUPLICATE KEY UPDATE that give you finer control over how data is written and read.

Table of Contents

1. INSERT INTO — Create Rows
2. INSERT Multiple Rows
3. INSERT IGNORE — Skip Conflicts Gracefully
4. REPLACE — Insert or Delete-and-Insert
5. ON DUPLICATE KEY UPDATE — Upsert
6. SELECT — Read Data
7. SELECT DISTINCT — Unique Values Only
8. UPDATE — Modify Existing Rows
9. DELETE — Remove Rows
10. Best Practices
11. Common Mistakes
12. Quick Recap

1. INSERT INTO — Create Rows

Purpose: Add a new row (record) to a table.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

If you provide values for all columns in the exact table order, you can omit the column list:

INSERT INTO table_name
VALUES (value1, value2, ...);

Note: Omitting the column list is fragile — the order depends on the table definition. Always prefer listing columns explicitly.

Example

INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

Before:

After:

2. INSERT Multiple Rows

Purpose: Insert several rows in a single statement — faster and more efficient than separate INSERT calls.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES
    (row1_val1, row1_val2, ...),
    (row2_val1, row2_val2, ...),
    (row3_val1, row3_val2, ...);

Example

INSERT INTO users (name, email, age)
VALUES
    ('Bob', 'bob@example.com', 25),
    ('Charlie', 'charlie@example.com', 28),
    ('Diana', 'diana@example.com', 35);

Result (3 new rows):

Note: MySQL has a max_allowed_packet limit. For very large bulk inserts (hundreds of thousands of rows), batch in chunks of 500–1000 rows per statement.

3. INSERT IGNORE — Skip Conflicts Gracefully

Purpose: Insert rows but ignore any that would cause a duplicate-key error (or other warning-level errors). Rows that don't conflict are inserted normally; conflicting rows are silently skipped.

Syntax

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Example

Assume email has a UNIQUE constraint and 'alice@example.com' already exists:

INSERT IGNORE INTO users (name, email, age)
VALUES ('Eve', 'alice@example.com', 22);

Without IGNORE: ERROR 1062 (23000): Duplicate entry 'alice@example.com'

With IGNORE: No error — the row is silently skipped. MySQL reports a warning instead.

SHOW WARNINGS;
-- | Warning | 1062 | Duplicate entry 'alice@example.com' for key 'email'

Note: INSERT IGNORE also converts other non-fatal errors (e.g., data truncation, division by zero) into warnings. Use it carefully — you might hide real data issues.

4. REPLACE — Insert or Delete-and-Insert

Purpose: If a row with the same primary key or unique index already exists, delete it and insert a new row. If no conflict exists, it inserts normally.

This is a MySQL-specific extension (also available in SQLite).

Syntax

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

How It Works

1. MySQL tries to insert the row.
2. If a duplicate-key violation occurs (on PRIMARY KEY or UNIQUE index), MySQL deletes the old row, then inserts the new one.
3. This means id values (especially AUTO_INCREMENT) increment even on a replace.

Example

REPLACE INTO users (id, name, email, age)
VALUES (1, 'Alicia', 'alicia@example.com', 31);

Before:

After:

⚠️ Warning: REPLACE is a DELETE + INSERT, not an UPDATE. If your table has foreign key constraints with ON DELETE CASCADE, replacing a row can cascade-delete related rows in other tables. The AUTO_INCREMENT counter also increments.

5. ON DUPLICATE KEY UPDATE — Upsert

Purpose: Insert a row. If a duplicate-key conflict occurs, update the existing row instead of failing (or deleting it).

This is the most flexible upsert mechanism and is MySQL-specific.

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = new_value1,
    column2 = new_value2,
    ...;

You can reference both the values you tried to insert and the existing row values.

Example — Basic Upsert

INSERT INTO users (id, name, email, age)
VALUES (1, 'Alice', 'alice@newdomain.com', 31)
ON DUPLICATE KEY UPDATE
    email = VALUES(email),
    age = VALUES(age);

If id = 1 already exists, update the email and age columns.

Example — Counter (e.g., page views)

INSERT INTO page_views (page_slug, view_count)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE
    view_count = view_count + 1;

Using VALUES() (MySQL 8.0+)

VALUES(column_name) refers to the value you tried to insert for that column. In MySQL 8.0.20+, prefer the AS alias syntax:

INSERT INTO users (id, name, email, age) VALUES (1, 'Alice', 'a@b.com', 30) AS new
ON DUPLICATE KEY UPDATE
    name = new.name,
    email = new.email,
    age = new.age;

Note: This is the recommended approach over REPLACE when you want to preserve the existing row's AUTO_INCREMENT id, foreign key relationships, or only update specific columns. It performs an UPDATE, not a DELETE + INSERT.

6. SELECT — Read Data

Purpose: Retrieve (query) data from one or more tables.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• * selects all columns (use sparingly — prefer explicit columns).
• WHERE filters rows (optional — omitting it returns all rows).

Examples

-- All columns, all rows
SELECT * FROM users;

-- Specific columns with a condition
SELECT name, email FROM users WHERE age > 25;

-- Order results
SELECT name, age FROM users ORDER BY age DESC;

-- Limit results
SELECT name, email FROM users LIMIT 5;

-- Combine filters
SELECT name, email, age FROM users
WHERE age >= 18 AND age <= 65
ORDER BY name ASC
LIMIT 10;

Output:

7. SELECT DISTINCT — Unique Values Only

Purpose: Return only unique (non-duplicate) values for the selected column(s).

Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example

SELECT DISTINCT age FROM users;

If users has: (25, 30, 25, 35, 30) → Result: (25, 30, 35)

Multiple Columns — Uniqueness by Combination

SELECT DISTINCT city, state FROM customers;

Uniqueness is checked across all selected columns. ('Springfield', 'IL') and ('Springfield', 'MA') are both kept.

Note: SELECT DISTINCT scans all rows to find unique values. On large tables without an index, it can be slow. DISTINCT applies to the entire row combination — not per-column.

8. UPDATE — Modify Existing Rows

Purpose: Change the values of one or more columns in existing rows.

Syntax

UPDATE table_name
SET column1 = new_value1,
    column2 = new_value2, ...
WHERE condition;

⚠️ CRITICAL: Always use a WHERE clause unless you intend to update every row. An UPDATE without WHERE updates the whole table!

Example

UPDATE users
SET age = 31
WHERE name = 'Alice';

Before:

After:

Update Multiple Columns

UPDATE users
SET
    email = 'alice@newdomain.com',
    age = 32,
    updated_at = NOW()
WHERE id = 1;

Notes

• You can reference the current value: SET age = age + 1 increments by 1.
• You can use subqueries: SET salary = (SELECT AVG(salary) FROM employees)
• The number of affected rows is returned — rows that match the WHERE but whose values don't actually change are reported as "affected" (though ROW_COUNT() may differ with certain client settings).

9. DELETE — Remove Rows

Purpose: Delete existing rows from a table.

Syntax

DELETE FROM table_name
WHERE condition;

⚠️ CRITICAL: Always use a WHERE clause unless you intend to empty the table. DELETE FROM users (no WHERE) removes every row but keeps the table structure.

Example

DELETE FROM users
WHERE id = 5;

Before:

After:

Delete All Rows (Fast)

TRUNCATE TABLE users;     -- DDL: removes + recreates table structure
-- vs
DELETE FROM users;         -- DML: deletes row by row, fires triggers, slower

Note: TRUNCATE cannot be rolled back in most transactions and resets AUTO_INCREMENT counters. DELETE can be rolled back and preserves the counter.

Notes

• Deleting rows does not reclaim disk space automatically (use OPTIMIZE TABLE if needed).
• Foreign key constraints may prevent deletion or cascade it — know your relationship model.
• Use LIMIT to delete in batches on large tables (though MySQL's DELETE ... LIMIT may not play well with replication).

10. Best Practices