MySQL

App → MySQL Client Driver → TCP/IP → MySQL Server → Disk

price DECIMAL(10, 2)  -- up to 99,999,999.99
quantity INT UNSIGNED -- no negatives

email VARCHAR(255) NOT NULL,
name  VARCHAR(100),
body  TEXT

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

is_active TINYINT(1) NOT NULL DEFAULT 1

metadata JSON,

-- query a JSON field
SELECT metadata->>'$.plan' FROM users WHERE id = 1;

status ENUM('pending', 'active', 'cancelled') NOT NULL DEFAULT 'pending'

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop;

CREATE TABLE users (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  role       ENUM('admin', 'member', 'guest') NOT NULL DEFAULT 'member',
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    INT UNSIGNED NOT NULL,
  total      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status     ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

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

-- CHECK constraint (MySQL 8.0+)
age INT CHECK (age >= 0 AND age <= 120)

-- Add a column
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(500) AFTER name;

-- Change a column type
ALTER TABLE users MODIFY COLUMN name VARCHAR(200) NOT NULL;

-- Rename a column (MySQL 8.0+)
ALTER TABLE users RENAME COLUMN avatar_url TO profile_image;

-- Drop a column
ALTER TABLE users DROP COLUMN profile_image;

-- Add an index
ALTER TABLE users ADD INDEX idx_email (email);

-- Add a foreign key
ALTER TABLE orders ADD CONSTRAINT fk_orders_users
  FOREIGN KEY (user_id) REFERENCES users(id);

DROP TABLE IF EXISTS old_sessions;    -- removes table entirely
TRUNCATE TABLE logs;                  -- deletes all rows, resets AUTO_INCREMENT
DELETE FROM logs;                     -- deletes all rows, keeps AUTO_INCREMENT counter

-- Single row
INSERT INTO users (email, name, role)
VALUES ('alice@example.com', 'Alice', 'admin');

-- Multiple rows (one round-trip, much faster)
INSERT INTO products (name, price, category) VALUES
  ('Widget A', 9.99, 'widgets'),
  ('Widget B', 14.99, 'widgets'),
  ('Gadget C', 49.99, 'gadgets');

SELECT LAST_INSERT_ID();

INSERT INTO user_settings (user_id, key, value)
VALUES (42, 'theme', 'dark')
ON DUPLICATE KEY UPDATE value = 'dark';

-- Always use WHERE — without it you update every row
UPDATE users
SET name = 'Alice Smith', updated_at = NOW()
WHERE id = 1;

-- Multi-table update
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.status = 'cancelled'
WHERE u.is_active = 0 AND o.status = 'pending';

-- Delete specific rows
DELETE FROM sessions WHERE expires_at < NOW();

-- Delete with JOIN (removes orphaned rows)
DELETE o FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- Preview first
SELECT COUNT(*) FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01';

-- Then delete
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01';

SELECT id, name, email FROM users;        -- specific columns
SELECT * FROM users;                      -- all columns (avoid in production)
SELECT DISTINCT category FROM products;  -- unique values only

SELECT * FROM users WHERE is_active = 1;
SELECT * FROM users WHERE role = 'admin' AND created_at > '2024-01-01';
SELECT * FROM users WHERE role = 'admin' OR role = 'member';
SELECT * FROM users WHERE role IN ('admin', 'member');
SELECT * FROM users WHERE name LIKE 'Al%';       -- starts with Al
SELECT * FROM users WHERE name LIKE '%smith%';    -- contains smith
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

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

-- Pagination: page 3, 20 items per page
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  UPPER(email)                        AS email_upper,
  YEAR(created_at)                    AS join_year,
  price * 1.2                         AS price_with_tax
FROM users;

SELECT
  name,
  total,
  CASE
    WHEN total >= 1000 THEN 'VIP'
    WHEN total >= 100  THEN 'Regular'
    ELSE 'New'
  END AS customer_tier
FROM orders;

SELECT COALESCE(phone, 'N/A') AS phone FROM users;   -- first non-null value
SELECT IFNULL(bio, '')        AS bio   FROM users;   -- if null, use default
SELECT NULLIF(score, 0)                FROM results; -- returns NULL if score = 0

LENGTH('hello')           -- 5
SUBSTRING('hello', 2, 3) -- 'ell'
REPLACE('a-b-c', '-', '_') -- 'a_b_c'
TRIM('  hello  ')         -- 'hello'
LOWER(email), UPPER(name)

NOW()                          -- current datetime
CURDATE()                      -- current date
DATE_ADD(NOW(), INTERVAL 7 DAY)
DATEDIFF('2025-01-01', NOW())  -- days between
DATE_FORMAT(created_at, '%Y-%m')  -- '2024-03'

users              orders
──────────         ───────────────────
id │ name          id │ user_id │ total
───┼──────         ───┼─────────┼──────
 1 │ Alice          1 │    1    │  100
 2 │ Bob            2 │    1    │  200
 3 │ Carol          3 │    2    │   50
                    4 │    9    │  400  ← no matching user

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- Result: Alice(100), Alice(200), Bob(50)  — Carol and order#4 excluded

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- Result: Alice(100), Alice(200), Bob(50), Carol(NULL)

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;
-- Result: Carol

SELECT u.name, p.name AS product, oi.quantity
FROM users u
JOIN orders o    ON o.user_id   = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p  ON p.id        = oi.product_id
WHERE o.status = 'paid';

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
-- produces every size+color combination

SELECT
  COUNT(*)               AS total_orders,
  COUNT(DISTINCT user_id) AS unique_customers,
  SUM(total)             AS revenue,
  AVG(total)             AS avg_order,
  MIN(total)             AS smallest,
  MAX(total)             AS largest
FROM orders
WHERE status = 'paid';

-- Revenue per category
SELECT category, COUNT(*) AS qty, SUM(price) AS revenue
FROM products
GROUP BY category;

-- Orders per user per month
SELECT
  user_id,
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  COUNT(*) AS order_count,
  SUM(total) AS spend
FROM orders
GROUP BY user_id, month
ORDER BY user_id, month;

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

-- High-value customers (spent > $500 total)
SELECT user_id, SUM(total) AS total_spend
FROM orders
GROUP BY user_id
HAVING total_spend > 500
ORDER BY total_spend DESC;

SELECT
  u.name,
  COUNT(o.id)  AS order_count,
  SUM(o.total) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'           -- filter rows first
GROUP BY u.id, u.name             -- then group
HAVING lifetime_value > 1000      -- then filter groups
ORDER BY lifetime_value DESC
LIMIT 10;

SELECT user_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM order_items
GROUP BY user_id;
-- user 1 → "Keyboard, Monitor, Mouse"

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

-- Users who placed at least one order
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Users who have never ordered
SELECT name FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

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

SELECT u.name, order_stats.total_spend
FROM users u
JOIN (
  SELECT user_id, SUM(total) AS total_spend
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
) AS order_stats ON order_stats.user_id = u.id;

-- Each user's most recent order date
SELECT u.name,
  (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order
FROM users u;

-- IN — evaluates all rows in subquery first
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

-- EXISTS — stops at first match, faster on large subquery results
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

WITH paid_orders AS (
  SELECT user_id, SUM(total) AS total_spend, COUNT(*) AS order_count
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
),
vip_users AS (
  SELECT user_id FROM paid_orders WHERE total_spend > 1000
)
SELECT u.name, po.total_spend, po.order_count
FROM users u
JOIN paid_orders po ON po.user_id = u.id
JOIN vip_users v    ON v.user_id  = u.id
ORDER BY po.total_spend DESC;

WITH RECURSIVE category_tree AS (
  -- Anchor: start at root categories
  SELECT id, name, parent_id, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Recursive: join children to parents
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT depth, name FROM category_tree ORDER BY depth, name;

Table: 1M rows of users, no index on email
Query: SELECT * FROM users WHERE email = 'alice@example.com';
Cost:  Reads all 1,000,000 rows — O(n)

With index on email:
Cost:  Traverses ~20 B-tree levels — O(log n)

-- Single column
CREATE INDEX idx_email   ON users(email);
CREATE INDEX idx_status  ON orders(status);

-- Composite index (column order matters)
CREATE INDEX idx_cat_price ON products(category, price);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Full-text index (for LIKE '%...%' alternatives)
CREATE FULLTEXT INDEX idx_body ON posts(body);

-- Uses index
SELECT * FROM products WHERE category = 'widgets' AND price < 50;
SELECT * FROM products WHERE category = 'widgets';

-- Cannot use index efficiently
SELECT * FROM products WHERE price < 50;

-- Index: (status, created_at)
-- Query only reads index, never hits the table rows
SELECT status, created_at FROM orders WHERE status = 'paid';

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

-- Add EXTENDED for more detail (MySQL 5.6+)
EXPLAIN EXTENDED SELECT * FROM orders WHERE status = 'paid';
SHOW WARNINGS;  -- shows rewritten query

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries taking > 1 second
SHOW VARIABLES LIKE 'slow_query_log_file';  -- find the log file

START TRANSACTION;

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

-- Check for errors first, then:
COMMIT;    -- makes changes permanent
-- or:
ROLLBACK;  -- undoes everything since START TRANSACTION

START TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (42, 99.99);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity)
VALUES (@order_id, 5, 2);

-- If no errors:
COMMIT;

START TRANSACTION;
INSERT INTO logs (msg) VALUES ('step 1');
SAVEPOINT step1;

INSERT INTO logs (msg) VALUES ('step 2');
SAVEPOINT step2;

-- Oops — undo step 2 only
ROLLBACK TO SAVEPOINT step1;

COMMIT;  -- commits 'step 1' only

-- Check current level
SHOW VARIABLES LIKE 'transaction_isolation';

-- Change for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SHOW VARIABLES LIKE 'autocommit';  -- 1 = on
SET autocommit = 0;                 -- disable for session

T1: LOCK row A → waiting for row B
T2: LOCK row B → waiting for row A
→ Deadlock! InnoDB kills T2 and T1 proceeds.

BAD:  users.tags = "javascript,react,node"   (comma-list)
GOOD: separate tags table + user_tags pivot table

BAD:  order_items(order_id, product_id, product_name, quantity)
      product_name depends only on product_id, not on (order_id, product_id)
GOOD: Move product_name to the products table

BAD:  orders(id, user_id, user_email, user_name, total)
      user_email and user_name depend on user_id, not directly on order id
GOOD: Move user fields to the users table, orders stores only user_id

orders.user_id → users.id

CREATE TABLE order_items (
  order_id   INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

user_profiles.user_id → users.id  (UNIQUE foreign key)

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 42;

-- Query only active records
SELECT * FROM users WHERE deleted_at IS NULL;

function_name() OVER (
  PARTITION BY col   -- optional: split into groups
  ORDER BY col       -- optional: define row order within window
  ROWS BETWEEN ...   -- optional: define frame
)

SELECT
  name,
  department,
  salary,
  ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

WITH ranked AS (
  SELECT name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;

SELECT
  order_date,
  total,
  LAG(total, 1)  OVER (ORDER BY order_date) AS prev_day_total,
  LEAD(total, 1) OVER (ORDER BY order_date) AS next_day_total,
  total - LAG(total, 1) OVER (ORDER BY order_date) AS day_over_day_change
FROM daily_sales;

SELECT
  order_date,
  total,
  SUM(total)  OVER (ORDER BY order_date) AS running_total,
  AVG(total)  OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily_sales;

SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_totals;
-- quartile 1 = top 25%, quartile 4 = bottom 25%

SELECT
  name,
  salary,
  FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS top_salary_in_dept
FROM employees;

CREATE OR REPLACE VIEW active_users AS
  SELECT id, name, email, created_at
  FROM users
  WHERE is_active = 1 AND deleted_at IS NULL;

-- Query like a table
SELECT * FROM active_users WHERE created_at > '2024-01-01';

-- Drop
DROP VIEW IF EXISTS active_users;

DELIMITER //

CREATE PROCEDURE transfer_funds(
  IN from_account INT,
  IN to_account   INT,
  IN amount       DECIMAL(10,2)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
  COMMIT;
END //

DELIMITER ;

-- Call it
CALL transfer_funds(1, 2, 250.00);

DELIMITER //
CREATE PROCEDURE grade_score(IN score INT, OUT grade VARCHAR(1))
BEGIN
  IF score >= 90 THEN
    SET grade = 'A';
  ELSEIF score >= 80 THEN
    SET grade = 'B';
  ELSEIF score >= 70 THEN
    SET grade = 'C';
  ELSE
    SET grade = 'F';
  END IF;
END //
DELIMITER ;

CALL grade_score(85, @g);
SELECT @g;  -- 'B'

DELIMITER //
CREATE PROCEDURE generate_months(IN year INT)
BEGIN
  DECLARE m INT DEFAULT 1;
  WHILE m <= 12 DO
    INSERT INTO calendar_months (year, month) VALUES (year, m);
    SET m = m + 1;
  END WHILE;
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION full_name(first VARCHAR(50), last VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
  RETURN CONCAT(first, ' ', last);
END //
DELIMITER ;

-- Use in a query
SELECT full_name(first_name, last_name) AS name FROM users;

CREATE TABLE products (
  id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name     VARCHAR(200) NOT NULL,
  metadata JSON
);

INSERT INTO products (name, metadata) VALUES
  ('Widget', '{"color":"red","weight":0.5,"tags":["sale","new"]}');

SELECT
  name,
  metadata->>'$.color'        AS color,     -- unquoted string
  metadata->'$.weight'        AS weight,    -- JSON value (quoted)
  JSON_EXTRACT(metadata, '$.tags[0]') AS first_tag
FROM products;

SELECT * FROM products WHERE metadata->>'$.color' = 'red';
SELECT * FROM products WHERE JSON_CONTAINS(metadata->'$.tags', '"sale"');

UPDATE products
SET metadata = JSON_SET(metadata, '$.color', 'blue', '$.in_stock', true)
WHERE id = 1;

CREATE TABLE orders (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  subtotal   DECIMAL(10,2),
  tax_rate   DECIMAL(4,4) DEFAULT 0.08,
  -- VIRTUAL: computed on read (no storage)
  tax_amount DECIMAL(10,2) AS (subtotal * tax_rate) VIRTUAL,
  -- STORED: computed on write (indexable)
  total      DECIMAL(10,2) AS (subtotal + subtotal * tax_rate) STORED
);

-- You can even index a stored generated column
CREATE INDEX idx_total ON orders(total);

ALTER TABLE posts ADD FULLTEXT INDEX idx_ft (title, body);

-- Natural language search (scored by relevance)
SELECT title, MATCH(title, body) AGAINST ('machine learning') AS score
FROM posts
WHERE MATCH(title, body) AGAINST ('machine learning')
ORDER BY score DESC;

-- Boolean mode (more control)
SELECT title FROM posts
WHERE MATCH(title, body) AGAINST ('+mysql -oracle' IN BOOLEAN MODE);

SELECT * FROM articles WHERE FIND_IN_SET('mysql', tags);

INSERT IGNORE INTO user_favorites (user_id, product_id) VALUES (1, 5);

SHOW DATABASES;
SHOW TABLES;
DESCRIBE users;               -- columns and types
SHOW CREATE TABLE users;      -- full CREATE statement
SHOW INDEX FROM orders;       -- all indexes
SHOW PROCESSLIST;             -- active connections/queries

SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 2)  AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb,
  table_rows
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY data_length DESC;