Difference between revisions of "SQL Syntax"
From AgileApps Support Wiki
imported>Aeric |
imported>Aeric (→tables) |
||
Line 53: | Line 53: | ||
===tables=== | ===tables=== | ||
<div style="font-family:monospace; font-size:larger"> | <div style="font-family:monospace; font-size:larger"> | ||
:[(] table [, table ...] [)] | :[(] ''[[#table|table]]'' [, ''[[#table|table]]'' ...] [)] | ||
</div> | </div> | ||
<blockquote> | <blockquote> | ||
====table==== | ====table==== | ||
<div style="font-family:monospace; font-size:larger"> | <div style="font-family:monospace; font-size:larger"> | ||
: table_name [ [AS] alias ] | : ''table_name'' [ [AS] ''alias'' ] | ||
: | join_table | : | ''[[#join_table|join_table]]'' | ||
</div> | </div> | ||
====join_table==== | ====join_table==== | ||
<div style="font-family:monospace; font-size:larger"> | <div style="font-family:monospace; font-size:larger"> | ||
:table [INNER | LEFT | RIGHT] JOIN tables | :[[#table|table]] [INNER | LEFT | RIGHT] JOIN [[#tables|tables]] | ||
::[ON on_clause] | ::[ON ''[[#on_clause|on_clause]]''] | ||
</div> | </div> | ||
====on_clause==== | ====on_clause==== | ||
<div style="font-family:monospace; font-size:larger"> | <div style="font-family:monospace; font-size:larger"> | ||
:on_condition [ {AND|OR} on_condition ...] | :''[[#on_condition|on_condition]]'' [ {AND|OR} ''[[#on_condition|on_condition]]'' ...] | ||
</div> | </div> | ||
Line 78: | Line 78: | ||
: | ''table.col_name'' = ''table.col_name'' | : | ''table.col_name'' = ''table.col_name'' | ||
: | ''table.col_name'' != ''table.col_name'' | : | ''table.col_name'' != ''table.col_name'' | ||
: | ( on_clause ) | : | ( ''[[#on_clause|on_clause]]'' ) | ||
</div> | </div> | ||
where: | where: |
Revision as of 19:16, 17 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_references
- [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.)
- 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:: MySQL Select Statement syntax
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;
- SELECT CONCAT(last_name,', ',first_name) AS full_name
tables
table
- table_name [ [AS] alias ]
- | join_table
join_table
on_clause
- on_condition [ {AND|OR} on_condition ...]
on_condition
- table.col_name IS [NOT] NULL
- | table.col_name = table.col_name
- | table.col_name != table.col_name
- | ( on_clause )
where:
- table (name or alias) is required
where_clause
An expression that evaluates to true or false for a row. When true, the row is selected. The expression has the form:
- ...
where:
- xxx
- ...
expr
- SQL Function
- | 'string literal'
- | ( expr }
- | [+|-] expr
- Our expression / MySQL simpleExpression:
- | expr + expr
- | expr - expr
- | expr * expr
- | expr / expr
- MySQL expression:
- | expr OR expr
- | expr AND expr
- Our BNF:
- | expr IS [NOT] NULL
- | expr =