## 課堂筆記
一個表格只能有一個主鍵(PK)，但可以有多個外來鍵(FK)。
口訣：多的要設計成外來鍵，例如：一個部門會有多個員工。

主鍵(PK)：
➊ 必須是唯一值，不能重複。
➋ 不能是Null值，但外來鍵可以。
➌ 設定後不應再更動。

外來鍵(FK)：
➊ 當資料需要關聯到其他表格時才會用到。
➋ 必須對應到被參考資料表的主鍵。
➌ 命名通常會用'參考資料表_id'的格式。

[Postgres SQL指令](https://hackmd.io/@HCZvma86RdqPySqRGogXRA/ryZHj-v-yl)
<br>

## 作業一：拯救明華國小的資料庫，哪個欄位適合變成外來鍵？

>[!TIP]思考方式
>[miro資料庫關聯圖](https://miro.com/app/board/uXjVLJydqsQ=/?share_link_id=240893456501)
>因為一個班級有多個學生、一個性別有多個學生，所以「班級」、「性別」是外來鍵。建立students、class、genders資料表，最後再合併顯示資料。
### 建立class資料表，新增資料

--製作資料表
CREATE TABLE class(
    c_id SERIAL PRIMARY KEY,
    c_name VARCHAR(50)
);
--新增資料
INSERT INTO class(c_name)VALUES
    ('三年一班'),
    ('三年二班');

### 顯示class資料表

SELECT 
    c_id AS 班級編號,
    c_name AS 班級
FROM class;

### 建立genders資料表，新增資料

--製作資料表
CREATE TABLE genders(
    g_id SERIAL PRIMARY KEY,
    gender VARCHAR(50)
);
--新增資料
INSERT INTO genders(gender)VALUES
('女'),
('男');

### 顯示genders資料表

SELECT 
    g_id AS 性別編號,
    gender AS 性別  
FROM genders;

### 建立students資料表，新增資料

--製作資料表
CREATE TABLE students(
  st_id SERIAL PRIMARY KEY,
  st_name VARCHAR(50),
  class_id INTEGER,  --因為要顯示class資料表的PK，所以資料類型是INTEGER
  gender_id INTEGER, --因為要顯示genders資料表的PK，所以資料類型是INTEGER
  age INTEGER,
  FOREIGN KEY(class_id)REFERENCES class(c_id), --外來鍵
  FOREIGN KEY(gender_id)REFERENCES genders(g_id) --外來鍵
);
--新增資料
INSERT INTO students(st_name,class_id,gender_id,age)VALUES
('小明',1,2,8),
('小華',2,1,9),
('小美',1,2,8),
('小強',1,1,8),
('小智',2,2,9);

### 顯示students資料表

SELECT 
  students.st_id AS 學生編號, --資料表.欄位名稱
  students.st_name AS 姓名,
  class.c_name AS 班級,
  genders.gender AS 性別,
  students.age AS 年齡
  
FROM students --只寫主表

INNER JOIN class ON students.class_id=class.c_id --不用加逗號或分號
INNER JOIN genders ON students.gender_id=genders.g_id;

<br>

## 作業二：第一題的延伸，多了一個班級老師
>[!TIP]思考方式
>students資料表要建立一欄「班級老師」，然後更新students資料表每一筆資料的老師名字。最後再合併顯示students、class、genders資料表。
### students資料表新增teacher_name欄位(欄位內的資料是Null)

ALTER TABLE students --不用加逗號或分號 要新增欄位的資料表
ADD COLUMN teacher_name VARCHAR(50); --欄位名稱 資料類型

### 更新students資料表teacher_name欄位資料

UPDATE students
SET teacher_name='廖淯杰'
WHERE st_name='小明';

UPDATE students
SET teacher_name='卡斯伯'
WHERE st_name='小華';

UPDATE students
SET teacher_name='查理'
WHERE st_name='小美';

UPDATE students
SET teacher_name='麥可'
WHERE st_name='小強';

UPDATE students
SET teacher_name='李燕容'
WHERE st_name='小智';

### 顯示資料(合併students、class、genders資料表)

SELECT 
  students.st_id AS 學生編號, 
  students.st_name AS 姓名,
  class.c_name AS 班級,
  students.teacher_name AS 班級老師,
  genders.gender AS 性別,
  students.age AS 年齡
  
FROM students 

INNER JOIN class ON students.class_id=class.c_id 
INNER JOIN genders ON students.gender_id=genders.g_id;


<br>

## 作業三：小孩的家庭歸類資料庫，父母資料一值重複實在討厭！
>[!TIP]思考方式
>[miro資料庫關聯圖](https://miro.com/app/board/uXjVLJydqsQ=/?share_link_id=240893456501)
>建立parents資料表，然後students資料表新增一欄「家長編號 p_id(foreign key)」，再更新students資料表每一筆資料的家長編號資料。最後合併顯示students和parents資料表。
### 建立parents資料表，新增資料

CREATE TABLE parents(
  p_id SERIAL PRIMARY KEY,
  p_name VARCHAR(50),
  p_phone INTEGER,
  p_gender VARCHAR(50)
);

INSERT INTO parents(p_name,p_phone,p_gender)VALUES
('王大祥',0973254254,'男'),
('王曉如',0955717855,'女');

### students資料表新增p_id欄位，設為FK

ALTER TABLE students --要新增欄位的資料表
ADD COLUMN p_id INTEGER, --欄位名稱 資料類型
ADD FOREIGN KEY(p_id) REFERENCES parents(p_id);  --外來鍵

### 更新students資料表p_id欄位資料

UPDATE students
SET p_id=1
WHERE st_id IN(1,3,5);

UPDATE students
SET p_id=2
WHERE st_id IN(2,4);

### 顯示資料(合併students、parents資料表)

SELECT 
    students.st_id AS 小孩編號,
    students.st_name AS 姓名,
    parents.p_name AS 父母名稱,
    parents.p_phone AS 父母電話,
    parents.p_gender AS 父母性別
FROM students
INNER JOIN parents ON students.p_id=parents.p_id;


<br>

## 延伸作業：學校新開設了音樂班，聘請了一位美女老師擔任班導，王大祥家長的小孩都想轉到音樂班就讀。
>[!TIP]思考方式
>class資料表先新增音樂班，然後students資料表篩選王大祥後，更新class_id、teacher_name欄位資料，最後合併顯示資料。
### class資料表新增音樂班

INSERT INTO class(c_name)VALUES
    ('音樂班');

### 更新王大祥的老師和班級資料

UPDATE students
SET teacher_name='美女老師'
WHERE p_id=1;

UPDATE students
SET class_id=3
WHERE p_id=1;

### 顯示資料(合併students、class、genders、parents資料表)

SELECT 
  students.st_id AS 學生編號, 
  students.st_name AS 姓名,
  class.c_name AS 班級,
  students.teacher_name AS 班級老師,
  genders.gender AS 性別,
  students.age AS 年齡,
  parents.p_name AS 父母名稱
  
FROM students 

INNER JOIN class ON students.class_id=class.c_id 
INNER JOIN genders ON students.gender_id=genders.g_id
INNER JOIN parents ON students.p_id=parents.p_id;