SQLite3 使用教學

一、SQLite的使用場景

★如何權衡？
　　當你在權衡某個場合是否應該使用SQLite時，（在技術層面）至少要考慮如下幾點：
　　◇能否發揮SQLite的某些特長？
　　◇是否還有其它的替代方案？
　　◇是否有啥潛在的技術風險？
　　想清楚上述問題之後，再做出決策。

　　★SQLite的特點
　　關於SQLite的特長，在上次的帖子中已經介紹過了。考慮到某些同學比較健忘，我們再回顧一下：
　　◇檔案型資料庫，且只有單一資料檔案
　　◇輕量級
　　◇綠色（不依賴其它軟體庫）
　　◇跨平臺（包括引擎和資料檔案）
　　◇支援記憶體資料庫
　　◇支援較大的資料檔案（TB級別）

　　★可能的替代方案
　　剛才說了，權衡SQLite的使用需要考慮其它的替代方案，所以俺簡單介紹一下和SQLite用途相近的其它幾種技術手段。後面講應用場景的時候，會結合這幾個替代方案來作對比。

　　◇Access資料庫
　　Access資料庫也是檔案型的資料庫，支援的很多SQL特性都類似於SQLite。自從Windows 2000開始，Windows就內建了Access的資料庫引擎（Microsoft Jet Database Engine）。所以Access資料庫在上述系統中也是可以獨立執行的（不依賴Office）。
　　Access資料庫最主要的缺點就是不能跨平臺。另外還有幾個小缺點：檔案大小有限制（2GB）、不支援記憶體資料庫。

　　◇其它檔案型資料庫
　　其實，除了Access之外，還有另外一些檔案型資料庫。但是這些檔案型資料庫要麼名氣太小，要麼不支援多種程式語言（比如HSQLDB），要麼已經過時（比如FoxPro、Paradox）。所以後面分析應用場景的時候就不再提及這些玩意兒。

　　◇CSV檔案
　　CSV（Comma Separated Values，詳細解釋見“這裡”）是一種很簡單的純文字格式。它本身就是用來表示二維的資料資訊的。一個CSV檔案可以理解為資料庫的一張表。
　　CSV的缺點主要在於：不便於儲存非文字的資料資訊（比如BLOB型別的資訊）；如果需要同時儲存多張表的資訊，就需要對應有多個CSV檔案（檔案一多，就嫌麻煩）。

　　◇XML檔案
　　XML檔案想必大夥兒都知道，我就不多說了。XML格式主要缺點也有兩個：一個是由於XML本身是樹狀結構，有時候不便於表示二維資料表的資訊；另一個是資料量大（比如檔案超過10MB或者XML節點層次很深）的時候，解析XML的開銷蠻大的。

　　★作為資料庫的應用場景
　　前面說了一大通，現在開始切入正題，先說說SQLite作為一個輕型資料庫，方便幹哪些事兒？
　　在這類場景中，由於是把SQLite作為資料庫來使的，所以基本不用考慮CSV和XML這兩種替代方案。

　　◇需要資料庫的小型桌面軟體
　　如果你開發一個小型的桌面軟體並且需要用到資料庫功能（比如某個背單詞軟體），那SQLite是一個不錯的選擇。因為SQLite很綠色又很短小精悍。
　　不過，由於Windows在桌面系統的比重很大。對於那些不考慮跨平臺的開發人員，SQLite相對於Access來說，沒有太大的優勢。

　　◇需要資料庫的手機軟體
　　眼下手機應用的發展很迅猛，相應的開發人員也多起來了。假如你就是一個手機應用程式的開發人員，並且你開發的應用需要有資料庫功能（比如某個字典工具），那SQLite簡直是不二之選。由於手機作業系統名目繁多，同時手機的記憶體偏小，這時候SQLite跨平臺和輕量級的特長就充分發揮出來了。目前幾個知名的手機作業系統（比如Android、Windows Mobile、Symbin、Palm等），SQLite都支援得不錯。
　　在這種場合，Access基本沒戲。

　　★作為資料容器的應用場景
　　所謂資料容器，就是把SQLite作為裝資料的容器，充分發揮SQLite單一資料檔案的優點。另外，還可以避免自己定義一套資料檔案格式的麻煩。要知道，定義一個完善的資料檔案格式是難度極大滴（要考慮可擴充套件性、要考慮向下相容、假如跨CPU架構還要考慮位元組序、假如資料量大還要考慮效能、還要...）。

　　◇資料備份/恢復、資料匯入/匯出
　　某些軟體系統（尤其是些企業應用系統）經常會碰到資料備份/恢復的功能需求。比如說，客戶會要求你把一些資料（往往是業務相關的）定期備份成一個獨立的資料檔案，然後儲存在別處。一旦軟體系統自身發生不測，再把備份的資料恢復回來。
　　另外，匯入/匯出功能也是經常碰到的。一般是某個軟體安裝在多個地方。然後需要把一些資料（往往是業務相關的）從A處匯出，然後在B處匯入。
　　針對上述這兩種需求：如果牽涉的資料比較大，就不宜使用XML或者Access；如果牽涉到跨平臺，就無法使用Access；如果牽涉到多種資料，就不宜使用CSV（除非你能忍受多個CSV檔案並存）。有上述條件限制的地方，都很適合用SQLite。

　　◇線上升級
　　這年頭不聯網的單機已經很少了，提供線上升級功能的軟體會多起來。一般的線上升級分為兩類：升級程式（比如Firefox自動升級新版本）和升級業務資料（比如防毒軟體升級病毒庫）。這兩種型別都可以使用SQLite來完成。把需要要升級的內容放置到SQLite資料庫檔案中，將來升級時只需下載單一的升級檔案即可。
　　在這種場景，不太合適用CSV和XML。如果不考慮跨平臺，倒也可以用Access來搞定。

　　★作為記憶體資料庫的應用場景
　　在這種型別的場景中，我們們要充分發揮SQLite記憶體資料庫的特長。由於SQLite的API設計比較合理，操作記憶體資料庫和操作檔案資料庫幾乎沒啥區別，所以從檔案型切換到記憶體型，程式碼不用大改。另外，從3.6.11開始，SQLite增加了online backup介面，便於在記憶體資料庫和檔案資料庫之間進行資料的同步。

　　◇降低磁碟I/O開銷
　　比如開發了某個字典工具，詞庫儲存在SQLite資料庫檔案中。當詞庫越來越大的時候，你可能會發現，查詞的速度越來越慢。當然啦，速度慢未必是磁碟 I/O引起的。這時候你可以把程式略微修改一下（可能就10行左右的程式碼），在初始化時把詞庫載入記憶體的SQLite資料庫中。然後再對比測試一下效能。如果發現效能有明顯提升，那你以後就可以一直用這種方式了。
　　使用這個招數，要小心記憶體資料庫對記憶體空間的佔用。比如對於普通的PC，佔用個幾兆、十幾兆還行，再大的話就不爽了。另外，對於手機作業系統，此招數效果不好（手機本身的記憶體就不是很大，而且儲存介質的速度已經蠻快了）。

　　◇作為臨時表
　　記憶體資料庫方式，還可以用來充當臨時表，存放一些臨時資料。當程式的程式退出時，記憶體資料庫就自然消失了，不會留下任何垃圾。
　　不過這種方式只適合於某個程式獨佔臨時表的情形。如果臨時表需要被多個程式共用，這招就不靈了。
-------------------------------------------------------------------------------------------------------------------------

二、SQLite3 使用教學

OS X自從10.4後把SQLite這套相當出名的資料庫軟體，放進了作業系統工具集裡。OS X包裝的是第三版的SQLite，又稱SQLite3。這套軟體有幾個特色：

目前在OS X 10.4裡，SQLite是以/usr/bin/sqlite3的形式包裝，也就說這是一個命令列工具，必須先從終端機（Terminal.app或其他程式）進入shell之後才能使用。網路上有一些息協助使用SQLite的視覺化工具，但似乎都沒有像CocoaMySQL（配合MySQL資料庫使用）那般好用。或許隨時有驚喜也未可知，以下僅介紹命令列的操作方式。

SQLite顧名思議是以SQL為基礎的資料庫軟體，SQL是一套強大的資料庫語言，主要概念是由「資料庫」、「資料表」（table）、「查詢指令」（queries）等單元組成的「關聯性資料庫」（進一步的概念可參考網路上各種關於SQL及關聯性資料庫的檔案）。因為SQL的查詢功能強大，語法一致而入門容易，因此成為現今主流資料庫的標準語言（微軟、Oracle等大廠的資料庫軟體都提供SQL語法的查詢及操作）。

以下我們就建立資料庫、建立資料表及索引、新增資料、查詢資料、更改資料、移除資料、sqlite3命令列選項等幾個專案做簡單的介紹。

建立資料庫檔案

用sqlite3建立資料庫的方法很簡單，只要在shell下鍵入（以下$符號為shell提示號，請勿鍵入）：

$ sqlite3 foo.db

如果目錄下沒有foo.db，sqlite3就會建立這個資料庫。sqlite3並沒有強制資料庫檔名要怎麼取，因此如果你喜歡，也可以取個例如foo.icannameitwhateverilike的檔名。

在sqlite3提示列下操作

進入了sqlite3之後，會看到以下文字：

SQLite version 3.1.3
Enter ".help" for instructions
sqlite>

這時如果使用.help可以取得求助，.quit則是離開（請注意：不是quit）

SQL的指令格式

所以的SQL指令都是以分號（;）結尾的。如果遇到兩個減號（--）則代表註解，sqlite3會略過去。

建立資料表

假設我們要建一個名叫film的資料表，只要鍵入以下指令就可以了：

create table film(title, length, year, starring);

這樣我們就建立了一個名叫film的資料表，裡面有name、length、year、starring四個欄位。

這個create table指令的語法為：

create table table_name(field1, field2, field3, ...);

table_name是資料表的名稱，fieldx則是欄位的名字。sqlite3與許多SQL資料庫軟體不同的是，它不在乎欄位屬於哪一種資料型態：sqlite3的欄位可以儲存任何東西：文字、數字、大量文字（blub），它會在適時自動轉換。

建立索引

如果資料表有相當多的資料，我們便會建立索引來加快速度。好比說：

create index film_title_index on film(title);

意思是針對film資料表的name欄位，建立一個名叫film_name_index的索引。這個指令的語法為

create index index_name on table_name(field_to_be_indexed);

一旦建立了索引，sqlite3會在針對該欄位作查詢時，自動使用該索引。這一切的操作都是在幕後自動發生的，無須使用者特別指令。

加入一筆資料

接下來我們要加入資料了，加入的方法為使用insert into指令，語法為：

insert into table_name values(data1, data2, data3, ...);

例如我們可以加入

insert into film values ('Silence of the Lambs, The', 118, 1991, 'Jodie Foster');
insert into film values ('Contact', 153, 1997, 'Jodie Foster');
insert into film values ('Crouching Tiger, Hidden Dragon', 120, 2000, 'Yun-Fat Chow');
insert into film values ('Hours, The', 114, 2002, 'Nicole Kidman');

如果該欄位沒有資料，我們可以填NULL。

查詢資料

講到這裡，我們終於要開始介紹SQL最強大的select指令了。我們首先簡單介紹select的基本句型：

select columns from table_name where expression;

最常見的用法，當然是倒出所有資料庫的內容：

select * from film;

如果資料太多了，我們或許會想限制筆數：

select * from film limit 10;

或是照著電影年份來排列：

select * from film order by year limit 10;

或是年份比較近的電影先列出來：

select * from film order by year desc limit 10;

或是我們只想看電影名稱跟年份：

select title, year from film order by year desc limit 10;

查所有茱蒂佛斯特演過的電影：

select * from film where starring='Jodie Foster';

查所有演員名字開頭叫茱蒂的電影('%' 符號便是 SQL 的萬用字元）：

select * from film where starring like 'Jodie%';

查所有演員名字以茱蒂開頭、年份晚於1985年、年份晚的優先列出、最多十筆，只列出電影名稱和年份：

select title, year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10;

有時候我們只想知道資料庫一共有多少筆資料：

select count(*) from film;

有時候我們只想知道1985年以後的電影有幾部：

select count(*) from film where year >= 1985;

（進一步的各種組合，要去看SQL專書，不過你大概已經知道SQL為什麼這麼流行了：這種語言允許你將各種查詢條件組合在一起──而我們還沒提到「跨資料庫的聯合查詢」呢！）

如何更改或刪除資料

瞭解select的用法非常重要，因為要在sqlite更改或刪除一筆資料，也是靠同樣的語法。

例如有一筆資料的名字打錯了：

update film set starring='Jodie Foster' where starring='Jodee Foster';

就會把主角欄位裡，被打成'Jodee Foster'的那筆（或多筆）資料，改回成Jodie Foster。

delete from film where year < 1970;

就會刪除所有年代早於1970年（不含）的電影了。

其他sqlite的特別用法

sqlite可以在shell底下直接執行命令：

sqlite3 film.db "select * from film;"

輸出 HTML 表格：

sqlite3 -html film.db "select * from film;"

將資料庫「倒出來」：

sqlite3 film.db ".dump" > output.sql

利用輸出的資料，建立一個一模一樣的資料庫（加上以上指令，就是標準的SQL資料庫備份了）：

sqlite3 film.db < output.sql

在大量插入資料時，你可能會需要先打這個指令：

begin;

插入完資料後要記得打這個指令，資料才會寫進資料庫中：

commit;

小結

以上我們介紹了SQLite這套資料庫系統的用法。事實上OS X也有諸於SQLiteManagerX這類的圖形介面程式，可以便利資料庫的操作。不過萬變不離其宗，瞭解SQL指令操作，SQLite與其各家變種就很容易上手了。

至於為什麼要寫這篇教學呢？除了因為OS X Tiger大量使用SQLite之外（例如：Safari的RSS reader，就是把文章存在SQLite資料庫裡！你可以開開看~/Library/Syndication/Database3這個檔案，看看裡面有什麼料），OpenVanilla從0.7.2開始，也引進了以SQLite為基礎的詞彙管理工具，以及全字型檔的注音輸入法。因為使用SQLite，這兩個模組不管資料庫內有多少筆資料，都可以做到「瞬間啟動」以及相當快速的查詢迴應。

將一套方便好用的資料庫軟體包進OS X中，當然也算是Apple相當相當聰明的選擇。再勤勞一點的朋友也許已經開始想拿SQLite來記錄各種東西（像我們其中就有一人寫了個程式，自動記錄電池狀態，寫進SQLite資料庫中再做統計......）了。想像空間可說相當寬廣。

目前支援SQLite的程式語言，你能想到的大概都有了。這套資料庫2005年還贏得了美國O'Reilly Open Source Conference的最佳開放原始碼軟體獎，獎評是「有什麼東西能讓Perl, Python, PHP, Ruby語言團結一致地支援的？就是SQLite」。由此可見SQLite的地位了。而SQLite程式非常小，更是少數打 "gcc -o sqlite3 *"，不需任何特殊設定就能跨平臺編譯的程式。小而省，小而美，SQLite連網站都不多贅言，直指SQL語法精要及API使用方法，原作者大概也可以算是某種程式設計之道（Tao of Programming）裡所說的至人了。

SQLite資料庫是中小站點CMS的最佳選擇

作者：孫毓波 (AKCMS 作者)

SQLite 是一個類似Access的輕量級資料庫系統，但是更小、更快、容量更大，併發更高。為什麼說 SQLite 最適合做 CMS (內容管理系統)呢？並不是說其他資料庫不好, Oracle、MySQL、SQLServer 也都是非常優秀的 DBS，只不過他們設計目標不同，特性不同，所以只有更適用某個應用場景，沒有絕對的好壞之分。

我歸納的中小型站點的CMS的特點如下：

• 1、資料量不超過10萬
• 2、日頁面訪問量不超過10萬
• 3、 一部分網站全部生成靜態頁面，一部分網站實時查詢資料庫動態訪問
• 4、 站長不懂技術，不懂得複雜的資料庫維護，只會用 FTP 管理網站
• 5 、個人站點基本上是一個人管理，一般情況下只有一個人在訪問後臺，沒有併發
• 6、 對資料庫來說是讀多寫少，只有在站長訪問後臺的時候才會寫入
• 7、 多執行於虛擬主機，大部分PHP主機均同時支援MySQL，小部分PHP主機需要單獨購買MySQL，PHP+MySQL的主機價格較PHP主機價格高。（以萬網為例：最便宜的PHP空間780元，最便宜的PHP+MySQL的PHP空間1150元）
• 8、 多數中小站點的HTTP服務與MySQL部署在同一伺服器上

SQLite 的優點在中小網站CMS應用場景下表現突出：

• 1、與MySQL相比，它更徹底的免費，並且沒有任何使用上的限制
• 2、非常小巧，PHP5以上版本中無需任何配置即可支援SQLite
• 3、無需單獨購買資料庫服務，無伺服器程式，配置成本為零
• 4、整個資料庫儲存在一個單個的檔案中，資料匯入匯出備份恢復都是複製檔案，維護難度為零
• 5、讀速度快，在資料量不是很大的情況下速度較快，更重要的是：省掉了一次資料庫遠端連結沒有複雜的許可權驗證，開啟就能操作

SQLite的缺點在中小網站 CMS 應用場景下被規避：

• 1、併發低 動態訪問時當訪問量不超過10萬PV的時候，SQLite 超過 Access 的併發能力已經綽綽有餘；生成靜態頁後更無需考慮資料庫的併發問題
• 2、在大資料量的情況下表現較差 但是中小站點一般情況下資料量不超過10萬，而SQlite 在 100 萬資料量之下表現還不錯，因為省掉了對資料庫伺服器的遠端連線甚至會更快
• 3、寫入較慢 預設配置下的 SQlite 的寫入速度比MySQL慢了很多，但是 CMS 應用場景的寫入操作較少。在插入新文章的時候基本感受不到慢。集中的寫資料庫操作只有在安裝的時候會出現，不過只出現一次，可以忽略
• 4、為已有的表加索引較慢 但是在中小站點CMS中不會有這樣的需求，可以忽略
• 5、無法將 MySQL 部署到與前端機不同的伺服器上，但是中小站點也沒有分開部署的需求

綜上所述：在中小站點 CMS 的應用場景下 SQLite 能最大限度的降低建站成本，降低維護難度，又很好得規避了自身的缺點。所以我認為未來支援 SQLite 的 CMS 系統一定會大行其道。

SQLite For Windowns 安裝配置方法