SQL fundamentals and your first queries

SQL fundamentals: the four commands

Every day-to-day database interaction is one of four commands, sometimes called CRUD: SELECT to read, INSERT to create, UPDATE to change, DELETE to remove. Run these directly in HeidiSQL (the standard GUI that ships alongside MariaDB) or the mysql command line to get comfortable with the syntax before you wire any of it into Lua. The examples below assume a players table with name, money, and job columns.

SELECT: read data

-- Get all players
SELECT * FROM players;

-- Get only the name and money columns
SELECT name, money FROM players;

-- Get players with money over 5000
SELECT name, money FROM players WHERE money > 5000;

-- Get the top 5 richest players
SELECT name, money FROM players ORDER BY money DESC LIMIT 5;

* means "all columns", WHERE filters rows, ORDER BY sorts (DESC is highest first), and LIMIT caps the number of results.

INSERT: create new data

-- Add a new player
INSERT INTO players (name, money, job)
VALUES ('NewPlayer', 500, 'unemployed');

-- Add multiple rows at once
INSERT INTO items (name, label, weight)
VALUES
    ('bread', 'Bread', 1),
    ('water', 'Water Bottle', 1),
    ('medkit', 'Medkit', 2);

Column names go in parentheses, values in the same order, and string values need single quotes.

UPDATE: change existing data

-- Give a player more money
UPDATE players SET money = 1000 WHERE name = 'NewPlayer';

-- Give everyone 100 bonus (be careful with no WHERE!)
UPDATE players SET money = money + 100;

Always use WHERE unless you mean to update every row. Without it, UPDATE players SET money = 0 wipes everyone's money.

DELETE: remove data

-- Remove a specific player
DELETE FROM players WHERE name = 'NewPlayer';

-- Remove all players with 0 money
DELETE FROM players WHERE money = 0;

Never run DELETE without WHERE unless you want to empty the entire table. There is no undo; back up before you run one, and test the same condition as a SELECT first so you know exactly which rows it will hit.

Try the SELECT ... WHERE pattern yourself against a small sample table: