BlitzNG
/
database.mod
Mirror von https://github.com/bmx-ng/database.mod.git


			
							123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315
							<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>SQLite Query Language: expression</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #45735f }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:240px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  font-variant: small-caps;
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding:1px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #80a796; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(images/se.png) 100% 100% no-repeat #80a796}
.sw  { background: url(images/sw.png) 0% 100% no-repeat }
.ne  { background: url(images/ne.png) 100% 0% no-repeat }
.nw  { background: url(images/nw.png) 0% 0% no-repeat }

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="lang.html">
           <h2 align="center">SQL As Understood By SQLite</h2></a><h1>expression</h1><h4><a href="syntaxdiagrams.html#expr">expr:</a></h4><blockquote> <img alt="syntax diagram expr" src="images/syntax/expr.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#literal-value">literal-value:</a></h4><blockquote> <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#signed-number">signed-number:</a></h4><blockquote> <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif"></img> </blockquote>
<h4><a href="syntaxdiagrams.html#raise-function">raise-function:</a></h4><blockquote> <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif"></img> </blockquote>


<p>This section is different from the others.  Most other sections of
this document talks about a particular SQL command.  This section does
not talk about a standalone command but about "expressions" which are 
subcomponents of most other commands.</p>

<a name="binaryops"></a>

<h3>Operators</h3>
<p>SQLite understands the following binary operators, in order from
highest to lowest precedence:</p>

<blockquote><pre>
<font color="#2c2cf0"><big>||
*    /    %
+    -
&lt;&lt;   &gt;&gt;   &amp;    |
&lt;    &lt;=   &gt;    &gt;=
=    ==   !=   &lt;&gt;   </big>IS  IN  LIKE  GLOB  MATCH  REGEXP
AND   
OR</font>
</pre></blockquote>

<p>Supported unary prefix operators are these:</p>

<blockquote><pre>
<font color="#2c2cf0"><big>-    +    ~    NOT</big></font>
</pre></blockquote>

<p>The COLLATE operator can be thought of as a unary postfix
operator.  The COLLATE operator has the highest precedence.
It always binds more tightly than any prefix unary operator or
any binary operator.</p>


<p>The unary operator <font color="#2c2cf0"><big>+</big></font> is a no-op.  It can be applied
to strings, numbers, or blobs and it always gives as its result the
value of the operand.</p>

<p>Note that there are two variations of the equals and not equals
operators.  Equals can be either

<font color="#2c2cf0"><big>=</big></font> or <font color="#2c2cf0"><big>==</big></font>.
The non-equals operator can be either
<font color="#2c2cf0"><big>!=</big></font> or <font color="#2c2cf0"><big>&lt;&gt;</big></font>.
The <font color="#2c2cf0"><big>||</big></font> operator is "concatenate" - it joins together
the two strings of its operands.
The operator <font color="#2c2cf0"><big>%</big></font> outputs the remainder of its left 
operand modulo its right operand.</p>

<p>The result of any binary operator is a numeric value, except
for the <font color="#2c2cf0"><big>||</big></font> concatenation operator which gives a string
result.</p><a name="isisnot"></a>
<p>The <font color="#2c2cf0"><big>IS</big></font> and <font color="#2c2cf0"><big>IS NOT</big></font> operators work
like <font color="#2c2cf0"><big>=</big></font> and <font color="#2c2cf0"><big>!=</big></font> except that NULL values compare
equal to one another.  <font color="#2c2cf0"><big>IS</big></font> and <font color="#2c2cf0"><big>IS NOT</big></font>
have the same precedence as <font color="#2c2cf0"><big>=</big></font>.<a name="litvalue"></a>

<h3>Literal Values</h3>
<p>
A literal value is a constant of some kind.
Literal values may be integers, floating point numbers, strings,
BLOBs, or NULLs.
Scientific notation is supported for floating point literal values.
The "." character is always used 
as the decimal point even if the locale setting specifies "," for
this role - the use of "," for the decimal point would result in
syntactic ambiguity.  A string constant is formed by enclosing the
string in single quotes (').  A single quote within the string can
be encoded by putting two single quotes in a row - as in Pascal.
C-style escapes using the backslash character are not supported because
they are not standard SQL.
BLOB literals are string literals containing hexadecimal data and
preceded by a single "x" or "X" character.  For example:</p>

<blockquote><pre>
X'53514C697465'
</pre></blockquote>

<p>
A literal value can also be the token "NULL".
</p>

<a name="varparam"></a>

<h3>Parameters</h3>
<p>
A "variable" or "parameter" token
specifies a placeholder in the expression for a literal
value that is filled in at runtime using the
Bind() APIs.
Parameters can take several forms:
</p

<blockquote>
<table>
<tr>
<td align="right" valign="top"><b>?</b><i>NNN</i></td><td width="20"></td>
<td>A question mark followed by a number <i>NNN</i> holds a spot for the
NNN-th parameter.  NNN must be between 1 and SQLITE_MAX_VARIABLE_NUMBER.</td>
</tr>
<tr>
<td align="right" valign="top"><b>?</b></td><td width="20"></td>
<td>A question mark that is not followed by a number holds a spot for
the next unused parameter.</td>
</tr>
<tr>
<td align="right" valign="top"><b>:</b><i>AAAA</i></td><td width="20"></td>
<td>A colon followed by an identifier name holds a spot for a named
parameter with the name AAAA.  Named parameters are also numbered.
The number assigned is the next unused number.  To avoid confusion,
it is best to avoid mixing named and numbered parameters.</td>
</tr>
<tr>
<td align="right" valign="top"><b>@</b><i>AAAA</i></td><td width="20"></td>
<td>An "at" sign works exactly like a colon.</td>
</tr>
<tr>
<td align="right" valign="top"><b>$</b><i>AAAA</i></td><td width="20"></td>
<td>A dollar-sign followed by an identifier name also holds a spot for a named
parameter with the name AAAA.  The identifier name in this case can include
one or more occurances of "::" and a suffix enclosed in "(...)" containing
any text at all.  This syntax is the form of a variable name in the Tcl
programming language.</td>
</tr>
</table>
</blockquote>

<p>Parameters that are not assigned values using
Bind() are treated
as NULL.</p>

<a name="like"></a>

<h3>The LIKE and GLOB operators</h3>
<p>The LIKE operator does a pattern matching comparison. The operand
to the right contains the pattern, the left hand operand contains the
string to match against the pattern.

A percent symbol ("%") in the pattern matches any
sequence of zero or more characters in the string.  An underscore
("_") in the pattern matches any single character in the
string.  Any other character matches itself or it's lower/upper case
equivalent (i.e. case-insensitive matching).  (A bug: SQLite only
understands upper/lower case for ASCII characters.  The
LIKE operator is case sensitive for unicode characters that are beyond
the ASCII range.  For example, the expression <b>'a'&nbsp;LIKE&nbsp;'A'</b>
is TRUE but <b>'&aelig;'&nbsp;LIKE&nbsp;'&AElig;'</b> is FALSE.).</p>

<p>If the optional ESCAPE clause is present, then the expression
following the ESCAPE keyword must evaluate to a string consisting of
a single character. This character may be used in the LIKE pattern
to include literal percent or underscore characters. The escape
character followed by a percent symbol, underscore or itself matches a
literal percent symbol, underscore or escape character in the string,
respectively.

<p>The infix LIKE operator is implemented by calling the
application-defined SQL functions <a href="lang_corefunc.html#like">like(<i>Y</i>,<i>X</i>)</a> or
<a href="lang_corefunc.html#like">like(<i>Y</i>,<i>X</i>,<i>Z</i>)</a></a>.</p>

<p>The LIKE operator can be made case sensitive using the
<a href="pragma.html#pragma_case_sensitive_like">case_sensitive_like pragma</a>.</p>

<a name="glob"></a>

<p>The GLOB operator is similar to LIKE but uses the Unix
file globbing syntax for its wildcards.  Also, GLOB is case
sensitive, unlike LIKE.  Both GLOB and LIKE may be preceded by
the NOT keyword to invert the sense of the test.  The infix GLOB 
operator is implemented by calling the function
<a href="lang_corefunc.html#glob">glob(<i>Y</i>,<i>X</i>)</a> and can be modified by overriding
that function.</p>

<a name="regexp"></a>

<p>The REGEXP operator is a special syntax for the regexp()
user function.  No regexp() user function is defined by default
and so use of the REGEXP operator will normally result in an
error message.  If a user-defined function named "regexp"
is added at run-time, that function will be called in order
to implement the REGEXP operator.</p>

<a name="match"></a>

<p>The MATCH operator is a special syntax for the match()
user function.  The default match() function implementation
raises an exception and is not really useful for anything.
But extensions can override the match() function with more
helpful logic.</p>

<a name="between"></a>

<h3>The BETWEEN operator</h3>
<p>The BETWEEN operator is equivalent to a pair of comparisons.
"<i>a</i> <b>BETWEEN</b> <i>b</i> <b>AND</b> <i>c</i>" is 
equivalent to 
"<i>a</i><b>&gt;=</b><i>b</i> <b>AND</b> <i>a</i><b>&lt;=</b><i>c</i>".
The precedence of the BETWEEN operator is the same as the precedence
as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

<h3>Table Column Names</h3>
<p>A column name can be any of the names defined in the <a href="lang_createtable.html">CREATE TABLE</a>
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
These special identifiers all describe the
unique integer key (the <a href="lang_createtable.html#rowid">rowid</a>) associated with every 
row of every table.
The special identifiers only refer to the row key if the <a href="lang_createtable.html">CREATE TABLE</a>
statement does not define a real column with the same name.
The rowid can be used anywhere a regular
column can be used.</p>

<h3>Subqueries</h3>
<p><a href="lang_select.html">SELECT</a> statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
With the EXISTS operator, the columns in the result set of the <a href="lang_select.html">SELECT</a> are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
If no terms in the <a href="lang_select.html">SELECT</a> expression refer to values in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  If the <a href="lang_select.html">SELECT</a> expression
does contain variables from the outer query, then the <a href="lang_select.html">SELECT</a> is reevaluated
every time it is needed.</p>

<p>When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the result of the left operand is any of
the values generated by the select.  The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>

<p>When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  If the SELECT yields
more than one result row, all rows after the first are ignored.  If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<h3>CAST expressions</h3>
<p>A CAST expression changes the <a href="datatype3.html">datatype</a> of the <expr> into the
type specified by &lt;type&gt;. 
&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a <a href="lang_createtable.html">CREATE TABLE</a> statement.</p>

<h3>Functions</h3>
<p>Both <a href="lang_corefunc.html">simple</a> and <a href="lang_aggfunc.html">aggregate</a> functions are supported.
(For presentation purposes, simple functions are further subdivided into
<a href="lang_corefunc.html">core functions</a> and <a href="lang_datefunc.html">date-time functions</a>.)
A simple function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>