SQLite 函数 · ZetCode 中文系列教程

# SQLite 函数 > 原文： [http://zetcode.com/db/sqlite/sqlitefunctions/](http://zetcode.com/db/sqlite/sqlitefunctions/) 在 SQLite 教程的这一部分中，我们将介绍 SQLite 内置函数。 SQLite 数据库中有三种类型的函数：核心函数，聚合函数和日期&时间函数。我们将介绍每组 SQLite 函数中的一些函数。 ## SQLite 核心函数在这一组中，我们具有各种函数。有些是数字函数，有些是文本处理。其他人则做一些非常具体的事情。 ```sql sqlite> SELECT sqlite_version() AS 'SQLite Version'; SQLite Version -------------- 3.16.2 ``` `sqlite_version()`函数返回 SQLite 库的版本。 ```sql sqlite> SELECT random() AS Random; Random ------------------- 1056892254869386643 ``` `random()`函数返回 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。 ```sql sqlite> SELECT abs(11), abs(-5), abs(0), abs(NULL); abs(11) abs(-5) abs(0) abs(NULL) ------------------ ----------- ---------- ---------- 11 5 0 NULL ``` `abs()`函数返回数字参数的绝对值。 ```sql sqlite> SELECT max(Price), min(Price) FROM Cars; max(Price) min(Price) ---------- ---------- 350000 9000 ``` 在我们的示例中，`max()`和`min()`函数从`Cars`表中返回最昂贵和最便宜的汽车。 ```sql sqlite> .width 18 sqlite> SELECT upper(Name) AS 'Names in capitals' FROM Friends; Names in capitals ------------------ JANE THOMAS FRANK ELISABETH MARY LUCY JACK ``` `upper()`函数将字符转换为大写字母。 ```sql sqlite> SELECT lower(Name) AS 'Names in lowercase' FROM Friends ...> WHERE Id IN (1, 2, 3); Names in lowercase ------------------ jane thomas frank ``` 使用`lower()`函数，我们将前三行的名称更改为小写字母。 ```sql sqlite> SELECT length('ZetCode'); length('ZetCode') ------------------ 7 ``` `length()`函数返回字符串的长度。 ```sql sqlite> SELECT total_changes() AS 'Total changes'; Total changes ------------- 3 ``` 自打开当前数据库连接以来，`total_changes()`函数返回由`INSERT`，`UPDATE`或`DELETE`语句引起的行更改数。在当前的数据库连接中，我们已经完成了三个`INSERT`语句，所以总更改等于三个。 ```sql 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;`） ```sql 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`表中的内容。 ```sql 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 ``` 请注意，没有重复的记录。 ```sql sqlite> SELECT count(*) AS '# of cars' FROM Cars; # of cars ---------- 8 ``` `count()`函数返回表中的行数-共有八辆汽车。在`Orders`表中，我们确实有重复的客户记录。 ```sql 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 ``` 从逻辑上讲，每个客户可以下多个订单。我们如何计算订单数量以及如何计算客户数量？ ```sql sqlite> SELECT count(Customer) AS '# of orders' FROM Orders; # of orders ----------- 11 ``` 该 SQL 语句返回订单数。要计算唯一客户的数量，我们必须利用`DISTINCT`子句。 ```sql sqlite> SELECT count(DISTINCT Customer) AS '# of customers' FROM Orders; # of customers -------------- 5 ``` `Orders`表中有 5 个客户。他们下了 11 个订单。接下来，我们将演示`count(*)`和`count(ColumnName)`函数之间的区别。区别在于它们处理`NULL`值的方式。 ```sql sqlite> .nullvalue NULL ``` 首先，我们更改`sqlite3`显示`NULL`值的方式。默认情况下，`NULL`值显示为空字符串。 ```sql sqlite> CREATE TABLE TESTING(Id INTEGER); sqlite> INSERT INTO Testing VALUES (1), (2), (3), (NULL), (NULL); ``` 在这里，我们创建具有 3 个数字值和 2 个`NULL`值的表`Testing`。 ```sql sqlite> SELECT last_insert_rowid(); 5 ``` `last_insert_rowid()`函数返回最后插入的行的 ID。 ```sql sqlite> SELECT count(*) AS '# of rows' FROM Testing; # of rows ---------- 5 ``` `count(*)`返回表中的行数。它考虑了`NULL`值。 ```sql sqlite> SELECT count(Id) AS '# of non NULL values' FROM Testing; # of non NULL values -------------------- 3 ``` `count(Id)`仅计算非`NULL`值。 ```sql sqlite> SELECT avg(Price) AS 'Average price' FROM Cars; Average price ------------- 72721.125 ``` `avg()`函数返回所有非`NULL`记录的平均值。在我们的示例中，我们在`Cars`表中显示了汽车的平均价格。最后，我们提到`sum()`函数。它将所有非`NULL`值相加。 ```sql sqlite> SELECT sum(OrderPrice) AS Sum FROM Orders; Sum -------- 4930 ``` 在这里，我们计算客户下的订单数量。 ## SQLite 日期和时间函数 SQLite 具有处理日期和时间的函数。这些函数采用各种时间字符串，修饰符和格式。 ```sql sqlite> .header OFF sqlite> SELECT date('now'); 2014-11-17 ``` 带有`now`字符串的`date()`函数返回当前日期。 ```sql sqlite> SELECT datetime('now'); 2018-07-20 09:57:38 ``` `datetime()`函数返回当前日期和时间。 ```sql sqlite> SELECT time('now'); 09:57:56 ``` `time()`函数给出当前时间。 ```sql 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()`函数的第一个参数是时间字符串。可以紧跟一个或多个修饰符。 ```sql sqlite> SELECT date('now', '2 months'); 2018-09-20 ``` 在此示例中，`'2 months'`是修饰语。它会将当前日期增加两个月。因此该函数返回从今天起两个月的日期。 ```sql sqlite> SELECT date('now', '-55 days'); 2018-05-26 ``` 也可以使用负修饰符。在此示例中，我们从今天开始提取 55 天。 ```sql sqlite> SELECT date('now', 'start of year'); 2018-01-01 ``` 使用`start of year`修饰符，我们可以得到年初的日期，例如 1 月 1 日。 ```sql sqlite> SELECT datetime('now', 'start of day'); 2018-07-20 00:00:00 ``` 借助`start of day`修饰符，我们可以了解当天的开始时间。 ```sql sqlite> SELECT date('now', 'weekday 6'); 2018-07-21 ``` `weekday`修饰符前进到下一个日期，其中星期日是 0，星期一 1，...，星期六 6。在本示例中，我们获得最近的星期六的日期。修饰符可以组合。 ```sql 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()`函数返回根据指定为第一个参数的格式字符串格式化的日期和时间。第二个参数是时间字符串。可以紧跟一个或多个修饰符。 ```sql sqlite> SELECT strftime('%d-%m-%Y'); 20-07-2018 ``` 我们可以使用`strftime()`函数以其他格式返回日期。 ```sql sqlite> SELECT 'Current day: ' || strftime('%d'); Current day: 20 ``` 该 SQL 语句返回每月的当前日期。我们使用了`strftime()`函数。 ```sql sqlite> SELECT 'Days to XMas: ' || (strftime('%j', '2018-12-24') - strftime('%j', 'now')); Days to XMas: 157 ``` 在这里，我们计算了到圣诞节为止的天数。 `%j`修饰符给出时间字符串的年份。在 SQLite 教程的这一部分中，我们使用了内置的 SQLite 函数。