Log in Sign up

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 created

Insert 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

ConditionExample
comparisontotal > 100, status <> 'cancelled'
several conditionsstatus = 'paid' AND total >= 50
liststatus IN ('paid', 'shipped')
rangecreated_at BETWEEN '2026-01-01' AND '2026-12-31 23:59:59'
patternemail LIKE '%@example.com' (% = any text, _ = one character)
empty valuesphone 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–30

Calculated 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 once

Useful 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:

  1. Run the same WHERE as a SELECT COUNT(*) first and check the number.
  2. 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
  1. Start the client in safe-updates mode, which refuses UPDATE/DELETE without a key in WHERE or a LIMIT:
mysql --safe-updates -u shop -p shop

In 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.

Next: MySQL Joins, Grouping and Subqueries

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.