概況
SQLite獨(dú)有的功能
Zero-Configuration : 配置簡單
Serverless：基于文件的，沒有類似于SQLServer之類的服務(wù)端
SingleDatabase File：單個數(shù)據(jù)庫文件
Stable Cross-Platform Database File: 因?yàn)楹唵?，因此跨平臺兼容性好
Compact: 整個SQLITE庫文件大小很小，運(yùn)行起來所需的資源也很少
Manifest typing：為了實(shí)現(xiàn)簡單，沒有使用像其他數(shù)據(jù)庫引擎使用的靜態(tài)類型機(jī)制，二是采用了存儲類型機(jī)制，只有NULL/INTEGER/REAL/TEXT/BLOB這五種存儲類型(storage class)，存儲類型比數(shù)據(jù)類型更泛泛。
https://sqlite.org/datatype3.html
Variable-length records: 記錄長度是可變的，而許多的其他的數(shù)據(jù)庫實(shí)現(xiàn)都給每行分配一個固定長度的空間。
Readable source code：源代碼開源
SQL statements compile into virtual machine code:執(zhí)行優(yōu)化
Public domain：沒有任何授權(quán)上的限制，但是文檔和擴(kuò)展不包括在內(nèi)
SQL language extensions : 提供了靈活的擴(kuò)展機(jī)制
SQLite沒有實(shí)現(xiàn)的功能
RIGHT and FULL [OUTER] JOIN : 個人猜測應(yīng)該是為了方便實(shí)現(xiàn)，只實(shí)現(xiàn)了LEFT JOIN和INNER JOIN，但是可以通過交換表名用LEFT JOIN來實(shí)現(xiàn)RIGHT JOIN，而FULL [OUTER] JOIN可以利用 ((LEFT JOIN) UNION (RIGHT JOIN))來實(shí)現(xiàn)。這里補(bǔ)充一下，FULL OUTER JOIN有時會簡稱為FULL JOIN。
Complete ALTER TABLE support : 對表的操作，只支持對表名進(jìn)行重命名和添加數(shù)據(jù)列，不支持drop table, alter column, 以及add constraint，統(tǒng)統(tǒng)只能通過刪除-新建的方式來達(dá)成想要的改變。
Complete trigger Support : SQLITE沒有實(shí)現(xiàn)完整的觸發(fā)器機(jī)制，只支持行級觸發(fā)器，而不支持語句級的觸發(fā)器，也就是粒度不夠。
Writing to VIEWS : SQLITE中的視圖是只讀的，你不能在VIEW中執(zhí)行一個DELETE/INSERT/UPDATE操作
GRANT and REVOKE：sqlite只是內(nèi)存+磁盤文件，沒有提供除了底層系統(tǒng)所提供的權(quán)限控制機(jī)制以外的任何權(quán)限控制機(jī)制。
Storage Procedure: 不支持存儲過程，至于存儲過程能否提升數(shù)據(jù)庫訪問的性能，It's debatable，看具體的實(shí)現(xiàn)。
PRAGMA聲明
PRAGMA聲明是一個SQL擴(kuò)展，專門用來修改SQLITE庫的操作或者查詢一些內(nèi)部數(shù)據(jù)(非表數(shù)據(jù))。和其他SQLITE命令一樣，PRAGMA聲明也是用類似SELECT、INSERT關(guān)鍵字來發(fā)出的，但是又有以下的不同：
- 某些PRAGMA聲明可能會在將來的版本移除，PRAGMA機(jī)制并不保證后向兼容性
- 如果使用未知的PRAGMA聲明，并不會打印錯誤信息。未知的PRAGMA直接被忽略掉。
- 有些PRAGMA在sql語句的編譯階段起作用，而不是執(zhí)行階段。這意味著如果使用sqlite3_prepare()、sqlite3_step()、sqlite3_finalize()接口的話，PRAGMA可能在sqlite3_prepare()調(diào)用的時候運(yùn)行，而不是和常規(guī)的sql語句一樣在sqlite3_step()調(diào)用的時候運(yùn)行。
- PRAGMA命令只對sqlite起作用，當(dāng)然這并不意味著其他數(shù)據(jù)庫引擎(這里需要對DBMS的架構(gòu)有所了解，數(shù)據(jù)庫引擎（或存儲引擎）是數(shù)據(jù)庫管理系統(tǒng)（DBMS）用于從數(shù)據(jù)庫創(chuàng)建，讀取，更新和刪除（CRUD）數(shù)據(jù)的底層軟件組件)不支持PRAGMA，又或者其他實(shí)現(xiàn)不和這里描述的PRAGMA沒有共同之處，只是說PRAGMA不是標(biāo)準(zhǔn)的一部分，也就是PRAGMA的實(shí)現(xiàn)會因數(shù)據(jù)庫引擎不同而不同。
PRAGMA命令語法
pragma-stmt ::= PRAGMA [schema-name.]pragma-name[=pragma-value|(pragma-value)]
pragma-value ::= signed-number|name|string-literal
signed-number ::= [+|-]numeric-literal
其中schema-name是當(dāng)前attached（這個概念也是針對sqlite的，但不一定是獨(dú)有的）的數(shù)據(jù)庫，通常有main和temp，如果省略schema-name，那么系統(tǒng)會假定采用main。有的PRAGMA語句在schema-name沒有任何實(shí)際意義的時候會忽略掉schema-name，注意這是忽略而不是省略。
PRAGMA 函數(shù)
返回結(jié)果并且沒有副作用的PRAGMA可以從普通的SELECT語句中作為表值函數(shù)(table-valued functions)來訪問。而對應(yīng)的函數(shù)名稱則是前綴pragma_加上pragma-name，而pragma參數(shù)和schema，如果存在的話則當(dāng)做表值函數(shù)的參數(shù)傳遞。
這樣的好處是，可以使用SELECT語句的where語句，可以使用并集函數(shù)，并且可以使用別的表值函數(shù)作為輸入。
額外需要注意的是：
表值函數(shù)只為了內(nèi)置的PRAGMA存在，使用SQLITE_FCNTL_PRAGMA文件控制定義的PRAGMA則沒有對應(yīng)的表值函數(shù)
只有能返回結(jié)果并且無副作用的PRAGMA有對應(yīng)的表值函數(shù)
This feature could be used to implement information schema by first creating a separate schema using
ATTACH ':memory:' AS 'information_schema';
Then creating VIEWs in that schema that implement the official information schema tables using table-valued PRAGMA functions.
這個功能是實(shí)驗(yàn)性的，可能會在之后的版本發(fā)生改變
這個功能是在3.16.0后才添加的
函數(shù)
核心函數(shù)
| 函數(shù)名 | 描述 |
|---|---|
| abs(X) | 該函數(shù)返回數(shù)值參數(shù)X的絕對值，如果X為NULL，則返回NULL，如果X為不能轉(zhuǎn)換成數(shù)值的字符串，則返回0，如果X值是Integer類型，并且轉(zhuǎn)換后超出Integer的上限，則拋出"Integer Overflow"的異常。 |
| changes() | 該函數(shù)返回最近執(zhí)行的INSERT、UPDATE或DELETE語句所影響的數(shù)據(jù)行數(shù)。我們也可以通過執(zhí)行C/C++函數(shù)sqlite3_changes()得到相同的結(jié)果。 |
| char(X1,X2,...,XN) | SELECT char(67,72,65,82); > CHAR，其實(shí)就是把X1、X2...XN對應(yīng)的ascii碼拼成一個字符串，這要求所有的參數(shù)都是integer |
| coalesce(X,Y,...) | 返回函數(shù)參數(shù)中第一個非NULL的參數(shù)，如果參數(shù)都是NULL，則返回NULL。該函數(shù)至少2個參數(shù)。 |
| glob(x,y) | The glob(X,Y) function is equivalent to the expression "Y GLOB X". Note that the X and Y arguments are reversed in the glob() function relative to the infix GLOB operator. If the sqlite3_create_function() interface is used to override the glob(X,Y) function with an alternative implementation then the GLOB operator will invoke the alternative implementation. |
| hex(X) | The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob. |
| ifnull(X,Y) | 該函數(shù)等同于兩個參數(shù)的coalesce()函數(shù)，即返回第一個不為NULL的函數(shù)參數(shù)，如果兩個均為NULL，則返回NULL。 |
| instr(X,Y) | instr(X,Y)函數(shù)查找字符串X中字符串Y的第一次出現(xiàn)，并返回這個位置前的字符的數(shù)目加1，或者如果Y在X中沒有找到，則返回0?；蛘?，如果X和Y都是BLOB類型，則instr(X,Y)則是根據(jù)字節(jié)來實(shí)現(xiàn)前面的操作。如果兩個參數(shù)X和Y都是非NULL并且不是BLOB類型，那么X和Y則被當(dāng)成字符類型。如果instr(X,Y)中的X或Y是NULL，則結(jié)果為NULL。 |
| last_insert_rowid() | 函數(shù)返回調(diào)用這個函數(shù)之前最后一條插入的行的ROWID，last_insert_rowid()函數(shù)其實(shí)是封裝了sqlite3_last_insert_rowid() |
| length(X) | 如果參數(shù)X為字符串，則返回字符的數(shù)量，如果為數(shù)值，則返回該參數(shù)的字符串表示形式的長度，如果X為NULL，則返回NULL。 |
| like(X,Y[,z]) | 這個函數(shù)用來實(shí)現(xiàn)'Y LIKE X [ESCAPE Z]'表達(dá)式，因此可以通過使用sqlite3_create_function()函數(shù)來覆寫like()函數(shù)來改變LIKE操作符的行為 |
| likelihood(X,Y) | Y只能在0.0和1.0的閉區(qū)間之間取值。The purpose of the likelihood(X,Y) function is to provide a hint to the query planner that the argument X is a boolean that is true with a probability of approximately Y |
| likely(X) | likelihood(X,0.9375)的速記寫法 |
| load_extension(X[,Y]) | 加載擴(kuò)展，Y為擴(kuò)展的入口點(diǎn) |
| lower(X) | 返回函數(shù)參數(shù)X的小寫形式，缺省情況下，該函數(shù)只能應(yīng)用于ASCII字符。 |
| ltrim(X[,Y]) | 如果沒有可選參數(shù)Y，該函數(shù)將移除參數(shù)X左側(cè)的所有空格符。如果有參數(shù)Y，則移除X左側(cè)的任意在Y中出現(xiàn)的字符。最后返回移除后的字符串。 |
| max(X,Y,...) | 返回函數(shù)參數(shù)中的最大值，如果有任何一個參數(shù)為NULL，則返回NULL。 |
| min(X,Y,...) | 返回函數(shù)參數(shù)中的最小值，如果有任何一個參數(shù)為NULL，則返回NULL。 |
| nullif(X,Y) | 如果函數(shù)參數(shù)相同，返回NULL，否則返回第一個參數(shù)。 |
| printf(FORMAT,...) | The printf(FORMAT,...) SQL function works like the sqlite3_mprintf() C-language function and the printf() function from the standard C library. The first argument is a format string that specifies how to construct the output string using values taken from subsequent arguments. If the FORMAT argument is missing or NULL then the result is NULL. The %n format is silently ignored and does not consume an argument. The %p format is an alias for %X. The %z format is interchangeable with %s. If there are too few arguments in the argument list, missing arguments are assumed to have a NULL value, which is translated into 0 or 0.0 for numeric formats or an empty string for %s. |
| quote(X) | The quote(X) function returns the text of an SQL literal which is the value of its argument suitable for inclusion into an SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. Strings with embedded NUL characters cannot be represented as string literals in SQL and hence the returned string literal is truncated prior to the first NUL. |
| random() | 返回整型的偽隨機(jī)數(shù)。 |
| randomblob(N) | 返回N個任意的字節(jié)，如果N小于1，則范圍一個長度為1的字節(jié) |
| replace(X,Y,Z) | 將字符串類型的函數(shù)參數(shù)X中所有子字符串Y替換為字符串Z，最后返回替換后的字符串，源字符串X保持不變。 |
| round(X[,Y]) | 返回數(shù)值參數(shù)X被四舍五入到Y(jié)刻度的值，如果參數(shù)Y不存在，缺省參數(shù)值為0。 |
| rtrim(X[,Y]) | 如果沒有可選參數(shù)Y，該函數(shù)將移除參數(shù)X右側(cè)的所有空格符。如果有參數(shù)Y，則移除X右側(cè)的任意在Y中出現(xiàn)的字符。最后返回移除后的字符串。 |
| soundex(X) | The soundex(X) function returns a string that is the soundex encoding of the string X. The string "?000" is returned if the argument is NULL or contains no ASCII alphabetic characters. This function is omitted from SQLite by default. It is only available if the SQLITE_SOUNDEX compile-time option is used when SQLite is built. |
| sqlite_compileoption_get(N) | The sqlite_compileoption_get() SQL function is a wrapper around the sqlite3_compileoption_get() C/C++ function. This routine returns the N-th compile-time option used to build SQLite or NULL if N is out of range. See also the compile_options pragma. |
| sqlite_compileoption_used(X) | The sqlite_compileoption_used() SQL function is a wrapper around the sqlite3_compileoption_used() C/C++ function. When the argument X to sqlite_compileoption_used(X) is a string which is the name of a compile-time option, this routine returns true (1) or false (0) depending on whether or not that option was used during the build. |
| sqlite_source_id() | The sqlite_source_id() function returns a string that identifies the specific version of the source code that was used to build the SQLite library. The string returned by sqlite_source_id() is the date and time that the source code was checked in followed by the SHA1 hash for that check-in. This function is an SQL wrapper around the sqlite3_sourceid() C interface. |
| sqlite_version() | The sqlite_version() function returns the version string for the SQLite library that is running. This function is an SQL wrapper around the sqlite3_libversion() C-interface. |
| substr(X,Y[,Z]) | 返回函數(shù)參數(shù)X的子字符串，從第Y位開始(X中的第一個字符位置為1)截取Z長度的字符，如果忽略Z參數(shù)，則取第Y個字符后面的所有字符。如果Z的值為負(fù)數(shù)，則從第Y位開始，向左截取abs(Z)個字符。如果Y值為負(fù)數(shù)，則從X字符串的尾部開始計數(shù)到第abs(Y)的位置開始。 |
| total_changes() | 該函數(shù)返回自從該連接被打開時起，INSERT、UPDATE和DELETE語句總共影響的行數(shù)。我們也可以通過C/C++接口函數(shù)sqlite3_total_changes()得到相同的結(jié)果。 |
| trim(x[,y]) | 如果沒有可選參數(shù)Y，該函數(shù)將移除參數(shù)X兩側(cè)的所有空格符。如果有參數(shù)Y，則移除X兩側(cè)的任意在Y中出現(xiàn)的字符。最后返回移除后的字符串。 |
| upper(X) | 返回函數(shù)參數(shù)X的大寫形式，缺省情況下，該函數(shù)只能應(yīng)用于ASCII字符。 |
| typeof(X) | 返回函數(shù)參數(shù)數(shù)據(jù)類型的字符串表示形式，如"Integer、text、real、null"等。 |
| unicode(X) | The unicode(X) function returns the numeric unicode code point corresponding to the first character of the string X. If the argument to unicode(X) is not a string then the result is undefined. |
| unlikely(X) | likelihood(X, 0.0625)的速記寫法(short-hand) |
| upper(X) | The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to their upper-case equivalent. |
| zeroblob(N) | The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using incremental BLOB I/O. This SQL function is implemented using the sqlite3_result_zeroblob() routine from the C/C++ interface. |
并集函數(shù)
| 函數(shù)名 | 描述 |
|---|---|
| avg(x) | 該函數(shù)返回在同一組內(nèi)參數(shù)字段的平均值。對于不能轉(zhuǎn)換為數(shù)字值的String和BLOB類型的字段值，如'HELLO'，SQLite會將其視為0。avg函數(shù)的結(jié)果總是浮點(diǎn)型，唯一的例外是所有的字段值均為NULL，那樣該函數(shù)的結(jié)果也為NULL。 |
| count(x|*) | count(x)函數(shù)返回在同一組內(nèi)，x字段中值不等于NULL的行數(shù)。count(*)函數(shù)返回在同一組內(nèi)的數(shù)據(jù)行數(shù)。 |
| group_concat(x[,y]) | 該函數(shù)返回一個字符串，該字符串將會連接所有非NULL的x值。該函數(shù)的y參數(shù)將作為每個x值之間的分隔符，如果在調(diào)用時忽略該參數(shù)，在連接時將使用缺省分隔符","。再有就是各個字符串之間的連接順序是不確定的。 |
| max(x) | 該函數(shù)返回同一組內(nèi)的x字段的最大值，如果該字段的所有值均為NULL，該函數(shù)也返回NULL。 |
| min(x) | 該函數(shù)返回同一組內(nèi)的x字段的最小值，如果該字段的所有值均為NULL，該函數(shù)也返回NULL。 |
| sum(x) | 該函數(shù)返回同一組內(nèi)的x字段值的總和，如果字段值均為NULL，該函數(shù)也返回NULL。如果所有的x字段值均為整型或者NULL，該函數(shù)返回整型值，否則就 返回浮點(diǎn)型數(shù)值。最后需要指出的是，如果所有的數(shù)據(jù)值均為整型，一旦結(jié)果超過上限時將會拋出"integer overflow"的異常。 |
| total(x) | 該函數(shù)不屬于標(biāo)準(zhǔn)SQL，其功能和sum基本相同，只是計算結(jié)果比sum更為合理。比如當(dāng)所有字段值均為NULL時，和sum不同的是，該函數(shù)返回0.0。再有就是該函數(shù)始終返回浮點(diǎn)型數(shù)值。該函數(shù)始終都不會拋出異常。 |
時間和日期函數(shù)
時間這部分因?yàn)榧?xì)節(jié)比較多，就不詳細(xì)翻譯了。
sqlite支持以下五種時間日期函數(shù)
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)
Android相關(guān)的細(xì)節(jié)
可以在/path/to/aosp/external/sqlite/中查看sqlite的源碼
根據(jù)/path/to/aosp/external/sqlite/dist/Android.bp中的cflags可以得知：
LOAD_EXTENSION()函數(shù)
PRAGMA COMPILE_OPTIONS
都默認(rèn)被禁用了，所以如果需要使用這些功能，可能需要在應(yīng)用內(nèi)置自己編譯的sqlite。
參考
creating-stored-procedure-and-sqlite
sqlite changelog
Distinctive Features of SQLite
Date and Time functions
char()
