The examples use the customers and orders tables from MySQL Databases, Tables and Data Types.
INSERT – adding rows
INSERT INTO customers (email, name) VALUES ('anna@example.com', 'Anna');
-- several rows at once (much faster than single inserts)
INSERT INTO customers (email, name) VALUES
('ben@example.com', 'Ben'),
('cleo@example.com', 'Cleo');
SELECT LAST_INSERT_ID(); -- the AUTO_INCREMENT id just createdInsert or update ("upsert")
If a row with the same PRIMARY KEY or UNIQUE value exists, update it instead:
-- MySQL 8.0.19+
INSERT INTO customers (email, name) VALUES ('anna@example.com', 'Anna B.') AS new
ON DUPLICATE KEY UPDATE name = new.name;
-- MariaDB and older MySQL
INSERT INTO customers (email, name) VALUES ('anna@example.com', 'Anna B.')
ON DUPLICATE KEY UPDATE name = VALUES(name);INSERT IGNORE silently skips rows that would cause a duplicate – handy, but it also hides other errors.
SELECT – reading rows
SELECT * FROM customers;
SELECT id, email FROM customers;
SELECT id, email FROM customers WHERE is_active = 1;Filtering with WHERE
| Condition | Example |
|---|---|
| comparison | total > 100, status <> 'cancelled' |
| several conditions | status = 'paid' AND total >= 50 |
| list | status IN ('paid', 'shipped') |
| range | created_at BETWEEN '2026-01-01' AND '2026-12-31 23:59:59' |
| pattern | email LIKE '%@example.com' (% = any text, _ = one character) |
| empty values | phone IS NULL, phone IS NOT NULL (never = NULL) |
Sorting and limiting
SELECT id, total FROM orders
WHERE status = 'paid'
ORDER BY total DESC, id
LIMIT 10; -- first 10 rows
SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 20; -- rows 21–30Calculated columns and aliases
SELECT id,
total,
ROUND(total * 0.19, 2) AS vat,
DATE(created_at) AS day,
UPPER(status) AS status_text
FROM orders;
SELECT DISTINCT status FROM orders; -- each value onceUseful functions: CONCAT(), LOWER(), UPPER(), TRIM(), COALESCE(a, b), IF(cond, a, b), NOW(), CURDATE(), DATE_FORMAT(d, '%d.%m.%Y'), DATE_ADD(d, INTERVAL 7 DAY), DATEDIFF(a, b).
UPDATE – changing rows
UPDATE orders SET status = 'shipped' WHERE id = 42;
UPDATE customers
SET name = 'Ben Smith', is_active = 0
WHERE email = 'ben@example.com';DELETE – removing rows
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2025-01-01';Avoiding disasters
An UPDATE or DELETE without WHERE changes every row in the table. Good habits:
- Run the same
WHEREas aSELECT COUNT(*)first and check the number. - Use a transaction so you can undo (see MySQL Transactions and InnoDB):
START TRANSACTION;
DELETE FROM orders WHERE status = 'cancelled';
SELECT ROW_COUNT(); -- how many rows were deleted?
ROLLBACK; -- or COMMIT if it is right- Start the client in safe-updates mode, which refuses
UPDATE/DELETEwithout a key inWHEREor aLIMIT:
mysql --safe-updates -u shop -p shopIn applications
Never build SQL by pasting user input into a string – that allows SQL injection. Use prepared statements, as shown in PHP Databases with PDO.
Put together with AI as a quick reference I keep for my own work. Entries like this are usually not reviewed in detail by a person.