MySQL CRUD Operations (Create, Read, Update, Delete)

CRUD (Create, Read, Update, Delete) operations are fundamental to interacting with databases in software development. CRUD stands for Create, Read, Update, and Delete, and represents the four basic operations you can perform on data. Let's break down each operation with practical examples, assuming a simple database of users, where each user has an ID, a name, and an email.

Create

Creating is about adding new data into the database.

Create MySQL Example

SQL
INSERT INTO Users (Name, Email) VALUES ('John Doe', '[email protected]');

This SQL command adds a new user with the name "John Doe" and email "[email protected]" to the Users table.

Read

Reading is retrieving data from the database.

Read MySQL Example

SQL
SELECT * FROM Users;

This command fetches all records from the Users table. If you want to retrieve only specific records, you can use a condition. For example, to get only John Doe's details:

SQL
SELECT * FROM Users WHERE Name = 'John Doe';

Update

Updating means modifying existing data.

Update MySQL Example

SQL
UPDATE Users SET Email = '[email protected]' WHERE Name = 'John Doe';

This command updates John Doe's email address to a new one.

Delete

Deleting is about removing data from the database.

Delete MySQL Example

SQL
DELETE FROM Users WHERE Name = 'John Doe';

This command will delete John Doe's record from the Users table.

Practical Application

In a programming environment, these operations are often implemented in functions or methods. For example, in a Python application using a library like SQLAlchemy for database interaction, you might have:

  • create_user(name, email) - To add a new user.
  • get_user(name) - To retrieve a user's details.
  • update_user(name, new_email) - To update a user's email.
  • delete_user(name) - To delete a user.

Each of these functions would contain the relevant SQL commands and additional logic as needed for your application.