Skip to main content
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 operationSQL statement(s)Example
CreateINSERTINSERT INTO users (username, email) VALUES ('cody', 'cody@example.com');
ReadSELECTSELECT * FROM videos WHERE user_id = 1;
UpdateUPDATEUPDATE users SET email = 'new@addr.com' WHERE user_id = 1;
DeleteDELETEDELETE FROM videos WHERE video_id = 10;
Column types and rationale
ColumnTypeReason
user_id, video_idINT AUTO_INCREMENTSimple numeric PKs, efficient joins and indexing
username, titleVARCHAR(100)Fixed upper bound for user-friendly text
emailVARCHAR(255)Common practice for email length & indexes
linkVARCHAR(500)Accommodate long URLs
upload_dateDATEStores 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

Watch Video