2024-09-27 MySQL

分享範圍：資料庫介紹、MySQL 介紹、資料庫異動
參考講義：黃彬華老師線上講義

1. 資料庫的基本知識

由欄（columns）與列（rows）所組成，每一列都代表一筆實際的 data。

1.1 交易控制（Transaction Management）

對資料庫執⾏⼀個交易可能包含⼀連串的新增、修改或刪除指令。為了保證交易的正確與可靠，必須符合 ACID（Atomicity, Consistency, Isolation,Durability） 原則。
- 單元性、最小性（Atomicity）：交易中的所有操作不是全部執行，就是全部拒絕，沒有任何中間狀態。
- 一致性（Consistency）：交易前與交易後的結果都沒有破壞資料庫的完整性，也就是完全符合資料庫設定的規則。
- 隔離性（Isolation）：多個交易完全隔離開來，一個交易的執行不會被其他交易所影響。
- 持久性（Durability）：一個交易完成後，該交易對資料庫的變更會永久存在資料庫中。

1.2 關聯式資料庫 v.s. 非關聯式資料庫

關聯式資料庫
- 以數學集合論為基礎，建立表格與表格之間建立關聯性，以處理複雜資料。
- 使用 SQL 語言。
- 優點是什麼？避免浪費儲存空間、資料錯誤率提高、增加修改困難。
- Oracle DB, MySQL DB, Microsoft SQL Server, PostgreSQL, …
非關聯式資料庫
- 專門用來處理不易以固定表格結構表示的資料或大量非結構化的資料。
- 非關聯式資料庫不依賴數學集合論，並且不一定使用 SQL 語言進行操作。
- 常見的資料模型包括文件、鍵值、圖形、和寬行存儲等。
- MongoDB（文件型資料庫）、Redis（鍵值型資料庫）、Cassandra（列族型資料庫）、Neo4j（圖形資料庫）…
常見的 NoSQL 資料庫類型
1. 文件型資料庫：
- 將資料以文件形式儲存，每個文件是一個鍵值對或嵌套結構，可以存儲 JSON、BSON、XML 等格式。
1. 鍵值型資料庫：
- 以鍵值對的方式儲存資料，每個鍵對應一個值，非常適合簡單查詢和高速寫入的場景。
1. 圖形資料庫：
- 適合存儲和處理網狀結構的資料，節點與邊關聯的圖模型可以用來處理社交網絡、推薦系統等場景。
1. 列族型資料庫（寬行資料庫）：
- 類似於鍵值型資料庫，但可以將多列資料儲存在同一個鍵下，適合儲存超大規模的結構化和半結構化資料。

1.3 主鍵（PK） & 外來鍵（FK）

主鍵（Primary Key, PK）特性
- 主鍵欄位的值具有唯一性不可重複
- Not Null
外來鍵（Foreign Key, FK）特性
- 為了關聯而存在
- 必須參照到另一個表格的主鍵欄位
- 主鍵欄位必須要有值，外來鍵才能關聯

1.4 資料庫正規化（略）

2. 欄位資料類型介紹

2.1 數字類型

整數

signed / unsigned 代表有 / 無正負號

- TINYINT（1 byte）
- SMALLINT（2 bytes）
- MEDIUMINT（3 bytes）
- INT（4 bytes）
- BIGINT（5 bytes）

Image Not Showing Possible Reasons

The image was uploaded to a note which you don't have access to
The note which the image was originally uploaded to has been deleted

Learn More →

浮點數

- FLOAT（4 bytes）
- DOUBLE（8 bytes）

自訂精準位數

- DECIMAL(M,D)
# M代表總共的有效位數，D代表小數位數
# DECIMAL(5,2) -> 999.99 ~ -999.99

2.2 文字類型

- CHAR(M) 
# 固定長度，值為　0 ~ 255

- VARCHAR(M)
# 動態長度，⽤在不確定但有限度的字數上，值為　0 ~ 65,535

- TEXT（0 ~ 65535 characters）
- MEDIUMTEXT（0 ~ 16777215 characters）
- LONGTEXT（0 ~ 4294967295 characters）
# 用在不確定字數且無限度的字數

# M代表長度（字元數）
# UTF-8 ⼀個字元要佔⽤ 4 bytes

2.3 日期時間類型

輸入時可以接受⽂字或數字

- DATE
# 'YYYY-MM-DD'
# '2022-10-3', '20221003', 20221003

- TIME
# 'hh:mm:ss[.fraction]'
# fraction 是秒數後的位數，可到 6 位數
# '21:10:3', '21:10:3.123456', '211003', '211003.123456', 211003, 211003.123456

- TIMESTAMP
# 'YYYY-MM-DD hh:mm:ss[.fraction]'
# 會儲存時區資訊，將日期時間轉成 UTC 時間再儲存

- DATETIME
# 'YYYY-MM-DD hh:mm:ss[.fraction]'
# 不儲存時區資訊

可自動填入系統時間的型別：TIMESTAMP、DATETIME

- DEFAULT CURRENT_TIMESTAMP
# 新增時⾃動填入系統時間

- ON UPDATE CURRENT_TIMESTAMP
# 修改時⾃動填入系統時間

# fraction位數設定必須⼀致
# 例如：TIMESTAMP(6)，就要設定
DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

2.4 位元資料

可儲存⼤量資料，例如圖片

- BLOB（0 ~ 65535 bytes）
- MEDIUMBLOB（0 ~ 16777215 bytes）
- LONGBLOB（0 ~ 4294967295 bytes）

3. SQL 基礎語法 ─ 建立（待補）

4. SQL 基礎語法 ─ 修改（待補）

5. SQL 基礎語法 ─ 刪除（待補）

QA

FK 要怎麼改欄位型別？

Answer：先把約束條件拔掉

Base64 轉成 Blob（二位元資料）儲存的內容是只有 0 和 1 嗎？

Answer：待查找

可以在 MySQL Workbench 更新，然後再自動更新於 vscode 的 MySQL 嗎？

@@autocommit 的 @@ 代表什麼意思？

Answer：https://chatgpt.com/share/66f628a0-2370-8003-b731-86eb8d79d18b

References

SQL

延伸討論

PHP

https://www.w3schools.com/php/default.asp

Laravel

https://tony915.gitbooks.io/laravel4/content/intro_laravel/what_is_laravel.html