MENU


SQL Essentials and Basic CRUD

Most of what an application does to its data is CRUD: create, read, update and delete, performed by INSERT, SELECT, UPDATE and DELETE. The examples run in the mysql client (Section 3.1.9) against MySQL 9.7 with default settings, on the bookshop database used throughout the chapter. Watch the status lines: they tell you how many rows each statement really touched. Section 3.4 covers table definitions and Section 3.5 queries and joins in full.

3.2.1 Creating a Database and a First Table

A server holds many databases (also called schemas), each holding tables. Create one, make it the session default with USE, and add a table:

CREATE DATABASE IF NOT EXISTS shop

CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE shop;

CREATE TABLE authors (

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

country CHAR(2) NOT NULL,

born YEAR NULL

);

Listing 3.14: Creating the shop database and an authors table

Query OK, 1 row affected (0.039 sec)

Database changed

Query OK, 0 rows affected (0.036 sec)

Output of Listing 3.14 Rerun the script and IF NOT EXISTS turns the failure into note 1007 ("database exists"). The other bookshop tables come from the chapter's script: mysql shop < sample-db.sql. Statements end with ;; comments are -- text, # or /* */; strings take single quotes. Keywords are case-insensitive, but table names map to files, so on Linux Authors and authors are different tables. Use lowercase names.

3.2.2 INSERT, Multi-Row Inserts, and INSERT ... SELECT

INSERT adds rows. Columns you leave out take their default: NULL, a DEFAULT clause, or the next AUTO_INCREMENT number.

INSERT INTO authors (name, country, born) VALUES ('Rasmus Lerdorf', 'CA', 1968);

INSERT INTO authors (name, country, born) VALUES

('Michael Widenius', 'FI', 1962), ('David Axmark', 'SE', 1962),

('Taylor Otwell', 'US', NULL);

INSERT INTO authors SET name = 'Linus Torvalds', country = 'FI';

INSERT INTO authors VALUES ('Tim Berners-Lee', 'GB', 1955); -- no column list

INSERT INTO customers (email, name, country) VALUES ('ana@example.com', 'Ana S.', 'BR');

CREATE TABLE orders_archive LIKE orders;

INSERT INTO orders_archive SELECT * FROM orders WHERE status = 'cancelled';

Listing 3.15: Four ways to insert, and two mistakes

Query OK, 1 row affected (0.007 sec)

Query OK, 3 rows affected (0.005 sec)

Records: 3 Duplicates: 0 Warnings: 0

Query OK, 1 row affected (0.005 sec)

ERROR 1136 (21S01): Column count doesn't match value count at row 1

ERROR 1062 (23000): Duplicate entry 'ana@example.com' for key 'customers.email'

Query OK, 0 rows affected (0.053 sec)

Query OK, 1 row affected (0.014 sec)

Records: 1 Duplicates: 0 Warnings: 0

Output of Listing 3.15 A multi-row insert is parsed and committed once, so use it for bulk loads. Afterward, LAST_INSERT_ID() returns 2, the first new id. It is per connection, which is why PHP can safely read back a new order's id (Chapter 4).Omitting the column list (error 1136) demands every column in table order, and when the counts happen to match, values land silently in the wrong columns. Error 1062 is the UNIQUE index on email at work. INSERT IGNORE would skip that row, but it downgrades every error in the statement to a warning. The last statement copies the cancelled order into a clone made with CREATE TABLE ... LIKE; the SELECT may filter, join and compute.

3.2.3 Reading Rows with SELECT

SELECT lists columns or expressions, FROM names the table, and WHERE keeps rows whose condition is true. AS names a computed column.

SELECT sku, price * stock AS stock_value FROM products WHERE title = 'indexing DEEP dive';

SELECT id, body = NULL AS eq_null, body IS NULL AS is_null FROM reviews WHERE id IN (4, 5);

Listing 3.16: A computed column, a case-insensitive match, and NULL

+-----------+-------------+

| sku | stock_value |

+-----------+-------------+

| BK-SQL-02 | 0.00 |

+-----------+-------------+

1 row in set (0.000 sec)

+----+---------+---------+

| id | eq_null | is_null |

+----+---------+---------+

| 4 | NULL | 0 |

| 5 | NULL | 1 |

+----+---------+---------+

2 rows in set (0.000 sec)

Output of Listing 3.16 The title matched despite the scrambled case: the default collation utf8mb4_0900_ai_ci ignores accents and case. That suits titles and emails; give tokens and codes a _bin collation (Section 3.3). The second query is the trap everyone meets. NULL means unknown, so body = NULL is unknown even for review 5, which has no body, and WHERE body = NULL returns an empty set. Use IS NULL and IS NOT NULL. In application code, name columns instead of SELECT *, so a new column cannot break your PHP.

3.2.4 UPDATE and the Safe-Updates Guard

UPDATE changes every row its WHERE matches, and without a WHERE, every row in the table. Assignments may use the current value, as in this 10% rise for the Databases category:

UPDATE products SET price = price * 1.10 WHERE category_id = 3;

UPDATE products SET price = 48.95 WHERE sku = 'BK-SQL-01'; -- already 48.95

SET SESSION sql_safe_updates = 1;

UPDATE products SET stock = stock + 10 WHERE stock < 10;

UPDATE products SET stock = stock + 10 WHERE stock < 10 ORDER BY stock LIMIT 2;

Listing 3.17: Matched versus changed, and safe-updates mode

Query OK, 2 rows affected (0.009 sec)

Rows matched: 2 Changed: 2 Warnings: 0

Query OK, 0 rows affected (0.000 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Query OK, 0 rows affected (0.000 sec)

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a

WHERE that uses a KEY column.

Query OK, 2 rows affected (0.008 sec)

Rows matched: 2 Changed: 2 Warnings: 0

Output of Listing 3.17 "Rows affected" counts rows that actually changed, not rows matched, and PDO reports the same number, so never read 0 as "no such row". Safe-updates mode rejects an UPDATE or DELETE that has no LIMIT and no key column in its WHERE. Start the client with mysql --safe-updates (or put safe-updates under [mysql] in your option file) and it also caps unbounded SELECTs at 1,000 rows. Applications rely on transactions (Section 3.14) instead.

3.2.5 DELETE, TRUNCATE, and REPLACE

DELETE removes the rows its WHERE matches, or all of them. TRUNCATE TABLE empties a table by re-creating it. REPLACE inserts a row after deleting any row with the same primary or unique key.

DELETE FROM reviews WHERE id = 7;

DELETE FROM customers WHERE id = 1; -- Ana has orders

TRUNCATE TABLE orders_archive; -- holds one row

TRUNCATE TABLE products;

REPLACE INTO authors (id, name, country) VALUES (1, 'Rasmus Lerdorf', 'GL');

SELECT id, name, born FROM authors WHERE id = 1;

Listing 3.18: Deleting rows, and the foreign keys that refuse

Query OK, 1 row affected (0.007 sec)

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

(`shop`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES

`customers` (`id`))

Query OK, 0 rows affected (0.076 sec)

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

(`shop`.`order_items`, CONSTRAINT `order_items_ibfk_2`)

Query OK, 2 rows affected (0.006 sec)

+----+----------------+------+

| id | name | born |

+----+----------------+------+

| 1 | Rasmus Lerdorf | NULL |

+----+----------------+------+

1 row in set (0.000 sec)

Output of Listing 3.18 The foreign keys protect the order history (Section 3.4.4 covers ON DELETE CASCADE). TRUNCATE reports 0 rows whatever the table held. Property DELETE TRUNCATE TABLE WHERE clause Yes No, whole table Rollback Inside a transaction No, implicit commit AUTO_INCREMENT Keeps counting Resets Delete triggers Fire Do not fire Table 3.3: DELETE versus TRUNCATE TABLE on InnoDB REPLACE reported 2 rows, one deleted and one inserted, and Rasmus lost his birth year because born was not in the statement. It is a delete plus an insert, so it also fires delete triggers and trips foreign keys. Change existing rows with UPDATE or an upsert.

3.2.6 Upserts with ON DUPLICATE KEY UPDATE

An upsert inserts a row or, if the primary key or a UNIQUE index already holds its value, updates the existing row. A stock delivery keyed on the unique sku is the classic case:

INSERT INTO products (sku, title, category_id, price, stock)

VALUES ('BK-SQL-01', 'SQL Queries That Scale', 3, 44.50, 20),

('BK-PHP-02', 'PHP 8.5 Pocket Guide', 2, 19.90, 30) AS new

ON DUPLICATE KEY UPDATE stock = products.stock + new.stock, price = new.price;

INSERT INTO products (sku, title, category_id, price, stock)

VALUES ('BK-PHP-03', 'Testing with Pest', 2, 27.00, 15);

SELECT id, sku, price, stock FROM products WHERE id > 8 OR sku = 'BK-SQL-01';

Listing 3.19: A stock delivery as an upsert

Query OK, 3 rows affected (0.015 sec)

Records: 2 Duplicates: 1 Warnings: 0

Query OK, 1 row affected (0.008 sec)

+----+-----------+-------+-------+

| id | sku | price | stock |

+----+-----------+-------+-------+

| 2 | BK-SQL-01 | 44.50 | 32 |

| 9 | BK-PHP-02 | 19.90 | 30 |

| 11 | BK-PHP-03 | 27.00 | 15 |

+----+-----------+-------+-------+

3 rows in set (0.001 sec)

Output of Listing 3.19 Each row counts 1 if inserted, 2 if updated and 0 if nothing changed, so 3 is one update (stock 12 to 32) plus one insert. AS new is a row alias (MySQL 8.0.19+): new.stock is the incoming value, products.stock the stored one. The older VALUES(stock) spelling is deprecated since 8.0.20 and now draws warning 1287.Note the ids: the upsert reserved an AUTO_INCREMENT value for the row that became an update, so the next insert got 11, not 10. InnoDB never reuses such values; never treat ids as a gapless sequence.

3.2.7 Sorting and Paging with ORDER BY and LIMIT

Tables have no inherent order. ORDER BY sorts on columns or expressions, ASC by default or DESC; LIMIT n OFFSET m (or MySQL's LIMIT m, n) skips m rows and returns n.

SELECT id, sku, category_id FROM products ORDER BY category_id LIMIT 3;

SELECT id, sku, category_id FROM products ORDER BY category_id, id LIMIT 3 OFFSET 3;

Listing 3.20: Ties, then a tie-breaker for page 2

+----+-----------+-------------+

| id | sku | category_id |

+----+-----------+-------------+

| 1 | BK-PHP-01 | 2 |

| 11 | BK-PHP-03 | 2 |

| 6 | BK-LNX-01 | 2 |

+----+-----------+-------------+

3 rows in set (0.000 sec)

+----+-----------+-------------+

| id | sku | category_id |

+----+-----------+-------------+

| 9 | BK-PHP-02 | 2 |

| 11 | BK-PHP-03 | 2 |

| 2 | BK-SQL-01 | 3 |

+----+-----------+-------------+

3 rows in set (0.000 sec)

Output of Listing 3.20 Five products share category 2, and the first query returned three in arbitrary order. Tied rows may come back in any order, even differently with and without LIMIT, so pages can repeat or skip rows. End every paging sort with a unique column, as the second query does. NULL sorts first in ascending order.OFFSET still reads every skipped row, so deep pages slow down. Keyset pagination remembers the last row's sort key instead: WHERE (category_id, id) > (2, 11) ORDER BY category_id, id LIMIT 3 returns the next page directly, and with an index every page costs the same (Section 3.12.10).

3.2.8 SQL Modes and Strict Mode

sql_mode sets how forgiving the server is. MySQL 9.7 defaults to six modes, among them STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY (Section 3.5.4) and the zero-date and division-by-zero checks. Strict mode rejects invalid, oversized or missing values instead of adjusting them with a warning:

INSERT INTO customers (email, name, country) VALUES ('lena@example.com', 'Lena Gruber', 'AUT');

INSERT INTO customers (email, country) VALUES ('jo@example.com', 'NZ');

INSERT INTO orders (customer_id, ordered_at) VALUES (2, '2026-02-30 10:00:00');

SET SESSION sql_mode = '';

INSERT INTO customers (email, name, country) VALUES ('lena@example.com', 'Lena Gruber', 'AUT');

SELECT name, country FROM customers WHERE email = 'lena@example.com';

Listing 3.21: Bad values in strict mode, then with sql_mode cleared

ERROR 1406 (22001): Data too long for column 'country' at row 1

ERROR 1364 (HY000): Field 'name' doesn't have a default value

ERROR 1292 (22007): Incorrect datetime value: '2026-02-30 10:00:00' for column 'ordered_at' at

row 1

Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected, 1 warning (0.013 sec)

+-------------+---------+

| name | country |

+-------------+---------+

| Lena Gruber | AU |

+-------------+---------+

1 row in set (0.000 sec)

Output of Listing 3.21 Without strict mode, Austria's AUT was cut to AU, which is Australia, with only a warning. The other two inserts also succeed in a lax session: Jo's name becomes an empty string, and 30 February is stored as 0000-00-00 00:00:00 (warning 1264). Nothing fails, so nobody notices until a report is wrong.Keep the defaults. Relax a mode only per session, for a legacy import, and restore it with SET SESSION sql_mode = DEFAULT. The zero-date and division modes are deprecated but still on. Laravel sets strict mode on its connections ('strict' => true, Chapter 5).