Learn MySQL Data Manipulation: INSERT, SELECT, UPDATE, DELETE Operations

Introduction

In this lab, we will explore the fundamental data manipulation operations in MySQL. You will learn how to insert, select, update, and delete data in MySQL tables. These operations, commonly known as CRUD (Create, Read, Update, Delete), form the backbone of database interactions. Through hands-on exercises, you'll gain practical experience with these essential database operations and understand how to effectively manage data in MySQL tables.

Basic Data Insertion

In this step, we'll learn how to insert data into MySQL tables using the INSERT statement. We'll start with simple single-row insertions and then move on to multiple-row insertions.

First, let's connect to MySQL:

sudo mysql -u root

Once connected, select the store database:

USE store;

Single Row Insertion

Let's start by inserting a single product into our products table:

INSERT INTO products (name, price, description)
VALUES ('Coffee Maker', 49.99, 'A 12-cup drip coffee maker with programmable timer');

Let's break down this command:

INSERT INTO products: Specifies the table we're inserting into
(name, price, description): Lists the columns we're providing values for
VALUES (...): Specifies the actual values to insert

Note that we didn't include:

id: It's auto-incrementing, so MySQL handles it automatically
created_at: It has a default value of the current timestamp

To verify our insertion, we can select the data:

SELECT * FROM products;

You should see output like this:

+----+--------------+--------+----------------------------------------------------+---------------------+
| id | name         | price  | description                                        | created_at          |
+----+--------------+--------+----------------------------------------------------+---------------------+
|  1 | Coffee Maker |  49.99 | A 12-cup drip coffee maker with programmable timer | 2024-11-07 09:12:06 |
+----+--------------+--------+----------------------------------------------------+---------------------+

Multiple Row Insertion

Now, let's insert multiple products at once. This is more efficient than inserting rows one at a time:

INSERT INTO products (name, price, description) VALUES
    ('Toaster', 29.99, '2-slice toaster with multiple browning settings'),
    ('Blender', 79.99, 'High-speed blender for smoothies and soups'),
    ('Microwave', 129.99, '1000-watt microwave with digital controls');

This command follows the same structure as single-row insertion but includes multiple value sets separated by commas.

Let's verify our insertions:

SELECT * FROM products;

You should see output like this:

+----+--------------+--------+----------------------------------------------------+---------------------+
| id | name         | price  | description                                        | created_at          |
+----+--------------+--------+----------------------------------------------------+---------------------+
|  1 | Coffee Maker |  49.99 | A 12-cup drip coffee maker with programmable timer | 2024-11-07 09:12:06 |
|  2 | Toaster      |  29.99 | 2-slice toaster with multiple browning settings    | 2024-11-07 09:12:16 |
|  3 | Blender      |  79.99 | High-speed blender for smoothies and soups         | 2024-11-07 09:12:16 |
|  4 | Microwave    | 129.99 | 1000-watt microwave with digital controls          | 2024-11-07 09:12:16 |
+----+--------------+--------+----------------------------------------------------+---------------------+

Basic SELECT Queries

In this step, we'll learn how to retrieve data from our table using SELECT statements. We'll explore different ways to select and format our output.

Selecting Specific Columns

Instead of selecting all columns with *, we can specify exactly which columns we want to see:

SELECT name, price FROM products;

This gives us a more focused view of our data:

+-------------+--------+
| name        | price  |
+-------------+--------+
| Coffee Maker| 49.99  |
| Toaster     | 29.99  |
| Blender     | 79.99  |
| Microwave   | 129.99 |
+-------------+--------+

Using Column Aliases

We can make our output more readable by using column aliases with the AS keyword:

SELECT
    name AS product_name,
    price AS retail_price,
    price * 0.8 AS sale_price
FROM products;

This query:

Renames 'name' to 'product_name'
Renames 'price' to 'retail_price'
Calculates a sale price (20% off) and names it 'sale_price'

You'll see output like:

+-------------+-------------+------------+
| product_name| retail_price| sale_price |
+-------------+-------------+------------+
| Coffee Maker| 49.99       | 39.99      |
| Toaster     | 29.99       | 23.99      |
| Blender     | 79.99       | 63.99      |
| Microwave   | 129.99      | 103.99     |
+-------------+-------------+------------+

Filtering Results

We can use the WHERE clause to filter our results:

SELECT name, price
FROM products
WHERE price < 50.00;

This shows us only products under $50:

+-------------+-------+
| name        | price |
+-------------+-------+
| Coffee Maker| 49.99 |
| Toaster     | 29.99 |
+-------------+-------+

Updating Data

In this step, we'll learn how to modify existing data using the UPDATE statement. The UPDATE statement allows us to change values in our tables.

IMPORTANT: Always use a WHERE clause with UPDATE statements! Without WHERE, the update will affect ALL rows in the table.

Let's say we need to increase the price of our Coffee Maker:

UPDATE products
SET price = 54.99
WHERE name = 'Coffee Maker';

We can also update multiple columns at once:

UPDATE products
SET
    price = 89.99,
    description = 'Professional-grade high-speed blender for smoothies and soups'
WHERE name = 'Blender';

To verify our updates:

SELECT name, price, description
FROM products
WHERE name IN ('Coffee Maker', 'Blender');

You should see the updated values:

+--------------+-------+---------------------------------------------------------------+
| name         | price | description                                                   |
+--------------+-------+---------------------------------------------------------------+
| Coffee Maker | 54.99 | A 12-cup drip coffee maker with programmable timer            |
| Blender      | 89.99 | Professional-grade high-speed blender for smoothies and soups |
+--------------+-------+---------------------------------------------------------------+

Deleting Data

In this final step, we'll learn how to remove data from our tables using the DELETE statement. Like UPDATE, it's crucial to use WHERE with DELETE to avoid accidentally removing all data.

Let's remove the Toaster from our products:

DELETE FROM products
WHERE name = 'Toaster';

To verify the deletion:

SELECT * FROM products;

You should see all products except the Toaster:

+----+-------------+-------+-----------------------------------------------------+---------------------+
| id | name        | price | description                                         | created_at          |
+----+-------------+-------+-----------------------------------------------------+---------------------+
|  1 | Coffee Maker| 54.99 | A 12-cup drip coffee maker with programmable timer | 2024-01-01 12:00:00 |
|  3 | Blender     | 89.99 | Professional-grade high-speed blender for smoothies| 2024-01-01 12:00:00 |
|  4 | Microwave   | 129.99| 1000-watt microwave with digital controls          | 2024-01-01 12:00:00 |
+----+-------------+-------+-----------------------------------------------------+---------------------+

We can also delete multiple rows at once. Let's remove all products over $100:

DELETE FROM products
WHERE price > 100;

This should remove the Microwave from our products list.

MariaDB [store]> SELECT * FROM products;
+----+--------------+-------+---------------------------------------------------------------+---------------------+
| id | name         | price | description                                                   | created_at          |
+----+--------------+-------+---------------------------------------------------------------+---------------------+
|  1 | Coffee Maker | 54.99 | A 12-cup drip coffee maker with programmable timer            | 2024-11-07 09:12:06 |
|  3 | Blender      | 89.99 | Professional-grade high-speed blender for smoothies and soups | 2024-11-07 09:12:16 |
+----+--------------+-------+---------------------------------------------------------------+---------------------+

Summary

In this lab, we've covered the fundamental data manipulation operations in MySQL:

INSERT statements for adding single and multiple rows of data
SELECT queries for retrieving and formatting data
UPDATE statements for modifying existing data
DELETE statements for removing data from tables

These operations form the foundation of database manipulation and are essential skills for anyone working with databases.

MySQL Basic Data Manipulation