考慮到現在進行的專案將會使用到後端資料庫等相關內容,因此今日筆者想來對此部份做個簡單的整理!那麼就讓我們開始吧!
sqlite3
,可直接在終端機輸入 SQL 指令。([sqlite.org][1]).
的是 shell 指令,不是 SQL):.tables -- 列出所有表
.schema users -- 查看指定表的結構
.quit -- 離開
.help -- 查看所有支援的 shell 指令
先建立兩個表:使用者 users
與貼文 posts
,示範主鍵(Primary)、唯一鍵(Unique)、外鍵(Foreign)與檢查條件 (check)。
-- 建 users 表格
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
created_at INTEGER NOT NULL
);
-- 建 posts 表格,並宣告對 users(id) 的外鍵
PRAGMA foreign_keys = ON; -- SQLite 建議顯式開啟外鍵約束
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
body TEXT NOT NULL CHECK (length(body) <= 5000),
created_at INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
插入(INSERT):
INSERT INTO users (username, email, created_at)
VALUES ('alice', 'alice@example.com', strftime('%s','now'));
INSERT INTO posts (user_id, title, body, created_at)
VALUES (1, 'Hello SQLite', 'First post!', strftime('%s','now'));
查詢(SELECT):
-- 全部欄位
SELECT * FROM users;
-- 指定欄位 + 排序 + 限制筆數
SELECT id, username, email
FROM users
ORDER BY id DESC
LIMIT 10;
更新(UPDATE)與刪除(DELETE):
UPDATE users SET email = 'alice@newmail.com' WHERE id = 1;
DELETE FROM posts WHERE id = 1;
-- 條件 + 排序
SELECT id, username
FROM users
WHERE username LIKE 'a%'
ORDER BY id DESC;
-- 聚合與分組
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) >= 1
ORDER BY post_count DESC;
-- 取出每篇貼文與其作者名稱
SELECT p.id, p.title, u.username
FROM posts AS p
JOIN users AS u ON u.id = p.user_id
ORDER BY p.id DESC;
一次執行多筆變更時,建議包在交易裡,能確保「全部成功或全部失敗」:
BEGIN; -- 或 BEGIN TRANSACTION;
INSERT INTO users(username, email, created_at)
VALUES ('bob', 'bob@example.com', strftime('%s','now'));
INSERT INTO posts(user_id, title, body, created_at)
VALUES (last_insert_rowid(), 'Hi', 'Bob here', strftime('%s','now'));
COMMIT; -- 若要放棄則改用 ROLLBACK;
SQLite 支援 BEGIN / COMMIT / ROLLBACK
,END TRANSACTION
等同 COMMIT
。
對常查詢或常排序的欄位加索引可提升查詢速度(但插入/更新會稍增成本):
-- 一般索引
CREATE INDEX IF NOT EXISTS idx_posts_userid ON posts(user_id);
-- 唯一索引(避免重複資料)
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);
參考資料: