SQL

String types

CHAR

Fixed length string

'This is a string!'

VARCHAR

Variable length string that takes the following arguments:

'This is a string!'

BLOB

Binary Large Objects

Numeric types

BIT

BOOLEAN

INTEGER

Integer

64

FLOAT

Float (number with decimal point)

DOUBLE

Double

NUMERIC

Numeric type that takes the following argument:

MONEY

Currency type

Temporal types

DATE

DATETIME

Arithmetic operators

+

Addition

Unary positive, makes number positive

-

Subtraction

Unary negative, makes number negative

*

Multiplication

/

Division

%

Modulo (remainder of division)

++

Increment

--

Decrement

Assignment operators

=

Equals

+=

a+=b is a=a+b

-=

a-=b is a=a-b

*=

a*=b is a=a*b

/=

a/=b is a=a/b

%=

a%=b is a=a%b

&=

a&=b is a=a&b

|*=

a|*=b is a=a|b

^-=

a^-=b is a=a^b

Comparison operators

=

Is equal to

<>

Is not equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

Bitwise operators

&

Bitwise AND

|

Bitwise OR

^

Bitwise XOR

Logical operators

ADD

Add a column to a table

ALL

Represents all records of a SELECT statement

ALL(selectstatement);

AND

Returns TRUE if all subquery values separated by AND are TRUE

ANY

Represents some record of a SELECT statement

ANY(selectstatement);

BETWEEN

Returns TRUE if the operand is within the range of comparisons

EXISTS

Returns TRUE if the subquery returns one or several records

IN

Returns TRUE if the operand is equal to one of a list of expressions

IN(selectstatement);

LIKE

Returns TRUE if the operand matches a pattern with wildcards

NOT

Displays a record if the condition(s) is NOT TRUE

OR

Returns TRUE if at least one of the subquery values separated by OR is TRUE

SOME

Returns TRUE if at least one of the subquery meets the condition

IS NOT NULL

Returns TRUE if record doesn't have a null value

Miscellaneous Operators

.

Denotes a column originating from a table

*

Denotes all columns

;

Declares end of a block

()

Declares a function

''

Declares a string

Wildcards

*

Multiple character wildcard; represents zero or more characters (MS Access only)

%

Multiple character wildcard; represents zero or more characters (SQL Server only)

?

Single character wildcard; Represents a single character (MS Access only)

_

Single character wildcard; Represents a single character (SQL Server only)

[]

Represents any single character within the brackets

-

Represents any single character within the specified range

!

Represents any character not in the brackets (MS Access only)

^

Represents any character not in the brackets (SQL Server only)

Key word

SELECT

Selects tables or columns

SELECT column FROM table;

DISTINCT

Removes duplicate records in a SELECT statement

SELECT DISTINCT column FROM table;

FROM

Denotes the table for a SELECT statement

SELECT column FROM table;

AS

Denotes an alias for a column or table name

WHERE

  • Filters records by a condition
  • Sets condition to change values in UPDATE statement

    • WHERE condition;

    ORDER BY

    Orders records in ascending or descending order

    ASC

    Ascending order

    ORDER BY ASC;

    DESC

    Descending order

    ORDER BY DESC;

    UNION

    Returns results from multiple SELECT statements with an equal column count

    LIMIT

    Limits the amount of records shown in a SELECT statement

    INTO OUTFILE

    Writes query to file

    USE

    Selects a database to query to

    SHOW

    Shows databases or tables

    ALTER

    Alters a database table's columns

    UPDATE

    Updates a database table's column values based on a condition

    CREATE DATABASE

    Creates a database

    CREATE TABLE

    Creates a table

    DROP DATABASE

    Deteles a database

    CREATE DATABASE

    Creates a database

    CREATE DATABASE database;

    DROP DATABASE

    Deletes a database

    CREATE DATABASE database;

    CREATE TABLE

    Creates a table

    CREATE TABLE table
    (column1 datatype, columnn datatypen);

    DROP TABLE

    Deletes a table

    GROUP BY

    Groups results based on different values of a certain column

    HAVING

    Filtering condition applied to a column mentioned in a GROUP BY statement. Note that this key word is only present in GROUP BY statements

    GROUP BY column HAVING condition;

    SET

    Sets a variable

    CASE

    Denotes a case statement

    CASE
     WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     WHEN conditionN THEN resultN
     ELSE result
    END

    WHEN

    Denotes a condition for a CASE

    THEN

    Denotes an action for a CASE

    PRIMARY KEY

    Declares a primary key constraint

    NOT NULL

    Declares a constraint that ensures the values for this field are not null

    CHECK

    Declares a constraint that ensures the values for this field meet a custom condition

    AUTO_INCREMENT

    Increments value for each new record

    FOREIGN KEY

    Declares a foreign key constraint

    DROP COLUMN

    Removes a column

    LIKE

    Matches values with a pattern including wildcards

    CONSTRAINT

    Adds specific quality for a column

    CONSTRAINT constraintname constraintword

    REFERENCES

    Used to connect a column to a foreign key in another table

    INNER JOIN

    Returns the common values of two tables based on a certain column condition

    table1 INNER JOIN table2  ON joiningCondition

    LEFT JOIN

    Returns values in the left table based on a certain column condition between two tables

    RIGHT JOIN

    Returns values in the right table based on a certain column condition between two tables

    LEFT OUTER JOIN

    Returns values in the left table based on a certain column condition between two tables

    table1 LEFT OUTER JOIN table2  ON joiningCondition

    RIGHT OUTER JOIN

    Returns values in the right table based on a certain column condition between two tables

    table1 RIGHT OUTER JOIN table2  ON joiningCondition

    CROSS JOIN

    Performs cartesian product on two tables (in other words, displays records where each element in table A is listed besides each element in table B)

    table1 CROSS JOIN table2;

    INSERT INTO

    Appends record with listed columns to a table

    INSERT INTO table(column1, column2, columnN)
    VALUES (column1value, column2value, columnNvalue);

    VALUES

    Used for denoting values being inserted into a column

    CREATE VIEW

    Creates a view; a view is a virtual table based on an SQL query

    CREATE VIEW viewname AS viewstatement;

    DELETE

    DELETE FROM table WHERE condition;

    Functions

    MIN()

    Returns the smallest value of the selected column

    MAX()

    Returns the largest value of the selected column

    COUNT()

    Returns the amount of rows that meet a criteria

    AVG()

    SUM()

    MOD()

    Returns the modulo of two numbers

    PI()

    Returns π

    IFNULL()

    Returns an alternate value if a record's column is NULL (MySQL only)

    ISNULL()

    Returns an alternate value if a record's column is NULL (SQL Server only)

    IsNull()

    Returns TRUE if a record's column is NULL (MS Access only)

    Commenting

    --

    Used to tell compiler the rest of this line is not to be compiled

    /*

    Opens a multi line comment

    /*This isn't compiled
    neither is this line*/

    */

    Closes a multi line comment

    /*This isn't compiled
    neither is this line*/