Mysql cheatsheet

Connect to MySQL

• To connect to a MySQL server, use the following command:

  mysql -u username -p
  

• Replace username with your username. You will be prompted to enter your password.

Basic MySQL Commands

• SHOW DATABASES; – Displays a list of all databases.

• USE database_name; – Selects the database you want to work with.

• SHOW TABLES; – Displays a list of all tables in the current database.

• DESCRIBE table_name; – Displays the structure of a table.

Creating and Dropping Databases

• CREATE DATABASE database_name; – Creates a new database with the specified name.

• DROP DATABASE database_name; – Deletes the database with the specified name.

Creating and Dropping Tables

• CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype); – Creates a new table with the specified columns and datatypes.

• DROP TABLE table_name; – Deletes the table with the specified name.

Inserting Data into a Table

• INSERT INTO table_name (column1, column2, column3) VALUES (value1, value2, value3); – Inserts a new row into the specified table with the specified values.

Retrieving Data from a Table

• SELECT * FROM table_name; – Retrieves all rows and columns from the specified table.

• SELECT column1, column2 FROM table_name; – Retrieves specific columns from the specified table.

• SELECT * FROM table_name WHERE column1 = 'value'; – Retrieves all rows from the specified table where the specified column has the specified value.

Updating Data in a Table

• UPDATE table_name SET column1 = 'value1', column2 = 'value2' WHERE column3 = 'value3'; – Updates the specified columns in the specified table where the specified column has the specified value.

Deleting Data from a Table

• DELETE FROM table_name WHERE column1 = 'value'; – Deletes all rows from the specified table where the specified column has the specified value.

Aggregation Functions

• SELECT COUNT(column_name) FROM table_name; – Returns the number of rows in the specified column.

• SELECT SUM(column_name) FROM table_name; – Returns the sum of all values in the specified column.

• SELECT AVG(column_name) FROM table_name; – Returns the average of all values in the specified column.

• SELECT MAX(column_name) FROM table_name; – Returns the maximum value in the specified column.

• SELECT MIN(column_name) FROM table_name; – Returns the minimum value in the specified column.

Grouping Data

• SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; – Groups rows with the same value in the specified column and returns the count of each group.

• SELECT column1, AVG(column2) FROM table_name GROUP BY column1; – Groups rows with the same value in column1 and returns the average of column2 for each group.

HAVING Clause

• The HAVING clause is used to filter the groups generated by the GROUP BY clause. It is similar to the WHERE clause but operates on the groups instead of individual rows.

MYSQL Copy

                  
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s)
HAVING condition 
      
              

Joins

• INNER JOIN



MYSQL Copy

                  
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
      
              




• LEFT JOIN



MYSQL Copy

          
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

      




• RIGHT JOIN



MYSQL Copy

          
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

      




• FULL OUTER JOIN



MYSQL Copy

          
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

      




• SELF JOIN



MYSQL Copy

          
SELECT column_name(s)
FROM table1 t1, table1 t2
WHERE t1.column_name = t2.column_name;