How to use CRUD Queries with MYSQL.

What is MySQL?

MySQL is an open-source RDBMS that uses Structured Query Language (SQL) to manage and manipulate data. It’s renowned for its scalability, reliability, and ease of use. Many websites and applications use MySQL for data storage, including giants like Facebook, YouTube, and Twitter. With its robust performance and flexibility, MySQL is a go-to choice for managing large volumes of data.

Creating a Table in MySQL

Before working with data, you need a table to store it. A table in MySQL is a structured format that organizes data into rows and columns.

Syntax for Creating a Table

CREATE TABLE table_name (

    column_name1 data_type constraints,

    column_name2 data_type constraints,

    ...

);

Example: Creating a “users” Table

Let’s create a users table to store user information:

CREATE TABLE users (

    user_id INT AUTO_INCREMENT PRIMARY KEY,

    username VARCHAR(50) NOT NULL,

    email VARCHAR(100) NOT NULL UNIQUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    status VARCHAR(20) DEFAULT 'active'

);



CREATE TABLE tbl_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id  VARCHAR(100) NULL,    
    payment_name varchar(100),
    payment_status VARCHAR(20) DEFAULT '0',
    desp varchar(2000) null,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);


CREATE TABLE tbl_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name  VARCHAR(100) NULL,    
    email varchar(100),
    mobile VARCHAR(20) DEFAULT '0',
    address varchar(2000) null,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
);

Explanation:

1.      user_id: A unique identifier for each user, set to auto-increment.

2.   username: Stores the name of the user, must not be NULL.

3.   email: The user’s email address, which must be unique.

4.   created_at: Automatically stores the timestamp when the record is created.

5.  status: Indicates the user’s status, with a default value of 'active'.

Understanding CRUD Operations

CRUD stands for Create, Read, Update, and Delete, which are the basic operations you can perform on data in a database.

1. Create: Adding Data to the Table

The INSERT statement allows you to add new records to a table.

Example:

INSERT INTO users (username, email, status)

VALUES ('MmantrTech', 'john@example.com', 'active');

2. Read: Retrieving Data from the Table

The SELECT statement is used to fetch data from the table.

Example:

SELECT * FROM users;

This query retrieves all columns and rows from the users table.

To retrieve specific columns:

SELECT username, email FROM users;

3. Update: Modifying Existing Data

The UPDATE statement allows you to change data in the table.

Example:

UPDATE users

SET status = 'inactive'

WHERE user_id = 1;

This updates the status of the user with user_id = 1 to 'inactive'.

4. Delete: Removing Data from the Table

The DELETE statement is used to remove records from the table.

Example:

DELETE FROM users

WHERE user_id = 1;

This deletes the record where user_id = 1.

Combining CRUD Operations in a Workflow

Let’s simulate a real-world scenario:

1.      Create: Add new users to the users table.

2.   Read: Retrieve and display user details.

3.   Update: Change user status to reflect account activity.

4.   Delete: Remove users who are no longer active.

Example Workflow:

-- Create a new user

INSERT INTO users (username, email, status) VALUES ('mmantratech', 'tech@example.com', 'active');

 

-- Read user information

SELECT * FROM users;

 

-- Update user status

UPDATE users SET status = 'inactive' WHERE username = 'mmantratech';

 

-- Delete a user

DELETE FROM users WHERE username = 'mmantratech';

Conclusion

MySQL is a powerful tool for managing data, and mastering its core operations will give you the foundation needed for any database project. By learning how to create tables and perform CRUD operations, you’re well on your way to becoming proficient in database management. Whether you're building small-scale projects or large applications, MySQL has the versatility and performance to meet your needs.