Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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 mysql then brew services start mysql.
  • Linux: sudo apt install mysql-server then sudo 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: run sudo mysql instead.

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 use utf8mb4.

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, 20 means offset 10, count 20 in MySQL — the opposite order of LIMIT 20 OFFSET 10. Prefer the explicit OFFSET form.

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 to GROUP BY or wrap it in an aggregate like ANY_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 define ON 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 for WHERE 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:

  1. List all Electronics products under ₹1000 that are in stock.
  2. Show each customer's number of orders and last order date.
  3. Find the month with the highest revenue.
  4. Show customers who bought both a Book and an Electronics product.
  5. Write an INSERT ... ON DUPLICATE KEY UPDATE that adds stock when a product with the same name exists (add a UNIQUE index first).
  6. Write a transaction that places an order only if enough stock is available.
  7. Add the right index for "orders of a customer in a date range" and check it with EXPLAIN.
  8. Create a shop_readonly user that can only SELECT.

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