MySQL

MySQL – Relational Database Management System (RDBMS)

MySQL is an open-source, relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage data. It is one of the most popular databases, particularly in web applications, due to its high performance, reliability, and ease of use. MySQL is widely used in combination with web development languages like PHP, Python, and JavaScript (Node.js), as well as frameworks like WordPress, Joomla, and Drupal.

________________________________________

1. Key Features of MySQL

🔹 Open Source

MySQL is free to use under the GPL license, making it accessible for individuals and businesses alike. There are also enterprise versions with advanced features available via a commercial license.

🔹 Cross-Platform

MySQL works on various operating systems, including Windows, Linux, macOS, and others.

🔹 High Performance

MySQL is designed to handle high volumes of data and provide fast query execution. It supports indexing, full-text search, and optimized query execution to enhance performance.

🔹 Scalability

MySQL can be scaled horizontally (adding more servers) or vertically (upgrading hardware) to handle larger datasets and more users. It is suitable for applications of all sizes, from small websites to large-scale systems.

🔹 ACID Compliant

MySQL supports ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring data integrity and reliability, which is essential for handling transactions.

🔹 Replication and Clustering

MySQL offers replication features that allow data to be mirrored from one server to another, which provides high availability, fault tolerance, and load balancing.

________________________________________

2. MySQL Basic Concepts

🔹 Database

A database is a collection of organized data. It consists of tables that store data in rows and columns. Each table is designed to store specific types of information (e.g., customer data, product details).

CREATE DATABASE my_database;

🔹 Table

A table is a collection of rows and columns, similar to a spreadsheet. Each column represents a type of data (e.g., customer name, product price), and each row contains a record.

CREATE TABLE customers (

    customer_id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    email VARCHAR(100),

    phone_number VARCHAR(15)

);

🔹 Row

A row (or record) is a single entry in a table, representing one unit of data, such as a customer or order.

INSERT INTO customers (name, email, phone_number) 

VALUES ('John Doe', 'john.doe@example.com', '555-1234');

🔹 Column

A column defines the type of data that each row can hold. For example, in the customers table above, name, email, and phone_number are columns.

🔹 Primary Key

A primary key is a unique identifier for a row in a table. It ensures that each record can be uniquely identified. Typically, primary keys are set on columns that contain unique values (e.g., customer ID).

🔹 Foreign Key

A foreign key is a field that links one table to another. It is used to create relationships between tables.

CREATE TABLE orders (

    order_id INT AUTO_INCREMENT PRIMARY KEY,

    customer_id INT,

    order_date DATE,

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

________________________________________

3. SQL Queries in MySQL

🔹 SELECT Statement

The SELECT statement is used to retrieve data from one or more tables.

SELECT * FROM customers;

🔹 WHERE Clause

The WHERE clause is used to filter results based on a condition.

SELECT * FROM customers WHERE name = 'John Doe';

🔹 INSERT INTO Statement

The INSERT INTO statement is used to add new rows into a table.

INSERT INTO customers (name, email, phone_number)

VALUES ('Jane Doe', 'jane.doe@example.com', '555-6789');

🔹 UPDATE Statement

The UPDATE statement is used to modify existing records in a table.

UPDATE customers

SET phone_number = '555-9876'

WHERE customer_id = 1;

🔹 DELETE Statement

The DELETE statement removes one or more rows from a table.

DELETE FROM customers WHERE customer_id = 2;

🔹 ORDER BY Clause

The ORDER BY clause sorts the results in ascending or descending order.

SELECT * FROM customers ORDER BY name ASC;

🔹 JOIN Clause

The JOIN clause is used to combine rows from two or more tables based on a related column.

SELECT customers.name, orders.order_date

FROM customers

JOIN orders ON customers.customer_id = orders.customer_id;

🔹 GROUP BY Clause

The GROUP BY clause groups rows that have the same values into summary rows, such as counting the number of orders per customer.

SELECT customer_id, COUNT(*) AS order_count

FROM orders

GROUP BY customer_id;

________________________________________

4. Advanced MySQL Concepts

🔹 Indexes

An index is a data structure that improves the speed of data retrieval operations on a table. Indexes can be created on columns to enhance performance.

CREATE INDEX idx_email ON customers(email);

🔹 Transactions

A transaction is a sequence of SQL queries that are executed together. MySQL supports transactions to ensure that a set of operations are completed successfully. If any operation fails, the transaction can be rolled back.

START TRANSACTION;

UPDATE customers SET phone_number = '555-9999' WHERE customer_id = 1;

INSERT INTO orders (customer_id, order_date) VALUES (1, '2025-04-18');

COMMIT;  -- To commit the transaction

-- ROLLBACK;  -- To undo changes in case of an error

🔹 Views

A view is a virtual table based on the result of a SQL query. Views do not store data but are used to simplify complex queries.

CREATE VIEW customer_orders AS

SELECT customers.name, orders.order_date

FROM customers

JOIN orders ON customers.customer_id = orders.customer_id;

🔹 Stored Procedures

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. They are used for repetitive tasks and to encapsulate logic.

DELIMITER //

CREATE PROCEDURE GetCustomerInfo (IN customer_id INT)

BEGIN

    SELECT * FROM customers WHERE customer_id = customer_id;

END //

DELIMITER ;

🔹 Triggers

A trigger is a set of instructions that automatically executes in response to certain events on a table (e.g., INSERT, UPDATE, or DELETE).

CREATE TRIGGER update_order_status

AFTER UPDATE ON orders

FOR EACH ROW

BEGIN

    IF NEW.status = 'shipped' THEN

        UPDATE customers SET status = 'active' WHERE customer_id = NEW.customer_id;

    END IF;

END;

________________________________________

5. MySQL Optimization

🔹 Query Optimization

• Use indexes on frequently searched columns.

• Avoid using *SELECT ; and select only the necessary columns.

• Use LIMIT to limit the number of rows returned.

• Avoid nested queries if possible; instead, use JOIN for better performance.

🔹 Normalization

Normalization is the process of organizing data to reduce redundancy and dependency. It involves dividing a large table into smaller ones and establishing relationships between them.

🔹 Denormalization

Denormalization is the process of combining tables to improve performance in certain scenarios (such as read-heavy applications). However, it may increase redundancy.

________________________________________

6. MySQL Tools and Utilities

• MySQL Workbench: A GUI tool for managing MySQL databases. It provides visual tools for database design, query execution, and performance monitoring.

• phpMyAdmin: A web-based interface for managing MySQL databases.

• MySQL Command-Line Client: A terminal-based tool for interacting with MySQL.