MySQL Spickzettel

# connect to local server (prompt for password)
mysql -u root -p

# connect to a remote host on a custom port
mysql -h 192.168.1.100 -P 3307 -u admin -p

# connect directly to a specific database
mysql -u root -p mydb

# non-interactive: run a query and exit
mysql -u root -p -e "SELECT VERSION();"

# show server status inside the client
\s
SELECT VERSION(), CURRENT_USER, DATABASE();

# list all databases
SHOW DATABASES;

# create a database with charset and collation
CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

# switch to a database
USE mydb;

# show the current database
SELECT DATABASE();

# drop a database (irreversible)
DROP DATABASE IF EXISTS mydb;

# alter a database's charset
ALTER DATABASE mydb CHARACTER SET utf8mb4;

# list tables in the current database
SHOW TABLES;

# show table structure (columns, types, keys)
DESCRIBE users;
# equivalent
SHOW COLUMNS FROM users;

# show create statement for a table
SHOW CREATE TABLE users\G

# list indexes on a table
SHOW INDEX FROM users;

# show stored procedures / functions
SHOW PROCEDURE STATUS WHERE Db = 'mydb';
SHOW FUNCTION STATUS WHERE Db = 'mydb';

# list triggers
SHOW TRIGGERS\G

# view a system variable
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'version%';

# view session vs global variable
SELECT @@session.sql_mode, @@global.sql_mode;

# set a variable for the current session
SET sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.foreign_key_checks = 0;

# set globally (needs SUPER/privilege, persists until restart)
SET GLOBAL max_connections = 200;

# show server status counters
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Uptime';

-- single-line comment (note the space after --)
SELECT 1; -- inline comment

/* multi-line
   comment block */
SELECT 2;

# hash-style comment (MySQL-specific, to end of line)
SELECT 3;

# MySQL executable comments: run only on MySQL
SELECT 1 /*!50100 , 2 */;  /* the part runs on MySQL >= 5.1 */

# statements end with semicolon; \G ends and prints vertically
SELECT * FROM users\G

# /etc/my.cnf or ~/.my.cnf  (Linux/macOS)
# C:\ProgramData\MySQL\MySQL Server 8.0\my.ini  (Windows)

[mysqld]
port            = 3306
datadir         = /var/lib/mysql
max_connections = 200
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci
innodb_buffer_pool_size = 2G
slow_query_log  = 1
long_query_time = 2

[client]
default-character-set = utf8mb4

[mysql]
prompt = \u@\h [\d]>\_

CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(50)  NOT NULL UNIQUE,
  email       VARCHAR(255) NOT NULL,
  birth_date  DATE         NULL,
  status      ENUM('active','inactive','banned') NOT NULL DEFAULT 'active',
  balance     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# add a column
ALTER TABLE users
  ADD COLUMN phone VARCHAR(20) NULL AFTER email;

# add multiple columns
ALTER TABLE users
  ADD COLUMN first_name VARCHAR(50) NULL,
  ADD COLUMN last_name  VARCHAR(50) NULL;

# modify a column type
ALTER TABLE users
  MODIFY COLUMN phone VARCHAR(30) NOT NULL;

# rename a column (MySQL 8+ preserves data)
ALTER TABLE users
  RENAME COLUMN phone TO phone_number;

# rename a table
RENAME TABLE users TO members;
ALTER TABLE users RENAME TO members;

# drop a table (removes structure and data)
DROP TABLE IF EXISTS old_logs;

# drop multiple tables
DROP TABLE IF EXISTS temp1, temp2;

# truncate: empty the table, keep structure, reset AUTO_INCREMENT
TRUNCATE TABLE session_data;

# truncate cannot be rolled back in some engines
# (InnoDB: TRUNCATE is DDL, implicitly commits)
SET foreign_key_checks = 0;
TRUNCATE TABLE child_table;
SET foreign_key_checks = 1;

CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(10,2)
);

# set the next auto-increment value
ALTER TABLE orders AUTO_INCREMENT = 1000;

# insert without specifying id
INSERT INTO orders (amount) VALUES (99.50);

# get the last inserted id
SELECT LAST_INSERT_ID();

# show current AUTO_INCREMENT value
SHOW TABLE STATUS LIKE 'orders'\G

CREATE TABLE orders (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id      INT UNSIGNED NOT NULL,
  total        DECIMAL(10,2) NOT NULL,
  status       VARCHAR(20) NOT NULL,

  # unique constraint
  CONSTRAINT uq_order_no UNIQUE (user_id, status),

  # check constraint (MySQL 8.0.16+ enforced)
  CONSTRAINT chk_total CHECK (total >= 0),

  # foreign key with cascading actions
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE=InnoDB;

# add a constraint later
ALTER TABLE orders
  ADD CONSTRAINT chk_status CHECK (status IN ('paid','shipped','cancelled'));

# session-scoped, auto-dropped on disconnect
CREATE TEMPORARY TABLE tmp_active
  SELECT id, username FROM users WHERE status = 'active';

# identical name shadowing a real table
CREATE TEMPORARY TABLE users
  (id INT, name VARCHAR(50));

# only this session sees the temp table
SELECT * FROM users;

# explicit cleanup
DROP TEMPORARY TABLE IF EXISTS tmp_active;

# integers (with optional UNSIGNED)
TINYINT        -- 1 byte, -128..127 or 0..255 (UNSIGNED)
SMALLINT       -- 2 bytes
MEDIUMINT      -- 3 bytes
INT            -- 4 bytes
BIGINT         -- 8 bytes (use for large AUTO_INCREMENT)

# fixed-point (exact, for money)
DECIMAL(10,2)  -- 10 digits total, 2 after decimal
NUMERIC(8,4)

# floating-point (approximate)
FLOAT          -- 4 bytes
DOUBLE         -- 8 bytes

# other
BIT(8)         -- bit-field
SERIAL         -- alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

# fixed vs variable length
CHAR(10)       -- fixed 10 chars, padded with spaces
VARCHAR(255)   -- variable, up to 255 chars (stores actual length)

# large text
TINYTEXT       -- up to 255 bytes
TEXT           -- up to 64 KB
MEDIUMTEXT     -- up to 16 MB
LONGTEXT       -- up to 4 GB

# binary counterparts
BINARY(16), VARBINARY(255), BLOB, MEDIUMBLOB, LONGBLOB

# national character set
NATIONAL VARCHAR(100)  -- same as VARCHAR with utf8mb4

# VARCHAR with charset counts characters, not bytes
VARCHAR(100) CHARACTER SET utf8mb4

DATE            -- 'YYYY-MM-DD', range 1000-01-01 .. 9999-12-31
TIME            -- 'HH:MM:SS', can be negative for elapsed time
DATETIME        -- 'YYYY-MM-DD HH:MM:SS', 8 bytes, no timezone
TIMESTAMP       -- stored as UTC, converted to session timezone, 4 bytes
YEAR            -- YEAR(4) e.g. 2024

# DATETIME with fractional seconds and auto-default
CREATE TABLE events (
  ts DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)
                 ON UPDATE CURRENT_TIMESTAMP(3)
);

# TIMESTAMP range: 1970-01-01 .. 2038-01-19 (32-bit limit)
# MySQL 8.0.28+ extends TIMESTAMP to 24 bits of range

# ENUM: exactly one value from a list
CREATE TABLE shirts (
  size ENUM('S','M','L','XL') NOT NULL DEFAULT 'M',
  color ENUM('red','green','blue') NULL
);

# stored as integer index (1-based), space-efficient
INSERT INTO shirts (size) VALUES ('L'), ('XL');

# SET: zero or more values from a list (bit flags)
CREATE TABLE posts (
  tags SET('news','tech','sports','fun') NOT NULL DEFAULT ''
);

INSERT INTO posts (tags) VALUES ('tech,fun');

# query SET members with FIND_IN_SET
SELECT * FROM posts WHERE FIND_IN_SET('tech', tags);

CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  attrs JSON NOT NULL
);

# insert JSON literals
INSERT INTO products (id, name, attrs) VALUES
  (1, 'Widget', '{"color":"red","size":42,"tags":["new","sale"]}'),
  (2, 'Gadget', '{"color":"blue","in_stock":true}');

# JSON values are validated on insert and stored binary
SELECT attrs FROM products WHERE id = 1;

# pretty-print JSON
SELECT JSON_PRETTY(attrs) FROM products WHERE id = 1;

# binary large objects
TINYBLOB   -- up to 255 bytes
BLOB       -- up to 64 KB
MEDIUMBLOB -- up to 16 MB
LONGBLOB   -- up to 4 GB

# fixed-size binary (e.g. hashes, UUIDs)
BINARY(16)    -- fixed 16 bytes, right-padded with \0
VARBINARY(255)

# store a UUID as binary for compactness
CREATE TABLE sessions (
  id BINARY(16) PRIMARY KEY,
  data VARBINARY(1000)
);
INSERT INTO sessions (id, data) VALUES (UUID_TO_BIN(UUID()), '...');

# retrieve as text
SELECT BIN_TO_UUID(id) FROM sessions;

# single row
INSERT INTO users (username, email)
VALUES ('alice', '[email protected]');

# multiple rows in one statement
INSERT INTO users (username, email) VALUES
  ('bob', '[email protected]'),
  ('carol', '[email protected]'),
  ('dave', '[email protected]');

# insert from a SELECT
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE status = 'inactive';

# insert with column-order-free syntax
INSERT INTO users SET username='eve', email='[email protected]';

CREATE TABLE counters (
  name VARCHAR(50) PRIMARY KEY,
  hits INT NOT NULL DEFAULT 0
);

# upsert: insert or update on key conflict
INSERT INTO counters (name, hits)
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE hits = hits + 1;

# reference the proposed values with VALUES()
INSERT INTO counters (name, hits) VALUES ('about', 5)
ON DUPLICATE KEY UPDATE hits = VALUES(hits) + 1;

# MySQL 8.0.20+: use an alias for the row being inserted
INSERT INTO counters (name, hits) VALUES ('home', 1) AS new
ON DUPLICATE KEY UPDATE hits = counters.hits + new.hits;

# basic update with WHERE (always include one!)
UPDATE users
SET status = 'banned', email = NULL
WHERE id = 42;

# update with expression
UPDATE products
SET price = price * 1.10
WHERE category = 'electronics';

# multi-table update with JOIN
UPDATE users u
JOIN orders o ON o.user_id = u.id
SET u.total_spent = u.total_spent + o.amount
WHERE o.paid = 1;

# limit and order (useful for batched updates)
UPDATE logs SET archived = 1
WHERE archived = 0
ORDER BY created_at ASC
LIMIT 1000;

# delete specific rows
DELETE FROM users WHERE status = 'banned' AND last_login < '2020-01-01';

# delete with LIMIT (batched)
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 5000;

# multi-table delete with JOIN
DELETE u, o
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = 99;

# delete all rows (keeps table & AUTO_INCREMENT, unlike TRUNCATE)
DELETE FROM temp_data;

# quick delete that doesn't return row count
DELETE QUICK FROM logs WHERE year = 2020;

# REPLACE deletes then inserts on key conflict
REPLACE INTO users (id, username, email)
VALUES (1, 'alice', '[email protected]');

# REPLACE ... SET form
REPLACE INTO users SET id = 1, username = 'alice', email = '[email protected]';

# REPLACE from SELECT
REPLACE INTO daily_stats (day, visits)
SELECT CURDATE(), COUNT(*) FROM visits_log;

# caveat: REPLACE fires DELETE + INSERT triggers,
# not UPDATE, and resets columns not listed

# fast bulk import from a CSV file
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, @created)
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');

# LOCAL reads from the client machine (needs --local-infile)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (username, email);

# export counterpart
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# select all columns
SELECT * FROM users;

# select specific columns (preferred)
SELECT id, username, email FROM users;

# column aliases
SELECT username AS name, email AS contact FROM users;

# constants and expressions
SELECT 1 + 1 AS sum, NOW() AS now, 'hello' AS greeting;

# limit rows and skip (pagination)
SELECT id, username FROM users
ORDER BY id DESC
LIMIT 10 OFFSET 20;

# MySQL shorthand: LIMIT offset, count
SELECT id, username FROM users LIMIT 20, 10;

# comparison operators
SELECT * FROM products WHERE price < 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 150;
SELECT * FROM users WHERE id IN (1, 5, 9);

# logical operators
SELECT * FROM users
WHERE status = 'active' AND (age >= 18 OR verified = 1);

# NULL checks (never use = with NULL)
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

# BETWEEN is inclusive on both ends
SELECT * FROM orders WHERE created_at
  BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

# NOT IN with NULL traps: NULL poisons the result

# ascending (default) and descending
SELECT * FROM users ORDER BY created_at;
SELECT * FROM users ORDER BY created_at DESC, username ASC;

# order by expression
SELECT product, price * stock AS value
FROM inventory
ORDER BY value DESC;

# order by FIELD() for custom ordering
SELECT * FROM tasks
ORDER BY FIELD(priority, 'high', 'medium', 'low');

# stable top-N per group via window function (8.0+)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
  FROM orders
) t WHERE rn <= 3;

# unique values of one column
SELECT DISTINCT country FROM users;

# distinct over multiple columns
SELECT DISTINCT city, country FROM users;

# COUNT distinct values
SELECT COUNT(DISTINCT country) AS countries FROM users;

# GROUP BY returns one row per group; DISTINCT is a special case
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country;

# deduplicate while keeping the latest row (8.0+)
WITH latest AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
  FROM users
)
SELECT id, email FROM latest WHERE rn = 1;

# LIKE wildcards: % (any chars) and _ (one char)
SELECT * FROM users WHERE username LIKE 'a%';     -- starts with a
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE username LIKE '_an';    -- 3 chars, ends 'an'

# escape a wildcard literally
SELECT * FROM users WHERE path LIKE '50\%' ESCAPE '\\';

# case-insensitive LIKE depends on column collation
# *_ci collations are case-insensitive

# REGEXP / RLIKE for regular expressions
SELECT * FROM users WHERE email REGEXP '^[a-z]+@';
SELECT * FROM products WHERE name RLIKE 'iPhone|Galaxy';

# capture groups with REGEXP_REPLACE (8.0+)
SELECT REGEXP_REPLACE(phone, '([0-9]{3})([0-9]{4})', '$1-$2')
FROM contacts;

# searched CASE (like if/else)
SELECT username,
  CASE
    WHEN age < 18 THEN 'minor'
    WHEN age < 65 THEN 'adult'
    ELSE 'senior'
  END AS age_group
FROM users;

# simple CASE (compare to a value)
SELECT order_id,
  CASE status
    WHEN 0 THEN 'pending'
    WHEN 1 THEN 'paid'
    WHEN 2 THEN 'shipped'
    ELSE 'unknown'
  END AS status_label
FROM orders;

# CASE in aggregate (pivot)
SELECT
  SUM(CASE WHEN status='paid' THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status='shipped' THEN 1 ELSE 0 END) AS shipped_count
FROM orders;

# only matching rows from both tables
SELECT u.username, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.paid = 1;

# join three tables
SELECT u.username, o.id, oi.product_id, oi.qty
FROM users u
JOIN orders o        ON o.user_id   = u.id
JOIN order_items oi  ON oi.order_id = o.id;

# USING clause when join columns share a name
SELECT u.username, o.id
FROM users u
JOIN orders o USING (user_id);

# LEFT JOIN: all left rows, with NULLs where no match
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

# find users with NO orders (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

# RIGHT JOIN is the mirror (rarely used; swap tables instead)
SELECT u.username, o.id
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

# Cartesian product: every row of A paired with every row of B
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

# same effect with a comma join
SELECT s.size, c.color FROM sizes s, colors c;

# generate a series of dates (no built-in GENERATE_SERIES)
WITH RECURSIVE nums(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 7
)
SELECT DATE_ADD('2024-01-01', INTERVAL n-1 DAY) AS day
FROM nums;

# build a matrix of all category x region combinations
SELECT cat.name, reg.name
FROM categories cat CROSS JOIN regions reg;

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

# find pairs of users in the same city
SELECT a.username AS user_a, b.username AS user_b, a.city
FROM users a
JOIN users b ON a.city = b.city AND a.id < b.id;

# hierarchical tree with recursive CTE (8.0+)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT CONCAT(REPEAT('-- ', depth-1), name) AS tree
FROM org;

# MySQL has no FULL OUTER JOIN — emulate with LEFT + UNION + RIGHT
SELECT u.username, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION
SELECT u.username, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

# the UNION removes duplicates; use UNION ALL to keep them
# for an anti-union (mismatches only) add WHERE o.id IS NULL
#   or u.id IS NULL in each branch

# NATURAL JOIN matches all same-named columns automatically
SELECT * FROM users NATURAL JOIN profiles;

# USING matches specific shared columns, dedups them in output
SELECT u.username, user_id
FROM users u
JOIN orders o USING (user_id);

# STRAIGHT_JOIN forces the optimizer to join left-to-right
SELECT STRAIGHT_JOIN u.username, o.id
FROM users u
JOIN orders o ON o.user_id = u.id;

SELECT
  COUNT(*)               AS row_count,
  COUNT(email)           AS emails,        -- ignores NULLs
  COUNT(DISTINCT country) AS countries,
  MIN(created_at)        AS first_user,
  MAX(created_at)        AS last_user,
  AVG(age)               AS avg_age,
  SUM(balance)           AS total_balance
FROM users;

# aggregate over a filtered set
SELECT SUM(amount) AS paid_total
FROM orders
WHERE status = 'paid';

# one row per country
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
GROUP BY country;

# group by multiple columns
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city
ORDER BY country, users DESC;

# group by expression
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS m, COUNT(*)
FROM users
GROUP BY yr, m;

# MySQL 8.0 enforces ONLY_FULL_GROUP_BY:
# every non-aggregated SELECT column must be in GROUP BY

# HAVING filters groups (after aggregation); WHERE filters rows (before)
SELECT country, COUNT(*) AS users
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY users DESC;

# filter on an aggregate
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

# combine WHERE and HAVING
SELECT country, COUNT(*) AS users, AVG(age) AS avg_age
FROM users
WHERE status = 'active'
GROUP BY country
HAVING avg_age >= 30;

# ROLLUP adds subtotals and a grand total row
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;

# output includes:
#   country | city    | users
#   'US'    | 'NYC'   | 120
#   'US'    | NULL    | 200   <- US subtotal
#   NULL    | NULL    | 950   <- grand total

# identify super-aggregate rows with GROUPING()
SELECT
  IF(GROUPING(country)=1,'ALL',country) AS country,
  IF(GROUPING(city)=1,'ALL',city)       AS city,
  COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;

# concatenate grouped values into one string
SELECT country,
  GROUP_CONCAT(username) AS all_names
FROM users
GROUP BY country;

# order within the list and deduplicate
SELECT user_id,
  GROUP_CONCAT(DISTINCT tag ORDER BY tag SEPARATOR ',') AS tags
FROM post_tags
GROUP BY user_id;

# the default separator is ',', max length is 1024 by default
# raise the limit:
# SET SESSION group_concat_max_len = 1000000;

SELECT department,
  GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ' | ') AS ranking
FROM employees
GROUP BY department;

# ROLLUP gives fewer rows (subtotals mixed into the result)
SELECT country, city, COUNT(*) AS users
FROM users
GROUP BY country, city WITH ROLLUP;

# a window aggregate keeps every detail row AND adds the total
SELECT
  country, city,
  COUNT(*)              AS users_in_city,
  COUNT(*) OVER (PARTITION BY country) AS users_in_country,
  COUNT(*) OVER ()                      AS users_total
FROM users;

# running total over time
SELECT created_at, amount,
  SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM daily_sales;

# returns a single value
SELECT username, balance,
  (SELECT AVG(balance) FROM users) AS avg_balance
FROM users
WHERE balance > (SELECT AVG(balance) FROM users);

# use in SELECT list, WHERE, HAVING, etc.
SELECT product, price,
  price - (SELECT MIN(price) FROM products) AS above_min
FROM products;

# a scalar subquery must return exactly one row, one column

# IN: value matches any row of the subquery
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status='active');

# NOT IN with NULLs is dangerous — prefer NOT EXISTS
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

# ANY / SOME / ALL with comparisons
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category='toys');

SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category='toys');

# EXISTS: true if the subquery returns any rows
SELECT u.username
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

# NOT EXISTS: the standard anti-semi-join (NULL-safe)
SELECT u.username
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

# correlated: the subquery references the outer row (u.id)
# EXISTS stops at the first matching row — efficient for presence checks

# a subquery in the FROM clause is a derived table
SELECT t.country, t.users
FROM (
  SELECT country, COUNT(*) AS users
  FROM users
  GROUP BY country
) t
WHERE t.users > 50;

# join a derived table
SELECT u.username, agg.order_count
FROM users u
JOIN (
  SELECT user_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY user_id
) agg ON agg.user_id = u.id;

# derived tables must be aliased

# the subquery references the outer row (u.id) and runs per row
SELECT u.username,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

# correlated subquery in WHERE (nth-highest salary classic)
SELECT e1.name, e1.salary
FROM employees e1
WHERE 2 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

# prefer a window function for rank-style problems in 8.0+

# a named CTE (WITH) — readable, can be referenced multiple times
WITH active_users AS (
  SELECT id, username FROM users WHERE status = 'active'
)
SELECT au.username, COUNT(o.id) AS orders
FROM active_users au
LEFT JOIN orders o ON o.user_id = au.id
GROUP BY au.username;

# RECURSIVE CTE: walk a hierarchy or generate a series
WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

# multiple CTEs, comma-separated
WITH a AS (SELECT ...), b AS (SELECT ...)
SELECT * FROM a JOIN b USING (id);

# single-column index
CREATE INDEX idx_email ON users(email);

# unique index (enforces uniqueness, allows fast lookup)
CREATE UNIQUE INDEX uq_username ON users(username);

# create with a table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255),
  UNIQUE KEY uq_email (email)
);

# full-table prefix index (limited length)
CREATE INDEX idx_name ON users(last_name(20));

# multi-column index (column order matters!)
CREATE INDEX idx_last_first ON users(last_name, first_name);

# supports these lookups (leftmost prefix rule):
#   WHERE last_name = 'X'                       -> uses index
#   WHERE last_name = 'X' AND first_name = 'Y'  -> uses index
#   WHERE first_name = 'Y'                      -> CANNOT use index

# covering index: all needed columns are in the index
CREATE INDEX idx_cover ON orders(user_id, status, amount);
SELECT user_id, SUM(amount) FROM orders
WHERE user_id = 5 AND status = 'paid' GROUP BY user_id;
# ^ index-only scan, no table lookup needed

CREATE TABLE articles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT KEY ft_title_body (title, body)
) ENGINE=InnoDB;

# natural-language search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('database performance');

# boolean mode: +require -exclude ~relax
SELECT id, title
FROM articles
WHERE MATCH(title, body)
  AGAINST ('+MySQL -Oracle >index' IN BOOLEAN MODE);

# query expansion (second pass adds related terms)
SELECT id FROM articles
WHERE MATCH(title, body) AGAINST ('database' WITH QUERY EXPANSION);

CREATE TABLE places (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  location POINT NOT NULL SRID 4326,
  SPATIAL KEY sp_location (location)
) ENGINE=InnoDB;

# insert a point (longitude, latitude)
INSERT INTO places (name, location)
VALUES ('HQ', ST_PointFromText('POINT(116.40 39.90)', 4326));

# find points within a bounding distance
SELECT name, ST_Distance_Sphere(location,
  ST_PointFromText('POINT(116.40 39.90)', 4326)) AS meters
FROM places
WHERE ST_Within(location,
  ST_Buffer(ST_PointFromText('POINT(116.40 39.90)', 4326), 0.01));

# show indexes on a table
SHOW INDEX FROM users;

# drop an index
DROP INDEX idx_email ON users;
ALTER TABLE users DROP INDEX uq_username;

# rename an index (8.0+)
ALTER TABLE users RENAME INDEX idx_email TO idx_user_email;

# rebuild all indexes on a table
ALTER TABLE users ENGINE=InnoDB;
OPTIMIZE TABLE users;

# check index cardinality
SHOW INDEX FROM users;
SELECT index_name, cardinality
FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='users';

# make an index invisible (8.0+): optimizer ignores it, still maintained
ALTER TABLE users ALTER INDEX idx_email SET INVISIBLE;
# ... observe query plans, then drop or restore:
ALTER TABLE users ALTER INDEX idx_email SET VISIBLE;

# optimizer hints (8.0+) to force / avoid indexes
SELECT /*+ INDEX(u idx_email) */ * FROM users u WHERE email LIKE 'a%';
SELECT /*+ NO_INDEX(u idx_email) */ * FROM users u;
SELECT /*+ FORCE INDEX(u idx_email) */ * FROM users u;

# index merge / IGNORE_INDEX legacy hint
SELECT * FROM users USE INDEX (idx_email) WHERE email = '[email protected]';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email = '[email protected]';

# a view is a stored SELECT
CREATE VIEW active_users AS
SELECT id, username, email
FROM users
WHERE status = 'active';

# query it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';

# create or replace, with explicit columns
CREATE OR REPLACE VIEW user_summary (username, order_count, total) AS
SELECT u.username, COUNT(o.id), COALESCE(SUM(o.amount), 0)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.username;

# list and inspect views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW CREATE VIEW active_users\G

# a view over a single base table is updatable
CREATE VIEW active_users AS
SELECT id, username, email, status
FROM users
WHERE status = 'active';

# INSERT through the view inserts into the base table
INSERT INTO active_users (id, username, email, status)
VALUES (10, 'newbie', '[email protected]', 'active');

# UPDATE through the view only touches visible rows
UPDATE active_users SET email = '[email protected]' WHERE id = 10;

# WITH CHECK OPTION prevents inserts/updates that leave the view
CREATE OR REPLACE VIEW active_users AS
SELECT id, username, email, status FROM users WHERE status='active'
WITH CHECK OPTION;

# LOCAL: only the defining view's WHERE is enforced
CREATE VIEW v_paid AS
  SELECT * FROM orders WHERE status='paid' WITH LOCAL CHECK OPTION;

# CASCADED (default): all underlying views' checks are enforced too
CREATE VIEW v_paid_large AS
  SELECT * FROM v_paid WHERE amount > 100
  WITH CASCADED CHECK OPTION;

# an insert that violates v_paid's filter:
INSERT INTO v_paid_large (id, status, amount) VALUES (1, 'unpaid', 200);
# CASCADED -> rejected (status would leave v_paid)
# LOCAL    -> accepted (only v_paid_large's filter checked)

# alter a view's definition (CREATE OR REPLACE is simpler)
ALTER VIEW active_users AS
  SELECT id, username FROM users WHERE status='active';

# drop a view
DROP VIEW IF EXISTS active_users;

# drop multiple views
DROP VIEW IF EXISTS v1, v2, v3;

# rename a view (no RENAME VIEW; recreate or use a table rename trick)
RENAME TABLE old_view TO new_view;

# view metadata
SELECT table_name, view_definition, is_updatable
FROM information_schema.views
WHERE table_schema = 'mydb';

# DEFINER (default): runs with the view creator's privileges
CREATE VIEW secret_emails SQL SECURITY DEFINER AS
  SELECT email FROM users;

# INVOKER: runs with the querying user's privileges
CREATE VIEW my_emails SQL SECURITY INVOKER AS
  SELECT email FROM users WHERE id = CURRENT_USER_ID();

# ALGORITHM: MERGE (preferred) vs TEMPTABLE vs UNDEFINED
CREATE ALGORITHM=MERGE VIEW v_active AS
  SELECT * FROM users WHERE status='active';

DELIMITER //
CREATE PROCEDURE get_user_by_id(IN p_id INT)
BEGIN
  SELECT id, username, email FROM users WHERE id = p_id;
END //
DELIMITER ;

# call it
CALL get_user_by_id(42);

# drop it
DROP PROCEDURE IF EXISTS get_user_by_id;

# show the definition
SHOW CREATE PROCEDURE get_user_by_id\G

DELIMITER //
CREATE PROCEDURE transfer(
  IN  p_from   INT,
  IN  p_to     INT,
  IN  p_amount DECIMAL(10,2),
  OUT p_result VARCHAR(50)
)
BEGIN
  DECLARE v_bal DECIMAL(10,2);
  START TRANSACTION;
  SELECT balance INTO v_bal FROM accounts WHERE id = p_from FOR UPDATE;
  IF v_bal >= p_amount THEN
    UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
    UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;
    COMMIT;
    SET p_result = 'OK';
  ELSE
    ROLLBACK;
    SET p_result = 'INSUFFICIENT_FUNDS';
  END IF;
END //
DELIMITER ;

# call with a session variable for the OUT value
CALL transfer(1, 2, 50.00, @res);
SELECT @res;

DELIMITER //
CREATE PROCEDURE classify(IN p_age INT, OUT p_label VARCHAR(20))
BEGIN
  DECLARE v_count INT DEFAULT 0;

  # IF / ELSEIF / ELSE
  IF p_age < 13 THEN
    SET p_label = 'child';
  ELSEIF p_age < 20 THEN
    SET p_label = 'teen';
  ELSE
    SET p_label = 'adult';
  END IF;

  # simple loop with a counter
  simple_loop: LOOP
    SET v_count = v_count + 1;
    IF v_count >= 5 THEN LEAVE simple_loop; END IF;
  END LOOP;

  # WHILE / REPEAT alternatives
  # WHILE cond DO ... END WHILE;
  # REPEAT ... UNTIL cond END REPEAT;

  # CASE statement
  CASE p_label
    WHEN 'child' THEN SET p_label = CONCAT(p_label, '!');
    ELSE SET p_label = CONCAT(p_label, '.');
  END CASE;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE log_all_users()
BEGIN
  DECLARE v_id INT;
  DECLARE v_name VARCHAR(50);
  DECLARE done INT DEFAULT 0;

  # declare the cursor BEFORE handlers
  DECLARE cur CURSOR FOR
    SELECT id, username FROM users WHERE status='active';

  # continue handler for end of cursor -> set done=1
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_id, v_name;
    IF done THEN LEAVE read_loop; END IF;
    INSERT INTO audit_log(user_id, action) VALUES (v_id, CONCAT('seen:', v_name));
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

CALL log_all_users();

DELIMITER //
CREATE PROCEDURE safe_insert(IN p_email VARCHAR(255))
BEGIN
  # SQLEXCEPTION catches any error; SQLSTATE/errno catch specific ones
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;  -- re-raise so the caller sees the error
  END;

  DECLARE CONTINUE HANDLER FOR 1062  -- duplicate key
  BEGIN
    SELECT 'Duplicate skipped' AS msg;
  END;

  START TRANSACTION;
  INSERT INTO users (username, email) VALUES ('x', p_email);
  INSERT INTO stats (email) VALUES (p_email);
  COMMIT;
END //
DELIMITER ;

# named conditions make handlers readable
DECLARE dup_key CONDITION FOR 1062;
DECLARE CONTINUE HANDLER FOR dup_key SELECT 'dup';

# list procedures in a database
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

# show definition
SHOW CREATE PROCEDURE get_user_by_id\G

# query information_schema for routines
SELECT routine_name, routine_type, created, last_altered,
       is_deterministic, security_type
FROM information_schema.routines
WHERE routine_schema = 'mydb';

# list stored functions too
SELECT routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema = 'mydb' AND routine_type='FUNCTION';

# length & case
SELECT LENGTH('hello'), CHAR_LENGTH('héllo');  -- bytes vs chars
SELECT UPPER('abc'), LOWER('ABC');

# substring & position
SELECT SUBSTRING('MySQL', 3);           -- 'SQL'
SELECT SUBSTRING('MySQL', 1, 2);        -- 'My'
SELECT INSTR('MySQL', 'SQL');           -- 3
SELECT LOCATE('SQL', 'MySQL', 1);       -- 3

# trim & pad
SELECT TRIM('  hi  '), LTRIM('  hi'), RTRIM('hi  ');
SELECT LPAD('5', 3, '0'), RPAD('5', 3, '-');  -- 005, 5--

# split & replace
SELECT REPLACE('a-b-c', '-', '/');
SELECT SUBSTRING_INDEX('a,b,c', ',', 2);       -- 'a,b'

# concatenate with NULL-safe separator
SELECT CONCAT_WS(',', 'a', NULL, 'b');         -- 'a,b'

# rounding
SELECT ROUND(2.567, 1);   -- 2.6
SELECT CEIL(2.1), FLOOR(2.9);   -- 3, 2
SELECT TRUNCATE(2.567, 1);      -- 2.5

# modular & power
SELECT MOD(17, 5), 17 % 5;      -- 2, 2
SELECT POWER(2, 10);            -- 1024
SELECT SQRT(16);                -- 4

# random
SELECT RAND();                  -- 0..1 float
SELECT FLOOR(RAND() * 100);     -- 0..99 integer
SELECT * FROM users ORDER BY RAND() LIMIT 5;  -- random sample (slow!)

# formatting
SELECT FORMAT(1234567.891, 2);  -- '1,234,567.89'
SELECT ABS(-5), SIGN(-5);       -- 5, -1

# current values
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();

# parts of a date
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), DAYNAME(NOW());
SELECT WEEK(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW());

# arithmetic
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB('2024-01-31', INTERVAL 1 MONTH);  -- 2023-12-31
SELECT DATEDIFF('2024-12-31', '2024-01-01');      -- 365 days
SELECT TIMEDIFF('18:00:00', '09:30:00');          -- 08:30:00

# formatting
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('31/12/2024', '%d/%m/%Y');

DELIMITER //
CREATE FUNCTION full_name(p_first VARCHAR(50), p_last VARCHAR(50))
RETURNS VARCHAR(101)
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN CONCAT_WS(' ', p_first, p_last);
END //
DELIMITER ;

# use it like a built-in
SELECT id, full_name(first_name, last_name) FROM users;

# drop it
DROP FUNCTION IF EXISTS full_name;

# functions MUST declare their nature for binary logging:
#   DETERMINISTIC  - same inputs -> same output (required for replication)
#   READS SQL DATA - reads but doesn't modify
#   NO SQL         - no SQL at all

# IF(test, true_val, false_val) - ternary
SELECT IF(age >= 18, 'adult', 'minor') FROM users;

# IFNULL / NULLIF
SELECT IFNULL(email, 'no-email') FROM users;          -- coalesce one
SELECT COALESCE(nick, email, username) FROM users;    -- coalesce many
SELECT NULLIF(status, 'inactive');  -- NULL if equal, else status

# CASE as a function (see SELECT section for full CASE)
SELECT CASE WHEN x>0 THEN 'pos' WHEN x<0 THEN 'neg' ELSE 'zero' END;

# ISNULL() returns 1/0 (different from IFNULL!)
SELECT ISNULL(email) FROM users;

# CAST to a specific type
SELECT CAST('2024-01-15' AS DATE);
SELECT CAST(3.14 AS SIGNED);          -- 3
SELECT CAST('123abc' AS SIGNED);      -- 123 (warns in strict mode)
SELECT CAST(123 AS CHAR(10));

# CONVERT is the same thing with different syntax
SELECT CONVERT('2024-01-15', DATE);
SELECT CONVERT('hello' USING utf8mb4);  -- charset conversion

# common: string -> date for comparison
SELECT * FROM orders
WHERE created_at >= CAST('2024-01-01' AS DATETIME);

# format a number as a zero-padded string
SELECT LPAD(CAST(id AS CHAR), 6, '0') FROM orders;

DELIMITER //
CREATE TRIGGER trg_users_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  # normalize email to lowercase before saving
  SET NEW.email = LOWER(NEW.email);
  IF NEW.created_at IS NULL THEN
    SET NEW.created_at = NOW();
  END IF;
END //
DELIMITER ;

# AFTER INSERT trigger logs the event
CREATE TRIGGER trg_users_after_insert
AFTER INSERT ON users
FOR EACH ROW
INSERT INTO audit_log(table_name, row_id, action, at)
VALUES ('users', NEW.id, 'INSERT', NOW());

DELIMITER //
CREATE TRIGGER trg_users_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.email <> NEW.email OR (OLD.email IS NULL) <> (NEW.email IS NULL) THEN
    INSERT INTO audit_log(table_name, row_id, field, old_val, new_val, at)
    VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
  END IF;
END //
DELIMITER ;

# a DELETE audit trigger
CREATE TRIGGER trg_users_after_delete
AFTER DELETE ON users
FOR EACH ROW
INSERT INTO archive_users(id, username, email, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, NOW());

# OLD: the row before the change (available on UPDATE/DELETE)
# NEW: the row after the change  (available on INSERT/UPDATE)

# BEFORE INSERT: only NEW, you can modify it
# AFTER  INSERT: only NEW, read-only
# BEFORE UPDATE: both OLD and NEW, you can modify NEW
# AFTER  UPDATE: both OLD and NEW, read-only
# BEFORE DELETE: only OLD, read-only
# AFTER  DELETE: only OLD, read-only

CREATE TRIGGER trg_balance_check
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Balance cannot be negative';
  END IF;
END;

# MySQL allows multiple triggers with the same event/timing on one table (5.7.2+)
# order them with FOLLOWS / PRECEDES

DELIMITER //
CREATE TRIGGER trg_users_after_insert_2
AFTER INSERT ON users
FOR EACH ROW
FOLLOWS trg_users_after_insert
BEGIN
  UPDATE user_stats SET total = total + 1 WHERE user_id = NEW.id;
END //
DELIMITER ;

# events: INSERT | UPDATE | DELETE
# timing: BEFORE | AFTER
# a trigger cannot span multiple events; create one per event

# list triggers
SHOW TRIGGERS\G
SHOW TRIGGERS WHERE `Table` = 'users';

# show definition
SHOW CREATE TRIGGER trg_users_before_insert\G

# query metadata
SELECT trigger_name, event_manipulation, action_timing,
       event_object_table, created
FROM information_schema.triggers
WHERE trigger_schema = 'mydb';

# drop a trigger
DROP TRIGGER IF EXISTS trg_users_before_insert;

# triggers fire on the table's schema; definer is recorded

# explicit transaction (autocommit is on by default)
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- or ROLLBACK;

# alternative syntax
BEGIN;
  INSERT INTO orders (user_id, amount) VALUES (1, 50);
  UPDATE users SET balance = balance - 50 WHERE id = 1;
COMMIT;

# turn autocommit off for the session
SET autocommit = 0;
-- every statement is now part of a transaction until COMMIT/ROLLBACK
SET autocommit = 1;

# DDL (CREATE/ALTER/DROP) implicitly commits — cannot be rolled back

START TRANSACTION;
  INSERT INTO orders (amount) VALUES (10);
  SAVEPOINT sp_after_order;

  INSERT INTO order_items (order_id) VALUES (LAST_INSERT_ID());
  SAVEPOINT sp_after_items;

  -- something risky
  UPDATE inventory SET stock = stock - 1 WHERE sku='X';
  -- undo just the last step, keep the order + items
  ROLLBACK TO SAVEPOINT sp_after_items;

  -- release a savepoint when no longer needed
  RELEASE SAVEPOINT sp_after_order;
COMMIT;

# four levels, from least to most strict
# READ UNCOMMITTED  - dirty reads possible (rarely used)
# READ COMMITTED    - no dirty reads; non-repeatable reads possible
# REPEATABLE READ   - default; consistent reads within a transaction
# SERIALIZABLE      - locks everything; safest, slowest

# set for the session
SET SESSION transaction_isolation = 'REPEATABLE-READ';
# MySQL 8.0+ name; older: tx_isolation

# set globally (new connections inherit it)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

# inspect
SELECT @@transaction_isolation;
SELECT @@GLOBAL.transaction_isolation;

# explicit table locks (outside transactions; MyISAM-style)
LOCK TABLES users WRITE, orders READ;
  -- only these tables are accessible; users is write-locked
  SELECT * FROM users;
UNLOCK TABLES;

# READ  lock: this session can read; others can read but not write
# WRITE lock: only this session can read/write; others blocked entirely

# in InnoDB prefer row-level locks (SELECT FOR UPDATE) over LOCK TABLES
# LOCK TABLES implicitly commits the active transaction

START TRANSACTION;
  # lock the row so other transactions cannot modify it until commit
  SELECT balance INTO @b FROM accounts WHERE id = 1 FOR UPDATE;

  IF @b >= 100 THEN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  END IF;
COMMIT;   -- releases the lock

# FOR UPDATE: exclusive lock (write intent)
# FOR SHARE:  shared lock (read intent, others can read)

# NOWAIT / SKIP LOCKED reduce waiting (8.0+)
SELECT * FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED;

# deadlock: two transactions each hold a lock the other needs
# Tx1: locks A, wants B
# Tx2: locks B, wants A
# -> InnoDB detects it and rolls back the victim automatically

# see the last deadlock
SHOW ENGINE INNODB STATUS\G

# log all deadlocks to the error log
SET GLOBAL innodb_print_all_deadlocks = 1;

# mitigate by:
#  - locking tables/rows in a consistent order across transactions
#  - keeping transactions short
#  - adding appropriate indexes (avoid gap locks on full scans)
#  - using a lower isolation level where safe

# create a user that can connect from anywhere
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass!2024';

# restrict to localhost only
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'localpass';

# MySQL 8.0 default plugin is caching_sha2_password
CREATE USER 'dev'@'10.0.%.%' IDENTIFIED WITH caching_sha2_password
  BY 'devpass';

# legacy mysql_native_password (needed for some older clients)
CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password
  BY 'legacypass';

# rename or change host
RENAME USER 'app'@'%' TO 'appuser'@'10.0.0.%';

# drop a user
DROP USER IF EXISTS 'legacy'@'%';

# grant all on a specific database
GRANT ALL PRIVILEGES ON mydb.* TO 'app'@'%';

# grant read-only on one table
GRANT SELECT ON mydb.users TO 'reporting'@'%';

# grant specific privileges
GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'editor'@'localhost';

# grant the ability to grant to others (WITH GRANT OPTION)
GRANT SELECT ON mydb.* TO 'lead'@'%' WITH GRANT OPTION;

# grant at server level (use sparingly)
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'10.0.0.5';

# reload privilege tables into memory (usually automatic)
FLUSH PRIVILEGES;

# remove specific privileges
REVOKE INSERT, UPDATE ON mydb.* FROM 'editor'@'localhost';

# remove all privileges (but keep the user)
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app'@'%';

# revoke at a specific scope
REVOKE SELECT ON mydb.users FROM 'reporting'@'%';

# see what a user currently has
SHOW GRANTS FOR 'app'@'%';

# show grants for the current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

# a role is a named bundle of privileges
CREATE ROLE 'app_read', 'app_write';

GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';

# grant a role to users
GRANT 'app_read' TO 'alice'@'%', 'bob'@'%';
GRANT 'app_write' TO 'carol'@'%';

# a user must activate a role to use it (default: none)
SET DEFAULT ROLE 'app_read' TO 'alice'@'%';
SET DEFAULT ROLE ALL TO 'alice'@'%';

# see active roles
SELECT CURRENT_ROLE();

# change your own password
ALTER USER USER() IDENTIFIED BY 'NewPass!2024';

# change another user's password
ALTER USER 'app'@'%' IDENTIFIED BY 'NewAppPass!2024';

# require a password to be expired (user must reset on next login)
ALTER USER 'app'@'%' PASSWORD EXPIRE;

# enforce password policy (8.0+ validate_password component)
INSTALL COMPONENT 'file://component_validate_password';
SET GLOBAL validate_password.policy = 'MEDIUM';  -- 0/LOW,1/MEDIUM,2/STRONG
SET GLOBAL validate_password.length = 12;

# lock an account temporarily
ALTER USER 'app'@'%' ACCOUNT LOCK;
ALTER USER 'app'@'%' ACCOUNT UNLOCK;

# list all users (MySQL 8.0+)
SELECT user, host, account_locked, password_expired
FROM mysql.user;

# grants for a specific user
SHOW GRANTS FOR 'app'@'%';

# grants for the current session
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

# who am I?
SELECT CURRENT_USER(), USER();

# active roles for the current session
SELECT CURRENT_ROLE();

# list global privileges
SELECT user, host, Super_priv, Process_priv, Reload_priv
FROM mysql.user;

# dump a single database to a file
mysqldump -u root -p mydb > mydb.sql

# dump all databases
mysqldump -u root -p --all-databases > alldb.sql

# dump with routines, triggers and events
mysqldump -u root -p --routines --triggers --events mydb > mydb_full.sql

# dump schema only (no data) or data only
mysqldump -u root -p --no-data mydb > schema.sql
mysqldump -u root -p --no-create-info mydb > data.sql

# consistent dump of InnoDB in one transaction
mysqldump -u root -p --single-transaction mydb > mydb.sql

# compress on the fly
mysqldump -u root -p mydb | gzip > mydb.sql.gz

# restore a database dump (database must exist)
mysql -u root -p mydb < mydb.sql

# restore all databases (creates them)
mysql -u root -p < alldb.sql

# restore a compressed dump
gunzip < mydb.sql.gz | mysql -u root -p mydb

# restore from inside the mysql client
SOURCE /path/to/mydb.sql;

# speed up restores: disable checks and autocommit during load
SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;
SOURCE /path/to/mydb.sql;
COMMIT;
SET foreign_key_checks = 1;
SET unique_checks = 1;

# server-side export to a file (needs FILE privilege + secure_file_priv)
SELECT id, username, email, created_at
FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

# client-side export with the mysql client (no FILE privilege needed)
mysql -u root -p -e "SELECT id, username, email FROM users" \
  --batch --raw mydb > users.tsv

# include column headers with a UNION
SELECT 'id','username','email' UNION
SELECT id, username, email FROM users
INTO OUTFILE '/var/lib/mysql-files/users.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

# server-side import (needs FILE privilege)
LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, username, email, @created_at)
SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');

# client-side import (no FILE privilege, sends file from client)
LOAD DATA LOCAL INFILE 'C:/data/users.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (id, username, email);

# enable LOCAL on the client:  --local-infile=1
# and on the server:  SET GLOBAL local_infile = 1;

# enable the binary log (my.cnf)
# [mysqld]
# log_bin = mysql-bin
# binlog_format = ROW
# server_id = 1

# show current binlog files and position
SHOW BINARY LOGS;
SHOW MASTER STATUS;

# view events in a binlog file
mysqlbinlog mysql-bin.000123

# filter by time / position
mysqlbinlog --start-datetime="2024-06-01 00:00:00" \
            --stop-datetime="2024-06-01 12:00:00" \
            mysql-bin.000123 > replay.sql

# replay events to recover
mysql -u root -p < replay.sql

# purge old binlogs
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';

# install the clone plugin (8.0+) for fast physical copy
INSTALL PLUGIN clone SONAME 'mysql_clone.so';

# clone a local data directory to another path
CLONE LOCAL DATA DIRECTORY = '/var/lib/mysql-clone';

# clone from a remote donor (good for provisioning replicas)
CLONE INSTANCE FROM 'donor'@'10.0.0.2':3306
  IDENTIFIED BY 'donorpass';

# set up replication from a donor
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='10.0.0.2', SOURCE_PORT=3306,
  SOURCE_USER='repl', SOURCE_PASSWORD='replpass',
  SOURCE_AUTO_POSITION=1;
START REPLICA;   -- 8.0+ (was START SLAVE)
SHOW REPLICA STATUS\G

# show the execution plan for a query
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

# extended: extra info + warnings
EXPLAIN EXTENDED SELECT ...;
SHOW WARNINGS;

# format as a tree (8.0+) — more readable
EXPLAIN FORMAT=TREE SELECT ...;

# the key columns to read:
#   type        - access method (const > eq_ref > ref > range > index > ALL)
#   key         - the index chosen
#   rows        - estimated rows scanned
#   Extra       - 'Using index' (good) / 'Using filesort' / 'Using temporary'

# actually runs the query and reports real per-step timing
EXPLAIN ANALYZE
SELECT u.username, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

# output shows actual rows, loops and per-iterator ms:
# -> Table scan on users  (actual rows=1000, loops=1)
#     -> Covering index lookup on o  (actual rows=2.4, loops=1000)

# unlike EXPLAIN, this executes the statement (read-only queries only)

# also useful: SHOW WARNINGS after EXPLAIN to see the rewritten query
EXPLAIN SELECT ...;
SHOW WARNINGS\G

# index hints (8.0+ style, after SELECT)
SELECT /*+ INDEX(u idx_email) */ *
FROM users u
WHERE email = '[email protected]';

SELECT /*+ NO_RANGE_OPTIMIZATION(u) */ *
FROM users u WHERE id BETWEEN 1 AND 1000;

# join order / algorithm hints
SELECT /*+ JOIN_ORDER(a,b,c) */ * FROM a JOIN b ON ... JOIN c ON ...;
SELECT /*+ HASH_JOIN(b) */ * FROM a JOIN b ON a.id=b.aid;
SELECT /*+ NLJ(b) */ * FROM a JOIN b ON a.id=b.aid;

# resource hints
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM big_table;
# aborts the query after 1000 ms (read-only)

# SET_VAR hint changes a variable for one statement
SELECT /*+ SET_VAR(sort_buffer_size=16M) */ ... FROM ...;

# enable in my.cnf
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2          # log queries slower than 2s
# log_queries_not_using_indexes = 1
# log_slow_admin_statements = 1

# enable at runtime (persists until restart)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

# analyze the slow log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
#   -s t sort by total time, -t 10 top 10
#   -s c count, -s r rows, -s l lock time

# pt-query-digest (Percona Toolkit) gives richer analysis
pt-query-digest /var/log/mysql/slow.log

# profiling (deprecated but quick) - per-statement stage timings
SET profiling = 1;
SELECT COUNT(*) FROM huge_table;
SHOW PROFILE;
SHOW PROFILE FOR QUERY 1;

# preferred: performance_schema (8.0+, enabled by default)
# top queries by total latency
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

# which stage of which query took longest
SELECT event_name, format_time(timer_wait) AS took
FROM performance_schema.events_stages_history_long
ORDER BY timer_wait DESC LIMIT 10;

# 1. index columns used in WHERE / JOIN / ORDER BY
#    with the most selective equality column first
CREATE INDEX idx_user_status ON orders(user_id, status, created_at);

# 2. avoid leading wildcards (defeats the index)
SELECT * FROM users WHERE name LIKE 'A%';   -- OK
SELECT * FROM users WHERE name LIKE '%A';   -- full scan

# 3. don't wrap indexed columns in functions
SELECT * FROM orders WHERE DATE(created_at)='2024-01-01';        -- bad
SELECT * FROM orders WHERE created_at >= '2024-01-01'
  AND created_at < '2024-01-02';                                  -- good

# 4. use a covering index so the query is index-only
EXPLAIN SELECT user_id, status FROM orders WHERE user_id=5;
-- 'Using index' in Extra means no table lookup

# 5. refresh statistics after big loads
ANALYZE TABLE orders;

CREATE TABLE products (
  id    INT PRIMARY KEY,
  name  VARCHAR(100),
  attrs JSON
);

# insert JSON literals
INSERT INTO products VALUES
  (1, 'Widget',
   '{"color":"red","size":42,"tags":["new","sale"],"stock":{"ny":5,"la":3}}'),
  (2, 'Gadget', '{"color":"blue","in_stock":true}');

# build JSON with functions
INSERT INTO products VALUES (3, 'Thing',
  JSON_OBJECT('color','green','tags',JSON_ARRAY('new')));

# extract a member (returns JSON)
SELECT JSON_EXTRACT(attrs, '$.color') FROM products WHERE id=1;   -- "red"
SELECT attrs->'$.color'   FROM products WHERE id=1;               -- "red"

# extract as text (unquoted)
SELECT attrs->>'$.color'  FROM products WHERE id=1;               -- red

# array element access
SELECT attrs->>'$.tags[0]' FROM products WHERE id=1;              -- new

# nested paths
SELECT attrs->>'$.stock.ny' FROM products WHERE id=1;             -- 5

# filter rows by a JSON value
SELECT id, name FROM products
WHERE attrs->>'$.color' = 'red';

# exists check
SELECT id FROM products
WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.stock');

# SET updates or adds a member (returns the modified document)
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'green', '$.weight', 1.2)
WHERE id = 1;

# INSERT adds only if the path doesn't exist
UPDATE products
SET attrs = JSON_INSERT(attrs, '$.code', 'W-100')
WHERE id = 1;

# REPLACE updates only if the path exists
UPDATE products
SET attrs = JSON_REPLACE(attrs, '$.size', 99)
WHERE id = 1;

# REMOVE deletes a member
UPDATE products
SET attrs = JSON_REMOVE(attrs, '$.tags[0]')
WHERE id = 1;

# MERGE_PATCH merges two JSON documents (8.0+)
SET attrs = JSON_MERGE_PATCH(attrs, '{"size":50,"color":null}')

# turn a JSON array of objects into relational rows
SELECT jt.*
FROM products p,
JSON_TABLE(p.attrs, '$.tags[*]'
  COLUMNS (
    tag VARCHAR(50) PATH '$'  -- each element as a column
  )
) AS jt
WHERE p.id = 1;

# flatten an array of objects
SELECT p.id, jt.color, jt.size
FROM products p,
JSON_TABLE(p.attrs, '$'
  COLUMNS (
    color VARCHAR(20) PATH '$.color',
    size  INT          PATH '$.size'
  )
) AS jt;

# nested arrays: NESTED PATH
JSON_TABLE(j, '$.stock.*'
  COLUMNS (warehouse VARCHAR(5) PATH '$.wh', qty INT PATH '$.qty'))

# JSON_ARRAYAGG: collect values into a JSON array
SELECT user_id,
  JSON_ARRAYAGG(email) AS emails
FROM contacts
GROUP BY user_id;
-- ["[email protected]","[email protected]"]

# JSON_OBJECTAGG: key/value pairs into a JSON object
SELECT user_id,
  JSON_OBJECTAGG(type, value) AS prefs
FROM user_prefs
GROUP BY user_id;
-- {"theme":"dark","lang":"en"}

# build a JSON array of objects with JSON_OBJECT + ARRAYAGG
SELECT JSON_ARRAYAGG(
  JSON_OBJECT('id', id, 'name', username)
) AS users
FROM users WHERE status='active';

# validate a string is JSON
SELECT JSON_VALID('{"a":1}');          -- 1
SELECT JSON_VALID('{bad}');            -- 0

# pretty-print and minify
SELECT JSON_PRETTY(attrs) FROM products WHERE id=1;
SELECT JSON_STORAGE_SIZE(attrs) FROM products WHERE id=1;  -- bytes

# JSON_SCHEMA_VALID (8.0.17+) enforces a schema with CHECK
CREATE TABLE events (
  id INT PRIMARY KEY,
  data JSON,
  CHECK (JSON_SCHEMA_VALID(
    '{"type":"object","required":["ts","type"],
      "properties":{"ts":{"type":"string"},
                    "type":{"type":"string"}}}', data))
);

# JSON_TABLE with ERROR ON ERROR for strict validation
SELECT * FROM JSON_TABLE(j, '$' COLUMNS(v INT PATH '$.x')
  DEFAULT NULL ON EMPTY ERROR ON ERROR) AS t;

# unique sequential number per row within a partition
SELECT username, country,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) AS rn
FROM users;

# top 3 oldest users per country
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY birth_date) AS rn
  FROM users
)
SELECT * FROM ranked WHERE rn <= 3;

# deduplicate: keep the latest row per email
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) AS rn
  FROM users
)
DELETE FROM users WHERE id IN (
  SELECT id FROM ranked WHERE rn > 1
);

# all three ranking functions on the same data
SELECT name, score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM players;

# with scores 100,100,90,80 the functions return:
#   rn: 1,2,3,4         (always sequential)
#   rnk:1,2,4,5         (ties share a rank, next rank skips)
#   drnk:1,2,3,4        (ties share a rank, no gap)

# find the 2nd-highest salary per department
WITH ranked AS (
  SELECT dept, salary,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr
  FROM employees
)
SELECT DISTINCT dept, salary FROM ranked WHERE dr = 2;

# compare each row to the previous/next row
SELECT day, sales,
  LAG(sales)  OVER (ORDER BY day) AS prev_day,
  sales - LAG(sales) OVER (ORDER BY day) AS diff,
  LEAD(sales) OVER (ORDER BY day) AS next_day
FROM daily_sales;

# with an explicit offset and default value
SELECT day, sales,
  LAG(sales, 7, 0) OVER (ORDER BY day) AS same_day_last_week
FROM daily_sales;

# year-over-year comparison
SELECT year, region, revenue,
  LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS prev_year,
  revenue - LAG(revenue) OVER (PARTITION BY region ORDER BY year) AS yoy
FROM annual_revenue;

# running total over time
SELECT day, sales,
  SUM(sales) OVER (ORDER BY day) AS running_total
FROM daily_sales;

# moving average over a 7-day window
SELECT day, sales,
  AVG(sales) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_sales;

# each row's share of its group total
SELECT user_id, amount,
  amount / SUM(amount) OVER (PARTITION BY user_id) AS share
FROM orders;

# grand total alongside detail rows
SELECT username, balance,
  SUM(balance) OVER () AS total_balance
FROM users;

# first value in each partition (per ordering)
SELECT day, region, sales,
  FIRST_VALUE(sales) OVER (PARTITION BY region ORDER BY day) AS first_sale
FROM daily_sales;

# LAST_VALUE needs an explicit frame to include current row!
SELECT day, region, sales,
  LAST_VALUE(sales) OVER (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS latest_so_far
FROM daily_sales;

# the Nth value within a partition
SELECT day, region, sales,
  NTH_VALUE(sales, 3) OVER (
    PARTITION BY region ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third
FROM daily_sales;

# divide rows into N equal buckets (1..N)
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;
-- quartile 1 = top 25%, quartile 4 = bottom 25%

# relative rank as a fraction 0..1
SELECT name, salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct,
  CUME_DIST()    OVER (ORDER BY salary) AS cum
FROM employees;
-- pct  = (rank-1)/(N-1), 0 = lowest, 1 = highest
-- cum  = (rows up to and including current)/N

# CUME_DIST: fraction of rows with a value <= current

# current timestamp (date + time) in one call
SELECT NOW(), SYSDATE(), CURRENT_TIMESTAMP, LOCALTIME;

# current date / time separately
SELECT CURDATE(), CURRENT_DATE;     -- '2024-07-02'
SELECT CURTIME(), CURRENT_TIME;     -- '14:30:00'

# UTC equivalents (store UTC, display local)
SELECT UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();

# microsecond precision with (6)
SELECT NOW(6), CURRENT_TIMESTAMP(6);

# UNIX timestamp (seconds since epoch) and back
SELECT UNIX_TIMESTAMP();                 -- 1719912600
SELECT FROM_UNIXTIME(1719912600);        -- '2024-07-02 12:30:00'
SELECT FROM_UNIXTIME(1719912600, '%Y-%m-%d');

# format a date/time to a string
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- 2024-07-02 14:30:00
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');            -- 02/07/2024
SELECT DATE_FORMAT(NOW(), '%W, %M %e %Y');        -- Tuesday, July 2 2024

# parse a string into a date
SELECT STR_TO_DATE('02/07/2024', '%d/%m/%Y');     -- 2024-07-02
SELECT STR_TO_DATE('Jul 2, 2024 2:30 PM', '%b %e, %Y %h:%i %p');

# common specifiers:
#   %Y 4-digit year   %y 2-digit year
#   %m month(01-12)   %c month(1-12)   %M month name   %b abbreviated
#   %d day(01-31)     %e day(1-31)     %j day of year
#   %H hour(00-23)    %h hour(01-12)   %i minutes  %s seconds
#   %W weekday name   %a abbreviated   %p AM/PM

# add an interval to a date
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);   -- 2024-02-29
SELECT DATE_ADD('2024-01-31', INTERVAL 1 DAY);     -- 2024-02-01
SELECT '2024-01-31' + INTERVAL 1 MONTH;            -- shorthand

# subtract
SELECT DATE_SUB(NOW(), INTERVAL 7 DAY);
SELECT NOW() - INTERVAL 1 HOUR;

# interval units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
#   SECOND_MICROSECOND MINUTE_MICROSECOND ... YEAR_MONTH (compound)

# add to a datetime (keeps the time component)
SELECT DATE_ADD(NOW(), INTERVAL 30 MINUTE);

# last day of next month
SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

# DATEDIFF: difference in DAYS (date1 - date2)
SELECT DATEDIFF('2024-12-31', '2024-01-01');   -- 365
SELECT DATEDIFF(NOW(), birth_date) AS days_alive FROM users;

# TIMESTAMPDIFF: difference in a chosen unit (date1, date2) -> date2 - date1
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age;
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-07-02');
SELECT TIMESTAMPDIFF(MICROSECOND, '2024-07-02 09:00:00', NOW());

# units: MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR
# (TIMESTAMPDIFF argument order is (unit, from, to))

# TIME values: TIMEDIFF returns a TIME
SELECT TIMEDIFF('18:00:00', '09:30:00');   -- 08:30:00

# pull out a single component
SELECT EXTRACT(YEAR FROM created_at)   AS yr,
       EXTRACT(MONTH FROM created_at)  AS mon,
       EXTRACT(DAY FROM created_at)    AS dy
FROM orders;

# dedicated functions for each part
SELECT YEAR(created_at), MONTH(created_at), DAY(created_at),
       HOUR(created_at), MINUTE(created_at), SECOND(created_at),
       DAYOFWEEK(created_at), DAYOFYEAR(created_at),
       WEEK(created_at), QUARTER(created_at);

# names instead of numbers
SELECT DAYNAME(created_at), MONTHNAME(created_at);

# group orders by month for a report
SELECT YEAR(created_at) AS yr, MONTH(created_at) AS mon,
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY yr, mon
ORDER BY yr, mon;

# see the session time zone
SELECT @@session.time_zone, @@global.time_zone;

# set the session time zone (UTC offset or named, needs tz data loaded)
SET time_zone = '+08:00';            # Asia/Shanghai offset
SET time_zone = 'Asia/Shanghai';     # named zone (load tz tables first)

# convert a datetime between time zones
SELECT CONVERT_TZ('2024-07-02 09:00:00', '+00:00', '+08:00');
-- 2024-07-02 17:00:00

# named zones require the timezone tables to be loaded:
#   mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

# TIMESTAMP columns auto-convert to the session time zone on display;
# DATETIME columns do not -- they store the literal value

# store all times in UTC and convert on the way out
SELECT CONVERT_TZ(created_at, '+00:00', @@session.time_zone)
FROM orders;