Chapter 5: Basic Data Manipulation (DML)

This is the chapter where we finally start playing with real data! Up to now we created the structure (databases & tables). Now we will:

• Add data (INSERT)
• Read data (SELECT)
• Change data (UPDATE)
• Remove data (DELETE)
• Completely empty a table (TRUNCATE)

These are the four most important commands you will use every single day as a developer!

Make sure you’re inside your database:

And that you have the students table we created in Chapter 4. (If you don’t have it yet, quickly run the CREATE TABLE command from the previous chapter.)

1. INSERT INTO – Adding New Records (Single & Multiple Rows)

Syntax – Single Row

Syntax – Multiple Rows (Very efficient!)

Important: You must provide values for all NOT NULL columns (like full_name in our table). For AUTO_INCREMENT columns like student_id, you don’t need to give a value — MySQL will auto-assign it.

Example – Adding 4 students

After running this, you should see:

Tip: If you forget to mention a column that has DEFAULT, MySQL uses the default value. If you forget a NOT NULL column, you get an error.

Another cool way – Insert without specifying column names (only if you give values in exact order)

(Note: NULL for student_id because it’s AUTO_INCREMENT)

2. SELECT Statement – Reading / Retrieving Data (The most used command!)

Basic Syntax:

Most common – See everything:

You should see something like this:

Select specific columns:

Select with alias (change display name):

Select only active students:

(We’ll learn WHERE properly in the next chapter – for now just remember it filters rows)

3. UPDATE – Changing Existing Records

Syntax:

Very important: Always use WHERE! If you forget WHERE, all rows will be updated — very dangerous!

Example – Update phone number of Priya Sharma

Update multiple columns:

Update all students who joined before 2025-02-01:

4. DELETE – Removing Records

Syntax:

Again – always use WHERE! No WHERE = deletes everything!

Example – Delete one student:

Delete all inactive students:

Delete everything (careful!):

(This empties the table but keeps the structure)

5. TRUNCATE TABLE – Completely Empty a Table (Faster than DELETE)

Syntax:

Differences between DELETE and TRUNCATE:

Example:

After this:

• Table is empty
• student_id will start from 1 again for new inserts

That’s it for Chapter 5! 🎉

Summary – What we learned today:

• INSERT INTO → Add new rows (single or multiple)
• SELECT → Read data (* or specific columns)
• UPDATE → Change existing data (always with WHERE!)
• DELETE → Remove rows (always with WHERE!)
• TRUNCATE → Fast way to empty entire table

Homework for today (do it right now – very important!)

1. Make sure you’re in my_coaching database
2. Run these commands one by one and copy-paste the output here: