01Connect & Sessioncommand-line client
02DatabasesDDL
CREATE DATABASE db;★Create a new database (schema).CREATE DATABASE IF NOT EXISTS db;Idempotent create — no error if it exists.USE db;★Set the active database for the session.ALTER DATABASE db CHARACTER SET utf8mb4;utf8mb4 is the 8.0 default — full emoji/Unicode.DROP DATABASE db;destroysDelete database + every table in it.
03Create & Inspect TablesDDL
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));★Define columns, types & keys in one go.CREATE TABLE t2 LIKE t1;Clone structure only (no rows).CREATE TABLE t2 AS SELECT ...;Create + populate from a query (CTAS).RENAME TABLE old TO new;Rename a table in place.DROP TABLE IF EXISTS t;destroysDelete table + data + structure.
04Alter Tablereshape structure
ALTER TABLE t ADD col VARCHAR(50);★Add a new column.ALTER TABLE t ADD col INT, ALGORITHM=INSTANT;★Instant DDL — metadata-only, no rebuild (8.0.12+).ALTER TABLE t MODIFY col INT NOT NULL;Change a column's type/constraints, same name.ALTER TABLE t RENAME COLUMN old TO new;Rename only, type unchanged (8.0+).ALTER TABLE t DROP COLUMN c;destroysRemove a column and its data.ALTER TABLE t ADD PRIMARY KEY (id);Add a primary key after the fact.
05Data Typeswhat a column can hold
Numeric
| TINYINT | 1B · ±127 (0–255 unsigned) |
| SMALL/MEDIUM/INT | 2 / 3 / 4 bytes |
| BIGINT | 8 bytes · ±9.2×10¹⁸ |
| DECIMAL(M,D) | exact fixed-point — use for money |
| FLOAT / DOUBLE | approximate floating-point |
String
| CHAR / VARCHAR(M) | fixed / variable length |
| TEXT / LONGTEXT | 64KB → 4GB of text |
| ENUM / SET | one / many from a fixed list |
| BLOB / LONGBLOB | binary, same size tiers as TEXT |
Date & Time
| DATE / TIME / YEAR | calendar parts |
| DATETIME | YYYY-MM-DD HH:MM:SS, no TZ shift |
| TIMESTAMP | UTC-stored, 1970–2038 |
Other & 8.0
| BOOLEAN | alias for TINYINT(1) |
| JSON | native, validated, binary-stored (5.7+) |
| GEOMETRY / POINT | spatial, SRID-aware in 8.0 |
06Insert DataDML
INSERT INTO t (a,b) VALUES (1,2);★Insert one row into named columns.INSERT INTO t (a,b) VALUES (1,2),(3,4);★Insert multiple rows in one statement.INSERT INTO t2 SELECT * FROM t1;Copy rows from one table into another.INSERT INTO t (a) VALUES (1) AS new ON DUPLICATE KEY UPDATE a=new.a;★Upsert — 8.0.19+ alias form (replaces VALUES()).INSERT IGNORE INTO t VALUES (...);Skip rows that would error (dup key etc).REPLACE INTO t VALUES (...);Delete-then-insert on key conflict.
07Select — BasicsDQL
SELECT * FROM t;★Every column, every row.SELECT a, b FROM t;★Only the columns you need — cheaper.SELECT DISTINCT col FROM t;Unique values only.SELECT price*1.1 AS with_tax FROM t;Computed column with an alias.SELECT * FROM t LIMIT 10;★Cap the number of rows returned.
08Filter Rows — WHEREDQL
... WHERE age > 21;★Comparison: = < > <= >= != (or <>).... WHERE a=1 AND b=2;★Combine conditions — also OR, NOT.... WHERE age BETWEEN 18 AND 30;Inclusive range check.... WHERE status IN ('new','open');★Match any value in a list.... WHERE name LIKE 'A%';★Pattern match — % any run, _ one char.... WHERE deleted_at IS NULL;★NULL check — never use = NULL.... WHERE col <=> NULL;NULL-safe equality (MySQL-specific).
09Sort & PaginateDQL
... ORDER BY created_at DESC;★Newest / largest first (ASC is default).... ORDER BY a, b DESC;Multi-key sort, mixed directions.... LIMIT 20 OFFSET 40;★Page 3 of 20-row pages.... LIMIT 40, 20;Shorthand — offset, count.... ORDER BY RAND() LIMIT 1;One random row — fine for small tables.
10JoinsDQL · combine tables
... FROM a JOIN b ON a.id=b.a_id;★INNER JOIN — only matching rows both sides.... FROM a LEFT JOIN b ON a.id=b.a_id;★All of a, matched b or NULLs.... FROM a RIGHT JOIN b ON a.id=b.a_id;All of b, matched a or NULLs.... FROM a CROSS JOIN b;Cartesian product, every combination.... FROM a JOIN b USING (id);Shorthand when the join column shares a name.... , LATERAL (SELECT ... WHERE x=a.id) dDerived table that references the outer row (8.0.14+).LEFT JOIN ... UNION ... RIGHT JOIN ...Emulates FULL OUTER JOIN (no native support).
11Group & AggregateDQL · roll rows up
SELECT dept, COUNT(*) FROM t GROUP BY dept;★One output row per distinct group.... GROUP BY dept HAVING COUNT(*) > 5;★Filter groups — runs after grouping.COUNT(*), SUM, AVG, MIN, MAX★The five core aggregate functions.GROUP_CONCAT(name SEPARATOR ', ')Concatenate group values into one string.... GROUP BY dept WITH ROLLUP;Adds subtotal / grand-total rows.... GROUPING(dept)Distinguish a real NULL from a ROLLUP super-aggregate.
12Set OperationsDQL · combine result sets
SELECT ... UNION SELECT ...★Stack two results, de-duplicated.SELECT ... UNION ALL SELECT ...★Stack & keep duplicates — faster.SELECT ... INTERSECT SELECT ...8.0.31+Rows present in both queries.SELECT ... EXCEPT SELECT ...8.0.31+Rows in the first query but not the second.VALUES ROW(1,'a'), ROW(2,'b')Table-value constructor as a standalone statement (8.0.19+).
13CTEs 8.0+WITH · named subqueries
WITH cte AS (SELECT ...) SELECT * FROM cte;★Name a subquery, reference it like a table — readable, reusable.WITH a AS (...), b AS (...) SELECT ...Chain multiple CTEs, comma-separated.WITH RECURSIVE cte AS (SELECT ... UNION ALL SELECT ... FROM cte) ...★Walk hierarchies (org charts, trees) or generate series.
14Window Functions 8.0+per-row calc over a window
ROW_NUMBER() OVER (ORDER BY total DESC)★Sequential 1,2,3… — no ties.RANK() / DENSE_RANK() OVER (ORDER BY x)★Ranking with ties — RANK gaps, DENSE_RANK doesn't.SUM(amt) OVER (PARTITION BY dept)★Aggregate per group without collapsing rows.LAG(x) / LEAD(x) OVER (ORDER BY d)★Value from the previous / next row — deltas, trends.NTILE(4) OVER (ORDER BY score)Split rows into N buckets (quartiles etc).FIRST_VALUE / LAST_VALUE / NTH_VALUEPull a specific row's value across the window.... OVER w ... WINDOW w AS (PARTITION BY dept ORDER BY x)Name a window once, reuse it across functions.
15JSON 8.0+native document handling
col->'$.name' · col->>'$.name'★Extract / extract-and-unquote — shorthand for JSON_EXTRACT.JSON_EXTRACT(col, '$.a.b')Pull a value at a path.JSON_OBJECT('k',v) · JSON_ARRAY(1,2)Build JSON from columns.JSON_SET/JSON_REPLACE/JSON_REMOVE(col,'$.a',1)★Partial, in-place updates to a document.JSON_CONTAINS(col, '"red"', '$.tags')Membership test inside a document.JSON_TABLE(col,'$[*]' COLUMNS (id INT PATH '$.id')) jt★Turn a JSON array into relational rows you can JOIN.JSON_ARRAYAGG(x) · JSON_OBJECTAGG(k,v)Aggregate rows back into a JSON array / object.JSON_VALUE(col,'$.a' RETURNING INT)Extract + type-cast in one call (8.0.21+).
16Regular Expressions 8.0+ICU engine, full Unicode
col REGEXP '^A.*z$'★Pattern match in WHERE — RLIKE is a synonym.REGEXP_LIKE(col, 'pat', 'i')★Match with flags — 'i' = case-insensitive.REGEXP_REPLACE(col, 'pat', 'repl')★Search-and-replace by pattern.REGEXP_SUBSTR(col, '[A-Z]{2}-[0-9]+')Extract the first matching substring.REGEXP_INSTR(col, 'pat')Position of the match (0 if none).
17Full-Text SearchInnoDB & MyISAM
CREATE FULLTEXT INDEX ft ON articles(title, body);★Index CHAR/VARCHAR/TEXT columns for word search.... WHERE MATCH(title,body) AGAINST('cake')★Natural-language search, relevance-ranked.... AGAINST('+cake -mud' IN BOOLEAN MODE)★Boolean operators: + require, - exclude, * prefix.... AGAINST('run' WITH QUERY EXPANSION)Broaden results via related terms.
18Common Functionsstring / date / numeric / flow
CONCAT(a,' ',b) · CONCAT_WS('-',a,b)★Join strings, optionally with a separator.UPPER / LOWER / TRIM / SUBSTRING / REPLACECore string manipulation.NOW() / CURDATE() / CURTIME()★Current timestamp / date / time.DATE_ADD(d, INTERVAL 7 DAY) · DATEDIFF(a,b)Date arithmetic & differences.DATE_FORMAT(d, '%Y-%m-%d')Format a date to a string.ROUND(x,2) / CEIL / FLOOR / ABS / MODNumeric helpers.COALESCE(a,b,'default') · IFNULL(x,0)★First non-NULL / NULL substitution.CASE WHEN x>0 THEN 'pos' ELSE 'neg' END★Inline conditional logic.CAST(x AS DECIMAL(10,2)) · CONVERT(...)Explicit type conversion.UUID() · UUID_TO_BIN(u)Generate UUIDs; 8.0 adds compact binary storage.
19Update & DeleteDML · handle with care
UPDATE t SET status='done' WHERE id=7;★Change matching rows — always use WHERE.UPDATE a JOIN b ON a.id=b.a_id SET a.x=b.y;Multi-table update via join.DELETE FROM t WHERE id=7;★Remove matching rows, logged & rollback-able.UPDATE t SET x=1; DELETE FROM t;no WHERETouches every row in the table.TRUNCATE TABLE t;destroysInstant wipe; resets AUTO_INCREMENT; not row-logged.
20Keys & ConstraintsDDL · data integrity
id INT AUTO_INCREMENT PRIMARY KEY★Unique row identity; persists across restart in 8.0.email VARCHAR(120) UNIQUE★No two rows may share this value.name VARCHAR(50) NOT NULL DEFAULT ''★Nullability + fallback value.FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE★Referential integrity + auto-cleanup of children.CHECK (price >= 0)Row-level validation — enforced since 8.0.16.
21IndexesDDL · speed up reads
CREATE INDEX idx ON t(email);★Speed up lookups/filters on a column.CREATE UNIQUE INDEX idx ON t(sku);Index that also enforces uniqueness.CREATE INDEX idx ON t(last, first);Composite — leftmost-prefix rule applies.CREATE INDEX idx ON t(created DESC);Descending index — real reverse scan (8.0+).ALTER TABLE t ALTER INDEX idx INVISIBLE;★Invisible index — test dropping it safely (8.0+).CREATE INDEX idx ON t((UPPER(name)));Functional index on an expression (8.0.13+).
22Generated ColumnsDDL · computed columns
total DECIMAL(10,2) AS (qty*price) STORED★Value computed & persisted on write.full_name VARCHAR(120) AS (CONCAT(first,' ',last)) VIRTUALComputed on read — no storage cost.city VARCHAR(50) AS (addr->>'$.city') STORED★Promote a JSON field to a real, indexable column.CREATE INDEX idx ON t(total);Generated columns can be indexed like any other.
23PartitioningDDL · split one table physically
... PARTITION BY RANGE (YEAR(d)) (PARTITION p0 VALUES LESS THAN (2020), ...)★Split by value range — time-series, archival.... PARTITION BY LIST (region) (...)Split by an explicit set of values.... PARTITION BY HASH (id) PARTITIONS 4;Even spread across N partitions.ALTER TABLE t DROP PARTITION p0;Drop a whole partition instantly — fast bulk delete.
24TransactionsTCL · InnoDB only
START TRANSACTION;★Begin a block of atomic statements.COMMIT; ROLLBACK;★Make permanent / undo the whole block.SAVEPOINT sp1; ROLLBACK TO sp1;Partial rollback to a named point.SET autocommit = 0;Turn off per-statement auto-commit.SET TRANSACTION ISOLATION LEVEL READ COMMITTED;Default is REPEATABLE READ in InnoDB.
25Locking Reads 8.0+DQL · concurrency
SELECT ... FOR UPDATE;★Lock the read rows for writing — blocks other writers.SELECT ... FOR SHARE;Shared lock — others read, not write (8.0 syntax).SELECT ... FOR UPDATE SKIP LOCKED;★Skip already-locked rows — the queue-worker pattern.SELECT ... FOR UPDATE NOWAIT;Error immediately instead of waiting on a lock.
26Users & PrivilegesDCL · access control
CREATE USER 'u'@'localhost' IDENTIFIED BY 'pw';★8.0 defaults to caching_sha2_password auth.CREATE USER 'legacy'@'%' IDENTIFIED WITH mysql_native_password BY 'pw';Fallback for old clients that can't do sha2.GRANT ALL PRIVILEGES ON db.* TO 'u'@'localhost';★Full rights on every table in db.GRANT SELECT, INSERT ON db.t TO 'u'@'localhost';Grant only specific privileges.ALTER USER 'u'@'localhost' IDENTIFIED BY 'newpw';Change a password.FLUSH PRIVILEGES;Reload grant tables after manual edits.DROP USER 'u'@'localhost';destroysDelete the account entirely.
27Roles 8.0+DCL · grouped privileges
CREATE ROLE 'app_read', 'app_write';★A named bundle of privileges.GRANT SELECT ON app.* TO 'app_read';★Grant privileges to the role, not each user.GRANT 'app_read' TO 'alice'@'%';★Assign the role to a user.SET ROLE 'app_read';Activate a role for the current session.SET DEFAULT ROLE ALL TO 'alice'@'%';Auto-activate roles on every login.
28ViewsDDL · saved queries
CREATE VIEW active AS SELECT * FROM u WHERE active=1;★Name a query — query it like a table.CREATE OR REPLACE VIEW v AS ...;Redefine an existing view.... WITH CHECK OPTIONReject writes through the view that violate its WHERE.DROP VIEW v;removesDelete the view (underlying data untouched).
29Procedures & Triggersserver-side logic
CREATE PROCEDURE p(IN x INT) BEGIN ... END;★Reusable stored routine; CALL p(1) to run.CREATE FUNCTION f(x INT) RETURNS INT ...Returns a scalar, usable inside SELECT.CREATE TRIGGER trg BEFORE INSERT ON t FOR EACH ROW ...★Auto-run logic on write; NEW./OLD. row refs.CREATE EVENT e ON SCHEDULE EVERY 1 DAY DO ...Scheduled job via the event scheduler.
30Backup, Restore & Importshell · not run inside mysql>
mysqldump -u u -p db > backup.sql★Export a full database to a .sql file.mysqldump -u u -p --all-databases > all.sqlDump every database on the server.mysql -u u -p db < backup.sql★Restore a dump into an existing database.mysqlsh -- util dumpInstance / loadDumpParallel logical dump utilities (MySQL Shell 8.0).LOAD DATA INFILE 'f.csv' INTO TABLE t FIELDS TERMINATED BY ',';Bulk-load a CSV — far faster than INSERTs.
31Optimize & Inspectperformance tuning
EXPLAIN ANALYZE SELECT ...;★Actual timing & row counts vs. estimates (8.0.18+).SELECT /*+ INDEX(t idx) */ ...Optimizer hint to force an index.ANALYZE TABLE t;Refresh index statistics / histograms.OPTIMIZE TABLE t;Rebuild & defragment storage.
★Operators & Wildcardsused throughout
= <=> != <> < > <= >=Standard + NULL-safe (<=>) equality.% (LIKE) · _ (LIKE) · .* + ? (REGEXP)LIKE wildcards vs. regex metacharacters.AND · OR · NOT · XORBoolean logic operators.IN · NOT IN · EXISTS · ANY / ALLSet-membership & subquery tests.IS NULL · IS NOT NULLThe only correct way to test for NULL.-> · ->>JSON extract / extract-unquote operators.