第2章入門 · SQLite 權威指南

# 第2章入門無論您使用何種操作系統，SQLite都很容易上手。對大多數用戶，安裝SQLite并創建一個新的數據庫不會超過5分鐘，且不需要任何經驗。空注：本章我只看了Windows操作系統下使用VC的內容。 ## 從哪得到SQLite SQLite網站(www.sqlite.org)同時提供SQLite的已編譯版本和源程序。編譯版本可同時適用于Windows和Linux。有幾種形式的二進制包供選擇，以適應SQLite的不同使用方式。包括： + 靜態鏈接的命令行程序(CLP) + SQLite動態鏈接庫(DLL) + Tcl擴展 SQLite源代碼以兩種形式提供，以適應不同的平臺。一種為了在Windows下編譯，另一種為了在POSIX平臺(如Linux, BSD, and Solaris)下編譯，這兩種形式下源代碼本身是沒有差別的。 ## 在Windows上使用SQLite 無論你是作為終端用戶還是作為程序員來使用SQLite，SQLite都可以很容易地安裝在Windows環境下。本節我們將討論所有相關的內容——安裝二進制包或在最普通的編譯環境下使用源代碼。 ## 獲得命令行程序 SQLite命令行程序(CLP)是開始使用SQLite的一個比較好的選擇。略，參原文。 ## 獲得SQLite的動態鏈接庫(DLL) SQLite的DLL文件供編譯好的程序動態連接SQLite。大多數使用SQLite的軟件都會擁有自己的SQLite DLL拷貝并隨軟件自動安裝。 ## 在Windows環境下編譯SQLite源代碼在Windows環境下編譯SQLite源代碼是很簡單的。根據你所使用的編譯器和你要做什么，有幾種方法來編譯SQLite。最常見的環境是Microsoft Visual C++或MinGW，本節都會加以介紹。關于使用其它編譯器編譯SQLite的內容，可參考SQLite Wiki (www.sqlite.org/cvstrac/wiki?p=HowToCompile)。 ## 用Microsoft?Visual?C++構建SQLite?DLL? 通過以下步聚，可使用源代碼，在Visual C++上構建SQLite DLL： 1\. 啟動Visual Studio。在解包的SQLite源程序目錄中創建一個新的DLL“空”項目。高：不同版本操作略有不同，不詳細解釋了。 2\. 將全部SQLite源文件加入到項目中來。包括所有的.c文件和.h文件。除了： shell.c：該文件包括main()函數，用于創建CLP可執行程序。 tclsqlite.c：該文件用于TCL支持。空注：我使用的版本(sqlite-source-3_6_18.zip)有些函數有重復定義，還得去掉兩個文件，不知會引起什么后果，它們是fts3.c和fts3_tokenizer.c。 3\. 執行構建(Build)命令，OK。還可以選擇構建線程完全的DLL或發布 (Release) 版的DLL，參原文。 ## 用Microsoft?Visual?C++構建SQLite?CLP 方法基本同上。創建項目時選擇Win32 Console Application，添加文件時把shell.c也加上，即可。 ## 使用SQLite數據庫 SQLite的CLP是使用和管理SQLite數據庫最常用的方法。它可運行于多種平臺，學會使用CLP，可以保證你永遠有一個通用和熟悉的途徑來管理你的數據庫。CLP其實是兩個程序。它可以運行在命令行模式下完成各種數據庫管理任務，也可以運行在Shell模式下，以交互的方式執行查詢操作。 ## Shell模式下使用CLP 運行DOS shell，進入工作目錄，在命令行上鍵入sqlite3命令，命令后跟隨一個可選的數據庫文件名。如果在命令行上不指定數據庫名，SQLite將會使用一個內存數據庫，其內容在退出CLP時將會丟失。 CLP以交互形式運行，你可以在其上執行查詢、獲得schema信息、導入/導出數據和執行其它各種各樣的數據庫任務。CLP認為你輸入的任何語句都是一個查詢命令(query)，除非命令是以點(.)開始，這些命令用于特殊操作。鍵入.help或.h可以得到這些操作的完整列表。鍵入.exit 或.e退出CLP。讓我們從創建一個稱為test.db的數據庫開始。在DOS shell下鍵入： sqlite3 test.db 盡管我們提供了數據庫名，但如果這個數據庫并不存在，SQLite并不會真正地創建它。SQLite會等到你真正地向其中增加了數據庫對象之后才創建它，比如在其中創建了表或視圖。這樣做的原因是給你機會在將數據庫寫到外部文件之前對數據庫做一些永久性的設置，如頁的大小等。有些設置，如頁大小、字符集(UTF-8或UTF-16)等，一旦數據庫創建之后就不能再修改了。這個中間期是你能改它們的唯一機會。我們采用默認設置，因此，要將數據庫寫到磁盤，我們僅需要在其中創建一個表。輸入如下語句： sqlite> create table test (id integer primary key, value text); 現在你有了一個稱為test.db的數據庫文件，其中包含一個表test，該表包含兩個字段。 + 一個稱為id的主鍵字段,它帶有自動增長屬性。無論何時你定義一個整型主鍵字段，SQLite 都會對該字段應用自動增長屬性。 + 一個簡單的稱為value的文本字段。向表中插入幾行數據： ``` sqlite> insert into test (value) values('eenie'); sqlite> insert into test (value) values('meenie'); sqlite> insert into test (value) values('miny'); sqlite> insert into test (value) values('mo'); ``` 將插入的數據取回： ``` sqlite> .mode col sqlite> .headers on sqlite> SELECT * FROM test; ``` 系統顯示： ``` id value 1 eenie 2 meenie 3 miny 4 mo ``` SELECT語句前的兩個命令(.headers and .mode)用于改進輸出的格式。可以看到SQLite為id字段賦予了連接的整數值，而這些值我們在INSERT語句中并沒的提供。對于自動增長的字段，你可能會關心最后插入的一條記錄該字段的取值，此值可以用SQL函數last_insert_rowid()得到。 ``` sqlite> select last_insert_rowid(); last_insert_rowid() 4 ``` 在退出CLP之前，讓我們來為數據庫創建一個索引和一個視圖，后面的內容中將會用到它們。 ``` sqlite> create index test_idx on test (value); sqlite> create view schema as select * from sqlite_master; ``` 使用.exit命令退出CLP。 ``` sqlite> .exit C:\Temp> ``` ### 獲得數據庫的Schema信息有幾個shell命令用于獲得有關數據庫內容的信息。你可以鍵入命令.tables [pattern]來得到所有表和視圖的列表，其中[pattern]可以是任何類SQL的操作符。執行上述命令會返回符合條件的所有表和視圖，如果沒有pattern項，返回所有表和視圖。 ``` sqlite> .tables schema test ``` 可以看到我們創建的表test和視圖schema。同樣的，要顯示一個表的索引，可以鍵入命令.indices [table name]： ``` sqlite> .indices test test_idx ``` 可以看到我們為表test所創建的名為test_idx的索引。使用.schema [table name]可以得到一個表或視圖的定義(DDL)語句。如果沒提供表名，則返回所有數據庫對象(包括table、indexe、view和index)的定義語句： ``` sqlite> .schema test CREATE TABLE test (id integer primary key, value text); CREATE INDEX test_idx on test (value); sqlite> .schema CREATE TABLE test (id integer primary key, value text); CREATE VIEW schema as select * from sqlite_master; CREATE INDEX test_idx on test (value); ``` 更詳細的schema信息可以通過SQLite唯一的一個系統視圖sqlite_master得到。這個視圖是一個系統目錄，它的結構如表2-1所示。表2-1 sqlite_master表結構 | 編號 | 字段 | 說明 | | --- | --- | --- | | 1 | type | 值為"table"、 "index"、 "trigger"或"view"之一。 | | 2 | name | 對象名稱，值為字符串。 | | 3 | tbl_name | 如果是表或視圖對象，此字段值與字段2相同。如果是索引或觸發器對象，此字段值為與其相關的表名。 | | 4 | rootpage | 對觸發器或視圖對象，此字段值為0。對表或索引對象，此字段值為其根頁的編號。 | | 5 | SQL | 字符串，創建此對象時所使用的SQL語句。 | 查詢當前數據庫的sqlite_master表，返回： ``` sqlite> .mode col sqlite> .headers on sqlite> select type, name, tbl_name, sql from sqlite_master order by type; type ??name ??????tbl_name ??sql --------- index ?test_idx ??test ??????CREATE INDEX test_idx on test (value) table ?test ??????test ??????CREATE TABLE test (id integer primary view ??schema ????schema ????CREATE VIEW schema as select * from s ``` ■Tip：使用向上的箭頭鍵可以回滾到前面輸入過的命令。 ### 數據導出可以使用.dump命令將數據庫導出為SQL格式的文件。不使用任何參數，.dump將導出整個數據庫。如果提供參數，CLP把參數理解為表名或視圖名。 ``` sqlite> .output file.sql sqlite> .dump sqlite> .output stdout ``` ### 數據導入有兩種方法可以導入數據，用哪種方法決定于要導入的文件的格式。如果文件由SQL語句構成，可以使用.read命令導入(執行)文件。如果文件是由逗號或其它定界符分隔的值(comma-separated values, CSV)組成，可使用.import [file][table]命令。此命令將解析指定的文件并嘗試將數據插入到指定的表中。 ``` sqlite> .show echo: off explain: off headers: on mode: column nullvalue: "" output: stdout separator: "|" width: ``` .read命令用來導入由.dump命令創建的文件。如果要使用前面作為備份文件所導出的file.sql，需要先移除已經存在的數據庫對象(test表和schema視圖)，然后用下面方法導入： ``` sqlite> drop table test; sqlite> drop view schema; sqlite> .read file.sql ``` ### 格式化 CLP提供了幾個格式化選項命令。最簡單的是.echo, 如果設置.echo on，則新輸入的命令在執行前都會回顯，默認值是off。.headers設置為on時，查詢結果顯示時帶有字段名。當遇到NULL值時，如果需要以一個字符串來顯示，使用.nullvalue命令設置，如： ``` sqlite> .nullvalue NULL ``` 默認情況下使用空串。如果要改變CLP的shell提示符，使用.prompt [value]，如： ``` sqlite> .prompt 'sqlite3> ' sqlite3> ``` .mode命令可以設置結果數據的幾種輸出格式。可選的格式為csv、column、html、insert、line、list、tabs和tcl。默認值是list，在此模式下顯示結果時列間以默認的分隔符分隔。如果你想以CSV格式輸出一個表的數據，可如下操作： ``` sqlite3> .output file.csv sqlite3> .separator , sqlite3> select * from test; sqlite3> .output stdout ``` 文件file.csv的內容為： ``` 1,eenie 2,meenie 3,miny 4,mo ``` 因為有一個CSV模式，所以下面的命令會得到相似的結果： ``` sqlite3> .output file.csv sqlite3> .mode csv sqlite3> select * from test; sqlite3> .output stdout ``` ## 在命令行方式下執行CLP 在DOS或UNIX的命令行方式下，直接執行SQLite的數據庫操作。 ## 數據庫管理所有的數據庫管理任務都可以在shell和命令行模式下完成。 ## 創建、備份和刪除數據庫數據庫的備份有兩種方法。第1種是使用.dump，可得到SQL格式的文件。在命令行方式下可如下做： ``` sqlite3 test.db .dump > test.sql ``` 在CLP中可如下做： ``` sqlite> .output file.sql sqlite> .dump sqlite> .exit ``` 相應地，導入一個SQL格式備份的數據庫可如下做： ``` sqlite3 test.db < test.sql ``` 此處假設test.db不存在。如果它存在，則或許會因為數據庫中有同名的對象而出錯。可以用復制的方法得到一個二進制的數據庫文件拷貝。但也許在復制之前你想先抽空(vacuum)它，也就是釋放數據庫文件中未使用的空間，以得到一個更小的數據庫文件。可操作如下： ``` sqlite3 test.db VACUUM cp test.db test.backup ``` 一般情況下，二進制的備份如不SQL備份兼容性好。盡管SQLite有很好的向上兼容性和各操作系統間文件格式的一致性，但如果想要將備份文件保留很長時間，還是SQL格式保險一些。當一個數據庫你不想再用時，簡單地從操作系統中將其文件刪除就行了。 ## 獲得數據庫文件的信息按前文所述，獲得數據庫信息的主要途徑是使用sqlite_master視圖，它提供一個數據庫所包含的所有對象的細節信息。如果你想獲得關于物理的數據庫結構信息，可以使用一個稱為SQLite Analyzer的工具，它可以在SQLite網站上下載得到。SQLite Analyzer可以提供磁盤SQLite數據庫的詳細技術信息。 (輸出結果略) ## 其它SQLite工具有很多其它開源的或商業的程序可工作于SQLite，其中具有優秀圖形化界面且跨平臺的有： + SQLite Database Browser (http://sqlitebrowser.sourceforge.net) + SQLite Control Center (http://bobmanc.home.comcast.net/sqlitecc.html) + SQLiteManager (www.sqlabs.net/sqlitemanager.php)