SQL -總結

奧莉薇

2024/08/28閱讀時間約 12 分鐘

※ 整個SQL課程過程

＊如何從資料庫拿資料開始：

• SELECT ：從資料庫拿資料。

• ORDER BY ：排序SELECT的結果。

• JOIN ：連結各個TABLE的資訊，發揮關聯式資料庫的優勢。

• GROUP BY ：對資料庫的資料做加總的AGGREGATE FUNCTION操作。

GROUP HAVING ：篩選出GROUP BY後的結果。

• Subquery ：把多個QUERY放在一個QUERY裡面，去減少多個QUERY造成的網路溝通成本。

CTE ：取代Subquery更簡潔的寫法，用更簡潔的方法去表達複雜的QUERY，可以幫助我們思考並減少錯誤。

＊如何加入、更新、刪除資料：

• INSERT

• Update

• Delete

＊把一系列的資料當成一個Transaction，結果不是成功就是失敗：

• Transaction

＊資料庫需求變動時，去更改table欄位的資訊；例如欄位的型別、新增和刪除欄位：

• Alter table

※ 創建資料庫案例：SPOTIFY SYSTEM

※ 創建資料庫第一步：設計Table 和Table的欄位

使用者（User）可以創建歌單（Playlist），每個歌單裡面可以包含多首歌曲（Song）。創作者（Artist）會發佈專輯（Album），每張專輯會包含多首歌曲。我們需要為這五個元素創建表格（table），以記錄它們的詳細資訊。

• Spotify 有什麼元素：

User （使用者）：使用者可以創建多個歌單（Playlist）。
Artist（創作者）：創作者可以發佈多張專輯。
Album （專輯）：每張專輯由創作者（Artist）發佈，並包含多首歌曲。
Song （歌）：每首歌曲可以屬於多個播放列表，也可以屬於一張專輯（Album）。
Playlist（歌單）：每個歌單可以包含多首歌曲（Song）。

• 元素之間的關係：

⼀對多

• Artist（創作者） -> Album （專輯）：

創作者一個人可以創造好幾張專輯，可是一張專輯只會被一個創造者所創造，就是一對多的關係。

舉例：在資料庫設計中，我們可以在 Albums 表格中添加一個 artist_id 欄位，這樣每張專輯都會有一個對應的創作者 ID。這個 artist_id 欄位是 Artists 表格中的主鍵（primary key），並且在 Albums 表格中作為外鍵（foreign key）使用。這樣的設計可以讓我們通過 artist_id 將 Albums 表格和 Artists 表格連結起來。

當我們需要查詢某張專輯的創作者詳細資料時，可以使用 SQL 的 join table 概念來連結這兩個表格。例如，查詢 album_id 為 1 的專輯的創作者詳細資料。

• Album（專輯） -> Song（歌）：

一張專輯裡面可以有好幾首歌，可是一首歌只會屬於一張專輯，就是一對多的關係。

舉例：當我們在歌曲 (song) 表格中加入 album_id 欄位後，可以將 song_id 為 1 的這首歌與 album_id 為 1 的專輯連結起來。然後，透過 JOIN 操作，可以在專輯 (album) 表格中查找 album_id 為 1 的詳細資訊。這樣就可以把兩個表格連結在一起，這正是一對多關係的處理方式。

多對多

• User <-> Song：一個使用者可以喜歡好幾首歌，一首歌也會被好幾個使用者喜歡。

• User <-> Album：一個使用者可以喜歡好幾張專輯，一張專輯可以被好幾個使用者喜歡。

• User <-> Artist：一個使用者可以追蹤一個創作者，一個創作者可以被好幾個使用者追蹤。

• User <-> User：一個創作者可以被好幾個使用者追蹤。使用者可以追蹤好幾個使用者，使用者可以被好幾個使用者追蹤。

• User <-> Playlist：一個使用者可以創建好幾個歌單，一個歌單也可以被好幾個創作者所創作。一個播放清單裡面可以有幾首歌，一首歌可以屬於好幾個歌單。

• Playlist <-> Song：一個歌單裡面可以有幾首歌，一首歌可以屬於好幾個歌單。

舉例：User <-> Song（使用者喜歡的歌）

在多對多關係中，直接在 Users 表或 Songs 表中新增欄位來記錄關係會導致資料重複。例如：

如果在 Users 表中新增 song_id 欄位，則每個使用者喜歡的每首歌都需要新增一行，這會導致使用者資訊的重複。
如果在 Songs 表中新增 user_id 欄位，則每首歌被每個使用者喜歡都需要新增一行，這會導致歌曲資訊的重複。

解決⽅案

額外建立中間表可以有效地管理多對多關係，並避免資料重複和空間浪費：

User_Song 表：用來儲存使用者喜歡的歌曲。
User_Album 表：用來儲存使用者喜歡的專輯。
User_Artist 表：用來儲存使用者追蹤的創作者。
User_Follow 表：用來儲存使用者之間的追蹤關係。
User_Playlist 表：用來儲存使用者創建的歌單。
Playlist_Song 表：用來儲存歌單中的歌曲。

※ Spotify 的操作

• 找到⼀張專輯裡⾯的歌 - ORDER BY

說明：

關係：專輯和歌曲之間是一對多的關係。
欄位：在 song 表中添加一個 album_id 欄位來標識每首歌所屬的專輯。
篩選：使用 SELECT 和 WHERE 語句來篩選出屬於特定 album_id 的所有歌曲。
順序：在 song 表中添加一個 order 欄位來記錄每首歌在專輯中的順序。
排序：使用 ORDER BY 語句根據 order 欄位對歌曲進行排序。

• 找到⼀⾸歌所屬的專輯跟創作者 - JOIN

說明：

關係：
- artist 和 album 是一對多的關係，所以 album 表中會有 artist_id 欄位。
- album 和 song 也是一對多的關係，所以 song 表中會有 album_id 欄位。
步驟：
- 首先，將 artist 和 album 兩個表進行 JOIN，使用 artist_id 作為連接條件。
- 然後，將上一步的結果與 song 表進行 JOIN，使用 album_id 作為連接條件。
查詢：
- 這樣可以得到包含 song_id、artist_id 和album_id 的結果。

• 找到使⽤者 Liked Songs（喜歡的歌）- CTE

說明：

建立 Junction Table：由於使用者和歌曲之間是多對多的關係，我們需要創建一個 junction table 來記錄這些關係。
使用 CTE 紀錄喜歡的歌曲：我們利用 CTE（Common Table Expression）來紀錄使用者喜歡的歌曲的 ID。
Inner Join：接著，我們將這些 ID 與歌曲表（song table）進行 inner join，以獲取使用者喜歡的歌曲的詳細資訊。
包含創作者和專輯資訊：每首歌都包含創作者和專輯的資訊。因此，我們需要將歌曲表與創作者表和專輯表進行 inner join。
最終結果：我們將這些表與 CTE 中的歌曲 ID 進行 join，這樣就可以獲取使用者喜歡的歌曲的完整資訊，包括創作者和專輯的詳細資料。

• 找到⼀個創作者的⽉總觀看數-GROUP BY

說明：

連結表格：首先，我們需要將 song_id、artist_id 和 album_id 這三個表格進行 JOIN 操作，得到一個包含 song_id、user_id、album_id 和 monthly_plays 的表格。
GROUP BY：接著，我們利用 GROUP BY 對 user_id 這個欄位進行分組。
加總：最後，我們將每個 user_id 所有歌曲的 monthly_plays 加總起來，得到每位創作者的月總觀看數。

找到⽉總觀看數超過⼀百萬的發燒創作者 -HAVING

說明：

GROUP BY：首先，我們對 user_id 進行分組，並對 monthly_plays 進行加總。
HAVING：接著，我們使用 HAVING 子句來篩選出月總觀看數超過一百萬的創作者。

• 使⽤者對⼀⾸歌按愛⼼ - INSERT

說明：

當使用者對一首歌按愛心時，我們需要將這個操作記錄到資料庫中。這涉及到在記錄使用者和歌曲之間多對多關係的中介表（junction table）中進行插入操作。具體步驟如下：

插入操作：將使用者的 ID（user_id）和歌曲的 ID （song_id）插入到中介表中。
記錄關係：這樣就能記錄使用者對這首歌按愛心的行為。

• 創作者更新封⾯圖片和⾃我介紹 - Update

說明：

我們可以使用 UPDATE 語法來更新 artist 表中的封面照片和自我介紹這兩個欄位。具體步驟如下：

指定表格：我們需要更新的表格是 artist。
設定新值：我們需要更新封面照片和自我介紹這兩個欄位。
條件：我們需要指定哪個創作者的資料需要更新。

• 創作者下架專輯 - DELETE

說明：

當創作者要下架專輯時，我們可以使用 DELETE 和 ON DELETE CASCADE 來確保專輯及其所屬的歌曲都被刪除：

刪除專輯：當創作者要下架專輯時，我們需要刪除 album 表中的記錄。
刪除歌曲：同時，專輯內所屬的歌曲也需要從 song 表中刪除。
ON DELETE CASCADE：在 song 表中設置一個指向 album 表的外鍵，並使用 ON DELETE CASCADE 選項。這樣當我們刪除 album 表中的記錄時，相關的 song 表中的記錄也會自動被刪除。

這樣的設置確保了當我們刪除專輯時，相關的歌曲也會被自動刪除。接著，我們可以使用 DELETE 語法來刪除專輯。

• 將歌單中的兩⾸歌調換順序 - TRANSACTION

說明：

當使用者想要將歌單中的兩首歌調換順序時，這涉及到多對多關係的中介表（junction table）。這個table除了記載playlist裡面有這首歌之外，還必須記載這首歌在playlist中的順序。為了確保兩個更新操作（即將第1首歌的順序更新為第2首，將第2首歌的順序更新為第1首）要麼全部成功，要麼全部失敗，我們可以使用TRANSACTION來包裹這兩個更新操作。

多對多關係：歌單和歌曲之間是多對多的關係，這些關係記錄在中介表（junction table）中。
順序記錄：中介表（junction table）除了記載playlist裡面有這首歌之外，還必須記載這首歌在playlist中的順序。
更新操作：當使用者想要調換兩首歌的順序時，需要進行兩個更新操作：
- 將第1首歌的順序更新為第2首
- 將第2首歌的順序更新為第1首
使用TRANSACTION：為了確保這兩個更新操作要麼全部成功，要麼全部失敗，可以使用TRANSACTION來包裹這兩個更新操作。