MySQL Tutorial for Beginners (2025): Learn MySQL Step by Step

MySQL is the world's most popular open-source relational database. It powers WordPress, Facebook, Twitter, YouTube, and millions of other apps. This tutorial takes you from zero to building real MySQL databases — no prior experience required.

What you'll learn

Part 1 — What Is MySQL?

MySQL is a Relational Database Management System (RDBMS). Data is stored in tables (rows and columns), and you query it with SQL.

App code  →  MySQL Server  →  Tables (rows + columns)
              ↑
           Port 3306

MySQL vs other databases

MySQL is the default choice for:

• WordPress and PHP apps
• E-commerce (WooCommerce, Magento)
• Traditional web backends (Laravel, Django, Rails)

Part 2 — Installation

Option 1: MySQL Community Server (direct install)

Windows:

1. Download MySQL Installer from mysql.com/downloads/installer
2. Choose "Developer Default" — installs server + workbench + connectors
3. Set root password during setup
4. Start MySQL from Services or net start MySQL80

macOS:

brew install mysql
brew services start mysql
mysql_secure_installation   # set root password

Linux (Ubuntu/Debian):

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation

Option 2: Docker (no install needed)

docker run --name mysql-dev \
  -e MYSQL_ROOT_PASSWORD=secret \
  -e MYSQL_DATABASE=myapp \
  -p 3306:3306 \
  -d mysql:8.0

Connect:

docker exec -it mysql-dev mysql -u root -psecret

Connect via CLI

mysql -u root -p
# Enter password when prompted

# Or one-liner (not for production — password visible in history):
mysql -u root -psecret

GUI Tools

Part 3 — Databases and Tables

Show what exists

SHOW DATABASES;
SHOW TABLES;

Create a database

CREATE DATABASE myshop;
USE myshop;

Data types

Create a table

CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  price       DECIMAL(10, 2) NOT NULL,
  stock       INT DEFAULT 0,
  category    VARCHAR(100),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key concepts:

• AUTO_INCREMENT — MySQL generates the id automatically
• PRIMARY KEY — unique identifier for each row
• NOT NULL — field is required
• DEFAULT — fallback value if not provided

View table structure

DESCRIBE products;
-- or
SHOW COLUMNS FROM products;

Modify a table

-- Add column
ALTER TABLE products ADD COLUMN description TEXT;

-- Rename column
ALTER TABLE products RENAME COLUMN category TO category_name;

-- Change data type
ALTER TABLE products MODIFY COLUMN price DECIMAL(12, 2);

-- Drop column
ALTER TABLE products DROP COLUMN description;

-- Rename table
RENAME TABLE products TO items;

Drop a table

DROP TABLE IF EXISTS products;

Part 4 — Inserting Data

-- Insert one row
INSERT INTO products (name, price, stock, category)
VALUES ('Laptop', 999.99, 50, 'Electronics');

-- Insert multiple rows
INSERT INTO products (name, price, stock, category) VALUES
  ('Mouse', 29.99, 200, 'Electronics'),
  ('Desk Chair', 249.00, 30, 'Furniture'),
  ('Notebook', 4.99, 500, 'Stationery'),
  ('Monitor', 399.00, 75, 'Electronics'),
  ('Keyboard', 79.99, 150, 'Electronics');

Check what was inserted:

SELECT LAST_INSERT_ID();   -- ID of last inserted row

Part 5 — SELECT Queries

Basic SELECT

-- All columns
SELECT * FROM products;

-- Specific columns
SELECT name, price FROM products;

-- With alias
SELECT name, price AS cost FROM products;

-- Computed column
SELECT name, price * 1.2 AS price_with_tax FROM products;

WHERE clause

-- Equal
SELECT * FROM products WHERE category = 'Electronics';

-- Not equal
SELECT * FROM products WHERE category != 'Furniture';

-- Comparison
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 500;

-- Multiple conditions
SELECT * FROM products WHERE category = 'Electronics' AND price < 200;
SELECT * FROM products WHERE category = 'Electronics' OR category = 'Furniture';

-- Pattern matching
SELECT * FROM products WHERE name LIKE 'M%';      -- starts with M
SELECT * FROM products WHERE name LIKE '%board';  -- ends with board
SELECT * FROM products WHERE name LIKE '%key%';   -- contains key

-- IN list
SELECT * FROM products WHERE category IN ('Electronics', 'Furniture');

-- NULL checks
SELECT * FROM products WHERE category IS NULL;
SELECT * FROM products WHERE category IS NOT NULL;

ORDER BY

-- Ascending (default)
SELECT * FROM products ORDER BY price;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM products ORDER BY category ASC, price DESC;

LIMIT and OFFSET

-- First 5 rows
SELECT * FROM products LIMIT 5;

-- Rows 11–20 (pagination)
SELECT * FROM products LIMIT 10 OFFSET 10;

-- Shorthand
SELECT * FROM products LIMIT 10, 10;   -- offset 10, limit 10

DISTINCT

SELECT DISTINCT category FROM products;

Part 6 — Updating and Deleting

UPDATE

-- Update one field
UPDATE products SET price = 899.99 WHERE id = 1;

-- Update multiple fields
UPDATE products SET price = 34.99, stock = 180 WHERE name = 'Mouse';

-- Update with calculation
UPDATE products SET stock = stock - 1 WHERE id = 3;

Always use WHERE with UPDATE — without it you update every row.

DELETE

-- Delete one row
DELETE FROM products WHERE id = 5;

-- Delete with condition
DELETE FROM products WHERE stock = 0;

-- Delete all rows (keep table structure)
TRUNCATE TABLE products;

Always use WHERE with DELETE — without it you delete every row.

Part 7 — Aggregate Functions

-- Count rows
SELECT COUNT(*) FROM products;
SELECT COUNT(*) FROM products WHERE category = 'Electronics';

-- Sum
SELECT SUM(stock) FROM products;
SELECT SUM(price * stock) AS total_inventory_value FROM products;

-- Average
SELECT AVG(price) FROM products;

-- Min / Max
SELECT MIN(price), MAX(price) FROM products;

-- Round
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

GROUP BY

-- Products per category
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category;

-- Total stock per category
SELECT category, SUM(stock) AS total_stock
FROM products
GROUP BY category
ORDER BY total_stock DESC;

-- Average price per category
SELECT category,
       COUNT(*) AS products,
       ROUND(AVG(price), 2) AS avg_price,
       MIN(price) AS min_price,
       MAX(price) AS max_price
FROM products
GROUP BY category;

HAVING (filter groups)

-- Categories with more than 2 products
SELECT category, COUNT(*) AS count
FROM products
GROUP BY category
HAVING count > 2;

-- Categories with average price over 100
SELECT category, ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING avg_price > 100;

Part 8 — JOINs

First, let's set up related tables:

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

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  product_id  INT NOT NULL,
  quantity    INT NOT NULL DEFAULT 1,
  order_date  DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Sample data
INSERT INTO customers (name, email, city) VALUES
  ('Alice Smith', 'alice@email.com', 'London'),
  ('Bob Jones', 'bob@email.com', 'Paris'),
  ('Carol White', 'carol@email.com', 'Berlin');

INSERT INTO orders (customer_id, product_id, quantity, order_date) VALUES
  (1, 1, 1, '2025-01-10'),
  (1, 2, 2, '2025-01-15'),
  (2, 3, 1, '2025-01-18'),
  (3, 1, 1, '2025-02-01');

JOIN types

INNER JOIN

-- Orders with customer and product names
SELECT
  o.id AS order_id,
  c.name AS customer,
  p.name AS product,
  o.quantity,
  o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p  ON o.product_id  = p.id;

LEFT JOIN

-- All customers, even those with no orders
SELECT
  c.name,
  COUNT(o.id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Self JOIN

-- Employees and their managers (same table)
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Part 9 — String Functions

SELECT UPPER('hello');                      -- HELLO
SELECT LOWER('WORLD');                      -- world
SELECT LENGTH('MySQL');                     -- 5
SELECT CHAR_LENGTH('MySQL');               -- 5 (same for ASCII)
SELECT CONCAT('Hello', ' ', 'World');      -- Hello World
SELECT CONCAT_WS(', ', 'Alice', 'Bob');    -- Alice, Bob
SELECT SUBSTRING('MySQL 8.0', 1, 5);       -- MySQL
SELECT TRIM('  hello  ');                  -- hello
SELECT LTRIM('  hello');                   -- hello
SELECT RTRIM('hello  ');                   -- hello
SELECT REPLACE('foo bar', 'bar', 'baz');   -- foo baz
SELECT LOCATE('SQL', 'MySQL 8.0');         -- 3
SELECT LEFT('MySQL', 2);                   -- My
SELECT RIGHT('MySQL', 3);                  -- SQL
SELECT LPAD('5', 3, '0');                  -- 005
SELECT RPAD('hi', 5, '.');                 -- hi...
SELECT REVERSE('abc');                     -- cba

Part 10 — Date and Time Functions

SELECT NOW();                           -- 2025-07-16 14:30:00
SELECT CURDATE();                       -- 2025-07-16
SELECT CURTIME();                       -- 14:30:00
SELECT DATE('2025-07-16 14:30:00');     -- 2025-07-16
SELECT YEAR('2025-07-16');              -- 2025
SELECT MONTH('2025-07-16');             -- 7
SELECT DAY('2025-07-16');              -- 16
SELECT DAYNAME('2025-07-16');           -- Wednesday
SELECT MONTHNAME('2025-07-16');         -- July
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- 2025-07-16
SELECT DATEDIFF('2025-12-31', NOW());   -- days until end of year
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY); -- 1 week from now
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 1 month ago

-- Orders in the last 30 days
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- Orders grouped by month
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;

Part 11 — Subqueries

-- Products more expensive than average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Customers who have placed orders
SELECT name FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders);

-- Customers with no orders
SELECT name FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders);

-- Top 3 most expensive products per category (correlated subquery)
SELECT p1.name, p1.category, p1.price
FROM products p1
WHERE (
  SELECT COUNT(*)
  FROM products p2
  WHERE p2.category = p1.category AND p2.price > p1.price
) < 3
ORDER BY p1.category, p1.price DESC;

Subquery in FROM (derived table)

SELECT category, avg_price
FROM (
  SELECT category, ROUND(AVG(price), 2) AS avg_price
  FROM products
  GROUP BY category
) AS category_stats
WHERE avg_price > 100;

Part 12 — Common Table Expressions (CTEs)

CTEs make complex queries more readable:

-- Equivalent to subquery example above, but clearer
WITH category_stats AS (
  SELECT category, ROUND(AVG(price), 2) AS avg_price
  FROM products
  GROUP BY category
)
SELECT category, avg_price
FROM category_stats
WHERE avg_price > 100;

-- Multiple CTEs
WITH
  top_customers AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
    HAVING order_count >= 2
  ),
  customer_details AS (
    SELECT c.id, c.name, c.city
    FROM customers c
    INNER JOIN top_customers tc ON c.id = tc.customer_id
  )
SELECT cd.name, cd.city, tc.order_count
FROM customer_details cd
INNER JOIN top_customers tc ON cd.id = tc.customer_id;

Part 13 — Indexes

Indexes speed up SELECT queries but slow down INSERT/UPDATE/DELETE.

-- View existing indexes
SHOW INDEX FROM products;

-- Create index
CREATE INDEX idx_category ON products(category);
CREATE INDEX idx_price ON products(price);

-- Composite index (useful for queries filtering both columns)
CREATE INDEX idx_category_price ON products(category, price);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_email ON customers(email);

-- Drop index
DROP INDEX idx_price ON products;

-- Check if query uses index (EXPLAIN)
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

When to add an index

Part 14 — Transactions

Transactions ensure multiple operations succeed or fail together:

START TRANSACTION;

-- Transfer stock between products
UPDATE products SET stock = stock - 10 WHERE id = 1;
UPDATE products SET stock = stock + 10 WHERE id = 2;

-- If both succeeded:
COMMIT;

-- If something went wrong (in application code):
ROLLBACK;

Real example: order placement

START TRANSACTION;

-- Create order
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (1, 1, 2, CURDATE());

-- Decrease stock
UPDATE products SET stock = stock - 2 WHERE id = 1;

-- Check stock didn't go negative
SET @new_stock = (SELECT stock FROM products WHERE id = 1);

IF @new_stock >= 0 THEN
  COMMIT;
ELSE
  ROLLBACK;
END IF;

Transaction properties (ACID)

Part 15 — Views

Views are saved SELECT queries you can query like a table:

-- Create a view
CREATE VIEW order_summary AS
SELECT
  o.id AS order_id,
  c.name AS customer,
  p.name AS product,
  o.quantity,
  p.price,
  o.quantity * p.price AS total,
  o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN products p  ON o.product_id = p.id;

-- Use the view
SELECT * FROM order_summary;
SELECT * FROM order_summary WHERE customer = 'Alice Smith';
SELECT customer, SUM(total) AS revenue FROM order_summary GROUP BY customer;

-- Drop view
DROP VIEW IF EXISTS order_summary;

Part 16 — Stored Procedures

Stored procedures are reusable SQL programs stored in the database:

DELIMITER //

CREATE PROCEDURE GetProductsByCategory(IN category_name VARCHAR(100))
BEGIN
  SELECT id, name, price, stock
  FROM products
  WHERE category = category_name
  ORDER BY price;
END //

DELIMITER ;

-- Call it
CALL GetProductsByCategory('Electronics');

Procedure with OUT parameter

DELIMITER //

CREATE PROCEDURE GetCategoryStats(
  IN  category_name VARCHAR(100),
  OUT product_count INT,
  OUT avg_price     DECIMAL(10,2)
)
BEGIN
  SELECT COUNT(*), ROUND(AVG(price), 2)
  INTO product_count, avg_price
  FROM products
  WHERE category = category_name;
END //

DELIMITER ;

-- Call it
CALL GetCategoryStats('Electronics', @count, @avg);
SELECT @count, @avg;

Part 17 — User Management & Security

-- Create user
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPass123!';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myshop.* TO 'appuser'@'localhost';
GRANT ALL PRIVILEGES ON myshop.* TO 'appuser'@'localhost';

-- View privileges
SHOW GRANTS FOR 'appuser'@'localhost';

-- Revoke privileges
REVOKE DELETE ON myshop.* FROM 'appuser'@'localhost';

-- Drop user
DROP USER 'appuser'@'localhost';

-- Change password
ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewPass456!';

-- Apply changes
FLUSH PRIVILEGES;

Principle of least privilege

root        → only for admin tasks
appuser     → SELECT, INSERT, UPDATE, DELETE on app DB
readonly    → SELECT only (for reporting, analytics)

Part 18 — Backup and Restore

# Backup one database
mysqldump -u root -p myshop > myshop_backup.sql

# Backup with compression
mysqldump -u root -p myshop | gzip > myshop_$(date +%Y%m%d).sql.gz

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

# Backup specific tables
mysqldump -u root -p myshop products customers > tables_backup.sql

# Restore
mysql -u root -p myshop < myshop_backup.sql

# Restore compressed
gunzip < myshop_20250716.sql.gz | mysql -u root -p myshop

Part 19 — Performance Tips

Use EXPLAIN

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';
-- Look for: "type" column — aim for "ref" or "range", avoid "ALL"

Common optimizations

-- BAD: function on indexed column disables index
SELECT * FROM orders WHERE YEAR(order_date) = 2025;

-- GOOD: range query uses index
SELECT * FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

-- BAD: leading wildcard disables index
SELECT * FROM products WHERE name LIKE '%board%';

-- GOOD: trailing wildcard uses index
SELECT * FROM products WHERE name LIKE 'Key%';

-- BAD: SELECT *
SELECT * FROM products WHERE category = 'Electronics';

-- GOOD: only needed columns
SELECT id, name, price FROM products WHERE category = 'Electronics';

Optimization checklist

Project 1 — Blog Database

CREATE DATABASE blog;
USE blog;

CREATE TABLE users (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  username   VARCHAR(50) UNIQUE NOT NULL,
  email      VARCHAR(255) UNIQUE NOT NULL,
  password   VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  author_id    INT NOT NULL,
  title        VARCHAR(255) NOT NULL,
  slug         VARCHAR(255) UNIQUE NOT NULL,
  content      TEXT NOT NULL,
  status       ENUM('draft', 'published', 'archived') DEFAULT 'draft',
  published_at DATETIME,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES users(id)
);

CREATE TABLE tags (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id  INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
);

CREATE TABLE comments (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  post_id    INT NOT NULL,
  author_id  INT NOT NULL,
  content    TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id)   REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users(id)
);

-- Indexes
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_comments_post ON comments(post_id);

-- Sample data
INSERT INTO users (username, email, password) VALUES
  ('alice', 'alice@blog.com', 'hashed_pw_1'),
  ('bob', 'bob@blog.com', 'hashed_pw_2');

INSERT INTO posts (author_id, title, slug, content, status, published_at) VALUES
  (1, 'Getting Started with MySQL', 'getting-started-mysql',
   'MySQL is the world''s most popular open-source database...', 'published', NOW()),
  (1, 'MySQL vs PostgreSQL', 'mysql-vs-postgresql',
   'Comparing the two most popular open-source RDBMS...', 'published', NOW()),
  (2, 'Draft Post', 'draft-post', 'Work in progress...', 'draft', NULL);

INSERT INTO tags (name) VALUES ('mysql'), ('database'), ('sql');
INSERT INTO post_tags VALUES (1, 1), (1, 2), (1, 3), (2, 1), (2, 2);

INSERT INTO comments (post_id, author_id, content) VALUES
  (1, 2, 'Great tutorial!'),
  (1, 2, 'Very helpful, thanks.');

-- Queries
-- Published posts with author and tag count
SELECT
  p.title,
  u.username AS author,
  COUNT(DISTINCT pt.tag_id) AS tags,
  COUNT(DISTINCT c.id) AS comments,
  p.published_at
FROM posts p
JOIN users u ON p.author_id = u.id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
GROUP BY p.id, p.title, u.username, p.published_at
ORDER BY p.published_at DESC;

-- Posts by tag
SELECT p.title, p.slug
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = 'mysql' AND p.status = 'published';

Project 2 — E-Commerce Database

CREATE DATABASE ecommerce;
USE ecommerce;

CREATE TABLE categories (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  parent_id INT DEFAULT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE products (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT,
  name        VARCHAR(255) NOT NULL,
  sku         VARCHAR(100) UNIQUE,
  price       DECIMAL(10,2) NOT NULL,
  stock       INT DEFAULT 0,
  FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE customers (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  first_name VARCHAR(100),
  last_name  VARCHAR(100),
  city       VARCHAR(100)
);

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  status      ENUM('pending','paid','shipped','delivered','cancelled') DEFAULT 'pending',
  total       DECIMAL(10,2),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id)   REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Business queries

-- Revenue per month
SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  SUM(total) AS revenue,
  COUNT(*) AS orders
FROM orders
WHERE status IN ('paid', 'shipped', 'delivered')
GROUP BY month
ORDER BY month;

-- Best-selling products
SELECT
  p.name,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status != 'cancelled'
GROUP BY p.id, p.name
ORDER BY units_sold DESC
LIMIT 10;

-- Customers with lifetime value
SELECT
  c.first_name,
  c.last_name,
  c.email,
  COUNT(o.id) AS orders,
  SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status != 'cancelled'
GROUP BY c.id, c.first_name, c.last_name, c.email
ORDER BY lifetime_value DESC
LIMIT 20;

-- Low stock alert
SELECT name, sku, stock
FROM products
WHERE stock < 10 AND stock > 0
ORDER BY stock;

Project 3 — Employee Directory

CREATE DATABASE company;
USE company;

CREATE TABLE departments (
  id   INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  department_id INT,
  manager_id    INT DEFAULT NULL,
  first_name    VARCHAR(100) NOT NULL,
  last_name     VARCHAR(100) NOT NULL,
  email         VARCHAR(255) UNIQUE,
  salary        DECIMAL(10, 2),
  hire_date     DATE,
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (manager_id)    REFERENCES employees(id)
);

INSERT INTO departments (name) VALUES
  ('Engineering'), ('Marketing'), ('Finance'), ('HR');

INSERT INTO employees (department_id, manager_id, first_name, last_name, email, salary, hire_date) VALUES
  (1, NULL,  'Sarah',  'Connor',  'sarah@company.com',  120000, '2018-03-01'),
  (1, 1,     'John',   'Smith',   'john@company.com',    90000, '2020-06-15'),
  (1, 1,     'Alice',  'Chen',    'alice@company.com',   95000, '2019-11-01'),
  (2, NULL,  'Mark',   'Taylor',  'mark@company.com',    85000, '2017-08-20'),
  (2, 4,     'Emma',   'Wilson',  'emma@company.com',    70000, '2021-02-28'),
  (3, NULL,  'David',  'Brown',   'david@company.com',   95000, '2016-01-10'),
  (3, 6,     'Lucy',   'Davis',   'lucy@company.com',    75000, '2022-05-03'),
  (4, NULL,  'James',  'Miller',  'james@company.com',   80000, '2019-09-12');

-- All employees with manager name
SELECT
  CONCAT(e.first_name, ' ', e.last_name) AS employee,
  d.name AS department,
  CONCAT(m.first_name, ' ', m.last_name) AS manager,
  e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY d.name, e.last_name;

-- Department salary stats
SELECT
  d.name AS department,
  COUNT(e.id) AS headcount,
  ROUND(AVG(e.salary), 0) AS avg_salary,
  SUM(e.salary) AS total_payroll,
  MIN(e.salary) AS min_salary,
  MAX(e.salary) AS max_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
ORDER BY total_payroll DESC;

-- Employees earning above department average
SELECT
  CONCAT(e.first_name, ' ', e.last_name) AS employee,
  d.name AS department,
  e.salary,
  ROUND(dept_avg.avg_salary, 0) AS dept_average
FROM employees e
JOIN departments d ON e.department_id = d.id
JOIN (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary
ORDER BY department, salary DESC;

MySQL vs related databases

MySQL quick reference

-- Database
CREATE DATABASE name;
USE name;
DROP DATABASE name;
SHOW DATABASES;

-- Tables
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, ...);
DESCRIBE t;
SHOW TABLES;
ALTER TABLE t ADD COLUMN col VARCHAR(100);
DROP TABLE t;

-- CRUD
INSERT INTO t (col1, col2) VALUES (v1, v2);
SELECT * FROM t WHERE condition;
UPDATE t SET col = val WHERE condition;
DELETE FROM t WHERE condition;

-- Filtering
WHERE col = 'value'
WHERE col LIKE 'prefix%'
WHERE col BETWEEN 10 AND 100
WHERE col IN (1, 2, 3)
WHERE col IS NULL

-- Sorting & limiting
ORDER BY col DESC
LIMIT 10 OFFSET 20

-- Aggregation
GROUP BY col
HAVING COUNT(*) > 5
COUNT(*) / SUM() / AVG() / MIN() / MAX()

-- JOINs
INNER JOIN t2 ON t.id = t2.fk_id
LEFT JOIN t2 ON t.id = t2.fk_id

-- Other
EXPLAIN SELECT ...   -- query execution plan
START TRANSACTION; ... COMMIT;
SHOW INDEX FROM t;
CREATE INDEX idx_name ON t(col);

Common mistakes

MySQL vs related terms

Learning path

Free resources

6 FAQ

Q: Should I use MySQL or PostgreSQL?
MySQL: simpler setup, better for PHP/WordPress, huge community. PostgreSQL: better for complex queries, JSONB, extensions. Either works for most web apps — MySQL is the default when using PHP or a managed LAMP stack.

Q: What is the difference between MySQL and MariaDB?
MariaDB is a community fork of MySQL created in 2009 when Oracle acquired MySQL. It's API-compatible — you can swap one for the other without changing SQL code. MariaDB has some extra features and is often faster, but MySQL is more widely deployed.

Q: How do I connect MySQL to my web app?

• PHP: use PDO (new PDO("mysql:host=...;dbname=...", $user, $pass)) or MySQLi
• Python: use mysql-connector-python or PyMySQL, then connect with SQLAlchemy
• Node.js: use mysql2 package (createConnection({host, user, password, database}))
• Java: use JDBC with com.mysql.cj.jdbc.Driver

Q: How do I prevent SQL injection?
Never concatenate user input into SQL strings. Always use prepared statements with parameterized queries:

# BAD
cursor.execute(f"SELECT * FROM users WHERE email = '{user_input}'")

# GOOD
cursor.execute("SELECT * FROM users WHERE email = %s", (user_input,))

Q: What storage engine should I use?
Almost always InnoDB (the default since MySQL 5.5). It supports transactions, foreign keys, and row-level locking. MyISAM is legacy — only useful for specific read-heavy workloads without transactions.

Q: How do I run MySQL in production?

1. Use a managed service if possible (AWS RDS, Google Cloud SQL, PlanetScale) — they handle backups, patches, failover
2. If self-hosted: separate MySQL server from app server, enable binary logging for point-in-time recovery, schedule nightly mysqldump backups, set up a read replica for scaling
3. Never expose port 3306 to the internet — use SSH tunnels or VPC