1Z0-909 cheat sheet

1Z0-909 · Keep the key ideas within reach.

Quick reference

MySQL 8.0 Database Developer

Connector/J URL parameters: useSSL, requireSSL, verifyServerCertificate; caching_sha2_password requires allowPublicKeyRetrieval=true if no SSL.
JSON functions: JSON_EXTRACT(doc, path), JSON_UNQUOTE, -> and ->> operators; ->> returns unquoted scalar, -> returns JSON value.
CTE syntax: WITH cte_name AS (subquery) SELECT ...; recursive CTE requires WITH RECURSIVE and UNION ALL with termination condition.
Window functions: ROW_NUMBER() OVER (PARTITION BY col ORDER BY col), RANK(), DENSE_RANK(); cannot be used in WHERE clause.
Generated columns: VIRTUAL (default, computed on read) or STORED (computed on write); can be indexed only if STORED or if VIRTUAL with index support in InnoDB.
Transaction isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ (default), SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL.
Character set and collation: utf8mb4 is default in 8.0; collation utf8mb4_0900_ai_ci is default; use COLLATE in ORDER BY or comparisons.
EXPLAIN ANALYZE provides actual execution statistics; EXPLAIN FORMAT=JSON shows query plan; use FORMAT=TREE for tree output.
InnoDB locking: SELECT ... FOR UPDATE acquires exclusive locks; SELECT ... FOR SHARE acquires shared locks; LOCK IN SHARE MODE deprecated.