CRUD Operations

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations you perform on data in any database. In MySQL these map directly to INSERT, SELECT, UPDATE, and DELETE statements.

INSERT — Creating Rows

Insert a single row:

INSERT INTO users (email, username)
VALUES ('alice@example.com', 'alice');

Insert multiple rows in a single statement for efficiency:

INSERT INTO users (email, username) VALUES
  ('bob@example.com',   'bob'),
  ('carol@example.com', 'carol'),
  ('dave@example.com',  'dave');

SELECT — Reading Rows

Retrieve all columns from a table:

SELECT * FROM users;

Select specific columns with a filter:

SELECT id, username, email
FROM users
WHERE created_at >= '2024-01-01'
ORDER BY username ASC
LIMIT 10;

WHERE filters rows, ORDER BY sorts results, and LIMIT restricts the number of rows returned.

Filtering with WHERE

Common operators: =, !=, >, <, >=, <=, LIKE (pattern matching with % and _ wildcards), IN, BETWEEN, IS NULL, and IS NOT NULL.

SELECT * FROM products
WHERE category IN ('electronics', 'books')
  AND price BETWEEN 10.00 AND 50.00;

UPDATE — Modifying Rows

Always include a WHERE clause when updating to avoid modifying every row in the table:

UPDATE users
SET email = 'alice_new@example.com',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

DELETE — Removing Rows

DELETE FROM users WHERE id = 5;

To remove all rows while keeping the table structure, use TRUNCATE TABLE — it is much faster than DELETE without a WHERE clause for large tables:

TRUNCATE TABLE session_logs;

REPLACE and INSERT ... ON DUPLICATE KEY UPDATE

MySQL offers INSERT ... ON DUPLICATE KEY UPDATE to perform an upsert — inserting a row if the key does not exist, or updating it if it does:

INSERT INTO page_views (page, views)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE views = views + 1;

Mastering these four operations is all you need to build functional data-driven applications.