CRUD Commands in Action

Earlier we designed an Entity Relationship Diagram (ERD) for Cody’s cat video club. We split her messy spreadsheet into two normalized tables—users and videos—and added primary keys, foreign keys, data types and relationships. Now we’ll convert that spreadsheet into a relational database using MySQL. In this lesson you’ll learn what CRUD means, create a new database, and run basic SQL to Create, Read, Update and Delete records. What is CRUD?

CRUD stands for Create, Read, Update, Delete. These are the four fundamental operations any application or service performs on persistent data.
SQL (Structured Query Language) is the standard language you use to perform CRUD operations in a relational DBMS like MySQL.

The image is about SQL (Structured Query Language), showing a database graphic with glasses and a question mark, highlighting tasks like adding data, finding data, fixing mistakes, and clearing old entries, alongside a person from KodeKloud explaining it.

Getting started with MySQL

We’ll use MySQL in the KodeKloud Playground (or your local MySQL instance). The playground provides login details and an interactive MySQL shell.
If you need help in the MySQL client, use the built-in HELP command or consult the official MySQL documentation.

Example: connect and run a simple SELECT in the MySQL client

mysql> SELECT * FROM my_table;
+----+------------+-----------+-------------------------+
| id | first_name | last_name | email                   |
+----+------------+-----------+-------------------------+
|  1 | John       | Smith     | johnsmith@newemail.com  |
+----+------------+-----------+-------------------------+
1 row in set (0.00 sec)

# connect as user 'bob'
mysql -u bob -p

Create and select a database

A database in MySQL is a logical namespace that holds tables and other objects. Create a database, list available databases, and switch to the new one:

CREATE DATABASE miaowtube;
SHOW DATABASES;
USE miaowtube;
SELECT DATABASE();

CREATE DATABASE miaowtube; creates an empty database named miaowtube.
SHOW DATABASES; lists all databases on the server.
USE miaowtube; selects the database for subsequent commands.
SELECT DATABASE(); confirms the current database.

Why create users first?

The videos table references users.user_id with a foreign key. MySQL requires the referenced table to exist when adding a foreign key (unless both are created in a single statement where supported). Creating users first avoids needing an extra ALTER TABLE later.

The image shows an entity-relationship diagram with tables for "Videos" and "Users," illustrating database structure alongside a person presenting.

Table design decisions (from the ERD)

Use INT AUTO_INCREMENT PRIMARY KEY for user_id and video_id so the database assigns unique IDs automatically.
VARCHAR(100) for username and title.
VARCHAR(500) for link to safely store longer URLs.
VARCHAR(255) for email (common, index-friendly choice).
DATE for upload_date (format YYYY-MM-DD).
Use InnoDB engine for foreign key support.
ON DELETE CASCADE on the foreign key ensures referential integrity by removing related videos if a user is deleted (only use this if that is the desired behavior).

Create the database and both tables

CREATE DATABASE miaowtube;
USE miaowtube;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE videos (
    video_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(100) NOT NULL,
    link VARCHAR(500) NOT NULL,
    upload_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
) ENGINE=InnoDB;

Key SQL elements explained

INT AUTO_INCREMENT PRIMARY KEY — auto-incrementing integer primary key.
VARCHAR(n) — variable-length string up to n characters.
NOT NULL — value is required in that column.
DATE — stores YYYY-MM-DD.
FOREIGN KEY (user_id) REFERENCES users(user_id) — enforces that every user_id in videos exists in users.
ON DELETE CASCADE — deletes dependent videos rows when the referenced users row is removed.
ENGINE=InnoDB — required for foreign key enforcement in MySQL.

CRUD quick-reference table

CRUD operation	SQL statement(s)	Example
Create	`INSERT`	`INSERT INTO users (username, email) VALUES ('cody', 'cody@example.com');`
Read	`SELECT`	`SELECT * FROM videos WHERE user_id = 1;`
Update	`UPDATE`	`UPDATE users SET email = 'new@addr.com' WHERE user_id = 1;`
Delete	`DELETE`	`DELETE FROM videos WHERE video_id = 10;`

Column types and rationale

Column	Type	Reason
`user_id`, `video_id`	`INT AUTO_INCREMENT`	Simple numeric PKs, efficient joins and indexing
`username`, `title`	`VARCHAR(100)`	Fixed upper bound for user-friendly text
`email`	`VARCHAR(255)`	Common practice for email length & indexes
`link`	`VARCHAR(500)`	Accommodate long URLs
`upload_date`	`DATE`	Stores date without time portion

Tips for using the MySQL client

If you make a typo while entering a long statement, press Ctrl-C to cancel the current input.
Alternatively, finish the statement with a semicolon, then use your shell history (up-arrow) to retrieve and edit previous commands.
Use DESCRIBE table_name; or SHOW CREATE TABLE table_name; to inspect table definitions.

If you need to add a foreign key after creating both tables, use ALTER TABLE to add the constraint. However, creating the referenced table first avoids extra ALTER steps.

Links and references

MySQL Documentation: https://dev.mysql.com/doc/
Kubernetes Documentation (for related deployment topics)
KodeKloud Playground (use the playground for interactive MySQL practice)

Relational Databases

Relational vs Non-Relational Databases

Querying and Indexing

Project: Build Your Own Database

Introduction

CRUD Commands in Action

Watch Video