# SQLite學習手冊(內置函數) 一、聚合函數： SQLite中支持的聚合函數在很多其他的關系型數據庫中也同樣支持，因此我們這里將只是給出每個聚集函數的簡要說明，而不在給出更多的示例了。這里還需要進一步說明的是，對于所有聚合函數而言，distinct關鍵字可以作為函數參數字段的前置屬性，以便在進行計算時忽略到所有重復的字段值，如count(distinct x)。 | 函數 | 說明 | | --- | --- | | avg(x) | 該函數返回在同一組內參數字段的平均值。對于不能轉換為數字值的String和BLOB類型的字段值，如'HELLO'，SQLite會將其視為0。avg函數的結果總是浮點型，唯一的例外是所有的字段值均為NULL，那樣該函數的結果也為NULL。 | | count(x|*)　　 | count(x)函數返回在同一組內，x字段中值不等于NULL的行數。count(*)函數返回在同一組內的數據行數。 | | group_concat(x[,y]) | 該函數返回一個字符串，該字符串將會連接所有非NULL的x值。該函數的y參數將作為每個x值之間的分隔符，如果在調用時忽略該參數，在連接時將使用缺省分隔符","。再有就是各個字符串之間的連接順序是不確定的。　　 | | max(x) | 該函數返回同一組內的x字段的最大值，如果該字段的所有值均為NULL，該函數也返回NULL。 | | min(x) | 該函數返回同一組內的x字段的最小值，如果該字段的所有值均為NULL，該函數也返回NULL。 | | sum(x) | 該 函數返回同一組內的x字段值的總和，如果字段值均為NULL，該函數也返回NULL。如果所有的x字段值均為整型或者NULL，該函數返回整型值，否則就 返回浮點型數值。最后需要指出的是，如果所有的數據值均為整型，一旦結果超過上限時將會拋出"integer overflow"的異常。 | | total(x) | 該函數不屬于標準SQL，其功能和sum基本相同，只是計算結果比sum更為合理。比如當所有字段值均為NULL時，和sum不同的是，該函數返回0.0。再有就是該函數始終返回浮點型數值。該函數始終都不會拋出異常。 | 二、核心函數： 以下函數均為SQLite缺省提供的內置函數，其聲明和描述見如下列表： | 函數 | 說明 | | --- | --- | | abs(X) | 該函數返回數值參數X的絕對值，如果X為NULL，則返回NULL，如果X為不能轉換成數值的字符串，則返回0，如果X值超出Integer的上限，則拋出"Integer Overflow"的異常。 | | changes() | 該函數返回最近執行的INSERT、UPDATE和DELETE語句所影響的數據行數。我們也可以通過執行C/C++函數sqlite3_changes()得到相同的結果。 | | coalesce(X,Y,...)　　 | 返回函數參數中第一個非NULL的參數，如果參數都是NULL，則返回NULL。該函數至少2個參數。 | | ifnull(X,Y) | 該函數等同于兩個參數的coalesce()函數，即返回第一個不為NULL的函數參數，如果兩個均為NULL，則返回NULL。 | | length(X)　　 | 如果參數X為字符串，則返回字符的數量，如果為數值，則返回該參數的字符串表示形式的長度，如果為NULL，則返回NULL。 | | lower(X) | 返回函數參數X的小寫形式，缺省情況下，該函數只能應用于ASCII字符。 | | ltrim(X[,Y])　　 | 如果沒有可選參數Y，該函數將移除參數X左側的所有空格符。如果有參數Y，則移除X左側的任意在Y中出現的字符。最后返回移除后的字符串。 | | max(X,Y,...) | 返回函數參數中的最大值，如果有任何一個參數為NULL，則返回NULL。 | | min(X,Y,...) | 返回函數參數中的最小值，如果有任何一個參數為NULL，則返回NULL。 | | nullif(X,Y) | 如果函數參數相同，返回NULL，否則返回第一個參數。　　 | | random()　　 | 返回整型的偽隨機數。 | | replace(X,Y,Z)　　 | 將字符串類型的函數參數X中所有子字符串Y替換為字符串Z，最后返回替換后的字符串，源字符串X保持不變。 | | round(X[,Y]) | 返回數值參數X被四舍五入到Y刻度的值，如果參數Y不存在，缺省參數值為0。　　 | | rtrim(X[,Y])　　 | 如果沒有可選參數Y，該函數將移除參數X右側的所有空格符。如果有參數Y，則移除X右側的任意在Y中出現的字符。最后返回移除后的字符串。 | | substr(X,Y[,Z]) | 返回函數參數X的子字符串，從第Y位開始(X中的第一個字符位置為1)截取Z長度的字符，如果忽略Z參數，則取第Y個字符后面的所有字符。如果Z的值為負數，則從第Y位開始，向左截取abs(Z)個字符。如果Y值為負數，則從X字符串的尾部開始計數到第abs(Y)的位置開始。　 | | total_changes() | 該函數返回自從該連接被打開時起，INSERT、UPDATE和DELETE語句總共影響的行數。我們也可以通過C/C++接口函數sqlite3_total_changes()得到相同的結果。 | | trim(x[,y]) | 如果沒有可選參數Y，該函數將移除參數X兩側的所有空格符。如果有參數Y，則移除X兩側的任意在Y中出現的字符。最后返回移除后的字符串。? | | upper(X) | 返回函數參數X的大寫形式，缺省情況下，該函數只能應用于ASCII字符。 | | typeof(X) | 返回函數參數數據類型的字符串表示形式，如"Integer、text、real、null"等。 | 三、日期和時間函數： SQLite主要支持以下四種與日期和時間相關的函數，如： 1). `date(timestring, modifier, modifier, ...)` 2). `time(timestring, modifier, modifier, ...)` 3). `datetime(timestring, modifier, modifier, ...)` 4). `strftime(format, timestring, modifier, modifier, ...)` 以上所有四個函數都接受一個時間字符串作為參數，其后再跟有0個或多個修改符。其中strftime()函數還接受一個格式字符串作為其第一個參數。strftime()和C運行時庫中的同名函數完全相同。至于其他三個函數，date函數的缺省格式為："YYYY-MM-DD"，time函數的缺省格式為："HH:MM:SS"，datetime函數的缺省格式為："YYYY-MM-DD HH:MM:SS"。??? 1\. strftime函數的格式信息： | 格式 | 說明 | | --- | --- | | %d | day of month: 00 | | %f | fractional seconds: SS.SSS | | %H | hour: 00-24 | | %j | day of year: 001-366 | | %J | Julian day number | | %m | month: 01-12 | | %M | minute: 00-59 | | %s | seconds since 1970-01-01 | | %S | seconds: 00-59 | | %w | day of week 0-6 with Sunday==0 | | %W | week of year: 00-53 | | %Y | year: 0000-9999 | | %% | % | 需要額外指出的是，其余三個時間函數均可用strftime來表示，如： ``` date(...) ?? ??? ?strftime('%Y-%m-%d', ...) time(...) ?? ??? ?strftime('%H:%M:%S', ...) datetime(...)?? strftime('%Y-%m-%d %H:%M:%S', ...) ``` 2\. 時間字符串的格式： 見如下列表： 1). YYYY-MM-DD 2). YYYY-MM-DD HH:MM 3). YYYY-MM-DD HH:MM:SS 4). YYYY-MM-DD HH:MM:SS.SSS 5). HH:MM 6). HH:MM:SS 7). HH:MM:SS.SSS 8). now 5)到7)中只是包含了時間部分，SQLite將假設日期為2000-01-01。8)表示當前時間。 3\. 修改符： 見如下列表： 1). NNN days 2). NNN hours 3). NNN minutes 4). NNN.NNNN seconds 5). NNN months 6). NNN years 7). start of month 8). start of year 9). start of day 10).weekday N ?? ? 1)到6)將只是簡單的加減指定數量的日期或時間值，如果NNN的值為負數，則減，否則加。7)到9)則將時間串中的指定日期部分設置到當前月、年或日的開始。10)則將日期前進到下一個星期N，其中星期日為0。注：修改符的順序極為重要，SQLite將會按照從左到右的順序依次執行修改符。 4\. 示例： --返回當前日期。 ``` sqlite> SELECT date('now'); ? 2012-01-15?? ? ``` --返回當前月的最后一天。 ``` sqlite> SELECT date('now','start of month','1 month','-1 day'); 2012-01-31 ``` --返回從1970-01-01 00:00:00到當前時間所流經的秒數。 ``` sqlite> SELECT strftime('%s','now'); 1326641166?? ? ``` --返回當前年中10月份的第一個星期二是日期。 ``` sqlite> SELECT date('now','start of year','+9 months','weekday 2'); 2012-10-02??? ```