Querying rows_

Four statements do almost all the work in a relational database:

• SELECT reads rows
• INSERT adds new rows
• UPDATE changes existing rows
• DELETE removes rows

SQL is declarative: a query describes the result you want, and the database's optimizer decides how to produce it, choosing between indexes, scans, and join strategies on its own. That's why the same query keeps working as data grows and indexes change.

This page walks through each statement, plus the querying patterns you reach for daily: filtering, aggregation, pagination, and upserts.

Setup

The examples below work against a small product catalog. Create and seed it first:

CREATE TABLE products (
    id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

INSERT INTO products (name, price, stock) VALUES
    ('Keyboard', 79.00, 120),
    ('Mouse', 29.50, 200),
    ('Monitor', 349.99, 14),
    ('Webcam', 59.00, 0),
    ('Desk mat', 19.90, 45);

Read rows

A SELECT statement has three jobs, each handled by its own clause: choose which columns to return, decide which rows qualify, and put them in order:

SELECT name, price
FROM products
WHERE stock > 0
ORDER BY price DESC;

name	price
Monitor	349.99
Keyboard	79.00
Mouse	29.50
Desk mat	19.90

Four of the five products come back: the Webcam is filtered out because its stock is 0, and the rest arrive sorted by price, highest first. The other columns still exist on those rows; the query just didn't ask for them. Without an ORDER BY, row order is arbitrary and can change between runs, so always order results the user will see.

WHERE accepts any boolean expression, built from a handful of operators:

• Comparisons (=, <, >, <=, >=, <>) work on numbers, text, and dates alike.
• BETWEEN matches a range, bounds included.
• LIKE matches string patterns, where % stands for any sequence of characters; under the default utf8mb4 collation it compares case-insensitively, so '%mo%' matches both Mouse and Monitor.
• IN matches any value in a list.
• AND and OR combine conditions, with parentheses to group them.

SELECT name FROM products WHERE price BETWEEN 20 AND 100;   -- range
SELECT name FROM products WHERE name LIKE '%mo%';           -- pattern match
SELECT name FROM products WHERE stock IN (0, 14);           -- membership
SELECT name FROM products WHERE stock = 0 OR price < 25;    -- combined conditions

Avoid SELECT * in application code. Naming columns keeps results stable when the table gains columns later and avoids transferring data you don't use.

Insert rows

INSERT names the columns it provides and gives a value for each; omitted columns fall back to their defaults, like the auto-generated id here:

INSERT INTO products (name, price, stock)
VALUES ('USB hub', 24.00, 80);

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() returns the AUTO_INCREMENT value generated by the most recent insert on this connection. It is connection-scoped, so concurrent clients never see each other's IDs. Most drivers surface it directly on the statement result, no extra query needed.

Update rows

UPDATE changes the columns you name on every row matching the WHERE clause:

UPDATE products
SET price = price * 0.90
WHERE stock > 100;

The SET expression can reference the row's current values, as the discount above does. Without a WHERE clause, UPDATE rewrites the entire table, so check the target set first with a SELECT using the same WHERE when running one by hand.

Delete rows

DELETE removes every row matching the WHERE clause:

DELETE FROM products
WHERE stock = 0;

Without a WHERE clause, DELETE FROM products removes every row in the table. For hand-run maintenance, wrap the statement in a transaction so you can inspect the result and roll it back. See Transactions.

Aggregate

An aggregate function computes a single value over a set of rows, such as a count of orders or a total spend per customer:

• COUNT(*) counts rows
• SUM and AVG total and average a numeric column
• MIN and MAX find the extremes

The demonstration needs a table where several rows belong to each customer:

CREATE TABLE orders (
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer VARCHAR(100) NOT NULL,
    total    DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (customer, total) VALUES
    ('ada', 120.00), ('ada', 80.00), ('grace', 45.50),
    ('grace', 30.00), ('grace', 25.00), ('linus', 999.99);

By default an aggregate covers every row in the table: SELECT SUM(total) FROM orders returns one number, the total across all six orders. GROUP BY customer changes that. The database first gathers rows with the same customer value together, Ada's two orders, Grace's three, Linus's one, then runs the aggregate functions once for each of those groups. The result has one row per customer instead of one row for the whole table:

SELECT customer,
       COUNT(*)   AS orders,
       SUM(total) AS lifetime_value
FROM orders
GROUP BY customer
HAVING SUM(total) > 100
ORDER BY lifetime_value DESC;

customer	orders	lifetime_value
linus	1	999.99
ada	2	200.00
grace	3	100.50

Six order rows became three result rows, one per customer:

• Ada's two orders were summed into 200.00
• Grace's three orders were summed into 100.50
• Linus's single order stands alone at 999.99

In a grouped query, every selected column must be either grouped on (like customer) or aggregated (like SUM(total)), because each result row now represents many source rows.

WHERE and HAVING both filter, but at different stages:

• WHERE filters individual rows, before grouping. It cannot use aggregate results, because they haven't been computed yet.
• HAVING filters the groups, after the aggregates are computed.

Here HAVING SUM(total) > 100 keeps only customers whose orders total more than 100. Grace passes at 100.50; a threshold of 150 would exclude her.

Paginate

Pagination splits a large result into pages. There are two ways to do it:

• Offset pagination asks for "skip the first 20 rows, return the next 10". Simple, but the database still reads and discards every skipped row, so deep pages get slower and slower.
• Keyset pagination asks for "return the 10 rows after the last one I saw". The database jumps straight to that position, so every page costs the same as the first.

LIMIT and OFFSET are the offset approach:

SELECT id, name FROM products ORDER BY id LIMIT 2 OFFSET 2;

Keyset pagination filters on the last value seen instead:

SELECT id, name
FROM products
WHERE id > 2          -- last id from the previous page
ORDER BY id
LIMIT 2;

Keyset pagination stays fast at any depth because the index seeks straight to the boundary, provided the ordering columns are indexed. It requires a deterministic, unique ordering (a single column like id, or a composite) and can't jump to an arbitrary page number.

Upsert

An upsert writes a row without knowing whether it already exists: insert it if it's new, update it if it's not. Stock counts, settings, and sync jobs all need this.

The obvious approach, SELECT to check and then INSERT or UPDATE accordingly, has a race condition: another connection can insert the same key between your check and your write, and your insert fails.

INSERT ... ON DUPLICATE KEY UPDATE performs the check and the write as one atomic statement, so the race cannot happen:

CREATE TABLE inventory (
    sku      VARCHAR(20) PRIMARY KEY,
    quantity INT NOT NULL
);

INSERT INTO inventory (sku, quantity) VALUES ('KB-01', 10);

-- Second write for the same key updates instead of failing
INSERT INTO inventory (sku, quantity) VALUES ('KB-01', 5) AS new
ON DUPLICATE KEY UPDATE quantity = inventory.quantity + new.quantity;

SELECT * FROM inventory;

sku	quantity
KB-01	15

What happened:

• The first insert creates KB-01 with quantity 10.
• The second insert collides with it on the primary key, so instead of failing, it runs the ON DUPLICATE KEY UPDATE clause: existing 10 plus incoming 5 gives 15.
• The AS new alias names the row that would have been inserted, which is how the update reaches the incoming values.

To silently ignore duplicates instead, use INSERT IGNORE, but note it downgrades other errors to warnings too, so prefer ON DUPLICATE KEY UPDATE when in doubt.

Subqueries and CTEs

A common table expression (CTE) names an intermediate result so a complex query reads top to bottom:

WITH big_spenders AS (
    SELECT customer
    FROM orders
    GROUP BY customer
    HAVING SUM(total) > 150
)
SELECT customer FROM big_spenders ORDER BY customer;

CTEs and subqueries are interchangeable in most positions; prefer whichever keeps the query readable. MySQL also supports WITH RECURSIVE for hierarchical data such as category trees.