View on GitHub

SCRATCH PAD

No polish, just pulse

Relational Database (MySQL) - Complete Guide

1. Introduction to Relational Databases

2. Database Design and Normalization

3. MySQL Installation and Setup

4. SQL Basics

5. Advanced SQL Queries

6. Indexing and Performance Optimization

7. Transactions and Concurrency Control

8. Stored Procedures, Functions, and Triggers

9. MySQL Security Best Practices

10. MySQL Backup and Recovery

11. High Availability and Replication

12. MySQL and NoSQL Features

13. MySQL and Application Development

14. MySQL in the Cloud

15. MySQL Best Practices

16. MySQL Tools and Resources

1. Introduction to Relational Databases

What is a Database?

A database is a structured collection of data that is stored and managed electronically. It enables efficient retrieval, insertion, updating, and deletion of data while ensuring data integrity and security.

Types of Databases: Relational vs. NoSQL

Why Use MySQL?

MySQL is one of the most widely used relational database management systems (RDBMS). Reasons to choose MySQL:

Features of MySQL

2. Database Design and Normalization

Data Modeling Concepts

Data modeling is the process of structuring and organizing data within a database. It includes defining entities, attributes, relationships, and constraints.

Entity-Relationship (ER) Diagrams

ER diagrams visually represent database structure, showing entities, attributes, and relationships.

Normalization: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF

Normalization is the process of organizing database tables to reduce redundancy and improve data integrity.

Denormalization and When to Use It

Denormalization is the process of merging tables to reduce joins and improve performance at the cost of some redundancy.

Primary Key, Foreign Key, Composite Key

Constraints: UNIQUE, NOT NULL, CHECK, DEFAULT, AUTO_INCREMENT

Constraints ensure data integrity and consistency.

3. MySQL Installation and Setup

Installing MySQL on Windows, Mac, and Linux

MySQL can be installed on various operating systems using different methods:

MySQL Workbench and Command Line Interface (CLI)

Configuring MySQL Server

Creating and Managing Users and Permissions

4. SQL Basics

Data Types in MySQL

MySQL supports various data types categorized into:

Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age > 0),
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SQL Syntax Overview

CRUD Operations

SELECT (Retrieve data from a table)
SELECT name, age FROM users WHERE age > 18;
INSERT (Add new data to a table)
INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com');
UPDATE (Modify existing data)
UPDATE users SET age = 26 WHERE name = 'Alice';
DELETE (Remove data from a table)
DELETE FROM users WHERE name = 'Alice';

Filtering Data with WHERE

SELECT * FROM users WHERE age >= 21;

Sorting Data with ORDER BY

SELECT * FROM users ORDER BY age DESC;

Limiting Data with LIMIT

SELECT * FROM users LIMIT 5;

5. Advanced SQL Queries

Aggregate Functions

MySQL provides functions to perform calculations on multiple rows of data:

Example:

SELECT COUNT(*) AS total_users, AVG(age) AS average_age FROM users;

Grouping Data with GROUP BY and HAVING

Example:

SELECT age, COUNT(*) AS user_count FROM users GROUP BY age HAVING COUNT(*) > 1;

Using CASE Statements

The CASE statement allows conditional expressions.

SELECT name, age, 
CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 60 THEN 'Adult'
    ELSE 'Senior'
END AS age_category
FROM users;

Joins

INNER JOIN (Matches records from both tables)
SELECT users.name, orders.order_date 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;
LEFT JOIN (Includes all records from the left table)
SELECT users.name, orders.order_date 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;
RIGHT JOIN (Includes all records from the right table)
SELECT users.name, orders.order_date 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;
FULL JOIN (Combines LEFT and RIGHT JOIN; simulated using UNION)
SELECT users.name, orders.order_date 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id
UNION
SELECT users.name, orders.order_date 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;
SELF JOIN (Joins a table with itself)
SELECT e1.name AS employee, e2.name AS manager 
FROM employees e1 
JOIN employees e2 ON e1.manager_id = e2.id;

Subqueries

A query inside another query.

SELECT name FROM users WHERE age = (SELECT MAX(age) FROM users);

Common Table Expressions (CTEs)

CTEs simplify complex queries.

WITH user_ages AS (
    SELECT name, age FROM users WHERE age > 18
)
SELECT * FROM user_ages;

Window Functions

Used for ranking and aggregate calculations within partitions.

SELECT name, age, RANK() OVER (ORDER BY age DESC) AS rank FROM users;

6. Indexing and Performance Optimization

What is an Index?

An index is a database object that improves the speed of data retrieval operations on a table. Instead of scanning the entire table, MySQL can quickly locate the required rows using the index.

Types of Indexes

  1. Primary Index: Created automatically when defining a primary key. 
    CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
  2. Unique Index: Ensures that values in a column are unique. 
    CREATE UNIQUE INDEX idx_email ON users(email);
  3. Composite Index: Indexing multiple columns together. 
    CREATE INDEX idx_name_age ON users(name, age);
  4. Full-Text Index: Used for text searches. 
    CREATE FULLTEXT INDEX idx_desc ON products(description);
  5. Spatial Index: Used for geographical data. 
    CREATE SPATIAL INDEX idx_location ON locations(coordinates);

When to Use Indexes

Query Optimization Techniques

MySQL Query Execution Plan

Caching Strategies

7. Transactions and Concurrency Control

What is a Transaction?

A transaction is a sequence of one or more SQL statements executed as a single unit of work. Transactions ensure data consistency.

ACID Properties

  1. Atomicity - A transaction is all or nothing.
  2. Consistency - Ensures database remains in a valid state.
  3. Isolation - Transactions do not interfere with each other.
  4. Durability - Committed transactions are permanently stored.

MySQL Transaction Commands

Concurrency Control and Isolation Levels

  1. READ UNCOMMITTED - Allows dirty reads.
  2. READ COMMITTED - Prevents dirty reads.
  3. REPEATABLE READ - Prevents non-repeatable reads.
  4. SERIALIZABLE - Ensures full isolation. 
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Deadlocks and How to Avoid Them

8. Stored Procedures, Functions, and Triggers

What are Stored Procedures?

A stored procedure is a reusable SQL code block that executes a set of SQL statements.

   DELIMITER //
   CREATE PROCEDURE GetUsers()
   BEGIN
       SELECT * FROM users;
   END //
   DELIMITER ;
   CALL GetUsers();

Creating and Using Stored Procedures

What are User-Defined Functions (UDFs)?

A function returns a value and can be used in SQL queries.

   DELIMITER //
   CREATE FUNCTION CalculateTax(price DECIMAL(10,2)) RETURNS DECIMAL(10,2) DETERMINISTIC
   BEGIN
       RETURN price * 0.05;
   END //
   DELIMITER ;
   SELECT CalculateTax(100);

What are Triggers?

Triggers are automatic actions executed before or after a database event.

Using Triggers for Automation

9. MySQL Security Best Practices

Managing Users and Permissions (GRANT, REVOKE)

MySQL allows fine-grained control over user access and permissions using the GRANT and REVOKE commands.

Example:

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON database_name.* TO 'user1'@'localhost';
REVOKE INSERT ON database_name.* FROM 'user1'@'localhost';

Users and their privileges can be viewed using:

SELECT User, Host FROM mysql.user;
SHOW GRANTS FOR 'user1'@'localhost';

Securing MySQL Connections with SSL

Secure Sockets Layer (SSL) encrypts MySQL connections to prevent eavesdropping.

Example:

ALTER USER 'user1'@'localhost' REQUIRE SSL;

To check if SSL is enabled:

SHOW VARIABLES LIKE 'have_ssl';

Preventing SQL Injection

SQL injection can be mitigated using prepared statements and input validation.

Example in Python:

cursor.execute("SELECT * FROM users WHERE username = %s", (user_input,))

Using parameterized queries ensures safe data handling.

Data Encryption Techniques

MySQL supports data encryption at rest and in transit using AES_ENCRYPT() and AES_DECRYPT().

Example:

CREATE TABLE secure_data (
    id INT PRIMARY KEY,
    secret VARBINARY(255)
);
INSERT INTO secure_data VALUES (1, AES_ENCRYPT('SensitiveData', 'encryption_key'));
SELECT AES_DECRYPT(secret, 'encryption_key') FROM secure_data;

Auditing and Logging User Activities

Enable MySQL logs to track user activities.

Example:

SET GLOBAL general_log = 'ON';
SHOW VARIABLES LIKE 'general_log%';

Logs can be found in the MySQL data directory.

10. MySQL Backup and Recovery

Types of Backups: Logical and Physical

MySQL Dump and Restore (mysqldump)

Example:

mysqldump -u root -p database_name > backup.sql
mysql -u root -p database_name < backup.sql

Point-in-Time Recovery

Use binary logs to restore data to a specific point in time.

Example:

mysqlbinlog binlog.000001 | mysql -u root -p

Replication-Based Backup

Replication ensures data redundancy by maintaining a copy of the database on another server.

Using MySQL Enterprise Backup

MySQL Enterprise Backup provides incremental and full backups with minimal downtime.

11. High Availability and Replication

MySQL Replication Types

Setting Up Replication

Example:

CHANGE MASTER TO MASTER_HOST='master_host', MASTER_USER='replicator', MASTER_PASSWORD='password';
START SLAVE;

Check replication status:

SHOW SLAVE STATUS\G;

Failover Mechanisms

MySQL Router and HAProxy enable automatic failover.

Load Balancing with MySQL Cluster

MySQL Cluster provides high availability and auto-sharding.

MySQL with ProxySQL

ProxySQL optimizes query routing and load balancing.

12. MySQL and NoSQL Features

MySQL JSON Data Type and Functions

MySQL supports JSON storage and querying.

Example:

CREATE TABLE json_data (id INT PRIMARY KEY, data JSON);
INSERT INTO json_data VALUES (1, '{"name": "John", "age": 30}');
SELECT data->'$.name' FROM json_data;

Using MySQL as a Document Store

MySQL Shell allows NoSQL-like queries:

document = {"name": "Alice", "email": "alice@example.com"};

MySQL vs. NoSQL Databases

13. MySQL and Application Development

Connecting MySQL with Programming Languages

PHP Example:

$pdo = new PDO("mysql:host=localhost;dbname=test", "user", "password");
$stmt = $pdo->query("SELECT * FROM users");

Python Example:

import mysql.connector
conn = mysql.connector.connect(host="localhost", user="user", password="password", database="test")
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")

Java Example:

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "user", "password");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");

Node.js Example:

const mysql = require('mysql');
const connection = mysql.createConnection({host: 'localhost', user: 'user', password: 'password', database: 'test'});
connection.query("SELECT * FROM users", function (error, results, fields) {
    if (error) throw error;
    console.log(results);
});

Using ORMs (Object-Relational Mappers)

Hibernate (Java):

@Entity
public class User {
    @Id @GeneratedValue private Long id;
    private String name;
}

Sequelize (Node.js):

const User = sequelize.define('User', {name: Sequelize.STRING});
User.findAll().then(users => console.log(users));

SQLAlchemy (Python):

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
Session.query(User).all()

14. MySQL in the Cloud

15. MySQL Best Practices