SQL Syntax

From AgileApps Support Wiki
Revision as of 21:26, 17 November 2011 by imported>Aeric (→‎tables)

Here is the syntax for the SQL SELECT statement that the SQL parser recognizes.

Considerations
  • SQL syntax is case insensitive.
  • Field and table names are case sensitive
Legend
  • [ x ] - Optional (one or none)
  • [, x ... ] - Optional additional values, in a comma-separated list
  • x | y - Choose one. Curly braces are added when needed: { x | y } ...
  • CAPITALIZED - SQL Keyword (case insensitive)
  • italicized - Value you supply. (Table names and column names are case-sensitive)

SELECT Statement

In a select statement, you designate one more columns separated by commas (or "*" for all columns), plus a table or join to get the data from, and additional options:

Examples
SELECT * FROM Customers
SELECT * FROM Customers LIMIT 80
SELECT * FROM Customers ORDER BY zip_code, customer_name
SELECT year, SUM(order_amount) FROM `Order` GROUP BY year WITH ROLLUP;
Note: The "Order" table must be enclosed in backticks, because that name is a reserved word in SQL.
Syntax
SELECT
[DISTINCT]
[(] column_expr [, column_expr ...] [)]
FROM tables
[WHERE where_clause]
[GROUP BY col_name [ASC|DESC]  [, col_name [ASC|DESC] ...]
[WITH ROLLUP]
[HAVING where_clause]
]
[ORDER BY col_name [ASC|DESC]  [, col_name [ASC|DESC] ...]
[LIMIT {maximum_rows | offset, maximum_rows} ]

where:

DISTINCT
Eliminates duplicate rows from the result set.
For example: DISTINCT(customer_name, address)
(Parentheses are optional, but must be matched if present.)
WITH ROLLUP
Adds summary rows that aggregate values for each group.
HAVING
Is only allowed as part of GROUP BY, for performance reasons. (WHERE does the same thing outside of a GROUP BY clause, only more efficiently.)
ASC
Ascending (the default).
DESC
Descending.
maximum_rows
Maximum number of rows to return.
offset
The row to start from. Offset for the first row is zero (0).
Learn more:

column_expr

*  |  table_alias.*  |  col_name [ [AS] col_alias ]  |  expr [AS] col_alias

where:

col_alias
Used for the column name in the result set in the SQL Browser, or for the tag name in the REST execSQL resource.
Required when an expression is specified.
Can be used as a field name in a group_by_clause or order_by_clause.
For example:
SELECT CONCAT(last_name,', ',first_name) AS full_name
FROM Customer_Contacts ORDER BY full_name;

tables

Example
SELECT cust.customer_name, orders.order_amount 
   FROM `Order` as orders, Customer AS cust
   GROUP BY cust.customer_name WITH ROLLUP

Customer order query.png

...

File:...

Syntax
[(] table [, table ...] [)]

table

  table_name [ [AS] alias ]
| join_table

join_table

table [INNER | LEFT | RIGHT] JOIN tables
[ON on_clause]

on_clause

on_condition [ {AND|OR} on_condition ...]

on_condition

  table_alias.col_name IS [NOT] NULL
| table_alias.col_name = table_alias.col_name
| table_alias.col_name != table_alias.col_name
| ( on_clause )

where:

  • If multiple tables are specified, a JOIN is implied
  • An INNER join (aka a STRAIGHT join) is the default.
  • table_alias is required when specifying columns in a JOIN.

where_clause

An expression that evaluates to true or false for a row. When true, the row is selected.

Example
SELECT orders.order_number, Item.amount 
   FROM `Order` as orders, OrderItems AS Item
   WHERE Item.related_to_Orders = orders.id
   ORDER BY orders.order_number

Order items query.png

Syntax
condition [ {AND|OR} condition ...]

condition

  expr IS [NOT] NULL
| expr [NOT] LIKE wildcard_string
| expr [NOT] REGEXP pattern_string
| expr [NOT] IN (expr [, expr ...])
| expr = expr
| expr < expr
| expr > expr
| expr != expr
| expr <= expr
| expr >= expr

expr

  string
| number
| date/tim
| column_name
| SQL Function
| + expr
| - expr
| (expr [, expr ...] )

string

...

number

...

date/time

...

wildcard_string

...

pattern_string

...