快捷導(dǎo)航

SQLite數(shù)據(jù)庫管理系統(tǒng)-我所認識的數(shù)據(jù)庫引擎

更新時間：2013年01月04日 09:25:33 作者：

SQLite是一款輕量級的、被設(shè)計用于嵌入式系統(tǒng)的關(guān)聯(lián)式數(shù)據(jù)庫管理系統(tǒng),SQLite 是一個實現(xiàn)自我依賴、純客戶端、零配置且支持事務(wù)的數(shù)據(jù)庫引擎

SQLite 是一款輕量級的、被設(shè)計用于嵌入式系統(tǒng)的關(guān)聯(lián)式數(shù)據(jù)庫管理系統(tǒng)。SQLite 是一個實現(xiàn)自我依賴、純客戶端、零配置且支持事務(wù)的數(shù)據(jù)庫引擎。它由D. Richard Hipp首次開發(fā)，目前已是世界上最廣泛部署的開源數(shù)據(jù)庫引擎。

本文中，我們將介紹如下內(nèi)容：

創(chuàng)建一個SQLite 數(shù)據(jù)庫

復(fù)制代碼代碼如下:

SQLiteConnection conn = new SQLiteConnection("Data Source=mytest.s3db"); 
conn.Open();

SQLite 數(shù)據(jù)插入

復(fù)制代碼代碼如下:

/// <summary> 
/// Allows the programmer to easily insert into the DB 
/// </summary> 
/// <param name="tableName">The table into which we insert the data.</param> 
/// <param name="data">A dictionary containing the column names and data for the insert.</param> 
/// <returns>A boolean true or false to signify success or failure.</returns> 
public bool Insert(string tableName, Dictionary<string, string> data) 
{ 
Boolean returnCode = true; 
StringBuilder columnBuilder = new StringBuilder(); 
StringBuilder valueBuilder = new StringBuilder(); 
foreach (KeyValuePair<string, string> val in data) 
{ 
columnBuilder.AppendFormat(" {0},", val.Key); 
valueBuilder.AppendFormat(" '{0}',", val.Value); 
} 
columnBuilder.Remove(columnBuilder.Length - 1, 1); 
valueBuilder.Remove(valueBuilder.Length - 1, 1); 
try 
{ 
this.ExecuteNonQuery(string.Format("INSERT INTO {0}({1}) VALUES({2});", 
tableName, columnBuilder, valueBuilder)); 
} 
catch (Exception ex) 
{ 
mLog.Warn(ex.ToString()); 
returnCode = false; 
} 
return returnCode; 
}

復(fù)制代碼代碼如下:

DateTime entryTime; 
string name = string.Empty, title = string.Empty; 
GetSampleData(out name, out title, out entryTime); 
int id = random.Next(); 
insertParameterDic.Add("Id", id.ToString()); 
insertParameterDic.Add("Name", name); 
insertParameterDic.Add("Title", title); 
insertParameterDic.Add("EntryTime", 
entryTime.ToString("yyyy-MM-dd HH:mm:ss")); 

db.Insert("Person", insertParameterDic);

SQLite 的事務(wù)處理方式

Begin Transaction:

begin-stmt

Commit Transaction:

commit-stmt

Rollback Transaction:

rollback-stmt

復(fù)制代碼代碼如下:

try 
{ 
db.OpenTransaction(); 
Insert4Native(); 
db.CommiteTransaction(); 
} 
catch (System.Exception ex) 
{ 
mLog.Error(ex.ToString()); 
db.RollbackTransaction(); 
}

SQLite 的索引

索引是一種用來優(yōu)化查詢的特性，在數(shù)據(jù)中分為聚簇索引和非聚簇索引；前者是由數(shù)據(jù)庫中數(shù)據(jù)組織方式?jīng)Q定的，比如我們在往數(shù)據(jù)庫中一條一條插入數(shù)據(jù)時，聚簇索引能夠保證按順序插入，插入后數(shù)據(jù)的位置和結(jié)構(gòu)不變。非聚簇索引是指我們手動、顯式創(chuàng)建的索引，可以為數(shù)據(jù)庫中的每個列創(chuàng)建索引，和字典中的索引類似，遵循的原則是對有分散性和組合型的列建立索引，以利于大數(shù)據(jù)和復(fù)雜查詢情況下提高查詢效率。

create-index-stmt

復(fù)制代碼代碼如下:

/// <summary> 
/// Create index 
/// </summary> 
/// <param name="tableName">table name</param> 
/// <param name="columnName">column name</param> 
/// <param name="indexName">index name</param> 
public void CreateIndex(string tableName, string columnName, string indexName) 
{ 
string createIndexText = string.Format("CREATE INDEX {0} ON {1} ({2});", 
indexName, tableName, columnName); 
ExecuteNonQuery(createIndexText); 
}

簡單查詢、無關(guān)數(shù)據(jù)庫大小情況下對查詢效率的測試結(jié)果如下(700,000條數(shù)據(jù))：

復(fù)制代碼代碼如下:

string sql = "SELECT LeafName FROM File WHERE Length > 5000";

復(fù)雜查詢情況下對查詢效率的測試結(jié)果如下(~40,000條數(shù)據(jù))：

復(fù)制代碼代碼如下:

string sql = "SELECT folder.Location AS FilePath" 
+ "FROM Folder folder LEFT JOIN File file ON file.ParentGuid=folder.Guid" 
+"WHERE file.Length > 5000000 GROUP BY File.LeafName";

SQLite 的觸發(fā)器(Trigger)

觸發(fā)器是指當一個特定的數(shù)據(jù)庫事件(DELETE, INSERT, or UPDATE)發(fā)生以后自動執(zhí)行的數(shù)據(jù)庫操作, 我們可以把觸發(fā)器理解為高級語言中的事件(Event)。

假設(shè)我有兩個表：

Folder(Guid VCHAR(255) NOT NULL, Deleted BOOLEAN DEFAULT 0)

File(ParentGuid VCHAR(255) NOT NULL, Deleted BOOLEAN DEFAULT 0)

在Folder 表中創(chuàng)建一個觸發(fā)器Update_Folder_Deleted:

復(fù)制代碼代碼如下:

CREATE TRIGGER Update_Folder_Deleted UPDATE Deleted ON Folder 
Begin 
UPDATE File SET Deleted=new.Deleted WHERE ParentGuid=old.Guid; 
END;

創(chuàng)建完觸發(fā)器以后在執(zhí)行以下語句：

復(fù)制代碼代碼如下:

UPDATE Folder SET Deleted=1 WHERE Guid='13051a74-a09c-4b71-ae6d-42d4b1a4a7ae'

以上語句將會導(dǎo)致下面的語句自動執(zhí)行：

復(fù)制代碼代碼如下:

UPDATE File SET Deleted=1 WHERE ParentGuid='13051a74-a09c-4b71-ae6d-42d4b1a4a7ae' 

SQLite 的視圖(View)

視圖可以是一個虛擬表，里面可以存儲按照一定條件過濾出來的數(shù)據(jù)集合，這樣我們再下次想得到這些特定數(shù)據(jù)集合的時候就不用通過復(fù)雜查詢來獲得，簡單的查詢指定視圖就可以得到想要的數(shù)據(jù)。

在下個例子中，我們創(chuàng)建一個簡單的視圖：

Capture

基于上面的查詢結(jié)果我們創(chuàng)建一個視圖：

Capture2

SQLite 命令行工具

SQLite 庫中包含了一個SQLite3.exe 的命令行工具，它可以實現(xiàn)SQLite 各項基本操作。這里只介紹一下如何使用它來分析我們的查詢結(jié)果：

1. CMD->sqlite3.exe MySQLiteDbWithoutIndex.s3db

Capture

2. 開啟EXPLAIN 功能并分析指定查詢結(jié)果

Capture

3. 重新使用命令行打開一個有索引的數(shù)據(jù)庫并執(zhí)行前兩步

Capture

4. 通過比較兩個不同查詢語句的分析結(jié)果，我們可以發(fā)現(xiàn)如果查詢過程中使用了索引，SQLite 會在detail 列中提示我們。

5. 要注意的是每條語句后面都要加分號“；”

SQLite一些常見的使用限制

1. SQLite 不支持Unicode 字符的大小寫比較，請看以下測試結(jié)果：

Capture

2. 如何處理SQLite 轉(zhuǎn)義字符：

復(fù)制代碼代碼如下:

INSERT INTO xyz VALUES('5 O''clock');

3. 一條復(fù)合SELECT語句的條數(shù)限制：
一條復(fù)合查詢語句是指多條SELECT語句由 UNION, UNION ALL, EXCEPT, or INTERSECT 連接起來. SQLite進程的代碼生成器使用遞歸算法來組合SELECT語句。為了降低堆棧的大小，SQLite 的設(shè)計者們限制了一條復(fù)合SELECT語句的條目數(shù)量。 SQLITE_MAX_COMPOUND_SELECT的默認值是500. 這個值沒有嚴格限制，在實踐中，幾乎很難看到一條復(fù)合查詢語句的條目數(shù)大于500的。

這里提到復(fù)合查詢的原因是我們可以使用它來幫助我們快速插入大量數(shù)據(jù)：

復(fù)制代碼代碼如下:

public void Insert4SelectUnion() 
{ 
bool newQuery = true; 
StringBuilder query = new StringBuilder(4 * ROWS4ACTION); 
for (int i = 0; i < ROWS4ACTION; i++) 
{ 
if (newQuery) 
{ 
query.Append("INSERT INTO Person"); 
newQuery = false; 
} 
else 
{ 
query.Append(" UNION ALL"); 
} 

DateTime entryTime; 
string name = string.Empty, title = string.Empty; 
GetSampleData(out name, out title, out entryTime); 
int id = random.Next(); 
query.AppendFormat(" SELECT '{0}','{1}','{2}','{3}'", id, name, title, entryTime.ToString("yyyy-MM-dd HH:mm:ss")); 
if (i % 499 == 0) 
{ 
db.ExecuteNonQuery(query.ToString()); 
query.Remove(0, query.Length); 
newQuery = true; 
} 
} 

//executing remaining lines 
if (!newQuery) 
{ 
db.ExecuteNonQuery(query.ToString()); 
query.Remove(0, query.Length); 
} 
}