分類
網頁製作/軟體開發知識

2024 後端工程師 – 資料庫體驗營 | 小節作業：主鍵、外來鍵、inner join

在資料庫中設計結構化的資料表時，「主鍵」與「外來鍵」扮演了至關重要的角色。了解這些鍵的作用能有效地組織資料，並確保資料的完整性。此外，SQL 提供了 INNER JOIN 可以將不同表格的資料進行整合查詢。以下將針對主鍵、外來鍵與 INNER JOIN 做深入介紹，並使用 teams（部門）與 users（員工）兩個資料表來說明如何應用這些概念。

主鍵（Primary Key）

主鍵是資料表中的一個欄位（或多個欄位），能唯一識別每一筆資料。在設計資料表時，設定主鍵有助於避免重複資料，並加速查詢效率。在 teams 資料表與 users 資料表中，id 欄位被設定為主鍵，用於唯一識別每個部門與員工：

-- 部門資料表主鍵
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)  -- 設定外來鍵關聯
);

外來鍵（Foreign Key）

外來鍵是指在一個資料表中引用另一個資料表主鍵的欄位。透過外來鍵，可以在不同的表格之間建立關聯，從而將分散的資料組織在一起。在 users 資料表中，team_id 欄位作為外來鍵，參考了 teams 資料表中的 id 欄位。這樣的設計能將員工分配到特定部門，並維持資料的完整性。

SQL INNER JOIN

INNER JOIN 是 SQL 中的一種查詢方式，用來結合多個資料表中的資料。使用 INNER JOIN 查詢時，系統會比對兩個資料表中符合條件的紀錄，並僅返回那些符合條件的資料。以下的 SQL 查詢範例展示了如何利用 INNER JOIN 來查詢每位員工及其所屬的部門名稱。

SELECT 
    users.id AS user_id,      -- 員工編號
    users.name AS user_name,  -- 員工姓名
    users.salary,             -- 員工薪資
    teams.name AS team_name   -- 部門名稱
FROM 
    users
INNER JOIN 
    teams
ON 
    users.team_id = teams.id;

這段查詢會返回每位員工的 user_id、user_name、salary，以及其所屬的 team_name，即部門名稱。透過 INNER JOIN，系統會比對 users 資料表中的 team_id 欄位與 teams 資料表中的 id 欄位，從而顯示員工所屬的部門資料。執行後的結果如下：

user_iduser_namesalaryteam_name
1張小明45000開發部
2王大明48000開發部
3李小華52000人事部
4陳小玉55000人事部
5林小豪47000開發部

結論

在資料庫設計中，主鍵和外來鍵是確保資料完整性和關聯性的核心。透過這兩者的設計，資料表之間可以形成穩固的關聯結構。而使用 SQL 的 INNER JOIN 查詢，可以輕鬆地從多個表格中獲取關聯資料。正確運用主鍵、外來鍵與 INNER JOIN，不僅能增強資料的一致性，也讓數據管理更加便利有效。

作業1

作業一解答與說明

在這個學生資料表中，有以下幾個欄位：學生編號、姓名、班級、性別、年齡。

適合變成外來鍵的欄位是：班級。

原因說明：

  1. 班級欄位：通常每個班級會有自己的獨立資料表，包含班級名稱（如三年一班、三年二班）和其他班級相關資訊（例如導師姓名、班級代號等）。
  2. 建立關聯：當 班級 欄位作為外來鍵時，可以將學生資料表中的班級和班級資料表進行關聯。這樣可以查詢每個學生所屬的班級詳細資訊，並確保資料的完整性。

假設班級資料表的範例

-- 建立班級資料表
CREATE TABLE classes (
    id SERIAL PRIMARY KEY,    -- 班級編號，主鍵
    name VARCHAR(20)          -- 班級名稱，如三年一班
);

將班級設定為外來鍵的範例

-- 在學生資料表中使用外來鍵關聯班級
CREATE TABLE students (
    id SERIAL PRIMARY KEY,      -- 學生編號，主鍵
    name VARCHAR(50),           -- 姓名
    class_id INTEGER,           -- 班級編號，外來鍵
    gender VARCHAR(10),         -- 性別
    age INTEGER,                -- 年齡
    FOREIGN KEY (class_id) REFERENCES classes(id)  -- 設定班級欄位為外來鍵
);

這樣的設計能有效地將學生資料和班級資料分離，使資料庫更具結構化，也更方便查詢。

作業二解答與說明

在這種情況下，將「班級老師」的資訊獨立出來，放在班級資料表中會更合適。這樣可以避免重複輸入同一班級的老師資訊，同時方便日後管理和查詢。以下是如何修改資料表設計：

班級資料表

我們可以在 classes 資料表中增加一個 teacher_name 欄位，用於記錄每個班級的老師。

-- 班級資料表，增加班級老師欄位
CREATE TABLE classes (
    id SERIAL PRIMARY KEY,      -- 班級編號，主鍵
    name VARCHAR(20),           -- 班級名稱，如三年一班
    teacher_name VARCHAR(50)    -- 班級老師名稱
);

學生資料表

學生資料表保持不變，class_id 欄位作為外來鍵，指向 classes 資料表中的班級編號，這樣每個學生都可以關聯到特定的班級與班級老師。

-- 學生資料表
CREATE TABLE students (
    id SERIAL PRIMARY KEY,      -- 學生編號，主鍵
    name VARCHAR(50),           -- 姓名
    class_id INTEGER,           -- 班級編號，外來鍵
    gender VARCHAR(10),         -- 性別
    age INTEGER,                -- 年齡
    FOREIGN KEY (class_id) REFERENCES classes(id)  -- 設定班級欄位為外來鍵
);

使用 INNER JOIN 查詢每位學生的班級與班級老師

透過 INNER JOIN，我們可以查詢每位學生的班級名稱和班級老師：

SELECT 
    students.id AS student_id,      -- 學生編號
    students.name AS student_name,  -- 學生姓名
    students.gender,                -- 性別
    students.age,                   -- 年齡
    classes.name AS class_name,     -- 班級名稱
    classes.teacher_name            -- 班級老師名稱
FROM 
    students
INNER JOIN 
    classes
ON 
    students.class_id = classes.id;

查詢結果範例

執行上述查詢後，結果會包含每位學生的基本資訊，以及其所屬的班級名稱和班級老師名稱。例如：

student_idstudent_namegenderageclass_nameteacher_name
1小明男8三年一班廖清杰
2小華女9三年二班卡斯伯
3小美男8三年一班查理
4小強女8三年一班麥可
5小智男9三年二班李燕容

結論

將班級老師放在班級資料表中可以減少重複資料，並且可以更靈活地查詢每個學生的班級與老師資訊。透過設計良好的外來鍵和 INNER JOIN，可以更有效率地管理和查詢資料。

這個家庭資料庫的父母資訊有重複出現的情況。為了避免資料重複並提升資料管理效率，我們可以將父母資訊獨立成一張資料表，並使用外來鍵來將小孩資料與父母資料關聯起來。

作業三解答與說明

1. 建立「父母」資料表

將父母的姓名、電話號碼和性別等資訊放在單獨的 parents 資料表中。這樣可以避免每個小孩資料都重複儲存父母的資訊。

-- 父母資料表
CREATE TABLE parents (
    id SERIAL PRIMARY KEY,     -- 父母編號，主鍵
    name VARCHAR(50),          -- 父母名稱
    phone VARCHAR(20),         -- 父母電話
    gender VARCHAR(10)         -- 父母性別
);

2. 建立「小孩」資料表並使用外來鍵關聯父母

在 children 資料表中使用 parent_id 作為外來鍵，參考 parents 資料表的主鍵。這樣可以在 children 資料表中將每個小孩關聯到特定的父母。

-- 小孩資料表
CREATE TABLE children (
    id SERIAL PRIMARY KEY,      -- 小孩編號，主鍵
    name VARCHAR(50),           -- 小孩姓名
    parent_id INTEGER,          -- 父母編號，外來鍵
    FOREIGN KEY (parent_id) REFERENCES parents(id)  -- 設定外來鍵關聯
);

3. 插入父母資料

首先插入父母資料，以確保每個父母的資訊是唯一的。

-- 插入父母資料
INSERT INTO parents (name, phone, gender) VALUES 
    ('王大祥', '0973254254', '男'),
    ('王曉如', '0955717855', '女');

4. 插入小孩資料並關聯父母

在插入小孩資料時，利用 parent_id 來指定他們的父母。

-- 插入小孩資料，使用父母的 parent_id 來關聯
INSERT INTO children (name, parent_id) VALUES 
    ('小明', 1),   -- 1 表示父親王大祥
    ('小華', 2),   -- 2 表示母親王曉如
    ('小美', 1),   -- 1 表示父親王大祥
    ('小強', 2),   -- 2 表示母親王曉如
    ('小智', 1);   -- 1 表示父親王大祥

使用 INNER JOIN 查詢每個小孩與其父母的詳細資料

透過 INNER JOIN，可以查詢每位小孩的姓名以及對應的父母資訊：

SELECT 
    children.id AS child_id,        -- 小孩編號
    children.name AS child_name,    -- 小孩姓名
    parents.name AS parent_name,    -- 父母名稱
    parents.phone AS parent_phone,  -- 父母電話
    parents.gender AS parent_gender -- 父母性別
FROM 
    children
INNER JOIN 
    parents
ON 
    children.parent_id = parents.id;

查詢結果範例

執行上述查詢後的結果如下：

child_idchild_nameparent_nameparent_phoneparent_gender
1小明王大祥0973254254男
2小華王曉如0955717855女
3小美王大祥0973254254男
4小強王曉如0955717855女
5小智王大祥0973254254男

結論

透過將父母資料獨立成一張資料表並使用外來鍵關聯，可以有效地減少資料重複，並且使資料庫更具結構化。這樣的設計不僅有助於管理，也能讓查詢更為清晰明確。

找到父母姓名為 “王曉如” 的小孩姓名

要查詢父母姓名為 “王曉如” 的小孩姓名，可以使用 INNER JOIN 將 children 資料表與 parents 資料表進行關聯，並在 WHERE 條件中指定父母的姓名為 “王曉如”。

SELECT 
    children.name AS child_name  -- 小孩姓名
FROM 
    children
INNER JOIN 
    parents
ON 
    children.parent_id = parents.id
WHERE 
    parents.name = '王曉如';

查詢結果解釋

這段查詢會返回所有父母姓名為 “王曉如” 的小孩的姓名。這是透過 INNER JOIN 將 children 和 parents 資料表的 parent_id 和 id 欄位進行比對，然後篩選出符合條件的紀錄。

假設查詢結果如下：

child_name
小華
小強

這表示父母為 “王曉如” 的小孩有小華和小強。

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *