CRUD Operations in MySQL with Real Examples
Databases are the backbone of almost every modern application. Whether you are building a website, mobile app, or enterprise system, you need a reliable way to store and manage data. This is where CRUD operations come into play.
CRUD stands for Create, Read, Update, and Delete – the four basic operations used to interact with a database. In MySQL, these operations are performed using SQL queries that allow developers and database administrators to manage data efficiently.
In this blog, we will explore CRUD operations in MySQL with practical examples to help beginners and professionals understand how they work in real-world scenarios.
What are CRUD Operations?
CRUD is an acronym for four essential database operations:
| Operation | Description |
|---|---|
| Create | Insert new data into the database |
| Read | Retrieve data from the database |
| Update | Modify existing data |
| Delete | Remove data from the database |
These operations form the foundation of database-driven applications such as e-commerce platforms, banking systems, CRM tools, and more.
1. CREATE Operation in MySQL
The CREATE operation is used to insert new records into a table.
Before inserting data, we must first create a table.
Example: Creating a Table
CREATE TABLE employees ( emp_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50), salary INT );
This command creates a table named employees with the following columns:
-
emp_id– Unique employee ID -
name– Employee name -
department– Department name -
salary– Employee salary
Example: Inserting Data (CREATE)
INSERT INTO employees (name, department, salary) VALUES ('Rahul Sharma', 'IT', 60000);
Insert multiple records:
INSERT INTO employees (name, department, salary) VALUES ('Priya Mehta', 'HR', 45000), ('Amit Verma', 'Finance', 55000), ('Sneha Patil', 'IT', 70000);
After execution, the table will store these employee records.
2. READ Operation in MySQL
The READ operation retrieves data from the database using the SELECT statement.
Example: Fetch All Records
SELECT * FROM employees;
Output:
| emp_id | name | department | salary |
|---|---|---|---|
| 1 | Rahul Sharma | IT | 60000 |
| 2 | Priya Mehta | HR | 45000 |
| 3 | Amit Verma | Finance | 55000 |
| 4 | Sneha Patil | IT | 70000 |
Example: Fetch Specific Columns
SELECT name, salary FROM employees;
Example: Using WHERE Condition
SELECT * FROM employees WHERE department = 'IT';
This query retrieves only employees working in the IT department.
3. UPDATE Operation in MySQL
The UPDATE operation modifies existing records in a table.
Example: Update Salary
UPDATE employees SET salary = 65000 WHERE emp_id = 1;
This query updates the salary of employee with ID 1.
Example: Update Department
UPDATE employees SET department = 'Operations' WHERE name = 'Priya Mehta';
Important Tip
Always use a WHERE clause when updating data. Without it, MySQL will update all records in the table.
Example of dangerous query:
UPDATE employees SET salary = 50000;
This would change the salary of every employee.
4. DELETE Operation in MySQL
The DELETE operation removes records from a table.
Example: Delete Specific Record
DELETE FROM employees WHERE emp_id = 3;
This deletesthe employee with ID 3.
Example: Delete All Records
DELETE FROM employees;
This removes all rows but keeps the table structure.
Difference Between DELETE and TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes rows | Yes | Yes |
| Uses WHERE condition | Yes | No |
| Transaction safe | Yes | No |
| Resets AUTO_INCREMENT | No | Yes |
| Faster | No | Yes |
Example:
TRUNCATE TABLE employees;
This removes all data and resets the table.
Real-World Example of CRUD
Consider an e-commerce application where MySQL stores customer orders.
Create
A new customer places an order → record inserted.
INSERT INTO orders (customer_name, product, amount) VALUES ('Rohit', 'Laptop', 75000);
Read
Admin views all orders.
SELECT * FROM orders;
Update
Customer changes product quantity.
UPDATE orders SET amount = 80000 WHERE customer_name = 'Rohit';
Delete
Order gets cancelled.
DELETE FROM orders WHERE customer_name = 'Rohit';
Best Practices for CRUD Operations
- Always use WHERE clause in UPDATE and DELETE
- Backup important data before large updates
- Use transactions for critical operations
- Apply indexes for faster queries
- Implement data validation before inserting records
Conclusion
CRUD operations form the foundation of database management in MySQL. Understanding how to Create, Read, Update, and Delete data efficiently is essential for developers, data engineers, and database administrators.
Mastering these operations helps you build reliable applications, manage data effectively, and troubleshoot database issues in real-world systems.
At Learnomate Technologies, we help students and professionals gain hands-on experience with real-world database scenarios, covering MySQL, Oracle DBA, Data Engineering, and cloud technologies to build strong industry-ready skills.
