MySQL Cheatsheet

Setup

• mysql --version
• mysql -u root -p

Connect

• mysql -h 127.0.0.1 -P 3306 -u user -p
• mysql -u user -p db_name

Databases

• SHOW DATABASES;
• CREATE DATABASE app_db;
• USE app_db;
• DROP DATABASE app_db;

Tables

• SHOW TABLES;
• DESCRIBE users;
• CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(120));
• DROP TABLE users;

CRUD

• INSERT INTO users (name) VALUES ('Ada');
• SELECT * FROM users;
• SELECT id, name FROM users WHERE id = 1;
• UPDATE users SET name = 'Ada Lovelace' WHERE id = 1;
• DELETE FROM users WHERE id = 1;

Indexes

• CREATE INDEX idx_users_name ON users (name);
• SHOW INDEX FROM users;
• DROP INDEX idx_users_name ON users;

Users & Privileges

• CREATE USER 'app'@'%' IDENTIFIED BY 'secret';
• GRANT ALL PRIVILEGES ON app_db.* TO 'app'@'%';
• FLUSH PRIVILEGES;
• SHOW GRANTS FOR 'app'@'%';