🔄 Lesson 6: CRUD Operations

What Is CRUD?

CRUD is an acronym for the four basic operations you can perform on data:

Every web application, mobile app, and API you've ever used is performing these four operations behind the scenes. Learning them well is the foundation for everything else in SQL.

Setting Up Our Data

We'll work with the bookstore database from Lesson 4. If you need to rebuild it, run this first:

CREATE DATABASE IF NOT EXISTS bookstore
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE bookstore;

-- Drop existing tables so we start fresh
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS categories;

CREATE TABLE books (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    author      VARCHAR(100) NOT NULL,
    price       DECIMAL(8, 2) NOT NULL,
    pages       INT,
    published   DATE,
    in_stock    BOOLEAN DEFAULT TRUE
);

CREATE TABLE customers (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    city        VARCHAR(50),
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255)
);
                

CREATE — INSERT INTO

The INSERT INTO statement adds new rows to a table. There are several ways to use it.

Insert a Single Row

The most common form — specify the columns and their values:

INSERT INTO books (title, author, price, pages, published)
VALUES ('Dune', 'Frank Herbert', 12.99, 412, '1965-08-01');
                

Query OK, 1 row affected (0.01 sec)
                    

Notice what we didn't include:

• id — handled by AUTO_INCREMENT (MySQL assigns 1 automatically)
• in_stock — handled by DEFAULT TRUE

Verify It Worked

SELECT * FROM books;
                

+----+------+----------------+-------+-------+------------+----------+
| id | title| author         | price | pages | published  | in_stock |
+----+------+----------------+-------+-------+------------+----------+
|  1 | Dune | Frank Herbert  | 12.99 |   412 | 1965-08-01 |        1 |
+----+------+----------------+-------+-------+------------+----------+
                    

Insert Multiple Rows at Once

You can insert several rows in a single statement — much faster than individual inserts:

INSERT INTO books (title, author, price, pages, published) VALUES
    ('1984', 'George Orwell', 9.99, 328, '1949-06-08'),
    ('To Kill a Mockingbird', 'Harper Lee', 11.99, 281, '1960-07-11'),
    ('The Great Gatsby', 'F. Scott Fitzgerald', 8.99, 180, '1925-04-10'),
    ('Pride and Prejudice', 'Jane Austen', 7.99, 432, '1813-01-28'),
    ('The Hobbit', 'J.R.R. Tolkien', 10.99, 310, '1937-09-21');
                

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

Insert with All Columns (No Column List)

If you provide values for every column in order, you can skip the column list:

-- Works, but not recommended:
INSERT INTO books
VALUES (NULL, 'Fahrenheit 451', 'Ray Bradbury', 10.49, 158, '1953-10-19', TRUE);
                

The NULL for id tells MySQL to use AUTO_INCREMENT.

-- Always prefer this:
INSERT INTO books (title, author, price, pages, published)
VALUES ('Fahrenheit 451', 'Ray Bradbury', 10.49, 158, '1953-10-19');
                    

Now Let's Add Customers and Categories

-- Add some customers
INSERT INTO customers (first_name, last_name, email, city) VALUES
    ('Alice', 'Johnson', 'alice@email.com', 'New York'),
    ('Bob', 'Smith', 'bob@email.com', 'Chicago'),
    ('Carol', 'Williams', 'carol@email.com', 'Los Angeles'),
    ('Dave', 'Brown', 'dave@email.com', 'Houston'),
    ('Eve', 'Davis', 'eve@email.com', NULL);

-- Add some categories
INSERT INTO categories (name, description) VALUES
    ('Fiction', 'Novels, short stories, and other imaginative works'),
    ('Science Fiction', 'Stories exploring futuristic or scientific concepts'),
    ('Classic', 'Timeless literary works recognized across generations'),
    ('Fantasy', 'Stories set in imaginary worlds with magical elements'),
    ('Dystopian', 'Stories about oppressive societies and dark futures');
                

Notice that Eve has NULL for city — we don't know where she lives, and that's OK because city allows NULL.

READ — SELECT

SELECT is the most-used SQL statement by far — it retrieves data from tables. You'll write more SELECT queries than any other type of SQL.

SELECT * — All Columns

The asterisk (*) means "give me everything":

SELECT * FROM books;
                

+----+-----------------------+---------------------+-------+-------+------------+----------+
| id | title                 | author              | price | pages | published  | in_stock |
+----+-----------------------+---------------------+-------+-------+------------+----------+
|  1 | Dune                  | Frank Herbert       | 12.99 |   412 | 1965-08-01 |        1 |
|  2 | 1984                  | George Orwell       |  9.99 |   328 | 1949-06-08 |        1 |
|  3 | To Kill a Mockingbird | Harper Lee          | 11.99 |   281 | 1960-07-11 |        1 |
|  4 | The Great Gatsby      | F. Scott Fitzgerald |  8.99 |   180 | 1925-04-10 |        1 |
|  5 | Pride and Prejudice   | Jane Austen         |  7.99 |   432 | 1813-01-28 |        1 |
|  6 | The Hobbit            | J.R.R. Tolkien      | 10.99 |   310 | 1937-09-21 |        1 |
|  7 | Fahrenheit 451        | Ray Bradbury        | 10.49 |   158 | 1953-10-19 |        1 |
+----+-----------------------+---------------------+-------+-------+------------+----------+
                    

SELECT Specific Columns

Name only the columns you need:

SELECT title, author, price FROM books;
                

+-----------------------+---------------------+-------+
| title                 | author              | price |
+-----------------------+---------------------+-------+
| Dune                  | Frank Herbert       | 12.99 |
| 1984                  | George Orwell       |  9.99 |
| To Kill a Mockingbird | Harper Lee          | 11.99 |
| The Great Gatsby      | F. Scott Fitzgerald |  8.99 |
| Pride and Prejudice   | Jane Austen         |  7.99 |
| The Hobbit            | J.R.R. Tolkien      | 10.99 |
| Fahrenheit 451        | Ray Bradbury        | 10.49 |
+-----------------------+---------------------+-------+
                    

Column Aliases with AS

Rename columns in the output using AS:

SELECT
    title AS book_title,
    author AS written_by,
    price AS cost_usd
FROM books;
                

+-----------------------+---------------------+----------+
| book_title            | written_by          | cost_usd |
+-----------------------+---------------------+----------+
| Dune                  | Frank Herbert       |    12.99 |
| 1984                  | George Orwell       |     9.99 |
| ...                   | ...                 |      ... |
+-----------------------+---------------------+----------+
                    

Aliases don't change the actual column names — they only change the output labels. They're useful for making results more readable, especially in reports and joins (covered later).

SELECT with Expressions

You can do calculations and transformations right in the SELECT:

-- Calculate price with 10% tax
SELECT
    title,
    price,
    price * 1.10 AS price_with_tax
FROM books;

-- Combine first and last name
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    email
FROM customers;
                

+-----------------+-------------------+
| full_name       | email             |
+-----------------+-------------------+
| Alice Johnson   | alice@email.com   |
| Bob Smith       | bob@email.com     |
| Carol Williams  | carol@email.com   |
| Dave Brown      | dave@email.com    |
| Eve Davis       | eve@email.com     |
+-----------------+-------------------+
                    

SELECT DISTINCT — Remove Duplicates

DISTINCT returns only unique values:

-- All unique cities (removes duplicate values)
SELECT DISTINCT city FROM customers;
                

+-------------+
| city        |
+-------------+
| New York    |
| Chicago     |
| Los Angeles |
| Houston     |
| NULL        |
+-------------+
                    

Note that NULL appears in the results — Eve's unknown city. DISTINCT treats all NULLs as one group.

Quick WHERE Preview

You can filter results with WHERE — this is so important it gets its own full lesson (Lesson 7). Here's a quick taste:

-- Books under $10
SELECT title, price FROM books WHERE price < 10;

-- A specific customer
SELECT * FROM customers WHERE email = 'bob@email.com';

-- Books with more than 300 pages
SELECT title, pages FROM books WHERE pages > 300;
                

UPDATE — Modifying Data

UPDATE changes the values of existing rows. It always has three parts: which table, which columns to change, and which rows to change.

Update a Single Row

-- Change the price of "Dune"
UPDATE books
SET price = 14.99
WHERE id = 1;
                

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

Verify:

SELECT title, price FROM books WHERE id = 1;
                

+-------+-------+
| title | price |
+-------+-------+
| Dune  | 14.99 |
+-------+-------+
                    

Update Multiple Columns

Separate column assignments with commas:

-- Update Bob's city and email
UPDATE customers
SET city = 'San Francisco',
    email = 'bob.smith@newemail.com'
WHERE id = 2;
                

Update Multiple Rows

A WHERE clause that matches multiple rows will update all of them:

-- Give a 10% discount on all books over $10
UPDATE books
SET price = price * 0.90
WHERE price > 10;
                

Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0
                    

Check the "Rows matched" and "Changed" counts — they tell you exactly how many rows were affected.

Mark a Book as Out of Stock

UPDATE books
SET in_stock = FALSE
WHERE title = 'The Great Gatsby';

-- Verify
SELECT title, in_stock FROM books WHERE title = 'The Great Gatsby';
                

+------------------+----------+
| title            | in_stock |
+------------------+----------+
| The Great Gatsby |        0 |
+------------------+----------+
                    

-- ⛔ DANGER — this changes EVERY book's price!
UPDATE books SET price = 0.00;
                    

-- Step 1: Preview — which rows will change?
SELECT id, title, price FROM books WHERE price > 10;

-- Step 2: Looks right? Now update.
UPDATE books SET price = price * 0.90 WHERE price > 10;
                    

DELETE — Removing Data

DELETE removes rows from a table. Like UPDATE, the WHERE clause is critical.

Delete a Single Row

-- Delete the book "Fahrenheit 451"
DELETE FROM books WHERE id = 7;
                

Query OK, 1 row affected (0.01 sec)
                    

-- Verify it's gone
SELECT * FROM books WHERE id = 7;
                

Empty set (0.00 sec)
                    

Delete Multiple Rows

-- Delete all books under $9
DELETE FROM books WHERE price < 9;
                

Delete All Rows

Omitting WHERE deletes every row in the table:

-- ⛔ DANGER — deletes ALL customers!
DELETE FROM customers;
                

-- Step 1: Who are we about to delete?
SELECT * FROM customers WHERE city IS NULL;

-- Step 2: OK, delete those.
DELETE FROM customers WHERE city IS NULL;
                    

DELETE vs. TRUNCATE vs. DROP

Three ways to remove data, each with different effects:

Upsert Patterns

Sometimes you want to insert a row if it doesn't exist, or update it if it does. This is called an upsert (update + insert). MySQL provides two ways to do this.

INSERT ... ON DUPLICATE KEY UPDATE

If the insert would violate a UNIQUE or PRIMARY KEY constraint, it updates the existing row instead:

-- Try to insert a category that might already exist
INSERT INTO categories (name, description)
VALUES ('Fiction', 'Updated description for fiction')
ON DUPLICATE KEY UPDATE
    description = VALUES(description);
                

Since "Fiction" already exists (and name is UNIQUE), this updates its description instead of throwing an error.

REPLACE INTO

REPLACE is more aggressive — if the row exists, it deletes it first and then inserts the new one:

REPLACE INTO categories (name, description)
VALUES ('Fiction', 'Novels and imaginative prose');
                

Exercises

INSERT INTO books (title, author, price, pages, published) VALUES
    ('Brave New World', 'Aldous Huxley', 9.49, 311, '1932-01-01'),
    ('The Catcher in the Rye', 'J.D. Salinger', 8.99, 234, '1951-07-16'),
    ('Lord of the Rings', 'J.R.R. Tolkien', 15.99, 1178, '1954-07-29');

SELECT * FROM books;
                        

-- 1. Title and author only
SELECT title, author FROM books;

-- 2. Price with 8% tax
SELECT title, price, price * 1.08 AS price_with_tax FROM books;

-- 3. Unique cities
SELECT DISTINCT city FROM customers;

-- 4. Full name and email
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    email
FROM customers;
                        

-- 1. Change Alice's city
SELECT * FROM customers WHERE first_name = 'Alice'; -- Preview
UPDATE customers SET city = 'Brooklyn' WHERE id = 1;

-- 2. Increase price for long books
SELECT title, pages, price FROM books WHERE pages > 400; -- Preview
UPDATE books SET price = price + 2.00 WHERE pages > 400;

-- 3. Mark 1984 as out of stock
UPDATE books SET in_stock = FALSE WHERE title = '1984';

-- 4. Update Eve
UPDATE customers
SET city = 'Denver', email = 'eve.davis@email.com'
WHERE first_name = 'Eve' AND last_name = 'Davis';
                        

-- 1. Delete Dave
SELECT * FROM customers WHERE email = 'dave@email.com'; -- Preview
DELETE FROM customers WHERE email = 'dave@email.com';
SELECT * FROM customers; -- Verify

-- 2. Delete cheap books
SELECT title, price FROM books WHERE price < 9.00; -- Preview
DELETE FROM books WHERE price < 9.00;
SELECT * FROM books; -- Verify
                        

Summary

CRUD Quick Reference

📚 Additional Resources

• MySQL Docs — INSERT
• MySQL Docs — SELECT
• MySQL Docs — UPDATE
• MySQL Docs — DELETE
• MySQL Docs — INSERT ... ON DUPLICATE KEY UPDATE

🚀 What's Next?

You can now create, read, update, and delete data — the four pillars of database work. But so far, SELECT has been returning everything. In the next lesson, you'll learn to filter your results precisely with WHERE clauses, comparison operators, and pattern matching — the tools that let you ask your database exactly the right question.