Programmer’s Reference

SQL · MariaDB / MySQL

Query, change, and back up relational data with dialect details made explicit.

SQL runs inside a database client; connection and backup commands run in a terminal. Transactions require a transactional engine such as InnoDB. MariaDB and MySQL are separate products. Query examples assume a demo sites table (id, name, enabled) and checks table (id, site_id, result); schema-change examples use separate demo_sites fixtures.

19 entries · Essentials first · Reviewed October 5, 2026

Glossary: commands, keywords & terms

Select a term for its definition, syntax, example, and common mistakes.

Query keywords and terms

WHERE

Filter individual rows before grouping.

SELECT id, name FROM sites WHERE enabled = 1;

Practical useUse this construct in your own practice project.

Common mistakesUse IS NULL for null checks, not = NULL.

Source: dev.mysql.com documentation

AND / OR / NOT

Combine or negate conditions.

SELECT id, name FROM sites
WHERE enabled = 1 AND (name = 'Demo' OR name = 'Example');

Practical useUse this construct in your own practice project.

Common mistakesAND binds more tightly than OR; parentheses make intent clear.

Source: dev.mysql.com documentation

ORDER BY / ASC / DESC

Sort the result by one or more expressions.

SELECT id, name FROM sites ORDER BY name ASC, id DESC;

Practical useUse this construct in your own practice project.

Common mistakesWithout ORDER BY, returned row order is not guaranteed.

Source: dev.mysql.com documentation

LIMIT / OFFSET

Limit rows and skip an initial number of rows.

SELECT id, name FROM sites ORDER BY id LIMIT 10 OFFSET 20;

Practical useUse this construct in your own practice project.

Common mistakesUse a deterministic order; large offsets can be expensive.

Source: dev.mysql.com documentation

DISTINCT

Remove duplicate result rows.

SELECT DISTINCT result FROM checks;

Practical useUse this construct in your own practice project.

Common mistakesDISTINCT applies to the selected row, not only the first column.

Source: dev.mysql.com documentation

NULL / IS NULL / IS NOT NULL

Represent and test missing or unknown values.

SELECT id, name FROM sites WHERE name IS NOT NULL;

Practical useUse this construct in your own practice project.

Common mistakesNULL comparisons use three-valued logic; = NULL does not match nulls.

Source: dev.mysql.com documentation

CASE / WHEN / THEN / ELSE / END

Return a value selected by conditions.

SELECT name, CASE WHEN enabled = 1 THEN 'Ready' ELSE 'Disabled' END AS state
FROM sites;

Practical useUse this construct in your own practice project.

Common mistakesCASE is an expression, not a procedural goto or if statement.

Source: dev.mysql.com documentation

GROUP BY / HAVING

Group rows and filter groups using aggregate conditions.

SELECT result, COUNT(*) AS total
FROM checks GROUP BY result HAVING COUNT(*) > 1;

Practical useUse this construct in your own practice project.

Common mistakesWHERE filters rows before grouping; HAVING filters groups afterward.

Source: dev.mysql.com documentation

LIKE / % / _

Match text using percent for any sequence and underscore for one character.

SELECT id, name FROM sites WHERE name LIKE 'Demo%';

Practical useUse this construct in your own practice project.

Common mistakesCase sensitivity depends on dialect and collation; parameterize external patterns.

Source: dev.mysql.com documentation

Essentials

Connect and inspect tables

Connect to a local demo database using a password prompt. MariaDB uses mariadb; MySQL uses mysql.

mariadb -u demo_user -p demo_db
# MySQL equivalent:
mysql -u demo_user -p demo_db

Practical useConnect to a disposable database used by an application prototype.

Common mistakesDo not put a password after -p or in shell history. A client must be installed and the demo user provisioned.

Source: mariadb.com documentation (1) · dev.mysql.com documentation (2)

SELECT, WHERE, ORDER BY, LIMIT

Select named columns, filter rows, and return a predictable limited result set.

SELECT id, name
FROM sites
WHERE enabled = 1
ORDER BY name, id
LIMIT 10;

Practical useList enabled demo site records.

Common mistakesWithout ORDER BY, row order is not guaranteed. Use IS NULL rather than = NULL.

Source: dev.mysql.com documentation (1) · mariadb.com documentation (2)

SHOW TABLES and DESCRIBE

List tables in the current database and inspect a table’s columns.

SHOW TABLES;
DESCRIBE sites;

Practical useInspect a demo inventory schema.

Common mistakesThese commands are MariaDB/MySQL-specific. Select the intended database before inspection.

Source: dev.mysql.com documentation

Schema and indexes

Create a table

Create a demo table with an auto-incrementing primary key and a required name.

CREATE TABLE demo_sites (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(120) NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;

Practical useModel a disposable site inventory.

Common mistakesBOOLEAN is a synonym for TINYINT(1), not strict boolean enforcement. DDL commonly commits implicitly; use a test database.

Source: dev.mysql.com documentation (1) · dev.mysql.com documentation (2) · mariadb.com documentation (3)

Changing data

INSERT a row

Add one row by naming the columns explicitly.

⚠ Caution: Changes database contents.

INSERT INTO demo_sites (name, enabled)
VALUES ('Demo', TRUE);

Practical useAdd a fixture record to a staging database.

Common mistakesDo not concatenate external strings into SQL; use application prepared statements. Verify which database is selected.

Source: dev.mysql.com documentation

UPDATE with a preview

Preview the target rows, then change one record inside a transaction and roll back the example.

⚠ Caution: Updates rows; locks may affect other sessions until the transaction ends.

START TRANSACTION;
SELECT id, name FROM demo_sites WHERE id = 7;
UPDATE demo_sites SET enabled = FALSE WHERE id = 7;
ROLLBACK;

Practical usePractice disabling a demo inventory record.

Common mistakesOmitting WHERE updates every row. ROLLBACK needs transactional tables; use COMMIT only when the intended change is verified.

Source: dev.mysql.com documentation (1) · dev.mysql.com documentation (2)

DELETE and DROP differences

DELETE removes selected rows. This practice transaction previews its target and rolls back. DROP TABLE removes the table itself.

⚠ Caution: Deletes rows if committed. DROP/TRUNCATE can permanently remove data and cannot be treated like this rollback example.

START TRANSACTION;
SELECT * FROM demo_sites WHERE id = 7;
DELETE FROM demo_sites WHERE id = 7;
ROLLBACK;

Practical usePractice record removal only in a disposable database.

Common mistakesDELETE without WHERE removes all rows. DROP/TRUNCATE usually cause an implicit commit; do not assume ROLLBACK will recover them.

Source: dev.mysql.com documentation (1) · dev.mysql.com documentation (2)

Queries and reports

JOIN related records

Match site rows to their associated check rows using an explicit join condition.

SELECT s.name, c.result
FROM sites AS s
JOIN checks AS c ON c.site_id = s.id
WHERE c.result = 'failed';

Practical useBuild a demo report of failed site checks.

Common mistakesA missing join condition can multiply rows. INNER JOIN excludes sites with no matching check.

Source: dev.mysql.com documentation

COUNT and GROUP BY

Count checks for each result value.

SELECT result, COUNT(*) AS total
FROM checks
GROUP BY result
ORDER BY total DESC;

Practical useSummarize a staging check history.

Common mistakesEvery selected nonaggregated field should be grouped. COUNT(column) ignores NULL values.

Source: dev.mysql.com documentation

Backups and maintenance

Logical backup

Export a demo database using the correct product’s client. --single-transaction supports consistent InnoDB data during the dump.

⚠ Caution: Replaces the output file; protects neither nontransactional tables nor concurrent DDL.

mariadb-dump --single-transaction -u demo_user -p demo_db > demo-db.sql
# MySQL equivalent:
mysqldump --single-transaction -u demo_user -p demo_db > demo-mysql.sql

Practical useBack up a disposable application development database.

Common mistakesThe redirect overwrites the file; dumps may contain private data. Avoid concurrent schema changes and test restoration. Nontransactional tables need another consistency strategy.

Source: mariadb.com documentation (1) · dev.mysql.com documentation (2)

Back to top ↑