datatype3.tcl

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

'a' in each of the comparisons required to evaluate the expression.
</P>

<P>Expressions of the type "a IN (SELECT b ....)" are handled by the three
rules enumerated above for binary comparisons (e.g. in a
similar manner to "a = b"). For example if 'b' is a column value
and 'a' is an expression, then the affinity of 'b' is applied to 'a'
before any comparisons take place.</P>

<P>SQLite treats the expression "a IN (x, y, z)" as equivalent to "a = +x OR
a = +y OR a = +z".  The values to the right of the IN operator (the "x", "y",
and "z" values in this example) are considered to be expressions, even if they
happen to be column values.  If the value of the left of the IN operator is
a column, then the affinity of that column is used.  If the value is an
expression then no conversions occur.
</P>

<h4>3.1 Comparison Example</h4>

<blockquote>
<PRE>
CREATE TABLE t1(
    a TEXT,
    b NUMERIC,
    c BLOB
);

-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');

-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
SELECT a &lt; 60, a &lt; 40 FROM t1;
1|0

-- Comparisons are numeric. No conversions are required.
SELECT b &lt; 60, b &lt; 600 FROM t1;
0|1

-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c &lt; 60, c &lt; 600 FROM t1;
0|0
</PRE>
</blockquote>
<h3>4. Operators</h3>

<P>All mathematical operators (which is to say, all operators other
than the concatenation operator &quot;||&quot;) apply NUMERIC
affinity to all operands prior to being carried out. If one or both
operands cannot be converted to NUMERIC then the result of the
operation is NULL.</P>

<P>For the concatenation operator, TEXT affinity is applied to both
operands. If either operand cannot be converted to TEXT (because it
is NULL or a BLOB) then the result of the concatenation is NULL.</P>

<h3>5. Sorting, Grouping and Compound SELECTs</h3>

<P>When values are sorted by an ORDER by clause, values with storage
class NULL come first, followed by INTEGER and REAL values
interspersed in numeric order, followed by TEXT values usually in
memcmp() order, and finally BLOB values in memcmp() order. No storage
class conversions occur before the sort.</P>

<P>When grouping values with the GROUP BY clause values with
different storage classes are considered distinct, except for INTEGER
and REAL values which are considered equal if they are numerically
equal. No affinities are applied to any values as the result of a
GROUP by clause.</P>

<P>The compound SELECT operators UNION,
INTERSECT and EXCEPT perform implicit comparisons between values.
Before these comparisons are performed an affinity may be applied to
each value. The same affinity, if any, is applied to all values that
may be returned in a single column of the compound SELECT result set.
The affinity applied is the affinity of the column returned by the
left most component SELECTs that has a column value (and not some
other kind of expression) in that position. If for a given compound
SELECT column none of the component SELECTs return a column value, no
affinity is applied to the values from that column before they are
compared.</P>

<h3>6. Other Affinity Modes</h3>

<P>The above sections describe the operation of the database engine
in 'normal' affinity mode. SQLite version 3 will feature two other affinity
modes, as follows:</P>
<UL>
	<LI><P><B>Strict affinity</B> mode. In this mode if a conversion
	between storage classes is ever required, the database engine
	returns an error and the current statement is rolled back.</P>

	<LI><P><B>No affinity</B> mode. In this mode no conversions between
	storage classes are ever performed. Comparisons between values of
	different storage classes (except for INTEGER and REAL) are always
	false.</P>
</UL>

<a name="collation"></a>
<h3>7. User-defined Collation Sequences</h3>

<p>
By default, when SQLite compares two text values, the result of the
comparison is determined using memcmp(), regardless of the encoding of the
string. SQLite v3 provides the ability for users to supply arbitrary
comparison functions, known as user-defined collation sequences, to be used
instead of memcmp().
</p>  
<p>
Aside from the default collation sequence BINARY, implemented using
memcmp(), SQLite features one extra built-in collation sequences 
intended for testing purposes, the NOCASE collation:
</p>  
<UL>
	<LI><b>BINARY</b> - Compares string data using memcmp(), regardless
                            of text encoding.</LI>
	<LI><b>NOCASE</b> - The same as binary, except the 26 upper case
			    characters used by the English language are
			    folded to their lower case equivalents before
                            the comparison is performed.  </UL>


<h4>7.1 Assigning Collation Sequences from SQL</h4>

<p>
Each column of each table has a default collation type. If a collation type
other than BINARY is required, a COLLATE clause is specified as part of the
<a href="lang_createtable.html">column definition</a> to define it. 
</p>  

<p>
Whenever two text values are compared by SQLite, a collation sequence is
used to determine the results of the comparison according to the following
rules. Sections 3 and 5 of this document describe the circumstances under
which such a comparison takes place.
</p>  

<p>
For binary comparison operators (=, <, >, <= and >=) if either operand is a
column, then the default collation type of the column determines the
collation sequence to use for the comparison. If both operands are columns,
then the collation type for the left operand determines the collation
sequence used. If neither operand is a column, then the BINARY collation
sequence is used.  For the purposes of this paragraph, a column name
preceded by one or more unary "+" operators is considered a column name.
</p>  

<p>
The expression "x BETWEEN y and z" is equivalent to "x &gt;= y AND x &lt;=
z". The expression "x IN (SELECT y ...)" is handled in the same way as the
expression "x = y" for the purposes of determining the collation sequence
to use. The collation sequence used for expressions of the form "x IN (y, z
...)" is the default collation type of x if x is a column, or BINARY
otherwise.
</p>  

<p>
An <a href="lang_select.html">ORDER BY</a> clause that is part of a SELECT
statement may be assigned a collation sequence to be used for the sort
operation explicitly. In this case the explicit collation sequence is
always used.  Otherwise, if the expression sorted by an ORDER BY clause is
a column, then the default collation type of the column is used to
determine sort order. If the expression is not a column, then the BINARY
collation sequence is used.
</p>  

<h4>7.2 Collation Sequences Example</h4>
<p>
The examples below identify the collation sequences that would be used to
determine the results of text comparisons that may be performed by various
SQL statements. Note that a text comparison may not be required, and no
collation sequence used, in the case of numeric, blob or NULL values.
</p>
<blockquote>
<PRE>
CREATE TABLE t1(
    a,                 -- default collation type BINARY
    b COLLATE BINARY,  -- default collation type BINARY
    c COLLATE REVERSE, -- default collation type REVERSE
    d COLLATE NOCASE   -- default collation type NOCASE
);

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = b) FROM t1;

-- Text comparison is performed using the NOCASE collation sequence.
SELECT (d = a) FROM t1;

-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = d) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;

-- Text comparison is performed using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;

-- Grouping is performed using the NOCASE collation sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;

-- Grouping is performed using the BINARY collation sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;

-- Sorting is performed using the REVERSE collation sequence.
SELECT * FROM t1 ORDER BY c;

-- Sorting is performed using the BINARY collation sequence.
SELECT * FROM t1 ORDER BY (c || '');

-- Sorting is performed using the NOCASE collation sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;

</PRE>
</blockquote>

}
footer $rcsid