Truy vấn MySQL nâng cao

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id
UNION
SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id;

-- Khi optimizer chọn thứ tự sai, ép join theo thứ tự viết
SELECT STRAIGHT_JOIN * FROM small s JOIN big b ON b.s_id = s.id;

-- Scalar
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS cnt
FROM users u;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Derived table (bắt buộc alias!)
SELECT * FROM (
    SELECT user_id, SUM(total) total FROM orders GROUP BY user_id
) t WHERE t.total > 10000000;

SELECT *
FROM (
    SELECT user_id, SUM(total) AS total
    FROM orders
    GROUP BY user_id
) t
WHERE t.total > 10000000;

-- Lấy 3 đơn hàng gần nhất của mỗi user
SELECT u.name, o.order_date, o.total
FROM users u
JOIN LATERAL (
    SELECT order_date, total
    FROM orders
    WHERE user_id = u.id          -- ← tham chiếu u.id từ bảng ngoài
    ORDER BY order_date DESC
    LIMIT 3
) o ON TRUE;

-- Cách 1: Window function (luôn scan toàn bộ partition)
WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

-- Cách 2: LATERAL (có thể dừng sau khi lấy đủ 3 row)
SELECT u.name, o.*
FROM users u
JOIN LATERAL (
    SELECT * FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) o ON TRUE;

-- UNION — loại bỏ duplicate (phải sort/distinct → chậm hơn)
SELECT email FROM customers
UNION
SELECT email FROM suppliers;

-- UNION ALL — giữ tất cả row, kể cả duplicate (nhanh hơn)
SELECT email FROM customers
UNION ALL
SELECT email FROM suppliers;

-- INTERSECT — row xuất hiện trong CẢ 2 tập (MySQL 8.0.31+)
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;

-- EXCEPT — row có trong tập 1 nhưng KHÔNG có trong tập 2 (MySQL 8.0.31+)
SELECT email FROM customers
EXCEPT
SELECT email FROM banned;

SELECT id, name, 'customer' AS type FROM customers
UNION ALL
SELECT id, name, 'supplier' FROM suppliers
ORDER BY type, name;

WITH high_value AS (
    SELECT * FROM orders WHERE total > 10000000
)
SELECT u.name, COUNT(*)
FROM high_value h JOIN users u ON u.id = h.user_id
GROUP BY u.name;

WITH
    active_users AS (
        SELECT id, name FROM users WHERE status = 'active'
    ),
    user_totals AS (
        SELECT user_id, SUM(total) AS total_spent
        FROM orders
        WHERE created_at >= '2025-01-01'
        GROUP BY user_id
    ),
    ranked AS (
        SELECT
            au.name,
            ut.total_spent,
            RANK() OVER (ORDER BY ut.total_spent DESC) AS rk
        FROM active_users au
        JOIN user_totals ut ON ut.user_id = au.id
    )
SELECT name, total_spent, rk
FROM ranked
WHERE rk <= 10;

WITH RECURSIVE org AS (
    -- Anchor: CEO (không có manager)
    SELECT id, name, manager_id, 0 AS lvl,
           CAST(name AS CHAR(500)) AS path
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: nhân viên dưới quyền
    SELECT e.id, e.name, e.manager_id, o.lvl + 1,
           CONCAT(o.path, ' > ', e.name)
    FROM employees e JOIN org o ON o.id = e.manager_id
)
SELECT id, name, lvl, path
FROM org
ORDER BY path;

-- Sản phẩm lắp ráp: tìm tất cả linh kiện con của sản phẩm #1
WITH RECURSIVE bom AS (
    -- Anchor: linh kiện trực tiếp
    SELECT product_id, component_id, quantity, 1 AS lvl
    FROM bill_of_materials
    WHERE product_id = 1

    UNION ALL

    -- Recursive: linh kiện con của linh kiện
    SELECT b.product_id, b.component_id, b.quantity, bom.lvl + 1
    FROM bill_of_materials b
    JOIN bom ON bom.component_id = b.product_id
)
SELECT b.lvl, b.product_id, p.name AS assembly,
       b.component_id, c.name AS component, b.quantity
FROM bom b
JOIN products p ON p.id = b.product_id
JOIN products c ON c.id = b.component_id
ORDER BY b.lvl, b.product_id;

WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100
)
SELECT * FROM seq;

-- Generate calendar
WITH RECURSIVE dates(d) AS (
    SELECT '2025-01-01'
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2025-12-31'
)
SELECT d FROM dates;

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(created_at, '%Y-%m') AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_FORMAT(created_at, '%Y-%m')
),
with_growth AS (
    SELECT
        month, revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month))
            / LAG(revenue) OVER (ORDER BY month) * 100,
            2
        ) AS growth_pct
    FROM monthly_sales
)
SELECT * FROM with_growth
WHERE growth_pct IS NOT NULL
ORDER BY month;

-- ROWS: đếm theo số dòng vật lý
AVG(total) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW    -- 7 dòng gần nhất
)

-- RANGE: đếm theo giá trị của ORDER BY column
AVG(total) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW  -- 7 ngày gần nhất
)

-- GROUPS: đếm theo nhóm peer (các row có cùng giá trị ORDER BY)
AVG(total) OVER (
    PARTITION BY user_id
    ORDER BY created_at
    GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW  -- nhóm hiện tại + 1 nhóm trước
)

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY created_at DESC
        ) AS rn,
        RANK() OVER (
            PARTITION BY user_id ORDER BY total DESC
        ) AS rank_by_value
    FROM orders
)
SELECT * FROM ranked
WHERE rn <= 3;       -- 3 đơn gần nhất mỗi user

-- RANK vs DENSE_RANK vs ROW_NUMBER:
-- VALUES:    100, 100,  90,  80,  80
-- RANK:        1,   1,   3,   4,   4   ← bỏ gap
-- DENSE_RANK:  1,   1,   2,   3,   3   ← không bỏ gap
-- ROW_NUMBER:  1,   2,   3,   4,   5   ← unique

SELECT
    order_date,
    total,
    -- Running total: tổng lũy kế
    SUM(total) OVER (ORDER BY order_date) AS running_total,

    -- Moving average 7 dòng (ROWS)
    AVG(total) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma7_rows,

    -- Moving average 7 ngày thực (RANGE)
    AVG(total) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS ma7_range
FROM orders;

SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(total) AS revenue,
    LAG(SUM(total)) OVER (
        ORDER BY DATE_FORMAT(created_at, '%Y-%m')
    ) AS prev_month,
    LEAD(SUM(total)) OVER (
        ORDER BY DATE_FORMAT(created_at, '%Y-%m')
    ) AS next_month,
    ROUND(
        (SUM(total) - LAG(SUM(total)) OVER (
            ORDER BY DATE_FORMAT(created_at, '%Y-%m')
        ))
        / LAG(SUM(total)) OVER (
            ORDER BY DATE_FORMAT(created_at, '%Y-%m')
        ) * 100,
        2
    ) AS mom_growth_pct
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

SELECT
    user_id, order_date, total,
    FIRST_VALUE(total) OVER (
        PARTITION BY user_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order_value,
    NTH_VALUE(total, 2) OVER (
        PARTITION BY user_id ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_order_value
FROM orders;

SELECT
    name, score,
    ROUND(PERCENT_RANK() OVER (ORDER BY score DESC) * 100, 1) AS percentile,
    ROUND(CUME_DIST() OVER (ORDER BY score DESC) * 100, 1) AS cumulative_pct
FROM exam_results;

-- Tìm email Gmail
SELECT email FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\\.com$';

-- RLIKE là alias của REGEXP
SELECT * FROM products
WHERE name RLIKE '^(iPhone|iPad|Mac)';

-- Case-insensitive (mặc định)
SELECT * FROM users WHERE name REGEXP '^nguyen';

-- Chuẩn hóa số điện thoại: xóa tất cả ký tự không phải số
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS clean_phone
FROM contacts;

-- Mask email: giữ ký tự đầu và domain, ẩn phần giữa
SELECT REGEXP_REPLACE(email, '(.).*(@.*)', '$1***$2') AS masked_email
FROM users;

-- Trích xuất domain từ email
SELECT email,
       REGEXP_SUBSTR(email, '@(.+)$') AS domain
FROM users;

-- Trích xuất mã bưu điện từ địa chỉ
SELECT address,
       REGEXP_SUBSTR(address, '[0-9]{5,6}') AS postal_code
FROM shipping_addresses;

-- Vị trí bắt đầu của dãy số đầu tiên trong chuỗi
SELECT REGEXP_INSTR('Product-12345-XYZ', '[0-9]+') AS number_start;
-- → 9

-- Vị trí kết thúc (dùng option thứ 6 = 1)
SELECT REGEXP_INSTR('Product-12345-XYZ', '[0-9]+', 1, 1, 1) AS number_end;
-- → 13

SELECT
    payload->'$.user' AS user_json,              -- trả về JSON object (có quote)
    payload->>'$.user.name' AS user_name,        -- trả về text (đã unquote)
    JSON_EXTRACT(payload, '$.tags[0]') AS first_tag;

-- Kiểm tra array có chứa giá trị
SELECT * FROM events
WHERE JSON_CONTAINS(payload->'$.tags', '"vue"');

-- Kiểm tra path có tồn tại
SELECT * FROM events WHERE JSON_CONTAINS_PATH(payload, 'one', '$.user.id');

-- Search text trong toàn bộ document
SELECT * FROM events
WHERE JSON_SEARCH(payload, 'all', '%vue%') IS NOT NULL;

-- Cho JSON: {"items": [{"name": "Áo thun", "qty": 2}, {"name": "Quần jean", "qty": 1}]}
SELECT jt.*
FROM orders,
JSON_TABLE(
    payload,
    '$.items[*]'
    COLUMNS (
        item_name VARCHAR(100) PATH '$.name',
        quantity INT PATH '$.qty',
        row_num FOR ORDINALITY
    )
) AS jt;

-- Nested JSON — lồng nhiều cấp với NESTED PATH
-- Cho JSON: {"categories": [{"cat": "Thời trang", "products": [{"name": "Áo", "price": 200}]}]}
SELECT jt.cat_name, jt.product_name, jt.price
FROM orders,
JSON_TABLE(payload, '$.categories[*]'
    COLUMNS (
        cat_name VARCHAR(50) PATH '$.cat',
        NESTED PATH '$.products[*]' COLUMNS (
            product_name VARCHAR(100) PATH '$.name',
            price DECIMAL(10,2) PATH '$.price'
        )
    )
) AS jt;

-- Cách 1: Generated column (mọi version hỗ trợ JSON)
ALTER TABLE events
    ADD COLUMN event_type VARCHAR(50)
        AS (payload->>'$.type') VIRTUAL,
    ADD INDEX idx_type (event_type);

-- Query dùng generated column → dùng index
SELECT * FROM events WHERE event_type = 'click';

-- Cách 2: Functional index (8.0.13+) — không cần thêm cột
CREATE INDEX idx_event_type ON events ((CAST(payload->>'$.type' AS CHAR(50))));

-- GROUP BY chuẩn
SELECT city, COUNT(*) cnt
FROM users WHERE active = 1
GROUP BY city
HAVING cnt > 10
ORDER BY cnt DESC;

-- GROUP BY chỉ cho phép cột trong GROUP BY hoặc aggregate
-- (default ONLY_FULL_GROUP_BY mode từ 5.7+)
-- ❌ SELECT id, name, COUNT(*) FROM users GROUP BY city  -- lỗi

-- GROUPING SETS / ROLLUP
SELECT city, status, COUNT(*)
FROM orders
GROUP BY city, status WITH ROLLUP;     -- thêm row subtotal + grand total

START TRANSACTION;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;
-- Hoặc ROLLBACK;

-- Savepoint
START TRANSACTION;
INSERT ...;
SAVEPOINT sp1;
UPDATE ...;
ROLLBACK TO sp1;
COMMIT;

-- Mặc định MỖI statement = 1 transaction (auto-commit ON)
SET AUTOCOMMIT = 0;
-- Sau đó phải COMMIT/ROLLBACK explicit

-- Set level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 4 level MySQL hỗ trợ
-- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

-- Trong REPEATABLE READ, InnoDB lock cả khoảng trống giữa các index value
-- để chống phantom read
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- Lock không chỉ row 10-20 mà cả "gap" giữa các row → INSERT vào khoảng này bị block

START TRANSACTION;

-- Lock exclusive — chặn UPDATE và FOR UPDATE khác
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- Lock shared — cho phép FOR SHARE khác đọc, chặn UPDATE
SELECT * FROM products WHERE id = 1 FOR SHARE;

UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Xem deadlock gần nhất
SHOW ENGINE INNODB STATUS;

-- Bật log deadlock vào error log
SET GLOBAL innodb_print_all_deadlocks = ON;

EXPLAIN SELECT * FROM orders WHERE user_id = 1;

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;   -- 8.0+, chạy thực và đo

system   — bảng 1 row
const    — match constant 1 row (PK)
eq_ref   — JOIN unique key, 1 row mỗi outer
ref      — JOIN non-unique key
range    — index range
index    — full index scan
ALL      — full table scan ⚠️

-- Bật ở session level (chỉ ảnh hưởng connection hiện tại)
SET SESSION optimizer_switch = 'hypergraph=on';

-- Bật ở global level (áp dụng cho tất cả connection mới)
SET GLOBAL optimizer_switch = 'hypergraph=on';

-- Per-statement: dùng optimizer hint — không cần thay đổi setting
SELECT /*+ SET_VAR(optimizer_switch='hypergraph=on') */
    c.name, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY c.name
ORDER BY revenue DESC;

-- Sau khi chạy query, kiểm tra optimizer nào đã được sử dụng
SELECT @@last_optimizer_used;
-- Kết quả: 'hypergraph' hoặc 'old'

-- Kiểm tra version MySQL
SELECT VERSION();

-- Xem trạng thái hypergraph trong optimizer_switch
SHOW VARIABLES LIKE 'optimizer_switch';

-- Force dùng old optimizer nếu cần (per-statement)
SELECT /*+ SET_VAR(optimizer_switch='hypergraph=off') */
    * FROM orders WHERE user_id = 1;

CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Covering index
CREATE INDEX idx_orders_cover ON orders(user_id, created_at, total, status);

-- Functional index (8.0.13+)
CREATE INDEX idx_lower_email ON users ((LOWER(email)));

-- Descending index (8.0+)
CREATE INDEX idx_orders_recent ON orders(created_at DESC);

-- Drop
DROP INDEX idx_orders_user_date ON orders;