Just enough SQL

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.

The examples are SQLite-flavored (what bots use), but every statement here is standard SQL that runs unchanged on MySQL and PostgreSQL too.

A table to play with

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 — add rows

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 — read rows

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 — change rows

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 UPDATE or DELETE with no WHERE hits every row in the table. UPDATE users SET xp = 0; zeroes everyone. Always write the WHERE first, and take a backup before a bulk change on real data.

DELETE — remove rows

DELETE FROM users WHERE user_id = '222';

Bob is gone. Same warning as above: without WHERE, DELETE FROM users; empties the whole table.

Counting and totals — aggregates

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;

JOIN — combine two tables

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;

Never paste user input into SQL

⚠️ 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.

Cheat sheet

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.

Next steps

• Designing tables for your bot
• Evolving schemas without losing data
• phpMyAdmin: run SQL in your browser
• Connect your app to a database