2024-08-28|閱讀時間 ‧ 約 38 分鐘

SQL -總結

    ※ 整個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 有什麼元素 :

    1. User (使用者)使用者可以創建多個歌單(Playlist)。
    2. Artist(創作者):創作者可以發佈多張專輯。
    3. Album (專輯):每張專輯由創作者(Artist)發佈,並包含多首歌曲。
    4. Song (歌):每首歌曲可以屬於多個播放列表,也可以屬於一張專輯(Album)。
    5. Playlist(歌單):每個歌單可以包含多首歌曲(Song)。


    元素之間的關係:

    1. ⼀對多

    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 的詳細資訊。這樣就可以把兩個表格連結在一起,這正是一對多關係的處理方式。

    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

    說明:

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


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

    說明:

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


    找到使⽤者 Liked Songs(喜歡的歌)- CTE

    說明:

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


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

    說明:

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


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

    說明:

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


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

    說明:

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

    1. 插入操作:將使用者的 ID(user_id) 和歌曲的 ID (song_id)插入到中介表中。
    2. 記錄關係:這樣就能記錄使用者對這首歌按愛心的行為。


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

    說明:

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

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


    創作者下架專輯 - DELETE

    說明:

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

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

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


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

    說明:

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

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


    發現原本設計的 Table 不完美或需求改變 - ALTER TABLE

    說明:

    可以使用ALTER TABLE語句來修改現有的表結構。這可以包括添加、刪除或修改列,添加或刪除約束等。

    1. 添加新列當需要在表中添加新列時,可以使用ADD關鍵字。
    2. 刪除列當需要從表中刪除某列時,可以使用DROP COLUMN關鍵字。
    3. 修改列的數據類型當需要修改某列的數據類型時,可以使用ALTER COLUMN關鍵字。
    4. 重命名列當需要重命名某列時,可以使用RENAME COLUMN關鍵字。
    5. 添加約束當需要在表中添加約束時,可以使用ADD CONSTRAINT關鍵字。
    6. 刪除約束當需要從表中刪除約束時,可以使用DROP CONSTRAINT關鍵字。

    ※ ⼩叮嚀

    撰寫 QUERY 的關鍵 - 越少 QUERY,效率越好。




    分享至
    成為作者繼續創作的動力吧!
    © 2024 vocus All rights reserved.