MySQL Cheat Sheet

Here's a MySQL cheat sheet to help you quickly reference common commands and functions:

---

MySQL Basics

• Connect to MySQL:

    mysql -u username -p
  

• Select a Database:

    USE database_name;
  

• List Databases:

    SHOW DATABASES;
  

• List Tables:

    SHOW TABLES;
  

---

Database Commands

• Create Database:

    CREATE DATABASE database_name;
  

• Drop Database:

    DROP DATABASE database_name;
  

---

Table Commands

• Create Table:

    CREATE TABLE table_name (
        column1_name column1_type constraints,
        column2_name column2_type constraints,
        ...
    );
  

• Drop Table:

    DROP TABLE table_name;
  

• Describe Table (Schema):

    DESCRIBE table_name;
  

• Alter Table:

  • Add a Column:

      ALTER TABLE table_name ADD COLUMN column_name column_type;
    

  • Drop a Column:

      ALTER TABLE table_name DROP COLUMN column_name;
    

  • Modify a Column:

      ALTER TABLE table_name MODIFY COLUMN column_name new_column_type;
    

---

CRUD Operations

Insert Data

• Insert a Single Row:

    INSERT INTO table_name (column1, column2) VALUES (value1, value2);
  

• Insert Multiple Rows:

    INSERT INTO table_name (column1, column2) VALUES 
        (value1_1, value2_1),
        (value1_2, value2_2),
        ...
    ;
  

Read Data (SELECT)

• Basic Select:

    SELECT column1, column2 FROM table_name;
  

• Select All Columns:

    SELECT * FROM table_name;
  

• Where Clause:

    SELECT * FROM table_name WHERE condition;
  

• Order By:

    SELECT * FROM table_name ORDER BY column_name ASC|DESC;
  

• Limit Results:

    SELECT * FROM table_name LIMIT number_of_rows;
  

Update Data

• Update Rows:

    UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  

Delete Data

• Delete Rows:

    DELETE FROM table_name WHERE condition;
  

---

Joins

• Inner Join:

    SELECT columns FROM table1
    INNER JOIN table2 ON table1.common_column = table2.common_column;
  

• Left Join:

    SELECT columns FROM table1
    LEFT JOIN table2 ON table1.common_column = table2.common_column;
  

• Right Join:

    SELECT columns FROM table1
    RIGHT JOIN table2 ON table1.common_column = table2.common_column;
  

• Cross Join:

    SELECT columns FROM table1
    CROSS JOIN table2;
  

---

Aggregation Functions

• Count:

    SELECT COUNT(column_name) FROM table_name;
  

• Sum:

    SELECT SUM(column_name) FROM table_name;
  

• Average:

    SELECT AVG(column_name) FROM table_name;
  

• Max:

    SELECT MAX(column_name) FROM table_name;
  

• Min:

    SELECT MIN(column_name) FROM table_name;
  

• Group By:

    SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
  

---

Indexes

• Create Index:

    CREATE INDEX index_name ON table_name (column_name);
  

• Drop Index:

    DROP INDEX index_name ON table_name;
  

---

Constraints

• Primary Key:

    PRIMARY KEY (column_name)
  

• Foreign Key:

    FOREIGN KEY (column_name) REFERENCES other_table(other_column)
  

• Unique:

    UNIQUE (column_name)
  

• Not Null:

    column_name column_type NOT NULL
  

---

Transactions

• Begin Transaction:

    START TRANSACTION;
  

• Commit Transaction:

    COMMIT;
  

• Rollback Transaction:

    ROLLBACK;
  

---

User Management

• Create User:

    CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  

• Grant Privileges:

    GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
  

• Revoke Privileges:

    REVOKE ALL PRIVILEGES ON database_name.* FROM 'username'@'host';
  

• Show Grants:

    SHOW GRANTS FOR 'username'@'host';
  

• Delete User:

    DROP USER 'username'@'host';
  

---