前言

前幾天我們已經完成 Notes API 的 CRUD，目前可以透過 API 查詢、新增、修改和刪除 Note。不過現在的資料還是放在 JavaScript 的 Array 裡，只要 Server 關閉或重新啟動，原本新增的資料就會消失。

例如目前可能是：

const notes = [
  {
    id: 1,
    title: "學習 Node.js",
    content: "開始學習後端",
  },
];

這種方式很適合前期學習 API，因為可以先專注在 Request、Response 和 CRUD 流程，但實際的應用程式需要讓資料長期保存，因此不能一直依賴記憶體中的 Array，而需要把資料交給 Database。

在真正把 Node.js 接上資料庫之前，先理解資料庫最基本的操作會比較容易。今天會先認識 SQL，以及 SQL 如何建立資料表、新增資料、查詢資料、修改資料和刪除資料。這些語法大多是關聯式資料庫共同的基礎，因此這一天先不急著學 PostgreSQL 特有的功能，而是先建立 SQL 的基本能力。

SQL 是什麼？

SQL 是 Structured Query Language 的縮寫，是用來與關聯式資料庫溝通的語言。資料庫負責保存和管理資料，而 SQL 則讓我們可以告訴資料庫要做什麼，例如建立資料表、加入資料、查詢資料、修改資料或刪除資料。

可以先簡單理解成：

Database
→ 負責保存資料

SQL
→ 告訴 Database 要做什麼

因此之後看到：

SELECT *
FROM notes;

這是一段 SQL，而真正執行這段指令的是資料庫系統。這次最終會使用 PostgreSQL，但在開始使用 PostgreSQL 之前，先把 SQL 本身的基本概念建立起來。

Table、Row、Column

關聯式資料庫會使用 Table 組織資料。可以先把 Table 想成一張表格，例如 Notes API 可能有一張 notes：

notes

id | title          | content
---|----------------|--------------------
1  | 學習 Node.js    | 開始學習後端
2  | 學習 SQL        | 開始學習資料庫

整張表就是 Table，例如這裡的 notes；每一筆資料就是一個 Row，例如第一筆 Note 就是一個 Row；而 id、title、content 則是不同的 Column。

前面使用 JavaScript Array 時，每個 Object 可以代表一筆 Note；現在換成關聯式資料庫後，這些資料會變成 Table 裡的一個個 Row，而每個 Object 裡的屬性則對應到不同的 Column。

CREATE TABLE：建立資料表

有了 Database 之後，首先需要建立可以存放資料的 Table。SQL 使用 CREATE TABLE：

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL
);

這裡的 notes 是 Table 名稱，括號裡則是在定義這張 Table 有哪些 Column。id 使用 INTEGER，代表整數；PRIMARY KEY 表示這個欄位可以唯一識別一筆資料；title 和 content 則是文字欄位，而 NOT NULL 表示這些欄位不能沒有值。

這裡先刻意不處理「ID 要不要自動產生」，因為不同資料庫對自動遞增有不同的實作方式。現在先讓學生理解 Table 與欄位的定義，之後進入 PostgreSQL 時，再學習 PostgreSQL 產生 ID 的方式。

Constraint：限制資料應該符合什麼規則

除了定義資料型別之外，Table 還可以設定一些限制，這些限制通常稱為 Constraint。它們可以讓資料庫本身也具備基本的資料保護能力。

前面看到的 PRIMARY KEY 和 NOT NULL 都是 Constraint，另外還有幾個很常見的規則。

UNIQUE 可以限制某個欄位不能出現重複值，例如使用者 Email：

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

DEFAULT 可以設定預設值，例如：

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  status VARCHAR(20) DEFAULT 'draft'
);

如果新增資料時沒有提供 status，資料庫就會使用 draft。

CHECK 則可以限制資料必須符合某個條件，例如：

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) CHECK (price >= 0)
);

這代表 price 不可以小於 0。

這些規則可以理解成資料庫最後一道保護，API 在把資料送進 Database 之前仍然需要進行 Validation，但 Database 本身也可以透過 Constraint 限制不合理的資料。

INSERT：新增資料

建立好 Table 之後，就可以使用 INSERT INTO 新增資料：

INSERT INTO notes (id, title, content)
VALUES (1, '學習 Node.js', '開始學習後端');

再新增一筆：

INSERT INTO notes (id, title, content)
VALUES (2, '學習 SQL', '開始學習資料庫');

這時候 notes Table 裡就會有兩筆資料：

id | title          | content
---|----------------|--------------------
1  | 學習 Node.js    | 開始學習後端
2  | 學習 SQL        | 開始學習資料庫

這和前面使用：

notes.push(note);

其實是在做類似的事情，只是之前是把資料放進 JavaScript Array，現在則是透過 SQL 將資料寫進 Database。

SELECT：查詢資料

新增資料之後，就可以使用 SELECT 查詢：

SELECT *
FROM notes;

• 代表取得所有 Column，因此會把 notes Table 裡的所有資料查詢出來。

如果只需要特定欄位，也可以直接指定：

SELECT id, title
FROM notes;

這時候只會取得 id 和 title。

如果希望替查詢結果的欄位取一個別名，可以使用 AS：

SELECT
  id AS note_id,
  title AS note_title
FROM notes;

這時候查詢結果的欄位名稱就會顯示成 note_id 和 note_title。

WHERE：只查詢符合條件的資料

實際開發時，通常不會每次都把整張 Table 的資料查出來，而是會指定條件。這時候就會使用 WHERE。

例如查詢 id 為 1 的 Note：

SELECT *
FROM notes
WHERE id = 1;

也可以使用其他比較運算子：

SELECT *
FROM notes
WHERE id > 1;

SELECT *
FROM notes
WHERE id >= 2;

SELECT *
FROM notes
WHERE id <> 1;

除了單一條件，也可以使用 AND 和 OR 組合多個條件：

SELECT *
FROM notes
WHERE id > 1
  AND title = '學習 SQL';

AND 代表所有條件都需要成立，OR 則代表符合其中一個條件即可：

SELECT *
FROM notes
WHERE title = '學習 Node.js'
   OR title = '學習 SQL';

如果是多個固定值，也可以使用 IN：

SELECT *
FROM notes
WHERE id IN (1, 2, 3);

而 BETWEEN 則可以查詢一個範圍：

SELECT *
FROM notes
WHERE id BETWEEN 1 AND 3;

這些語法都是在 WHERE 上延伸出來的條件判斷，因此可以一起理解。

LIKE：搜尋文字

如果希望搜尋「包含某段文字」的資料，而不是完全相等，可以使用 LIKE：

SELECT *
FROM notes
WHERE title LIKE '%SQL%';

這裡的 % 可以代表任意數量的字元，因此會找到 title 中包含 SQL 的資料。

例如：

學習 SQL
SQL 入門
SQL 基礎

都可能符合這個條件。

LIKE 是非常常見的 SQL 語法，之後做 API 的搜尋功能時也會經常使用。

NULL：沒有值

SQL 中的 NULL 代表「沒有值」，因此不能直接使用 = 判斷。

例如：

SELECT *
FROM notes
WHERE content = NULL;

這不是正確的寫法。

判斷 NULL 應該使用：

SELECT *
FROM notes
WHERE content IS NULL;

如果要找出有值的資料：

SELECT *
FROM notes
WHERE content IS NOT NULL;

這個差異看起來很小，但在實際寫 SQL 時非常容易遇到。

DISTINCT：去除重複資料

如果只想取得某個欄位中不重複的值，可以使用 DISTINCT：

SELECT DISTINCT title
FROM notes;

假設資料是：

title
-----------
Node.js
SQL
Node.js
PostgreSQL

查詢結果就只會留下不重複的值：

title
-----------
Node.js
SQL
PostgreSQL

ORDER BY：排序

查詢資料時，可以使用 ORDER BY 排序。

例如按照 id 由小到大：

SELECT *
FROM notes
ORDER BY id ASC;

如果希望從大到小：

SELECT *
FROM notes
ORDER BY id DESC;

ASC 表示升冪，DESC 表示降冪。

排序在 API 裡很常見，例如取得最新建立的資料，就可能按照 ID 或建立時間由大到小排序。

UPDATE：修改資料

修改資料使用 UPDATE：

UPDATE notes
SET title = '開始學習 PostgreSQL'
WHERE id = 1;

SET 用來指定要修改的內容，而 WHERE 則決定要修改哪些資料。

這裡同樣需要特別注意 WHERE。如果寫成：

UPDATE notes
SET title = '新的標題';

就會修改整張 Table 裡所有資料的 title。

DELETE：刪除資料

刪除資料使用 DELETE：

DELETE FROM notes
WHERE id = 2;

這會刪除 id 為 2 的 Note。

如果沒有 WHERE：

DELETE FROM notes;

就會刪除 notes Table 裡所有資料，因此 UPDATE 和 DELETE 都應該特別確認條件是否正確。

COUNT：計算資料

除了取得資料本身，有時候也會需要知道目前有幾筆資料，可以使用 COUNT()：

SELECT COUNT(*)
FROM notes;

如果需要計算符合條件的資料，也可以搭配 WHERE：

SELECT COUNT(*)
FROM notes
WHERE id > 1;

COUNT() 是聚合函式的一種，之後還會遇到 SUM()、AVG()、MAX() 和 MIN()，目前先理解 COUNT() 的基本用途即可。

GROUP BY：按照欄位分組

當開始使用聚合函式之後，另一個常見的語法就是 GROUP BY。它可以把具有相同值的資料分成不同群組，再對每一組進行統計。

例如之後如果 Notes 有 user_id，就可以統計每個使用者有幾筆 Note：

SELECT user_id, COUNT(*)
FROM notes
GROUP BY user_id;

這個語法目前不需要深入，但可以先知道 GROUP BY 是 SQL 中用來分組統計的重要工具。

SQL CRUD

到這裡，最基本的 SQL CRUD 就已經完成：

INSERT
→ 新增

SELECT
→ 查詢

UPDATE
→ 修改

DELETE
→ 刪除

這和前面 Notes API 的 CRUD 是完全對應的，只是以前是 JavaScript 在操作 Array，現在則是 SQL 在操作 Database。

因此原本：

notes.push(note);

現在對應：

INSERT INTO notes (...);

原本：

notes.find(...);

現在對應：

SELECT ...
FROM notes
WHERE ...;

原本修改 Array 裡的 Object，現在則對應：

UPDATE notes
SET ...
WHERE ...;

原本刪除 Array 裡的資料，現在則對應：

DELETE FROM notes
WHERE ...;

可以看到，API CRUD 和 SQL CRUD 的概念其實非常接近，差別只是現在資料不再由 Node.js 自己保管，而是交給 Database。

今天先學通用 SQL

今天故意先不深入 PostgreSQL 特有的語法，而是把之後使用各種關聯式資料庫都會遇到的基本 SQL 建立起來。

目前可以先掌握：

建立
├── CREATE TABLE
└── Constraint

新增
└── INSERT

查詢
├── SELECT
├── WHERE
├── AND / OR
├── IN
├── BETWEEN
├── LIKE
├── IS NULL
├── DISTINCT
├── AS
└── ORDER BY

修改
└── UPDATE

刪除
└── DELETE

統計
├── COUNT
└── GROUP BY

這些語法先熟悉之後，之後再進入 PostgreSQL 特有或不同資料庫之間有差異的功能，會比較容易理解。

例如 PostgreSQL 常見的：

GENERATED AS IDENTITY
RETURNING
ILIKE
JSONB

這些都可以留到真正開始使用 PostgreSQL 時再學，不需要一開始就全部放進來。

結論

前面的 Notes API 已經完成 CRUD，但資料目前只是存在 Node.js 的 Array 裡，因此 Server 一旦重新啟動，資料就會消失。今天先不急著處理 PostgreSQL 的安裝或 Node.js 的 Database Connection，而是先把關聯式資料庫最基本的 SQL 建立起來，理解資料表、欄位、Constraint，以及如何使用 SQL 完成資料的新增、查詢、修改和刪除。

這些操作其實就是前面 API CRUD 的另一種版本。之前我們使用 JavaScript 操作 Array，現在則開始使用 SQL 操作 Database。當這些語法熟悉之後，就可以進一步認識 PostgreSQL，了解它和一般 SQL 語法之間的關係，再把 Node.js 接上 Database。

今天先把「如何用 SQL 操作資料」學會，下一步再處理「Node.js 如何使用 PostgreSQL」，這樣資料庫這一段的學習會更清楚。