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 ; Creates a new database CREATE DATABASE mydatabase;
DROP DATABASE ; Drops (deletes) a database DROP DATABASE mydatabase;
SHOW DATABASES; Lists all databases on the MySQL server SHOW DATABASES;
USE ; Selects a database to use USE mydatabase;

2. Table Operations

2.1 Creating, Dropping, and Altering Tables

Command Description Example
CREATE TABLE (); Creates a new table CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));
DROP TABLE ; Drops (deletes) a table DROP TABLE users;
ALTER TABLE ADD ; Adds a new column to an existing table ALTER TABLE users ADD email VARCHAR(100);
ALTER TABLE DROP COLUMN ; 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 () VALUES (); Inserts new data into a table INSERT INTO users (id, name) VALUES (1, 'John Doe');

3.2 Update Data

Command Description Example
UPDATE SET = WHERE ; Updates existing data in a table UPDATE users SET name='Jane Doe' WHERE id=1;

3.3 Delete Data

Command Description Example
DELETE FROM WHERE ; Deletes data from a table DELETE FROM users WHERE id=1;
DELETE FROM ; Deletes all data from a table DELETE FROM users;

4. Querying Data

4.1 Basic SELECT Queries

Command Description Example
SELECT * FROM ; Selects all columns from a table SELECT * FROM users;
SELECT FROM ; Selects specific columns from a table SELECT name FROM users;
SELECT * FROM WHERE ; 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 INNER JOIN ON =; 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 LEFT JOIN ON =; 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 RIGHT JOIN ON =; 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 FULL OUTER JOIN ON =; 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 ON (); Creates an index on a table column CREATE INDEX idx_user_name ON users(name);
DROP INDEX ON ; 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 ''@'' IDENTIFIED BY ''; Creates a new MySQL user CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON .* TO ''@''; Grants all privileges to a user on a specific database GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
REVOKE ON .* FROM ''@''; Revokes a specific privilege from a user REVOKE SELECT ON mydatabase.* FROM 'newuser'@'localhost';
DROP USER ''@''; Deletes a MySQL user DROP USER 'newuser'@'localhost';

9. Backup and Restore

9.1 Backup Database

Command Description Example
mysqldump -u -p > .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 -p < .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.