SQLite常用點滴總結(轉)

標籤：blog   使用   io   檔案   資料   ar   問題   cti   

expression
expr ::=     expr binary-op expr |
expr [NOT] like-op expr
[ESCAPE expr] |
unary-op expr |
( expr ) |
column-name |
table-name
. column-name |
database-name . table-name . column-name |
literal-value
|
parameter |
function-name ( expr-list | * ) |
expr ISNULL |
expr
NOTNULL |
expr [NOT] BETWEEN expr AND expr |
expr [NOT] IN ( value-list )
|
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name .]
table-name |
[EXISTS] ( select-statement ) |
CASE [expr] ( WHEN expr THEN
expr )+ [ELSE expr] END |
CAST ( expr AS type ) |
expr COLLATE
collation-name
like-op ::=     LIKE | GLOB | REGEXP |
MATCH

這部分有別於其它部分。本文檔的其它大多數部分都在講特定的 SQL
命令。本部分所講的不是一個單一的命令，而是“運算式”，它經常用作其它命令的一部分來使用。

SQLite
有如下二元運算子，根據其優先順序從高到低有：

    ||
    *    /    %
    +    -
   
<<   >>   &    |
    <    <=   >    >=
   
=    ==   !=   <>   IN
    AND  
   
OR

以下是支援的一元運算子：

    -    +    !    ~    NOT

COLLATE
運算子可以被看作是一個後置的一元運算子它具有最高的優先順序。通常，與其前面的一元、及二元運算子相比，它與參數結合更為緊密。

一元 [Operator
+] 什麼也不做。它可以被應用於字串，數字，或BLOB，並永遠返回跟它作用的數值相同的結果。

注意，兩種變體的等號運算子是不一樣的。等可以是 =
或 ==。不等操作符有 != 或 <>。 || 運算子為“串連符”，它將兩個字串串連到一起。 %
輸出其左邊的數除以右面數後的餘數。

除 || 之外，任何二元操作符的結果都是一個數值型的值。 ||
返回兩個運算元串連後的大字串。

字面值是一個整數或浮點數的值。也支援科學記號標記法。小數點永遠使用“.” 字元來表示，即使本地設定指定用“,”
來表示也不例外。在這種規則下，使用“,” 作小數點會引起語義上的二義性。字串值應該用單引號（‘） 引起來。像在 Pascl
語言中那樣，字串中的單引號應該使用兩個單引號表示。由於像 C 語言那樣使用反斜線進行轉義的方式不是 SQL 語言的標準，所以不支援那種類型的轉義。
BLOB字面值是以一個“x” 或“X” 打頭的包含十六進位資料的字串值。例如：

   
X‘53514C697465‘

字面值也可以是 "NULL" 記號。

參數用於指定一個字面值在運算式中的存放位置。它將在運行時被使用
sqlite3_bind API 替換。參數有如下幾種格式：

    ?NNN        問號後跟一個數字 NNN 用於存放第 NNN
個參數。 NNN 必須在 1 到 999 之間。
    ?        只有一個問號，它將用於存放緊跟其後的一個未用到的參數。
   
:AAAA        冒號後面跟一個標誌符，用於存放名字為 AAAA
的變數。命名的變數也會被編號，編號將使用第一個未被使用的編號。為避免混淆，最好不要混用名字和編號作為參數。
    @AAAA        一個 @
符號等價於一個冒號。
    $AAAA        貨幣符號後跟一個標誌符也會存放名字為 AAAA
的參數。在這種情況下，參數名字中可以出現很多“::”，並且，在“(...)”尾碼中可以包含任何文本。該文法是由 TCL
語言中相同的法而來的。

未使用 sqlite3_bind 來賦值的參數則認為是 NULL。

LIKE
操作符會作一個模式比對比較。它右邊是一個匹配模式，左邊包含被匹配的字串。 在匹配模式中，百分比符號 % 會匹配字串中任意 0 個或多個字元。一個底線 _
符號僅匹配一個任意的字元。除此之外，其它的任何字元均只匹配它們自己（在不區分大小寫環境下可能會匹配與之對應的大、小寫字母）。（一個BUG：SQLite中不區分大小寫僅對
7 位元的拉丁字元有效，對於 8 位的 iso8859 字元集UTF8字元則是區分大小寫）。例如：運算式 ‘a‘ LIKE ‘A‘ 是 TRUE 但 ‘æ‘
LIKE ‘Æ‘ 則是 FALSE)。

若有可選的 ESCAPE 子句，那麼 ESCAPE
關鍵字後的運算式必須是一個單個的字元（叫做逸出字元）。該字元可以用於 LIKE
模式字串中來體現百分比符號或底線。逸出字元後面的百分比符號或底線均分別保持它們原來的意思。中綴的 LIKE 操作符是使用 like(X,Y)
函數實現的。
LIKE 操作符不區分大小寫，它將匹配一邊是小寫而另一邊是大寫的字串。（一個BUG：SQLite中不區分大小寫僅對 7
位元的拉丁字元有效，對於 8 位的 iso8859 字元集UTF8字元則是區分大小寫）。例如：運算式 ‘a‘ LIKE ‘A‘ 是 TRUE 但 ‘æ‘
LIKE ‘Æ‘ 則是 FALSE)。

中綴操作符 LIKE 是通過調用使用者函數 like(X,Y) 實現的。但若後面還有 ESCAPE
子句，它會在以上函數中增加第三個參數。如果該函數被其它的 like() SQL 函數重載，則需要注意。

GLOB 操作符與 LIKE
類似，但它使用 Unix 萬用字元的的檔案匹配文法。並且，與 LIKE 不同， GLOB 是大小寫敏感的。GLOB 和 LIKE 都可以用 NOT
關鍵字對匹配結果取反。 中綴的 GLOB 操作符是通過調用使用者函數 glob(X,Y) 實現的，可以通過該函數對其進行重載。

REGEXP
操作符是使用 regexp() 使用者函數的一個特殊文法。預設情況下， regexp() 使用者函數沒有定義，所以使用 REGEXP
將會出錯。如果在運行時增加一個名為 regexp 的使用者定義函數，則使用該操作符時將使用此函數來實現 REGEXP 功能。

MATCH
操作符是使用 match() 使用者函數的一個特殊文法。預設的 match()
函數實現只會引發一個異常，從而沒多大用處。但可以通過擴充該函數來實現更有用的邏輯。

列名colum name 可以是任何在 CREATE
TABLE 語句中指定的列名，也可以是如下一個特殊標誌符： "ROWID"、"OID" 或
"_ROWID_"。這些特殊標誌符均描述與每個表每一行所關連的唯一的整數鍵。特殊標誌符只會在 CREATE TABLE
語句沒有指定相同的列名時才會真正指代每一行。行索引值就像唯讀列。一個行索引值可以用在任何一個正常列所能使用的地方。只是，不能使用 UPDATE 或 INSERT
來改變行的索引值。 “SELECT * ...”也不會返回行索引值。

SELECT 語句可以出現在 IN
操作符的後面，或作為一個單獨的數量值出現，或者跟在 EXISTS 操作符後面。當作為一個單獨數量值或在 IN 操作符中時，SELECT 必須只能返回單列。複合
SELECT 查詢 （使用 UNION 或 EXCEPT 關鍵字的查詢）也是允許的。當使用 EXISTS 關鍵字時，SELECT 結果中的列被忽略。如果
SELECT 返回了一行或多行，則運算式就為 TRUE，否同就為 FALSE。如果內層 SELECT 運算式中的項目與外層的值無關，則內層 SELECT
會最先求值，並在以後需要時重用該值。如果內層 SELECT 中含有外層查詢相關的變數，那麼內層 SELECT
會在每次需要的時候被重新求值。

當一個 SELECT 作為 IN 操作符的右運算元時，如果 IN 左邊的運算元存在於右邊 SELECT
的查詢結果中， IN 就返回 TRUE。 IN 操作符也可以搭配 NOT 來對錶達式結果取反。

當一個 SELECT 出現在除 IN
之外的其它運算式中時， SELECT 結果的第一行將作為一個單一的值用於該運算式中。如果 該SELECT 返回多行，那麼其它的行將會被忽略。若 SELECT
返回 0 行，則其結果將是 NULL。

一個 CAST 運算式會將 的資料類型轉換為指定的類型
<type>。其中<type> 可以是任何在 CREATE TABLE
語句中有效非空的類型名。

系統支援簡單和聚集合函式。一個簡單函數可以用於任何錶達式。簡單函數會在其輸入的基礎上立即返回結果。聚集合函式只能用於
SELECT 語句中。 聚集合函式會在返回的結果集上進行跨行計算，並返回相應的結果。
核心函數 Core
Functions

下列是預設可以的核心函數。額外的函數可以使用 C 語言編寫並使用 sqlite3_create_function() API
加入資料庫引擎中。
abs(X)     返回參數 X的絕對值。
coalesce(X,Y,...)     返回參數中第一個非 NULL
的值。若所有參數均為 NULL，則返回 NULL。該函數至少需要兩個參數。
glob(X,Y)     該函數使用者實現 SQLite 的 "X GLOB
Y" 文法。可以使用 sqlite3_create_function() 介面來重載該函數，從而改變 GLOB 操作符的行為方式。
ifnull(X,Y)
    返回第一個非 NULL 的參數值，如果所有參數都是 NULL， 則返回 NULL。與上面的 coalesce() 一樣。
hex(X)    
參數以 BLOB 對待。結果是 BLOB 內容的 16 進位表示。
last_insert_rowid()     返回當前資料庫連接中最後插入的一行的
ROWID。該值與使用 sqlite_last_insert_rowid() API 函數返回的值相同。
length(X)     返回 X
字串的長度。如果 SQLite 配置為支援 UTF-8，則返回的是 UTF-8
字元的長度，而不是位元組數。
like(X,Y)
like(X,Y,Z)     該函數用於實現 SQL 的 "X LIKE Y [ESCAPE
Z]" 文法。如果有可選的 ESCAPE 語句，將使用具有三個參數的函數，否則，將使用只有兩個參數的資料。可以使用
sqlite_create_function() 介面來重載該函數，以改變 LIKE
操作符的行為。如果那樣做，一定要注意同時重載兩個（兩個參數的和三個參數的）版本的函數。否則，依據是否使用了 ESCAPE
子句，可以會調用不同的代碼。
load_extension(X)
load_extension(X,Y)     裝入 SQLite
共用庫之外檔案名稱為 X 而進入點為 Y 的擴充庫。結果將是 NULL。 如果省略了 Y，那麼，將使用預設的進入點
sqlite3_extension_init。該函數在裝入或初始化失敗時會引發一個異常。

如果擴充試圖修改或刪除一個 SQL
函數或對照序列，則該函數會失敗。可以使用擴充增加新函數或對照序列，但不能修改或刪除已存在的。這是因為那些函數和/或對照序列可能正在被其它的SQL語句使用。要想裝入可以修改或刪除函數或對照序列的擴充，使用
sqlite3_load_extension() C 語言API。
lower(X)     返回將字串 X 轉換為小寫後的字串。該函數使用 C
語言庫函數 tolower() 進行轉換，所以，可能不能正確轉換 UTF-8 字元。
ltrim(X)
ltrim(X,Y)    
返回一個字串，它是從字串 X 的左邊刪除了任何存在於字串 Y 中的字元後剩餘的字串。如果省略參數 Y
，則會刪除左邊的空格。
max(X,Y,...)     返回參數的最大值。除數字外，參數可能是字串。它使用通常的排序次序來決定最大值。注意若
max() 有兩個或多個參數，則它是一個簡單函數。但如果只提供一個參數，它將變成一個聚集合函式。
min(X,Y,...)    
返回參數的最小值。除數字外，參數可能是字串。它使用通常的排序次序來決定最小值。注意若 min()
有兩個或多個參數，則它是一個簡單函數。但如果只提供一個參數，它將變成一個聚集合函式。
nullif(X,Y)    
如果參數不同，則返回第一個參數，否則返回NULL。
quote(X)     該函數返回一個對其參數值進行處理後適合包含在其它 SQL
語句中的字串。單引號包括的字串將會按需要轉換成內部引用的格式。 BLOB 將會編碼為十六進位字面值。當前實現的 VACUUM 使用該函數。在寫觸發器來實現
“撤消/重做” 功能時，該函數也是很有用的。
random(*)     返回一個偽隨機數。結果在 -9223372036854775808 與
+9223372036854775807之間。
replace(X,Y,Z)     返回一個將 X 字串中每一個出現 Y 的位置替換為 Z
後的字串。它使用二進位對照序列進行比較。
randomblob(N)     返回一個 N 位元組長的包含偽隨機位元組的 BLOG。 N
應該是正整數。
round(X)
round(X,Y)     對 X 圓整成小數點後 Y 位。或省略 Y ，則預設Y 為
0。
rtrim(X)
rtrim(X,Y)     返回從 X 的右邊去除所有出現在 Y 中字元以後的字串。如果省略
Y，則去除空格。
soundex(X)     計算字串 X的讀音編碼。如果參數為 NULL，則返回 "?000"。預設情況下 SQLite
忽略該函數。僅當在編譯時間指定 -DSQLITE_SOUNDEX=1 時才有效。
sqlite_version(*)     返回當前啟動並執行 SQLite
庫的版本號碼。如："2.8.0"
substr(X,Y,Z)
substr(X,Y)     返回字串 X 從第 Y 個字元開始，長度為 Z
的字串。如果省略 Z， 將返回直到字串結尾的字串。 X 的第一個字元從 1 開始。如果 Y 是負數，則從右邊開始數。如果 X 是 UTF-8
字串，那麼，下標值將指實際的 UTF-8 characters 字元，而不是位元組。如果 X 是一個
BLOB，那麼下標就是指位元組。
trim(X)
trim(X,Y)     返回從字串 X 的兩頭去掉所有存在於 Y 中字元後的字串。如果省略
Y，則去空格。
typeof(X)     返回運算式 X 的類型。只可能是 "null", "integer", "real", "text", 以及
"blob"。 SQLite 的類型處理在 Datatypes in SQLite Version 3 中有說明。
upper(X)     返回字串
X 被轉換為大寫後的字串。它使用 C 庫函數 toupper() 實現，對於 UTF-8，某些字串可能不能正確轉換。
zeroblob(N)    
返回一個 N 位元組長、全部由 0x00 組成的 BLOB。 SQLite 或以很有效組織這些 zeroblob。它可以被用於為以後的使用預留空間。以後可以使用
incremental BLOB I/O 來寫入 BLOB 資料。
日期和時間函數

日期和時間函數在 SQLite
Wiki上。
聚集合函式

有下列預設可和的聚集合函式：可以使用 sqlite3_create_function() API
來增加其它的聚集合函式。

任何聚集合函式都只有一個參數。其參數前可以有一個 DISTINCT。如果有，重複的元素將會在傳遞給聚集合函式前過濾掉。如，函數
"count(distinct X)" 將只返回在 X 列上不重複的行的總數，而不是在該列上所有非空的行的總數
avg(X)     返回 X
列的一組中所有非空值的平均值。字串或 BLOB 等非數字值將被認為是 0。即使所有輸入都是整數， avg()
的結果也永遠是浮點數。
count(X)
count(*)     第一種形式返回在
X列上的一組中非空的行的總數。第二種（無任何參數）返回一組中所有的行數。
group_concat(X)
group_concat(X,Y)    
結果是一個所有非空的 X 串連起來的一個字串。如果有參數 Y，則它會做為串連 X 時的分隔字元。若省略Y ，則預設是逗號（,）。
max(X)    
返回一組中的最大值。使用通常的排序次序來確定最大值。
min(X)    
返回一組中除非空值以外的最小值。使用通常的排序次序來確實最小值。只有當整個組中所有值均為 NULL 時才會返回
NULL。
sum(X)
total(X)     返回一組中所有非空值的數值總和。如果在輸入中沒有非空值，則 sum() 會返回 NULL 而
total() 會返回 0.0。不僅能在 sum() 沒有對任何行求和時， NULL能給出有協助的結果，而且 SQL 標準也需要它。而且其它的 SQL
資料庫引擎也是這麼實現的。 SQLite 這麼做也是為了保持相容性。我們也提供了一個非標準的 total() 函數，以提供一個方便的途徑來繞過 SQL
語言的這一設計問題。

total() 的結果永遠是浮點數。 如果所有非空的輸入都是整數， sum() 的結果將是整數值。 若任何輸入給 sum()
的值是除整數及 NULL 以外的值， sum() 都將返回浮點數。這可能是最接近標準 sum() 的實現方式吧。

如果輸入全部是整數或
NULL，在結果溢出時 sum() 將會產生一個 "integer overflow" 異常。 而 total() 永遠不會。


限制筆數
select * from film limit 10;
sqlite3 film.db "select * from
film;"

輸出 HTML 表格：

sqlite3 -html film.db "select * from
film;"

將資料庫「倒出來」：

sqlite3 film.db ".dump" >
output.sql

利用輸出的資料，建立一個一模一樣的資料庫（加上以上指令，就是標準的SQLDatabase Backup了）：

sqlite3
film.db <
output.sql

在大量插入資料時，你可能會需要先打這個指令：

begin;

插入完資料後要記得打這個指令，資料才會寫進資料庫中：

commit;


SQLite內建函數表
算術函數
abs(X)   
返回給定數字運算式的絕對值。
max(X,Y[,...])    返回運算式的最大值。
min(X,Y[,...])   
返回運算式的最小值。
random(*)    返回隨機數。
round(X[,Y])   
返回數字運算式並四捨五入為指定的長度或精度。
字元處理函數
length(X)    返回給定字串運算式的字元個數。
lower(X)   
將大寫字元資料轉換為小寫字元資料後返回字元運算式。
upper(X)   
返回將小寫字元資料轉換為大寫的字元運算式。
substr(X,Y,Z)    返回運算式的一部分。
randstr()  

quote(A)  
like(A,B)    確定給定的字串是否與指定的模式比對。
glob(A,B)  

條件判斷函數
coalesce(X,Y[,...])  
ifnull(X,Y)  
nullif(X,Y)  

集合函數
avg(X)    返回組中值的平均值。
count(X)    返回組中項目的數量。
max(X)   
返回組中值的最大值。
min(X)    返回組中值的最小值。
sum(X)   
返回運算式中所有值的和。
其他函數
typeof(X)    返回資料的類型。
last_insert_rowid()   
返回最後插入的資料的ID。
sqlite_version(*)    返回SQLite的版本。
change_count()   
返回受上一語句影響的行數。
last_statement_change_count()