MySQL Documentation
The most widely used open-source relational database — tables, joins, indexes, transactions and constraints.
MySQL is the world's most popular open-source relational database. It powers WordPress, countless PHP/Laravel and Node.js apps, and big platforms like Facebook (as part of their stack) and YouTube's early days. It stores data in tables, speaks SQL, and with the InnoDB engine supports transactions, foreign keys and row-level locking.
1. Introduction & Installation
- Windows: MySQL Installer (Server + Workbench + Shell), or XAMPP (comes with MariaDB, a MySQL fork).
- macOS:
brew install mysqlthenbrew services start mysql. - Linux:
sudo apt install mysql-serverthensudo mysql_secure_installation. - Cloud: free tiers on Aiven, PlanetScale-style services, or Docker:
docker run -e MYSQL_ROOT_PASSWORD=secret -p 3306:3306 mysql:8.
mysql --version
mysql -u root -p # connect (asks for the password)
mysql -u root -p shop < dump.sql # run a SQL file against database "shop"
mysqldump -u root -p shop > backup.sql
Inside the client:
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE shop;
SHOW DATABASES;
SHOW TABLES;
SELECT VERSION();
⚠️ Common error:
ERROR 1045 (28000): Access denied for user 'root'@'localhost'— wrong password, or (on Ubuntu) root uses socket auth: runsudo mysqlinstead.
2. Tables & Data Types
All examples use this sample shop database:
-- Sample database: customers, products and orders
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
email VARCHAR(120) NOT NULL UNIQUE,
city VARCHAR(40),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
category ENUM('Books', 'Electronics', 'Clothing') NOT NULL,
price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
stock INT NOT NULL DEFAULT 0
);
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
product_id INT NOT NULL,
qty INT NOT NULL DEFAULT 1,
ordered_on DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO customers (name, email, city) VALUES
('Asha', 'asha@example.com', 'Pune'),
('Ravi', 'ravi@example.com', 'Delhi'),
('Neha', 'neha@example.com', 'Pune'),
('Kabir', 'kabir@example.com', 'Mumbai');
INSERT INTO products (name, category, price, stock) VALUES
('Clean Code', 'Books', 450.00, 10),
('Earbuds', 'Electronics', 1299.00, 25),
('Hoodie', 'Clothing', 899.00, 5),
('Atomic Habits', 'Books', 399.00, 0),
('USB-C Cable', 'Electronics', 199.00, 100);
INSERT INTO orders (customer_id, product_id, qty, ordered_on) VALUES
(1, 1, 1, '2026-09-01'),
(1, 2, 2, '2026-09-03'),
(2, 5, 3, '2026-09-03'),
(3, 1, 2, '2026-09-10'),
(2, 3, 1, '2026-10-02'),
(1, 5, 1, '2026-10-05');
DESCRIBE products;
Output:
Field | Type | Null | Key | Default | Extra
id | int | NO | PRI | NULL | auto_increment
name | varchar(80) | NO | | NULL |
category | enum('Books','Electronics','Clothing') | NO | | NULL |
price | decimal(10,2) | NO | | NULL |
stock | int | NO | | 0 |
| Type | Use |
|---|---|
INT, BIGINT, TINYINT(1) |
whole numbers (TINYINT(1) / BOOLEAN for true/false) |
DECIMAL(p, s) |
exact numbers — use for money |
FLOAT, DOUBLE |
approximate numbers (scientific values) |
VARCHAR(n), TEXT |
strings (VARCHAR up to n characters) |
DATE, DATETIME, TIMESTAMP |
dates and times (TIMESTAMP is stored in UTC) |
ENUM(...) |
one value from a fixed list |
JSON |
JSON documents with validation and functions |
⚠️ Common error: using
utf8— in MySQL it's a 3-byte subset that can't store emojis. Always useutf8mb4.
3. Querying Data
SELECT name, price
FROM products
WHERE price < 1000 AND stock > 0
ORDER BY price DESC;
name | price
Hoodie | 899.00
Clean Code | 450.00
USB-C Cable | 199.00
SELECT name, category FROM products WHERE category IN ('Books', 'Clothing') AND name LIKE '%o%' ORDER BY name;
SELECT name, price FROM products ORDER BY price DESC LIMIT 2 OFFSET 1; -- 2nd and 3rd most expensive
name | category
Atomic Habits | Books
Clean Code | Books
Hoodie | Clothing
name | price
Hoodie | 899.00
Clean Code | 450.00
⚠️ Common error:
LIMIT 10, 20means offset 10, count 20 in MySQL — the opposite order ofLIMIT 20 OFFSET 10. Prefer the explicitOFFSETform.
4. Functions & Expressions
SELECT
name,
CONCAT(UPPER(LEFT(name, 3)), '-', LPAD(id, 4, '0')) AS sku,
ROUND(price * 1.18, 2) AS price_with_gst,
IF(stock = 0, 'Out of stock', 'Available') AS status,
CHAR_LENGTH(name) AS len
FROM products
ORDER BY id
LIMIT 4;
name | sku | price_with_gst | status | len
Clean Code | CLE-0001 | 531.00 | Available | 10
Earbuds | EAR-0002 | 1532.82 | Available | 7
Hoodie | HOO-0003 | 1060.82 | Available | 6
Atomic Habits | ATO-0004 | 470.82 | Out of stock | 13
Dates:
SELECT ordered_on,
DATE_FORMAT(ordered_on, '%d %b %Y') AS pretty,
DAYNAME(ordered_on) AS day,
DATE_ADD(ordered_on, INTERVAL 7 DAY) AS deliver_by,
DATEDIFF('2026-10-20', ordered_on) AS days_ago
FROM orders
WHERE ordered_on >= '2026-10-01'
ORDER BY ordered_on;
ordered_on | pretty | day | deliver_by | days_ago
2026-10-02 | 02 Oct 2026 | Friday | 2026-10-09 | 18
2026-10-05 | 05 Oct 2026 | Monday | 2026-10-12 | 15
Other useful functions: NOW(), CURDATE(), YEAR(), MONTH(), TIMESTAMPDIFF(), IFNULL(), COALESCE(), CASE ... END, GROUP_CONCAT(), REPLACE(), SUBSTRING(), TRIM().
5. Aggregation
SELECT category,
COUNT(*) AS products,
SUM(stock) AS total_stock,
ROUND(AVG(price), 2) AS avg_price,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS names
FROM products
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;
category | products | total_stock | avg_price | names
Books | 2 | 10 | 424.50 | Atomic Habits, Clean Code
Electronics | 2 | 125 | 749.00 | Earbuds, USB-C Cable
WITH ROLLUP adds subtotal rows:
SELECT category, SUM(stock) AS stock
FROM products
GROUP BY category WITH ROLLUP;
category | stock
Books | 10
Electronics | 125
Clothing | 5
NULL | 140
(The NULL row is the grand total. Groups are listed in ENUM order.)
⚠️ Common error:
ERROR 1055 ... ONLY_FULL_GROUP_BY— you selected a column that is neither grouped nor aggregated. Add it toGROUP BYor wrap it in an aggregate likeANY_VALUE().
6. Joins
SELECT o.id, c.name AS customer, p.name AS product, o.qty, o.qty * p.price AS amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
ORDER BY o.id;
id | customer | product | qty | amount
1 | Asha | Clean Code | 1 | 450.00
2 | Asha | Earbuds | 2 | 2598.00
3 | Ravi | USB-C Cable | 3 | 597.00
4 | Neha | Clean Code | 2 | 900.00
5 | Ravi | Hoodie | 1 | 899.00
6 | Asha | USB-C Cable | 1 | 199.00
-- total spent per customer, including customers with no orders
SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.qty * p.price), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN products p ON p.id = o.product_id
GROUP BY c.id, c.name
ORDER BY spent DESC;
name | orders | spent
Asha | 3 | 3247.00
Ravi | 2 | 1496.00
Neha | 1 | 900.00
Kabir | 0 | 0.00
MySQL supports INNER, LEFT, RIGHT and CROSS joins. FULL OUTER JOIN is not supported — emulate it with LEFT JOIN ... UNION ... RIGHT JOIN.
7. Subqueries, CTEs & Window Functions (MySQL 8)
-- products never ordered
SELECT name FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.id);
name
Atomic Habits
WITH monthly AS (
SELECT DATE_FORMAT(o.ordered_on, '%Y-%m') AS month, SUM(o.qty * p.price) AS revenue
FROM orders o JOIN products p ON p.id = o.product_id
GROUP BY month
)
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly
ORDER BY month;
SELECT name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category
FROM products
ORDER BY category, rank_in_category;
month | revenue | running_total
2026-09 | 4545.00 | 4545.00
2026-10 | 1098.00 | 5643.00
name | category | price | rank_in_category
Clean Code | Books | 450.00 | 1
Atomic Habits | Books | 399.00 | 2
Earbuds | Electronics | 1299.00 | 1
USB-C Cable | Electronics | 199.00 | 2
Hoodie | Clothing | 899.00 | 1
8. Modifying Data & Constraints
INSERT INTO customers (name, email, city) VALUES ('Isha', 'isha@example.com', 'Delhi');
-- insert or update when the unique key already exists
INSERT INTO customers (name, email, city) VALUES ('Asha R.', 'asha@example.com', 'Pune')
ON DUPLICATE KEY UPDATE name = VALUES(name);
UPDATE products SET stock = stock - 2 WHERE id = 1;
DELETE FROM customers WHERE email = 'kabir@example.com'; -- also deletes their orders (CASCADE)
SELECT id, name, city FROM customers ORDER BY id;
SELECT name, stock FROM products WHERE id = 1;
id | name | city
1 | Asha R. | Pune
2 | Ravi | Delhi
3 | Neha | Pune
5 | Isha | Delhi
name | stock
Clean Code | 8
Change the structure with ALTER TABLE:
ALTER TABLE customers ADD COLUMN phone VARCHAR(15) AFTER email;
ALTER TABLE customers MODIFY city VARCHAR(60) NOT NULL DEFAULT 'Unknown';
ALTER TABLE products RENAME COLUMN stock TO quantity;
ALTER TABLE orders ADD CONSTRAINT chk_qty CHECK (qty BETWEEN 1 AND 10);
⚠️ Common error:
Error 1451: Cannot delete or update a parent row: a foreign key constraint fails— rows in another table reference it. Delete the children first, or defineON DELETE CASCADE/SET NULL.
9. Indexes & Transactions
CREATE INDEX idx_orders_date ON orders (ordered_on);
CREATE INDEX idx_orders_cust_date ON orders (customer_id, ordered_on); -- composite (leftmost prefix rule)
SHOW INDEX FROM orders;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND ordered_on >= '2026-09-01';
In EXPLAIN output, look at type (ref/range good, ALL = full scan), key (index used) and rows (rows examined).
Transactions (InnoDB):
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 2 AND stock > 0;
INSERT INTO orders (customer_id, product_id, qty, ordered_on) VALUES (4, 2, 1, '2026-10-20');
COMMIT; -- or ROLLBACK;
SELECT stock FROM products WHERE id = 2;
SELECT COUNT(*) AS orders_now FROM orders;
stock
24
orders_now
7
Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (MySQL's default), SERIALIZABLE. Use SELECT ... FOR UPDATE to lock rows you are about to change (e.g. seat booking).
⚠️ Common error: a composite index
(customer_id, ordered_on)is not used forWHERE ordered_on = ...alone — queries must use the leftmost column(s).
10. Advanced Concepts
JSON columns:
CREATE TABLE settings (user_id INT PRIMARY KEY, prefs JSON);
INSERT INTO settings VALUES (1, '{"theme": "dark", "langs": ["python", "sql"]}');
SELECT prefs->>'$.theme' AS theme, JSON_LENGTH(prefs, '$.langs') AS langs,
JSON_CONTAINS(prefs->'$.langs', '"sql"') AS knows_sql
FROM settings;
theme | langs | knows_sql
dark | 2 | 1
Views, stored procedures and triggers:
CREATE VIEW order_summary AS
SELECT c.name, p.name AS product, o.qty
FROM orders o JOIN customers c ON c.id = o.customer_id JOIN products p ON p.id = o.product_id;
DELIMITER //
CREATE PROCEDURE customer_total(IN cid INT, OUT total DECIMAL(10, 2))
BEGIN
SELECT COALESCE(SUM(o.qty * p.price), 0) INTO total
FROM orders o JOIN products p ON p.id = o.product_id
WHERE o.customer_id = cid;
END //
CREATE TRIGGER reduce_stock AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE products SET stock = stock - NEW.qty WHERE id = NEW.product_id;
END //
DELIMITER ;
CALL customer_total(1, @t);
SELECT @t AS asha_total;
asha_total
3247.00
Users and privileges — never use root in your app:
CREATE USER 'shop_app'@'%' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'%';
SHOW GRANTS FOR 'shop_app'@'%';
From Node.js with mysql2 (parameterised queries prevent SQL injection):
import mysql from "mysql2/promise";
const pool = mysql.createPool({ host: "localhost", user: "shop_app", password: process.env.DB_PASS, database: "shop" });
const [rows] = await pool.query("SELECT name, price FROM products WHERE category = ? AND price < ?", ["Books", 500]);
console.log(rows);
Also learn: replication (primary/replica), backups with mysqldump or binary logs, partitioning, the slow query log, and MySQL Workbench for visual design.
11. Examples with Output
Example 1 — Best-selling product by quantity
SELECT p.name, SUM(o.qty) AS sold
FROM orders o JOIN products p ON p.id = o.product_id
GROUP BY p.id, p.name
ORDER BY sold DESC, p.name
LIMIT 1;
name | sold
USB-C Cable | 4
Example 2 — Customers from the same city (self join)
SELECT a.name AS customer, b.name AS neighbour, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id;
customer | neighbour | city
Asha | Neha | Pune
Example 3 — Low-stock report with a label
SELECT name, stock,
CASE WHEN stock = 0 THEN 'Reorder now' WHEN stock < 10 THEN 'Low' ELSE 'OK' END AS level
FROM products
ORDER BY stock;
name | stock | level
Atomic Habits | 0 | Reorder now
Hoodie | 5 | Low
Clean Code | 10 | OK
Earbuds | 25 | OK
USB-C Cable | 100 | OK
12. Practice Problems
Use the shop database above:
- List all Electronics products under ₹1000 that are in stock.
- Show each customer's number of orders and last order date.
- Find the month with the highest revenue.
- Show customers who bought both a Book and an Electronics product.
- Write an
INSERT ... ON DUPLICATE KEY UPDATEthat adds stock when a product with the same name exists (add a UNIQUE index first). - Write a transaction that places an order only if enough stock is available.
- Add the right index for "orders of a customer in a date range" and check it with
EXPLAIN. - Create a
shop_readonlyuser that can onlySELECT.
13. Interview Questions
1. InnoDB vs MyISAM? InnoDB (default) supports transactions, foreign keys, row-level locking and crash recovery; MyISAM has table-level locks and no transactions.
2. CHAR vs VARCHAR?
CHAR(n) is fixed length (padded); VARCHAR(n) stores only the characters used plus a length byte — better for variable text.
3. Why use DECIMAL for money?
It stores exact values; FLOAT/DOUBLE are approximate and cause rounding errors (0.1 + 0.2 ≠ 0.3).
4. What is the leftmost prefix rule?
A composite index (a, b, c) can be used for queries on a, a, b or a, b, c — but not b or c alone.
5. DELETE vs TRUNCATE?
DELETE removes rows one by one (can filter, fires triggers, transactional); TRUNCATE drops and recreates the table quickly and resets AUTO_INCREMENT.
6. What is the default isolation level?
REPEATABLE READ in InnoDB, which uses MVCC snapshots and gap locks to prevent most anomalies.
7. What does EXPLAIN show?
How MySQL will execute a query: join order, access type, chosen index, estimated rows — used to optimise slow queries.
8. utf8 vs utf8mb4?
utf8 in MySQL stores max 3 bytes per character; utf8mb4 is real UTF-8 (4 bytes) and supports emojis.
9. Primary key vs unique key? One primary key per table (NOT NULL, the clustered index in InnoDB); multiple unique keys allowed and they can contain NULLs.
10. How do you prevent SQL injection? Use prepared statements / parameterised queries in the application and least-privilege database users.
14. Cheat Sheet & References
Client and admin
# connect, import, export
mysql -u root -p
mysql -u user -p dbname < file.sql
mysqldump -u user -p dbname > backup.sql
Databases and tables
CREATE DATABASE app CHARACTER SET utf8mb4; USE app; SHOW TABLES; DESCRIBE users;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(120) UNIQUE NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE users ADD COLUMN name VARCHAR(60); ALTER TABLE users MODIFY name VARCHAR(80) NOT NULL;
DROP TABLE IF EXISTS users; TRUNCATE TABLE logs;
CRUD
INSERT INTO users (email, name) VALUES ('a@b.com', 'A'), ('c@d.com', 'C');
INSERT INTO t (k, v) VALUES (1, 'x') ON DUPLICATE KEY UPDATE v = VALUES(v);
SELECT * FROM users WHERE name LIKE 'A%' ORDER BY id DESC LIMIT 10 OFFSET 0;
UPDATE users SET name = 'B' WHERE id = 1; DELETE FROM users WHERE id = 1;
Queries
SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id;
SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 500;
SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM products;
WITH t AS (SELECT ...) SELECT * FROM t; SELECT RANK() OVER (ORDER BY price DESC) FROM products;
Functions
CONCAT(a, b); LPAD(n, 4, '0'); UPPER(s); CHAR_LENGTH(s); IFNULL(x, 0); IF(c, a, b);
NOW(); CURDATE(); DATE_FORMAT(d, '%d %b %Y'); DATE_ADD(d, INTERVAL 7 DAY); DATEDIFF(a, b);
j->>'$.key'; JSON_EXTRACT(j, '$.a'); JSON_ARRAYAGG(x);
Indexes, transactions, users
CREATE INDEX idx_name ON t (a, b); SHOW INDEX FROM t; EXPLAIN SELECT ...;
START TRANSACTION; UPDATE ...; COMMIT; ROLLBACK;
SELECT * FROM seats WHERE id = 5 FOR UPDATE;
CREATE USER 'app'@'%' IDENTIFIED BY 'pass'; GRANT SELECT ON db.* TO 'app'@'%';
Official documentation