Mastering CRUD Operations: Create, Read, Update, Delete in MySQL

Create, Read, Update, and Delete. These four operations are what you do with data day to day.

CREATE

‘Create’ covers new databases, new tables, and new rows.

Creating a new database

CREATE DATABASE shop_db;

That creates a database called shop_db.

Creating a new table

USE shop_db;
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2),
    in_stock BOOLEAN
);

A products table with columns for id, name, price, and stock status.

Adding data to a table

INSERT INTO products (name, price, in_stock) VALUES ('Coffee Mug', 9.99, TRUE);

That inserts one product row.

READ

Reading data means querying it, usually with SELECT.

Fetching all records

SELECT * FROM products;

Every column, every row.

Fetching specific columns

SELECT name, price FROM products;

Just names and prices.

Conditional fetch

Use WHERE to filter. Products currently in stock:

SELECT name FROM products WHERE in_stock = TRUE;

That returns names where in_stock is true.

UPDATE

UPDATE changes existing rows. Use it carefully – a missing WHERE clause updates everything.

Updating a record

UPDATE products SET price = 10.99 WHERE name = 'Coffee Mug';

Changes the Coffee Mug price to 10.99.

Multiple updates

Update every row at once – raise all prices by 10%:

UPDATE products SET price = price * 1.1;

No WHERE clause here, so every product is affected. That is intentional in this case, but worth checking before you run it.

DELETE

DELETE removes rows. Same warning as UPDATE: without a WHERE clause, you delete the lot.

Deleting a specific record

DELETE FROM products WHERE name = 'Coffee Mug';

Removes the Coffee Mug row.

Caution with DELETE

DELETE FROM products; with no WHERE clause empties the entire table. Check twice.

Best practices for CRUD operations

1. Be specific: precise WHERE clauses on UPDATE and DELETE.
2. Test on a sample: try UPDATE and DELETE on a few rows before running against a large table.
3. Regular backups: keep backups. Mistakes happen.

CRUD covers most of what you do with a database. Run these queries against your own tables until they feel routine.