Database Commands

1. Create a database

   CREATE DATABASE database_name;

2. Drop a database

   DROP DATABASE database_name;

3. Use a database

   USE database_name;

Table Commands

1. Create a table

   CREATE TABLE table_name (
       column1 datatype,
       column2 datatype,
       column3 datatype,
       ...
   );

2. Add another column to an existing table

   ALTER TABLE table_name
   ADD column_name datatype;

3. Drop a table

    DROP TABLE table_name;

4. Auto ID

   The primary key is the primary identifier of a row in a table. It is unique for each row and cannot be NULL. The auto-increment feature automatically generates a unique value for the primary key when a new row is inserted into the table.

   CREATE TABLE table_name (
        id INT NOT NULL AUTO_INCREMENT,
        column1 datatype,
        column2 datatype,
        PRIMARY KEY (id)
        ...
   );

5. Reference another table

   CREATE TABLE table_name (
        id INT AUTO_INCREMENT PRIMARY KEY,
        column1 datatype,
        column2 datatype,
        other_tables_column INT NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (other_tables_column) REFERENCES other_table(other_tables_column)
   );

CRUD Commands

1. Insert data into a table

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

2. Insert multiple rows into a table

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...),
          (value4, value5, value6, ...),
          (value7, value8, value9, ...);

3. Read all data from a table

   SELECT * FROM table_name;

4. Read limited data from a table

   SELECT * FROM table_name
   LIMIT number_of_rows;

5. Read specific columns from a table

    SELECT column1, column2, column3
    FROM table_name;

6. Read columns with an alias

   SELECT column1 AS alias_name, column2 AS alias_name
   FROM table_name;

7. Order data by a column

   SELECT * FROM table_name
   ORDER BY column_name ASC|DESC;

8. Get only unique values

   SELECT DISTINCT column_name
   FROM table_name;

9. Update data in a table

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

10. Filter with LIKE string filter

    SELECT * FROM table_name
    WHERE column_name LIKE '%value%';

    Everything inside the % can be anything. The % is a wildcard that matches any number of characters.

11. Use boolean operators

    SELECT * FROM table_name
    WHERE column_name = value1 AND column_name = value2;

    SELECT * FROM table_name
    WHERE column_name = value1 OR column_name = value2;

12. The BETWEEN operator

    SELECT * FROM table_name
    WHERE column_name BETWEEN value1 AND value2;

13. Delete data from a table

    DELETE FROM table_name
    WHERE condition;

JOIN Commands

Use JOIN to combine rows from two or more tables based on a shared column.

1. Basic JOIN

   SELECT * FROM table1
   JOIN table2
   ON table1.column_name = table2.column_name;

There are multiple types of JOINs:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT JOIN: Returns all records from the left table and the matched records from the right table.
• RIGHT JOIN: Returns all records from the right table and the matched records from the left table.

Aggregate Functions

1. Average

   SELECT AVG(column_name)
   FROM table_name;

2. Sum

   SELECT SUM(column_name)
   FROM table_name;

3. Count

   SELECT COUNT(column_name)
   FROM table_name;

4. Group by merges rows that have the same values in specified columns into summary rows.

   SELECT id, COUNT(id) FROM table_name
   GROUP BY id;

   SELECT b.column1 as column_name, COUNT(a.coluumn2) as number_of_rows
   FROM table1 AS b
   LEFT JOIN table2 AS a ON a.column_name = b.column_name
   GROUP BY b.column1;

5. Having The HAVING clause is used to filter records that work on summarized GROUP BY results.

   SELECT column_name, COUNT(column_name)
   FROM table_name
   GROUP BY column_name
   HAVING COUNT(column_name) > value;