2024 資料庫體驗營 - 課前作業二

はじめに

學習一些基本的資料庫邏輯跟基礎的 SQL 語法，以及 PostgreSQL 操作與實戰應用
👉🏻 2024 後端工程師-資料庫體驗營

• 課前作業一

課前作業二

這個章節主要在說明：主鍵、外來鍵、inner join 的相關知識，請依以下三個作業題目，建立資料表。

題目一

我將班級做為外來鍵

• 建立班級資料表：包含班級編號(PK)、班級名稱

  CREATE TABLE classes (
      id SERIAL PRIMARY KEY,  --班級編號，主鍵
      name VARCHAR(10)        --班級名稱
  ); 
  

• 建立學生資料表：包含學生編號(PK)、姓名、班級(FK)、性別(boolean)、年齡

  CREATE TABLE students (
      id SERIAL PRIMARY KEY,  --學生編號，主鍵
      name VARCHAR(10),       --學生姓名
      classes_id INTEGER,     --班級，外來鍵
      is_male BOOLEAN,        --性別
      age INTEGER,            --年齡
      FOREIGN KEY (classes_id) REFERENCES classes(id)
  );
  

• 增加資料

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

  INSERT INTO 
      students(name, classes_id, is_male, age)
  VALUES
      ('小明',1,TRUE,8),
      ('小華',2,FALSE,9),
      ('小美',1,TRUE,8),
      ('小強',1,FALSE,8),
      ('小智',2,TRUE,9);
  

• 將班級表串聯回學生資料表

  SELECT 
      students.id AS 學生編號,
      students.name AS 姓名,
      classes.name AS 班級,
      (CASE WHEN is_male THEN '男' ELSE '女' END) AS 性別,
      students.age AS 年齡
  FROM students
  INNER JOIN classes 
  ON students.classes_id = classes.id;
  

Query Result

學生編號	姓名	班級	性別	年齡
1	小明	三年一班	男	8
2	小華	三年二班	女	9
3	小美	三年一班	男	8
4	小強	三年一班	女	8
5	小智	三年二班	男	9

題目二

• 建立老師資料表：老師編號(PK)、姓名

  CREATE TABLE teachers(
      id SERIAL PRIMARY KEY, --教師編號，主鍵
      name VARCHAR(10)
  );
  

• 增加資料

  UPDATE students
  SET class_teacher = CASE
      WHEN id = 1 THEN 1
      WHEN id = 2 THEN 2
      WHEN id = 3 THEN 3
      WHEN id = 4 THEN 4
      WHEN id = 5 THEN 5
  END
  WHERE id IN (1,2,3,4,5);
  

  (本來想說班級是固定的老師可以關聯，但看起來沒有關係w)

  INSERT INTO teachers(name)
  VALUES
      ('廖洧杰'),
      ('卡斯伯'),
      ('查理'),
      ('麥可'),
      ('李燕容');
  

• 學生資料表增加欄位
  我寫到作業三的時候想到，好像不需要增加這一欄？但就變成需要多增加一個表來關聯兩邊的關係

  ALTER TABLE students
  ADD COLUMN class_teacher INTEGER;
  

• 我忘記要設外來鍵了，現在補設定

  ALTER TABLE students
  ADD CONSTRAINT fk_class_teacher
  FOREIGN KEY (class_teacher)
  REFERENCES teachers(id);
  

• 串聯回學生資料表

  SELECT 
      students.id AS 學生編號,
      students.name AS 姓名,
      classes.name AS 班級,
      teachers.name AS 班級老師,
      (CASE WHEN is_male THEN '男' ELSE '女' END) AS 性別,
      students.age AS 年齡
  FROM students
  INNER JOIN classes ON students.classes_id = classes.id
  INNER JOIN teachers ON students.class_teacher = teachers.id
  ;
  

Query Result

學生編號	姓名	班級	班級老師	性別	年齡
1	小明	三年一班	廖洧杰	男	8
2	小華	三年二班	卡斯伯	女	9
3	小美	三年一班	查理	男	8
4	小強	三年一班	麥可	女	8
5	小智	三年二班	李燕容	男	9

題目三

• 建立小孩資料表：包含小孩編號(PK)、姓名、父母名稱(FK)

  CREATE TABLE children(
      id SERIAL PRIMARY KEY,
      name VARCHAR(50),
      parent_id INTEGER,
      FOREIGN KEY (parent_id) REFERENCES parents(id)
  );
  

  • 增加資料

  INSERT INTO children(name,parent_id)
  VALUES
      ('小明',1),
      ('小華',2),
      ('小美',1),
      ('小強',2),
      ('小智',1);
  

• 建立家長資料表：包含父母編號(PK)、姓名、電話、性別

  CREATE TABLE parents (
  id SERIAL PRIMARY KEY,	--父母編號，主鍵
  name VARCHAR(50),
  phone_number VARCHAR(15),
  sex BOOLEAN
  );
  

  • 增加資料

  INSERT INTO parents (name, phone_number, sex)
  VALUES 
      ('王大祥','0973254254',TRUE),
      ('王曉如','0955717855',FALSE);
  

• 將家長表串聯回小孩資料表

  SELECT 
      children.id AS 小孩編號,
      children.name AS 姓名,
      parents.name AS 父母名稱,
      parents.phone_number AS 父母電話,
      (CASE WHEN parents.sex THEN '男' ELSE '女' END )AS 父母性別
  FROM children
  INNER JOIN parents ON children.parent_id = parents.id;
  

Query Result

小孩編號	姓名	父母名稱	父母電話	父母性別
1	小明	王大祥	0973254254	男
2	小華	王曉如	0955717855	女
3	小美	王大祥	0973254254	男
4	小強	王曉如	0955717855	女
5	小智	王大祥	0973254254	男

加碼題

教師大裁員，剩下廖洧杰和卡斯伯老師，分別為3年1班和2班老師

• 修改 students 資料表的老師資料(一班由校長帶，二班由卡柱老師帶)

  UPDATE students
  SET class_teacher = CASE
      WHEN classes_id = 1 THEN 1
      WHEN classes_id = 2 THEN 2
  END
  WHERE id IN (1,2,3,4,5);
  

• 在 teachers 資料表增加一個欄位 is_active 表示是否在職

  UPDATE teachers
  SET is_active = CASE
      WHEN id = 1 THEN TRUE
      WHEN id = 2 THEN TRUE
      ELSE FALSE
  END;
  

Query Result

學生編號	姓名	班級	班級老師	性別	年齡
1	小明	三年一班	廖洧杰	男	8
2	小華	三年二班	卡斯伯	女	9
3	小美	三年一班	廖洧杰	男	8
4	小強	三年一班	廖洧杰	女	8
5	小智	三年二班	卡斯伯	男	9

最後に

真的是不常用就記不住…課前作業一是上週的事情就已經忘的差不多了，還好有筆記可以參閱。這週是多表單的關聯，跟 Access 及 Google Looker 的關聯觀念都是一樣的，所以理論的理解上還蠻容易，但實際上要怎麼寫就會需要不停翻筆記，再加上最近在寫 JS 小魔王作業，腦子就會有點轉不過來。w