📄 lang_expr.html

📁 sqlite3源码,适合作为嵌入式（embedded）
💻 HTML
字号:
<!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="index.html"><img class="logo" src="images/SQLite.gif" alt="SQLite Logo" border="0"></a><div><!-- IE hack to prevent disappearing logo--></div><div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div><table width=100% style="clear:both"><tr><td>  <div class="se"><div class="sw"><div class="ne"><div class="nw">  <div class="toolbar">    <a href="about.html">About</a>    <a href="sitemap.html">Sitemap</a>    <a href="docs.html">Documentation</a>    <a href="download.html">Download</a>    <a href="copyright.html">License</a>    <a href="news.html">News</a>    <a href="http://www.sqlite.org/cvstrac/index">Developers</a>    <a href="support.html">Support</a>  </div></div></div></div></div></td></tr></table>  <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 src="images/syntax/expr.gif"></img> </blockquote><h4><a href="syntaxdiagrams.html#literal-value">literal-value:</a></h4><blockquote> <img src="images/syntax/literal-value.gif"></img> </blockquote><h4><a href="syntaxdiagrams.html#signed-number">signed-number:</a></h4><blockquote> <img src="images/syntax/signed-number.gif"></img> </blockquote><h4><a href="syntaxdiagrams.html#raise-function">raise-function:</a></h4><blockquote> <img src="images/syntax/raise-function.gif"></img> </blockquote><p>This section is different from the others.  Most other sections ofthis document talks about a particular SQL command.  This section doesnot talk about a standalone command but about "expressions" which are subcomponents of most other commands.</p><a name="binaryops"></a><p>SQLite understands the following binary operators, in order fromhighest to lowest precedence:</p><blockquote><pre><font color="#2c2cf0"><big>||*    /    %+    -&lt;&lt;   &gt;&gt;   &amp;    |&lt;    &lt;=   &gt;    &gt;==    ==   !=   &lt;&gt;   </big>IN  LIKE  GLOB  MATCH  REGEXPAND   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 postfixoperator.  The COLLATE operator has the highest precedence.It always binds more tightly than any prefix unary operator orany binary operator.</p><p>The unary operator <font color="#2c2cf0"><big>+</big></font> is a no-op.  It can be appliedto strings, numbers, or blobs and it always gives as its result thevalue of the operand.</p><p>Note that there are two variations of the equals and not equalsoperators.  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 togetherthe 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, exceptfor the <font color="#2c2cf0"><big>||</big></font> concatenation operator which gives a stringresult.</p><a name="literal_value"></a><p>A literal value is an integer number or a floating point number.Scientific notation is supported.  The "." character is always usedas the decimal point even if the locale setting specifies "," forthis role - the use of "," for the decimal point would result insyntactic ambiguity.  A string constant is formed by enclosing thestring in single quotes (').  A single quote within the string canbe encoded by putting two single quotes in a row - as in Pascal.C-style escapes using the backslash character are not supported becausethey are not standard SQL.BLOB literals are string literals containing hexadecimal data andpreceded 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><p>A parameter specifies a placeholder in the expression for a literalvalue that is filled in at runtime using the<a href="c3ref/bind_blob.html">sqlite3_bind()</a> 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 theNNN-th parameter.  NNN must be between 1 and <a href="limits.html#max_variable_number">SQLITE_MAX_VARIABLE_NUMBER</a>.</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 forthe 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 namedparameter 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 namedparameter with the name AAAA.  The identifier name in this case can includeone or more occurances of "::" and a suffix enclosed in "(...)" containingany text at all.  This syntax is the form of a variable name in the Tclprogramming language.</td></tr></table></blockquote><p>Parameters that are not assigned values using<a href="c3ref/bind_blob.html">sqlite3_bind()</a> are treatedas NULL.</p><a name="like"></a><p>The LIKE operator does a pattern matching comparison. The operandto the right contains the pattern, the left hand operand contains thestring to match against the pattern.A percent symbol <font color="#2c2cf0"><big>%</big></font> in the pattern matches anysequence of zero or more characters in the string.  An underscore<font color="#2c2cf0"><big>_</big></font> in the pattern matches any single character in thestring.  Any other character matches itself or it's lower/upper caseequivalent (i.e. case-insensitive matching).  (A bug: SQLite onlyunderstands upper/lower case for 7-bit Latin characters.  Hence theLIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8characters.  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 expressionfollowing the ESCAPE keyword must evaluate to a string consisting ofa single character. This character may be used in the LIKE patternto include literal percent or underscore characters. The escapecharacter followed by a percent symbol, underscore or itself matches aliteral percent symbol, underscore or escape character in the string,respectively. The infix LIKE operator is implemented by calling theapplication-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>The LIKE operator is not case sensitive and will match upper casecharacters on one side against lower case characters on the other.  (A bug: SQLite only understands upper/lower case for 7-bit Latincharacters.  Hence the LIKE operator is case sensitive for 8-bitiso8859 characters or UTF-8 characters.  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>The infix LIKEoperator is implemented by calling the user function <a href="#likeFunc">like(<i>X</i>,<i>Y</i>)</a>.  If an ESCAPE clause is present, it addsa third parameter to the function call. If the functionality of LIKE can beoverridden by defining an alternative implementation of thelike() SQL function.</p></p><a name="glob"></a><p>The GLOB operator is similar to LIKE but uses the Unixfile globbing syntax for its wildcards.  Also, GLOB is casesensitive, unlike LIKE.  Both GLOB and LIKE may be preceded bythe 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 overridingthat 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 defaultand so use of the REGEXP operator will normally result in anerror message.  If a user-defined function named "regexp"is added at run-time, that function will be called in orderto 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 implementationraises and exception and is not really useful for anything.But extensions can override the match() function with morehelpful logic.</p><a name="between"></a><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 precedenceas operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.<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 theunique 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.  Row keysact like read-only columns.  A row key can be used anywhere a regularcolumn can be used, except that you cannot change the valueof a row key in an <a href="lang_update.html">UPDATE</a> or <a href="lang_insert.html">INSERT</a> statement."SELECT * ..." does not return the row key.</p><p><a href="lang_select.html">SELECT</a> statements can appear in expressions as either theright-hand operand of the IN operator, as a scalar quantity, oras 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 itsresult.  Compound SELECTs (connected with keywords like UNION orEXCEPT) are allowed.With the EXISTS operator, the columns in the result set of the <a href="lang_select.html">SELECT</a> areignored and the expression returns TRUE if one or more rows existand FALSE if the result set is empty.If no terms in the <a href="lang_select.html">SELECT</a> expression refer to value in the containingquery, then the expression is evaluated once prior to any otherprocessing and the result is reused as necessary.  If the <a href="lang_select.html">SELECT</a> expressiondoes contain variables from the outer query, then the <a href="lang_select.html">SELECT</a> is reevaluatedevery time it is needed.</p><p>When a SELECT is the right operand of the IN operator, the INoperator returns TRUE if the result of the left operand is any ofthe values generated by the select.  The IN operator may be precededby the NOT keyword to invert the sense of the test.</p><p>When a SELECT appears within an expression but is not the rightoperand of an IN operator, then the first row of the result of theSELECT becomes the value used in the expression.  If the SELECT yieldsmore than one result row, all rows after the first are ignored.  Ifthe SELECT yields no rows, then the value of the SELECT is NULL.</p><p>A CAST expression changes the <a href="datatype3.html">datatype</a> of the <expr> into thetype specified by &lt;type&gt;. &lt;type&gt; can be any non-empty type name that is validfor the type in a column definition of a <a href="lang_createtable.html">CREATE TABLE</a> statement.</p><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 returna result immediately based on their inputs.  Aggregate functionsmay only be used in a SELECT statement.  Aggregate functions computetheir result across all rows of the result set.</p><hr><small><i>This page last modified 2009/01/02 16:47:13 UTC</i></small></div></body></html>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -