MySQL 基本大全

Share This Post

資料庫基本介紹

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

資料庫的分類

  • 關聯資料庫(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 實體關係圖

  1. 實體

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

Untitled
  1. 屬性

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

Untitled
  1. 實體關係

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

Untitled
  1. 畫出完整關聯圖
Untitled

參考資料

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, ...),... ;

查詢

  1. 單表的基本查詢
  • 查詢所有紀錄
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
  1. 單表的基本查詢搭配條件判斷
  • 基本款(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 '台北%'
  1. 單表的基本查詢並且排序
  • 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
  1. 單表的基本查詢搭配聚合函數
  • 聚合函數
    • COUNT****()****
    • MAX()
    • MIN()
    • AVG()
    • SUM()
SELECT SUM(price) as 總和 , AVG(price) as 平均 from Products
  1. 單表的基本查詢搭配資料分組
SELECT orderID as 訂單編號 , SUM(total) as 總金額 from Orderdetial GROUP BY orderID
// 將同樣的 orderID 組成群組
  1. 單表的基本查詢搭配 HAVING

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

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

合併查詢

  1. 內部合併查詢
SELECT a.ID,b.ID FROM a INNER JOIN b on a.ID= b.ID
Untitled
  1. 左外部合併查詢
SELECT a.ID,b.ID FROM a LEFT JOIN b on a.ID= b.ID
Untitled
  1. 右外部合併查詢
SELECT a.ID,b.ID FROM a RIGHT JOIN b on a.ID= b.ID
Untitled
  1. 完全外部合併查詢
SELECT a.ID,b.ID FROM a FULL JOIN b on a.ID= b.ID
Untitled

修改

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

刪除

DELETE table_name WHERE col_name1='資料1'

參考資料

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

SQL table資料表的基本操作

正規化

為何要執行正規化?

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

正規化的資料庫特性

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

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

第一正規化

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

第二正規化

  1. 消除資料表中的部分相依

第三正規化

  1. 消除資料表中的遞移相依

範例

未經正規化的表格

Untitled

第一正規化後的表格

Untitled

第二正規化後的表格

Untitled
Untitled
Untitled

第三正規化後的表格

Untitled
Untitled
Untitled

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"
  }
});

查詢資料

  1. findAll
const users = await User.findAll({attributes: ['foo', 'bar']});
  1. findOne
const users = await Post.findOne({
  where: {
    authorId: 12,
    status: 'active'
  }
});

更改資料

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

剩下請參考官方說明

訂閱研究文章

Get updates and learn from the best

More To Explore

Scroll to Top

hurry up !

軟體工程師培訓

限時免費報名中

藉由與「真實世界軟體專案」相同的技術、工具與開發流程,化簡成與商業機密無關、門檻較低更容易上手的「模擬專案」,讓你有機會在職場前輩的陪伴下,完成真槍實彈的練習,動手解決真實的問題,快速累積個人的經驗與作品,而不只是「學習技術」而已。