Viiisit [Database] - 資料庫是什麼？

在之前透過 Active Record Query 去探索在 Rails 裡是如何對資料庫找資料的，想想發現對於資料庫的基本觀念似乎還沒有講述，今天來點資料庫吧！

資料庫是什麼？

簡單來說，資料庫是用於有效地存儲、檢索和管理所有資料！
平時在詳列出自己的購物清單明細，其實這樣列點式出來就很像一個小型的資料庫，
一張紙上列出一筆筆購物項目，可以新增修改刪除。
而在電腦上的資料庫就是儲存在電腦系統上的資料資訊集合，例如書店的庫存資訊。

在資料庫中，資訊被組織成表格，每個表格包含一組列和行，每列表示一種類型的資料，每行包含一條特定的數據記錄。

隨著資料越來越龐大，想要讓資料保持井然有序、易於存取且安全無虞變得更為困難。
為了解決這些問題，就有了現在常聽到的資料庫管理系統 (DBMS)。而資料庫管理系統又是什麼？

資料庫系統是什麼？

想要有效率地使用與管理資料，能方便、有效使用資料庫的軟體，
就是資料庫管理系統 (Database Management System，簡稱 DBMS)。
資料庫管理系統能幫我們與資料庫做溝通，
其中包括關聯型資料庫管理系統（RDBMS）以及非關聯型資料庫 (NoSQL) 。

為什麼非關聯型資料庫稱為 NoSQL?
在關聯型資料庫管理系統 (RDBMS) 裡，通常使用結構化查詢語言，也就是 SQL 語法來管理和檢索資料；而非關聯型資料庫 (NoSQL) 通常不使用 SQL，而是使用不同的方法來存儲和檢索資料！

• 關聯型資料庫管理系統（RDBMS）

  • MySQL：
    一個開源的關聯型資料庫管理系統，相容於各個不同的程式語言、作業系統、資源眾多，廣泛用於 Web 應用程式。
  • PostgreSQL：
    另一個開源關聯型資料庫，具有高度可擴展性和擴展功能，商業應用導向，擁有比 MySQL 更嚴格的測試驗證和設計機制。
  • Oracle Database：
    由 Oracle Corporation 甲骨文公司開發的商業級 RDBMS，廣泛用於企業級應用程式，常見於較有歷史的組織，例如銀行、公部門。
  • Microsoft SQL Server：
    由 Microsoft 開發的 RDBMS，適用於 Windows 環境並支持企業級資料管理。

• 非關聯型資料庫 (NoSQL)

  • MongoDB (Document DB)：
    一個以文件儲存方式的 NoSQL 資料庫，用於處理半結構化資料，特別適合大資料應用。
  • Cassandra (Column DB)：
    一個高度可擴展的分佈式 NoSQL 資料庫，用於處理大規模資料和時間序列資料。
  • Redis (Key-Value)：
    一個用於快速資料存儲和緩存的 NoSQL 資料庫，通常用於緩存和即時應用。
  • Amazon DynamoDB (Key-Value)：
    由亞馬遜 AWS 提供的分佈式 NoSQL 資料庫，專為高可用性和擴展性而設計。
  • Neo4j (Graph DB)：
    一個圖形資料庫，專為處理具有複雜關係的資料而設計，常用於社交網絡和推薦系統。

  資料庫也可以分成 伺服器型資料庫 和 檔案型資料庫：
  伺服器型資料庫 - 幾乎所有 RDBMS 都是伺服器型資料庫，資料庫需要先將資料庫安裝於電腦主機上才能使用。
  檔案型資料庫 - 一個檔案本身就是一個資料庫，像是 SQLite 適合用在資料量較少，且不會有多人同時存取的情況。

如何設計資料庫?

在建立資料庫時，需要先針對需求以及規格去思考要建立哪些資料表以及資料表中所包含的資料型態，
甚至還有資料與資料之間的關聯，今天就來淺談這幾點吧！Let’s go!

設計資料表

• 根據需求，開始設計資料表。每個資料表應該對應到一個特定的實體，例如顧客、訂單、商品等。
• 決定每個資料表中需要包含哪些欄位，以及每個欄位的資料型態，例如整數、文字、日期等。
• 考慮是否需要主鍵來唯一識別每個記錄，以及是否需要外鍵來建立資料表之間的關聯，可能包括一對一、一對多、多對多等關聯類型。

主鍵 PK vs 外鍵 FK

• 主鍵（Primary Key，PK）

  • 主鍵是資料表中的一個欄位，它的值用來唯一識別該資料表中的每一筆記錄。
  • 每個資料表只能有一個主鍵。
  • 主鍵的值必須是唯一的，不能重複，且不能為 NULL。
  • 主鍵通常用來建立資料表之間的關聯，或者用來加速查詢和更新操作。

• 外鍵（Foreign Key，FK）

  • 外鍵是資料表中的一個欄位，它的值來自另一個資料表的主鍵，建立兩個資料表之間的關聯。
  • 外鍵的值在資料表中可能重複，也可以為 NULL。
  • 外鍵通常用來建立不同資料表之間的關聯，
    例如建立一對多（One-to-Many）或多對多（Many-to-Many）的關係。
  • 外鍵用來確保資料的完整性和一致性，保證關聯的資料在不同資料表中是有效的。

主鍵用來唯一識別一個資料表中的每一筆資料，而外鍵用來建立資料表之間的關聯，確保資料的一致性。主鍵和外鍵在資料庫設計和資料表之間的連接中扮演著重要角色。

資料型態

在建立資料表之前，我們要先來看看有哪些資料型態：

1. 整數 (INTEGER):

   • 整數資料型態用於存儲不帶小數點的數值，用於存儲年齡、計數等。

2. 小數 (DECIMAL/NUMERIC, FLOAT, DOUBLE):

   • 這些資料型態用於存儲帶有小數點的數值，用於存儲價格、百分比等。

3. 字符 (CHAR, VARCHAR, TEXT):

   • 用於存儲文字，像是名稱、地址、評論等。
   • CHAR 是固定長度的字符，而 VARCHAR 是可變長度的字符。
   • CHAR vs VARCHAR 可以參閱：Viiisit SQL!

4. 日期和時間 (DATE, TIME, DATETIME, TIMESTAMP):

   • 這些資料型態用於存儲日期和時間。

5. 布林 (BOOLEAN):

   • 用於存儲真或假值，用於表示狀態、開關等。

6. 枚舉 (ENUM):

   • 這種資料型態允許在一組預定義的值中選擇一個，用於表示選項，例如產品類型（例如 “小型”、”中型”、”大型”）。

7. 二進制 (BINARY, BLOB):

   • 用於存儲二進制數據，例如圖像、音頻、文件，用於存儲用戶上傳的圖像或文件。

建立資料表：

• 建立顧客（Customer）資料表，
  包括顧客ID、顧客名稱、名字、姓氏、電子郵件和註冊日期等欄位：

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Customername VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE
);

• 建立訂單（Order）資料表，
  包括訂單ID、顧客ID、訂單日期、總金額等欄位，
  使用外鍵來建立與 “Customers” 資料表之間的關聯：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

• 商品（Product）資料表，
  包括商品ID、商品名稱、價格、類別等欄位：

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2),
    Category VARCHAR(50)
);

SQL 的基礎語法

上面我們看到了在建立資料表時，使用的 SQL 語法，
接著淺談一下，在 SQL 裡的基礎語法有哪些吧！

練習 SQL 語法！

Remark:
每段最後同時寫上呼應的 Rails 語法，底下的 Hero 是 Model 喔！

• SELECT 查詢資料

  SELECT * -- 挑出所有欄位
  FROM heroes;
  -- Rails: Hero.all

  SELECT *
  FROM heroes
  WHERE hero_level = 'S';
  -- Rails: Hero.where(hero_level: 'S')

  -- Method 1
  SELECT *
  FROM heroes
  WHERE (hero_level, gender) =  ('S', 'F');
  
  -- Method 2
  SELECT *
  FROM heroes
  WHERE hero_level = 'S';
  AND gender = 'F';
  -- Rails: Hero.where(hero_level: 'S', gender: 'F')

  • 只要名字與等級欄位：

  SELECT name, hero_level
  FROM heroes
  WHERE hero_level = 'S';
  -- Rails: Hero.select(:name, :hero_level).where(hero_level: 'S')

  • 判斷 age 是 NULL：

  SELECT *
  FROM heroes
  WHERE age is NULL;
  -- Rails: Hero.where(age: nil)

  • 練習搜尋的語法 LIKE
    找有背心開頭兩個字的詞：

  SELECT *
  FROM heroes
  WHERE name LIKE '背心%' ;
  -- Rails: Hero.where("name LIKE '%背心%'")

  • 尋找特定範圍：

  -- Method 1
  SELECT *
  FROM heroes
  WHERE age BETWEEN 10 AND 25; -- 有包含 10 and 25
  
  -- Method 2
  SELECT *
  FROM heroes
  WHERE age > 10 AND age < 25;
  -- Rails: Hero.where(age: 10...25)

  • 尋找 hero_level 是 S 級跟 A 級的：

  -- Method 1
  SELECT *
  FROM heroes
  WHERE hero_level IN ('S', 'A');
  
  -- Method 2
  SELECT *
  FROM heroes
  WHERE hero_level = 'S' OR hero_level = 'A';
  -- Rails: Hero.where(hero_level: ['S', 'A'])

  • 尋找 hero_level 不是 S 級的：

  -- Method 1
  SELECT *
  FROM heroes
  WHERE hero_level != 'S';
  
  -- Method 2 用大於小於組合！ SQL 專屬！
  SELECT *
  FROM heroes
  WHERE hero_level <> 'S';
  -- Rails: Hero.where.not(hero_level: 'S')
  -- Rails: Hero.where("hero_level != 'S'")

  • 尋找 hero_level 不是 S 級，也不是 A 級的：

  SELECT *
  FROM heroes
  WHERE hero_level NOT IN ('S', 'A');
  -- Rails: Hero.where.not(hero_level: ['S', 'A'])

  • 尋找是 A 級的男性，列出姓名與年齡：

  SELECT name, age
  FROM heroes
  WHERE gender = 'M' AND hero_level = 'A';

• UPDATE 更新資料

  UPDATE heroes
  SET age = 10
  WHERE id = 25

  • 讓所有的 heroes 年齡都加一：

  UPDATE heroes
  SET age = age + 1;

  • 請把 id = 35 的英雄等級由原本的 C 級 388 位調整成 B 級的 101 位：

  UPDATE heroes
  SET hero_level = 'B' ,  hero_rank = 101
  WHERE ID = 35;

• DELETE 刪除資料

  DELETE FROM
  WHERE

• 進階查詢 - 計算總數

  • 計算所有 S 級的總數：

  SELECT COUNT(*)
  FROM heroes
  WHERE hero_level = 'S';
  -- Rails: Hero.where(hero_level: 'S').count

  • 計算 A 級年齡的總和：

  SELECT SUM(age)
  FROM heroes
  WHERE hero_level = 'A' AND age IS NOT NULL;
  -- Rails: Hero.where(hero_level: 'A').sum

  • 計算 A 級年齡的平均：

  SELECT AVG(age)
  FROM heroes
  WHERE hero_level = 'A' AND age IS NOT NULL;
  -- Rails: Hero.where(hero_level: 'A').where.not(age: nil).average(:age)

• 分組

  • 以每個等級做分組且算出每組的平均年齡：

  SELECT hero_level, AVG(age)
  FROM heroes
  GROUP BY hero_level;
  -- Rails: Hero.group(:hero_level).average(:age)

• DISTINCT

  在 SQL 中，DISTINCT 用於去除查詢結果中重複的行，使得查詢結果只包含唯一值。

  挑出不同的級數：

  SELECT DISTINCT danger_level
  FROM monsters;

• 排序

  • 對 S 級做排序：

  -- 升冪排序
  SELECT *
  FROM heroes
  WHERE hero_level = 'S'
  ORDER BY hero_rank ASC;
  
  -- 降冪排序
  SELECT *
  FROM heroes
  WHERE hero_level = 'S'
  ORDER BY hero_rank DESC;

  SELECT *
  FROM heroes
  WHERE hero_level = 'S'
  AND age IS NOT NULL -- 可以排除掉沒有的值
  ORDER BY age;

  SELECT *
  FROM heroes
  WHERE hero_level = 'S'
  ORDER BY hero_rank
  LIMIT 5; -- 限定數量

  Remark:
  蝦皮的捲軸分頁視窗：
  如果 page = params[:page] || 1
  
  SELECT *
  FROM items
  ORDER BY id
  LIMIT 6
  OFFSET (page - 1) * 6;
  

• INNER JOIN 兩者交集
  LEFT JOIN 左邊為主對照右邊；RIGHT JOIN 右邊為主對照左邊

  SELECT *
  FROM t1
  INNER JOIN t2
  on t1.username = t2.name; -- 比對 t1 跟 t2 的相同名稱

  • 反派是被誰打倒的？

  SELECT monsters.name, heroes.name
  FROM monsters
  INNER JOIN heroes
  on monsters.kill_by = heroes.id
  WHERE kill_by IS NOT NULL;

  簡化：

  SELECT m.name, h.name
  FROM monsters as m
  INNER JOIN heroes as h
  on m.kill_by = h.id
  WHERE m.kill_by IS NOT NULL;

  透過 battle_histories 將資訊由 id 轉為 name：

  SELECT h.name,  m.name
  FROM battle_histories as bh
  INNER JOIN heroes as h
  INNER JOIN monsters as m
  on bh.hero_id = h.id AND bh.monster_id = m.id;

ERD - Entity Relationship Diagram 實體關係圖

一種用來描述資料庫中資料實體和它們之間關係的視覺化工具。

• 運用 子查詢，尋找被埼玉與傑諾斯幹掉的怪獸：

SELECT *
FROM monsters
WHERE kill_by = (
	SELECT id
	FROM heroes
	WHERE name = '埼玉'
)

SELECT *
FROM monsters
WHERE kill_by IN (
	SELECT id
	FROM heroes
	WHERE name IN ('埼玉', '傑諾斯')
)

思考一下：資料庫與 excel 的差別

資料庫和 Excel 是兩種不同的資料管理工具，在設計和使用上有很多差異：

• 資料結構：
  • 資料庫使用表格（Table）來組織和儲存資料，包含多個欄位（Column）和資料列（Row）。
  • Excel 是一個電子試算表軟體，使用單一工作表來儲存資料，每個工作表包含多個儲存格。
• 多用戶支援：
  • 資料庫可以支援多個使用者同時存取和修改資料，通過設置權限保護資料的一致性和安全性。
  • Excel 常常是單用戶的工具，一般情況下不支援多個使用者同時編輯。
• 資料容量：
  • 資料庫可以處理大量的資料，並且支援擴展來應對日益增長的資料量。
  • Excel 對於大型資料集的處理可能受限，因為它主要是設計為處理小型資料集。
• 查詢和分析：
  • 資料庫提供更強大的查詢語言（如 SQL），能夠進行複雜的查詢和分析操作。
  • Excel 也提供一些基本的查詢和過濾功能，但相對於資料庫來說功能有限。
• 適用範圍：
  • 資料庫主要用於大型企業或應用程式，用於儲存和管理大量結構化資料。
  • Excel 常用於個人或小型組織，用於較小的資料集的儲存和分析。

Brief Summary

資料庫適用於大型結構化資料的管理和處理，並且支援多用戶存取和複雜的查詢需求。
Excel 則適合用於較小的資料集，用於個人或小型組織的資料管理和分析。

今天就到這，我們下篇見！