Essential MySQL Commands Cheat Sheet: Manage Databases, Tables, Users & More

Database Management

Commands to view, create, delete, and switch databases.

SHOW DATABASES;

CREATE DATABASE shop_db;

DROP DATABASE test_db;

USE shop_db;

SHOW TABLES;

SHOW CREATE DATABASE shop_db;

ALTER DATABASE shop_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

SELECT DATABASE();

Table Structure Management

Commands to create, drop, describe tables and modify schema.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DROP TABLE users;

DESC users;

SHOW CREATE TABLE users;

ALTER TABLE users ADD email VARCHAR(100);

ALTER TABLE users DROP COLUMN age;

RENAME TABLE users TO user_info;

Data Operations

Insert, query, update, delete and truncate data.

INSERT INTO users (name, age, email) VALUES ('Alice', 25, '[email protected]');

SELECT * FROM users;

UPDATE users SET age = 30 WHERE name = 'Alice';

DELETE FROM users WHERE id = 3;

TRUNCATE TABLE users;

SELECT * FROM users LIMIT 5;

SELECT * FROM users ORDER BY age DESC;

User and Permission Management

Create users, grant privileges, view grants and drop users.

CREATE USER 'dev_user'@'localhost' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON shop_db.* TO 'dev_user'@'localhost';

SHOW GRANTS FOR 'dev_user'@'localhost';

DROP USER 'dev_user'@'localhost';

Performance Monitoring

Inspect running queries, thread status and configuration variables.

SHOW PROCESSLIST;

SHOW STATUS LIKE 'Threads%';

SHOW VARIABLES LIKE 'max_connections';

Backup and Restore

Export and import databases using mysqldump and mysql.

mysqldump -u root -p shop_db > /backup/shop_db.sql

mysql -u root -p shop_db < /backup/shop_db.sql

SHOW BINARY LOGS;