Introduction:

MySQL is a popular open-source relational database management system. It allows users to perform various operations on data, including Create, Read, Update, and Delete (CRUD). CRUD operations are essential to managing and manipulating data in any database system. In this article, we will explore CRUD operations in MySQL and demonstrate them using a sample table.

Sample Table:

Let's consider a sample table named "users" with the following structure and data:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    age INT
);

INSERT INTO users (name, email, age)
VALUES ('John Doe', '[email protected]', 30),
       ('Jane Smith', '[email protected]', 25),
       ('Bob Williams', '[email protected]', 40);

This table has four columns: id, name, email, and age. The id column is the primary key, which means it is unique for each row and is used to identify a specific record.

CRUD Operations:

Now let's explore each CRUD operation in MySQL and provide sample queries with outputs.

Create Operation:

The create operation is used to insert new records into a table. The following query inserts a new record into the users table:

INSERT INTO users (name, email, age)
VALUES ('Sarah Johnson', '[email protected]', 35);

Output:

Query OK, 1 row affected

Read Operation:

The read operation is used to retrieve records from a table. The following query retrieves all records from the users table:

SELECT * FROM users;

Output:

+----+--------------+------------------------+-----+
| id | name         | email                  | age |
+----+--------------+------------------------+-----+
|  1 | John Doe     | [email protected]    |  30 |
|  2 | Jane Smith   | [email protected]  |  25 |
|  3 | Bob Williams | [email protected]|  40 |
|  4 | Sarah Johnson| [email protected]| 35 |
+----+--------------+------------------------+-----+

Update Operation:

The update operation is used to modify existing records in a table. The following query updates the age of the user with id=1:

UPDATE users SET age = 32 WHERE id = 1;

Output:

Query OK, 1 row affected

Delete Operation:

The delete operation is used to remove records from a table. The following query deletes the user with id=2:

DELETE FROM users WHERE id = 2;

Output:

Query OK, 1 row affected

Conclusion:

CRUD operations are essential to managing and manipulating data in MySQL. In this article, we explored each CRUD operation with sample queries and outputs using a sample table. By understanding CRUD operations, users can perform various operations on data in a MySQL database effectively.