SQLite 函数

最后修改于 2020 年 7 月 6 日

在本 SQLite 教程中，我们将介绍 SQLite 内置函数。 SQLite 数据库中有三种类型的函数：核心函数、聚合函数和日期 & 时间函数。

我们将介绍来自每组 SQLite 函数的一些函数。

SQLite 核心函数

在这个组中，我们有各种函数。有些是数值函数，有些处理文本。其他函数执行一些非常具体的操作。

sqlite> SELECT sqlite_version() AS 'SQLite Version';
SQLite Version
--------------
3.16.2

sqlite_version 函数返回 SQLite 库的版本。

sqlite> SELECT random() AS Random;
Random             
-------------------
1056892254869386643

random 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。

sqlite> SELECT abs(11), abs(-5), abs(0), abs(NULL);
abs(11)             abs(-5)      abs(0)      abs(NULL) 
------------------  -----------  ----------  ----------
11                  5            0           NULL

abs 函数返回数值参数的绝对值。

sqlite> SELECT max(Price), min(Price) FROM Cars;
max(Price)  min(Price)
----------  ----------
350000      9000

在我们的例子中，max 和 min 函数从 Cars 表中返回最贵和最便宜的汽车。

sqlite> .width 18
sqlite> SELECT upper(Name) AS 'Names in capitals' FROM Friends;
Names in capitals 
------------------
JANE              
THOMAS            
FRANK             
ELISABETH         
MARY              
LUCY              
JACK

upper 函数将字符转换为大写字母。

sqlite> SELECT lower(Name) AS 'Names in lowercase' FROM Friends
   ...> WHERE Id IN (1, 2, 3);
Names in lowercase
------------------
jane              
thomas            
frank

使用 lower 函数，我们将前三行的名称更改为小写字母。

sqlite> SELECT length('ZetCode');
length('ZetCode') 
------------------
7

length 函数返回字符串的长度。

sqlite> SELECT total_changes() AS 'Total changes';
Total changes
-------------
3

total_changes 函数返回自当前数据库连接打开以来，由 INSERT、UPDATE 或 DELETE 语句引起的行更改的数量。在当前数据库连接中，我们执行了三个 INSERT 语句，因此总更改次数等于三。

sqlite> .width 5
sqlite> SELECT sqlite_compileoption_used('SQLITE_DEFAULT_FOREIGN_KEYS') AS 'FK';
FK   
-----
0

sqlite_compileoption_used 函数返回一个布尔值，具体取决于该选项是否在构建期间使用。在我们的例子中，我们检查是否默认强制执行 FOREIGN KEY 约束。该函数返回 0，这意味着默认情况下不强制执行该约束。我们使用 PRAGMA 语句来更改它。（PRAGMA foreign_keys = 1;）

sqlite> SELECT typeof(12), typeof('ZetCode'), typeof(33.2), typeof(NULL), 
   ...> typeof(x'345edb');
typeof(12)    typeof('ZetCode')   typeof(33.2)  typeof(NULL)  typeof(x'345edb')
------------  ------------------  ------------  ------------  -----------------
integer       text                real          null          blob

typeof 函数返回参数的数据类型。

SQLite 聚合函数

使用聚合函数，我们可以获得一些统计数据。以单个参数为参数的聚合函数可以前面加上 DISTINCT 关键字。在这种情况下，重复的元素在传递到聚合函数之前会被过滤掉。

我们重述了 Cars 表中我们所拥有的内容。

sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600

请注意，没有重复的记录。

sqlite> SELECT count(*) AS '# of cars' FROM Cars;
# of cars 
----------
8

count 函数返回表中的行数 - 有八辆车。

在 Orders 表中，我们确实有重复的客户记录。

sqlite> SELECT * FROM Orders;
Id          OrderPrice  Customer  
----------  ----------  ----------
1           1200        Williamson
2           200         Robertson 
3           40          Robertson 
4           1640        Smith     
5           100         Robertson 
6           50          Williamson
7           150         Smith     
8           250         Smith     
9           840         Brown     
10          440         Black     
11          20          Brown

从逻辑上讲，每个客户可以下多个订单。我们如何计算订单数量以及如何计算客户数量？

sqlite> SELECT count(Customer) AS '# of orders'  FROM Orders;
# of orders
-----------
11

此 SQL 语句返回订单数量。要计算唯一客户的数量，我们必须使用 DISTINCT 子句。

sqlite> SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders;
# of customers
--------------
5

我们的 Orders 表中有 5 个客户。他们下了 11 个订单。

接下来，我们将演示 count(*) 和 count(ColumnName) 函数之间的区别。区别在于它们处理 NULL 值的方式。

sqlite> .nullvalue NULL

首先，我们更改 sqlite3 显示 NULL 值的方式。默认情况下，NULL 值显示为空字符串。

sqlite> CREATE TABLE TESTING(Id INTEGER);
sqlite> INSERT INTO Testing VALUES (1), (2), (3), (NULL), (NULL);

在这里，我们创建表 Testing，其中包含 3 个数值和 2 个 NULL 值。

sqlite> SELECT last_insert_rowid();
5

last_insert_rowid 函数返回最后插入行的 Id。

sqlite> SELECT count(*) AS '# of rows' FROM Testing;
# of rows 
----------
5

count(*) 返回表中的行数。它考虑了 NULL 值。

sqlite> SELECT count(Id) AS '# of non NULL values' FROM Testing;
# of non NULL values
--------------------
3

count(Id) 仅计算非 NULL 值。

sqlite> SELECT avg(Price) AS 'Average price' FROM Cars;
Average price
-------------
72721.125

avg 函数返回所有非 NULL 记录的平均值。在我们的示例中，我们显示了 Cars 表中汽车的平均价格。

最后，我们提到了 sum 函数。它对所有非 NULL 值求和。

sqlite> SELECT sum(OrderPrice) AS Sum FROM Orders;
Sum     
--------
4930

在这里，我们计算了我们的客户下了多少个订单。

SQLite 日期和时间函数

SQLite 具有用于处理日期和时间的函数。这些函数使用各种时间字符串、修饰符和格式。

sqlite> .header OFF
sqlite> SELECT date('now');
2014-11-17

date 函数与 now 字符串一起返回当前日期。

sqlite> SELECT datetime('now');
2018-07-20 09:57:38

datetime 函数返回当前日期和时间。

sqlite> SELECT time('now');
09:57:56

time 函数给出当前时间。

sqlite> SELECT time(), time('now');
09:58:30    09:58:30  
sqlite> SELECT date(), date('now');
2018-07-20  2018-07-20

now 字符串可以省略。

date、time 和 datetime 函数的第一个参数是时间字符串。它后面可以跟一个或多个修饰符。

sqlite> SELECT date('now', '2 months');
2018-09-20

在这个例子中，'2 个月' 是一个修饰符。它将两个月添加到当前日期。因此该函数返回从今天开始两个月的日期。

sqlite> SELECT date('now', '-55 days');
2018-05-26

也可以使用负修饰符。在这个例子中，我们从今天提取 55 天。

sqlite> SELECT date('now', 'start of year');
2018-01-01

使用 start of year 修饰符，我们可以获得年初的日期，例如 1 月 1 日。

sqlite> SELECT datetime('now', 'start of day');
2018-07-20 00:00:00

在 start of day 修饰符的帮助下，我们可以获得当天的开始时间。

sqlite> SELECT date('now', 'weekday 6');
2018-07-21

weekday 修饰符会前进到下一个日期，其中星期日为 0，星期一为 1，...，星期六为 6。在这个例子中，我们得到最近一个星期六的日期。

修饰符可以组合使用。

sqlite> SELECT date('now', 'start of year', '10 months', 'weekday 4');
2018-11-01

此 SQL 语句返回当年 11 月的第一个星期四。在这个例子中，我们使用了三个修饰符：start of year、+x months 和 weekday x。now 时间字符串给出当前日期。 start of year 将日期向后移动到年初。10 months 将 10 个月添加到当前月份（一月）。最后，weekday 4 修饰符将日期提前到第一个星期四。

strftime 函数根据指定为第一个参数的格式字符串返回格式化的日期和时间。第二个参数是时间字符串。它后面可以跟一个或多个修饰符。

sqlite> SELECT strftime('%d-%m-%Y');
20-07-2018

我们可以使用 strftime 函数以不同的格式返回日期。

sqlite> SELECT 'Current day: ' || strftime('%d');
Current day: 20

此 SQL 语句返回当月的当前日期。我们使用了 strftime 函数。

sqlite> SELECT 'Days to XMas: ' || (strftime('%j', '2018-12-24') - strftime('%j', 'now'));
Days to XMas: 157

在这里，我们计算了圣诞节前的天数。%j 修饰符给出了时间字符串的年数。

在本 SQLite 教程中，我们使用了内置的 SQLite 函数。

电子书

SQLite 函数

SQLite 核心函数

SQLite 聚合函数

SQLite 日期和时间函数