SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数,且所有函数都是大小写不敏感,这意味着我们可以使用这些函数的小写形式或大写形式或混合形式。
我已经创建了一个数据表 PERSON ,现在表中的数据为:
id name age dept_id salary
---------- ---------- ---------- ---------- ----------
1 Jack 21 2 2800.0
2 Mary 20 1 3600.0
3 Mike 22 3 4400.0
4 Amy 19 1 5200.0
5 Alen 18 2 6000.0
6 Ren 21 3 6800.0
7 Lucy 20 1 7600.0
8 John 17 0 8400.0
9 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(*)
----------
9
sqlite> 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 2
2 Mary 1
3 Mike 22 3
4 Amy 1
5 Alen 18 2
6 Ren 3
7 Lucy 20 1
8 John 0
9 Mini 16 1
sqlite> select coalesce(age, name) from new_person;
coalesce(age, name)
-------------------
21
Mary
22
Amy
18
Ren
20
John
16
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,Mini
sqlite> 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;
0
0
0
0
1
1
1
1
1
LENGTH 函数
如果参数X为字符串,则返回字符的数量,如果为数值,则返回该参数的字符串表示形式的长度,如果为NULL,则返回NULL。
sqlite> select name, length(name) from person;
name length(name)
---------- ------------
Jack 4
Mary 4
Mike 4
Amy 3
Alen 4
Ren 3
Lucy 4
John 4
Mini 4
LOWER 函数
返回函数参数X的小写形式,缺省情况下,该函数只能应用于ASCII字符。
sqlite> select lower(name) from person;
lower(name)
-----------
jack
mary
mike
amy
alen
ren
lucy
john
mini
LTRIM 函数
ltrim(X[,Y]) 如果没有可选参数Y,该函数将移除参数X左侧的所有空格符。如果有参数Y,则移除X左侧的任意在Y中出现的字符。最后返回移除后的字符串。
sqlite> select ltrim(name, 'J') from person;
ltrim(name, 'J')
----------------
ack
Mary
Mike
Amy
Alen
Ren
Lucy
ohn
Mini
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;
16
17
18
19
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')
-----------------------
JacM
Mary
MiMe
Amy
Alen
Ren
Lucy
John
Mini
ROUND 函数
round(X[,Y]) 返回数值参数X被四舍五入到Y刻度的值,如果参数Y不存在,缺省参数值为0。
sqlite> select round(4.5);
round(4.5)
----------
5.0
sqlite> 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')
------------------
Jac
Mary
Mi
Amy
Al
R
Lucy
Joh
Mini
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)
------------------
ac
ar
ik
my
le
en
uc
oh
in
UPPER 函数
SQLite UPPER 函数把字符串转换为大写字母。
sqlite> select upper(name) from person;
upper(name)
-----------
JACK
MARY
MIKE
AMY
ALEN
REN
LUCY
JOHN
MINI
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')
----------------
ac
Mary
Mike
Amy
Alen
Ren
Lucy
ohn
Mini
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–日期 & 时间这篇博客中有详细的介绍,有兴趣的可以去看,这里我就不提啦。
结束语:本文仅用来学习记录,参考查阅。