# 第二堂：資料模型與資料表操作
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 | 開發部 |
- How to split a table（method）
- 口訣：
- <mark>多</mark>的要設成外來鍵
- 從<mark>欄位</mark>角度去規劃
### 情境題（練習題 ex）
- 公司資料庫 [miro](https://miro.com/app/board/uXjVLMcM_hQ=/)
- 國小資料庫 [miro](https://miro.com/app/board/uXjVLMcM_hQ=/)
- 小孩的家庭歸類資料庫 [miro](https://miro.com/app/board/uXjVLMcM_hQ=/)
- 情境一：訂單資料表
以欄位角度出發進行分析：
1. 以 ?? 角度來說
2. 以 ?? 角度來說
| 訂單編號 | 客戶姓名 | 客戶電話 | 客戶地址 | 訂單日期 | 商品 | 金額 |
| --- | --- | --- | --- | --- | --- | --- |
| 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. 當月的學生預約的課程總堂數
### 資料表圖片
### 進階工程師作業模版
* [連結](https://github.com/hexschool/backend-database-camping-task-startkit)
:::
Sign in with Wallet
Connect another wallet