SQLite 通用函数

2019-09-09 5 0

SQLite教程以最新的SQLite版本version 3.33.0（2020-08-20）为基础，带你学习和了解最新的SQLite语法及使用。
本教程类含有大量的测试用例，都是本人亲测，简单好用，是你学习和参考的好帮手。
加入字节流官方QQ群：952873936或联系站长进行技术交流。

SQLite提供了五种类别的函数：

核心(core)通用函数
时间及日期( Date & Time )函数
统计（ aggregate ）函数
视窗（WINDOW）函数
JSON函数

SQLite所有函数都是大小写不敏感.。

abs(X)

abs（X）函数返回数值参数X的绝对值。

如果X为NULL，abs（X）返回NULL。
如果X是无法转换为数值的字符串或blob，则Abs（X）返回0.0。
如果X是整数-9223372036854775808，那么abs（X）抛出一个整数溢出错误，因为没有等效的64位双补码值。

9223372036854775808=0x8000000000000000,所以小于等于-9223372036854775808需要65方可表示。SQLite整数只支持到64位。

sqlite> .mode line

sqlite> select abs(NULL);
abs(NULL) =

sqlite> select abs(-5);
abs(-5) = 5

sqlite> select abs("www.bytekits.com");
abs("www.bytekits.com") = 0.0

sqlite> select abs(-9223372036854775808);
Error: integer overflow
sqlite>

changes()

changes()函数返回最近完成的INSERT、DELETE或UPDATE语句更改、插入或删除的数据库行数（不包括低级触发器中的语句除外）。

sqlite> select changes();
changes() = 0

sqlite> delete from t_web where id=4;

sqlite> select changes();
changes() = 1

char(X1,X2,…,XN)

char（X1，X2，…，XN）函数返回一个由字符组成的字符串，这些字符分别具有整数X1到XN的unicode码位值。

sqlite> select char(65,66,67);
char(65,66,67) = ABC

coalesce(X,Y,…)

coalesce(X,Y,…)返回第一个非空参数的副本；如果所有参数都为空，则返回NULL。Coalesce（）必须至少有2个参数。

sqlite> select coalesce(null,123);
coalesce(null,123) = 123
sqlite> select coalesce(null,null);
coalesce(null,null) =

glob(X,Y)

glob（X，Y）函数相当于表达式“y glob X”，用来匹配通配符指定模式的文本值。请注意，在glob（）函数中，X和Y参数相对于infix glob运算符是相反的。Y是字符串，X是匹配模式。

如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的。

星号（*）,代表零个、一个或多个数字或字符
问号（?）代表一个单一的数字或字符。

这些符号可以被组合使用

sqlite> select glob("www*","www.bytekits.com");
glob("www*","www.bytekits.com") = 1

sqlite> select glob("www?","www.bytekits.com");
glob("www?","www.bytekits.com") = 0

hex(X)

用于将十六进制blob数据转换成大写十六进制字符串。

sqlite> select hex('www.bytekits.com');
hex('www.bytekits.com') = 7777772E627974656B6974732E636F6D

w的十六进制为:0x77

ifnull()

ifnull返回其第一个非NULL参数的副本；如果两个参数都为NULL，则返回NULL。Ifnull（）必须有2个参数。ifnull（）函数与coalesce（）等效，有两个参数。

sqlite> select ifnull(null,'www.bytekits.com');
ifnull(null,'www.bytekits.com') = www.bytekits.com

sqlite> select ifnull(null,null);
ifnull(null,null) =

iif(X,Y,Z)

如果X为真，iif（X，Y，Z）函数返回值Y，否则返回Z。iif（X，Y，Z）函数在逻辑上等价于CASE表达式“CASE WHEN X THEN Y ELSE Z END”并生成相同的字节码。
其实是条件表达式：X?Y:Z

sqlite> select iif(1,2,3);
iif(1,2,3) = 2

sqlite> select iif(0,2,3);
iif(0,2,3) = 3

0为FALSE.

也可以组合表达：

sqlite> select iif(1>2,2,3);
iif(1>2,2,3) = 3

instr(X,Y)

instr（X，Y）函数查找字符串X中第一个出现的字符串Y，并返回前面的字符数加上1，如果X中找不到Y，则返回0。

如果X和Y都是blob，instr（X，Y）返回的字节数比第一次出现Y之前的字节数多1个，如果Y不出现在X中的任何地方，则返回0。
如果instr（X，Y）的参数X和Y都是非NULL且不是blob，则这两个参数都被解释为字符串。
如果instr（X，Y）中的X或Y为NULL，则结果为NULL。

sqlite> select instr('www.bytekits.com','byte');
instr('www.bytekits.com','byte') = 5

last_insert_rowid()

返回调用函数的数据库连接中最后一行插入的rowid。

sqlite> select last_insert_rowid();
last_insert_rowid() = 0

sqlite> insert into t_web values(4,'www.taobao.com');
sqlite> select last_insert_rowid();
last_insert_rowid() = 4

sqlite> select * from t_web;
id  name
--  ----------------
1   www.bytekits.com
2   wwww.google.com
3   www.baidu.com
4   www.taobao.com

length(X)

length（X）函数通常返回字符串X中的字符总数。

对于字符串值X，length（X）函数返回X中第一个NUL字符之前的字符数（而不是字节数）。
对于blob值X，length（X）返回blob中的字节数。
如果X为NULL，则长度（X）为NULL。

sqlite> .mode line
sqlite> select length('www.bytekits.com');
length('www.bytekits.com') = 16

sqlite> select length(123);
length(123) = 3

sqlite> select length(123.4);
length(123.4) = 5

如果X是数字，那么length（X）返回X的字符串表示的长度。

like(X,Y),like(X,Y,Z)

like（）函数用于实现“Y like X[ESCAPE Z]”表达式。如果存在可选的ESCAPE子句，则使用三个参数调用like（）函数。否则，它只能用两个参数调用。这里导出的是2个参数。

请注意，在like（）函数中，X和Y参数相对于中缀运算符是相反的。X是匹配模式，Y是匹配该模式的字符串。

sqlite> select like('%byte%','www.bytekits.com');
like('%byte%','www.bytekits.com') = 1

likely(X)，likelihood(X,Y)

likely(x)函数返回x原值，其仅用于CPU优化，其函数本身不消耗CPU时间片。

load_extension(X)，load_extension(X,Y)

lower(X)

lower(x)返加字符串小写

sqlite> select lower('WWW.BYTEKITS.COM');
lower('WWW.BYTEKITS.COM') = www.bytekits.com

ltrim(X)，ltrim(X,Y)

ltrim（X，Y）函数返回一个字符串，该字符串是通过从X的左侧删除Y中出现的所有字符而形成的。如果省略Y参数，ltrim（X）将从X的左侧删除空格。

sqlite> select ltrim('  www.bytekits.com');
ltrim('  www.bytekits.com') = www.bytekits.com

sqlite> select ltrim('www.bytekits.com','w');
ltrim('www.bytekits.com','w') = .bytekits.com

max(X,Y,…)

max(X,Y,…)返回具有最大值的参数，如果任何参数为空，则返回NULL。
multi-argument max（）函数从左到右搜索定义排序函数的参数，并将该排序函数用于所有字符串比较。如果max（）的参数都没有定义排序函数，则使用二进制排序函数。请注意，当max（）有2个或多个参数时，它是一个简单的函数，但如果只给定一个参数，则它将作为聚合函数运行。

sqlite> select max(1,3,4,5,6,7,8,8,9);
max(1,3,4,5,6,7,8,8,9) = 9

min(X,Y,…)

返回具有最小值的参数。multi-argument min（）函数从左到右搜索定义排序函数并将该排序函数用于所有字符串比较的参数。如果min（）的参数都没有定义排序函数，则使用二进制排序函数。请注意，当min（）有2个或多个参数时，它是一个简单的函数，但如果只给定一个参数，则它将作为聚合函数运行。

sqlite> select min(1,3,4,5,6,7,8,8,9);
min(1,3,4,5,6,7,8,8,9) = 1

nullif(X,Y)

如果参数不同，nullif（X，Y）函数返回第一个参数；如果参数相同，则返回NULL。nullif（X，Y）函数从左到右搜索其参数，以查找定义排序函数并将该排序函数用于所有字符串比较的参数。如果nullif（）的参数都没有定义排序函数，则使用二进制。

sqlite> select nullif(1,1);
nullif(1,1) =
sqlite> select nullif(3,5);
nullif(3,5) = 3

printf(FORMAT,…)

printf（FORMAT，…）SQL函数的工作方式类似于标准C库中的sqlite3_mprintf（）C语言函数和printf（）函数。指定如何使用第一个字符串参数的格式从字符串获取后续参数。如果格式参数丢失或为NULL，则结果为NULL。%n格式被静默忽略，不使用参数。%p格式是%X的别名。%z格式可以与%s互换。如果参数列表中的参数太少，则假定缺少的参数具有空值，对于数字格式，该值将转换为0或0.0，对于%s，则转换为空字符串。

sqlite> select printf("%s hello world!,I am %d yeas old","www.bytekits.com",3);
printf("%s hello world!,I am %d yeas old","www.bytekits.com",3) = www.bytekits.com hello world!,I am 3 yeas old

quote(X)

quote（X）函数返回SQL文本，该文本是适合包含在SQL语句中的参数值。字符串由单引号包围，根据需要在内部引号上加上转义符。blob被编码为十六进制文本。在SQL中，嵌入NUL字符的字符串不能表示为字符串文本，因此返回的字符串文本在第一个NUL之前被截断。

sqlite> select quote("bytekits'world");
quote("bytekits'world") = 'bytekits''world'

random()

random()返回一个介于-9223372036854775808和+9223372036854775807之间的伪随机整数。如要获取指定区间的数，可以用模实现。

sqlite> select random();
random() = 6025798862244869879
sqlite> select random()%10;
random()%10 = 3
sqlite> select random()%10;
random()%10 = -2

randomblob(N)

返回一个包含伪随机字节的N字节blob。如果N小于1，则返回一个1字节的随机blob。

提示：应用程序可以使用此函数与hex（）和/或lower（）一起生成全局唯一标识符，如下所示：
-hex(randomblob(16))
-lower(hex(randomblob(16)))

sqlite> select hex(randomblob(16));
hex(randomblob(16)) = CB059A7BA0CC6869E02D902621AED5AD
sqlite> select hex(randomblob(16));
hex(randomblob(16)) = 2B0B94EF74BF30FE2811C0B7F5318881

sqlite> select lower(hex(randomblob(16)));
lower(hex(randomblob(16))) = 9e2dafeebdfe0deffdd46d419beb9055
sqlite> select lower(hex(randomblob(16)));
lower(hex(randomblob(16))) = b28138025fe84255a1dcb41ee517fc3d

replace(X,Y,Z)

replace（X，Y，Z）函数返回一个字符串，该字符串由字符串Z替换字符串X中出现的字符串Y组成。二进制排序序列用于比较。如果Y是空字符串，则返回X不变。如果Z最初不是字符串，则在处理之前将其转换为UTF-8字符串。

sqlite> select replace('www.bytekits.com','b','d');
replace('www.bytekits.com','b','d') = www.dytekits.com

rtrim(X),rtrim(X,Y)

rtrim（X，Y）函数返回一个字符串，该字符串是从X的右侧删除Y中出现的所有字符而形成的。如果省略Y参数，rtrim（X）将从X的右侧删除空格。

sqlite> select rtrim('www.bytekits.com  ');
rtrim('www.bytekits.com  ') = www.bytekits.com
sqlite> select rtrim('www.bytekits.comaaa','a');
rtrim('www.bytekits.comaaa','a') = www.bytekits.com

soundex(X)

sqlite_compileoption_get(N)

sqlite_compileoption_used(X)

sqlite_offset(X)

sqlite_source_id()

返回一个字符串，该字符串标识用于构建sqlite库的源代码的特定版本。sqlite_source_id（）返回的字符串是签入源代码的日期和时间，后跟该签入的SHA1哈希。此函数是sqlite3_sourceid（）C接口的SQL包装。

sqlite> select sqlite_source_id();
sqlite_source_id() = 2020-08-14 13:23:32 fca8dc8b578f215a969cd899336378966156154710873e68b3d9ac5881b0ff3f

sqlite_version()

返回正在运行的sqlite库的版本字符串。此函数是sqlite3_libversion（）C接口的SQL包装。

sqlite> select sqlite_version();
sqlite_version() = 3.33.0

substr(X,Y,Z),substr(X,Y)

substr（X，Y，Z）函数返回输入字符串X的子字符串，该子字符串以第Y个字符开头，长度为Z个字符。如果省略Z，则substr（X，Y）返回字符串X末尾以Y-th开头的所有字符。X最左边的字符是数字1。如果Y是负数，则子串的第一个字符是从右数而不是从左数。如果Z为负，则返回第Y个字符之前的abs（Z）字符。如果X是一个字符串，那么字符索引指的是实际的UTF-8字符。如果X是BLOB，则索引引用字节。

sqlite> select substr('www.bytekits.com',5);
substr('www.bytekits.com',5) = bytekits.com

sqlite> select substr('www.bytekits.com',-12,4);
substr('www.bytekits.com',-12,4) = byte

sqlite> select substr('www.bytekits.com',5,4);
substr('www.bytekits.com',5,4) = byte

total_changes()

自打开当前数据库连接以来，由INSERT、UPDATE或DELETE语句引起的行更改数。

sqlite> select total_changes();
total_changes() = 2

trim(X),trim(X,Y)

trim（X，Y）函数返回一个字符串，该字符串是通过从X的两端删除Y中出现的所有字符而形成的。如果省略Y参数，trim（X）将从X的两端删除空格。

sqlite> select trim('  www.bytekits.com  ');
trim('  www.bytekits.com  ') = www.bytekits.com

sqlite> select trim('11www.bytekits.com11',1);
trim('11www.bytekits.com11',1) = www.bytekits.com

typeof(X)

函数返回一个字符串，该字符串指示表达式X的数据类型：“null”、“integer”、“real”、“text”或“blob”。

sqlite> select typeof(1);
typeof(1) = integer
sqlite> select typeof(1.1);
typeof(1.1) = real
sqlite> select typeof('www.bytekits.com');
typeof('www.bytekits.com') = text

unicode(X)

unicode（X）函数返回与字符串X的第一个字符相对应的数字unicode码位。如果unicode（X）的参数不是字符串，则结果未定义。

sqlite> select unicode('a');
unicode('a') = 97

unlikely(X)

返回X.

upper(X)

upper（X）函数返回一个输入字符串X的副本，其中所有小写的ASCII字符都被转换为大写的等价字符。

sqlite> select upper('www.bytekits.com');
upper('www.bytekits.com') = WWW.BYTEKITS.COM

zeroblob(N)

zeroblob（N）函数返回由N个字节组成的BLOB，字节数为0x00。SQLite非常高效地管理这些零块。ZeroBoLB可以用来为BLB保留空间，稍后使用增量BLB I/O来编写该SQL函数。

sqlite> select hex(zeroblob(5));
hex(zeroblob(5)) = 0000000000

字节流是站长多年来的工作经验和技术总结，和站长一起学习，每天都有进步。
通俗易懂，深入浅出。
文章不深奥，不需要钻研，不烧脑细胞，人人都可以学习,在公交、在地铁、在厕所都可以阅读，随时随地涨姿势。