SQL | DB Cheatsheet

Structured Query Language

Manipulation

Create Table

CREATE TABLE table_name (column1_name data_type, column2_name data_type, etc.);

This creates a new table in your database. You can include as many columns as you want.

Alter Table

ALTER TABLE table ADD COLUMN new_column data_type;

Modify the columns in a table.

Select

SELECT column1, column2, etc. FROM table;

This pulls data from a table.

Select Distinct

SELECT DISTINCT column FROM table;

Select every unique value in a column.

Insert

INSERT INTO table (column1, column2, etc.) VALUES (value1, value2, etc.);

This inserts a row into the specified table.

Update

UPDATE table SET column1 = new_value WHERE column2 = value;

This updates data in a table. You can change the data in a column for all rows meeting certain criteria.

Delete From

DELETE FROM table WHERE column IS criteria;

Delete rows in a table.

Queries

Like

SELECT column FROM table WHERE column LIKE criteria;

Return rows where data in the specified column matches the criteria.
'_' is a search wildcard ('Se_en' returns 'Seven' and 'Se7en').
'%' is a append wildcard ('d%' returns 'dog,' '%h' returns 'fish,' '%a%' returns 'cat').

And

SELECT column FROM table WHERE column1 = criteria1 AND column 2 = criteria2;

Link multiple search criteria in a single query.

Between ... And ...

SELECT column FROM table WHERE column BETWEEN criteria1 AND criteria2;

Search for rows with values between two boundary values.

Or

SELECT column FROM table WHERE column1 = criteria1 OR column2 = criteria2;

Search for rows that satisfy at least one of a given OR statement.

Order by

SELECT column FROM table ORDER BY criteria ASC;

Return the columns, ordered either ascending (smallest to largest, ASC) or descending (largest to smallest, DESC).

Limit

SELECT column FROM table LIMIT number;

Return up to the limit rows meeting your search criteria.

Aggregate Functions

Count

SELECT COUNT(column) FROM table;

Return a count of rows with column =/= NULL. To see all rows, set column = *

Group By

SELECT column1, FUNCTION(column2) FROM table GROUP BY column1;

Groups the results by the distinct values in column1. Works best with aggregator functions.

Sum

SELECT SUM(column) FROM table;

Aggregates sum for the specified column.

Max

SELECT MAX(column) FROM table;

Finds the maximum value within the specified column.

Min

SELECT MIN(column) FROM table;

Finds the minimum value within the specified column.

Average

SELECT AVG(column) FROM table;

Finds the average value of a specified column.

Round

SELECT ROUND(column, decimal_places) FROM table;

Rounds the given output to the stated number of decimal places. If decimal_places is excluded, it will round to the nearest integer.

Multiple Tables

Primary Key

CREATE TABLE table_name (id INTEGER PRIMARY KEY, column2_name data_type2, etc.);

The primary key acts as the id value for the record. Each value in this column will be unique, and no value will be NULL.

Inner Join

SELECT table1.column, table2.column 

              FROM table1 

              JOIN table2 ON table1.foreign_key = table2.primary_key;

The inner join returns only the records from table1 that join to table2.

Left Outer Join

SELECT table1.column, table2.column 

              FROM table1 

              LEFT JOIN table2 ON table1.foreign_key = table2.primary_key;

The outer join returns all records from table1. If they join to table2, it will show the joined data. If they don't join, it will return NULL in the joined columns.

Alias

SELECT 

              table1.column AS alias1, 

              table2.column AS alias2 

              FROM table1 

              JOIN table2 ON table1.foreign_key = table2.primary_key;

Use AS to create a new alias for each column in the query results.