MySQL Insert, Select, Update and Delete

The four basic jobs of any app are Create, Read, Update and Delete, often called CRUD. This lesson shows how MySQL does each one.

Insert Rows

Because id is AUTO_INCREMENT, you leave it out:

INSERT INTO products (name, category, price, stock)
VALUES
  ('Basmati Rice 5kg', 'Grocery', 2450.00, 40),
  ('Cooking Oil 1L', 'Grocery', 620.00, 75),
  ('Tea 950g', 'Grocery', 1650.00, 30),
  ('USB Cable', 'Electronics', 450.00, 120),
  ('Power Bank', 'Electronics', 3800.00, 15),
  ('School Bag', 'Stationery', 2200.00, 0);

Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

To find the id MySQL just created, use LAST_INSERT_ID():

INSERT INTO orders (product_id, quantity, customer, order_date)
VALUES (1, 2, 'Ayesha', '2026-10-01');

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

Insert or Update in One Step

If a row with the same unique value already exists, ON DUPLICATE KEY UPDATE changes it instead of failing. Here new stock arrives for a product that may already be in the table:

INSERT INTO products (name, category, price, stock)
VALUES ('Tea 950g', 'Grocery', 1650.00, 20)
ON DUPLICATE KEY UPDATE stock = stock + 20;

Because name is UNIQUE, MySQL finds the existing tea row and raises its stock from 30 to 50.

Read Rows

All the SQL you learned in the SQL tutorial works here. Products under Rs 2,000:

SELECT name, price
FROM products
WHERE price < 2000
ORDER BY price;

+----------------+---------+
| name           | price   |
+----------------+---------+
| USB Cable      |  450.00 |
| Cooking Oil 1L |  620.00 |
| Tea 950g       | 1650.00 |
+----------------+---------+
3 rows in set (0.00 sec)

LIMIT and OFFSET for Pages

Websites show long lists in pages. LIMIT sets how many rows, and OFFSET how many to skip first:

SELECT id, name
FROM products
ORDER BY id
LIMIT 2 OFFSET 2;

+----+-----------+
| id | name      |
+----+-----------+
|  3 | Tea 950g  |
|  4 | USB Cable |
+----+-----------+
2 rows in set (0.00 sec)

This is "page 2" when each page shows 2 products. MySQL also accepts the short form LIMIT 2, 2 (offset first, then count).

Update Rows

Ali's order of USB cables has sold some stock:

UPDATE products
SET stock = stock - 3
WHERE id = 4;

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

A sale on all electronics, 10% off:

UPDATE products
SET price = price * 0.90
WHERE category = 'Electronics';

Delete Rows

Remove products that are out of stock:

DELETE FROM products
WHERE stock = 0;

Query OK, 1 row affected (0.00 sec)

MySQL also lets you add LIMIT to DELETE and UPDATE, so at most that many rows change: DELETE FROM orders WHERE customer = 'Test' LIMIT 1;

Safe Updates Mode

MySQL Workbench turns on safe updates by default. It blocks an UPDATE or DELETE that has no WHERE on a key column, so you cannot wipe a whole table by mistake:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

CRUD Summary

Exercise