Chapter 1: The Library (What & Why)

That system is **MySQL**. A "Database" is just a collection of information that a computer can search through in a split second.

We talk to the database using **SQL** (Structured Query Language). It's like a formal list of instructions for the librarian.

Chapter 2: Building Cabinets (DDL)

There are four major commands for managing the "Structure" of your library:

• CREATE: Build a new cabinet.
• ALTER: Add a new drawer to an existing cabinet.
• DROP: Destroy the cabinet and everything inside it forever.
• TRUNCATE: Keep the cabinet, but dump everything inside it into the trash. (Much faster than deleting one by one!)

-- 1. Create a table for users
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
);

-- 2. Add a new drawer for 'age'
ALTER TABLE users ADD COLUMN age INT;

-- 3. Clear all users but keep the table
TRUNCATE TABLE users;

-- 4. Delete the whole table from existence
-- DROP TABLE users;

Chapter 3: Managing Data (DML)

This is where you spend most of your time. You are managing the "Content" inside the cabinets.

-- 1. Adding multiple people at once
INSERT INTO users (name, age) VALUES 
('Alex', 15),
('Sam', 14),
('Jordan', 16);

-- 2. Updating data (Always use WHERE!)
UPDATE users SET age = 16 WHERE name = 'Alex';

-- 3. Deleting data
DELETE FROM users WHERE name = 'Jordan';

Chapter 4: The Master Key (IDs)

This is called a Primary Key. It's a column where every single value must be different. Most developers use AUTO_INCREMENT so the library gives out the next ID number (1, 2, 3...) automatically.

Chapter 5: Data Types Deep-Dive

• VARCHAR(size): For text like names or emails. You set the max size!
• CHAR(size): For fixed-size text like a Zip Code or Country Code (US, UK). It's faster for the computer to read.
• INT: For whole numbers.
• DECIMAL: For money (like 19.99). It is much more accurate than "Float".
• TEXT: For long paragraphs or blog posts.
• ENUM: A list of options. E.g., ('Small', 'Medium', 'Large').

Chapter 6: Searching like a Pro

-- 1. SELECT DISTINCT: Show only UNIQUE ages (no duplicates)
SELECT DISTINCT age FROM users;

-- 2. LIMIT & OFFSET: Only show 10 people, starting after the first 5
SELECT * FROM users LIMIT 10 OFFSET 5;

-- 3. ALIAS (AS): Rename a column just for this search
SELECT name AS 'Student Name' FROM users;

Chapter 7: Advanced Filtering

-- 1. BETWEEN: Find numbers in a range
SELECT * FROM users WHERE age BETWEEN 12 AND 18;

-- 2. IN: Find items from a specific list
SELECT * FROM users WHERE city IN ('London', 'Paris', 'New York');

-- 3. LIKE (Wildcards): Find names starting with 'S'
-- The % means "anything can be after this"
SELECT * FROM users WHERE name LIKE 'S%';

-- 4. IS NULL: Find people who forgot to enter their email
SELECT * FROM users WHERE email IS NULL;

Chapter 8: The Math Genius

-- COUNT: How many total users?
SELECT COUNT(*) FROM users;

-- SUM: Total money in all bank accounts?
SELECT SUM(balance) FROM accounts;

-- AVG: Average age of our users?
SELECT AVG(age) FROM users;

-- MIN/MAX: Youngest and oldest person?
SELECT MIN(age), MAX(age) FROM users;

Chapter 9: Grouping the Crowd

We use GROUP BY to bundle rows together. If you want to filter these bundles, you use HAVING (not WHERE!).

-- Count people in each city, but only show cities with more than 5 people
SELECT city, COUNT(*) 
FROM users 
GROUP BY city 
HAVING COUNT(*) > 5;

Chapter 10: Joins (Full Masterclass)

1. INNER JOIN (The Strict Match)

This is the most common join. It only shows results when there is a match in **BOTH** tables. If a student isn't in a class, or a class has no students, they are hidden.

-- Show only students who have a class
SELECT students.name, classes.class_name
FROM students
INNER JOIN classes ON students.id = classes.student_id;

2. LEFT JOIN (The Kind Inclusion)

This shows **ALL** rows from the left table, even if there is no match on the right. If a student has no class, the class name will just say "NULL" (Empty).

-- Show EVERY student, even those with no class
SELECT students.name, classes.class_name
FROM students
LEFT JOIN classes ON students.id = classes.student_id;

3. RIGHT JOIN (The Reverse Inclusion)

This is the opposite of a Left Join. it shows **ALL** rows from the right table. If a class has no students assigned to it yet, the class will still show up, but the student name will be "NULL".

-- Show EVERY class, even those with no students
SELECT students.name, classes.class_name
FROM students
RIGHT JOIN classes ON students.id = classes.student_id;

4. CROSS JOIN (The Party Planner)

This matches **every single row** from the first table with **every single row** from the second table. It creates every possible combination. If you have 5 students and 4 classes, you get 20 results.

-- Show every possible pairing of students and classes
SELECT students.name, classes.class_name
FROM students
CROSS JOIN classes;

5. SELF JOIN (The Family Tree)

Sometimes a table needs to join with itself. Imagine a "Staff" table where every employee has a `manager_id`. To see the manager's name, you have to look back at the same table!

-- Link employees to their managers in the same table
SELECT e.name AS Employee, m.name AS Manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

6. FULL OUTER JOIN

This shows **ALL** rows from both tables. If there is a match, it glues them. If not, it shows NULL for the missing side. (Note: In MySQL, we usually do this by combining a LEFT JOIN and a RIGHT JOIN with a `UNION`).

Chapter 11: Virtual Tables (Views)

A **View** is like a window. It doesn't store data, it just looks at the data in a specific way. It behaves exactly like a real table!

-- Create the view
CREATE VIEW premium_users AS 
SELECT name, email FROM users WHERE balance > 1000;

-- Use it just like a table!
SELECT * FROM premium_users;

Chapter 12: The Speed Pass (Indexing)

What is an Index?

An **Index** is a hidden, super-fast map that MySQL creates. It's exactly like the index at the back of a textbook. Instead of reading the whole book, you find the word "Apples" in the index, and it tells you exactly which page to turn to.

How it Works (The B-Tree)

Internally, MySQL uses a structure called a **B-Tree** (Balanced Tree). It's like a game of "Higher or Lower". If you are looking for ID #50, the computer goes to the middle and asks: "Is 50 higher or lower than 100?" It then cuts the search in half again and again, finding your data in just a few steps!

Types of Indexes

• Primary Key: The ultimate index. It is unique and never empty. Every table should have one.
• Unique Index: Like a primary key, it prevents duplicates. Use this for things like "Username" or "Email".
• Regular Index (INDEX): Used to speed up searches on columns you use often in `WHERE` clauses.
• Full-Text Index: A special index for searching inside long paragraphs of text (like blog posts or product descriptions).
• Composite Index: An index that covers two or more columns at the same time (e.g., searching for both `first_name` and `last_name`).

Real-World Examples

-- 1. Create a Unique Index for emails
CREATE UNIQUE INDEX idx_user_email ON users(email);

-- 2. Create a Regular Index for a frequently searched city
CREATE INDEX idx_user_city ON users(city);

-- 3. Create a Composite Index for Name searches
CREATE INDEX idx_full_name ON users(first_name, last_name);

-- 4. Create a Full-Text Index for searching blog content
CREATE FULLTEXT INDEX idx_blog_search ON posts(content);

The Pros & Cons (The Architect's Trade-off)

The Pros:

• **Lightning Speed:** Searches that took 5 seconds now take 0.001 seconds.
• **Better Performance:** Your server can handle thousands of users at once without breaking a sweat.

The Cons:

• **Slower Writing:** Every time you `INSERT` or `UPDATE` data, MySQL has to update the hidden index map. This takes a tiny bit of extra time.
• **Disk Space:** Indexes take up extra space on the hard drive. A table with 10 indexes will be much "heavier" than a table with 1.

When to Index?

Rule of thumb: Index columns that appear frequently in your `WHERE`, `ORDER BY`, and `JOIN` clauses. Do **not** index columns that are rarely searched, or columns with only two values (like "Gender").

Chapter 13: Security Chains (Foreign Keys)

What is a Foreign Key?

It is a column in one table (like `orders.user_id`) that points to a Primary Key in another table (like `users.id`). It ensures that your data relationships always make sense.

Enforcing Rules (Constraints)

• ON DELETE CASCADE: The "Cleanup" rule. If you delete a user, MySQL automatically deletes all their orders too. No mess left behind!
• ON DELETE SET NULL: The "Archive" rule. If you delete a user, the orders stay, but the `user_id` becomes empty (NULL).
• RESTRICT: The "Strict" rule. MySQL will physically block you from deleting a user if they have active orders.

-- Creating a table with a Foreign Key security chain
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Chapter 14: Safe Money (Transactions)

A **Transaction** ensures that a group of steps either all finish successfully, or none of them happen at all. It's "All or Nothing".

The ACID Rules

• Atomicity: The whole transaction is one single "atom". You can't split it. Either all of it happens or zero happens.
• Consistency: The database must follow all rules before and after the transaction.
• Isolation: If two people are doing transactions at the same time, they shouldn't see each other's half-finished work.
• Durability: Once a transaction is "Committed", it is written to the hard drive forever, even if the power goes out a second later.

START TRANSACTION;

-- Step 1: Deduct money
UPDATE accounts SET balance = balance - 50 WHERE id = 1;

-- Step 2: Add money
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

-- If everything is perfect:
COMMIT;

-- If something went wrong (like a crash):
-- ROLLBACK;

Chapter 15: The Engines (InnoDB vs MyISAM)

InnoDB (The Professional Standard)

This is the default engine for MySQL. It supports **Transactions** and **Foreign Keys**. It is very safe. If your computer crashes, InnoDB has a "journal" to recover your data automatically.

MyISAM (The Fast Reader)

This is an older engine. It is very fast for reading data (like a blog or a news site), but it does **not** support transactions or foreign keys. If the power goes out during a write, your data might get corrupted.

-- You can choose the engine when creating a table
CREATE TABLE log_data (
    id INT,
    message TEXT
) ENGINE=MyISAM; -- Use MyISAM for fast logging

Chapter 16: Built-in Magic (Functions)

String Magic

CONCAT(a, b) glues text together. UPPER() makes text shout. SUBSTRING() cuts out a piece of text.

Date Magic

NOW() gives the exact second. DATEDIFF() calculates how many days between two dates. Very useful for calculating ages or deadlines!

Logic Magic (CASE WHEN)

You can even write "If/Else" logic directly inside your SQL search!

-- Use CASE to label users based on age
SELECT name, 
CASE 
    WHEN age >= 18 THEN 'Adult' 
    WHEN age < 18 THEN 'Minor'
    ELSE 'Unknown'
END AS user_status
FROM users;

Chapter 17: Librarian's Logic (Normalization)

The Three Levels of Neatness

• 1st Normal Form (1NF): No "lists" in one drawer. Every drawer must hold exactly ONE item. (No "hobbies: soccer, gaming" in one box).
• 2nd Normal Form (2NF): Every drawer in the cabinet must relate directly to the ID card. No random data!
• 3rd Normal Form (3NF): No "indirect" relations. If you have the user's city and the city's zip code, put those in a separate "Cities" table. Don't repeat the zip code for every user in that city!

Why do we do this? It prevents mistakes. If a user changes their address, you only have to change it in ONE place, not in 50 different orders!