[Hexschool Backend experience camp] — 資料庫體驗營 #2

發表於 更新於 分類於 ， ， 文章字數： 8.3k 所需閱讀時間 ≈ 8 分鐘

Introduction & 前言

Banner

小節作業於摘要第六點，如果只想參照作業的部分可以快轉跳至 小節作業：Table 資料表管理

緊接著上週的練習，這週將剩下的影片看完了，把接著一些關於資料表怎麼建立多張，以及怎麼進行 SQL 函數使用

另外還有怎麼進行 Join 查詢、子查詢…等等，一起做筆記心得上來

整篇文章是筆者自己的心得，如果文內有錯誤的地方，還請盡情指出。

本篇會從零開始記錄筆記，目前課程影片的心得在此處以及 [Hexschool Backend experience camp] — 資料庫體驗營，原本以為會有四篇錄影課程的心得紀錄，上一篇是 25%，這篇會多一點是後 75% 的部分。

Summary & 摘要

如果需要觀看小節作業，可以跳過前五章

另外本篇文章還不會實際操作到真實 DB，可以先透過線上網站去練習：線上練習網站 PG SQL

  1. Tables 資料表管理
  2. 跨資料表的撈取
  3. Join 合併資料表查詢
  4. Postgres 函式
  5. 子查詢
  6. 小節作業：Table資料表管理

Tables 資料表管理

張表的複雜度越來越高時，就可以進行拆分 Table，例如一張員工表，裡面含有員工屬於哪個部門

| 員工編號 | 姓名 | 性別 | 出生年月日 | 薪資 | 部門 |
| — | — | — | — | — | — |
| 1 | 王小明 | 男 | 1995/1/1 | 45000 | 資訊部 |
| 2 | 張大帥 | 男 | 1975/12/31 | 22000 | 資訊部 |
| 3 | 陳小美 | 女 | 2000/12/15 | 55000 | 人資部 |

這時候如果想要更改 資訊部 為 開發部，需要每一筆都進行更新，這種時候就可以把部門資訊另外拆出來用一張表去管理，一張表盡量放比較相關資訊的東西就好，如果太複雜會讓表格太難管理。

此外也有可能在新增資料時，誤把 資訊部 打錯，打成 資訊「步」。

這時候我們單獨再用一張表去管理

| 部門編號 | 部門名稱 |
| — | — |
| 1 | 開發部 |
| 2 | 人事部 |

原本的表格就可以把原本 FK(部門欄位) 對應到另一張表格的 PK (部門編號)

| 員工編號[PK] | 姓名 | 性別 | 出生年月日 | 薪資 | 部門[FK] |
| — | — | — | — | — | — |
| 1 | 王小明 | 男 | 1995/1/1 | 45000 | 1 |
| 2 | 張大帥 | 男 | 1975/12/31 | 22000 | 1 |
| 3 | 陳小美 | 女 | 2000/12/15 | 55000 | 2 |

之後如果需要修改部門名稱就簡單多了，從需要修改多筆資料，變成只需要更改一筆資料。

拆解規劃（一對多的資料表）

課程提到口訣是「多的要設定成外來鍵」，也可以理解為一對「多」的部分，可以額外拆出去做一張表管理。

例如上面提到的部門表格，可以先思考兩個角度

  1. 以「員工」角度：一個員工會有「多」個部門
  2. 以「部門」角度：一個部門會有「多」個員工
    如果不考慮複雜的情況，基本上會是第二點更為合理且常見，所以我們就可以找到「多」的部分去拆解 Table。

以上的方式如果從部門的表格來看稱為「一對多的資料表」，如果從員工的表格來看稱為「多對一的資料表」。

另外還有「多對多」或「一對一」…等等，詳細可參考 AndyWu’s 的部落格介紹資料庫關聯種類

關鍵欄位（主鍵、外來鍵）

  • 主鍵 Primary Key（PK）
    • 每張資料表都要有一個主鍵
    • 主鍵必須是唯一的，不能重複
    • 主鍵不能是 Null 值，但外來鍵可以是 Null 值
    • 設定後就不該再進行改動
  • 外來鍵 Foreign Key（FK）
    • 當資料需要關連到其他表格才會用到
    • 命名通常會用 {被參考的資料表名稱}_id 來命名
    • 外來鍵必須對應到被參考的資料表的主鍵

如果建立重複的 PK 或者 FK，則會出現 constraint 的錯誤提醒，例如 duplicate key value violates unique constraint “資料表名稱” 或 insert or update on table “資料表名稱” violates foreign key constraint “資料表名稱”

主鍵設定方式、ID 自動遞增方法

PostgreSQL 的方式

首先創建一張表格，id 設為主鍵，且是 SERIAL 會自動遞增的屬性

1
2
3
4
CREATE TABLE users(
	id SERIAL PRIMARY KEY, -- 員工編號，主鍵
	name VARCHAR(50) -- 員工姓名
)

創建成功後再插入資料

1
2
3
4
INSERT INTO
  users(name)
VALUES('王小明'),
  ('陳小美');

SERIAL 及 UUID 差異

資料的建立如果使用 ID SERIAL 遞增的方式，很容易會被猜到值，並且進入頁面查看資訊，這時候我們就可以使用 UUID 方式替代

有一些資料庫需要手動開啟 UUID 功能，如 PostgreSQL，使用 CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; 可以開啟

| 特性 | SERIAL | UUID |
| — | — | — |
| 格式 | 整數 (1, 2, 3…) | 32位元十六進制 (如：550e8400-e29b-41d4-a716-446655440000) |
| 生成方式 | 自動遞增 | 隨機生成 |
| 空間使用 | 小 | 大 |
| 優點 | 簡單、易讀、節省空間 | 全球唯一、安全性高 |
| 建立語法 | id SERIAL PRIMARY KEY | id UUID PRIMARY KEY DEFAULT gen_random_uuid() |
| 可預測性 | 容易預測下一個值 | 完全隨機，無法預測 |

1
2
3
4
5
6
7
8
-- 建立員工資料表
CREATE TABLE users (
   id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
   name VARCHAR(50) NOT NULL,
   email VARCHAR(100) NOT NULL,
   team_name VARCHAR(50) NULL, 
   salary INTEGER NULL        
);

資料庫建立流程

由於實際情況我們在一開始就會先去思考資料表怎麼建立，不會在建立會員表格後才在想怎麼拆一個新的表格去放部門。

這邊就應該要從部門表個先建立，流程會像底下這樣

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE teams(
	id SERIAL PRIMARY KEY, -- 部門編號
  name VARCHAR(50) -- 部門名稱
);

CREATE TABLE users(
	id SERIAL PRIMARY KEY, -- 員工編號
  name VARCHAR(50), -- 員工名稱
  salary INTEGER, -- 員工薪水
  team_id INTEGER, -- 外來鍵
  FOREIGN KEY(team_id) REFERENCES teams(id)
);

跨資料表的撈取

撈取資料時，我們會需要把一些資料表進行組合，我們先進行資料表的建立

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE teams(
	id SERIAL PRIMARY KEY, -- 部門編號
  name VARCHAR(50) -- 部門名稱
);

CREATE TABLE users(
	id SERIAL PRIMARY KEY, -- 員工編號
  name VARCHAR(50), -- 員工名稱
  salary INTEGER, -- 員工薪水
  team_id INTEGER, -- 外來鍵
  FOREIGN KEY(team_id) REFERENCES teams(id)
);

INSERT INTO
  users(name, salary, team_id)
VALUES('王小明',45000,1),('陳小美',75000,2),('富二代',99999,1),('陳小玉',22000,1),('王曉豪',5000,1);

這時候我們會想要撈出會員以及他所屬部門的資料，如果我們使用以下方式去撈取，會發現資料重複好幾筆。

1
2
3
4
5
6
SELECT
	users.id,
  users.name,
  users.salary,
  teams.name AS 部門名稱
FROM users,teams;

以下我們就會使用幾種方式去做組合查詢

基本的 Where 組合查詢

最基本的使用 Where 可以進行表格整合的查詢，主要關鍵在於 WHERE {條件}

1
2
3
4
5
6
7
SELECT
	users.id,
  users.name,
  users.salary,
  teams.name AS 部門名稱
FROM users,teams
WHERE users.team_id = teams.id;

Join 合併資料表查詢

Inner Join

取得符合條件的交集資料

Inner Join

如果想使用更簡易的方式去組合查詢，可以使用 Inner Join 的方式，主要關鍵在於 INNER JOIN {表格} ON {條件}

1
2
3
4
5
6
7
SELECT
	users.id,
  users.name,
  users.salary,
  teams.name AS 部門名稱
FROM users
INNER JOIN teams ON users.team_id = teams.id;

Outer Join

除了 Inner Join 還有另外 Outer Join 的方法可以使用，其中包含

Left Join

  • Left Join

取得符合條件的交集資料，以及原本左側表格沒有符合的資料也保留

基本上沒有符合條件的資料欄位都會使用 NULL 補上，左側的表有幾個欄位就會補上幾個欄位

Right Join

  • Right Join

取得符合條件的交集資料，以及原本右側表格沒有符合的資料也保留

和 Left Join 相同的，兩張表會組合，相反的，不會保留原本沒有匹配到右邊表的資料，但是右邊表如果沒有匹配到左側表的則會留下來。

透過這個 Left 或 Right 的方法去篩選完之後，我們也可以再加上 Where 去濾出我們想要的資料

Full Join

  • Full Join

取的符合條件的交集資料，甚至原本兩邊沒有符合的資料也一起保留

基本就是 Left Join + Right Join

Postgres 函式

NULL 欄位解析

對於設定 NOT NULL 的欄位如果想要直接塞入一筆資料，會報出錯誤，如下面示範

1
2
3
4
5
6
7
8
CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  salary INTEFER NULL
);

INSERT INTO users(name) VALUES(‘王小明’);

之後會得到 null value in column “email” of relation “users” violates not-null constraint 這種錯誤

COALESCE 函數

用來檢查並處理 NULL 結果

有些程式語言如果直接從資料庫回傳 null 可能會報錯，這時候可以使用 COALESCE 去把 null 替代成其他的值

1
2
3
4
5
6
7
SELECT
	id,
	name,
	email,
	COALESCE(team_name, ‘待分配’) as team_name,
	COALESCE(salary, 0) as salary
FROM users;

透過 COALESCE 函數修整 Join 過的資料表

透過 Join 的方式，或多或少都會出現 null 的資料，這時候就可以透過 COALESCE 去把 null 改為我們想要顯示的資料

COALESCE 的使用方式為 COALESCE(欄位名稱, 要替換掉 null 的名稱)，基本上包裹起來的欄位如果有值，就會顯示 欄位名稱 的值，沒有就會改成顯示 要替換掉 null 的名稱

1
2
3
4
5
SELECT
  COALESCE(teams.name, ‘未分配’) AS team_name,
	COALESCE(users.name, ‘無員工’) AS user_name
FROM users
FULL JOIN teams ON users.team_id = teams.id;

DISTINCT 不重複函數

SQL 中用於去除重複資料的關鍵字

1
SELECT DISTINCT team_name FROM users;

COUNT 函數

計算出總數量

1
2
3
4
5
6
7
8
9
SELECT COUNT(*) AS 學生總數 FROM students;

-- 帶入 WHERE 條件
SELECT
  COUNT(*) AS 三年一班的學生總數
FROM
  students
WHERE
  class_id = 1;

計算函數

  • AVG(平均)
1
SELECT AVG(salary) AS 員工平均薪資 FROM users;
  • SUM(總和)
1
SELECT SUM(salary) AS 員工總薪資 FROM users;
  • MAX(最高)、MIN(最低)
1
SELECT MAX(salary) AS 員工總薪資 FROM users;

Order By 排序

排序有三種方式

  • ASC 從小到大
  • DESC 從大到小
  • Limit 筆數設定
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 從小到大排序
SELECT
  *
FROM
  users
ORDER BY
  salary ASC;

-- 從大到小排序
SELECT
  *
FROM
  users
ORDER BY
  salary DESC;

-- 只想顯示兩筆資料
SELECT
  *
FROM
  users
ORDER BY
  salary DESC
LIMIT 2;

實務上我們會有多個資料需要進行排序，例如我們想要先排序部門的 id，接著再找出部門內薪資最高到最低，就可以透過下面方法

1
2
3
4
5
6
7
-- 多筆資料排序
SELECT
  *
FROM
  users
ORDER BY
  team_id ASC, salary DESC;

Group By 群組

透過 Group By 可以進行資料分組，再進行計算

1
2
3
4
5
SELECT 
  team_id AS 部門ID,
  COUNT(*) AS 人數
FROM users
GROUP BY team_id;

組合時也可以帶入 Join 或者 SUM 這種語法一起組合使用。

子查詢

實務上可能會碰到我們需要先處理 A 查詢，查詢結束後再進行 B 查詢，例如我們想先查詢平均薪資，之後再查詢大於平均薪資的員工。

SELECT … WHERE xxx > (子查詢)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 條件 A, 找出平均薪資(放入子查詢中先跑)
-- 條件 B, 找出大於平均薪資的人
SELECT
  users.id,
  users.name,
  users.salary,
  teams.name AS 部門
FROM
  users
  INNER JOIN teams ON users.team_id = teams.id
WHERE
  salary > (
    SELECT
      AVG(salary)
    FROM
      users
  );

子查詢 INSERT

當然我們也可以透過子查詢來塞入資料，假設有個情境是我們需要新增一個員工，但因為資料表內的 FK 都是數字，我們只知道員工要放到開發部，就可以用下面的方式去進行塞資料

1
2
3
4
5
6
7
INSERT INTO users (name, email, salary, team_id) VALUES 
(
  '新同事',
  'new@gmail.com',
  50000,
  (SELECT id FROM teams WHERE name = '開發部')
);

小節作業：Table資料表管理

小節作業

因為小節內有作業，作業會回覆到六角學院的 Discord，也能對下一個同學發問，這邊先貼上小節作業的回答

小節作業回答

Q1: 拯救明華國小的資料庫，哪個欄位適合變成外來鍵
A1: 班級及性別，依照以下兩個角度來思考，以班級角度來看更為適合

  1. 以「學生」角度：一個學生會有「多」個班級
  2. 以「班級」角度：一個班級會有「多」個學生

Q2: 第一題的延伸，多了一個班級老師
A2: 班級跟老師都可以，因為兩者都會有多個學生

以上兩題都適合使用以下方式建立表格，甚至性別都適合單獨拔出來當一個表格使用，可參考 在有與沒有之間–為什麼設計資料庫欄位時不建議用布林值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
CREATE TABLE classes(
  id SERIAL PRIMARY KEY,-- 班級編號
  name VARCHAR(50) -- 班級名稱
);

CREATE TABLE genders(
  id SERIAL PRIMARY KEY,-- 性別編號
  name VARCHAR(50) -- 性別名稱
);

CREATE TABLE teachers(
  id SERIAL PRIMARY KEY,-- 老師編號
  name VARCHAR(50) -- 老師名稱
);

CREATE TABLE students(
  id SERIAL PRIMARY KEY,-- 學生編號
  name VARCHAR(50), -- 學生名稱
  age INTEGER, -- 學生年齡
  class_id INTEGER, -- 班級編號, 外來鍵
  gender_id INTEGER, -- 性別編號, 外來鍵
  teacher_id INTEGER, -- 老師編號, 外來鍵
  FOREIGN KEY (class_id) REFERENCES classes(id),
  FOREIGN KEY (gender_id) REFERENCES genders(id),
  FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

隨後再進行資料的塞入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT
  INTO genders(name)
VALUES('男'),('女'),('第三性別');

INSERT
	INTO teachers(name)
VALUES('音樂王子洪老師'),('數學黃厚娜老師'),('體育林子強老師');

INSERT
	INTO classes(name)
VALUES('三年一班'),('三年二班');

INSERT
	INTO students(name,age,class_id,gender_id,teacher_id)
VALUES('王小明’,10,1,1,1),(‘陳小美’,10,1,2,1),(‘張大帥’,12,2,1,3),(‘陳老伯(來重修的)’,60,2,1,2);

這時候如果需要撈取資料可以使用 Where 以及 Inner Join

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 使用 Where
SELECT
	students.id,
  students.name,
  classes.name AS 班級名稱
FROM
  students,
  classes
WHERE
  students.class_id = classes.id;

-- 使用 INNER JOIN
SELECT
  students.id,
  students.name,
  classes.name AS 班級名稱
FROM
  students
  INNER JOIN classes ON students.class_id = classes.id;

Q3: 小孩的家庭歸類資料庫，父母資料一直重複實在討厭（父母一直生）
A3: 其實這邊做法跟 Q2 一樣，就不再重複寫入，但可以另外到 PG SQL 進行練習

上一位同學的問題

Question

上一位同學題目：圖書館頒布新規定：”每人同時只能借一本書”，請通知重複借書的人進快還書

因為上一個學生的 Notion 一直打不開，就直接拿上上一個學生的 hackmd 來進行回答

這邊預期問題應該是還沒有還書的人，因為實務上已經還書的人一定是可以借下一本書的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
  books.id AS id,
  books.name AS 書本名稱,
  borrowers.name AS 借閱者,
  books.checkout_date AS 還書日期
FROM
  books
  INNER JOIN borrowers ON borrower_id = borrowers.id
WHERE books.checkout_date ISNULL
AND borrowers.id IN (
  SELECT borrower_id
  FROM books
  WHERE checkout_date IS NULL
  GROUP BY borrower_id
  HAVING COUNT(*) >= 2
)

想了很久想不到怎麼可以濾出且保留重複的人的資料，之前課程內都是直接濾掉，只好去爬文一下爬到 HAVING 可以使用

出給下位同學的問題

如果路過的朋友也可以嘗試回答看看哦

Question

1
Q: 請依照作業二，將年齡進行資料表拆分，最後請計算出平均年齡，年齡總和。

Conclusion & 結論

這次一口氣多教了很多 SQL 的函數，吸取了好多新的知識，但透過同學的任務突然要融會貫通還是需要一點時間，我想同學的問題應該有更好的解答，這邊會再嘗試進行修改。

這次的影音課程內容都已經看完了，接下來還會有一個小作業，需要到 Github 進行 Fork，後續如果有整理出什麼心得在紀錄上來。

參考網站