[轉]SQLite 用法(二)--簡單SQL語法使用

[本文轉載來源為：http://openvanilla.org/wiki/zh/index.php?title=SQLite%E4%BD%BF%E7%94%A8%E6%95%99%E5%AD%B8]

SQLite使用教學
OpenVanilla輸入法與文字處理架構
OS X自從10.4後把SQLite這套相當出名的資料庫軟體，放進了作業系統工具集裡。OS X包裝的是第三版的SQLite，又稱SQLite3。這套軟體有幾個特色：

軟體屬於公共財（public domain），SQLite可說是某種「美德軟體」（virtueware），作者本人放棄著作權，而給使用SQLite的人以下的「祝福」（blessing）：
May you do good and not evil. 願你行善莫行惡
May you find forgiveness for yourself and forgive others. 願你原諒自己寬恕他人
May you share freely, never taking more than you give. 願你寬心與人分享，所取不多於你所施予
支援大多數的SQL指令（下面會簡單介紹）。
一個檔案就是一個資料庫。不需要安裝資料庫伺服器軟體。
完整的Unicode支援（因此沒有跨語系的問題）。
速度很快。
目前在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.db3
如果目錄下沒有foo.db3，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的欄位可以儲存任何東西：文字、數字、大量文字（blob），它會在適時自動轉換。

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

create index film_title_index on film(title);
意思是針對film資料表的title欄位，建立一個名叫film_title_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;
或是年份比較早的電影先列出來（預設為 asc）：

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 <> output.sql
利用輸出的資料，建立一個一模一樣的資料庫（加上以上指令，就是標準的SQL資料庫備份了）：

sqlite3 film.db < title="SQLite%E4%BD%BF%E7%94%A8%E6%95%99%E5%AD%B8">