SQLite 指令操作

參考引用來源
--
概述
SQLite介紹
自幾十年前出現的商業應用程式以來，資料庫就成為軟體應用程式的主要組成部分。正與資料庫管理系統非常關鍵一樣，它們也變得非常龐大，並佔用了相當多的系統資源，增加了管理的複雜性。隨著軟體應用程式逐漸模組模組化，一種新型資料庫會比大型複雜的傳統資料庫管理系統更適應。嵌入式資料庫直接在應用程式進程中運行，提供了零配置（zero-configuration）運行模式，並且資源佔用非常少。
SQLite是一個開源的嵌入式關係資料庫，它在2000年由D. Richard Hipp發佈，它的減少應用程式管理資料的開銷，SQLite可攜性好，很容易使用，很小，高效而且可靠。
SQLite嵌入到使用它的應用程式中，它們共用相同的進程空間，而不是單獨的一個進程。從外部看，它並不像一個RDBMS，但在進程內部，它卻是完整的，自包含的資料庫引擎。
嵌入式資料庫的一大好處就是在你的程式內部不需要網路設定，也不需要管理。因為用戶端和伺服器在同一進程空間運行。SQLite 的資料庫許可權只依賴于檔案系統，沒有使用者帳戶的概念。SQLite 有資料庫級鎖定，沒有網路伺服器。它需要的記憶體，其它開銷很小，適合用於嵌入式設備。你需要做的僅僅是把它正確的編譯到你的程式。
架構(architecture)
SQLite採用了模組的設計，它由三個子系統，包括8個獨立的模組構成。




介面(Interface)
介面由SQLite C API組成，也就是說不管是程式、指令碼語言還是庫檔，最終都是通過它與SQLite交互的(我們通常用得較多的ODBC/JDBC最後也會轉化為相應C API的調用)。
編譯器(Compiler)
在編譯器中，分詞器（Tokenizer）和分析器(Parser)對SQL進行語法檢查，然後把它轉化為底層能更方便處理的階層式資料結構---語法樹，然後把語法樹傳給代碼產生器(code generator)進行處理。而代碼產生器根據它生成一種針對SQLite的彙編代碼，最後由虛擬機器(Virtual Machine)執行。
虛擬機器(Virtual Machine)
架構中最核心的部分是虛擬機器，或者叫做虛擬資料庫引擎(Virtual Database Engine,VDBE)。它和JAVA虛擬機器相似，解釋執行位元組代碼。VDBE的位元組代碼由128個操作碼(opcodes)構成，它們主要集中在資料庫操作。它的每一條指令都用來完成特定的資料庫操作(比如打開一個表的游標)或者為這些操作棧空間的準備(比如壓入參數)。總之，所有的這些指令都是為了滿足SQL命令的要求(關於VM，後面會做詳細介紹)。
後端(Back-End)
後端由B-樹(B-tree)，頁緩存(page cache，pager)和作業系統介面(即系統調用)構成。B-tree和page cache共同對資料進行管理。B-tree的主要功能就是索引，它維護著各個頁面之間的複雜的關係，便於快速找到所需資料。而pager的主要作用就是通過OS介面在B-tree和Disk之間傳遞頁面。
SQLite的特點(SQLite’s Features and Philosophy)
零配置(Zero Configuration)
可移植(Portability)：
它是運行在Windows,Linux,BSD,Mac OS X和一些商用Unix系統，比如Sun的Solaris,IBM的AIX，同樣，它也可以工作在許多嵌入式作業系統下，比如QNX,VxWorks,Palm OS, Symbin和Windows CE。
Compactness：
SQLite是被設計成羽量級，自包含的。one header file, one library, and you’re relational, no external database server required
簡單(Simplicity)
靈活(Flexibility)
可靠(Reliability)：
SQLite的核心大約有3萬行標準C代碼，這些代碼都是模組化的，很容易閱讀。


事務(Transaction)
事務的週期(Transaction Lifecycles)
程式與事務之間有兩件事值得注意：
A、哪些物件在事務下運行——這直接與API有關。
B、事務的生命週期，即什麼時候開始，什麼時候結束以及它在什麼時候開始影響別的連接（這點對於併發性很重要）——這涉及到SQLite的具體實現。
一個連接（connection）可以包含多個(statement)，而且每個連接有一個與資料庫關聯的B-tree和一個pager。Pager在連接中起著很重要的作用，因為它管理事務、鎖、記憶體緩存以及負責崩潰恢復(crash recovery)。當你進行資料庫寫操作時，記住最重要的一件事：在任何時候，只在一個事務下執行一個連接。這些回答了第一個問題。
一般來說，一個事務的生命和statement差不多，你也可以手動結束它。預設情況下，事務自動提交，當然你也可以通過BEGIN..COMMIT手動提交。接下來就是鎖的問題。



關於這個圖有以下幾點值得注意：
A、一個事務可以在UNLOCKED，RESERVED或EXCLUSIVE三種狀態下開始。預設情況下在UNLOCKED時開始。
B、白色框中的UNLOCKED, PENDING, SHARED和 RESERVED可以在一個資料庫的同一時存在。
C、從灰色的PENDING開始，事情就變得嚴格起來，意味著事務想得到排斥鎖(EXCLUSIVE)（注意與白色框中的區別）。
雖然鎖有這麼多狀態，但是從體質上來說，只有兩種情況：讀事務和寫事務。


讀者可以從HTTP://www.sqlite.org/下載SQLite 最新的版本
Cmd 進入命令列



創建資料庫檔案:
>SQLite3 d:\test.db 回車
就生成了一個test.db在d盤。
這樣同時也SQLite3掛上了這個test.db



用.help可以看看有什麼命令
>.help 回車即可


看看有創建了多少表
>.tables


看表結構
>.schema 表名


看看目前掛的資料庫
>.database


如果要把查詢輸出到檔
>.output 檔案名
> 查詢語句；


把查詢結果用螢幕輸出
>.output stdout
把表結構輸出，同時索引也會輸出
> .dump 表名
退出
>.exit 或者.quit


從HTTP://sqlite.phxsoftware.com/下載Ado.net驅動。
下載了安裝，在安裝目錄中存在System.Data.SQLite.dll
我們只需要拷貝這個檔到引用目錄，並增加參考即可對SQLite資料庫操作了
所有的Ado.net物件都是以SQLite開頭的，比如SQLiteConnection
連接串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--應用在和應用程式或者.net能夠自動找到的目錄
剩下的就很簡單了~~
SQL語法
由於以前用SQLServer或者ISeries，所以DDL的語法很汗顏
創建一個單個Primary Key的table
CREATE TABLE [Admin] (
[UserName] [Nvarchar] (20) PRIMARY KEY NOT Null ,
[Password] [Nvarchar] (50) NOT Null ,
[Rank] [Smallint] NOT Null ,
[MailServer] [Nvarchar] (50) NOT Null ,
[MailUser] [Nvarchar] (50) NOT Null ,
[MailPassword] [Nvarchar] (50) NOT Null ,
[Mail] [Nvarchar] (50) NOT Null
) ;
創建一個多個Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [Nvarchar] (10) NOT Null ,
[CdCode] [Nvarchar] (20) NOT Null ,
[CdString1] [Ntext] NOT Null ,
[CdString2] [Ntext] NOT Null ,
[CdString3] [Ntext] NOT Null,
PRIMARY KEY (CdType,CdCode)
) ;
創建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
還可以視圖等等。



SQLite 分頁查詢
寫法1:
SELECT * FROM TABLE1 LIMIT 20 OFFSET 20 ;
寫法2:
SELECT * FROM TABLE1 LIMIT 20 , 20;
SQLite 檔的壓縮
在多次刪除資料、插入資料、更新資料後，資料庫體積增大，但實際有效資料量很小，則需要對資料庫進行壓縮、整理，把已經刪除的資料從物理檔中移除。調用一下SQL命令即可：
VACUUM
VACUUM的實現




資料插入與更新
使用REPLACE替代INSERT、UPDATE命令。在無滿足條件記錄，則執行Insert，有滿足條件記錄，則執行UPDATE。
1 REPLACE INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
Insert or Replace Into 和Replace Into 的效果是一樣的上面這句話也可以這樣寫
1 Insert or Replace INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);


字元編碼轉換
sqlite3的源碼中，提供了utf8ToUnicode()、unicodeToUtf8()、mbcsToUnicode()、unicodeToMbcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8ToMbcs ()等8個函數進行字元在不同編碼間的轉換，但未在sqlite3.def、sqlite3.h檔中列出，即未對外公開。這些函數中，都使用了MultiByteToWideChar()、WideCharToMultiByte()兩個函數實現字元間轉換。


開發示例
001 using System;
002 using System.Data;
003 using System.Data.SQLite;
004 using System.Collections.Generic;
005 using System.IO;
006
007 namespace DataHelper
008 {
009 public class SqLiteHelper
010 {
011 ///

012 /// ConnectionString樣例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
013 ///
014 public static string ConnectionString
015 {
016 get
017 {
018 return @"Data source= "+DataBasePath+";";
019 }
020 set { throw new NotImplementedException(); }
021 }
022
023 public static string DataBasePath
024 {
025 get { return "SpringYang.db";};
026 }
027
028 private static object lockObject = new object();
029
030 private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, List parameters)
031 {
032 if (conn.State != ConnectionState.Open)
033 conn.Open();
034 cmd.Parameters.Clear();
035 cmd.Connection = conn;
036 cmd.CommandText = cmdText;
037 cmd.CommandType = CommandType.Text;
038 cmd.CommandTimeout = 30;
039 foreach (var parameter in parameters)
040 {
041 cmd.Parameters.Add(parameter);
042 }
043 }
044
045 public static DataSet ExecuteQuery(string cmdText, List parameters)
046 {
047 lock (lockObject)
048 {
049 using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
050 {
051 using (SQLiteCommand command = new SQLiteCommand())
052 {
053 DataSet ds = new DataSet();
054 PrepareCommand(command, conn, cmdText, parameters);
055 SQLiteDataAdapter da = new SQLiteDataAdapter(command);
056 da.Fill(ds);
057 return ds;
058 }
059 }
060 }
061 }
062
063 public static int ExecuteNonQuery(string cmdText, List parameters)
064 {
065 lock (lockObject)
066 {
067 using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
068 {
069 using (SQLiteCommand command = new SQLiteCommand())
070 {
071
072 PrepareCommand(command, conn, cmdText, parameters);
073 return command.ExecuteNonQuery();
074 }
075 }
076 }
077 }
078
079 public static SQLiteDataReader ExecuteReader(string cmdText, List parameters)
080 {
081 lock (lockObject)
082 {
083 SQLiteConnection conn = new SQLiteConnection(ConnectionString);
084
085 SQLiteCommand command = new SQLiteCommand();
086
087 PrepareCommand(command, conn, cmdText, parameters);
088 SQLiteDataReader sqLiteDataReader = command.ExecuteReader();
089 return sqLiteDataReader;
090 }
091 }
092
093 public static object ExecuteScalar(string cmdText, List parameters)
094 {
095 lock (lockObject)
096 {
097 using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
098 {
099 using (SQLiteCommand command = new SQLiteCommand())
100 {
101 PrepareCommand(command, conn, cmdText, parameters);
102 return command.ExecuteScalar();
103 }
104 }
105 }
106 }
107 public static void CreateDataBase()
108 {
109 if (!File.Exists(DataBasePath))
110 SQLiteConnection.CreateFile(DataBasePath);
111 CreateTable();
112 }
113
114
115 public static void CreateTable()
116 {
117 ExecuteNonQuery(CodeDetailTabale, null);
118 }
119
120
121 private static string CodeDetailTabale
122 {
123 get
124 {
125 return @"CREATE TABLE [CodeDetail] (
126 [CdType] [Nvarchar] (10) NOT Null ,
127 [CdCode] [Nvarchar] (20) NOT Null ,
128 [CdString1] [Ntext] NOT Null ,
129 [CdString2] [Ntext] NOT Null ,
130 [CdString3] [Ntext] NOT Null,
131 PRIMARY KEY (CdType,CdCode)
132 ) ;";
133 }
134 }
135 }
136 }

示例講解
A、使用到自己定義的鎖private static object lockObject = new object();
B、使用完連接後都進行關閉操作。使用了using
C、創建資料庫命令：SQLiteConnection.CreateFile(DataBasePath);

最後再講解個Insert or Replace into的經典用法

1 Insert or Replace INTO User(ID, Name,Age) Select old.ID,new.Name,new.Age From
2 (select 'Spring Yang' as Name, '25' as Age) as new left join (Select ID,Name from User where Name = 'Spring Yang' ) as old on old.Name = new.Name