在寫 iOS 的 SQLite 相關程式之前,先用終端機把語法熟悉一遍,會省下不少除錯時間。以下用一個學生成績的範例,把常用指令走過一次。
$ sqlite3 data.db SQLite version 3.7.13 2012-07-17 17:46:21 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
進入後所有 SQL 都要以分號結尾,而 .table 這類以點號開頭的是 SQLite 自己的管理指令。
sqlite> create table USER(id, name, score); sqlite> .table USER sqlite> drop table USER; sqlite> create table if not exists USER(id, name, score); -- 建議加上 if not exists
sqlite> insert into USER values(1, "普京", 90); sqlite> insert into USER values(2, "奧巴馬", 50); sqlite> select * from USER; 1|普京|90 2|奧巴馬|50
輸出預設以 | 分隔欄位,這是 SQLite 的 list 模式。
sqlite> delete from USER where id = 2; sqlite> update USER set id = 2 where id = 6; sqlite> update USER set score = 89 where id = 2;
sqlite> select name, score from USER; -- 指定欄位 sqlite> select * from USER limit 3; -- 取前三筆 sqlite> select * from USER order by id; -- 依 id 升冪 sqlite> select * from USER order by score desc; -- 依 score 降冪 sqlite> select count(*) from USER; -- 5 sqlite> select sum(score) from USER; -- 519 sqlite> select avg(score) from USER; -- 103.8
用 integer primary key autoincrement 建立自增主鍵:
sqlite> create table if not exists WUGONG(wid integer primary key autoincrement, name, uid);
sqlite> insert into WUGONG(name, uid) values("葵花", 5);
sqlite> insert into WUGONG(name, uid) values("獨孤九劍", 4);
sqlite> insert into WUGONG(name, uid) values("降龍十八掌", 3);
sqlite> insert into WUGONG(name, uid) values("黯然銷魂掌", 2);
sqlite> insert into WUGONG(name, uid) values("一陽指", 1);
兩表以 USER.id = WUGONG.uid 關聯:
sqlite> select USER.id, USER.name, WUGONG.name, USER.score ...> from USER, WUGONG where USER.id = WUGONG.uid; 1|普京|一陽指|90 3|神雕|降龍十八掌|100 4|楊過|獨孤九劍|150 5|王麗坤|葵花|90 2|陳喬恩|黯然銷魂掌|89 sqlite> select USER.id, USER.name, WUGONG.name, USER.score ...> from USER, WUGONG where USER.id = WUGONG.uid order by USER.score; -- 成績升冪
integer primary key autoincrement 時,該欄會自動遞增,新增時可省略不填。INNER JOIN;若某個 USER 沒有對應的 WUGONG 紀錄,該列不會出現在結果中,這種情況要改用 LEFT JOIN。order、group、table),否則語法會衝突。.headers on 與 .mode column,讓終端機的輸出更容易閱讀。