Example Typenames From The(从上面可以看出,sqlite3只是从声明类型字符串中去查找它知道的声明类型,比如”XINT”将被赋予INTEGER近似因为这个字符串里面有”INT”,所以这里并不需要一个单独的正确的声明类型,而是只要声明类型字符串里面包含了sqlite所知道的声明类型即可) 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 > NULL3.2 比较操作数的近似(Affinity) Sqlite可能在执行一个比较之前会在INTEGER,REAL或TEXT之间转换比较值。是否在比较操作之前发生转换基于操作数的近似(类型)。 注意每一个表的列都有近似列,但表达式不一定有。l 一个”CAST(expr as type)”形式的表达式与用声明类型为”type”的列有相同的affinity l 其他的情况,一个表达式为NONE affinity 3.3 类型转换之前的比较 l 如果一个操作数有TEXT近似,另一个有没有近似,那么TEXT近似被应用到另一个操作数 l 其他的情况,不应用近似,两个操作数按本来的样子比较 表达式"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 respectivelyINSERT 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-- No affinity conversions occur. Right-hand side values all have-- storage class INTEGER which compare numerically with the INTEGER-- values on the left.SELECT d < 40, d < 60, d < 600 FROM t1;0|0|1-- No affinity conversions occur. INTEGER values on the left are-- always less than TEXT values on the right.SELECT d < '40', d < '60', d < '600' FROM t1;1|1|1
海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理:https://www.xlkjsw.com
原标题:Sqlite3中存储类型和数据类型结合文档解析。
关键词:sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们:
admin#shaoqun.com
(#换成@)。
|