> For the complete documentation index, see [llms.txt](https://guvi.gitbook.io/fsd/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://guvi.gitbook.io/fsd/docs/module-7-database/mysql/basic-crud-queries.md).

# Basic CRUD Queries

#### Introduction

CRUD operations are the backbone of interacting with databases. CRUD stands for Create, Read, Update, and Delete. In MySQL, these operations allow users to manage and manipulate data within a database. This guide will provide detailed instructions and examples on how to perform these basic CRUD operations in MySQL.

#### Prerequisites

Before diving into CRUD operations, ensure you have:

* MySQL installed and running on your system.
* Basic understanding of SQL and relational database concepts.
* A MySQL database and a table to work with. For this guide, we'll use a simple table named `employees` in a database named `company`.

#### 1. Create

Creating data involves inserting new records into a table. The `INSERT` statement is used for this purpose.

**Example Table Structure**

```sql
CREATE DATABASE company;

USE company;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    hire_date DATE NOT NULL
);
```

**Inserting Data**

```sql
INSERT INTO employees (first_name, last_name, email, salary, hire_date) 
VALUES ('John', 'Doe', 'john.doe@example.com', 50000.00, '2023-05-01');
```

You can insert multiple records in a single query:

```sql
INSERT INTO employees (first_name, last_name, email, salary, hire_date) 
VALUES 
('Jane', 'Smith', 'jane.smith@example.com', 55000.00, '2023-06-15'),
('Michael', 'Brown', 'michael.brown@example.com', 60000.00, '2023-07-20');
```

#### 2. Read

Reading data from the database involves querying the table to retrieve records. The `SELECT` statement is used for this purpose.

**Retrieving All Records**

```sql
SELECT * FROM employees;
```

**Retrieving Specific Columns**

```sql
SELECT first_name, last_name, email FROM employees;
```

**Retrieving Records with a Condition**

```sql
SELECT * FROM employees WHERE salary > 55000;
```

**Sorting Results**

```sql
SELECT * FROM employees ORDER BY hire_date DESC;
```

#### 3. Update

Updating data involves modifying existing records in a table. The `UPDATE` statement is used for this purpose.

**Updating a Single Record**

```sql
UPDATE employees 
SET salary = 65000 
WHERE email = 'jane.smith@example.com';
```

**Updating Multiple Records**

```sql
UPDATE employees 
SET salary = salary * 1.10 
WHERE hire_date < '2023-06-01';
```

#### 4. Delete

Deleting data involves removing records from a table. The `DELETE` statement is used for this purpose.

**Deleting a Single Record**

```sql
DELETE FROM employees 
WHERE email = 'john.doe@example.com';
```

**Deleting Multiple Records**

```sql
DELETE FROM employees 
WHERE salary < 55000;
```

#### Best Practices

* **Backup Data**: Always backup your data before performing `DELETE` or `UPDATE` operations.
* **Use Transactions**: For critical operations, use transactions to ensure data integrity.
* **Test Queries**: Test your queries on a small subset of data before applying them to the entire database.
* **Use Constraints**: Apply proper constraints like `NOT NULL`, `UNIQUE`, `FOREIGN KEY` to maintain data integrity.

#### Conclusion

Understanding and effectively using CRUD operations is essential for database management. By mastering `CREATE`, `READ`, `UPDATE`, and `DELETE` statements, you can efficiently interact with and manage your MySQL databases. Practice these operations regularly to become proficient in handling real-world database scenarios.