Difference between revisions of "SQL Syntax"

From AgileApps Support Wiki
imported>Aeric
imported>Aeric
Line 31: Line 31:
where:
where:
:;''column_alias'':  
:;''column_alias'':  
:: Used for the column name in the result set, in the [[SQL Browser]], or for the tag name in the REST [[REST API:sql Resource|sql resource]].
:: Used for the column name in the result set, in the [[SQL Browser]], or for the tag name in the REST [[REST API:execSQL Resource|execSQL resource]].
:: Required when an expression is specified.  
:: Required when an expression is specified.  
:: Can be used as a field name in a [[#group_by_clause|group_by_clause]] or [[#order_by_clause|order_by_clause]].
:: Can be used as a field name in a [[#group_by_clause|group_by_clause]] or [[#order_by_clause|order_by_clause]].

Revision as of 02:32, 16 November 2011

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:

SELECT
[ DISTINCT ]
[ ( ]  column_expr [ , column_expr ... ]  [ ) ]
FROM table_reference
[ WHERE query [ {AND|OR} query ... ] ]
[ GROUP BY group_by_clause  [HAVING query [{AND|OR} query ...]] ]
[ ORDER BY order_by_clause ]
[ LIMIT limit_clause ]

where:

DISTINCT
Eliminates duplicate rows from the result set.
For example: DISTINCT(customer_name, address)
HAVING
Is only allowed as part of GROUP BY, for performance reasons. (WHERE is vastly more efficient.)

Learn more:: MySQL Select Statement syntax

column_expr

*  |  table_alias.*  |  column_name [ [AS] column_alias ]  |  expr [AS] column_alias

where:

column_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;

expr

SQL Function
   | stuff...

table_reference

...

where:

xxx
...

query

...

where:

xxx
...

group_by_clause

GROUP BY column_name [ASC | DESC]  [ , column_name [ASC | DESC] ... ]  [ WITH ROLLUP ]

where:

ASC
Ascending (the default).
DESC
Descending.

order_by_clause

ORDER BY column_name [ASC | DESC]  [ , column_name [ASC | DESC] ... ]

where:

ASC
Ascending (the default).
DESC
Descending.

limit_clause

maximum_rows  |  offset, maximum_rows

where:

maximum_rows
Maximum number of rows to return.
offset
The row to start from. Offset for the first row is zero (0).