MySQL Cheatsheet
#
MySQL is a widely used relational database management system known for its speed, reliability, and ease of use. It is commonly used for managing databases in various applications.
General Commands
#
| Command/Option | Example | Description |
|---|
mysql -u <user> -p | mysql -u root -p | Connect to MySQL server with username and password |
mysql -u <user> -p <database> | mysql -u root -p mydb | Connect to a specific database |
exit | exit | Exit the MySQL command-line client |
Database Management
#
| Command/Option | Example | Description |
|---|
CREATE DATABASE <db_name>; | CREATE DATABASE mydb; | Create a new database |
DROP DATABASE <db_name>; | DROP DATABASE mydb; | Delete a database |
SHOW DATABASES; | SHOW DATABASES; | List all databases |
Table Management
#
| Command/Option | Example | Description |
|---|
USE <db_name>; | USE mydb; | Switch to a specific database |
CREATE TABLE <table_name> (column_definitions); | CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100)); | Create a new table |
DROP TABLE <table_name>; | DROP TABLE users; | Delete a table |
SHOW TABLES; | SHOW TABLES; | List all tables in the current database |
Data Manipulation
#
| Command/Option | Example | Description |
|---|
INSERT INTO <table> (columns) VALUES (values); | INSERT INTO users (name) VALUES ('John Doe'); | Insert new data into a table |
SELECT * FROM <table>; | SELECT * FROM users; | Retrieve all data from a table |
UPDATE <table> SET column = value WHERE condition; | UPDATE users SET name = 'Jane Doe' WHERE id = 1; | Update existing data in a table |
DELETE FROM <table> WHERE condition; | DELETE FROM users WHERE id = 1; | Delete data from a table |
Query Filtering and Sorting
#
| Command/Option | Example | Description |
|---|
SELECT column1, column2 FROM <table> WHERE condition; | SELECT name FROM users WHERE id = 1; | Retrieve specific data based on a condition |
| `SELECT * FROM ORDER BY column [ASC | DESC];` | SELECT * FROM users ORDER BY name ASC; |
Indexing
#
| Command/Option | Example | Description |
|---|
CREATE INDEX <index_name> ON <table> (column); | CREATE INDEX idx_name ON users (name); | Create an index on a column |
DROP INDEX <index_name> ON <table>; | DROP INDEX idx_name ON users; | Delete an index |
User Management
#
| Command/Option | Example | Description |
|---|
CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>'; | CREATE USER 'user'@'localhost' IDENTIFIED BY 'password'; | Create a new MySQL user |
DROP USER '<username>'@'<host>'; | DROP USER 'user'@'localhost'; | Delete a MySQL user |
GRANT ALL PRIVILEGES ON <db_name>.* TO '<username>'@'<host>'; | GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost'; | Grant privileges to a user |
REVOKE ALL PRIVILEGES ON <db_name>.* FROM '<username>'@'<host>'; | REVOKE ALL PRIVILEGES ON mydb.* FROM 'user'@'localhost'; | Revoke privileges from a user |
Backup and Restore
#
| Command/Option | Example | Description |
|---|
mysqldump -u <user> -p <db_name> > <file.sql> | mysqldump -u root -p mydb > mydb_backup.sql | Backup a database to a SQL file |
mysql -u <user> -p <db_name> < <file.sql> | mysql -u root -p mydb < mydb_backup.sql | Restore a database from a SQL file |
Server Status and Configuration
#
| Command/Option | Example | Description |
|---|
SHOW STATUS; | SHOW STATUS; | Display server status variables |
SHOW VARIABLES; | SHOW VARIABLES; | Display server system variables |
SHOW PROCESSLIST; | SHOW PROCESSLIST; | Show currently running threads |
This cheatsheet covers the most commonly used MySQL commands and options, helping you manage databases, tables, data, indexing, user permissions, backups, and server status efficiently.