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 外键= 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);

在这里，我们创建具有 3 个数字值和 2 个NULL值的表Testing。

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

带有now字符串的date()函数返回当前日期。

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在当月（1 月）增加了 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 日期和时间函数

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

SQLite 教程

回顶部

SQLite 核心函数

SQLite 聚合函数

SQLite 日期和时间函数

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

SQLite 教程

回顶部

切换注册登录

用户名或邮箱

密码

切换登录注册

昵称

邮箱