後端技術考古題-資料庫篇(下)

※ 資料庫與 SQL

※ 題目：

1. 請寫出 SQL 讀取 people table 中所有 gender 是 M 而且 age 大於 18 的資料。

※ 解答：

SELECT *					
					FROM people					
					WHERE gender = 'M' AND age > 18				
				
				※ 說明：
這個查詢使用 SELECT 來選擇所有欄位（*表示所有），FROM 用於指定表名（在這裡是 people）
，而 WHERE 子句則用於指定篩選條件，以滿足 gender 為 "M" 且 age 大於 18	。	
																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																																		

2. 什麼情況要加入 index 索引？

何謂資料庫索引：

「索引 (indexes)」是資料庫提供的一個屬性之一。索引( index )就像是書的目錄，可以快速地找到內容所在的位置，目的是為了提高查詢速度。以資料庫來說，不使用索引的話，就必須從第一條紀錄開始比對，直到找到符合的值為止。如果表格的規模非常大，就會讓查詢速度非常慢，因此一張好的資料表要有相對應的索引來幫助搜尋。

添加索引的情況：

1. 頻繁的查詢操作：如果某個欄位經常被用於查詢，這個欄位上建立索引可以提高查詢效能。
2. 唯一性約束：確保某個欄位的值是唯一的時候，可以在這個欄位上建立唯一索引。
3. 排序和分組操作：經常對某個欄位進行order by , group by這種所謂排序或分組，則在該欄位上建立索引可以加速這些操作。
4. 外鍵關係：在外鍵欄位上建立索引有助於提高查詢的效能，尤其是涉及到 JOIN 操作的時候。
5. 範圍查詢：執行帶有範圍條件的查詢時，例如 BETWEEN 或 IN，在相應的欄位上建立索引可以提高查詢效能。
6. 大型資料表：執行查詢時，索引可以減少搜索所需的時間。

3. 什麼是 Transaction？什麼時候會用到？

※ 解答：

何謂Transaction：

Transaction，中文翻作交易或事務，是資料庫執行過程中的一個「邏輯單位」，一個 transaction 中包含多個對資料庫操作的行為，每個 transaction 有兩種可能的結局：全部執行成功 or 全部不執行（只要其中一個行為失敗就會發生「還原 (rollback)」，回復過程中的操作）。假如我們可以把一整組 SQL 操作包裝成一筆完整的交易，在執行 SQL 時必須要全部執行完成，否則寧願都不要執行。

什麼時候會用到：

當需要確保一系列操作的原子性、一致性、隔離性和持久性時，就需要使用Transaction。這有助於保證應用程序的正確運行，防止在不同步操作中出現故障或不一致的情況。

常見的應用場景：

1. 金融交易： 在金融應用中，如銀行轉帳、付款和提款等操作，需要確保資金的正確轉移，並防止出現不一致的狀態。

2. 訂單處理： 在電子商務系統中，當用戶下單時，需要確保相關庫存的扣減和訂單的生成是一個不可分割的操作，以保證庫存和訂單的一致性。

3. 預訂系統： 在預訂系統中，如機票預訂、酒店預訂等，需要確保座位或房間的狀態在操作中是一致的，以避免重複預訂。

4. 資料庫更新： 在對資料庫進行複雜的更新、插入或刪除操作時，為了確保這些操作的一致性，可以將它們放在一個交易中。

5. 系統管理： 在進行系統管理操作時，例如配置文件的修改，需要確保修改操作是完整的，防止出現系統配置的不一致。

6. 多用戶並發操作： 當多個用戶同時訪問資料庫時，可能會發生並發問題，使用交易可以確保這些操作之間的隔離性，防止相互影響。

4. 如何下 SQL 做分頁？

※ 解答：

在網頁中表格資料的呈現最怕就是資料量大到幾千筆甚至到萬筆查詢Loading過久，造成Server負荷不來的情況。因此通常會做一些分頁減輕查詢耗費資源與時間成本。

在 SQL 中，你可以使用 OFFSET 和 FETCH 或 LIMIT 和 OFFSET 來實現分頁。以下是兩種常見的 SQL 分頁方法的示例：

• 使用 OFFSET 和 FETCH (SQL Server, PostgreSQL, MySQL 8.0+)

SELECT column1, column2, ...
FROM your_table
ORDER BY your_order_column
OFFSET (page_number - 1) * page_size ROWS
FETCH NEXT page_size ROWS ONLY;

※ 說明：
your_table 是你的表格名稱。
your_order_column 是你想要排序的列。
page_number 是你希望獲取的頁數。
page_size 是每頁的行數

• 使用 LIMIT 和 OFFSET (MySQL, SQLite)

SELECT column1, column2, ...
FROM your_table
ORDER BY your_order_column
LIMIT page_size OFFSET (page_number - 1) * page_size;

※ 說明：
your_table 是你的表格名稱。
your_order_column 是你想要排序的列。
page_number 是你希望獲取的頁數。
page_size 是每頁的行數