iT邦幫忙

2025 iThome 鐵人賽

DAY 19
0
Modern Web

【網頁是什麼,能吃嗎】── 零基礎也能學會網頁製作系列 第 19

【Day 19】 後端資料庫的基礎使用

  • 分享至 

  • xImage
  •  

考慮到現在進行的專案將會使用到後端資料庫等相關內容,因此今日筆者想來對此部份做個簡單的整理!那麼就讓我們開始吧!


SQL / SQLite 是什麼?

  • SQL 是用來操作關聯式資料庫的語言(定義表格、查詢、更新、刪除…)。
  • SQLite 是一個「檔案級」的資料庫:整個資料庫就是一個單一檔案,免伺服器、安裝即用,超適合小型專案或學習。官方 CLI 叫做 sqlite3,可直接在終端機輸入 SQL 指令。([sqlite.org][1])

常用指令(前面有 . 的是 shell 指令,不是 SQL):

.tables          -- 列出所有表
.schema users    -- 查看指定表的結構
.quit            -- 離開
.help            -- 查看所有支援的 shell 指令

建表(DDL)

先建立兩個表:使用者 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)
);

基本 CRUD

插入(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;

JOIN(表與表之間的關聯)

-- 取出每篇貼文與其作者名稱
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;

交易(Transaction)

一次執行多筆變更時,建議包在交易裡,能確保「全部成功或全部失敗」:

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 / ROLLBACKEND TRANSACTION 等同 COMMIT

索引(Index)

對常查詢或常排序的欄位加索引可提升查詢速度(但插入/更新會稍增成本):

-- 一般索引
CREATE INDEX IF NOT EXISTS idx_posts_userid ON posts(user_id);

-- 唯一索引(避免重複資料)
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);

參考資料:

  • SQLite 官方文件:([sqlite.org][https://sqlite.org])
  • ChatGPT 之文法整理:ChatGPT

上一篇
【Day 18】Router 是什麼?── Express入門篇
下一篇
【Day 20】為專案加上資料庫 ── Vue.js 實作篇
系列文
【網頁是什麼,能吃嗎】── 零基礎也能學會網頁製作20
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言