INSERT, UPDATE and DELETE

Change data safely, with transactions and injection in mind.

INSERT INTO users (name, country, age) VALUES ('Eli', 'US', 22);
UPDATE users SET age = 32 WHERE name = 'Ana';
DELETE FROM users WHERE id = 4;

CarefulUPDATE and DELETE without WHERE change every row. Run the same condition in a SELECT first and check the result.

Transactions

A transaction groups statements so they all succeed or none do.

BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;   -- or ROLLBACK; to cancel

SQL injection

Never build queries by pasting user input into a string. An input like x' OR '1'='1 can change the meaning of the query. Use parameterized queries (placeholders) so input is always treated as data.

-- Safe: the driver sends the value separately
SELECT * FROM users WHERE email = ?;

Keep it fast

An index on columns you filter or join on makes lookups fast, at a small cost when writing. Check slow queries with EXPLAIN.