CRUD & SQL Queries

The four things you ever do to stored data — Create, Read, Update, Delete — as INSERT, SELECT, UPDATE, DELETE.

CRUD is the four things you ever do to stored data — Create, Read, Update, Delete — written in SQL as INSERT, SELECT, UPDATE, DELETE. A table holds rows (records), each with one value in every column. A WHERE clause simply picks which rows a statement touches; leave it off and the statement hits *every* row.

The four verbs, in order

1Create → INSERT adds a new row.
2Read → SELECT returns rows, optionally filtered by WHERE.
3Update → UPDATE ... SET ... WHERE changes values in matching rows.
4Delete → DELETE ... WHERE removes matching rows.

A worked sequence on one table

-- CREATE: add two users
INSERT INTO users (id, name, city) VALUES (1, 'Ana', 'Pune');
INSERT INTO users (id, name, city) VALUES (2, 'Ben', 'Delhi');

-- READ: only the rows that pass the filter
SELECT name, city FROM users WHERE city = 'Pune';

-- UPDATE: change one row, pinned by its primary key
UPDATE users SET city = 'Mumbai' WHERE id = 1;

-- DELETE: remove matching rows
DELETE FROM users WHERE id = 2;

The WHERE clause is not optional in spirit

UPDATE users SET city = 'Mumbai' with no WHERE rewrites *every* row's city; DELETE FROM users empties the whole table. The filter is the only thing scoping the damage — forget it and the statement touches all rows.

Filter on the primary key to hit exactly one row

WHERE id = 1 targets a single row because the primary key is unique. That is why identifying rows by their key — rather than by a name that might repeat — is the safe way to update or delete.

OperationTimeSpace

Any statement (no index) · full scan of the tableO(rows)O(1)

WHERE on indexed key · jump to matching rowsO(log n)O(1)

Check yourself

What does UPDATE users SET city = 'Mumbai' do when written with no WHERE clause?