自學 SQL 語法？ sqlite 幫你閃電入門！

[我失憶了嗎？ 2022/4 早就寫過一篇差不多的： SQL 自學起手式]

關於資料庫這門課， 我一直覺得最值得初學者花時間的有趣地方是 ER model 跟 select 指令的各種花式變化。 也一直覺得很多同學很可憐， 被帶著從 Oracle 或 MS SQL 的管理開始學起， 光是設定帳號密碼， 熱情跟好奇心就被澆了一些冷水。 就連自由軟體 PostgreSQL 或 mariaDB 我也覺得並不適合新手。 最適合新手的， 是不必帳號密碼、 沒有複雜管理系統、 直接以一個檔案儲存一個資料庫的 sqlite！ 在 debian 系列上： apt install sqlite3 把它安裝起來吧！

一、 建立表格、 從 csv 匯入資料

下載 太陽系天然衛星軌道常數表： wget https://github.com/ckhung/scatplot/raw/master/satellites/satellites.csv 又把以下指令存檔命名為 satellite_schema.sql：

create table satellite (
    name text,
    radius float,
    orbit_major float,
    rev_cycle float,
    planet text
);
-- PRAGMA table_info(satellite);

然後進入 sqlite3 同時建立一個空的資料庫： sqlite3 satellite.sqlite3 副檔名可以隨便取； .db 也是常見的副檔名。

.database			-- 查看資料庫/檔案名稱
.tables				-- 含有哪些表格？ (目前是空的)
.read satellite_schema.sql	-- 讀取 sql 指令檔、 建立一個表格
.tables				-- 現在出現一個表格了
PRAGMA table_info(satellite);	-- 查看表格 satellite 的定義
.mode csv			-- 準備以 csv 格式匯入資料
.import --skip 1 satellites.csv satellite
-- 把 csv 檔匯入唯一的表格， 略過第一列 (欄位名稱那一列)
SELECT * from satellite;	-- 查看所有資料
delete from satellite;		-- 哦， 多餘的空格也被當成字串的一部分！
SELECT * from satellite;	-- 全刪！
-- 在另一個命令列視窗或分頁整修 csv 檔， 刪掉所有多餘的空格：
-- perl -i.bak -pe 's/\s*,\s*/,/g' satellites.csv
.import --skip 1 satellites.csv satellite
-- 重新匯入一次
SELECT * from satellite;	-- 這次就正常啦！

按 ctrl-d 離開 sqlite3、 檢查一下 satellite.sqlite3 的檔案大小。 我的才不到 30K！

二、 litecli 命令/表格名稱/欄位名稱自動快打

讓我們安裝更好用的命令列介面： pip3 install litecli 然後下 litecli satellite.sqlite3 進入 litecli， 大推！ 它是 sqlite3 的外包裝介面， 支援 GNU readline 命令列快打 (快捷鍵一覽表) 特別是 "completion" 的功能超好用。 (指令/表格名稱/欄位名稱自動快打) 你甚至還可以改變 介面色系。 大部分時候我只需要下 (適用於所有 DBMS) 的一般 sql 指令， 所以都用它。 但是對於 sqlite 本身特有的 dot commands， litecli 的支援並不完整， 所以剛剛匯入 csv 檔的時候， 我們還是用原始的 sqlite3 命令列介面。

以下的指令請 不要剪貼！ 請自己打， 才會感受到 completion 的強大！

select * from satellite
select name,planet from satellite
select name,planet,radius from satellite where radius>300 order by planet, radius

1. 當你打 "se" 的時候， litecli 自動帶出兩個相關指令 "select" 跟 "set"。 按一次 tab 鍵就會選 select、 再按一次就會選 set。
2. 同理， 半自動打完 from 之後， 一個字母都還沒打， 它就帶出唯一的表格名稱 satellite， 此時只要按一次 tab 命令列就自動完成。
3. 標準的 sql 指令最後面應該要有分號； 但 litecli 允許你省略。 呃.. 這算是被寵壞、 養成壞習慣嗎？
4. 呈現查詢結果的， 是 less 指令， 按 "q" 可以離開。
5. 打第二句第三句這種句型時， 我會先打 select from satellite 再把遊標移回 select 後面， 這時候 litecli 就會根據我有興趣的表格， 自動推薦欄位名稱讓我選。
6. 當你打過很多不同的 select 指令之後， 下次再打 sel 時， litecli 會推薦最近一次的完整 select 指令。 此時若按 "右箭頭"， 就可以重複這個指令。 也可以按幾次 "Page Up" 叫出更早的 select 指令， 再按 "右箭頭" 選它。

三、 補充說明

在 sql 語法裡面， 何時要用哪種引號？ 倒引號不在 sql 標準裡； 單引號、雙引號的差別： 雙引號用於表格或欄位名稱 (但建議省略)、 單引號用於字串常數 (literal)； mysql 把倒引號視為跟雙引號同樣用途。

想要用 正規表示式 來強化你的搜尋能力嗎？ 安裝套件： apt install sqlite3-pcre 然後在 litecli 裡面執行 .load /usr/lib/sqlite3/pcre.so 之後就可以這樣查詢： select * from satellite where name regexp '^S/20' 也可以把 .load 那一句放在 ~/.sqliterc 裡面， 這樣每次進入 sqlite3 時， 就會自動載入。 litecli 好像沒有對應的設定檔？

如果想把某個 select 的結果 存成 csv 檔， 供試算表或其他程式進一步處理， 可以直接在 shell 裡一步完成： sqlite3 -header -csv satellite.sqlite3 "select * from satellite where planet='Jupiter';" > jovian.csv 或是回到原始的 sqlite3 命令列裡， 切成 csv 模式、 把輸出導向一個 csv 檔、 做完馬上又把輸出導回 stdout：

.headers on
.mode csv
.output jovian.csv
select * from satellite where planet='Jupiter';
.output stdout

官網有一套完整的 教學文。 另外， 這一篇是關於提高效能的建議： SQLite Optimizations for Ultra High-Performance。 即使以我自己這種粗淺的 sqlite 使用程度，都對這兩件事很有感： (1) 根據經常查詢的動作建立 index。 (2) 批次大量修改資料庫內容時 (新增/刪除/修改)， 一定要用 transaction。

教書的最後兩年， 因為有國際班， 終於有機會把 DBMS 撿起來教， 也才有機會認識這個超讚的輕量級資料庫。 如果你還在猶豫該不該用 sqlite， 請搜尋 「why sqlite」。 它並不是永遠的正確答案， 但是你若說不出來為什麼它不適用於你的情境， 那麼很有可能你找的其他複雜 DBMS 系統也不會是正確答案。