# 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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://guvidocs.gitbook.io/guvi-mern/docs/module-7-database/mysql/basic-crud-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.