SQLite Learning、SQL Query Optimization In Multiple Rule

catalog

1. SQLite簡介
2. Sqlite安裝
3. SQLite Programing
4. SQLite statements

 

1. SQLite簡介

SQLite是一款輕型的資料庫，是遵守ACID的關係型資料庫管理系統，它包含在一個相對小的C庫中。它是D.RichardHipp建立的公有領域專案。它的設計目標是嵌入式的，而且目前已經在很多嵌入式產品中使用了它，它佔用資源非常的低，在嵌入式裝置中，可能只需要幾百K的記憶體就夠了。它能夠支援Windows/Linux/Unix等等主流的作業系統，同時能夠跟很多程式語言相結合，比如 Tcl、C#、PHP、Java等，還有ODBC介面，同樣比起Mysql、PostgreSQL這兩款開源的世界著名資料庫管理系統來講，它的處理速度比他們都快
SQLite 支援跨平臺，操作簡單，能夠使用很多語言直接建立資料庫，而不象Access一樣需要Office的支援。如果是個很小型的應用，或者你想做嵌入式開發，沒有合適的資料庫系統，那麼可以考慮使用SQLite

0x1: SQLite原理

不像常見的客戶-伺服器範例，SQLite引擎不是個程式與之通訊的獨立程式，而是連線到程式中成為它的一個主要部分。所以主要的通訊協議是在程式語言內的直接API呼叫。這在消耗總量、延遲時間和整體簡單性上有積極的作用。整個資料庫(定義、表、索引和資料本身)都在宿主主機上儲存在一個單一的檔案中。它的簡單的設計是通過在開始一個事務的時候鎖定整個資料檔案而完成的

0x2: 功能特性

1. ACID事務 
2. 零配置: 無需安裝和管理配置(得益於非C/S架構的設計)
3. 儲存在單一磁碟檔案中的一個完整的資料庫(檔案型資料庫)
4. 資料庫檔案可以在不同位元組順序的機器間自由的共享
5. 支援資料庫大小至2TB
6. 足夠小: 大致13萬行C程式碼，4.43M
7. 比一些流行的資料庫在大部分普通資料庫操作要快
8. 簡單API方式
9. 包含TCL繫結, 同時通過Wrapper支援其他語言的繫結
10. 良好註釋的原始碼, 並且有著90%以上的測試覆蓋率
11. 獨立: 沒有額外依賴
12. 原始碼完全的開源，可以用於任何用途 
13. 支援多種開發語言，C、C++、PHP、Perl、Java、C#、Python、Ruby等

0x3: SQL語法支援

SQLite雖然很小巧，但是支援的SQL語句不會遜色於其他開源資料庫，它支援的SQL包括

ATTACH DATABASE
BEGIN TRANSACTION
comment
COMMIT TRANSACTION
COPY
CREATE INDEX
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
DELETE
DETACH DATABASE
DROP INDEX
DROP TABLE
DROP TRIGGER
DROP VIEW
END TRANSACTION
EXPLAIN
expression
INSERT
ON CONFLICT clause
PRAGMA
REPLACE
ROLLBACK TRANSACTION
SELECT
UPDATE

0x4: 資料型別

SQLite是無型別的，即typelessness(無型別)，我們可以儲存任何型別的資料到所想要儲存的任何表的任何列中，無論這列宣告的資料型別是什麼(除了欄位型別為"Integer Primary Key"之外)，對於SQLite來說對欄位不指定型別是完全有效的，如:

Create Table ex1(a, b, c);
//雖然SQLite允許忽略資料型別, 但是仍然建議在Create Table語句中指定資料型別. 因為資料型別對於和其他的程式設計師交流, 或者準備換掉的資料庫引擎時能起到一個提示或幫助的作用

SQLite支援常見的資料型別，如:

CREATE TABLE ex2
a VARCHAR(10),
b NVARCHAR(15),
c TEXT,
d INTEGER,
e FLOAT,
f BOOLEAN,
g CLOB,
h BLOB,
i TIMESTAMP,
j NUMERIC(10,5)
k VARYING CHARACTER (24),
l NATIONAL VARYING CHARACTER(16)

0x5: SQLite常用函式

The core functions shown below are available by default. Date & Time functions and aggregate functions are documented separately. An application may define additional functions written in C and added to the database engine using the sqlite3_create_function() API.

1. abs(X)
The abs(X) function returns the absolute value of the numeric argument X. Abs(X) returns NULL if X is NULL. Abs(X) returns 0.0 if X is a string or blob that cannot be converted to a numeric value. If X is the integer -9223372036854775808 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value.

2. changes()    
The changes() function returns the number of database rows that were changed or inserted or deleted by the most recently completed INSERT, DELETE, or UPDATE statement, exclusive of statements in lower-level triggers. The changes() SQL function is a wrapper around the sqlite3_changes() C/C++ function and hence follows the same rules for counting changes.

3. char(X1,X2,...,XN)    
The char(X1,X2,...,XN) function returns a string composed of characters having the unicode code point values of integers X1 through XN, respectively.

4. coalesce(X,Y,...)    
The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. Coalesce() must have at least 2 arguments.

5. 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.

6. ifnull(X,Y)
The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. Ifnull() must have exactly 2 arguments. The ifnull() function is equivalent to coalesce() with two arguments.

7. instr(X,Y)
The instr(X,Y) function finds the first occurrence of string Y within string X and returns the number of prior characters plus 1, or 0 if Y is nowhere found within X. Or, if X and Y are both BLOBs, then instr(X,Y) returns one more than the number bytes prior to the first occurrence of Y, or 0 if Y does not occur anywhere within X. If both arguments X and Y to instr(X,Y) are non-NULL and are not BLOBs then both are interpreted as strings. If either X or Y are NULL in instr(X,Y) then the result is NULL.

8. 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.

9. last_insert_rowid()    The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

10. length(X)
For a string value X, the length(X) function returns the number of characters (not bytes) in X prior to the first NUL character. Since SQLite strings do not normally contain NUL characters, the length(X) function will usually return the total number of characters in the string X. For a blob value X, length(X) returns the number of bytes in the blob. If X is NULL then length(X) is NULL. If X is numeric then length(X) returns the length of a string representation of X.

11. like(X,Y)
12. like(X,Y,Z)    
The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The sqlite3_create_function() interface can be used to override the like() function and thereby change the operation of the LIKE operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.

13. likelihood(X,Y)
The likelihood(X,Y) function returns argument X unchanged. The value Y in likelihood(X,Y) must be a floating point constant between 0.0 and 1.0, inclusive. The likelihood(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles during run-time (that is, during calls to sqlite3_step()). 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. The unlikely(X) function is short-hand for likelihood(X,0.0625). The likely(X) function is short-hand for likelihood(X,0.9375).

14. likely(X)    The likely(X) function returns the argument X unchanged. The likely(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles at run-time (that is, during calls to sqlite3_step()). The purpose of the likely(X) function is to provide a hint to the query planner that the argument X is a boolean value that is usually true. The likely(X) function is equivalent to likelihood(X,0.9375). See also: unlikely(X).

15. load_extension(X)
16. load_extension(X,Y)
The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y. The result of load_extension() is always a NULL. If Y is omitted then the default entry point name is used. The load_extension() function raises an exception if the extension fails to load or initialize correctly.
The load_extension() function will fail if the extension attempts to modify or delete an SQL function or collating sequence. The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the sqlite3_load_extension() C-language API.
//For security reasons, extension loaded is turned off by default and must be enabled by a prior call to sqlite3_enable_load_extension().

17. lower(X)
The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.

18. ltrim(X)
19. ltrim(X,Y)
The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. If the Y argument is omitted, ltrim(X) removes spaces from the left side of X.

20. max(X,Y,...)
The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL. The multi-argument max() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to max() define a collating function, then the BINARY collating function is used. Note that max() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.

21. min(X,Y,...)
The multi-argument min() function returns the argument with the minimum value. The multi-argument min() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If none of the arguments to min() define a collating function, then the BINARY collating function is used. Note that min() is a simple function when it has 2 or more arguments but operates as an aggregate function if given only a single argument.

22. nullif(X,Y)
The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to nullif() defines a collating function then the BINARY is used.

23. 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.

24. 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.

25. random()
The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

26. randomblob(N)
The randomblob(N) function return an N-byte blob containing pseudo-random bytes. If N is less than 1 then a 1-byte random blob is returned.
/*
Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower() like this:
hex(randomblob(16))
lower(hex(randomblob(16)))
*/

27. replace(X,Y,Z)
The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrence of string Y in string X. The BINARY collating sequence is used for comparisons. If Y is an empty string then return X unchanged. If Z is not initially a string, it is cast to a UTF-8 string prior to processing.

28. round(X)
29. round(X,Y)
The round(X,Y) function returns a floating-point value X rounded to Y digits to the right of the decimal point. If the Y argument is omitted, it is assumed to be 0.

30. rtrim(X)
31. rtrim(X,Y)
The rtrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the right side of X. If the Y argument is omitted, rtrim(X) removes spaces from the right side of X.

32. 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.

33. 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.

34. 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.

35. 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() begins with the date and time that the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. This function is an SQL wrapper around the sqlite3_sourceid() C interface.

36. 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.

37. substr(X,Y,Z)
38. substr(X,Y)
The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. If Z is omitted then substr(X,Y) returns all characters through the end of the string X beginning with the Y-th. The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left. If Z is negative then the abs(Z) characters preceding the Y-th character are returned. If X is a string then characters indices refer to actual UTF-8 characters. If X is a BLOB then the indices refer to bytes.

39. total_changes()
The total_changes() function returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened. This function is a wrapper around the sqlite3_total_changes() C/C++ interface.

40. trim(X)
42. trim(X,Y)    
The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. If the Y argument is omitted, trim(X) removes spaces from both ends of X.

43. typeof(X)
The typeof(X) function returns a string that indicates the datatype of the expression X: "null", "integer", "real", "text", or "blob".

44. unlikely(X)
The unlikely(X) function returns the argument X unchanged. The unlikely(X) function is a no-op that the code generator optimizes away so that it consumes no CPU cycles at run-time (that is, during calls to sqlite3_step()). The purpose of the unlikely(X) function is to provide a hint to the query planner that the argument X is a boolean value that is usually not true. The unlikely(X) function is equivalent to likelihood(X, 0.0625).

45. 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.

46. 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.

47. 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.

Relevant Link:

http://www.sqlite.org/lang_corefunc.html
https://www.sqlite.org/c3ref/funclist.html
http://baike.baidu.com/view/19310.htm
http://zh.wikipedia.org/wiki/SQLite
http://www.sqlite.org/

 

2. Sqlite安裝

SQLite 是一個開源的嵌入式關聯式資料庫，實現自包容、零配置、支援事務的SQL資料庫引擎。 其特點是高度便攜、使用方便、結構緊湊、高效、可靠。 與其他資料庫管理系統不同，SQLite 的安裝和執行非常簡單，在大多數情況下 - 只要確保SQLite的二進位制檔案存在即可開始建立、連線和使用資料庫

1. SQLite on Windows
http://www.sqlite.org/download.html
下載 Windows 下的預編譯二進位制檔案包：
sqlite-shell-win32-x86-<build#>.zip
sqlite-dll-win32-x86-<build#>.zip
注意: <build#> 是 sqlite 的編譯版本號
將 zip 檔案解壓到你的磁碟，並將解壓後的目錄新增到系統的 PATH 變數中，以方便在命令列中執行 sqlite 命令。
可選: 如果你計劃釋出基於 sqlite 資料庫的應用程式，你還需要下載原始碼以便編譯和利用其 API
sqlite-amalgamation-<build#>.zip

2. SQLite on Linux 
/* For Debian or Ubuntu /*
$ sudo apt-get install sqlite3 sqlite3-dev
 
/* For RedHat, CentOS, or Fedora/*
$ yum install SQLite3 sqlite3-dev

0x1: 建立首個 SQLite 資料庫

1. 在命令列視窗中輸入如下命令來建立一個名為  test.db 的資料庫。
sqlite3 test.db

2. 建立表 
create table mytable(id integer primary key, value text);
//該表包含一個名為 id 的主鍵欄位和一個名為 value 的文字欄位

3. 接下來往表裡中寫入一些資料
insert into mytable(id, value) values(1, 'Micheal');
insert into mytable(id, value) values(2, 'Jenny');
insert into mytable(value) values('Francis');
insert into mytable(value) values('Kerk');

4. 查詢資料
select * from test;

5. 設定格式化查詢結果
.mode column;    //.mode column 將設定為列顯示模式
.header on;    //.header 將顯示列名
select * from test;

6. 建立檢視
create view nameview as select * from mytable;

7. 建立索引
create index test_idx on mytable(value);

Relevant Link:

http://www.oschina.net/question/12_53183

 

2. SQLite Programing

0x1: 用PHP操作sqlite資料庫

//下面的 PHP 程式碼段將用於在先前建立的資料庫中建立一個表 
<?php
    class MyDB extends SQLite3
    {
        function __construct()
        {
            $this->open('test.db');
        }
    }

    $db = new MyDB();
    if(!$db)
    {
        echo $db->lastErrorMsg();
    } 
    else 
    {
        echo "Opened database successfully\n";
    }

    $sql =<<<EOF
        CREATE TABLE COMPANY
        (ID INT PRIMARY KEY     NOT NULL,
        NAME           TEXT    NOT NULL,
        AGE            INT     NOT NULL,
        ADDRESS        CHAR(50),
        SALARY         REAL);
    EOF;

    $ret = $db->exec($sql);
    if(!$ret)
    {
        echo $db->lastErrorMsg();
    } 
    else 
    {
        echo "Table created successfully\n";
    }
    $db->close();
?>

0x2: 使用linux下的C操作SQLite

#include <stdio.h>
#include <sqlite3.h>

int main( void )
{
    sqlite3 *db = NULL;
    char *zErrMsg = 0;
    int rc;
    
    //開啟指定的資料庫檔案,如果不存在將建立一個同名的資料庫檔案
    rc = sqlite3_open("littlehann.db", &db);
    if( rc )
    {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }
    else printf("You have opened a sqlite3 database named littlehann.db successfully!\nCongratulations! Have fun ! ^-^ \n");
    sqlite3_close(db); //關閉資料庫
    return 0;
}
//gcc -o test test.c -lsqlite3

Relevant Link:

http://www.w3cschool.cc/sqlite/sqlite-php.html
http://www.w3cschool.cc/sqlite/sqlite-tutorial.html

 

4. SQLite statements

從安全攻防的角度來說，SQL預編譯statements可以有效防禦SQL Injection注入攻擊，從效能的角度來看，將SQL語句預編譯為一個二進位制的形式直接載入進記憶體中可以大幅度提高SQL查詢的效能

Relevant Link:

http://php.net/manual/en/sqlite3.prepare.php

 

Copyright (c) 2015 LittleHann All rights reserved