SQLite学习笔记（三）

概述

本篇文章将介绍 SQLite 中的函数，了解如何利用内置的函数来处理数据。文章结构如下：

• 聚合函数介绍
• 其他内置函数介绍
• 时间和日期函数介绍
• 数据类型的比较和NULL值的处理

---

一、聚合函数

SQLite 常用的聚合函数如下表所示，对于所有的聚合函数来说，关键字 DISTINCT 可以作为它们函数参数字段的前置属性，用以过滤重复的字段值：

函数名	描述
count(x | *)	计算字段x中非NULL行数，count(*)返回在同一组内的数据行数
avg(x)	计算字段x的平均值
max(x)	选择字段x的最大值
min(x)	选择字段x的最小值
sum(x)	为一个数值列计算总和
total(x)	该函数不属于标准SQL，其功能和sum基本相同，但计算结果比sum更合理。比如当所有字段值均为NULL时，该函数返回0.0。再有就是该函数始终返回浮点型数值。该函数始终都不会抛出异常。
group_concat(x [,y])	该函数返回一个字符串，该字符串将会连接所有非NULL的x值。该函数的y参数将作为每个x值之间的分隔符，如果在调用时忽略该参数，在连接时将使用缺省分隔符","

这里需要特别注意一下 count 函数，例如有表 Contacts 的数据如下所示：

sqlite> SELECT * FROM Contacts;
id          name        phone
----------  ----------  ----------
1           Marck       NULL
2           Beryl       10086
3           Micheal     10010
4           Tom         NULL

可以看到表中一共有4条数据，其中有两条的phone字段为空。当我们使用 count(*) 时，返回的是表的行数：

sqlite> SELECT count(*) FROM Contacts;
count(*)
----------
4

需要注意的是，即使某一行的数据全为 NULL（表格允许的话），那么 count(*) 依然会算上这一行数据。

而当 count 函数的参数为表格的某一字段时，返回的是该字段的非 NULL 行数：

sqlite> SELECT count(phone) FROM Contacts;
count(phone)
----------
2

---

二、其它函数

除了上述的聚合函数之外，SQLite 还提供了下列函数：

函数名	描述
abs(x)	返回参数x的绝对值
length(x)	如果参数X为字符串，则返回字符的数量，如果为数值，则返回该参数的字符串表示形式的长度，如果为NULL，则返回NULL
upper(x)	返回参数x的大写形式，该函数只能应用于ASCII字符
lower(x)	返回参数x的小写形式，该函数只能应用于ASCII字符
random()	返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数
ltrim(x [,y])	如果没有可选参数Y，该函数将移除参数X左侧的所有空格符；如果有参数Y，则移除X左侧的任意在Y中出现的字符。最后返回移除后的字符串
rtrim(x [,y])	如果没有可选参数Y，该函数将移除参数X右侧的所有空格符；如果有参数Y，则移除X右侧的任意在Y中出现的字符。最后返回移除后的字符串
trim(x [,y])	如果没有可选参数Y，该函数将移除参数X两侧的所有空格符。如果有参数Y，则移除X两侧的任意在Y中出现的字符。最后返回移除后的字符串
changes()	返回最近执行的INSERT、UPDATE和DELETE语句所影响的数据行数
coalesce(x,y,…)	返回函数参数中第一个非NULL的参数，如果参数都是NULL，则返回NULL
ifnull(x,y)	返回第一个不为NULL的函数参数，如果两个均为NULL，则返回NULL
typeod(x)	返回函数参数数据类型的字符串表示形式，如"Integer、text、real、null"等
nullif(x,y)	如果x、y相等返回NULL，否则的话返回第一个参数x

---

三、时间和日期函数

SQLite 支持以下五个日期和时间函数：

函数名	描述
date(timestring, modifier, modifier, …)	以 YYYY-MM-DD 格式返回日期
time(timestring, modifier, modifier, …)	以 HH:MM:SS 格式返回时间
datetime(timestring, modifier, modifier, …)	以 YYYY-MM-DD HH:MM:SS 格式返回
julianday(timestring, modifier, modifier, …)	返回从格林尼治时间的公元前 4714 年 11 月 24 日正午算起的天数
strftime(format, timestring, modifier, modifier, …)	根据第一个参数指定的格式字符串返回格式化的日期

上述五个日期和时间函数把时间字符串作为参数。时间字符串后跟零个或多个 modifier 修饰符。strftime() 函数也可以把格式字符串 format 作为其第一个参数。

1. 时间字符串(timeString)

时间字符串可以采用下列的任意格式：

时间字符串	实例
YYYY-MM-DD	2019-04-20
YYYY-MM-DD HH:MM	2019-04-20 22:43
YYYY-MM-DD HH:MM:SS	2019-04-20 22:43:46
YYYYMMDD HHMMSS	20190420 224346
YYYY-MM-DDTHH:MM	2019-04-20 22:43:46
HH:MM:SS	22:43:46
now	2019-04-20

2. 修饰符(modifier)

修饰符	描述
NNN days	用于天数的调整，+1 day表示向后一天，-1 day表示向前一天
NNN hours	用于小时的调整，+1 hour表示向后一小时，-1 hour表示向前一小时
NNN minutes	用于分钟的调整，+1 minute表示向后一分钟，-1 minute表示向前一分钟
NNN.NNNN seconds	用于秒的调整，+1 second表示向后一秒，-1 second表示向前一秒
NNN months	用于月份的调整，+1 month表示向后一个月，-1 month表示向前一个月
NNN years	用于年份的调整，+1 year表示向后一年，-1 year表示向前一年
start of month	某一日期的当前月份
start of year	某一日期的当前年份
start of day	某一日期的当前天数
weekday N	表示星期N，N取值为0~6，分别表示周日 ~ 周六
unixepoch	UNIX 时间戳
localtime	本地时区的日期和时间
utc	转化为UTC时间值

3. 格式化(format)

格式化是用于 strftime 函数的参数，可采用值如下：

格式	描述
%d	一月中的第几天，01-31
%f	带小数部分的秒，SS.SSS
%H	小时，00-23
%j	一年中的第几天，001-366
%J	儒略日数，DDDD.DDDD
%m	月，00-12
%M	分，00-59
%s	从 1970-01-01 算起的秒数
%S	秒，00-59
%w	一周中的第几天，0-6 (0 is Sunday)
%W	一年中的第几周，01-53
%Y	年，YYYY
%%	% symbol

4. 实例

计算当前月份的最后一天：

sqlite> SELECT date('now','start of month', '+1 month', '-1 day') AS last_day_of_this_month;
last_day_of_this_month
----------------------
2019-04-30

计算当年 10 月的第一个星期二的日期：

sqlite> SELECT date('now', 'start of year', '+9 month', 'weekday 2') AS october_first_tues;
october_first_tues
------------------
2019-10-01

计算从 2004 年某一特定时刻以来的秒数:

sqlite> SELECT strftime('%s', 'now')-strftime('%s', '2004-01-01 11:26:00') AS Count;
Count
----------
482817426

计算新中国成立以来至今的天数：

sqlite> SELECT julianday('now')-julianday('1949-10-01') AS China_Found_Day;
China_Found_Day
----------------
25403.6443211804

当前时间与UTC时间的转换：

sqlite> SELECT datetime('now', 'utc') AS UTC;
UTC
-------------------
2019-04-20 07:24:24

---

四、数据类型的比较和NULL值的处理

首先创建一个表 Test，然后插入任意的值，如下所示：

CREATE TABLE Test(whatever);
INSERT INTO Test VALUES('314159');
INSERT INTO Test VALUES(314159);
INSERT INTO Test VALUES(x'314159');
INSERT INTO Test VALUES(3.14159);
INSERT INTO Test VALUES(NULL);

我们在插入这些值之后，通过 SELECT 和 typeof 函数来查看我们插入的值：

SELECT whatever, typeof(whatever) 
FROM Test;
whatever    typeof(whatever)
----------  ----------------
314159      text
314159      integer
1AY         blob
3.14159     real
NULL        null

可以看到字段 whatever 没有添加任何约束，因此它可以存储不同的类。我们通过 typeof 函数也验证了它确实存储了不同的类，那这些不同的类之间是如何比较大小的呢？事实上它们遵循一定的规则：

• NULL 存储类聚有最低的类值。一个具有 NULL 存储类的值比所有其他的值都小（包括其它具有 NULL 存储类的值）。在 NULL 值之间，没有具体的排序顺序。
• integer 或 real 存储类值高于 NULL，它们的类值相等，integer 值和 real 值通过去数值进行比较。
• text 存储类的值比 integer 和 real 高。数值永远比字符串的值低。当两个 text 值进行比较时，其大小由其值中定义的“排序法”决定。
• blob 存储类具有最高的类值。具有 blob 类的值大于其他所有类的值。

所以当我们执行下列语句时，我们就能知道会得出什么结果了：

SELECT whatever, typeof(whatever) 
FROM Test 
ORDER BY whatever;
whatever    typeof(whatever)
----------  ----------------
NULL        null
3.14159     real
314159      integer
314159      text
1AY         blob

其结果与我们所说的规则一致。在了解了数据类型之间的比较之后，接下来我们来看到数据库中比较特殊的 NULL 值。

NULL值

NULL 值在 SQLite 中表示的是一种对“未知”或“不可知”概念的支持。NULL 是缺失信息的占位符，本身不是值。NULL 表示该位置没有值：NULL 不是什么其它值，NULL不是真，也不是假，不是零，也不是空字符串。在 SQLite 中，关于 NULL 有如下三条约定：

1. 为了在逻辑表达式中使用 NULL，SQLite 使用了三态逻辑，NULL 是真假值之一。下表显示了 AND、OR 与 NULL 之间的关系：

x	y	x AND y	x OR y
True	True	True	True
True	False	False	True
True	NULL	NULL	True
False	False	False	False
False	NULL	False	NULL
NULL	NULL	NULL	NULL

2. 可以通过 IS NULL 或者 IS NOT NULL 操作符检测 NULL 是否存在。
3. NULL 不等于其他值，包括 NULL，不可以将 NULL 与其他值进行比较，NULL 绝对不会大于或小于其他 NULL 值。

---

参考

SQLite学习手册（内置函数）

SQLite 日期 & 时间

《SQLite权威指南》

• 第3章 sqlite中的sql
• 第4章 sqlite中的高级sql

---

希望这篇文章对你有所帮助~