SQL Crash Course

SQL crash course (for beginners)

omp-MySQL sends SQL to the database for you, but you still write the SQL. Good news: you only need a handful of commands for 95% of a gamemode. This page teaches them from scratch, with examples you can paste into MySQL Workbench or mysql.

Try these against a test database. ; ends each statement.

The four verbs

Everything is built from four commands:

Verb	Does	Plain English
SELECT	read rows	"show me…"
INSERT	add a row	"add a new…"
UPDATE	change rows	"change…"
DELETE	remove rows	"remove…"

SELECT — reading data

SELECT * FROM accounts;                       -- every column, every row
SELECT name, money FROM accounts;             -- just these columns
SELECT name, money FROM accounts WHERE id = 5;-- only matching rows

WHERE filters which rows. Combine conditions with AND / OR:

SELECT * FROM accounts WHERE money > 1000 AND banned = 0;

ORDER BY sorts; LIMIT caps how many:

SELECT name, score FROM accounts ORDER BY score DESC LIMIT 10;  -- top 10

DESC = high→low, ASC (default) = low→high.

Counting & math (let the DB do it, don't pull every row):

SELECT COUNT(*) AS total FROM accounts;            -- how many accounts
SELECT SUM(money) AS bank FROM accounts;           -- total money
SELECT AVG(score) AS avg_score FROM accounts;      -- average

AS something gives the result a name you read back with mysql_rs_get_*_by(0, "something", ...).

INSERT — adding a row

INSERT INTO accounts (name, money) VALUES ('Carol', 0);

List the columns, then the matching values. Columns you skip use their DEFAULT (e.g. id auto-fills, money might default to 0).

UPDATE — changing rows

UPDATE accounts SET money = 500 WHERE id = 5;       -- set money to 500
UPDATE accounts SET money = money + 100 WHERE id = 5;-- add 100

⚠️ Always include WHERE on UPDATE/DELETE. Without it you change/delete every row:

UPDATE accounts SET money = 0;   -- DISASTER: everyone broke

DELETE — removing rows

DELETE FROM accounts WHERE id = 5;     -- remove one account

(Again: no WHERE = deletes the whole table's rows.)

Creating a table

CREATE TABLE IF NOT EXISTS accounts (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(24) NOT NULL UNIQUE,
    money INT NOT NULL DEFAULT 0
) CHARACTER SET utf8mb4;

See Designing your tables for what each part means.

A few more you'll meet

• LIKE — pattern match: WHERE name LIKE 'Bob%' (names starting with "Bob").
• IN — match a list: WHERE id IN (1, 2, 3).
• NOW() — current time: INSERT ... VALUES (..., NOW()).
• JOIN — combine two tables (e.g. accounts + their vehicles):

  SELECT a.name, v.model
  FROM accounts a
  JOIN vehicles v ON v.account_id = a.id;

  (You won't need JOINs early on — learn them when you have related tables.)

Using these from Pawn

You pass the SQL string to mysql_execute, and read results in the callback:

mysql_execute(g_DB, "SELECT money FROM accounts WHERE id = 5", "OnMoney");

For anything with player input, don't paste it into the string — use prepared statements or %e (see First queries).

Practice ideas

1. Create an accounts table.
2. INSERT three players.
3. SELECT them ordered by money.
4. UPDATE one player's money.
5. SELECT COUNT(*).
6. DELETE one player.

Once these feel natural, you can build almost anything. Next: First queries (doing this from Pawn) · Cookbook.