SQLite SQL語句結構詳解

Replace  Replace  Replace  Replace  Replace  Replace

SELECT

sql-statement ::=	SELECT[ALL|DISTINCT]result[FROMtable-list] [WHEREexpr] [GROUP BYexpr-list] [HAVINGexpr] [compound-opselect]* [ORDER BYsort-expr-list] [LIMITinteger[(OFFSET|,)integer]]
result ::=	result-column[,result-column]*
result-column ::=	*|table-name.*|expr[[AS]string]
table-list ::=	table[join-optablejoin-args]*
table ::=	table-name[ASalias]| (select)[ASalias]
join-op ::=	,|[NATURAL][LEFT|RIGHT|FULL][OUTER|INNER|CROSS]JOIN
join-args ::=	[ONexpr][USING (id-list)]
sort-expr-list ::=	expr[sort-order][,expr[sort-order]]*
sort-order ::=	[COLLATEcollation-name][ASC|DESC]
compound_op ::=	UNION|UNION ALL|INTERSECT|EXCEPT

SELECT語句用於查詢資料庫。一條SELECT命令的返回結果是零或多行每行有固定欄位數的資料。欄位的數目由在SELECT和FROM之間的表示式列表定義。任意的表示式都可以被用作結果。若表示式是 *則表示所有表的所有欄位。若表示式是表的名字後接.*則結果為該表中的所有欄位。

DISTINCT關鍵字的使用會使返回的結果是原結果的一個不含相同行的子集。NULL值被認為是相同的。預設行為是返回所有的行，為清楚起見可以使用關鍵字ALL。

查詢對FROM之後定義的一個或多個表進行。若多個表用逗號連線，則查詢針對它們的交叉連線。所有的SQL-92連線語法均可以用於定義連線。圓括號中的副查詢可能被FROM子句中的任意表名替代。當結果中僅有一行包含表示式列表中的結果的行時，整個的FROM子句會被忽略。

WHERE子句可以限定查詢操作的行數目。

GROUP BY子句將一行或多行結果合成單行輸出。當結果有聚集函式時這將尤其有用。GROUP BY子句的表示式不須是出現在結果中的表示式。HAVING子句與WHERE相似，只是HAVING用於過濾分組建立的行。HAVING子句可能包含值，甚至是不出現在結果中的聚集函式。

ORDER BY子句對所得結果根據表示式排序。表示式無須是簡單SELECT的結果，但在複合SELECT中每個表示式必須精確對應一個結果欄位。每個表示式可能跟隨一個可選的COLLATE關鍵字以及用於排序文字的比較函式名稱和/或關鍵字ASC或DESC，用於說明排序規則。

LIMIT子句限定行數的最大值。負的LIMIT表示無上限。後跟可選的OFFSET說明跳過結果集中的前多少行。在一個複合查詢中，LIMIT子句只允許出現在最終SELECT語句中。限定對於所有的查詢均適用，而不僅僅是新增了LIMIT子句的那一行。注意OFFSET關鍵字用於LIMIT子句中，則限制值是第一個數字，而偏移量(offset)是第二個數字。若用逗號替代OFFSET關鍵字，則偏移量是第一個數字而限制值是第二個數字。這是為了加強對遺留的SQL資料庫的相容而有意造成的矛盾。

複合的SELECT由兩個或更多簡單SELECT經由UNION, UNION ALL, INTERSECT, EXCEPT中的一個運算子連線而成。在一個複合SELECT中，各個SELECT需指定相同個數的結果欄位。僅允許一個ORDER BY子句出現在SELECT的末尾。UNION和UNION ALL運算子從左至右將所有SELECT的結果合成一個大的表。二者的區別在於UNION的所有結果行是不相同的而 UNION ALL允許重複行。INTERSECT運算子取左右兩個SELECT結果的交。EXCEPT從左邊SELECT的結果中除掉右邊SELECT的結果。三個或更多SELECT複合時，它們從左至右結合。

 

UPDATE

sql-statement ::=	UPDATE[ORconflict-algorithm][database-name.]table-name SETassignment[,assignment]* [WHEREexpr]
assignment ::=	column-name=expr

UPDATE語句用於改變表中所選行的欄位值。每個UPDATE的賦值的等號左邊為欄位名而右邊為任意表示式。表示式可以使用其它欄位的值。所有的表示式將在賦值之前求出結果。可以使用WHERE子句限定需要改變的行。

在使用這一命令時，利用可選的ON CONFLICT子句可以定義替代的約束衝突判定演算法。更多資訊，參見 ON CONFLICT。

 

REPLACE

sql-statement ::=

REPLACE INTO[database-name.]table-name[(column-list)]VALUES (value-list)| REPLACE INTO[database-name.]table-name[(column-list)]select-statement

REPLACE命令用於替代INSERT的"INSERT OR REPLACE"變體，以更好的相容MySQL。檢視 INSERT命令文件獲取更多資訊。

INSERT

sql-statement ::=

INSERT[ORconflict-algorithm]INTO[database-name.]table-name[(column-list)]VALUES(value-list)| INSERT[ORconflict-algorithm]INTO[database-name.]table-name[(column-list)]select-statement

INSERT語句有兩種基本形式。一種帶有"VALUES"關鍵字，在已有表中插入一個新的行。若不定義欄位列表，那麼值的數目將與表中的欄位數目相同。否則值的數目須與欄位列表中的欄位數目相同。不在欄位列表中的欄位被賦予預設值或NULL(當未定義預設值)。

INSERT的第二種形式從SELECT語句中獲取資料。若未定義欄位列表，則從SELECT得到的欄位的數目必須與表中的欄位數目相同，否則應與定義的欄位列表中的欄位數目相同。SELECT的每一行結果在表中插入一個新的條目。SELECT可以是簡單的或者複合的。如果SELECT語句帶有ORDER BY子句，ORDER BY會被忽略。

在使用這一命令時，利用可選的ON CONFLICT子句可以定義替代的約束衝突判定演算法。更多資訊，參見 ON CONFLICT 。為了相容MySQL，可以使用REPLACE代替"INSERT OR REPLACE".

 

DELETE

sql-statement ::=

DELETE FROM[database-name.]table-name[WHEREexpr]

DELETE命令用於從表中刪除記錄。命令包含"DELETE FROM"關鍵字以及需要刪除的記錄所在的表名。

若不使用WHERE子句，表中的所有行將全部被刪除。否則僅刪除符合條件的行。

 

表示式

expr ::=

exprbinary-opexpr| expr[NOT]like-opexpr[ESCAPEexpr]| unary-opexpr| (expr)| column-name| table-name.column-name| database-name.table-name.column-name| literal-value| parameter| function-name(expr-list|*)| exprISNULL| exprNOTNULL| expr[NOT]BETWEENexprANDexpr| expr[NOT]IN (value-list)| expr[NOT]IN (select-statement)| expr[NOT]IN[database-name.]table-name| [EXISTS](select-statement)| CASE[expr](WHENexprTHENexpr)+[ELSEexpr]END| CAST (exprAStype)

like-op ::=

LIKE|GLOB|REGEXP

這一節與其它的各節有所不同。我們討論的不是一個單一的SQL命令，而是作為其他大部分命令的一部分的表示式。

SQLite支援如下的二元運算子，按優先順序由高至低排列：

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

所支援的一元運算子：

- + ! ~

注意等號和“不等”號的兩個變種。等號可以是 =或==. “不等”號可以是!=或<>. ||為“連線符”——它將兩個字串連線起來。 %輸出左邊部分以右邊部分為模取模得到的餘數。

二元運算子的結果均為數字，除了||連線符，它給出字串結果。

文字值(literal value)是一個整數或浮點數。可以使用科學計數法。"."符號總是被當作小數點即使本地設定中用","來表示小數點 ——用","表示小數點會造成歧義。字串常量由字串加單引號(')構成。字串內部的單引號可像Pascal 中一樣用兩個單引號來表示。C風格的加反斜線的表示法由於不是標準SQL而不被支援。 BLOB文字是以"x"或"X"開頭的含有十六進位制文字資訊的文字值 For example:

X'53514697465'

文字值同樣可以為"NULL".

表示式中插入文字值佔位符的引數可以使用 sqlite3_bind API函式在執行時插入。引數可以是如下幾種形式：

?NNN		問號跟隨數字NNN為第NNN個引數佔位。NNN需介於1和999之間。
?		不加數字的問號為下一個引數佔位。
:AAAA		冒號帶一個識別符號名稱為一個名為AAAA的引數佔位。命名的引數同樣可以使用序號佔位，被賦予的引數序號為下一個尚未被使用的序號。建議不要混合使用命名代表的引數和序號代表的引數以免引起混淆。
$AAAA		$符號帶一個識別符號名稱也可以為一個名為AAAA的引數佔位。在這一情況下識別符號名稱可以包括一個或更多的"::" 以及包含任何文字的"(...)"字尾。該語法是Tcl程式語言中的一個可變形式。

不使用sqlite3_bind賦值的引數被視為NULL.

LIKE運算子進行模式匹配比較。運算子右邊為進行匹配的模式而左邊為需進行匹配的字串。 模式中的百分號%匹配結果中的零或任意多個字元。下劃線 _匹配任意單個字元。其他的任意字元匹配本身或等同的大/小寫字元。 (即不區分大小寫的匹配)。 (一個bug: SQLite僅對7-bit拉丁字元支援不區分大小寫匹配。這是由於 LIKE運算子對8-bit iso8859字元或UTF-8字元是大小寫敏感的。例如，表示式'a' LIKE 'A' 的值為真而'?' LIKE '?'為假)。

如果使用可選的ESCAPE子句，則跟隨ESCAPE關鍵字的必須是一個有一個字元的字串。這一字元(逃逸字元)可用於LIKE模式中，以代替百分號或下劃線。逃逸字元後跟百分號，下劃線或它本身代表字串中的百分號，下劃線或逃逸字元。插入的LIKE運算子功能通過呼叫使用者函式 like(X,Y)來實現。

當使用可選的ESCAPE子句，它對函式給出第三個引數，LIKE的功能可以通過過載SQL函式like()進行改變。

GLOB運算子與LIKE相似，但它使用Unix檔案 globbing 語法作為萬用字元。還有一點不同是GLOB對大小寫敏感。 GLOB和LIKE都可以字首NOT關鍵字構成相反的意思。插入的GLOB運算子功能通過呼叫使用者函式glob(X,Y) 可以通過過載函式改變GLOB的功能。

REGEXP運算子是使用者函式regexp()的一個特殊的代表符號。預設情況下regexp()函式不被定義，所以使用REGEXP運算子會報錯。當執行時存在使用者定義的"regexp"函式的定義，則呼叫該函式以實現REGEXP運算子功能。

欄位名可以是CREATE TABLE語句定義的任何名字或如下幾個特殊識別符號之一"ROWID", "OID", 以及"_ROWID_".這些特殊識別符號均代表每個表每一行關聯的那個唯一隨機整數鍵("row key")。僅僅在CREATE TABLE語句沒有對這些特殊識別符號的真實欄位予以定義的情況下，它們才代表"row key"。它們與只讀欄位類似，可以像任何正常欄位一樣使用，除了在UPDATE或INSERT語句中(即是說你不能新增或更改row key)。 "SELECT * ..."不返回row key.

SELECT語句可以在表示式中出現，作為IN運算子的右邊運算量，作為一個純量，或作為EXISTS運算子的運算量。當作純量或IN的運算量時，SELECT語句的結果僅允許有一個欄位，可以使用複合的SELECT(用UNION或 EXCEPT等關鍵字連線)。作為EXISTS運算子的運算量時，SELECT結果中的欄位被忽略，在結果為空時表示式為假，反之為真。若SELECT表示式代表的查詢中不含有引用值的部分，則它將在處理其它事務之前被計算，並且結果在必要時會被重複使用。若SELECT表示式含從其它查詢中得到的變數，在每一次使用時該表示式均被重新計算。

當SELECT作為IN運算子的右運算量，在左邊的運算量是SELECT產生的任意一個值時，表示式返回TRUE。IN運算子前可以加NOT 構成相反的意思。

當SELECT與表示式一同出現且不在IN的右邊，則SELECT結果的第一行作為表示式中使用的值。SELECT返回的結果在第一行以後的部分被忽略。返回結果為空時SELECT語句的值為NULL.

CAST表示式將的資料型別改為宣告的型別。 可以是CREATE TABLE語句欄位定義部分定義的對該欄位有效的任意非空資料型別。

表示式支援簡單函式和聚集函式。簡單函式直接從輸入獲得結果，可用於任何表示式中。聚集函式使用結果集中的所有行計算結果，僅用於SELECT語句中。

T下面這些函式是預設可用的。可以使用C語言寫出其它的函式然後使用sqlite3_create_function() API函式新增到資料庫引擎中。

abs(X)	返回引數X的絕對值。
coalesce(X,Y,...)	返回第一個非空引數的副本。若所有的引數均為NULL，返回NULL。至少2個引數。
glob(X,Y)	用於實現SQLite的 "X GLOB Y"語法。可使用 sqlite3_create_function() 過載該函式從而改變GLOB運算子的功能。
ifnull(X,Y)	返回第一個非空引數的副本。 若兩個引數均為NULL，返回NULL。與上面的 coalesce()類似。
last_insert_rowid()	返回當前資料庫連線最後插入行的ROWID。sqlite_last_insert_rowid() API函式同樣可用於得到該值。
length(X)	返回X的長度，以字元計。如果SQLite被配置為支援UTF-8，則返回UTF-8字元數而不是位元組數。
like(X,Y [,Z])	用於實現SQL語法"X LIKE Y [ESCAPE Z]".若使用可選的ESCAPE子句，則函式被賦予三個引數，否則只有兩個。可使用sqlite3_create_function() 過載該函式從而改變LIKE運算子的功能。
lower(X)	返回X字串的所有字元小寫化版本。這一轉換使用C語言庫的tolower()函式，對UTF-8字元不能提供好的支援。
max(X,Y,...)	返回最大值。引數可以不僅僅為數字，可以為字串。大小順序由常用的排序法則決定。注意，max()在有2個或更多引數時為簡單函式，但當僅給出一個引數時它變為聚集函式。
min(X,Y,...)	返回最小值。與max()類似。
nullif(X,Y)	當兩引數不同時返回X，否則返回NULL.
quote(X)	返回引數的適於插入其它SQL語句中的值。字串會被新增單引號，在內部的引號前會加入逃逸符號。 BLOB被編碼為十六進位制文字。當前的VACUUM使用這一函式實現。在使用觸發器實現撤銷/重做功能時這一函式也很有用。
random(*)	返回介於-2147483648和 +2147483647之間的隨機整數。
round(X) round(X,Y)	將X四捨五入，保留小數點後Y位。若忽略Y引數，則預設其為0。
soundex(X)	計算字串X的soundex編碼。引數為NULL時返回字串"?000".預設的SQLite是不支援該函式的，當編譯時選項 -DSQLITE_SOUNDEX=1 時該函式才可用。
sqlite_version(*)	返回所執行的SQLite庫的版本號字串。如 "2.8.0"。
substr(X,Y,Z)	返回輸入字串X中以第Y個字元開始，Z個字元長的子串。 X最左端的字元序號為1。若Y為負，則從右至左數起。若SQLite配置支援UTF-8，則“字元”代表的是UTF-8字元而非位元組。
typeof(X)	返回表示式X的型別。返回值可能為"null", "integer", "real", "text", 以及 "blob". SQLite的型別處理參見SQLite3的資料型別.
upper(X)	返回X字串的所有字元大寫化版本。這一轉換使用C語言庫的toupper()函式，對UTF-8字元不能提供好的支援。

以下是預設可用的聚集函式列表。可以使用C語言寫出其它的聚集函式然後使用sqlite3_create_function() API函式新增到資料庫引擎中。

在單引數聚集函式中，引數可以加字首DISTINCT。這時重複引數會被過濾掉，然後才穿入到函式中。例如，函式"count(distinct X)"返回欄位X的不重複非空值的個數，而不是欄位X的全部非空值。

avg(X)	返回一組中非空的X的平均值。非數字值作0處理。avg()的結果總是一個浮點數，即使所有的輸入變數都是整數。
count(X) count(*)	返回一組中X是非空值的次數的第一種形式。第二種形式(不帶引數)返回該組中的行數。
max(X)	返回一組中的最大值。大小由常用排序法決定。
min(X)	返回一組中最小的非空值。大小由常用排序法決定。僅在所有值為空時返回NULL。
sum(X) total(X)	返回一組中所有非空值的數字和。若沒有非空行，sum()返回NULL而total()返回0.0. NULL通常情況下並不是對於“沒有行”的和的一個有意義的結果，但SQL標準如此要求，且大部分其它SQL資料庫引擎這樣定義sum()，所以SQLite 也如此定義以保證相容。我們提供非標準的total()函式作為解決該SQL語言設計問題的一個簡易方法。 total()的返回值式中為浮點數。sum()可以為整數，當所有非空輸入均為整數時，和是精確的。 若sum()的任意一個輸入既非整數也非NULL或計算中產生整數型別的溢位時，sum()返回接近真和的浮點數。