資料庫基本介紹

資料庫，又稱為資料管理系統，使用者可以對檔案中的資料執行新增、擷取、更新、刪除等操作。

資料庫的分類

關聯資料庫(RDBMS)

資料庫是由多個資料表(Table)所組成，並且可以將資料表關聯起來，去連結多個資料表之間的關係。像是 MySQL , PostgreSQL … 都是關聯式資料庫。關聯式資料庫的特點如下：

由資料表(Table)組成，其中 row 代表一筆資料，column 代表資料欄位名稱
Schema 必須先定義好，並且只接受同樣格式資料的插入與修改。往後如果要修改 schema，必須對於已存在的資料做相對應的處理較為麻煩
可以使用 JOIN 來連結多個資料表，做較複雜的查詢
具備 ACID 特性
使用 SQL(Structured Querying Language) 來管理及查詢資料

<aside> 💡 ACID，是指資料庫管理系統在寫入或更新資料的過程中，為保證事務是正確可靠的，所必須具備的四個特性：原子性（不可分割性）、一致性、隔離性（獨立性）、持久性。

</aside>

非關聯資料庫(NOSQL)：

不需要定義 schema、沒有關聯的關係。像是 MongoDB, Redis, … 都是非關聯式資料庫的一種。非關聯式資料庫的特點如下：

資料庫由 collection 組成
collection 中每筆資料為一份 document，document 的資料格式不需一致
以 CAP theorem 為概念設計
常用於分散式雲端系統

<aside> 💡 CAP theorem，是指對於一個分布式計算系統來說，不可能同時滿足以下三點

一致性（等同於所有節點訪問同一份最新的數據副本）
可用性（每次請求都能獲取到非錯的響應——但是不保證獲取的數據為最新數據）
分區容錯性（以實際效果而言，分區相當於對通信的時限要求。系統如果不能在時限內達成數據一致性，就意味著發生了分區的情況，必須就當前操作在C和A之間做出選擇。） </aside>

關聯式資料庫設計程序

設計程序包含下列步驟

決定資料庫的用途 這有助於讓您為其餘步驟做好準備。
尋找及整理所需的資訊 收集您可能要在資料庫中記錄的所有資訊類型，例如產品名稱與訂單號碼。
將資訊分成多個資料表 將資訊項目分成主要實體或主體，例如 [產品] 或 [訂單]。每個主體就會變成資料表。
將資訊項目轉成資料行 決定您要在每個資料表中儲存哪些資訊。每個項目都會變成欄位，而且在資料表中會顯示為資料行。例如，[員工] 資料表可能包含 [姓氏] 和 [雇用日期] 等欄位。
指定主索引鍵 選擇每個資料表的主索引鍵。主索引鍵是用來唯一識別每個資料列的資料行。可能的範例為 [產品識別碼] 或 [訂單識別碼]。
設定資料表關聯性 查看每個資料表，並且決定一個資料表中的資料如何與其他資料表中的資料關聯。視需要將欄位新增到資料表或建立新資料表以釐清關聯性。
讓您的設計更完善 分析設計中的錯誤。建立資料表並新增幾筆範例資料的記錄。看看是否能夠從資料表獲得您想要的結果。視需要調整設計。
套用正規化規則 套用資料正規化規則，確認資料表的結構是否正確。視需要調整資料表。

ER Model 實體關係圖

實體

根據系統需求分析，寫出及畫出所有的實體，以方形來表示實體，並加以說明其意義。像是學生、老師、課程等，並將實體轉成資料表。如果實體必須依附其他實體而存在，則為弱實體，以雙方形來表示。例如選課系統中的學生與課程兩個實體，學生的緊急聯絡人則為弱實體，因為學生轉學，則該學生的緊急連絡人資料便不需要被紀錄。下圖是學生選課系統中的實體：

屬性

實體擁有的特性稱為屬性，以橢圓形來表示屬性，當屬性具有多重值時，則以雙橢圓形來表示，屬性為主鍵，則屬性名稱底下畫一條底線區別，複合屬性則為屬性再分割，衍生屬性則以虛橢圓形來表示，例如選課系統中的學生實體擁有學號、姓名、性別、地址、年齡和電話等屬性，可以如下所示：

實體關係

各實體之間相互關聯的限制，指實體與實體之間如果有關聯，則將此關聯以菱形連結兩實體，並寫出對應的基數。例如：每一個學生可以選修許多課程，一門課程可以被許多學生所選；每位學生皆有一位緊急聯絡人，每位聯絡人也只對應到一位學生。

畫出完整關聯圖

參考資料

4-2：實體關聯模型(E-R model)分析 – 資料庫系統教材教學網

資料庫層的基本操作

建立資料庫

寫上要建立的資料庫名稱。特別注意資料庫的名稱不可以有空白。

create database <db_name>

刪除資料庫

刪除特定名稱的料庫。

drop database <db_name>

資料表層的基本操作

建立資料表

寫上要建立的資料表名稱，資料表名稱後面接一個括號，括號裡面放置數組欄位名稱（column_name）與資料型態（data_type）。

NOT NULL 該欄位為必填
DEFAULT 該欄位設置預設值
PRIMARY KEY(column_name) 設主鍵，主鍵不可重複也不可為空值(NULL)
AUTO_INCREMENT 自動加1
UNIQUE 設定欄位值是唯一的，但可為空值
FOREIGN KEY (column_name 設外部鍵)

CREATE TABLE table_name (
    column_name1 data_type NOT NULL,
    column_name2 data_type DEFAULT "default",
    column_name3 data_type,
    column_name4 data_type **AUTO_INCREMENT**,
    column_name5 data_type ****UNIQUE**** ,    
		... ,
    PRIMARY KEY(column_name6)
    FOREIGN KEY (column_name7) REFERENCES customers(C_Id)
)

刪除資料表

drop table <table_name>

SQL 裡面的 table 操作

新增

新增一筆紀錄

INSERT INTO table_name(col_name1, col_name2, ...) values (col_value1, col_value2, ...);

新增多筆紀錄

INSERT INTO table_name(col_name1, col_name2, ...) values (col_value1, col_value2, ...),(col_value1, col_value2, ...),... ;

查詢

單表的基本查詢

查詢所有紀錄

SELECT * from table_name

查詢特定欄位紀錄(通過 as 給欄位取別名)

SELECT col_name1 as 名字 ,col_name2 from table_name

查詢所有不重複的紀錄

SELECT DISTINCT col_name1 from table_name

查詢所有不重複列的紀錄

SELECT DISTINCTROW col_name1,col_name2 from table_name

單表的基本查詢搭配條件判斷

基本款(AND、OR、>、<)

SELECT col_name1,col_name2 from table_name where col_name1>10

搭配 BETWEEN 查詢連續範圍

SELECT col_name1,col_name2 from table_name where col_name1 BETWEEN value1 AND value2

搭配 LIKE 查詢特定內容

% 萬用字元

SELECT * FROM customers WHERE Address LIKE '台北%'

單表的基本查詢並且排序

ASC 正序 (預設)

SELECT col_name1,col_name2 from table_name where col_name1>10 ORDER BY col_name1 (ASC)

DESC 倒序

SELECT col_name1,col_name2 from table_name where col_name1>10 ORDER BY col_name1 DESC

單表的基本查詢搭配聚合函數

聚合函數
- COUNT****()****
- MAX()
- MIN()
- AVG()
- SUM()

SELECT SUM(price) as 總和 , AVG(price) as 平均 from Products

單表的基本查詢搭配資料分組

SELECT orderID as 訂單編號 , SUM(total) as 總金額 from Orderdetial GROUP BY orderID
// 將同樣的 orderID 組成群組

單表的基本查詢搭配 HAVING

HAVING 可以搭配聚合函數做使用，但 WHERE 不行

SELECT Customer, SUM(Price) FROM orders GROUP BY Customer HAVING SUM(Price)<1000;

合併查詢

內部合併查詢

SELECT a.ID,b.ID FROM a INNER JOIN b on a.ID= b.ID

左外部合併查詢

SELECT a.ID,b.ID FROM a LEFT JOIN b on a.ID= b.ID

右外部合併查詢

SELECT a.ID,b.ID FROM a RIGHT JOIN b on a.ID= b.ID

完全外部合併查詢

SELECT a.ID,b.ID FROM a FULL JOIN b on a.ID= b.ID

修改

UPDATE table_name SET col_name1='資料2'， col_name2='資料3' WHERE col_name1='資料1'

刪除

DELETE table_name WHERE col_name1='資料1'

參考資料

MySQL資料庫的安裝與基本操作

SQL table資料表的基本操作

正規化

為何要執行正規化？

提昇儲存資料與資料庫操作效率
減少資料異常
使資料庫維護更容易

正規化的資料庫特性

經過正規化後的資料庫，應具備以下特性：

欄位唯一性：每個欄位只儲存一項資料
主關鍵欄位：每筆資料都擁有一個主鍵，來區別這些資料
功能關聯性：欄位之間的關聯應該要明確
欄位獨立性：欄位之間不應存在遞移相依

第一正規化

一個欄位只能有單一值
沒有任何兩筆以上的資料是完全重覆
資料表中有主鍵, 而其他所有的欄位都相依於「主鍵」

第二正規化

消除資料表中的部分相依

第三正規化

消除資料表中的遞移相依

範例

未經正規化的表格

第一正規化後的表格

第二正規化後的表格

第三正規化後的表格

ORM 工具 – Sequelize

<aside> 💡 本文章將使用 Node.js 來實作

</aside>

安裝 ORM 以及基礎設定請參考下方連結

Node.js ORM tool : Sequelize

新增資料

const jane = await User.create({ firstName: "Jane", lastName: "Doe" });

刪除資料

const jane = await User.destroy({
  where: {
    firstName: "Jane"
  }
});

查詢資料

findAll

const users = await User.findAll({attributes: ['foo', 'bar']});

findOne

const users = await Post.findOne({
  where: {
    authorId: 12,
    status: 'active'
  }
});

更改資料

const users = await User.update({ lastName: "Doe" }, {
  where: {
    lastName: null
  }
});

剩下請參考官方說明

MySQL 基本大全

Jane

Share This Post