SQL Reference Sheet
SQL commands and datatypes can be difficult to memorize. A quick look-up chart or cheatsheet can be helpful when working with SQL. Below is a basic chart created for reference when working with a SQL database.
Data Types
Date TypeDescriptionINTEGER(size)
INT(size)
SMALLINT(size)
TINYINT(size)
Hold integers only. The maximum number of digits are specified in parenthesis.BOOLEANStores TRUE or FALSE valuesFLOAT(p)Holds a floating number in base 10 exponential notation. The size argument for this type consists of a single number specifying the minimum precision. ‘p’ is the mantissa precision.REALHolds a real number. Mantissa Precision is 7.TEXTHolds a variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.CHAR(size)Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis.VARCHAR(size) Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis.BINARYHolds a maximum length of 8,000 bytesDATEStores year, month, and day valuesTIMEStores hour, minute, and second values TIMESTAMPStores year, month, day, hour, minute, and second valuesBLOBA large amount of text
Table Manipulation
CommandDescriptionCREATE TABLE “table_name”
(“column_1” “data_type_for_column_1”,
“column_2” “data_type_for_column_2”,
… )
Create a table in a database.ALTER TABLE table_name ADD column_name datatype
Add columns in an existing table.ALTER TABLE table_name DROP column_name datatype
Deletes columns in an existing table.DROP TABLE table_name
Deletes a table.
Data Manipulation (Insert/Delete)
CommandDescriptionINSERT INTO table_name
VALUES (value_1, value_2,….)
Insert new rows into a table.INSERT INTO table_name (column1, column2,…)
VALUES (value_1, value_2,….)
Update one or several columns in rows.DELETE FROM table_name
WHERE column_name = some_value
Delete rows in a table.TRUNCATE TABLE table_nameDeletes the data inside the table.UPDATE table SET
column1=value1,
column2=value2,
…
columnk=valuek
WHERE condition(s)
Updates data in a table
Querying Data in Table
CommandDescriptionSELECT column_name(s) FROM table_nameSelect data from a table.SELECT * FROM table_nameSelect all data from a table.SELECT DISTINCT column_name(s) FROM table_nameSelect only distinct (different) data from a table.SELECT column_name(s) FROM table_name
WHERE column operator value
AND column operator value
OR column operator value
AND (… OR …)
…
Select only certain data from a table.
Select only certain data from a table.
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1, value2, …)
The IN operator may be used if you know the exact value you want to return for at least one of the columns.SELECT column_name(s) FROM table_name
ORDER BY row_1, row_2 DESC [row_3 ASC]
Select data from a table with sort the rows. ASC (ascend) is a alphabetical and numerical order. DESC is a reverse alphabetical and numerical order
Mathematical Functions in SQL
CommandDescriptionCOUNT(column | *)Count rows per groupAVG(column)Average value of groupMIN(column)Minimum value of groupMAX(column)Maximum value of groupSUM(column)Sum values in a groupABS(number)Absolute value of a numberROUND(number)Rounds a numberFLOOR(number)Largest integer not greaterCEILING(number)Smallest integer not smallerSQRT(number)Square root of a numberPOW(base, exponent)Nth powerRAND()Random Number
Join Functions
CommandDescriptionGraphicSELECT column_1_name, column_2_name, …
FROM first_table_name
INNER JOIN second_table_name
ON first_table_name.keyfield = second_table_name.foreign_keyfield
The INNER JOIN returns all rows from both tables where there is a match. If there are rows in first table that do not have matches in second table, those rows will not be listed.SELECT column_1_name, column_2_name, …
FROM first_table_name
LEFT JOIN second_table_name
ON first_table_name.keyfield = second_table_name.foreign_keyfield
The LEFT OUTER JOIN returns all the rows from the first table, even if there are no matches in the second table. If there are rows in first table that do not have matches in second table, those rows also will be listed.SELECT column_1_name, column_2_name, …
FROM first_table_name
RIGHT JOIN second_table_name
ON first_table_name.keyfield = second_table_name.foreign_keyfield
The RIGHT OUTER JOIN returns all the rows from the second table, even if there are no matches in the first table. If there had been any rows in second table that did not have matches in first table, those rows also would have been listed.
