[Linux 命令] sqlite3 : 數據庫操作

屬性 : 應用程式 : 數據庫操作
語法 : sqlite3 [options] [databasefile] [SQL]
簡介 : 

sqlite3 is a terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results in multiple formats. sqlite3 can also be used within shell scripts and other applications to provide batch processing features.

參數 | 功能

-init file | Read and execute commands from file , which can contain a mix of SQL statements and meta-commands.
-echo | Print commands before execution.
-[no]header | Turn headers on or off.
-column | Query results will be displayed in a table like form, using whitespace characters to separate the columns and align the output.
-html | Query results will be output as simple HTML tables.
-line | Query results will be displayed with one value per line, rows separated by a blank line.
-list | Query results will be displayed with the separator (|, by default) character between each field value. The default.
-separator separator | Set output field separator. Default is '|'.
-nullvalue string | Set string used to represent NULL values. Default is '' (empty string).
-version | Show SQLite version.
-help | Show help on options and exit.

SQLITE META-COMMANDS :
在使用 sqlite3 進入互動模式後, 可以使用一系列的 meta-commands 來對數據庫進行 administrative 操作. 而這些 meta-commands 通常會有前置 "." 已與一般命令進行區別. 而事實上你可以在互動模式下鍵入 '.help' 來檢視所有的 meta-commands :

~ # sqlite3 <進入互動模式>
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .help <檢視支援的 meta-commands>
.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
...

執行範例 :
* 進入數據庫 cav.LOG.db 並建立表格 memos 後 插入兩筆數據

~ # sqlite3 /var/log/cav.LOG.db <進入數據庫 cav.LOG.db>
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table memos(text,priority INTEGER); <建立表格 memos>
sqlite> insert into memos values('work', 10); <插入數據1>
sqlite> insert into memos values('lunch with john', 100); <插入數據2>
sqlite> select * from memos; <查詢數據庫 memos>
work|10
lunch with john|100
sqlite>

* 不進入互動模式化, 使用 -line 查詢數據庫 memos

~ # sqlite3 /var/log/cav.LOG.db 'select * from memos where priority >20;' 
lunch with john|100

補充說明 :
* sqlite3(1) - Linux man page