Introduction SQLite

大家比較耳熟能詳的database大多是client/server的架構居多，如：MySQL、PostgreSQL、MS-SQL和Oracle等等，而SQLite感覺比較像MS的Access， 程式本身就負責開啟資料庫並且直接操作，更多的資訊可以參考官網http://www.sqlite.org/。

以下就個人在SQLite網站看到的介紹，寫下一些筆記。
About SQLite
SQLite不像許多SQL資料庫有分開的Server process。SQLite直接對資料庫檔案做存取，這些資料庫檔案室可以跨平台的， 意味著您可以直接在32/64和big-endian/little-endian複製這些資料庫檔案，都能正確地被SQLite所存取。

Appropriate Uses For SQLite/Situations Where Another RDBMS May Work Better SQLite因為直接對database做存取，所以多個client同時對某database做存取可能會有問題。 如果是高流量(High-volume Websites)或是高資料量(Very large datasets)都不適合SQLite，畢竟它是拿來給embedded用的。

總體而言，對於小資料庫的應用SQLite已經是非常好的選擇了。


基本操作

brook:~$ ls hello.db
ls: cannot access hello.db: No such file or directory
brook:~$ sqlite hello.db
Loading resources from /home/brook/.sqliterc
SQLite version 2.8.17
Enter ".help" for instructions
sqlite> .help
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in a text format
.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.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line(s)", "column(s)",
                       "insert", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.nullvalue STRING      Print STRING instead of nothing for NULL data
.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
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode

sqlite> .tables
sqlite> create table hello (x integer PRIMARY KEY ASC, y);
sqlite> .tables
hello
sqlite> insert into hello (y) values('a');
sqlite> insert into hello (y) values(10);
sqlite> insert into hello (y) values(datetime('now'));
sqlite> select * from hello;
x           y
----------  ----------
1           a
2           10
3           2012-07-30
sqlite> .quit

我們也可以直接在command line上面執行sqlite

brook:~$ sqlite hello.db .dump
Loading resources from /home/brook/.sqliterc
BEGIN TRANSACTION;
create table hello (x integer PRIMARY KEY ASC, y);
INSERT INTO hello VALUES(1,'a');
INSERT INTO hello VALUES(2,10);
INSERT INTO hello VALUES(3,'2012-07-30 04:20:12');
COMMIT;
brook:~$ sqlite hello.db "select * from hello"
Loading resources from /home/brook/.sqliterc
x           y
----------  ----------
1           a
2           10
3           2012-07-30
brook:~$ cat ~/.sqliterc
.mode column
.header on
.nullvalue NULL

更多SQL語法請參考http://www.sqlite.org/lang.html

參考資料：
• http://www.sqlite.org/