MySQL Common Commands Cheat Sheet

MySQL is the world’s most popular open-source relational database management system. This cheat sheet organizes the most commonly used SQL commands in daily database development and maintenance.

Connectivity

Command	Description	Example
`mysql -u root -p`	Connect to database as root	`mysql -u root -p`
`exit` / `quit`	Exit MySQL command line	`exit`
`show databases;`	List all databases	`show databases;`
`use <db_name>;`	Switch to specified database	`use my_app;`
`status;`	View current connection status	`status;`

Database & Table Operations (DDL)

Command	Description	Example
`create database <name>;`	Create new database	`create database test_db;`
`drop database <name>;`	Delete database	`drop database test_db;`
`show tables;`	List all tables in current DB	`show tables;`
`create table <table> (...);`	Create a new table	`create table users (id int, name varchar(20));`
`describe <table>;`	View table structure	`describe users;`
`alter table <table> add <col>;`	Add a new column	`alter table users add email varchar(50);`
`drop table <table>;`	Delete a table	`drop table users;`

CRUD Operations (DML)

// Insert data

insert into users (name, age) values ('Alice', 25), ('Bob', 30);

// Query data

select * from users where age > 20 order by id desc limit 10;
select count(*) from users;

// Update data

update users set age = 26 where name = 'Alice';

// Delete data

delete from users where id = 10;
truncate table users; -- Clear table content and reset auto-increment ID

Join Queries (JOIN)

Type	Description
`INNER JOIN`	Returns rows with matching values in both tables
`LEFT JOIN`	Returns all rows from left table and matched rows from right
`RIGHT JOIN`	Returns all rows from right table and matched rows from left
`UNION`	Combine results of two queries (unique)

// Link table query

select u.name, o.id from users u 
inner join orders o on u.id = o.user_id;

Index Management

Command	Description
`create index <idx_name> on <table>(<col>);`	Create a normal index
`create unique index ...`	Create a unique index
`show index from <table>;`	Show indexes in table
`drop index <idx_name> on <table>;`	Drop an index

User & Permissions (DCL)

Command	Description
`create user 'user'@'host' identified by 'pass';`	Create a new user
`grant all privileges on db.* to 'user'@'host';`	Grant privileges
`revoke all on db.* from 'user'@'host';`	Revoke privileges
`drop user 'user'@'host';`	Delete a user
`flush privileges;`	Reload privileges to take effect