十分鐘掌握SQLite操作

最近用Ruby寫了一個七牛的demo參賽作品，使用了sqlite3，用到很多操作，利用假期的時間，簡單做一個快速掌握SQLite命令的小入門。

SQLite是一個開放原始碼的資料庫引擎，具有獨立，無伺服器依賴，零配置，支援事務等特點。SQLite一直以輕量級為特點，在移動和嵌入式裝置上使用廣泛，官方稱其是世界上部署最廣泛的資料庫引擎。

本文主要側重部分常用操作命令的介紹。試圖以最簡單的示例來展示如何操作。

強大的命令集

首先我們看一下sqlite3提供了哪些強大的命令。

sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail ON|OFF           Stop after hitting an error.  Default OFF
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ?ON|OFF?      Turn output mode suitable for EXPLAIN on or off.
                         With no args, it turns EXPLAIN on.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.load FILE ?ENTRY?     Load an extension library
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Print STRING in place of NULL values
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.show                  Show the current values for various settings
.stats ON|OFF          Turn stats on or off
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
.timer ON|OFF          Turn the CPU timer measurement on or off
sqlite>

以”.“開始的命令規則

看到了上面的全部命令，可以觀察到，所有的命令都是以”.“開始的。而常用的SQL語句是格式自由的，並且可以跨越多行，空白字元（whitespace）和註釋可以出現在任何地方。而SQLite中以.開始的命令有更多的限制，具體如下

• 所有命令以 . 開始，並且 . 的左側不包含任何空白字元
• 所有命令必須全部包含在一行輸入行中
• 所有命令不能出現在SQL語句之中
• 命令不識別註釋

常用操作

建立一個資料庫檔案

#找一個不存在的檔案
09:35:16-androidyue/tmp$ cat test.db
cat: test.db: No such file or directory

#使用sqlite3 想要建立的資料庫檔案
09:35:28-androidyue/tmp$ sqlite3 test.db

#進入sqlite，執行建表語句
sqlite> CREATE TABLE qn_uploaded(filePath VARCHAR(255), bucket VARCHAR(63),  lastModified FLOAT);
#退出SQLite
sqlite> .exit

#檢視指定的檔案，建立成功
09:42:26-androidyue/tmp$ cat test.db
09:44:45-androidyue/tmp$ dedqn_uploadedCREATE TABLE qn_uploaded(filePath VARCHAR(255), bucket VARCHAR(63),  lastModified FLOAT)

開啟已存在的資料庫檔案

22:56:15-androidyue~ $ sqlite3 database_file.db

檢視資料庫

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/androidyue/qiniu/.qiniu.db
1    temp

檢視資料表

sqlite> .tables
qn_uploaded

檢視建表語句

sqlite> .schema qn_uploaded
CREATE TABLE qn_uploaded(filePath VARCHAR(255), bucket VARCHAR(63),  lastModified FLOAT);

顯示欄位名稱

#沒有開啟
sqlite> select * from qn_uploaded;
/home/androidyue/Documents/octopress/public//images/email.png|droidyue|1410096518.43964

#開啟之後
sqlite> .header on
sqlite> select * from qn_uploaded;
filePath|bucket|lastModified
/home/androidyue/Documents/octopress/public//images/email.png|droidyue|1410096518.43964

匯出資料表結構和資料(文字形式)

sqlite> .dump qn_uploaded
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE qn_uploaded(filePath VARCHAR(255), bucket VARCHAR(63),  lastModified FLOAT);
INSERT INTO "qn_uploaded" VALUES('/home/androidyue/Documents/octopress/public/images/dotted-border.png','droidyue',1410096552.54864);
COMMIT;

調整輸出

sqlite3程式可以使用八種不同的格式顯示結果。 這些格式是”csv”, “column”, “html”, “insert”, “line”, “list”, “tabs”, and “tcl”. 你可以使用.mode命令來進行切換輸出格式

預設的輸出模式list，使用了list模式，每條查詢結果記錄都會輸出到一行，每一列使用一個分割符分割，預設的分割符是 “|“，list模式有一個常用的使用情況，就是當你想對查詢結果記性額外處理（比如AWK處理）時，會事半功倍。

列表模式輸出

sqlite> select * from qn_uploaded;
/home/androidyue/Documents/octopress/public//images/email.png|droidyue|1410096518.43964

修改列表模式分割符

sqlite> .separator ", "
sqlite> select * from qn_uploaded;
/home/androidyue/Documents/octopress/public//images/email.png, droidyue, 1410096518.43964

使用Line模式

每行的輸出格式為 欄位名 = 欄位值

sqlite> .mode line
sqlite> select * from qn_uploaded;
    filePath = /home/androidyue/Documents/octopress/public//images/email.png
      bucket = droidyue
lastModified = 1410096518.43964

使用列模式

sqlite> .mode column
sqlite> select * from qn_uploaded;
/home/androidyue/Documents/octopress/public//images/email.png  droidyue    1410096518.43964
/home/androidyue/Documents/octopress/public/images/rss.png     droidyue    1410096552.54764

輸出內容

輸出結果

預設情況下，所有的查詢結果都是都是作為標準的輸出展示。使用.output可以將輸出結果定向到檔案中。

sqlite> select * from qn_uploaded;
sqlite> .exit
17:48:54-androidyue~/Documents/octopress/qiniu (master)$ cat /tmp/test.txt
file  bucket         last
----  -------------  ----
/home/androidyue/Documents/octopress/public//images/email.png  droidyue       1410096518.43964
/home/androidyue/Documents/octopress/public/images/rss.png  droidyue       1410096552.54764

備份和恢復

備份

#語法 .backup ?DB? FILE      Backup DB (default "main") to FILE
sqlite> .backup main /tmp/main.txt

恢復

#語法.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.restore main  /tmp/main.txt