MySQL

Introduction

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating data. It is widely used for web applications, enterprise applications, and data-driven services.

Key Features of MySQL

1. Relational Database – Stores data in tables with structured relationships.

2. ACID Compliance – Ensures reliability through transactions.

3. High Performance – Supports indexing, caching, and optimized queries.

4. Scalability – Works well with small and large-scale applications.

5. Security – Provides user authentication, role-based access control (RBAC), and encryption.

6. Replication & High Availability – Master-slave and master-master replication for failover support.

7. Stored Procedures & Triggers – Supports procedural programming within the database.

MySQL Architecture

The MySQL architecture consists of multiple components:

1. Client Layer – Applications communicate using MySQL drivers.

2. SQL Parser & Optimizer – Parses and optimizes queries for efficiency.

3. Storage Engine Layer – Manages how data is stored and retrieved.

   • InnoDB (default) – ACID-compliant, supports transactions.

   • MyISAM – Fast for read-heavy operations but lacks transactions.

4. Buffer Pool & Query Cache – Speeds up query execution.

5. Replication & Clustering – Provides failover and load balancing.

Basic MySQL Operations

1. Creating a Database

CREATE DATABASE my_database;
USE my_database;

1. Creating a Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1. Inserting Data

INSERT INTO users (username, email, password)
VALUES ('john_doe', '[email protected]', 'hashed_password');

1. Retrieving Data

SELECT * FROM users WHERE email = '[email protected]';

1. Updating Data

UPDATE users SET password = 'new_hashed_password' WHERE username = 'john_doe';

1. Deleting Data

DELETE FROM users WHERE username = 'john_doe';

MySQL Use Cases

• Web Applications – Used by Facebook, Twitter, and WordPress.

• E-Commerce Platforms – Manages transactions, orders, and users.

• Enterprise Applications – Suitable for CRM, ERP, and HR systems.

• Data Warehousing – Stores structured data for reporting and analytics.