SQLite--函数介绍

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数，且所有函数都是大小写不敏感，这意味着我们可以使用这些函数的小写形式或大写形式或混合形式。

我已经创建了一个数据表 PERSON ,现在表中的数据为：

id          name        age         dept_id     salary----------  ----------  ----------  ----------  ----------1           Jack        21          2           2800.02           Mary        20          1           3600.03           Mike        22          3           4400.04           Amy         19          1           5200.05           Alen        18          2           6000.06           Ren         21          3           6800.07           Lucy        20          1           7600.08           John        17          0           8400.09           Mini        16          1           9200.0

聚合函数

对于所有聚合函数而言，distinct关键字可以作为函数参数字段的前置属性，以便在进行计算时忽略到所有重复的字段值，如count(distinct x)。

AVG 函数

该函数返回在同一组内参数字段的平均值。对于不能转换为数字值的String和BLOB类型的字段值，如’HELLO’，SQLite会将其视为0。avg函数的结果总是浮点型，唯一的例外是所有的字段值均为NULL，那样该函数的结果也为NULL。

sqlite> select avg(salary) from person;avg(salary)-----------6000.0

COUNT 函数

count(x)函数返回在同一组内，x字段中值不等于NULL的行数。count(*)函数返回在同一组内的数据行数。

sqlite> select count(*) from person;count(*)----------9sqlite> select count(distinct age) from person;count(distinct age)-------------------7

MAX 函数

max(x) 该函数返回同一组内的x字段的最大值，如果该字段的所有值均为NULL，该函数也返回NULL。

sqlite> select max(salary) from person;max(salary)-----------9200.0

MIN 函数

min(x) 该函数返回同一组内的x字段的最小值，如果该字段的所有值均为NULL，该函数也返回NULL。

sqlite> select min(age) from person;min(age)----------16

SUM 函数

该函数返回同一组内的x字段值的总和，如果字段值均为NULL，该函数也返回NULL。如果所有的x字段值均为整型或者NULL，该函数返回整型值，否则就 返回浮点型数值。最后需要指出的是，如果所有的数据值均为整型，一旦结果超过上限时将会抛出”integer overflow”的异常。

sqlite> select sum(salary) from person;sum(salary)-----------54000.0

TOTAL 函数

该函数不属于标准SQL，其功能和sum基本相同，只是计算结果比sum更为合理。比如当所有字段值均为NULL时，和sum不同的是，该函数返回0.0。再有就是该函数始终返回浮点型数值。该函数始终都不会抛出异常。

sqlite> select total(salary) from person;total(salary)-------------54000.0

核心函数

以下函数均为SQLite缺省提供的内置函数。

ABS 函数

该函数返回数值参数X的绝对值，如果X为NULL，则返回NULL，如果X为不能转换成数值的字符串，则返回0，如果X值超出Integer的上限，则抛出”Integer Overflow”的异常。

sqlite> select abs(5), abs(-15), abs(NULL), abs(0), abs('ABC');abs(5)      abs(-15)    abs(NULL)   abs(0)      abs('ABC')----------  ----------  ----------  ----------  ----------5           15                      0           0.0

CHANGES 函数

该函数返回最近执行的INSERT、UPDATE和DELETE语句所影响的数据行数。

sqlite> update person set salary = 2000 * id;sqlite> select changes();changes()----------9

CHAR 函数

char(X1,X2,…,XN) 返回X1,X2,…,XN等UNICODE码对应的字符。

sqlite> select char(64,65,66,67,68,69);@ABCDE

COALESCE 函数

coalesce(X,Y,…) 返回函数参数中第一个非NULL的参数，如果参数都是NULL，则返回NULL。该函数至少2个参数。

因为我创的 PERSON 表都是 NOT NULL 的，所以我们新创建一个表。

// 这样创建能把数据复制，但是会没有约束，是我们需要的。sqlite> create table new_person as select id, name, age from person; sqlite> update new_person set age = null where id % 2 = 0;sqlite> select * from new_person;id          name        age         dept_id----------  ----------  ----------  ----------1           Jack        21          22           Mary                    13           Mike        22          34           Amy                     15           Alen        18          26           Ren                     37           Lucy        20          18           John                    09           Mini        16          1

sqlite> select coalesce(age, name) from new_person;coalesce(age, name)-------------------21Mary22Amy18Ren20John16

GLOB 函数

glob(X,Y) 用于实现SQLite的 “Y GLOB X”语法。可使用 sqlite3_create_function() 重载该函数从而改变GLOB运算符的功能。

sqlite> select glob('*a*','jack');1

GROUP_CONCAT 函数

group_concat(x[,y]) 该函数返回一个字符串，该字符串将会连接所有非NULL的x值。该函数的y参数将作为每个x值之间的分隔符，如果在调用时忽略该参数，在连接时将使用缺省分隔符”,”。再有就是各个字符串之间的连接顺序是不确定的。　　

sqlite> select group_concat(name) from person;group_concat(name)------------------------------------------Jack,Mary,Mike,Amy,Alen,Ren,Lucy,John,Minisqlite> select group_concat(name, '-') from person;group_concat(name, '-')------------------------------------------Jack-Mary-Mike-Amy-Alen-Ren-Lucy-John-Mini

HEX 函数

参数以 BLOB 对待。结果是 BLOB 内容的 16 进制表示。

INSTR 函数

instr（x，y）函数找到字符串X第一次出现的字符Y的位置，没有就返回0。

sqlite> select instr('sdfpdsfsdf', 'f');3

IFNULL 函数

ifnull(X,Y) 该函数等同于两个参数的coalesce()函数，即返回第一个不为NULL的函数参数，如果两个均为NULL，则返回NULL。

LAST_INSERT_ROWID 函数

SQLiteAPI 函数sqlite3_last_insert_rowid()可以取得最后一条插入的记录的rowid。但sqlite3_last_insert_rowid()是基于当前进程的。也就是说，sqlite3_last_insert_rowid()取到的是当前进程最后一次插入记录的rowid。对于不是当前进程插入的记录，sqlite3_last_insert_rowi()均返回0。

sqlite> select last_insert_rowid();last_insert_rowid()-------------------7

退出数据库再次进入后：

sqlite> select last_insert_rowid();0

LIKE 函数

like(x, y) 函数是判断字符串是否匹配，第一个为表达式，第二个参数用它来匹配。匹配成功返回为1，否则为0。

sqlite> select like('%2%', age) from person;000011111

LENGTH 函数

如果参数X为字符串，则返回字符的数量，如果为数值，则返回该参数的字符串表示形式的长度，如果为NULL，则返回NULL。

sqlite> select name, length(name) from person;name        length(name)----------  ------------Jack        4Mary        4Mike        4Amy         3Alen        4Ren         3Lucy        4John        4Mini        4

LOWER 函数

返回函数参数X的小写形式，缺省情况下，该函数只能应用于ASCII字符。

sqlite> select lower(name) from person;lower(name)-----------jackmarymikeamyalenrenlucyjohnmini

LTRIM 函数

ltrim(X[,Y]) 如果没有可选参数Y，该函数将移除参数X左侧的所有空格符。如果有参数Y，则移除X左侧的任意在Y中出现的字符。最后返回移除后的字符串。

sqlite> select ltrim(name, 'J') from person;ltrim(name, 'J')----------------ackMaryMikeAmyAlenRenLucyohnMini

sqlite> select ltrim('  I like Computer');ltrim('  I like Computer')--------------------------I like Computer

MAX 函数

max(X,Y,…) 返回函数参数中的最大值，如果有任何一个参数为NULL，则返回NULL。

sqlite> select max(2, 3, 1);max(2, 3, 1)------------3

MIN 函数

min(X,Y,…) 返回函数参数中的最小值，如果有任何一个参数为NULL，则返回NULL。

NULLIF 函数

nullif(X,Y) 如果函数参数相同，返回NULL，否则返回第一个参数。　

sqlite> select nullif(3, 4);nullif(3, 4)------------3

QUOTE 函数

quote(X) 返回参数的适于插入其它SQL语句中的值。字符串会被添加单引号，在内部的引号前会加入逃逸符号。 BLOB被编码为十六进制文本。当前的VACUUM使用这一函数实现。在使用触发器实现撤销/重做功能时这一函数也很有用。

sqlite> select quote(name) from person;'Jack''Mary''Mike''Amy'

sqlite> select quote(age) from person;16171819

RANDOMBLOB 函数

返回一个 N 字节长的包含伪随机字节的 BLOG。 N 是正整数。

sqlite> select randomblob(16);yZ??{?zO?6??

RANDOM 函数

SQLite RANDOM 函数返回一个介于 -2^63 和 +2^63 - 1 之间的伪随机整数。

sqlite> select random() as random;random-------------------2583308869213998071

REPLACE 函数

replace(X,Y,Z) 将字符串类型的函数参数X中所有子字符串Y替换为字符串Z，最后返回替换后的字符串，源字符串X保持不变。

sqlite> select replace(name, 'k', 'M') from new_person;replace(name, 'k', 'M')-----------------------JacMMaryMiMeAmyAlenRenLucyJohnMini

ROUND 函数

round(X[,Y]) 返回数值参数X被四舍五入到Y刻度的值，如果参数Y不存在，缺省参数值为0。

sqlite> select round(4.5);round(4.5)----------5.0sqlite> select round(4.55, 1);round(4.55, 1)--------------4.5

这里需要注意的是SQLite 使用二进制算术，在二进制中， 无法用有限的二进制位数表示 4.55 。使用 64-bit IEEE 浮点（SQLite就是使用这个）最接近 4.55 的二进制表示是 4.5499…，所以会舍去。

RTRIM 函数

rtrim(X[,Y]) 如果没有可选参数Y，该函数将移除参数X右侧的所有空格符。如果有参数Y，则移除X右侧的任意在Y中出现的字符。最后返回移除后的字符串。

sqlite> select rtrim(name, 'ken') from person;rtrim(name, 'ken')------------------JacMaryMiAmyAlRLucyJohMini

SUBSTR 函数

substr(X,Y[,Z]) 返回函数参数X的子字符串，从第Y位开始(X中的第一个字符位置为1)截取Z长度的字符，如果忽略Z参数，则取第Y个字符后面的所有字符。如果Z的值为负数，则从第Y位开始，向左截取abs(Z)个字符。如果Y值为负数，则从X字符串的尾部开始计数到第abs(Y)的位置开始。　

sqlite> select substr(name, 2, 2) from person;substr(name, 2, 2)------------------acarikmyleenucohin

UPPER 函数

SQLite UPPER 函数把字符串转换为大写字母。

sqlite> select upper(name) from person;upper(name)-----------JACKMARYMIKEAMYALENRENLUCYJOHNMINI

TOTAL_CHANGES 函数

total_changes() 该函数返回自从该连接被打开时起，INSERT、UPDATE和DELETE语句总共影响的行数。

sqlite> select total_changes();total_changes()---------------49

TRIM 函数

trim(x[,y]) 如果没有可选参数Y，该函数将移除参数X两侧的所有空格符。如果有参数Y，则移除X两侧的任意在Y中出现的字符。最后返回移除后的字符串。

sqlite> select trim(name, 'Jk') from person;trim(name, 'Jk')----------------acMaryMikeAmyAlenRenLucyohnMini

TYPEOF 函数

typeof(X) 返回函数参数数据类型的字符串表示形式，如”Integer、text、real、null”等。

sqlite> select typeof(1);typeof(1)----------integer

sqlite_version 函数

SQLite sqlite_version 函数返回 SQLite 库的版本。

sqlite> select sqlite_version() as version;version----------3.18.0

ZEROBLOB 函数

返回一个 N 字节长、全部由 0x00 组成的 BLOB。 SQLite 或以很有效的组织这些 zeroblob。它可以被用于为以后的使用预留空间。以后可以使用 incremental BLOB I/O 来写入 BLOB 数据。

日期和时间函数

关于日期和时间的函数，我在SQLite–日期 & 时间这篇博客中有详细的介绍，有兴趣的可以去看，这里我就不提啦。

结束语：本文仅用来学习记录，参考查阅。