Some notes about SQL

Database
Query
Filtering
Inner join
Sets
Grouping
Subqueries
Outer and Cross joins
Conditional logic
Transaction
Indexes and Constraints
Views

Database

Data types

Character data

char(...) (have fixed length);
varchar(...) (variable lenght).

Numerical data

Tinyint;
Smallint;
Mediumint;
Int;
Bigint.

Each implements a different range of integers.

Temporal data

Date;
Datetime;
Timestamp;
Year;
Time.

Undefined

NULL

Table creation

CREATE TABLE table (...)

The statement must contain one or more column definitions.

Column definition

<column> <TYPE>

Check constraints

<column> <TYPE> CHECK (<column> IN (<values>))

Are optional.
Primary key constraints

CONSTRAINT pk_<column> PRIMARY KEY (<column>)

Can be one or more. Primary key values must be unique.
Foreign key contraints

CONSTRAINT fk_<key> FOREIGN KEY (<column>) REFERENCES <table> (<column>)

Values in <column> must exist in <table>~/~<column>.

Can be on or more.

Inserting data

INSERT INTO <table> (<columns>) VALUES (<values>)

The number of columns and the number of values must correspond.

Updating data

UPDATE <table> SET <columns> WHERE <condition>

Omit WHERE clause to update every row in the table.

Deleting data

DELETE FROM <table> WHERE <condition>

Omit WHERE clause to delete every row in the table.

Query

Select

SELECT <items> FROM <containers> [WHERE ... ORDER BY ...]

<items> can be one or more:

columns
literals
built-in functions
user defined functions

<items> can be aliased: <item> AS <name>.

Use * to select all columns.

SELECT DISTINCT ...

The DISTINCT clause removes duplicates from the result.

From

Identifies the containers to by queried. Containers can be tables, subqueries and views.

Defines the means to link the tables toghether (i.e. with Joins). Tables can be aliased: <table> AS <name>.

Where

WHERE <logical expression>

The WHERE clause is used to filter out unwated rows.

Order by

ORDER BY <criteria>

Sorts the result set. <criteria> can be raw column data or an expression. ASC or DESC keywords can be used to specify ascending or descending order.

Filtering

WHERE clause

Operators

Clause conditions evaluates using the usual boolean logic:

<condition 1> AND <condition 2>

<condition 1> OR <condition 2>

NOT <condition>

() cab be used to group conditions.

<condition> can be:

a number
a column name
a string literal
a built-in function
a subquery
a list of expressions

Condition expressions

Equality

column = value
Inequality

column <> value
Range

column BETWEEN lower_limit AND upper_limit
Membership

column IN (NOT IN) values column IN (NOT IN) subquery
Matching
column LIKE expression

Expression can contain wildcards:
- _ means exactly one character
- % means 0 or more characters

Inner join

It is a query that joins two or more tables (by performing an intesection).

Joining two tables

SELECT <columns> FROM <table1> INNER JOIN <table2> ON <table1 column> = <table2 column>

Joining N tables

Specify (N - 1) JOIN ... ON ... clauses.

Self joins

To perform a join on the same table, use a different alias for each instance of the table.

Non-equi-joins

Join tables via ranges of values.

Filter conditions

To perfom filtering to narrow the result, add a ... WHERE <condition> clause at the end of the query.

Sets

Prerequisites

Data sets must have the same number of columns;
Columns must give the same data type (or data types that can be converted to one another).

Union

UNION (UNION ALL)

Combine two sets, with common elements included only once. UNION ALL keeps duplicates.

Intersect

INTERSECT (INTERSECT ALL)

Include only elements common to both sets. INTERSECT ALL keeps duplicates.

Except

EXCEPT (EXCEPT ALL)

Returns the first set minus the elements common to both sets. EXCEPT ALL removes only one occourrence per each duplicate element.

Grouping

GROUP BY <columns>

Groups data by specific column, filtering out unwanted data.

HAVING <filter condition>

Filter condition works on grouped data.

In the GROUP BY clause it is possibile to specify a single column, multiple columns or an expression.

Subqueries

Subqueries are queries contained within another SQL statement.

Non Correlated

Subqueries that do not reference anything from the containing statement. Can return different kinds of result sets.

Single row, single column

Can be used with the comparison operators.

Multiple rows, single column

Need special operators.

IN / NOT IN

Searches for a value within the set.

ALL / ANY

Must be preceded by a comparison operator

Compares a value against all values in a set.

ALL: TRUE if comparison is true for all elements in the set;
ANY: TRUE if comparison is true for just one element of the set.

Multiple columns

Compare against all columns at the same time.

Correlated

It is dependent from its containing statement, as it references one or more of the containing statement columns.

The conditions usually used with correlated subqueries are equality, range or relationship existence (EXISTS / NOT EXISTS).

Usage

As data sources

Include the subqueries in the FROM clause (non correlated queries only).

In filter conditions

Include the subqueries in the WHERE or HAVING clauses.

As expression generators

Include the subqueries anywhere an expression can appear (scalar subqueries only).

Outer and Cross joins

Outer join

... <LEFT|RIGHT> OUTER JOIN ...

Includes all rows from one table and matching rows from the other table.

LEFT: includes all rows from the table on the left side of the join;
RIGHT: includes all rows from the table on the right side of the join.

Cross join

... CROSS JOIN ...

Returns the cartesian product of two tables.

Conditional logic

Simple Case

CASE <value>
     WHEN <value 1> THEN <expression 1>
     ...
     WHEN <value n> THEN <expression n>
     [ELSE <expression>]
END

Uses equality conditions by default

Searched Case

CASE
    WHEN <condition 1> THEN <expression 1>
    ...
    WHEN <condition n> THEN <expression n>
    [ELSE <expression>]
END

Supports user defined conditions.

Transaction

It is a device to ensure atomicity: all or none of the statements succeeded.

Begin a transaction

BEGIN TRANSACTION

Needs to be started explicitly, otherwise statements are automatically committed independently.

End a transaction

COMMIT

Commit changes. A schema statement auto commits the current transaction.

ROLLBACK

Undo changes. A deadlock detection causes a rollback.

Savepoints

SAVEPOINT <savepoint>

ROLLBACK TO SAVEPOINT <savepoint>

Used to rollback to a particular statement.

Indexes and Constraints

Indexes

Create

ALTER TABLE <table> ADD <INDEX|UNIQUE> <index_name> (<column>)

INDEX allows duplicate values for the column;
UNIQUE forces unique values in the column.

To create a multicolumn index specify multiple, comma separated columns.

List

SHOW INDEX FROM <table>

Remove

ALTER TABLE <table> DROP INDEX <index_name>

Best practice

Index primary key columns (done by the server by default);
Index all columns referenced in foreign keys constraints;
Index all columns that will be frequently used.

Constraints

CREATE TABLE ... CONSTRAINT ...
ALTER TABLE ... ADD CONSTRAINT ...
ALTER TABLE ... DROP ...

Primary key

Column(s) that guarantees uniqueness within a table;
Generates unique index.

Foreign key

... CONSTRAINT <name> FOREIGN KEY (<column>) REFERENCES <table> (<column>)

Restrict column(s) values to the ones found in another table primary key;
Add ON UPDATE CASCADE clause to propagate changes to child rows (otherwise changing a foreign key value will raise an error);
Add ON DELETE CASCADE clause to delete child rows as well (otherwise deleting a foreign key value will raise an error);

Views

CREATE <view> (<columns>) AS SELECT ...

Are temporary tables created on-the-fly by a select statement.

data can be queried;
data can be updated with restrictions (restrictions are server dependant);
no data is stored, just the view definition.

Views are useful to limit the data a regular user can query, return pre-aggregated data to the end users and hide tables structure.