Introduction to MySQL

🗄️ MySQL · Basics · 4 min read

MySQL is the world's most popular open-source relational database. It stores data in tables with rows and columns and uses SQL (Structured Query Language) to query and manipulate that data.

Why MySQL?

Free and open source (Community Edition)
Powers WordPress, Facebook, Twitter, YouTube
Excellent Spring Boot and JPA integration
ACID compliant with InnoDB engine

💡 MySQL 8.x is the current major version. Use it for all new projects — it adds window functions, CTEs, and JSON support.

Setup & Connect

🗄️ MySQL · Basics · 4 min read

Install via Docker (quickest)

docker run -d --name mysql8 \

  -e MYSQL_ROOT_PASSWORD=secret \

  -e MYSQL_DATABASE=mydb \

  -p 3306:3306 \

  mysql:8

Connect via CLI

mysql -h 127.0.0.1 -P 3306 -u root -p

-- then type your password

Useful Initial Commands

SHOW DATABASES;

USE mydb;

SHOW TABLES;

DESCRIBE users;

Data Types

🗄️ MySQL · Basics · 5 min read

Numeric

INT — integer (-2B to 2B)
BIGINT — large integer (use for IDs in big apps)
DECIMAL(10,2) — exact decimal, use for money
DOUBLE — floating point (avoid for money)

String

VARCHAR(255) — variable-length string (most common)
TEXT — long text (blog posts, descriptions)
CHAR(10) — fixed-length (phone codes, status flags)
ENUM('active','inactive') — restricted set of values

Date & Time

DATE — YYYY-MM-DD
DATETIME — YYYY-MM-DD HH:MM:SS
TIMESTAMP — auto-updates on row change

CREATE TABLE

🗄️ MySQL · DDL · 5 min read

CREATE TABLE users (

  id         BIGINT       AUTO_INCREMENT PRIMARY KEY,

  name       VARCHAR(100) NOT NULL,

  email      VARCHAR(150) NOT NULL UNIQUE,

  age        INT          DEFAULT 0,

  role       ENUM('user','admin') DEFAULT 'user',

  created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP

);

-- Foreign key

CREATE TABLE orders (

  id      BIGINT AUTO_INCREMENT PRIMARY KEY,

  user_id BIGINT NOT NULL,

  amount  DECIMAL(10,2),

  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

);

ALTER & DROP

🗄️ MySQL · DDL · 4 min read

-- Add column

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Modify column type

ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Rename column (MySQL 8+)

ALTER TABLE users RENAME COLUMN phone TO mobile;

-- Drop column

ALTER TABLE users DROP COLUMN mobile;

-- Drop table

DROP TABLE IF EXISTS temp_data;

-- Truncate (delete all rows, keep table)

TRUNCATE TABLE logs;

INSERT

🗄️ MySQL · DML · 3 min read

-- Single row

INSERT INTO users (name, email, age)

VALUES ('Aftab', 'aftab@example.com', 30);

-- Multiple rows

INSERT INTO users (name, email) VALUES

  ('Alice', 'alice@example.com'),

  ('Bob',   'bob@example.com');

-- Upsert (insert or update on duplicate key)

INSERT INTO users (email, name)

VALUES ('aftab@example.com', 'Aftab Khan')

ON DUPLICATE KEY UPDATE name = VALUES(name);

SELECT & WHERE

🗄️ MySQL · DML · 6 min read

SELECT * FROM users;

SELECT name, email FROM users;

-- Filtering

SELECT * FROM users WHERE age > 25;

SELECT * FROM users WHERE name LIKE 'A%';

SELECT * FROM users WHERE role IN ('admin', 'user');

SELECT * FROM users WHERE age BETWEEN 20 AND 35;

SELECT * FROM users WHERE email IS NOT NULL;

-- Sorting & Limiting

SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

-- Alias

SELECT CONCAT(name, ' <', email, '>') AS display_name FROM users;

UPDATE & DELETE

🗄️ MySQL · DML · 3 min read

-- Update

UPDATE users SET role = 'admin' WHERE id = 1;

UPDATE users SET age = age + 1 WHERE role = 'user';

-- Delete

DELETE FROM users WHERE id = 5;

DELETE FROM users WHERE created_at < '2020-01-01';

⚠️ Always use a WHERE clause with UPDATE and DELETE. Omitting it affects every row in the table.

JOINs

🗄️ MySQL · Advanced Queries · 7 min read

-- INNER JOIN (matching rows in both tables)

SELECT u.name, o.amount

FROM users u

INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (all users, even those with no orders)

SELECT u.name, o.amount

FROM users u

LEFT JOIN orders o ON u.id = o.user_id;

-- Multiple joins

SELECT u.name, o.amount, p.name AS product

FROM users u

JOIN orders o ON u.id = o.user_id

JOIN products p ON o.product_id = p.id;

GROUP BY & HAVING

🗄️ MySQL · Advanced Queries · 5 min read

-- Count orders per user

SELECT user_id, COUNT(*) AS order_count

FROM orders

GROUP BY user_id;

-- Aggregate functions

SELECT role,

  COUNT(*) AS total,

  AVG(age) AS avg_age,

  MAX(age) AS max_age

FROM users

GROUP BY role;

-- HAVING filters groups (like WHERE for aggregates)

SELECT user_id, SUM(amount) AS total

FROM orders

GROUP BY user_id

HAVING total > 1000;

Subqueries & CTEs

🗄️ MySQL · Advanced Queries · 5 min read

-- Subquery in WHERE

SELECT * FROM users

WHERE id IN (SELECT user_id FROM orders WHERE amount > 500);

-- CTE (Common Table Expression) — MySQL 8+

WITH big_spenders AS (

  SELECT user_id, SUM(amount) AS total

  FROM orders

  GROUP BY user_id

  HAVING total > 1000

)

SELECT u.name, bs.total

FROM users u

JOIN big_spenders bs ON u.id = bs.user_id;

Indexes

🗄️ MySQL · Advanced Queries · 6 min read

An index is a data structure that speeds up reads on a column at the cost of slightly slower writes and extra storage.

-- Create index

CREATE INDEX idx_users_email ON users(email);

CREATE INDEX idx_orders_user ON orders(user_id);

-- Composite index (column order matters!)

CREATE INDEX idx_name_role ON users(role, name);

-- Show indexes on a table

SHOW INDEX FROM users;

-- Check if query uses index (EXPLAIN)

EXPLAIN SELECT * FROM users WHERE email = 'x@y.com';

💡 Index columns you filter on in WHERE, JOIN ON, and ORDER BY. Don't over-index — each index slows INSERT/UPDATE slightly.

Transactions

🗄️ MySQL · Pro Topics · 5 min read

A transaction groups multiple SQL statements into an atomic unit — either all succeed or all are rolled back.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;

UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If everything is OK:

COMMIT;

-- If something went wrong:

ROLLBACK;

ACID Properties

Atomicity — all or nothing
Consistency — data is always valid
Isolation — concurrent transactions don't interfere
Durability — committed data survives crashes

Stored Procedures

🗄️ MySQL · Pro Topics · 5 min read

Stored procedures are reusable SQL blocks stored in the database and executed by name.

DELIMITER //

CREATE PROCEDURE GetUserOrders(IN userId BIGINT)

BEGIN

  SELECT o.id, o.amount, o.created_at

  FROM orders o

  WHERE o.user_id = userId

  ORDER BY o.created_at DESC;

END//

DELIMITER ;

-- Call it

CALL GetUserOrders(1);

Performance Tips

🗄️ MySQL · Pro Topics · 6 min read

Use EXPLAIN before optimising — find which queries do full table scans
Index every foreign key column
Avoid SELECT * — only fetch columns you need
Use LIMIT for pagination; index the sort column
Avoid functions on indexed columns in WHERE (WHERE YEAR(created_at) = 2024 kills the index)
Use connection pooling (HikariCP in Spring Boot does this automatically)
Enable slow query log to find bottlenecks

-- Enable slow query log (my.cnf)

slow_query_log = 1

slow_query_log_file = /var/log/mysql/slow.log

long_query_time = 1