-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|text

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
DELETE FROM t1;
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|real

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
DELETE FROM t1;
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
text|integer|integer|real|integer

-- BLOBs are always stored as BLOBs regardless of column affinity.
DELETE FROM t1;
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
blob|blob|blob|blob|blob

-- NULLs are also unaffected by affinity
DELETE FROM t1;
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);
SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;
null|null|null|null|null

　　这是文档中给的例子。。。

3.0 比较表达式

? ? ? ? Sqlite v3有一系列有用的比较操作符，包括 "=", "==", "<","<=", ">", ">=", "!=","<>", "IN", "NOT IN", "BETWEEN","IS", 和"IS NOT"

3.1 排序

比较操作的结果基于操作数的存储类型，根据下面的规则：

l? 存储类型为NULL的值被认为小于其他任何的值（包括另一个存储类型为NULL的值）

l? 一个INTEGER或REAL值小于任何TEXT或BLOB值。当一个INTEGER或REAL值与另外一个INTEGER或REAL值比较的话，就执行数值比较

l? TEXT值小于BLOB值。当两个TEXT值比较的时候，就根据序列的比较来决定结果

l? 当两个BLOB值比较的时候，使用memcmp()来决定结果

简单的说：BLOB > TEXT > INTEGER/REAL > NULL

3.2 比较操作数的近似（Affinity）

? ? ? ? Sqlite可能在执行一个比较之前会在INTEGER，REAL或TEXT之间转换比较值。是否在比较操作之前发生转换基于操作数的近似（类型）。

注意每一个表的列都有近似列，但表达式不一定有。
操作数近似（类型）由以下的规则决定：

3.3 类型转换之前的比较
? ? ? ? 只有在转换是无损、可逆转的时候“应用近似”才意味着将操作数转换到一个特定的存储类。近似在比较之前被应用到比较的操作数，遵循下面的规则（根据先后顺序）:?

表达式"aBETWEEN b AND c"表示两个单独的二值比较” a >= b AND a <= c”，即使在两个比较中不同的近似被应用到’a’。

3.4比较例子

CREATE TABLE t1(
? ? a TEXT,? ? ? -- text affinity
? ? b NUMERIC,? -- numeric affinity
? ? c BLOB,? ? ? -- no affinity
? ? d? ? ? ? ? ? -- no affinity
);

-- Values will be stored as TEXT, INTEGER, TEXT, and INTEGER respectively
INSERT INTO t1 VALUES('500', '500', '500', 500);
SELECT typeof(a), typeof(b), typeof(c), typeof(d) FROM t1;
text|integer|text|integer

-- Because column "a" has text affinity, numeric values on the
-- right-hand side of the comparisons are converted to text before
-- the comparison occurs.
SELECT a < 40,? a < 60,? a < 600 FROM t1;
0|1|1

-- Text affinity is applied to the right-hand operands but since
-- they are already TEXT this is a no-op; no conversions occur.
SELECT a < '40', a < '60', a < '600' FROM t1;
0|1|1

-- Column "b" has numeric affinity and so numeric affinity is applied
-- to the operands on the right.? Since the operands are already numeric,
-- the application of affinity is a no-op; no conversions occur.? All
-- values are compared numerically.
SELECT b < 40,? b < 60,? b < 600 FROM t1;
0|0|1

-- Numeric affinity is applied to operands on the right, converting them
-- from text to integers.? Then a numeric comparison occurs.
SELECT b < '40', b < '60', b < '600' FROM t1;
0|0|1

-- No affinity conversions occur.? Right-hand side values all have
-- storage class INTEGER which are always less than the TEXT values
-- on the left.
SELECT c < 40,? c < 60,? c < 600 FROM t1;
0|0|0

-- No affinity conversions occur.? Values are compared as TEXT.
SELECT c < '40', c < '60', c < '600' FROM t1;
0|1|1