---
# System prepended metadata

title: 第二堂：資料模型與資料表操作
tags: [2024 後端工程師 - 資料庫體驗營]

---

# 第二堂：資料模型與資料表操作
20241122

* 記得錄影
* [簡報](https://gamma.app/docs/SQL--23heyix4r93u5u0?mode=doc)

---
[TOC]

## Tables 資料表管理 - 準備要學多張表整合

### 模擬資料 - 以公司資料為例

- Split 1table into 2table(users,teams) （圖解） [[Miro 示意圖]](https://miro.com/app/board/uXjVLMcM_hQ=/)

| 員工編號 (id) | 姓名 (name) | 薪資 (salary) | 部門名稱 (team) |
|--------------|------------|--------------|----------------|
| 1 | 張小明 | 45000 | 開發部 |
| 2 | 王大明 | 48000 | 開發部 |
| 3 | 李小華 | 52000 | 人事部 |
| 4 | 陳小玉 | 55000 | 人事部 |
| 5 | 林小豪 | 47000 | 開發部 |

![image](https://hackmd.io/_uploads/rkojJrCGJe.png)

- How to split a table（method）
    - 口訣：
        - <mark>多</mark>的要設成外來鍵
        - 從<mark>欄位</mark>角度去規劃


### 情境題（練習題 ex）
- 公司資料庫 [miro](https://miro.com/app/board/uXjVLMcM_hQ=/)
![image](https://hackmd.io/_uploads/H1q_DH0Gyg.png)

- 國小資料庫 [miro](https://miro.com/app/board/uXjVLMcM_hQ=/)
![image](https://hackmd.io/_uploads/ByH6wrCMJg.png)

- 小孩的家庭歸類資料庫 [miro](https://miro.com/app/board/uXjVLMcM_hQ=/)
![image](https://hackmd.io/_uploads/Hk-APrCzyg.png)

- 情境一：訂單資料表
    以欄位角度出發進行分析：
    1. 以 ?? 角度來說
    2. 以 ?? 角度來說
![image](https://hackmd.io/_uploads/B1FlOrCGye.png)


| 訂單編號 | 客戶姓名 | 客戶電話 | 客戶地址 | 訂單日期 | 商品 | 金額 |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | 張小明 | 0912345678 | 台北市中山區 | 2024-01-01 | 筆電 | 30000 |
| 2 | 張小明 | 0912345678 | 台北市中山區 | 2024-02-01 | 手機 | 25000 |
| 3 | 王大明 | 0923456789 | 台北市信義區 | 2024-02-05 | 耳機 | 2000 |
| 4 | 張小明 | 0912345678 | 台北市中山區 | 2024-03-01 | 平板 | 15000 |
| 5 | 王大明 | 0923456789 | 台北市信義區 | 2024-03-15 | 滑鼠 | 1000 |
|  |  |  |  |  |  |  |

- 情境二：掛號資料表
    以欄位角度出發進行分析：
    1. 以 ?? 角度來說
    2. 以 ?? 角度來說

| 掛號序號 | 姓名 | 身分證字號 | 生日 | 電話 | 掛號日期 | 科別 |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | 張小明 | A123 | 1990-01-01 | 0912345678 | 2024-01-01 | 內科 |
| 2 | 王大明 | B123 | 1985-05-05 | 0923456789 | 2024-01-01 | 外科 |
| 3 | 張小明 | A123 | 1990-01-01 | 0912345678 | 2024-01-15 | 內科 |
| 4 | 張小明 | A123 | 1990-01-01 | 0912345678 | 2024-02-01 | 小兒科 |
| 5 | 張小明 | A123 | 1990-01-01 | 0912345678 | 2024-02-15 | 內科 |


### SQL語法

- 建立主鍵、自動產生 ID 方式

```sql
-- 新增資料表
CREATE TABLE users(
 id SERIAL PRIMARY KEY, // 1.integer 整數 2.SERIAL 自動遞增 3.UUID+新增時間
 name VARCHAR(50)
);
-- 新增一筆資料
INSERT INTO users(name)
VALUES('小杰');
```

- 建立完整資料庫流程
```sql
-- 建立部門資料表
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),
    ('李小華', 52000, 2),
    ('陳小玉', 55000, 2),
    ('林小豪', 47000, 1);
    
```
- 嘗試故意寫錯資料
```sql
INSERT INTO users (name, salary, team_id) 
VALUES 
    ('張小明3', 45000, 5);

```
- 整合兩張表的方式 (where)

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

- 可嘗試修改部門名稱看結果
```sql
UPDATE teams
SET name = '人事部2'
WHERE id =2
```









## postgres 函式

### null 
```sql
-- 建立員工資料表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,        
    name VARCHAR(50) NOT NULL,   //NOT NULL 不可是空值
    email VARCHAR(100) NOT NULL,  
    salary INTEGER NULL
);
```

### count
初始資料：
```sql=
-- 建立員工資料表
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
   -- 完整資料的員工
   ('張小明', 'zhang@example.com', '開發部', 45000),
   ('王大明', 'wang@example.com', '行銷部', 48000),
   
   -- 還沒分配部門，但已有薪水
   ('李小華', 'lee@example.com', NULL, 42000),
   ('陳小玲', 'chen@example.com', NULL, 44000),
   
   -- 已分配部門，但還沒定薪水
   ('林小美', 'lin@example.com', '人事部', NULL),
   ('周小威', 'zhou@example.com', '開發部', NULL),
   
   -- 新進員工，部門和薪水都還沒設定
   ('劉小安', 'liu@example.com', NULL, NULL),
   ('黃小凱', 'huang@example.com', NULL, NULL);


```

count 語法
```sql
-- 計算總共有幾個員工
SELECT COUNT(*) as 員工總數 FROM users;

-- 計算開發部有幾個員工
SELECT COUNT(*) as 開發部人數 
FROM users 
WHERE team_name = '開發部';

-- 計算薪水大於45000的員工人數
SELECT COUNT(*) as 高薪員工數 
FROM users 
WHERE salary > 45000;
```
### AVG、SUM、MAX、MIN 函數
基本範例：公司整體統計
```sql
SELECT 
    AVG(salary) AS 平均薪資,
    SUM(salary) AS 總薪資
FROM users;
```

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

完整統計資料
```sql
SELECT 
    COUNT(*) AS 員工數,
    AVG(salary) AS 平均薪資,
    SUM(salary) AS 總薪資,
    MAX(salary) AS 最高薪資,
    MIN(salary) AS 最低薪資
FROM users;
```

## order by 排序資料

員工資料庫範例

```sql
-- 建立部門資料表
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);
    
```

薪水排序
```sql
SELECT name AS 姓名, salary AS 薪資
FROM users
ORDER BY salary ASC;  
```
* 說明
    * ASC 從小到大
    * DESC 從大到小
    * limit 筆數設定




## 任務二：健身教練線上直播課平台
- 兩種方式上交作業：
    - pg-sql
    - docker+Dbeaver
- 11/27.29 解說

:::spoiler
* [線稿圖](https://miro.com/app/board/uXjVLbiDml0=/?share_link_id=404709829822)
* [建立資料表語法](https://hackmd.io/@hexschool/SkpaT_jfye)
### 訂價策略
#### 小班制健身直播課

- **小班制健身直播課制**：教練會在平台開課，學生前往教練開設的課程頁面報名，課程時間到了後，就可點擊課程直播室進行上課
- 堂數售價：每位健身教練的收費都一致，一堂 50 分鐘**小班制健身直播課**都是 200 元

#### 堂數組合包方案

- 7 堂組合包方案：
    - 價格：1,400 元
- 14 堂方案：
    - 價格：2,520 元
- 21 堂方案：
    - 價格：4,800 元

### 使用者故事

客戶使用者故事：

1. 作為一個客戶，我想要能夠註冊帳號，以便開始使用健身教練網站的服務。
2. 作為一個客戶，我想要查看教練的個人資料和專長，以便選擇適合我的教練。
3. 作為一個客戶，我想要瀏覽教練開的線上直播課，以便選擇適合我的課程。
4. 作為一個客戶，我想要購買課程堂數方案，以獲得可用於預約課程的堂數。
5. 作為一個客戶，我想要查看我當前的可用堂數，以便規劃我的課程安排。
6. 作為一個客戶，我想要瀏覽可用的課程和時段，以便預約課程。
7. 作為一個客戶，我想要預約課程，並使用我的可用堂數。
8. 作為一個客戶，我想要查看我的預約歷史，以便追蹤我的健身進度。
9. 作為一個客戶，我想要閱讀部落格文章，以獲取健身知識和技巧。

教練使用者故事：

1. 作為一個教練，我想要建立我的教練資料，包括我的專長和經驗，以吸引潛在客戶。
2. 作為一個教練，我想要建立新的**多人線上教練課**，以提供給客戶選擇。
3. 作為一個教練，我想要設置課程的時間表，以便客戶可以預約。

後台管理者使用者故事：

1. 作為一個管理者，我想要查看系統的整體使用情況，包括課程預約率和堂數使用情況。
2. 作為一個管理者，我想要檢視以下報表
    1. 當月的 7、14、30 方案購買數量的圓餅圖報表
    2. 當月的活躍付費會員人數
    3. 當月的總營業額數字
    4. 當月的學生預約的課程總堂數



### 資料表圖片
![MTc1Mzc2NDQzNDQ4MzQ1Mjg0NDc=_2024-11-20T15_20_32Z](https://hackmd.io/_uploads/ryNS9T6G1l.png)


### 進階工程師作業模版
* [連結](https://github.com/hexschool/backend-database-camping-task-startkit)
:::
