MySQL Cheat Sheet
Client and admin
// connect, import, export
mysql -u root -p
mysql -u user -p dbname < file.sql
mysqldump -u user -p dbname > backup.sql
Databases and tables
CREATE DATABASE app CHARACTER SET utf8mb4; USE app; SHOW TABLES; DESCRIBE users;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(120) UNIQUE NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);
ALTER TABLE users ADD COLUMN name VARCHAR(60); ALTER TABLE users MODIFY name VARCHAR(80) NOT NULL;
DROP TABLE IF EXISTS users; TRUNCATE TABLE logs;
CRUD
INSERT INTO users (email, name) VALUES ('a@b.com', 'A'), ('c@d.com', 'C');
INSERT INTO t (k, v) VALUES (1, 'x') ON DUPLICATE KEY UPDATE v = VALUES(v);
SELECT * FROM users WHERE name LIKE 'A%' ORDER BY id DESC LIMIT 10 OFFSET 0;
UPDATE users SET name = 'B' WHERE id = 1; DELETE FROM users WHERE id = 1;
Queries
SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id;
SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 500;
SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM products;
WITH t AS (SELECT ...) SELECT * FROM t; SELECT RANK() OVER (ORDER BY price DESC) FROM products;
Functions
CONCAT(a, b); LPAD(n, 4, '0'); UPPER(s); CHAR_LENGTH(s); IFNULL(x, 0); IF(c, a, b);
NOW(); CURDATE(); DATE_FORMAT(d, '%d %b %Y'); DATE_ADD(d, INTERVAL 7 DAY); DATEDIFF(a, b);
j->>'$.key'; JSON_EXTRACT(j, '$.a'); JSON_ARRAYAGG(x);
Indexes, transactions, users
CREATE INDEX idx_name ON t (a, b); SHOW INDEX FROM t; EXPLAIN SELECT ...;
START TRANSACTION; UPDATE ...; COMMIT; ROLLBACK;
SELECT * FROM seats WHERE id = 5 FOR UPDATE;
CREATE USER 'app'@'%' IDENTIFIED BY 'pass'; GRANT SELECT ON db.* TO 'app'@'%';
Full MySQL documentation