Skip to content

MySQL_Comprehensive_Notes.md

Latest commit

 

History

History
986 lines (786 loc) · 32.4 KB

MySQL_Comprehensive_Notes.md

File metadata and controls

986 lines (786 loc) · 32.4 KB
Raw

MySQL — Comprehensive Notes (Basic to Advanced)

Table of Contents

  1. Introduction to Databases & MySQL
  2. MySQL Data Types
  3. Database & Table Basics (DDL)
  4. Data Manipulation (DML)
  5. Constraints
  6. Operators & the WHERE Clause
  7. Sorting & Limiting Results
  8. Aggregate Functions & Grouping
  9. Joins
  10. Subqueries
  11. Set Operations (UNION)
  12. Views
  13. Indexes
  14. String, Numeric & Date Functions
  15. Stored Procedures & Functions
  16. Triggers
  17. Transactions & ACID Properties
  18. User Management & Privileges
  19. Normalization
  20. Advanced Topics: CTEs, Window Functions, JSON, Full-Text Search
  21. Performance: EXPLAIN & Query Optimization
  22. Backup & Restore

1. Introduction to Databases & MySQL

A database is an organized collection of structured data. A Database Management System (DBMS) is software used to store, retrieve, and manage that data. A Relational Database Management System (RDBMS) organizes data into tables (rows and columns) with relationships between them.

MySQL is one of the world's most popular open-source RDBMS, originally developed by MySQL AB (now owned by Oracle Corporation). It uses SQL (Structured Query Language) to define, manipulate, and query data.

Key characteristics of MySQL:

  • Open-source and free (Community Edition), with paid Enterprise options.
  • Client-server architecture — a MySQL server manages the data; clients (applications, command-line tools like mysql, GUI tools like MySQL Workbench) connect to it.
  • Supports multiple storage engines, most commonly InnoDB (default, supports transactions and foreign keys) and MyISAM (older, faster reads, no transaction support).
  • Widely used in web applications (the "M" in the LAMP stack: Linux, Apache, MySQL, PHP/Python/Perl).

Basic connection (command line):

mysql -u root -p
-- Enter password when prompted

Categories of SQL commands:

Category Full Form Commands Purpose
DDL Data Definition Language CREATE, ALTER, DROP, TRUNCATE Define/modify structure
DML Data Manipulation Language INSERT, UPDATE, DELETE Manipulate data
DQL Data Query Language SELECT Retrieve data
DCL Data Control Language GRANT, REVOKE Control access/permissions
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT Manage transactions

2. MySQL Data Types

Category Data Type Description
Numeric INT Whole number (4 bytes)
BIGINT Large whole number (8 bytes)
TINYINT Very small integer (1 byte); often used for booleans (0/1)
DECIMAL(p,s) / NUMERIC(p,s) Exact fixed-point number — p = total digits, s = digits after decimal
FLOAT, DOUBLE Approximate floating-point numbers
String CHAR(n) Fixed-length string, padded with spaces
VARCHAR(n) Variable-length string, up to n characters
TEXT Large variable-length text (up to 65,535 chars)
ENUM('a','b',...) String from a predefined list of values
Date/Time DATE Date only (YYYY-MM-DD)
TIME Time only (HH:MM:SS)
DATETIME Date and time combined
TIMESTAMP Date/time, auto-updates, timezone-aware, range 1970–2038
YEAR Year only
Other BOOLEAN Alias for TINYINT(1) (0 = false, 1 = true)
BLOB Binary Large Object — for storing binary data (images, files)
JSON Native JSON document storage (MySQL 5.7.8+)

Example — choosing appropriate data types:

CREATE TABLE products (
    product_id INT,
    name VARCHAR(100),
    price DECIMAL(10,2),      -- e.g., 99999999.99
    in_stock BOOLEAN,
    added_on DATE,
    description TEXT
);

3. Database & Table Basics (DDL)

3.1 Creating & Selecting a Database

CREATE DATABASE company_db;
SHOW DATABASES;              -- lists all databases
USE company_db;              -- selects the database to work with
DROP DATABASE company_db;    -- deletes the database entirely (irreversible!)

3.2 Creating a Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    salary DECIMAL(10,2) DEFAULT 0.00,
    dept_id INT,
    hire_date DATE
);
  • AUTO_INCREMENT – automatically generates a unique, increasing number for each new row (typically used with the primary key).
  • NOT NULL – the column cannot be left empty.
  • DEFAULT – value used automatically if none is provided.

3.3 Viewing Table Structure

DESCRIBE employees;     -- or: DESC employees;
SHOW TABLES;             -- lists all tables in current database
SHOW CREATE TABLE employees;   -- shows the exact CREATE statement used

3.4 Altering a Table

ALTER TABLE employees ADD COLUMN phone VARCHAR(15);           -- add a column
ALTER TABLE employees DROP COLUMN phone;                       -- remove a column
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);      -- change column type
ALTER TABLE employees CHANGE COLUMN first_name fname VARCHAR(50);  -- rename + retype
ALTER TABLE employees RENAME TO staff;                          -- rename the table

3.5 Dropping & Truncating

Command Effect
DROP TABLE employees; Deletes the table and its structure completely
TRUNCATE TABLE employees; Deletes all rows but keeps the table structure; resets AUTO_INCREMENT; faster than DELETE
DELETE FROM employees; Deletes all rows one by one (can be rolled back within a transaction; slower) 
TRUNCATE TABLE employees;   -- empties the table instantly, keeps structure

4. Data Manipulation (DML)

4.1 INSERT — Adding Data

-- Inserting a single row (all columns, in order)
INSERT INTO employees VALUES (1, 'Amit', 'Sharma', 'amit@company.com', 55000.00, 10, '2022-03-15');

-- Inserting specific columns (recommended — more readable and safer)
INSERT INTO employees (first_name, last_name, email, salary, dept_id, hire_date)
VALUES ('Priya', 'Verma', 'priya@company.com', 62000.00, 20, '2023-01-10');

-- Inserting multiple rows at once
INSERT INTO employees (first_name, last_name, email, salary, dept_id, hire_date) VALUES
('Rahul', 'Gupta', 'rahul@company.com', 48000.00, 10, '2021-07-01'),
('Sneha', 'Rao', 'sneha@company.com', 71000.00, 30, '2020-11-20');

4.2 SELECT — Retrieving Data

SELECT * FROM employees;                          -- all columns, all rows
SELECT first_name, salary FROM employees;         -- specific columns
SELECT DISTINCT dept_id FROM employees;           -- unique values only
SELECT first_name AS "First Name" FROM employees; -- column alias for display

4.3 UPDATE — Modifying Data

UPDATE employees
SET salary = 60000.00
WHERE emp_id = 3;

-- Updating multiple columns at once
UPDATE employees
SET salary = salary * 1.10, dept_id = 20      -- 10% raise
WHERE dept_id = 10;

Caution: Omitting the WHERE clause in an UPDATE statement updates every row in the table.

4.4 DELETE — Removing Data

DELETE FROM employees WHERE emp_id = 5;
DELETE FROM employees WHERE salary < 30000;

Caution: Omitting WHERE in a DELETE statement deletes every row in the table (structure remains, unlike DROP).

5. Constraints

Constraints enforce rules on the data allowed in a table, ensuring accuracy and reliability.

Constraint Purpose
PRIMARY KEY Uniquely identifies each row; implies NOT NULL + UNIQUE
FOREIGN KEY Enforces a link between two tables' data (referential integrity)
UNIQUE Ensures all values in a column are different
NOT NULL Ensures a column cannot have a NULL (empty) value
DEFAULT Provides a default value when none is specified
CHECK Ensures values in a column satisfy a specific condition
AUTO_INCREMENT Automatically generates a unique sequential number

Example — table with multiple constraints:

CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) CHECK (salary >= 0),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Foreign key referential actions:

Action Effect when the referenced (parent) row is deleted/updated
CASCADE Automatically deletes/updates matching child rows
SET NULL Sets the foreign key column in child rows to NULL
RESTRICT / NO ACTION Prevents the delete/update if matching child rows exist (default)

Adding a constraint after table creation:

ALTER TABLE employees ADD CONSTRAINT fk_dept
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

6. Operators & the WHERE Clause

The WHERE clause filters rows based on a condition.

6.1 Comparison Operators

SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE salary != 60000;   -- or <>

6.2 Logical Operators

SELECT * FROM employees WHERE salary > 50000 AND dept_id = 10;
SELECT * FROM employees WHERE dept_id = 10 OR dept_id = 20;
SELECT * FROM employees WHERE NOT dept_id = 10;

6.3 Special Operators

Operator Purpose Example
BETWEEN...AND Value within a range (inclusive) salary BETWEEN 40000 AND 60000
IN (...) Value matches any in a list dept_id IN (10, 20, 30)
LIKE Pattern matching with wildcards first_name LIKE 'A%'
IS NULL / IS NOT NULL Checks for NULL values email IS NULL

Wildcards used with LIKE: % matches any number of characters, _ matches exactly one character.

SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
SELECT * FROM employees WHERE dept_id IN (10, 20);
SELECT * FROM employees WHERE first_name LIKE 'A%';     -- starts with 'A'
SELECT * FROM employees WHERE first_name LIKE '%a';     -- ends with 'a'
SELECT * FROM employees WHERE first_name LIKE '_a%';    -- 'a' as second letter
SELECT * FROM employees WHERE email IS NULL;

7. Sorting & Limiting Results

7.1 ORDER BY

SELECT * FROM employees ORDER BY salary;             -- ascending (default)
SELECT * FROM employees ORDER BY salary DESC;         -- descending
SELECT * FROM employees ORDER BY dept_id, salary DESC; -- multi-column sort

7.2 LIMIT (and OFFSET)

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;          -- top 5 highest paid
SELECT * FROM employees ORDER BY salary DESC LIMIT 5 OFFSET 5; -- next 5 (pagination, rows 6-10)
SELECT * FROM employees LIMIT 10, 5;   -- alternate syntax: skip 10, take 5

8. Aggregate Functions & Grouping

8.1 Aggregate Functions

Function Purpose
COUNT() Number of rows
SUM() Total of a numeric column
AVG() Average value
MIN() Smallest value
MAX() Largest value 
SELECT COUNT(*) FROM employees;                  -- total number of employees
SELECT AVG(salary) FROM employees;                -- average salary
SELECT MAX(salary), MIN(salary) FROM employees;   -- highest and lowest salary

8.2 GROUP BY

Groups rows sharing the same value(s) so aggregate functions can be applied per group.

SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

Sample Output:

dept_id | emp_count | avg_salary
--------|-----------|------------
10      | 2         | 51500.00
20      | 1         | 68200.00
30      | 1         | 71000.00

8.3 HAVING

Filters groups (after aggregation) — unlike WHERE, which filters individual rows before aggregation.

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 55000;

WHERE vs HAVING:

WHERE HAVING
Filters rows before grouping Filters groups after aggregation
Cannot use aggregate functions Can use aggregate functions
WHERE salary > 50000 HAVING AVG(salary) > 50000

9. Joins

Joins combine rows from two or more tables based on a related column.

Sample tables used in examples below:

-- employees: emp_id, first_name, dept_id
-- departments: dept_id, dept_name

9.1 INNER JOIN

Returns only rows that have matching values in both tables.

SELECT e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

9.2 LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, plus matching rows from the right table (or NULL if no match).

SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- Employees with no matching department still appear, with dept_name = NULL

9.3 RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table, plus matching rows from the left (or NULL if no match).

SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
-- Departments with no employees still appear, with first_name = NULL

9.4 FULL OUTER JOIN

Returns all rows from both tables, matched where possible. MySQL has no native FULL OUTER JOIN — it's simulated using UNION:

SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

9.5 SELF JOIN

A table joined with itself — useful for hierarchical data (e.g., an employee-manager relationship).

-- employees table has a 'manager_id' column referencing emp_id of the same table
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

9.6 CROSS JOIN

Returns the Cartesian product — every row of the first table combined with every row of the second. Rarely used directly except for generating combinations.

SELECT e.first_name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-- If employees has 4 rows and departments has 3, result has 12 rows

Joins visual summary:

Join Type Returns
INNER JOIN Only matching rows in both tables
LEFT JOIN All left rows + matches from right (NULL if none)
RIGHT JOIN All right rows + matches from left (NULL if none)
FULL OUTER JOIN All rows from both (simulated via UNION in MySQL)
SELF JOIN A table joined with itself
CROSS JOIN Every combination of rows (Cartesian product)

10. Subqueries

A subquery is a query nested inside another query, executed first so its result can be used by the outer query.

10.1 Subquery in WHERE

SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Employees earning more than the company-wide average

10.2 Subquery with IN

SELECT first_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name = 'Sales');

10.3 Subquery in FROM (Derived Table)

SELECT dept_id, avg_sal
FROM (
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY dept_id
) AS dept_avg
WHERE avg_sal > 50000;

10.4 Correlated Subquery

A subquery that references a column from the outer query — it re-executes for each row of the outer query.

SELECT first_name, salary, dept_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id      -- references outer query's row
);
-- Employees earning more than their own department's average

10.5 EXISTS

Checks whether a subquery returns any rows at all (more efficient than IN for large datasets in many cases).

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- Only departments that have at least one employee

11. Set Operations (UNION)

Combines the results of two or more SELECT statements. Both queries must have the same number of columns with compatible data types.

SELECT first_name FROM employees WHERE dept_id = 10
UNION
SELECT first_name FROM employees WHERE dept_id = 20;
-- UNION removes duplicate rows automatically

SELECT first_name FROM employees WHERE dept_id = 10
UNION ALL
SELECT first_name FROM employees WHERE dept_id = 20;
-- UNION ALL keeps duplicates, and is faster (no de-duplication step)

12. Views

A view is a virtual table based on the result of a stored SELECT query. It doesn't store data itself — it dynamically reflects the underlying table(s) each time it's queried.

Why use views?

  • Simplify complex, frequently-used queries.
  • Restrict access to specific columns/rows for security (e.g., hide salary from certain users).
  • Provide a consistent, simplified interface over changing table structures.
CREATE VIEW high_earners AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;

-- Using the view just like a table
SELECT * FROM high_earners;

-- Updating a (simple) view
UPDATE high_earners SET salary = 65000 WHERE first_name = 'Sneha';
-- Note: only simple views (single table, no aggregation/GROUP BY) are updatable

DROP VIEW high_earners;

13. Indexes

An index is a special lookup structure that speeds up data retrieval, at the cost of extra storage and slightly slower writes (INSERT/UPDATE/DELETE, since indexes must also be updated).

CREATE INDEX idx_lastname ON employees(last_name);      -- single-column index
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);   -- composite index
CREATE UNIQUE INDEX idx_email ON employees(email);       -- enforces uniqueness too

SHOW INDEX FROM employees;    -- view all indexes on a table
DROP INDEX idx_lastname ON employees;

When to use indexes:

  • Columns frequently used in WHERE, JOIN, or ORDER BY clauses.
  • Not recommended on columns that change very frequently or have very few distinct values (e.g., a boolean flag), since the overhead may outweigh the benefit.

A PRIMARY KEY automatically creates a unique index; foreign keys are often indexed automatically by InnoDB as well.

14. String, Numeric & Date Functions

14.1 String Functions

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
SELECT UPPER(first_name), LOWER(last_name) FROM employees;
SELECT LENGTH(first_name) FROM employees;              -- number of characters
SELECT SUBSTRING(first_name, 1, 3) FROM employees;      -- first 3 characters
SELECT TRIM('  Hello  ');                                -- removes leading/trailing spaces
SELECT REPLACE(email, '@company.com', '@newdomain.com') FROM employees;

14.2 Numeric Functions

SELECT ROUND(salary, 0) FROM employees;    -- rounds to nearest whole number
SELECT CEIL(45.2);          -- 46
SELECT FLOOR(45.8);         -- 45
SELECT ABS(-25);            -- 25
SELECT MOD(10, 3);          -- 1 (remainder)

14.3 Date & Time Functions

SELECT NOW();                                     -- current date and time
SELECT CURDATE();                                  -- current date only
SELECT DATEDIFF('2026-12-31', hire_date) FROM employees;   -- days between two dates
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees; -- add a year
SELECT YEAR(hire_date), MONTH(hire_date) FROM employees;
SELECT DATE_FORMAT(hire_date, '%d-%m-%Y') FROM employees;   -- custom format

15. Stored Procedures & Functions

15.1 Stored Procedures

A stored procedure is a named, precompiled collection of SQL statements stored in the database, which can be executed (called) repeatedly.

Note on syntax: MySQL uses ; to end statements, so DELIMITER is used to temporarily change the statement terminator while defining a procedure (which itself contains semicolons).

DELIMITER $$

CREATE PROCEDURE GetEmployeesByDept(IN p_dept_id INT)
BEGIN
    SELECT * FROM employees WHERE dept_id = p_dept_id;
END $$

DELIMITER ;

-- Calling the procedure
CALL GetEmployeesByDept(10);

Procedure with IN, OUT, and logic:

DELIMITER $$

CREATE PROCEDURE GiveRaise(IN p_emp_id INT, IN p_percent DECIMAL(5,2), OUT p_new_salary DECIMAL(10,2))
BEGIN
    UPDATE employees
    SET salary = salary + (salary * p_percent / 100)
    WHERE emp_id = p_emp_id;

    SELECT salary INTO p_new_salary FROM employees WHERE emp_id = p_emp_id;
END $$

DELIMITER ;

CALL GiveRaise(1, 10, @result);
SELECT @result;    -- displays the updated salary

15.2 Stored Functions

Unlike procedures, a function must return exactly one value and can be used directly inside a SELECT statement.

DELIMITER $$

CREATE FUNCTION AnnualSalary(monthly DECIMAL(10,2))
RETURNS DECIMAL(12,2)
DETERMINISTIC
BEGIN
    RETURN monthly * 12;
END $$

DELIMITER ;

SELECT first_name, salary, AnnualSalary(salary) AS yearly_salary FROM employees;

Procedure vs Function:

Stored Procedure Stored Function
May return zero, one, or multiple values (via OUT params) Must return exactly one value
Called using CALL Used directly inside SQL expressions (e.g., SELECT)
Can contain DML (INSERT/UPDATE/DELETE) freely More restricted; typically used for computation

16. Triggers

A trigger is a stored program that automatically executes in response to a specific event (INSERT, UPDATE, DELETE) on a table.

Syntax:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- trigger logic
END;

Example — logging salary changes into an audit table:

CREATE TABLE salary_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_on DATETIME
);

DELIMITER $$

CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_on)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
    END IF;
END $$

DELIMITER ;
  • OLD.column – refers to the row's value before the change (available in UPDATE/DELETE triggers).
  • NEW.column – refers to the row's value after the change (available in INSERT/UPDATE triggers).
UPDATE employees SET salary = 70000 WHERE emp_id = 1;
SELECT * FROM salary_audit;   -- automatically logged by the trigger

17. Transactions & ACID Properties

A transaction is a sequence of one or more SQL operations executed as a single logical unit of work — either all operations succeed, or none do.

17.1 ACID Properties

Property Meaning
Atomicity All operations in a transaction succeed, or none are applied
Consistency The database moves from one valid state to another, respecting all rules/constraints
Isolation Concurrent transactions don't interfere with each other
Durability Once committed, changes persist even after a system crash

17.2 Transaction Control Statements

START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 1;   -- debit
UPDATE accounts SET balance = balance + 5000 WHERE acc_id = 2;   -- credit

COMMIT;   -- makes all changes permanent

Rolling back on error:

START TRANSACTION;

UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 1;
-- suppose something goes wrong here (e.g., insufficient balance detected)
ROLLBACK;   -- undoes all changes made since START TRANSACTION

Using SAVEPOINT for partial rollback:

START TRANSACTION;

UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
SAVEPOINT sp1;

UPDATE accounts SET balance = balance - 500 WHERE acc_id = 1;
ROLLBACK TO sp1;    -- undoes only the second update, keeps the first

COMMIT;

Transactions require a storage engine that supports them — InnoDB does; MyISAM does not.

18. User Management & Privileges

-- Creating a new user
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123';

-- Granting specific privileges
GRANT SELECT, INSERT, UPDATE ON company_db.employees TO 'app_user'@'localhost';

-- Granting all privileges on a database
GRANT ALL PRIVILEGES ON company_db.* TO 'app_user'@'localhost';

-- Applying privilege changes
FLUSH PRIVILEGES;

-- Viewing a user's privileges
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoking privileges
REVOKE INSERT ON company_db.employees FROM 'app_user'@'localhost';

-- Removing a user
DROP USER 'app_user'@'localhost';

19. Normalization

Normalization is the process of organizing data to minimize redundancy and avoid update/insertion/deletion anomalies, by dividing large tables into smaller, related ones.

Normal Form Rule
1NF (First Normal Form) Each column holds atomic (indivisible) values; no repeating groups/arrays in a single column
2NF (Second Normal Form) Must satisfy 1NF; every non-key column depends on the entire primary key (relevant for composite keys)
3NF (Third Normal Form) Must satisfy 2NF; no transitive dependency — non-key columns depend only on the primary key, not on other non-key columns
BCNF (Boyce-Codd Normal Form) A stricter version of 3NF; every determinant must be a candidate key

Example — an unnormalized table:

OrderID | CustomerName | Product1  | Product2
1       | Amit         | Pen       | Notebook

This violates 1NF (repeating "Product" columns). Normalized into 1NF:

OrderID | CustomerName | Product
1       | Amit         | Pen
1       | Amit         | Notebook

Example illustrating 2NF and 3NF:

Unnormalized (with a transitive dependency — DeptName depends on DeptID, not directly on EmpID):

EmpID | EmpName | DeptID | DeptName

Normalized into two related tables (removing the transitive dependency, satisfying 3NF):

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Benefits of normalization: less data duplication, smaller storage footprint, fewer anomalies when inserting/updating/deleting data. Trade-off: more tables often means more JOINs are needed to reconstruct full information — sometimes denormalization is deliberately used in read-heavy systems (e.g., reporting/analytics) to trade some redundancy for query speed.

20. Advanced Topics

20.1 Common Table Expressions (CTEs) — WITH clause

A CTE creates a named, temporary result set that exists only for the duration of a single query — improves readability over nested subqueries. (MySQL 8.0+)

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT e.first_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

Recursive CTE (e.g., traversing an employee-manager hierarchy):

WITH RECURSIVE org_chart AS (
    SELECT emp_id, first_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL          -- top-level (CEO)

    UNION ALL

    SELECT e.emp_id, e.first_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT * FROM org_chart ORDER BY level;

20.2 Window Functions (MySQL 8.0+)

Unlike GROUP BY, window functions perform calculations across a set of rows related to the current row, without collapsing rows into groups.

SELECT
    first_name, dept_id, salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary
FROM employees;
  • PARTITION BY – divides rows into groups (like GROUP BY, but rows aren't collapsed).
  • RANK() – assigns a rank within each partition, with ties sharing a rank and a gap afterward.
  • Other common window functions: ROW_NUMBER(), DENSE_RANK(), LEAD(), LAG().
SELECT first_name, salary,
    LAG(salary) OVER (ORDER BY salary) AS previous_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;

20.3 JSON Support (MySQL 5.7.8+)

CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    config JSON
);

INSERT INTO settings (config) VALUES ('{"theme": "dark", "notifications": true}');

SELECT config->>'$.theme' AS theme FROM settings;    -- extracts a JSON value
UPDATE settings SET config = JSON_SET(config, '$.theme', 'light') WHERE id = 1;

20.4 Full-Text Search

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
);

SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

21. Performance: EXPLAIN & Query Optimization

EXPLAIN shows how MySQL plans to execute a query — which indexes it will use, how many rows it expects to scan, and the join order — essential for diagnosing slow queries.

EXPLAIN SELECT * FROM employees WHERE dept_id = 10;

Key columns in EXPLAIN output:

Column Meaning
type Join/access type (ALL = full table scan — usually bad; ref/range/const = using an index — good)
possible_keys Indexes MySQL could use
key The index MySQL actually chose
rows Estimated number of rows MySQL expects to examine

Common optimization tips:

  • Add indexes on columns used in WHERE, JOIN, and ORDER BY.
  • Avoid SELECT * — retrieve only the columns you need.
  • Avoid applying functions to indexed columns in WHERE (e.g., WHERE YEAR(hire_date) = 2024 prevents index use; prefer a range: WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31').
  • Use LIMIT when you only need a subset of results.
  • Normalize to reduce redundancy, but consider selective denormalization for heavy read/reporting workloads.

22. Backup & Restore

Using mysqldump (command line, outside the MySQL prompt):

# Backup a single database to a .sql file
mysqldump -u root -p company_db > company_db_backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases_backup.sql

# Restore from a backup file
mysql -u root -p company_db < company_db_backup.sql

Quick Revision Summary

Concept One-line takeaway
RDBMS & MySQL Open-source relational database using SQL; InnoDB is the standard transactional engine
Data Types INT, DECIMAL, VARCHAR, TEXT, DATE, DATETIME, JSON, etc. — pick based on precision and size needs
DDL CREATE, ALTER, DROP, TRUNCATE — define/modify structure
DML INSERT, UPDATE, DELETE — manipulate data; always use WHERE carefully
Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT enforce data integrity
WHERE Operators =, >, <, BETWEEN, IN, LIKE, IS NULL filter rows
Sorting/Limiting ORDER BY for sequence, LIMIT/OFFSET for pagination
Aggregates & Grouping COUNT, SUM, AVG, MIN, MAX with GROUP BY; filter groups with HAVING
Joins INNER, LEFT, RIGHT, FULL (via UNION), SELF, CROSS combine related tables
Subqueries Nested queries — scalar, IN, derived table, correlated, EXISTS
Views Virtual tables based on a stored SELECT, useful for simplification and security
Indexes Speed up reads at the cost of extra storage and slower writes
Functions Rich built-in string/numeric/date functions for transforming data
Stored Procedures/Functions Reusable, precompiled SQL logic stored in the database
Triggers Auto-execute logic on INSERT/UPDATE/DELETE events, using OLD/NEW
Transactions START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT — all-or-nothing operations (ACID)
User Management CREATE USER, GRANT, REVOKE control who can do what
Normalization 1NF → 2NF → 3NF → BCNF reduce redundancy and anomalies
CTEs & Window Functions WITH clause and OVER() enable advanced, readable analytical queries
JSON & Full-Text Search Native support for semi-structured data and text search
EXPLAIN Diagnoses query performance; guides index and query design decisions
Backup mysqldump for exporting/importing databases

End of MySQL Notes