Date & Time Functions
An aggregate function processes values from a set of rows and returns a summary value. They appear in a SELECT clause and process all rows that satisfy the WHERE clause condition. If a SELECT statement has no WHERE clause, the aggregate function processes all rows.
Ignores NULL values.
[S]ingle quotes are for [S]trings Literals (date literals are also strings);
[D]ouble quotes are for [D]atabase Identifiers;
Explicit values that are string, numeric, or binary.
Strings must be surrounded by single quotes or double quotes.
Binary values are represented with x'0' where the 0 is any hex value.
USE & SHOW
Additional SHOW statements generate information about system errors, configuration, privileges, logs, etc.
Create & Drop Databases
Create & Drop Table
MySQL Data Types
UNSIGNED Integers have the same range but start from zero.
Returns NULL when either operand is NULL
When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause.
Determine if a value matches one of several values.
Value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue.
% matches any number of characters. Ex: LIKE 'L%t' matches "Lt", "Lot", "Lift", and "Lol cat".
_ matches exactly one character. Ex: LIKE 'L_t' matches "Lot" and "Lit" but not "Lt" and "Loot".
Case-insensitive by default Case-sensitive if followed by the BINARY keyword. Ex: LIKE BINARY 'L%t' matches 'Left' but not 'left'.
Wildcard search % or _, a backslash (\) must precede % or _. Ex: LIKE 'a\%' matches "a%".
Order selected rows by one or more columns in ascending order. DESC orders rows in descending order.
Commonly used with aggregate functions. GROUP BY and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group.
Appears between the WHERE clause, if any, and the ORDER BY clause.
Used with GROUP By to filter group results. Follows GROUP BY and precedes ORDER BY.
Prefix & Alias
Prefix is the TableName.ColumnName.
Alias = AS
The AS keyword is optional and may be omitted. Ex: SELECT Name N FROM Country C.
SELECT LeftColumn, RightColumn
FROM LeftTable, RightTable
WHERE Key = Key;
FROM specifies the left table.
INNER JOIN or FULL JOIN specifies the right table.
ON specifies the join columns.
Union Full Join
Use for FULL JOINS in MtSQL. Similar to JOIN but JOIN is good practice. The first SELECT returns matching rows and the second SELECT returns unmatched Department rows. The UNION keyword combines the two results into one table.
Table1: First Table in Database.
Table2: Second Table in Database.
column_match: The column common to both the tables.
Compares columns with an operator other than =, such as < and >.
Joins a table to itself. A self-join can compare any columns of a table, as long as the columns have comparable data types. If a foreign key and the referenced primary key are in the same table, a self-join commonly compares those key columns. In a self-join, aliases are necessary to distinguish left and right tables.
Combines two tables without comparing columns. Uses a CROSS JOIN clause without an ON clause. As a result, all possible combinations of rows from both tables appear in the result.
Sometimes called a nested query or inner query, is a query within another SQL query. Subquery runs first.
The subquery is typically used in a SELECT statement's WHERE clause to return data to the outer query and restrict the selected results. The subquery is placed inside parentheses ().
When the subquery's WHERE clause references a column from the outer query. The rows selected depend on what row is currently being examined by the outer query.
If a column name is identical to a column name in the outer query, the TableName.ColumnName differentiates the columns. Ex: City.CountryCode refers to the City table's CountryCode column. An alias can also help differentiate the columns.
Returns TRUE if a subquery selects at least one row and FALSE if no rows are selected. The NOT EXISTS operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected.
Replacing with a join. Most subqueries that follow NOT EXISTS or contain a GROUP BY clause cannot be flattened.
1. Retain the outer query SELECT, FROM, GROUP BY, HAVING, and ORDER BY clauses.
2. Add INNER JOIN clauses for each subquery table.
3. Move comparisons between subquery and outer query columns to ON clauses.
4. Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses.
5. Remove duplicate rows with SELECT DISTINCT.