Skip to content

YPINPIN/postgresql-note

BranchesTags

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

17 Commits
homework
homework
 
 
images
images
 
 
task
task
 
 
README.md
README.md
 
 

Repository files navigation

PostgreSQL 資料庫學習紀錄

主要根據六角學院 「2024 後端工程師 - 資料庫體驗營」整理的學習筆記，方便查閱。

直播課講義

目錄

資料庫的形式

  • 資料表 ( Table )

    整體資料，包含了所有相關的資料。

  • 欄位 ( Columns )

    定義資料的屬性和類型 ( Data Type )。

  • 資料列 ( Rows )

    橫向的資料集合，代表一筆完整資料。

如何操作資料庫？

可以透過 SQL 指令操作資料庫，全名是 "Structured Query Language"，中文是「結構化查詢語言」。

練習平台：Temporary Postgres Database

資料庫設計順序

圖片01

Data Type 資料類型

資料型態 描述 使用情境 範例 範例欄位
INTEGER 整數 ID、年齡、數量 1, 42, -15 age INTEGER
VARCHAR(n) 可變長度字串 名稱、描述 "John Doe" name VARCHAR(50)
TIMESTAMP 日期時間 建立/更新時間 '2024-03-20 15:30:00' created_at TIMESTAMP
DECIMAL(p,s) 精確小數 金額計算 199.99 price DECIMAL(10,2)
BOOLEAN 真/假值 狀態、開關 true, false is_active BOOLEAN
JSON JSON 資料 彈性資料結構 '{"name": "John"}' settings JSON

新增一張資料表

使用 CREATE TABLE 來指定 資料表名稱及定義 資料的屬性和類型。

記得結尾加上 ; 來結束整個 SQL 指令。

CREATE TABLE users (
  name VARCHAR(50),
  email VARCHAR(100),
  age INTEGER
);

新增資料 (insert)

使用 INSERT INTO 來指定要新增資料的 資料表名稱及 資料欄位，VALUES 後面 使用 () 來添加對應欄位的一筆資料。

INSERT INTO users (name, email, age)
VALUES ('張小明', '[email protected]', 25);

若要一次新增多筆資料則使用 , 分隔多筆 () 資料即可。

INSERT INTO users (name, email, age)
VALUES
  ('張小美', '[email protected]', 21),
  ('張小立', '[email protected]', 34),
  ('張小花', '[email protected]', 28);

SELECT 查詢

使用 SELECT 可以查詢指定資料表 ( FROM 後指定資料表名稱 ) 的特定欄位的所有資料。

-- 查詢所有欄位
SELECT * FROM users;

圖片02

-- 查詢特定欄位 (只要 name 和 age)
SELECT name, age FROM users;

圖片03

AS 新增別名

可以使用 AS 設定查詢資料的欄位別名，並且也可設定查詢計算的資料欄位名稱。

SELECT
  name AS 姓名,
  age AS 現在年齡,
  65 - age AS 距離退休年數
FROM users;

圖片04

WHERE 篩選資料

使用 WHERE 可以篩選指定的資料。

練習用資料庫環境

-- 商品資料表
CREATE TABLE products (
    name VARCHAR(100),           -- 商品名稱
    price INTEGER,              -- 原價
    discount_price INTEGER,      -- 折扣價
    stock INTEGER,              -- 庫存數量
    category VARCHAR(50),        -- 商品類別
    status VARCHAR(20)          -- 商品狀態（上架、下架）
);

INSERT INTO products (name, price, discount_price, stock, category, status) VALUES
   ('iPhone 16', 25000, 23000, 50, '3C', 'active'),
   ('AirPods', 5000, 5000, 5, '3C', 'inactive'),
   ('iPad', 18000, 16000, 25, '3C', 'active'),
   ('充電線', 500, 500, 100, '配件', 'active'),
   ('手機殼', 300, 250, 30, '配件', 'active'),
   ('螢幕保護貼', 200, 180, 150, '配件', 'active'),
   ('手機支架', 400, 400, 0, '配件', 'inactive');

圖片05

查找 3C 類別的產品：

SELECT name, price
FROM products
WHERE category = '3C';

圖片06

圖片07

比較運算子

運算子 意義 範例
= 等於 WHERE price = 100
> 大於 WHERE price > 100
< 小於 WHERE price < 100
>= 大於等於 WHERE price >= 100
<=> 小於等於 WHERE price <= 100

查找庫存小於 50 的產品：

SELECT name, stock
FROM products
WHERE stock < 50;

圖片08

邏輯運算子

運算子 意義 範例
AND 且，兩個條件都要成立 WHERE category = '3C' AND price > 1000
OR 或，其中一個條件成立 WHERE category = '3C' OR category = '配件'

已上架的 3C 商品：

SELECT name, price, stock
FROM products
WHERE status = 'active'
AND category = '3C';

圖片09

下架或無庫存商品：

SELECT name, status, stock
FROM products
WHERE status = 'inactive'
  OR stock = 0;

圖片10

集合與範圍運算子

運算子 意義 範例
BETWEEN 在指定的範圍內 WHERE price BETWEEN 100 AND 500
IN 包含在列出的值中 WHERE category IN ('3C', '配件')
NOT IN 不包含在列出的值中 WHERE category NOT IN ('3C')

想幫家人買生日禮物，預算在 500~1000 元：

SELECT * FROM products
WHERE discount_price BETWEEN 500 AND 1000;

圖片11

找出特定類別商品：

SELECT * FROM products
WHERE category IN ('3C');

圖片12

排除特定商品 (充電線、手機殼、螢幕保護貼)：

SELECT * FROM products
WHERE name NOT IN ('充電線', '手機殼', '螢幕保護貼');

圖片13

更新資料

圖片14

調整特定商品的價格：

UPDATE products
SET price = 28000
WHERE name = 'iPhone 16';

更新庫存數量：

UPDATE products
SET stock = stock + 50
WHERE name = '手機殼';

同時修改多個欄位：

UPDATE products
SET stock = stock + 50, name = '充電線-修正'
WHERE name = '充電線';

刪除資料

圖片15

單筆刪除 - 刪除特定商品：

DELETE FROM products
WHERE name = 'iPhone 16';

條件刪除 - 刪除類別為 3C 的商品：

DELETE FROM products
WHERE category = '3C';

多重條件刪除 - 刪除沒庫存且已下架的商品 ：

DELETE FROM products
WHERE stock = 0 AND status = 'inactive';

小節作業：家具店的菜雞銷售奇遇記

小節作業

Tables 資料表管理

miro 簡報

從單張資料表升級到多張資料表的管理流程

圖片21

當我們只有單張資料表時，想要修改部門名稱的開發部為資訊部時，就會需要花較多的時間去針對所有開發部的員工資料去進行寫入修改，對資料庫的效能來說較差。

實務上則是會拆分成多張資料表來進行管理，如下圖：

圖片22

多了一個部門編號 (team_id) 的欄位，並且其會對應到新的部門名稱資料表 (teams) 中的部門編號 (id) 的欄位。

這時候如果想要修改開發部為資訊部時，就只需要對部門名稱資料表 (teams) 進行寫入修改即可。

主鍵和外來鍵的差異

當拆分成多張資料表進行管理時，會將資料表的指定欄位設定為主鍵和外來鍵來進行多張資料表之間的關聯。

圖片23

  • 主鍵 ( Primary Key，簡稱 PK )

    • 1.每個資料表都要有一個主鍵 (只能有一個)。

    • 2.主鍵的值必須是唯一的，不能重複。

    • 3.主鍵不能是 Null 值，但外來鍵可以是 Null 值。

    • 4.大部分情況會命名為 'id'，使用整數或 UUID 。

    • 5.設定後就不應該再更動。

  • 外來鍵 ( Foreign Key，簡稱 FK )

    • 1.當資料需要關聯到其他表格時才會用到。

    • 2.同一個資料表中，可以有多個外來鍵。

    • 3.命名通常會用 '參考資料表_id' 的格式。

    • 4.外來鍵必須對應到被參考資料表的主鍵。

剛剛的範例中就是將員工資料表 (users) 的員工編號 (id) 設為主鍵 (PK)，而外來鍵 (FK) 部門編號 (team_id) 則是會對應到部門名稱資料表 (teams) 中的主鍵 (PK) 部門編號 (id)。

如何規劃外來鍵與資料表拆分

圖片24

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

可以在新增資料表時，使用 PRIMARY KEY 設定主鍵，使用 SERIAL 則是可以設定欄位為 ID 自動遞增。

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

現在新增資料只需要設定員工姓名 ( name ) 欄位，員工編號 ( id ) 則會自己遞增。

INSERT INTO users (name)
VALUES
  ('張小美'),
  ('張小立'),
  ('張小花');

圖片25

建立完整資料庫流程

根據以下結構建立完整的資料表。

圖片26

  • 建立 teams 資料表

    CREATE TABLE teams (
  id SERIAL PRIMARY KEY, -- 部門編號，主鍵
  name VARCHAR(50)  -- 部門名稱
);

  • 建立 users 資料表

    使用 FOREIGN KEY (未來鍵欄位) REFERENCES 資料表 (主鍵欄位) 語法來設定外來鍵並關聯資料表主鍵。

    CREATE TABLE users (
  id SERIAL PRIMARY KEY, -- 員工編號，主鍵
  name VARCHAR(50), -- 姓名
  salary INTEGER, -- 薪資
  team_id INTEGER,  -- 部門編號，外來鍵
  FOREIGN KEY (team_id) REFERENCES teams (id) -- 設定外來鍵關聯
);

  • 新增部門 ( teams ) 資料

    INSERT INTO teams (name)
VALUES
  ('開發部'),
  ('人事部');

    圖片27

  • 新增員工 ( users ) 資料

    INSERT INTO users (name, salary, team_id)
VALUES
  ('張小明', 45000, 1),
  ('王大明', 48000, 1),
  ('李小華', 52000, 2),
  ('陳小玉', 55000, 2),
  ('林小豪', 47000, 1);

    圖片28

合併資料表查詢

  • 搭配 WHERE 條件。

    同時查詢兩張表的指定欄位，並搭配 WHERE 根據部門 id 查詢符合的資料。

    users.name 和 teams.name 欄位名稱相同，可能導致錯誤，因此可以使用 AS 指定欄位別名。

    SELECT
  users.id,
  users.name,
  users.salary,
  teams.name AS 部門名稱
FROM users,teams
WHERE users.team_id = teams.id;

    圖片29

  • 使用 INNER JOIN。

    實際結果與 WHERE 相同，主流建議使用這種方式。

    INNER JOIN 要合併的資料表 ON 條件。

    FROM 後面只需要主要查詢的資料表。

    SELECT
  users.id,
  users.name,
  users.salary,
  teams.name AS 部門名稱
FROM users
INNER JOIN teams ON users.team_id = teams.id;

    圖片30

主鍵、外來鍵 constraint 約束提醒

  • 當新增資料時主鍵重複，會出現錯誤提示。

    INSERT INTO users (id, name, salary, team_id)
VALUES (4, '李小花', 48000, 2);

    圖片31

  • 當新增資料時外來鍵不存在對應資料，會出現錯誤提示。

    INSERT INTO users (name, salary, team_id)
VALUES ('李小花', 48000, 3);

    圖片32

小節作業：主鍵、外來鍵、INNER JOIN

小節作業

postgres 函式

NULL 介紹

NULL 在資料庫中表示「未知」或「沒有值」。和零 (0) 或空字串 ('') 是不同的概念。

  • 使用時機

    用來表示資料庫中某個欄位尚未填入資料。

    在實際應用中，並不是所有資料在一開始就具備，像是表單的某些欄位可能會被使用者選擇性填寫。例如：

    • 社群網站的個人大頭照

    • 新員工尚未被指派部門，需先顯示為 null

  • 使用方法

    可以設置 NOT NULL 表示欄位必填，NULL 則表示欄位可為空。

    -- 建立員工資料表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    salary INTEGER NULL
);

-- 新增員工資料
INSERT INTO users (name, email)
VALUES ('王小明', '[email protected]');

    圖片33

    若是未正確設置必填欄位，則會出現錯誤提示。

    -- 新增員工資料 (未正確設置必填欄位)
INSERT INTO users (name)
VALUES ('王小美');

    圖片34

COALESCE 函數

用來檢查並處理 NULL 結果。

會依序檢查傳入的參數，回傳參數列表中第一個不是 NULL 的值。

  • users 資料表

    -- 建立員工資料表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  team_name VARCHAR(50) NULL,  -- 可能還沒分配部門
  salary INTEGER NULL         -- 可能還沒設定薪水
);

  • 模擬員工資料

    -- 插入員工資料，展示不同情況
INSERT INTO users (name, email, team_name, salary) VALUES
  -- 完整資料的員工
  ('張小明', '[email protected]', '開發部', 45000),
  ('王大明', '[email protected]', '行銷部', 48000),

  -- 還沒分配部門，但已有薪水
  ('李小華', '[email protected]', NULL, 42000),
  ('陳小玲', '[email protected]', NULL, 44000),

  -- 已分配部門，但還沒定薪水
  ('林小美', '[email protected]', '人事部', NULL),
  ('周小威', '[email protected]', '開發部', NULL),

  -- 新進員工，部門和薪水都還沒設定
  ('劉小安', '[email protected]', NULL, NULL),
  ('黃小凱', '[email protected]', NULL, NULL);

  • 原始資料表

    圖片35

  • 使用 COALESCE 函數整理查詢結果

    SELECT
  id,
  name,
  email,
  COALESCE(team_name, '待分配') as team_name,
  COALESCE(salary, 0) as salary
FROM users;

    圖片36

DISTINCT 不重複函數

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

  • users 資料表

    -- 建立員工資料表
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  team_name VARCHAR(50) NULL,
  salary INTEGER NULL
);

  • 模擬員工資料

    INSERT INTO users (name, email, team_name, salary)
VALUES
    ('陳志明', '[email protected]', '開發部', 68000),
    ('林雅婷', '[email protected]', '開發部', 55000),
    ('王建宏', '[email protected]', '開發部', 46000),
    ('張美玲', '[email protected]', '人事部', 42000),
    ('黃佳琳', '[email protected]', '人事部', 48000);

  • 原始資料表

    圖片37

  • 使用 DISTINCT 查詢有哪些部門

    SELECT DISTINCT team_name FROM users;

    圖片38

COUNT 函數

COUNT 是一個計數函數，用來計算資料表中的資料筆數 。是最常用的集合函數（Aggregate Function）之一。

  • 資料表

    圖片39

  • 計算總共有幾個員工

    SELECT COUNT(*) AS 員工總數
FROM users;

    圖片40

  • 計算開發部有幾個員工

    SELECT COUNT(*) AS 開發部人數
FROM users
WHERE team_name = '開發部';

    圖片41

  • 計算薪水大於 45000 的員工人數

    SELECT COUNT(*) AS 高薪員工數
FROM users
WHERE salary > 45000;

    圖片42

AVG、SUM、MAX、MIN 函數

AVG（Average）：計算一組數值的「平均值」。

SUM（Summary）：計算一組數值的「總和」。

MAX（Maximum）：找出一組數值中的「最大值」。

MIN（Minimum）：找出一組數值中的「最小值」。

  • 資料表

    圖片39

  • 公司整體統計

    SELECT
  AVG(salary) AS 平均薪資,
  SUM(salary) AS 總薪資
FROM users;

    圖片43

  • 開發部統計

    SELECT
  AVG(salary) AS 開發部平均薪資,
  SUM(salary) AS 開發部總薪資
FROM users
WHERE team_name = '開發部';

    圖片44

  • 完整統計資料

    SELECT
  COUNT(*) AS 員工數,
  AVG(salary) AS 平均薪資,
  SUM(salary) AS 總薪資,
  MAX(salary) AS 最高薪資,
  MIN(salary) AS 最低薪資
FROM users;

    圖片45

UUID 介紹

資料庫開啟 UUID 功能 (postgresql 13 版本以上不用)：

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

SERIAL 跟 UUID 差異：

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

  • users 資料表 (使用 UUID)

    -- 建立員工資料表
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
);

  • 模擬員工資料

    INSERT INTO users (name, email, team_name, salary)
VALUES
    ('陳志明', '[email protected]', '開發部', 68000),
    ('林雅婷', '[email protected]', '開發部', 55000),
    ('王建宏', '[email protected]', '開發部', 46000),
    ('張美玲', '[email protected]', '人事部', 42000),
    ('黃佳琳', '[email protected]', '人事部', 48000);

  • 資料表結果

    圖片46

JOIN 資料關聯

miro 簡報

可將兩個資料表 (Table) 組合在一起。

JOIN 語法種類：

  • inner join (最常使用)

  • outer join

    • left join

    • right join

    • full join

模擬資料：

-- 建立部門資料表
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,  -- 允許 NULL，代表未分配部門的員工
    FOREIGN KEY (team_id) REFERENCES teams(id)
);

-- 新增部門資料
INSERT INTO teams (name)
VALUES
    ('開發部'),
    ('人事部'),
    ('行銷部'), -- 新成立還沒有人的部門
    ('研發部'); -- 新成立還沒有人的部門

-- 新增員工資料
INSERT INTO users (name, salary, team_id)
VALUES
    ('張小明', 45000, 1),
    ('王大明', 48000, 1),
    ('李小華', 52000, 2),
    ('陳小玉', 55000, 2),
    ('林小豪', 47000, 1),
    ('陳小明', 42000, NULL),  -- 新進員工，還沒分配部門
    ('王小美', 41000, NULL);  -- 新進員工，還沒分配部門

圖片47

圖片48

INNER JOIN

INNER JOIN 為只顯示條件符合的合併資料。

圖片49

情境：查詢有部門的員工

SELECT
  users.id,
  users.name AS users_name,
  users.salary,
  teams.name AS teams_name
FROM users
INNER JOIN teams ON users.team_id = teams.id;

圖片50

LEFT JOIN

LEFT JOIN 為包含 INNER JOIN 的合併特性，但是同時也保留顯示未符合條件的的左側資料，未符合的資料則會顯示 NULL。

圖片51

情境：未分配部門的員工資料處理

SELECT
  users.name AS users_name,
  teams.name AS teams_name
FROM users
LEFT JOIN teams ON users.team_id = teams.id;

圖片52

RIGHT JOIN

RIGHT JOIN 為包含 INNER JOIN 的合併特性，但是與 LEFT JOIN 相反會保留顯示未符合條件的的右側資料，未符合的資料則會顯示 NULL。

圖片53

情境：查詢空部門的報表

SELECT
  teams.name AS teams_name,
  users.name AS users_name
FROM users
RIGHT JOIN teams ON users.team_id = teams.id;

圖片54

同時也可以搭配 WHERE 篩選查詢：

SELECT
  teams.name AS teams_name,
  users.name AS users_name
FROM users
RIGHT JOIN teams ON users.team_id = teams.id
WHERE users.name IS NULL;

圖片55

FULL JOIN

FULL JOIN 其實就是 LEFT JOIN 和 RIGHT JOIN 的組合。

圖片56

情境：部門配置與新進人員總覽

SELECT
	teams.name AS team_name,
  users.name AS user_name
FROM users
FULL JOIN teams ON users.team_id = teams.id;

圖片57

JOIN 搭配 COALESCE 設計

可以搭配 COALESCE 來處理 NULL 資料顯示。

SELECT
	COALESCE(teams.name, '未分配') AS team_name,
  COALESCE(users.name, '無員工') AS user_name
FROM users
FULL JOIN teams ON users.team_id = teams.id;

圖片58

ORDER BY 排序資料

可以使用 ORDER BY 來設置排序條件，默認值為 ASC。

公司員工資料庫範例：

-- 建立部門資料表
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 teams (name)
VALUES
    ('開發部'),
    ('人事部');

-- 新增員工資料
INSERT INTO users (name, salary, team_id)
VALUES
    ('張小明', 45000, 1),
    ('王大明', 48000, 1),
    ('李小華', 43000, 2),
    ('陳小玉', 55000, 2),
    ('林小豪', 47000, 1);

圖片59

ASC 從小到大

-- 薪資從小到大
SELECT
  name AS 姓名,
  salary AS 薪資
FROM users
ORDER BY salary ASC;

圖片60

DESC 從大到小

-- 薪資從大到小
SELECT
  name AS 姓名,
  salary AS 薪資
FROM users
ORDER BY salary DESC;

圖片61

LIMIT 筆數設定

-- 薪資從大到小，只取前兩筆
SELECT
  name AS 姓名,
  salary AS 薪資
FROM users
ORDER BY salary DESC
LIMIT 2;

圖片62

多條件排序

也可以使用 , 分隔多個排序條件。

-- 根據部門排序顯示且薪資從大到小
SELECT
  name AS 姓名,
  salary AS 薪資,
  team_id AS 部門ID
FROM users
ORDER BY team_id ASC, salary DESC;

圖片63

JOIN 組合+排序技巧

-- JOIN 搭配排序顯示
SELECT
  users.name AS 姓名,
  users.salary AS 薪資,
  teams.name AS 部門名稱
FROM users
INNER JOIN teams ON users.team_id = teams.id
ORDER BY teams.name, salary ASC;

圖片64

-- JOIN 搭配 WHERE 篩選、排序顯示
SELECT
  users.name AS 姓名,
  users.salary AS 薪資,
  teams.name AS 部門名稱
FROM users
INNER JOIN teams ON users.team_id = teams.id
WHERE teams.name = '人事部'
ORDER BY salary ASC;

圖片65

GROUP BY 分組資料

可以使用 GROUP BY 來進行資料分組。

並且也可以搭配聚合函數 ( Aggregate Functions ) 來進行分組計算( COUNT、SUM、AVG、MAX、MIN )，例如：計算各部門的平均薪資等等。

公司員工資料庫範例：

-- 建立部門資料表
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 teams (name)
VALUES
    ('開發部'),
    ('人事部');

-- 新增員工資料
INSERT INTO users (name, salary, team_id)
VALUES
    ('張小明', 45000, 1),
    ('王大明', 48000, 1),
    ('李小華', 43000, 2),
    ('陳小玉', 55000, 2),
    ('林小豪', 47000, 1);

圖片59

基本語法

分組資料：

SELECT
	team_id AS 部門ID
FROM users
GROUP BY team_id;

圖片66

搭配聚合函數計算：

-- 計算每個部門的人數
SELECT
	team_id AS 部門ID,
  COUNT(*) AS 部門人數
FROM users
GROUP BY team_id;

圖片67

JOIN 整合教學

也可以先使用 JOIN 合併資料表再進行分組及計算。

-- 加上部門名稱計算每個部門的人數
SELECT
	teams.name AS 部門名稱,
  COUNT(*) AS 部門人數
FROM users
INNER JOIN teams ON users.team_id = teams.id
GROUP BY teams.name;

圖片68

-- 計算各部門的完整統計資料
SELECT
	teams.name AS 部門名稱,
  COUNT(*) AS 部門人數,
  SUM(users.salary) AS 總薪資,
  AVG(users.salary) AS 平均薪資,
  MAX(users.salary) AS 最高薪資,
  MIN(users.salary) AS 最低薪資
FROM users
INNER JOIN teams ON users.team_id = teams.id
GROUP BY teams.name;

圖片69

子查詢 Subquery

  • 可在一個 SQL 指令使用多個 SELECT。

  • 在開頭使用的 SELECT 是主要的部分，所以稱為「主查詢」，其餘 SELECT 稱為「子查詢」。

  • 會先運行完子查詢在運行主查詢，通常使用子查詢來獲取計算或篩選條件資料。

  • 子查詢會放在 () 中。

公司員工資料庫範例：

-- 建立部門資料表
CREATE TABLE teams (
    id SERIAL PRIMARY KEY,  -- 部門編號，主鍵
    name VARCHAR(50)        -- 部門名稱
);

-- 建立員工資料表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,     -- 員工編號，主鍵
    name VARCHAR(50),          -- 姓名
    email VARCHAR(100),        -- 信箱
    salary INTEGER,            -- 薪資
    team_id INTEGER,           -- 部門編號，外來鍵
    FOREIGN KEY (team_id) REFERENCES teams(id)  -- 設定外來鍵關聯
);

-- 新增部門資料
INSERT INTO teams (name)
VALUES
    ('開發部'),
    ('人事部');

-- 新增員工資料
INSERT INTO users (name,email, salary, team_id)
VALUES
    ('張小明','[email protected]', 45000, 1),
    ('王大明','[email protected]',  48000, 1),
    ('李小華','[email protected]', 43000, 2),
    ('陳小玉','[email protected]', 55000, 2),
    ('林小豪','[email protected]', 47000, 1);

圖片70

搭配 WHERE 篩選

查出哪些員工大於平均薪資

SELECT name, salary
FROM users
WHERE salary > 47600; -- 平均薪資的值可以透過子查詢取得

調整為子查詢作法：

SELECT name, salary
FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

圖片71

子查詢延伸運用

顯示每位員工薪資與平均薪資的差距

透過子查詢先取得平均薪資在計算。

SELECT
    name AS 姓名,
    salary AS 薪資,
    salary - (SELECT AVG(salary) FROM users) AS 差距值
FROM users;

圖片72

用部門名稱來找 team_id 插入資料

透過子查詢先取得 '開發部' 的 id，在將其作為資料插入。

INSERT INTO users (name, email, salary, team_id)
VALUES
(
  '新同事',
  '[email protected]',
  50000,
  (
    -- 子查詢取得 team_id
    SELECT id FROM teams
    WHERE name = '開發部'
  )
);

圖片73

圖片74

透過 Eamil 找到某位員工的薪資，來設定新員工薪資

透過子查詢先獲得指定 Eamil 員工的薪資，在將其作為資料插入。

INSERT INTO users (name, email, salary, team_id)
VALUES
(
  '新同事-2',
  '[email protected]',
  (
    -- 子查詢取得指定 Eamil 員工的薪資
    SELECT salary FROM users
    WHERE email = '[email protected]'
  ),
  (
    -- 子查詢取得 team_id
    SELECT id FROM teams
    WHERE name = '開發部'
  )
);

圖片75

圖片76

資料庫與 Docker 環境建立

以下皆使用 Windows 環境。

安裝 DBeaver

DBeaver 是一個 SQL 客戶端和資料庫管理工具。

安裝 Windows 版本：

圖片16

安裝完成：

圖片17

安裝 Docker Desktop

Docker 是一種軟體平台，可讓你快速地建置、測試和部署應用程式。

Docker 將軟體封裝到名為容器的標準化單位，其中包含程式庫、系統工具、程式碼和執行時間等執行軟體所需的所有項目。

圖片18

安裝 Windows 版本：

Windows 排錯教學

圖片19

安裝完成：

圖片20

任務二：健身教練線上直播課平台

任務二：健身教練線上直播課平台

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

0 stars

Watchers

1 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

No packages published