• SELECT :從資料庫拿資料。
• ORDER BY :排序SELECT的結果。
• JOIN :連結各個TABLE的 資訊,發揮關聯式資料庫的優勢。
• GROUP BY :對資料庫的資料做加總的AGGREGATE FUNCTION操作。
• Subquery :把多個QUERY放在一個QUERY裡面,去減少多個QUERY造成的網路溝通成本。
• INSERT
• Update
• Delete
• Transaction
• Alter table
使用者(User)可以創建歌單(Playlist),每個歌單裡面可以包含多首歌曲(Song)。創作者(Artist)會發佈專輯(Album),每張專輯會包含多首歌曲。我們需要為這五個元素創建表格(table),以記錄它們的詳細資訊。
• Spotify 有什麼元素 :
• 元素之間的關係:
• 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
表:用來儲存歌單中的歌曲。• 找到⼀張專輯裡⾯的歌 - ORDER BY
說明:
song
表中添加一個 album_id
欄位來標識每首歌所屬的專輯。SELECT
和 WHERE
語句來篩選出屬於特定 album_id
的所有歌曲。song
表中添加一個 order
欄位來記錄每首歌在專輯中的順序。ORDER BY
語句根據 order
欄位對歌曲進行排序。• 找到⼀⾸歌所屬的專輯跟創作者 - JOIN
說明:
artist_id
欄位。album_id
欄位。artist_id
作為連接條件。album_id
作為連接條件。song_id
、artist_id
和album_id
的結果。• 找到使⽤者 Liked Songs(喜歡的歌)- CTE
說明:
• 找到⼀個創作者的⽉總觀看數-GROUP BY
說明:
song_id
、artist_id
和 album_id
這三個表格進行 JOIN 操作,得到一個包含 song_id
、user_id
、album_id
和 monthly_plays
的表格。GROUP BY
對 user_id
這個欄位進行分組。user_id
所有歌曲的 monthly_plays
加總起來,得到每位創作者的月總觀看數。HAVING
說明:
user_id
進行分組,並對 monthly_plays
進行加總。HAVING
子句來篩選出月總觀看數超過一百萬的創作者。• 使⽤者對⼀⾸歌按愛⼼ - INSERT
說明:
當使用者對一首歌按愛心時,我們需要將這個操作記錄到資料庫中。這涉及到在記錄使用者和歌曲之間多對多關係的中介表(junction table)中進行插入操作。具體步驟如下:
user_id
) 和歌曲的 ID (song_id
)插入到中介表中。• 創作者更新封⾯圖片和⾃我介紹 - Update
說明:
我們可以使用 UPDATE
語法來更新 artist
表中的封面照片和自我介紹這兩個欄位。具體步驟如下:
artist
。• 創作者下架專輯 - DELETE
說明:
當創作者要下架專輯時,我們可以使用 DELETE
和 ON DELETE CASCADE
來確保專輯及其所屬的歌曲都被刪除:
album
表中的記錄。song
表中刪除。song
表中設置一個指向 album
表的外鍵,並使用 ON DELETE CASCADE
選項。這樣當我們刪除 album
表中的記錄時,相關的 song
表中的記錄也會自動被刪除。這樣的設置確保了當我們刪除專輯時,相關的歌曲也會被自動刪除。接著,我們可以使用 DELETE
語法來刪除專輯。
• 將歌單中的兩⾸歌調換順序 - TRANSACTION
說明:
當使用者想要將歌單中的兩首歌調換順序時,這涉及到多對多關係的中介表(junction table)。這個table除了記載playlist裡面有這首歌之外,還必須記載這首歌在playlist中的順序。為了確保兩個更新操作(即將第1首歌的順序更新為第2首,將第2首歌的順序更新為第1首)要麼全部成功,要麼全部失敗,我們可以使用TRANSACTION
來包裹這兩個更新操作。
TRANSACTION
:為了確保這兩個更新操作要麼全部成功,要麼全部失敗,可以使用TRANSACTION
來包裹這兩個更新操作。• 發現原本設計的 Table 不完美或需求改變 - ALTER TABLE
說明:
可以使用ALTER TABLE
語句來修改現有的表結構。這可以包括添加、刪除或修改列,添加或刪除約束等。
ADD
關鍵字。DROP COLUMN
關鍵字。ALTER COLUMN
關鍵字。RENAME COLUMN
關鍵字。ADD CONSTRAINT
關鍵字。DROP CONSTRAINT
關鍵字。撰寫 QUERY 的關鍵 - 越少 QUERY,效率越好。