SYNTAX

Database Basics

CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name;
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;
USE database_name;
SHOW DATABASES;

Table Structure Basics

SHOW TABLES;

CREATE TABLE table_name (
    column1 INT PRIMARY KEY,
    column2 VARCHAR(50) NOT NULL,
    column3 INT DEFAULT 25000,
    CONSTRAINT CHECK (column3 >= 18)
);

INSERT INTO table_name (col1, col2) VALUES (val1, val2), (val3, val4);
DROP TABLE table_name;
TRUNCATE TABLE table_name;

Common Data Types

• INT
• VARCHAR(50)

Keys & Linking Rules (Constraints)

PRIMARY KEY
NOT NULL
DEFAULT
CONSTRAINT CHECK

FOREIGN KEY (col) REFERENCES tableName1(col)
    ON UPDATE CASCADE          -- To have data updated from parent and child table

    ON DELETE CASCADE          -- To delete

Extracting & Filtering Data (Queries)

SELECT * FROM table;
SELECT DISTINCT col FROM table;

SELECT AggFunction(col)        -- (MIN, MAX, AVG, COUNT, SUM)
FROM table_name
WHERE condition                -- (AND, OR, BETWEEN + AND, IN, NOT IN)
                               -- (=, !=, +, -, *, /, %, <, >, <=, >=)
GROUP BY col
HAVING condition
ORDER BY col                   -- (DESC, ASC)
LIMIT number;

Subqueries

SELECT * FROM table WHERE col > (SELECT AVG(col) FROM table);

-- {subqueries}

Combining Tables (Joins Matrix)

Here is your exact structural layout for combining tables:

SELECT * FROM table1 AS short-table-name1
(INNER JOIN, LEFT JOIN, UNION[FULL JOIN], RIGHT JOIN) table2 AS short-table-name2
ON stn1.col = stn2.col (IS NULL{opp.of inner join})

Modifying Rows (Data Manipulation)

SET sql_safe_updates = 0;

UPDATE table_name
SET col = value
WHERE condition;               -- Can use (BETWEEN + AND)

DELETE FROM table_name
WHERE condition;

Migrating Schema (Altering Design)

ALTER TABLE table_name ADD COLUMN col_name INT;
ALTER TABLE table_name DROP COLUMN col_name;
ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE table_name CHANGE COLUMN old_name new_name DATA_TYPE;
ALTER TABLE table_name MODIFY col_name DATA_TYPE DEFAULT value;

Shortcuts & Virtualization (Views)

CREATE VIEW viewName AS SELECT colNames FROM table;
SELECT * FROM viewName;
DROP VIEW viewName;

Combining Tables (Joins)

-- Inner, Left, Right, and Full (Outer) Joins
SELECT * FROM table1 AS s
INNER JOIN table2 AS c ON s.id = c.id;

SELECT * FROM table1 AS s
LEFT JOIN table2 AS c ON s.id = c.id;

SELECT * FROM table1 AS s
RIGHT JOIN table2 AS c ON s.id = c.id;

-- Full Outer Join (Using UNION)
SELECT * FROM table1 AS s LEFT JOIN table2 AS c ON s.id = c.id
UNION
SELECT * FROM table1 AS s RIGHT JOIN table2 AS c ON s.id = c.id;

-- Anti-Joins (Opposite of Inner Join using IS NULL)
SELECT * FROM table1 AS s LEFT JOIN table2 AS c ON s.id = c.id WHERE c.id IS NULL
UNION
SELECT * FROM table1 AS s RIGHT JOIN table2 AS c ON s.id = c.id WHERE s.id IS NULL;

Joining a Table to Itself (Self Join)

SELECT a.col1 AS alias_name, b.col1
FROM table AS a
JOIN table AS b 
ON a.PK_col = b.FK_col;

Stacking Result Sets

SELECT col FROM table
UNION                          -- Removes duplicates
SELECT col FROM table;

SELECT col FROM table
UNION ALL                      -- Keeps duplicates
SELECT col FROM table;