Home/MySQL/Cheat Sheet

MySQL Command Reference

MySQL Common command Quick Reference,

mysql -u root -p MySQL
mysql -h host -P 3306 -u user -p dbname
SHOW DATABASES;
CREATE DATABASE db CHARACTER SET utf8mb4;
USE database_name;
DROP DATABASE IF EXISTS db;
status;

SHOW TABLES;
DESC table_name;
SHOW CREATE TABLE table_name;
CREATE TABLE t (id BIGINT PRIMARY KEY AUTO_INCREMENT, ...);
ALTER TABLE t ADD COLUMN col VARCHAR(50);
ALTER TABLE t MODIFY COLUMN col VARCHAR(100);
ALTER TABLE t DROP COLUMN col;
DROP TABLE IF EXISTS t;
TRUNCATE TABLE t; (ID)

CRUD

INSERT INTO t (col1, col2) VALUES (v1, v2);
INSERT INTO t (col) VALUES (v1), (v2), (v3);
SELECT col1, col2 FROM t WHERE condition;
UPDATE t SET col = val WHERE condition; New
DELETE FROM t WHERE condition;
INSERT INTO t ... ON DUPLICATE KEY UPDATE ...; New
REPLACE INTO t (col1, col2) VALUES (v1, v2);

Tips

SELECT * FROM t ORDER BY col DESC LIMIT 10; +
SELECT col, COUNT(*) FROM t GROUP BY col;
SELECT * FROM t1 JOIN t2 ON t1.id = t2.fk_id;
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.fk_id;
SELECT DISTINCT col FROM t;
SELECT * FROM t WHERE col IN (SELECT ...);
SELECT * FROM t WHERE col LIKE '%keyword%';
SELECT IFNULL(col, '') FROM t;

CREATE USER 'user'@'%' IDENTIFIED BY 'pass';
GRANT SELECT, INSERT ON db.* TO 'user'@'%';
REVOKE DELETE ON db.* FROM 'user'@'%';
SHOW GRANTS FOR 'user'@'%';
ALTER USER 'user'@'%' IDENTIFIED BY 'newpass';
DROP USER 'user'@'%';
FLUSH PRIVILEGES; New

mysqldump -u root -p --single-transaction db > backup.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p db table1 table2 > tables.sql
mysqldump --no-data db > schema.sql
mysql -u root -p db < backup.sql
source /path/to/backup.sql MySQL

EXPLAIN SELECT ...;
SHOW INDEX FROM table_name;
CREATE INDEX idx_col ON t(col);
SHOW FULL PROCESSLIST; and
KILL process_id;
ANALYZE TABLE t; New
SET GLOBAL slow_query_log = ON;
SHOW STATUS LIKE 'Threads%';