GuidesOnly show guides for your app
GuidesGuidesThe five commands that do 90% of the work — SELECT, INSERT, UPDATE, DELETE, and JOIN — with small, runnable examples you can paste into phpMyAdmin, a bot, or the sqlite tool.
SQL is the language every relational database speaks — MySQL, PostgreSQL, and SQLite all understand the same core. You do not need much of it to build real things: five commands cover almost everything a bot or web app does. This guide teaches those five with tiny examples you can run and see work.
| At a glance | |
|---|---|
| You need | any database to practise on — a bot's SQLite file, or a managed database |
| Where to run these | phpMyAdmin's SQL tab, a bot using better-sqlite3, or the sqlite3 tool locally |
| Time | about fifteen minutes |
The examples are SQLite-flavored (what bots use), but every statement here is standard SQL that runs unchanged on MySQL and PostgreSQL too.
Every example uses this one table. CREATE TABLE defines the columns and their types once:
CREATE TABLE users (
user_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
xp INTEGER NOT NULL DEFAULT 0
);
PRIMARY KEY makes user_id unique and fast to look up; NOT NULL DEFAULT 0 means xp is always a real number, starting at zero. (Why the shapes look the way they do: Designing tables for your bot.)
INSERT INTO users (user_id, name, xp) VALUES ('111', 'alice', 500);
INSERT INTO users (user_id, name, xp) VALUES ('222', 'bob', 150);
INSERT INTO users (user_id, name, xp) VALUES ('333', 'carol', 800);
You list the columns, then the values in the same order. That's it — three rows now exist.
SELECT is the one you'll use most. Ask for columns, filter with WHERE, sort with ORDER BY, cap with LIMIT:
SELECT name, xp FROM users
WHERE xp > 200
ORDER BY xp DESC
LIMIT 5;
Reading it left to right: the name and xp, from users, where xp is over 200, highest first, at most five rows. You get back:
carol|800
alice|500
SELECT * grabs every column; naming the ones you want is tidier. Swap > for =, <, >=, !=, or LIKE 'a%' (starts with "a") to filter differently.
UPDATE changes existing rows. The WHERE decides which ones:
UPDATE users SET xp = xp + 20 WHERE user_id = '111';
Note xp = xp + 20 — you can compute the new value from the old one. That gives alice 520.
⚠️ Heads up: An
UPDATEorDELETEwith noWHEREhits every row in the table.UPDATE users SET xp = 0;zeroes everyone. Always write theWHEREfirst, and take a backup before a bulk change on real data.
DELETE FROM users WHERE user_id = '222';
Bob is gone. Same warning as above: without WHERE, DELETE FROM users; empties the whole table.
Aggregate functions collapse many rows into one answer:
SELECT COUNT(*) AS total, AVG(xp) AS avg_xp, MAX(xp) AS top FROM users;
COUNT(*) counts rows, AVG averages a column, MAX/MIN/SUM do what they say. AS total just renames the result column. Add GROUP BY to get one answer per group — the foundation of any "per user" or "per day" report:
SELECT name, COUNT(*) AS logins
FROM login_events
GROUP BY name;
Real data lives across tables — users in one, their warnings in another. A JOIN stitches them together on a shared column:
SELECT users.name, warnings.reason
FROM warnings
JOIN users ON users.user_id = warnings.user_id;
This reads each warning and pulls in the matching user's name. A plain JOIN (inner join) returns only rows that match in both tables. A LEFT JOIN keeps every row from the first table even when there's no match — perfect for "every user and their warning count, zero included":
SELECT users.name, COUNT(warnings.id) AS warns
FROM users
LEFT JOIN warnings ON warnings.user_id = users.user_id
GROUP BY users.user_id
ORDER BY warns DESC;
⚠️ Heads up: Building a query by gluing strings together —
"... WHERE name = '" + input + "'"— is the classic SQL injection hole. A user who types'; DROP TABLE users; --can wipe your data.
The fix is placeholders: write ? (SQLite/MySQL) or $1 (PostgreSQL) where a value goes and hand the value to the driver separately. It escapes the value safely and can never be treated as commands. Every recipe on this site does this — see it wired up in Connect your app to a database and PostgreSQL from Node.
| Task | Statement |
|---|---|
| Read | SELECT cols FROM t WHERE … ORDER BY … LIMIT … |
| Add | INSERT INTO t (cols) VALUES (…) |
| Change | UPDATE t SET col = … WHERE … |
| Remove | DELETE FROM t WHERE … |
| Count / total | SELECT COUNT(*), SUM(col) FROM t GROUP BY … |
| Combine tables | SELECT … FROM a JOIN b ON a.k = b.k |
That's the 90%. Everything deeper — subqueries, transactions, window functions — builds on these. The official SQLite language reference documents the exact dialect the bot examples use; MySQL and PostgreSQL each publish their own reference for the extras they add.