MySQL Tutorial

Developer Notes • Relational Databases & SQL

1. MySQL — Definition
MySQL is an open-source, relational database management system (RDBMS) that stores data
in structured tables made of rows and columns. It uses Structured Query Language (SQL) to
create, read, update, and delete data, making it one of the most widely adopted database
systems in the world.

MySQL organizes data into databases, which contain one or more tables. Each table has a
defined schema (column names and data types), and relationships between tables can be
established using keys. MySQL is maintained by Oracle Corporation and is available under both
open-source and commercial licenses.

MySQL follows the ACID model (Atomicity, Consistency, Isolation, Durability) when using the
InnoDB storage engine, ensuring data integrity even in multi-user environments. It supports
transactions, stored procedures, triggers, views, and full-text indexing.

2. Problem It Solves
MySQL exists to provide a reliable, structured, and queryable system for storing and managing
large volumes of data — something flat files and early data storage methods could not efficiently
offer.

Problems Before MySQL / Relational Databases

• Data was stored in flat files (CSV, text), making it nearly impossible to query or filter
efficiently
• No standard way to enforce relationships between different types of data
• Concurrent access by multiple users caused data corruption and inconsistency
• Manual data management required custom parsing code for every application

Limitations Developers Faced

• No built-in support for searching, sorting, or aggregating data at scale
• Duplicate data was stored everywhere — updating one record meant updating it in
dozens of files
• File-based storage had no concept of access control or data validation rules
 • Scaling to thousands of records required writing complex and fragile custom logic

Why MySQL Became Necessary

• The web boom of the 1990s required fast, reliable data storage for millions of users
• Developers needed a standard query language (SQL) that worked across teams and
platforms
• Transactional applications (banking, ecommerce) required atomic, consistent operations
• Free, open-source licensing made MySQL accessible for startups and large enterprises
alike

Where MySQL Is Commonly Used

• Web applications — as the database layer in LAMP stacks (Linux, Apache, MySQL, PHP)
• Ecommerce platforms — storing products, orders, customers, and inventory
• Content management systems — WordPress, Drupal, and Joomla all use MySQL
• Mobile and API backends — storing user data, sessions, and logs
• Analytics systems — tracking events, aggregating metrics, reporting dashboards

3. Detailed Explanation

Main Idea Behind MySQL

• MySQL stores data in tables, which are similar to spreadsheets but with strict schemas
• Every table has columns (data types/attributes) and rows (individual records)
• Relationships between tables are handled through foreign keys, allowing normalized data
models
• SQL is the language used to interact with MySQL — it is declarative, meaning you
describe what you want, not how to get it

How Developers Interact With MySQL

• Through a command-line client: mysql -u root -p
• Through GUI tools: MySQL Workbench, TablePlus, DBeaver, phpMyAdmin
• Through programming language drivers: [Link] (mysql2), Python (PyMySQL,
SQLAlchemy), PHP (PDO), Java (JDBC)
• Through ORM frameworks that abstract SQL into object-oriented code (e.g., Sequelize,
Hibernate, Prisma)
Important Rules and Behaviors
• SQL keywords are case-insensitive, but table/column names may be case-sensitive
depending on the OS
• Every SQL statement ends with a semicolon (;) in the CLI
• NULL means the absence of a value — it is not zero or an empty string
• Primary keys uniquely identify rows; foreign keys link tables together
• Indexes speed up SELECT queries but slow down INSERT/UPDATE/DELETE operations
• Transactions allow multiple operations to be grouped — if one fails, all can be rolled back

When MySQL Is Typically Used

• When your application needs structured, relational data
• When you need to run complex queries with filtering, aggregation, or joining across tables
• When ACID compliance and data integrity are priorities
• When your team needs a mature, well-documented, widely supported database

How It Affects Application Development

• Determines the data model — developers must design tables and relationships upfront
• Affects performance — poorly designed schemas or missing indexes cause slow queries
• Influences backend architecture — SQL queries shape API response structures
• Requires migration management when schema changes are needed in production

4. Core Concepts

4.1 — Database
A database in MySQL is a named container that holds a collection of tables. Developers create
separate databases for different applications or environments (e.g., myapp_dev,
myapp_production). All tables, views, stored procedures, and functions live inside a specific
database. You select which database to use with the USE command.

4.2 — Table
A table is the fundamental unit of data storage in MySQL. It has a fixed schema — a set of
columns, each with a name and data type. Rows represent individual records. Tables enforce
constraints like NOT NULL, UNIQUE, DEFAULT, and FOREIGN KEY to maintain data integrity.
4.3 — Primary Key
A primary key is a column (or combination of columns) that uniquely identifies each row in a
table. No two rows can have the same primary key value, and it cannot be NULL. MySQL
automatically indexes the primary key for fast lookups. INT AUTO_INCREMENT is the most
common primary key pattern.

4.4 — Foreign Key

A foreign key is a column in one table that references the primary key of another table. It
enforces referential integrity — you cannot insert a foreign key value that does not exist in the
parent table. Foreign keys are used to model one-to-many and many-to-many relationships
between entities.

4.5 — SQL Statements (CRUD)

SQL is divided into four main categories for data operations:
• SELECT — Read and query data from one or more tables
• INSERT — Add new rows into a table
• UPDATE — Modify existing rows based on a condition
• DELETE — Remove rows from a table

4.6 — Joins
Joins combine rows from two or more tables based on a related column. INNER JOIN returns
only matching rows. LEFT JOIN returns all rows from the left table and matching rows from the
right. RIGHT JOIN does the opposite. Joins are essential for working with normalized relational
data spread across multiple tables.

4.7 — Indexes
An index is a data structure (usually a B-tree) that MySQL builds on a column to speed up
queries. Without an index, MySQL performs a full table scan to find matching rows. With an
index, it can jump directly to the relevant rows. Primary keys are indexed automatically. You can
create additional indexes on frequently queried columns.

4.8 — Transactions
A transaction is a group of SQL operations treated as a single unit. If any operation in the
transaction fails, the entire group is rolled back — leaving the database unchanged. This
ensures data consistency. Transactions use BEGIN, COMMIT, and ROLLBACK. They are
critical in financial systems, inventory management, and any operation involving multiple
dependent updates.
4.9 — Stored Procedures & Views
Stored procedures are precompiled SQL routines stored in the database that can accept
parameters and be called by name. Views are virtual tables created by a SELECT query — they
simplify complex queries by giving them a reusable alias. Both are used to encapsulate
business logic and reduce code duplication across applications.

4.10 — Data Types

MySQL columns are assigned specific data types that define what kind of data they can hold.
Common types include:
• INT / BIGINT — Whole numbers
• VARCHAR(n) — Variable-length strings up to n characters
• TEXT — Long-form text data
• DATE / DATETIME / TIMESTAMP — Date and time values
• DECIMAL(p, s) — Precise decimal numbers (e.g., currency)
• BOOLEAN / TINYINT(1) — True/false values

5. How It Works (Internal Mechanism)

When a client sends a SQL query to MySQL, the server processes it through a well-defined
pipeline:

• Connection Layer — The MySQL client connects to the server over TCP/IP or a Unix
socket. The server authenticates the user using username, password, and host-based
access controls.
• SQL Parser — The server parses the SQL string into a parse tree, validating syntax and
structure. If there is a syntax error, it is returned at this stage before any execution.
• Query Optimizer — MySQL analyzes the parse tree and builds an execution plan. It
decides which indexes to use, in what order to join tables, and how to filter rows most
efficiently. EXPLAIN shows this plan.
• Storage Engine — The actual reading and writing of data is delegated to a storage
engine. InnoDB is the default and supports transactions, foreign keys, and row-level
locking. MyISAM is older and faster for reads but lacks transactions.
• Buffer Pool — InnoDB uses a memory buffer pool to cache frequently accessed data
pages. Reads from memory are far faster than disk reads, so MySQL tries to serve
queries from the buffer whenever possible.
• Write-Ahead Log (Redo Log) — Before writing data to disk, InnoDB writes the operation
to a redo log. This ensures that if the server crashes mid-write, the operation can be
replayed and completed on restart — guaranteeing durability.
• Result Set — After execution, MySQL returns the result set to the client as rows of data,
which the client driver maps into native language objects (e.g., JavaScript objects, Python
dicts).
6. Syntax
MySQL syntax follows standard SQL with some MySQL-specific extensions. Below are the core
syntax patterns:

6.1 — CREATE DATABASE & TABLE

SQL
CREATE DATABASE database_name;
USE database_name;

CREATE TABLE table_name (

column1 datatype constraints,
column2 datatype constraints,
...
PRIMARY KEY (column1)
);

6.2 — SELECT (Read)

SQL
SELECT column1, column2 -- columns to fetch (* = all)
FROM table_name -- source table
WHERE condition -- filter rows
ORDER BY column1 ASC|DESC -- sort results
LIMIT n -- max rows to return
OFFSET m; -- skip first m rows (pagination)

6.3 — INSERT
SQL
INSERT INTO table_name (col1, col2, col3)
VALUES ('value1', 'value2', value3);

6.4 — UPDATE
SQL
 UPDATE table_name
SET col1 = 'new_value',
col2 = col2 + 1
WHERE condition; -- ALWAYS include WHERE to avoid updating all rows

6.5 — DELETE
SQL
DELETE FROM table_name
WHERE condition; -- ALWAYS include WHERE to avoid deleting all rows

6.6 — JOIN
SQL
SELECT a.col1, b.col2
FROM table_a AS a
INNER JOIN table_b AS b
ON [Link] = b.table_a_id
WHERE [Link] = 'active';

7. Code Example
The following example builds a complete mini database for a blog application with users and
posts tables, inserts sample data, and queries it with a JOIN.

SQL
-- Step 1: Create the database
CREATE DATABASE blog_app;
USE blog_app;

-- Step 2: Create users table

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 3: Create posts table with FK to users

CREATE TABLE posts (
 id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
body TEXT,
published BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Step 4: Insert sample data

INSERT INTO users (name, email) VALUES
('Alice Johnson', 'alice@[Link]'),
('Bob Smith', 'bob@[Link]');

INSERT INTO posts (user_id, title, body, published) VALUES

(1, 'Learning MySQL', 'MySQL is powerful...', TRUE),
(1, 'Draft Post', 'Not ready yet', FALSE),
(2, 'Hello World', 'My first post!', TRUE);

-- Step 5: Query published posts with author names

SELECT
[Link],
[Link] AS author,
[Link],
p.created_at
FROM posts p
INNER JOIN users u ON p.user_id = [Link]
WHERE [Link] = TRUE
ORDER BY p.created_at DESC;

What This Code Does:

• Creates a blog_app database and switches into it
• Creates a users table with an auto-incrementing primary key, name, email (unique), and a
timestamp
• Creates a posts table with a foreign key user_id pointing to [Link] — ON DELETE
CASCADE means if a user is deleted, their posts are automatically deleted too
• Inserts 2 users and 3 posts — one post is a draft (published = FALSE)
• The final SELECT uses an INNER JOIN to combine posts and users, only returns
published posts, and orders by newest first

8. Practical Example
Scenario: Building the backend database for an ecommerce application that tracks customers,
products, and orders.
A developer setting up an online store needs to:
• Store customer profiles — name, email, shipping address
• Store product listings — name, description, price, stock quantity
• Record orders — which customer bought which products, in what quantity, at what price
• Query order history for a customer's dashboard
• Update stock levels after a purchase

The developer creates three tables: customers, products, and order_items. They use a
transaction to ensure that when an order is placed, both the order_items row is inserted AND
the [Link] is decremented atomically. If payment fails halfway through, the ROLLBACK
reverses both changes — no ghost inventory adjustments, no missing order records.

SQL
-- Place an order atomically
START TRANSACTION;

INSERT INTO order_items (customer_id, product_id, quantity, price)

VALUES (42, 7, 2, 29.99);

UPDATE products
SET stock = stock - 2
WHERE id = 7 AND stock >= 2;

-- Check rows affected; if 0, product was out of stock

-- Application checks ROW_COUNT() here

COMMIT; -- or ROLLBACK if payment fails

9. Real-World Example
MySQL powers some of the largest applications on the internet. Here is how it appears in real
systems:

• WordPress (CMS): Every blog post, page, comment, user, and plugin setting is stored in
MySQL tables (wp_posts, wp_users, wp_options, etc.). WordPress uses MySQL as its
entire data layer.

• Twitter (early architecture): Twitter originally used MySQL to store tweets, user
relationships (follows), and timelines. They used sharding (splitting data across many
MySQL servers) to scale to millions of users.
 • Shopify (Ecommerce): Product catalogs, inventory, orders, customer data, and payment
records are all managed with MySQL at the core of Shopify's multi-tenant database
architecture.

• Facebook (early): Facebook used MySQL extensively in its early years, famously
building a distributed query layer on top of thousands of MySQL instances.

• Analytics Dashboards: Internal BI tools query MySQL with GROUP BY and aggregate
functions (SUM, COUNT, AVG) to generate revenue reports, user activity metrics, and
funnel analysis displayed in tools like Metabase or Tableau.

• REST APIs: Most [Link] / Django / Laravel backend APIs connect to MySQL to
handle CRUD operations — GET /users fetches rows, POST /users inserts a row, PUT
/users/:id updates a row, DELETE removes one.

10. Common Mistakes

• Missing WHERE in UPDATE / DELETE: Writing UPDATE users SET active = 0 without
a WHERE clause will update every row in the table. Always double-check WHERE
conditions before running destructive queries.

• Using SELECT * in production: Selecting all columns wastes bandwidth, prevents

index-only scans, and breaks code when columns are added or removed. Always specify
the columns you need.

• Not using indexes on foreign keys: MySQL does not automatically index foreign key
columns on the child table. Queries joining on an unindexed foreign key cause full table
scans and slow down dramatically with large datasets.

• Storing passwords as plain text: Never store raw passwords in a VARCHAR column.
Always hash passwords using bcrypt or Argon2 in your application before inserting into
the database.

• Using VARCHAR for fixed-length data: Phone numbers, country codes, and status
flags with fixed formats should use CHAR(n), which is faster for comparison and uses less
storage when values are consistently the same length.

• Ignoring SQL injection: Concatenating user input directly into SQL strings (e.g.,
'SELECT * FROM users WHERE name = ' + userInput) is a critical security vulnerability.
Always use prepared statements with parameterized queries.

• Not backing up before schema migrations: Running ALTER TABLE on a production

database without a backup is dangerous. Large tables can lock during migrations,
causing downtime and data loss if something goes wrong.
 • Using MyISAM instead of InnoDB: MyISAM does not support transactions or foreign
keys. Unless you have a very specific read-heavy use case, always use InnoDB (the
default engine since MySQL 5.5).

11. Best Practices

• Always use prepared statements: Use parameterized queries in your application driver
to prevent SQL injection. Example in [Link]: [Link]('SELECT * FROM users WHERE
id = ?', [userId])

• Name columns and tables clearly: Use snake_case, plural table names (users, orders,
products), and descriptive column names. Avoid abbreviations that obscure meaning.

• Add indexes strategically: Index columns used in WHERE, JOIN, and ORDER BY
clauses. Use EXPLAIN on slow queries to identify missing indexes. Do not over-index —
each index slows down write operations.

• Normalize your schema: Avoid storing repeated data across rows. Use foreign keys to
reference shared data. This reduces storage and ensures that updates only need to
happen in one place.

• Use transactions for multi-step operations: Any time you need two or more related
writes to succeed together, wrap them in a transaction. This prevents partial updates from
corrupting data.

• Set appropriate data types: Use the smallest data type that fits your data. Use
DECIMAL for currency (not FLOAT, which has precision errors). Use TIMESTAMP for
times that need timezone awareness.

• Backup regularly and test restores: Schedule automated backups using mysqldump or
a managed cloud backup service. Periodically verify that restores work correctly —
untested backups are not real backups.

• Monitor slow queries: Enable the MySQL slow query log to identify and optimize queries
taking longer than a threshold. Use EXPLAIN and EXPLAIN ANALYZE to understand
query execution plans.

• Use connection pooling in applications: Opening a new database connection on every

request is expensive. Use a connection pool (e.g., mysql2/promise pool in [Link]) to
reuse connections efficiently.
12. Interview Notes

Interview Tip
MySQL interviews often test both theoretical knowledge and practical query-writing skills.
Be prepared to write JOINs, explain indexes, describe normalization, and debug slow queries.

Commonly Asked Interview Questions

Q1: What is the difference between INNER JOIN, LEFT JOIN, and RIGHT JOIN?
• INNER JOIN returns only rows that have a matching value in BOTH tables
• LEFT JOIN returns ALL rows from the left table, and matching rows from the right — non-
matching right rows appear as NULL
• RIGHT JOIN is the mirror: ALL rows from the right table, matching from the left
• FULL OUTER JOIN (not natively supported in MySQL but achievable with UNION)
returns all rows from both tables

Q2: What is the difference between WHERE and HAVING?

• WHERE filters rows BEFORE grouping — it cannot reference aggregate functions like
SUM or COUNT
• HAVING filters groups AFTER a GROUP BY — it is used to filter aggregated results
• Example: WHERE price > 50 filters individual rows; HAVING COUNT(*) > 5 filters groups

Q3: What is an index? When should you use one?

• An index is a separate data structure that maps column values to their row locations,
enabling fast lookups
• Use indexes on columns frequently used in WHERE conditions, JOIN ON clauses, and
ORDER BY
• Avoid over-indexing — every index consumes storage and slows
INSERT/UPDATE/DELETE

Q4: What is database normalization?

• Normalization is the process of organizing tables to reduce data redundancy and improve
integrity
• 1NF: Each column holds atomic values, no repeating groups
• 2NF: No partial dependency — all non-key columns depend on the entire primary key
• 3NF: No transitive dependency — non-key columns depend only on the primary key, not
on other non-key columns
Q5: What is a transaction and what does ACID mean?
• A transaction is a sequence of SQL operations executed as a single unit
• Atomicity — all operations succeed or all are rolled back
• Consistency — the database moves from one valid state to another
• Isolation — concurrent transactions do not interfere with each other
• Durability — committed transactions persist even after a system crash

Q6: What is the difference between DELETE, TRUNCATE, and DROP?

Command Behavior
DELETE Removes specific rows based on WHERE;
logged, can be rolled back
TRUNCATE Removes ALL rows instantly; not logged per-row;
cannot be rolled back in most cases; resets
AUTO_INCREMENT
DROP Deletes the entire table structure and all its data
permanently

Q7: How do you prevent SQL injection?

• Use prepared statements / parameterized queries — never concatenate user input into
SQL strings
• Example ([Link] mysql2): [Link]('SELECT * FROM users WHERE id = ?', [id])
• Use ORMs (Sequelize, Prisma) that handle parameterization automatically
• Validate and sanitize input on the application layer before it reaches the database

Q8: What is the difference between CHAR and VARCHAR?

• CHAR(n) always uses exactly n bytes regardless of the stored string length — fast for
fixed-length data
• VARCHAR(n) uses only as many bytes as needed plus 1-2 bytes for the length — more
efficient for variable-length data
• Use CHAR for status codes, country codes, or fixed-format strings; VARCHAR for names,
emails, and descriptions

Q9: How does EXPLAIN work?

• EXPLAIN prefixed to a SELECT statement shows MySQL's execution plan without
running the query
• Key columns to inspect: type (ALL = full scan is bad), key (which index is used), rows
(estimated rows scanned)
 • type values from best to worst: system > const > eq_ref > ref > range > index > ALL
• Use EXPLAIN ANALYZE (MySQL 8.0+) to get actual execution statistics, not just
estimates

Q10: What is the difference between MyISAM and InnoDB?

Feature InnoDB vs MyISAM

Transactions InnoDB: Yes (ACID) | MyISAM: No
Foreign Keys InnoDB: Yes | MyISAM: No
Locking InnoDB: Row-level | MyISAM: Table-level
Crash Recovery InnoDB: Yes (redo log) | MyISAM: Manual repair
Use Case InnoDB: Almost always | MyISAM: Legacy / read-
only tables

MySQL Developer Notes • For Interview & Reference Use • Paste-ready for Notion