MySQL for Beginners: Modifying Data. (Part-10)

 

Welcome to the MySQL for Beginners series! In this blog post, we’ll dive into the essential topic of modifying data in MySQL. Whether you’re just starting or need a refresher, we’ll cover the basics and provide practical examples to help you grasp the concepts.

Why Is Modifying Data Important?

As a beginner, understanding how to manipulate data within a database is crucial. Whether you’re adding new records, updating existing ones, or deleting data, these skills are fundamental for any aspiring developer or data enthusiast.

Let’s Get Started!

1. INSERT: Adding New Data

The INSERT statement allows you to add new records to a table. Imagine you have a table called Customers, and you want to add an “Email” column. Here’s how you’d do it:

ALTER TABLE Customers ADD Email VARCHAR(255);

Now your Customers table includes an “Email” column to store email addresses.

2. INSERT Multiple Rows

Sometimes you need to insert multiple rows at once. Use the following syntax:

INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (101, 1, '2023-06-01'),
       (102, 2, '2023-06-02'),
       (103, 3, '2023-06-03');

This adds three new orders to the Orders table.

3. INSERT INTO SELECT

The INSERT INTO SELECT statement lets you copy data from one table to another. Suppose you want to create a new table called VIPCustomers with data from the Customers table:

CREATE TABLE VIPCustomers AS
SELECT * FROM Customers WHERE TotalPurchases > 1000;

4. UPDATE: Modifying Existing Data

The UPDATE statement allows you to modify existing records. For example, let’s change the email address for customer ID 1:

SQL

UPDATE Customers
SET Email = 'new@email.com'
WHERE CustomerID = 1;

5. DELETE: Removing Data

To delete records, use the DELETE statement. If you want to delete all orders for a specific customer, you can use:

SQL

DELETE FROM Orders WHERE CustomerID = 2;

6. ON DELETE CASCADE

When you delete a record from a parent table, you might want related records in child tables to be deleted automatically. That’s where ON DELETE CASCADE comes in:

SQL

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

7. DELETE JOIN

Combining DELETE with JOIN allows you to delete data from multiple tables simultaneously. For instance:

SQL

DELETE Customers, Orders
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.Country = 'USA';

8. REPLACE: Handling Duplicates

The REPLACE statement replaces existing records or inserts new ones if duplicates are found. Useful when dealing with unique or primary keys. Example

Handling Duplicates: The REPLACE statement in MySQL is used to insert a new row into a table or update an existing row if a duplicate key is found. Example:

Let’s say we have a table named cities:

CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    population INT NOT NULL
);
--We insert some initial data:

INSERT INTO cities (name, population) VALUES 
('New York', 8008278),
('Los Angeles', 3694825),
('San Diego', 1223405);

--Now, if we want to update the population of Los Angeles, we can use the REPLACE statement:

REPLACE INTO cities (id, name, population) VALUES 
(2, 'Los Angeles', 3696820);

In this example, the REPLACE statement will:

1. Delete the existing row with id = 2.
2. Insert a new row with the updated population for Los Angeles.

9. Prepared Statements

Prepared statements are a feature in MySQL that allows you to execute the same SQL statement repeatedly with high efficiency. They help protect against SQL injection attacks and improve performance by pre-compiling the SQL statement.

Example:Let’s consider a scenario where we need to insert multiple rows into a users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

--Using a prepared statement in MySQL:

PREPARE stmt FROM 'INSERT INTO users (username, email) VALUES (?, ?)';
SET @username = 'john_doe';
SET @useremail = 'john@example.com';
EXECUTE stmt USING @username, @useremail;

SET @username = 'jane_doe';
SET @useremail = 'jane@example.com';
EXECUTE stmt USING @username, @useremail;

DEALLOCATE PREPARE stmt;

In this example:

1. The PREPARE statement creates a prepared statement with placeholders (?).
2. The SET statements assign values to the placeholders.
3. The EXECUTE statement runs the prepared statement with the specified values.
4. Finally, the DEALLOCATE PREPARE statement releases the prepared statement2.

Using prepared statements ensures that the SQL queries are safe from injection attacks and can be executed more efficiently.

Conclusion:

Congratulations! We’ve learned the basics of modifying data in MySQL. Remember, practice makes perfect. Keep exploring and stay curious! 🚀

“In the world of databases, every change is a step toward progress.” — Anonymous

If you found this post helpful, please follow me on LinkedIn and give it a clap👏👏! Let’s continue our MySQL journey together. 😊👍.I hope this blog post helps you understand MySQL data manipulation better! If you have any questions or need further clarification, feel free to ask. 🌟

More Resources Books📚📘👇:

1. MySQL for Beginners

2. Querying MySQL

3. Learn SQL (using MySQL) in One Day