A quick reference for MySQL commands and syntax. Covers data types, CRUD operations, JOINs, indexes, user management, backup/restore, common functions, and schema changes. MySQL 8.0 reached end of life on 30 April 2026 — 8.4 LTS (supported to 2032) is the upgrade target, with 9.7 (April 2026) opening the next LTS series.
Data Types
| Type | Description |
|---|---|
| INT / BIGINT | Integer (4 bytes / 8 bytes) |
| DECIMAL(10,2) | Exact numeric with precision |
| FLOAT / DOUBLE | Approximate floating-point |
| VARCHAR(255) | Variable-length string (max 255) |
| TEXT / LONGTEXT | Large text (64KB / 4GB max) |
| BOOLEAN | Alias for TINYINT(1) |
| DATE / DATETIME | Date / date and time |
| TIMESTAMP | UTC timestamp, auto-converts timezone — range ends 2038-01-19 |
| JSON | Native JSON data type |
| ENUM("a","b","c") | Predefined set of string values |
CRUD Operations
| Command | Description |
|---|---|
| SELECT * FROM users WHERE id = 1 | Read rows matching condition |
| SELECT name, email FROM users ORDER BY name | Select specific columns, sorted |
| SELECT COUNT(*) FROM users | Count rows |
| INSERT INTO users (name, email) VALUES ("Jo", "jo@x.com") | Insert a row |
| INSERT INTO users (name) VALUES ("A"), ("B") | Insert multiple rows |
| UPDATE users SET name = "Jo" WHERE id = 1 | Update matching rows |
| DELETE FROM users WHERE id = 1 | Delete matching rows |
| SELECT DISTINCT status FROM orders | Select unique values |
JOINs
| Syntax | Description |
|---|---|
| INNER JOIN t2 ON t1.id = t2.fk | Rows matching in both tables |
| LEFT JOIN t2 ON t1.id = t2.fk | All rows from left, matched from right |
| RIGHT JOIN t2 ON t1.id = t2.fk | All rows from right, matched from left |
| CROSS JOIN t2 | Cartesian product of both tables |
| SELECT ... FROM t1 JOIN t2 USING (id) | Join using same-named column |
| SELECT * FROM t1 NATURAL JOIN t2 | Join on all same-named columns |
| LEFT JOIN t2 ON ... WHERE t2.id IS NULL | Rows in t1 with no match in t2 |
Indexes
| Command | Description |
|---|---|
| CREATE INDEX idx_name ON users (name) | Create an index |
| CREATE UNIQUE INDEX idx_email ON users (email) | Create a unique index |
| CREATE INDEX idx_comp ON t (a, b) | Create a composite index |
| DROP INDEX idx_name ON users | Drop an index |
| SHOW INDEX FROM users | Show indexes on a table |
| EXPLAIN SELECT * FROM users WHERE name = "Jo" | Show query execution plan |
| CREATE FULLTEXT INDEX idx_ft ON posts (body) | Create full-text search index |
User Management
| Command | Description |
|---|---|
| CREATE USER 'app'@'%' IDENTIFIED BY 'pass' | Create user (any host) |
| GRANT ALL ON mydb.* TO 'app'@'%' | Grant all privileges on database |
| GRANT SELECT, INSERT ON mydb.users TO 'app'@'%' | Grant specific privileges |
| REVOKE INSERT ON mydb.users FROM 'app'@'%' | Revoke a privilege |
| SHOW GRANTS FOR CURRENT_USER | Show current user privileges |
| DROP USER 'app'@'%' | Delete a user |
| ALTER USER 'app'@'%' IDENTIFIED BY 'newpass' | Change user password |
| FLUSH PRIVILEGES | Reload privilege tables |
Backup & Restore
| Command | Description |
|---|---|
| mysqldump -u root -p mydb > backup.sql | Dump entire database |
| mysqldump -u root -p mydb users > users.sql | Dump a single table |
| mysqldump --all-databases > all.sql | Dump all databases |
| mysqldump --single-transaction mydb > backup.sql | Consistent dump without locking tables (InnoDB) |
| mysql -u root -p mydb < backup.sql | Restore from dump file |
| mysqldump --no-data mydb > schema.sql | Dump schema only (no data) |
| mysqldump --routines --triggers mydb > full.sql | Include routines and triggers |
Common Functions
| Function | Description |
|---|---|
| NOW() | Current date and time |
| CURDATE() / CURTIME() | Current date / current time |
| CONCAT(a, b) | Concatenate strings |
| IFNULL(expr, default) | Return default if expr is NULL |
| COALESCE(a, b, c) | Return first non-NULL argument |
| GROUP_CONCAT(col SEPARATOR ",") | Aggregate values into a string |
| DATE_FORMAT(dt, "%Y-%m-%d") | Format a date value |
| SUBSTRING(str, pos, len) | Extract part of a string |
ALTER TABLE
| Command | Description |
|---|---|
| ALTER TABLE t ADD COLUMN age INT | Add a column |
| ALTER TABLE t DROP COLUMN age | Remove a column |
| ALTER TABLE t MODIFY COLUMN name VARCHAR(100) | Change column type |
| ALTER TABLE t RENAME COLUMN old TO new | Rename a column |
| ALTER TABLE t ADD PRIMARY KEY (id) | Add primary key |
| ALTER TABLE t ADD FOREIGN KEY (uid) REFERENCES users(id) | Add foreign key |
| ALTER TABLE t RENAME TO new_table | Rename a table |
| ALTER TABLE t ENGINE = InnoDB | Change storage engine |
Common gotchas
- MySQL 8.0 hit end of life on 30 April 2026 — no more security patches. AWS RDS auto-enrolls remaining 8.0 instances into paid Extended Support from August 2026. Upgrade to 8.4 LTS; skipping an LTS series (8.4 → 10.x) is not a supported path.
- In 8.4,
mysql_native_passwordis disabled by default — old clients and connectors fail at auth after the upgrade. Migrate accounts tocaching_sha2_passwordfirst. utf8is an alias for the 3-byteutf8mb3— emoji and many CJK characters fail to insert. Always useutf8mb4(the default since 8.0).TIMESTAMPconverts to the session time zone on read and dies at 2038;DATETIMEstores what you wrote, no conversion. Pick one convention per schema and document it.- Plain
mysqldumptakes table locks for the whole dump on busy databases. Use--single-transactionfor InnoDB — and rememberTRUNCATEand DDL commit implicitly, so they cannot be rolled back.
Dialect-agnostic syntax lives in the SQL cheat sheet; comparing engines? See the PostgreSQL cheat sheet. Format long queries with the SQL formatter.