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 函數。