好站 : SQLite

這兩天寫爬蟲有用到 SQLite, 除了複習自己寫的筆記外, 還找到下面這篇很棒的文章 :

# https://hackmd.io/@wootu/Bk_pYqWIM?type=view

此文從安裝 DB Browser for SQLite 建立資料庫, 到新增/修改/刪除資料表,  CRUD 作業之 SQL 語法, 資料庫匯出匯入, 到資料表欄位說明等都整理得非常簡明扼要, 是臨時要 SQLite 用時可以迅速找到解答的好地方. 

好用的 SQLite 管理軟體 SQLite Expert Personal

SQLite 是一款小而美的單檔資料庫軟體, 今天找到一款好用的 SQLite 資料庫管理軟體 SQLite Expert Personal 來操作資料庫, 網址如下 : 

# https://sqliteexpert.com/download.html

官網提供商用收費的 SQLite Expert Professional 試用版 (30 日到期) 與免費的 Personal 版 (功能較少但無到期日), 安裝 Personal 版就夠用了 : 

關於付費之專業版與免費版功能差異比較參考 :

# https://sqliteexpert.com/features.html

1. 安裝軟體 : 

執行下載之檔案進行安裝 : 

安裝完畢執行此軟體 : 

2. 新增資料庫 : 

按左上角的按鈕即可新增資料庫 :

3. 新增資料表 : 

開啟資料庫後, 按上面中間的 "New Table" 鈕 :

輸入資料表名稱 : 

切換到第二個頁籤 "Columns", 按右上角的 "Add" 鈕 :

於彈出視窗中設定欄位資料後按 OK, 先輸入 date, stock_id, 與 stock_name 這三個欄位 :

按右下角的 "Apply" 鈕即將此三欄位加入資料表中 : 

樹莓派安裝 SQLite 資料庫

SQLite 資料庫是一個廣受歡迎的嵌入式 (embedded, 非 client-server 架構) 輕量級 SQL 關聯式資料庫, 被許多軟體用來儲存系統資料 (例如 Android 平台, 瀏覽器), 它具有如下特點 :

1. 開源 (免費)
2. 無伺服器 (無須啟動, 不占用程序)
3. 單一檔案 (備份方便)
4. 零設定 (使用簡單)
5. 跨平台 (可移植性佳)
6. 自我完善 (無外部相依軟體)

參考 :

# https://zh.wikipedia.org/wiki/SQLite

樹莓派 Raspbian 並未預載 SQLite, 需自行安裝.


一. 安裝 SQLite :

安裝 SQLite 指令如下 :

sudo apt-get install sqlite3 

pi@raspberrypi:~ $ sudo apt-get install sqlite3 
正在讀取套件清單... 完成
正在重建相依關係       
正在讀取狀態資料... 完成
建議套件：
  sqlite3-doc
下列【新】套件將會被安裝：
  sqlite3
升級 0 個，新安裝 1 個，移除 0 個，有 0 個未被升級。
需要下載 709 kB 的套件檔。
此操作完成之後，會多佔用 1,991 kB 的磁碟空間。
下載:1 http://mirror.ossplanet.net/raspbian/raspbian stretch/main armhf sqlite3 armhf 3.16.2-5+deb9u1 [709 kB]
取得 709 kB 用了 5s (136 kB/s)                     
選取了原先未選的套件 sqlite3。
（讀取資料庫 ... 目前共安裝了 136703 個檔案和目錄。）
Preparing to unpack .../sqlite3_3.16.2-5+deb9u1_armhf.deb ...
Unpacking sqlite3 (3.16.2-5+deb9u1) ...
設定 sqlite3 (3.16.2-5+deb9u1) ...
Processing triggers for man-db (2.7.6.1-2) ...

整個程式只有 709KB, 實在非常輕巧啊! 

二. 建立 SQLite 資料庫 :   

在命令列下 sqlite3 test.db 可建立或載入資料庫, 若 test.db 不存在則在目前目錄下建立名為 test.db 的 SQLite 資料庫, 若已存在載入資料庫, 並進入 sqlite3 的 shell 介面, 下 .help 指令會顯示使用說明, 例如 :

pi@raspberrypi:~ $ sqlite3 test.db 
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite>

資料庫操作指令除 SELECT 外需用 BEGIN; 開始, 用 COMMIT; 結束. 以下操作參考了下面文章 : 

# SQLite Database on a Raspberry Pi
# SQLite DB on Raspberry Pi
# 進階篇 - SQLite資料庫
# 樹莓派學習筆記——SQLite操作簡述

關於 SQL 指令可參考之前整理的筆記 :

# 最常用的 SQL 指令

三. 建立資料表 : 

建立資料表用 CREATE TABLE 指令, 例如 :

sqlite> BEGIN;   
sqlite> CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT); 
sqlite> COMMIT;   

上面建立了一個 dhtreadings 資料表. 用 .table 指令可列出目前資料庫已有哪些資料表, 而 .fullschema 則列出全部資料表的結構, 例如 :

sqlite> .tables   
dhtreadings
sqlite> .fullschema 
CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT);
/* No STAT tables available */

以下為資料表的 CRUD 操作 :


四. 新增與查詢紀錄 :

在資料表中新增資料使用 INSERT 指令, 而查詢則用 SELECT 指令, 例如 : 

sqlite> BEGIN; 
sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.4, 48, date('now'), time('now'), "manual"); 
sqlite> COMMIT;     

這樣就新增了一筆紀錄到資料表 dhtreadings, 可用 SELECT 查詢資料表 :

sqlite> SELECT * FROM dhtreadings;   
1|22.4|48|2019-04-10|02:59:32|manual   

可知目前整個資料表只有一筆紀錄. 再新增一筆紀錄 :

sqlite> BEGIN;   
sqlite> INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(22.5, 48.7, date('now'), time('now'), "manual"); 
sqlite> COMMIT; 

用 SELECT 查詢已有兩筆紀錄 :

sqlite> SELECT * FROM dhtreadings;   
1|22.4|48|2019-04-10|02:59:32|manual
2|22.5|48.7|2019-04-10|03:00:40|manual


五. 更新紀錄 : 

更新紀錄使用 UPDATE 指令, 可同時更新一個以上欄位, 每個欄位用逗號隔開, 例如更改上面第二筆紀錄之溫濕度 :

sqlite&glt; BEGIN; 
sqlite&glt; UPDATE dhtreadings SET temperature='33', humidity='66' WHERE id='2'; 
sqlite&glt; COMMIT; 
sqlite&glt; SELECT * FROM dhtreadings;   
1|22.4|48|2019-04-10|02:59:32|manual
2|33|66|2019-04-10|03:00:40|manual 

可見 id=2 的溫溼度都被修改了.


六. 刪除紀錄 :

刪除紀錄使用 DELETE 指令, 必須用 WHERE 限定刪除對象, 否則資料表內的紀錄會全部被刪除, 例如刪除上面第一筆紀錄 :

sqlite&glt; BEGIN;
sqlite&glt; DELETE FROM dhtreadings WHERE id='1'; 
sqlite&glt; COMMIT; 
sqlite&glt; SELECT * FROM dhtreadings; 
2|33|66|2019-04-10|03:00:40|manual

可見第一筆資料已被刪除.


七. 刪除資料表 : 

刪除資料表用 DROP TABLE 指令, 與 SELECT 一樣不需要用 BEGIN 與 COMMIT, 例如 :

sqlite&glt; .tables 
dhtreadings 
sqlite&glt; DROP TABLE dhtreadings;   
sqlite&glt; .tables 
sqlite&glt;

可見資料表 dhtreadings 已經被刪除了. 但如果資料表不存在會出現錯誤訊息, DROP TABLE 指令最好加上 IF EXISTS, 例如上面已刪除資料表 dhtreadings, 若再刪除一次就會報錯 :

sqlite&glt; DROP TABLE dhtreadings; 
Error: no such table: dhtreadings   
sqlite&glt; DROP TABLE IF EXISTS dhtreadings;   
sqlite&glt;


八.  跳出 SQLite Shell : 

在 SQLite Shell 輸入 .quit 或 .exit 均可跳出 Shell 回到命令列 :

sqlite&glt; .quit   
pi@raspberrypi:~ $


以上是用 SQLite Shell 直接操作資料庫, 亦可用 Python, Java 等程式語言存取 SQLite 資料庫, 參考 :

# Python 學習筆記 : 資料庫存取測試 (一) SQLite
# Java使用JDBC操作SQLite
# SQLite Java