Operators
=
Equal
<>
Not equal
>
Greater than
<
Less than.
>=
Greater than or equal.
<=
Less than or equal.
BETWEEN
Between an inclusive range.
IN
Search for exact value in a list.
LIKE
Search for a pattern.
%
Wildcard/s in a pattern.
AND
Used between arguments in WHERE statement.
OR
Used between arguments in WHERE statement
ASC
Alphabetical and numerical order – default for ORDER BY statement
DESC
Reverse alphabetical and numerical order
Data Types
integer(size)
Integers only. Maximum number of digits specified in parenthesis.
int(size)
Integers only. Maximum number of digits specified in parenthesis.
smallint(size)
Integers only. Maximum number of digits specified in parenthesis.
tinyint(size)
Integers only. Maximum number of digits specified in parenthesis.
decimal(size,d)
Hold numbers with fractions. Maximum number of digits specified in “size”. Maximum number of digits to the right of the decimal is specified in “d”.
numeric(size,d)
Hold numbers with fractions. Maximum number of digits specified in “size”. Maximum number of digits to the right of the decimal is specified in “d”.
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.
date(yyyymmdd)
Holds a date
Functions
SUM(column)
Return the total sum of columns’ values
AVG(column)
Return the average value of a column
COUNT(column)
Return the number of rows (excluding NULL) of a column
MAX(column)
Return the highest value of a column
MIN(column)
Return the lowest value of a column
Database Manipulation
Create database
CREATE DATABASE database_name
Delete database
DROP DATABASE database_name
Table Manipulation
Create table
CREATE TABLE “table_name”
(“column_1” “data_type_for_column_1”,
“column_2” “data_type_for_column_2”,
… )
Add column to a table
ALTER TABLE table_name
ADD column_name datatype
Delete column from a table
ALTER TABLE table_name
DROP column_name datatype
Delete table
DROP TABLE table_name
Index Manipulation
Create index
CREATE INDEX index_name
ON table_name (column_name_1, column_name_2, …)
Create a unique index
CREATE UNIQUE INDEX index_name
ON table_name (column_name_1, column_name_2, …)
Delete index
DROP INDEX table_name.index_name
Data Manipulation
Insert new rows into a table
INSERT INTO table_name VALUES (value_1, value_2,….)
or
INSERT INTO table_name (column1, column2,…)
VALUES (value_1, value_2,….)
Update column/s
UPDATE table_name
SET column_name_1 = new_value_1, column_name_2 = new_value_2
WHERE column_name = some_value
Delete row/s
DELETE FROM table_name
WHERE column_name = some_value
Delete the data from a table
TRUNCATE TABLE table_name
Select
Select all data from a table
SELECT *
FROM table_name
Select all data from specified columns
SELECT column_name(s)
FROM table_name
Select using alias
SELECT table_alias.column_name AS column_alias
FROM table_name AS table_alias
Select only distinct (different) data from a table.
SELECT DISTINCT column_name(s)
FROM table_name
Limit selection
Select data using WHERE, AND, OR
SELECT column_name(s) FROM table_name
WHERE column operator value
AND column operator value
OR column operator value
Select data using IN.
SELECT column_name(s) FROM table_name
WHERE column_name IN (value1, value2, …)
Order selection
Order results buy column value ascending or descending.
SELECT column_name(s) FROM table_name
ORDER BY column_1 DESC/ASC
Join selection
Select all matching rows from 2 tables.
SELECT column_1_name, column_2_name, …
FROM first_table_name
INNER JOIN second_table_name
Select all rows from the first table, even if there are no matches in the second table.
SELECT column_1_name, column_2_name, …
FROM first_table_name
LEFT JOIN second_table_name
ON first_table_name.key = second_table_name.foreign_key
Select all rows from the second table, even if there are no matches in the first table.
SELECT column_1_name, column_2_name, …
FROM first_table_name
RIGHT JOIN second_table_name
ON first_table_name.key = second_table_name.foreign_key
Add up selections
Select all the values from multiple SQL statements.
SQL_Statement_1
UNION ALL
SQL_Statement_2
Select all the different values from multiple SQL statements.
SQL_Statement_1
UNION
SQL_Statement_2 Select
Populate with selection
Select and insert into another table.
SELECT column_name(s)
INTO new_table_name
FROM source_table_name
WHERE …
Select and insert into another database.
SELECT column_name(s)
IN external_database_name
FROM source_table_name
WHERE …
Populate a virtual table with the result-set of a SELECT statement.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE …
