Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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