MySQL Cheatsheet: Comprehensive Guide with Examples
Introduction
MySQL is a popular open-source relational database management system used by developers and businesses worldwide. It supports a wide range of operations that allow you to create, manage, and manipulate data efficiently. This comprehensive cheatsheet covers essential MySQL commands and concepts, complete with examples presented in HTML tables for easy reference.
1. Database Operations
1.1 Creating and Dropping Databases
| Command | Description | Example |
|---|
CREATE DATABASE <database_name>; | Creates a new database | CREATE DATABASE mydatabase; |
DROP DATABASE <database_name>; | Drops (deletes) a database | DROP DATABASE mydatabase; |
SHOW DATABASES; | Lists all databases on the MySQL server | SHOW DATABASES; |
USE <database_name>; | Selects a database to use | USE mydatabase; |
2. Table Operations
2.1 Creating, Dropping, and Altering Tables
| Command | Description | Example |
|---|
CREATE TABLE <table_name> (<column_definitions>); | Creates a new table | CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100)); |
DROP TABLE <table_name>; | Drops (deletes) a table | DROP TABLE users; |
ALTER TABLE <table_name> ADD <column_definition>; | Adds a new column to an existing table | ALTER TABLE users ADD email VARCHAR(100); |
ALTER TABLE <table_name> DROP COLUMN <column_name>; | Drops a column from an existing table | ALTER TABLE users DROP COLUMN email; |
3. Inserting, Updating, and Deleting Data
3.1 Insert Data
| Command | Description | Example |
|---|
INSERT INTO <table_name> (<columns>) VALUES (<values>); | Inserts new data into a table | INSERT INTO users (id, name) VALUES (1, 'John Doe'); |
3.2 Update Data
| Command | Description | Example |
|---|
UPDATE <table_name> SET <column_name>=<value> WHERE <condition>; | Updates existing data in a table | UPDATE users SET name='Jane Doe' WHERE id=1; |
3.3 Delete Data
| Command | Description | Example |
|---|
DELETE FROM <table_name> WHERE <condition>; | Deletes data from a table | DELETE FROM users WHERE id=1; |
DELETE FROM <table_name>; | Deletes all data from a table | DELETE FROM users; |
4. Querying Data
4.1 Basic SELECT Queries
| Command | Description | Example |
|---|
SELECT * FROM <table_name>; | Selects all columns from a table | SELECT * FROM users; |
SELECT <columns> FROM <table_name>; | Selects specific columns from a table | SELECT name FROM users; |
SELECT * FROM <table_name> WHERE <condition>; | Selects rows based on a condition | SELECT * FROM users WHERE id=1; |
4.2 Using Aggregate Functions
| Function | Description | Example |
|---|
COUNT() | Returns the number of rows that match the query | SELECT COUNT(*) FROM users; |
SUM() | Returns the sum of a numeric column | SELECT SUM(age) FROM users; |
AVG() | Returns the average value of a numeric column | SELECT AVG(age) FROM users; |
MAX() | Returns the maximum value in a column | SELECT MAX(age) FROM users; |
MIN() | Returns the minimum value in a column | SELECT MIN(age) FROM users; |
5. Joins
5.1 Inner Join
| Command | Description | Example |
|---|
SELECT * FROM <table1> INNER JOIN <table2> ON <table1.column>=<table2.column>; | Returns records that have matching values in both tables | SELECT users.name, orders.amount FROM users INNER JOIN orders ON users.id=orders.user_id; |
5.2 Left Join
| Command | Description | Example |
|---|
SELECT * FROM <table1> LEFT JOIN <table2> ON <table1.column>=<table2.column>; | Returns all records from the left table and matched records from the right table | SELECT users.name, orders.amount FROM users LEFT JOIN orders ON users.id=orders.user_id; |
5.3 Right Join
| Command | Description | Example |
|---|
SELECT * FROM <table1> RIGHT JOIN <table2> ON <table1.column>=<table2.column>; | Returns all records from the right table and matched records from the left table | SELECT users.name, orders.amount FROM users RIGHT JOIN orders ON users.id=orders.user_id; |
5.4 Full Join
| Command | Description | Example |
|---|
SELECT * FROM <table1> FULL OUTER JOIN <table2> ON <table1.column>=<table2.column>; | Returns all records when there is a match in either left or right table | SELECT users.name, orders.amount FROM users FULL OUTER JOIN orders ON users.id=orders.user_id; |
6. Indexes
6.1 Creating and Dropping Indexes
| Command | Description | Example |
|---|
CREATE INDEX <index_name> ON <table_name>(<column_name>); | Creates an index on a table column | CREATE INDEX idx_user_name ON users(name); |
DROP INDEX <index_name> ON <table_name>; | Drops an index from a table | DROP INDEX idx_user_name ON users; |
7. Transactions
7.1 Starting, Committing, and Rolling Back Transactions
| Command | Description | Example |
|---|
START TRANSACTION; | Begins a new transaction | START TRANSACTION; |
COMMIT; | Saves all changes made in the transaction | COMMIT; |
ROLLBACK; | Reverts all changes made in the transaction | ROLLBACK; |
8. User Management
8.1 Creating and Managing Users
| Command | Description | Example |
|---|
CREATE USER '<username>'@'<host>' IDENTIFIED BY '<password>'; | Creates a new MySQL user | CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123'; |
GRANT ALL PRIVILEGES ON <database_name>.* TO '<username>'@'<host>'; | Grants all privileges to a user on a specific database | GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost'; |
REVOKE <privilege> ON <database_name>.* FROM '<username>'@'<host>'; | Revokes a specific privilege from a user | REVOKE SELECT ON mydatabase.* FROM 'newuser'@'localhost'; |
DROP USER '<username>'@'<host>'; | Deletes a MySQL user | DROP USER 'newuser'@'localhost'; |
9. Backup and Restore
9.1 Backup Database
| Command | Description | Example |
|---|
mysqldump -u <username> -p <database_name> > <backup_file>.sql | Backs up a database to a .sql file | mysqldump -u root -p mydatabase > backup.sql |
9.2 Restore Database
| Command | Description | Example |
|---|
mysql -u <username> -p <database_name> < <backup_file>.sql | Restores a database from a .sql file | mysql -u root -p mydatabase < backup.sql |
Conclusion
This MySQL cheatsheet provides a comprehensive overview of the most commonly used MySQL commands, complete with examples to help you quickly find the information you need. Whether you're creating and managing databases, writing queries, or handling transactions, this guide serves as a quick reference to help you work more efficiently with MySQL.
Comments
Please log in to leave a comment.