資料庫基本介紹
資料庫,又稱為資料管理系統,使用者可以對檔案中的資料執行新增、擷取、更新、刪除等操作。
資料庫的分類
- 關聯資料庫(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'
參考資料
正規化
為何要執行正規化?
- 提昇儲存資料與資料庫操作效率
- 減少資料異常
- 使資料庫維護更容易
正規化的資料庫特性
經過正規化後的資料庫,應具備以下特性:
- 欄位唯一性:每個欄位只儲存一項資料
- 主關鍵欄位:每筆資料都擁有一個主鍵,來區別這些資料
- 功能關聯性:欄位之間的關聯應該要明確
- 欄位獨立性:欄位之間不應存在遞移相依
第一正規化
- 一個欄位只能有單一值
- 沒有任何兩筆以上的資料是完全重覆
- 資料表中有主鍵, 而其他所有的欄位都相依於「主鍵」
第二正規化
- 消除資料表中的部分相依
第三正規化
- 消除資料表中的遞移相依
範例
未經正規化的表格

第一正規化後的表格

第二正規化後的表格



第三正規化後的表格



ORM 工具 – Sequelize
<aside> 💡 本文章將使用 Node.js 來實作
</aside>
安裝 ORM 以及基礎設定請參考下方連結
新增資料
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
}
});
剩下請參考官方說明