[第九週]後端基礎 — 資料庫系統、Table schema、SQL 語法

學習目標：了解資料庫系統系統的種類、設定基本資料結構（Table schema）及 SQL 資料庫基本操作

資料庫（Database）

在程式的世界裡，我們可以將處理的資料儲存的變數裡，但當關閉程式後資料就會消失，沒有辦法永久儲存，因此我們會需要一個工具，拿長期存放我們需要應用的資料。舉例來說

資處三丙的導師徐磊為了成為稱職的老師，想把學生每次考試的成績記錄下來，因此就將成績儲存在一個 excel，而這個 excel 就是所謂的資料庫了。

圖片來源

某天磊哥找你，「我想做一個網站，讓學生可以看到自己期初、期中、期末的成績，你能幫幫我嗎？」你想想剛好最近在學習後端開發，這是個練習的好機會就答應了。
磊哥就把期初成績的 excel 給你，接著磊哥三個月就給一個新檔，有時候資料格式填錯，但看他的熱忱你只摸摸鼻子自己幫忙改資料，接著你又發現用 excel 每次讀檔引入程式時都要經過層層處理才能使用資料，很沒有效率效能又低，難道就沒有更好的方法嗎？？

…

因此就有人開發了資料庫系統（Database System），透過方便的介面與指令不僅能長期存放我們需要應用的資料，也方便隨時查詢與管理資料，這就是資料庫系統的價值所在。

資料庫系統（Database System）

用來管理資料庫的程式，提供更有效率的方法讓使用者透過資料庫管理系統來新增資料、資料更新、資料刪除、資料查看等功能。

所以資料庫系統裡分成兩個部分：

• 資料庫
• 資料庫管理系統(DBMS)

資料庫系統的種類

關聯式資料庫系統（RDB）

• 全名：Relational database
• 以 SQL 語言操作。
• 市面上常見的關聯式資料庫：MySQL、PostgreSQL、MSSQL。
• 大部分時機都是使用關聯資料。
• 類似 table 格式，資料間是有明確關聯性，藉由關聯性去撈取不同 table 間的資料。
• 例如：學生資料庫，其中明確的的關聯為學號。

table2：學生資料 ( 學號、姓名、電話、地址 ) ；table2：學生分數 ( 學號、國文、英文、數學 )

[補充說明] MySQL
MySQL 是目前市佔率最高的資料庫系統，主要也會採這個系統做練習，但我們看到 XAMPP 上面是寫 MariaDB，並不是 MySQL，這是因為當初 MySQL 被甲骨文公司收購後，大家擔心會不會突然有一天轉為商用，因此社群就 clone 出了 MariaDB ，因此這兩個系統幾乎是一模一樣的。

非關聯式資料庫系統

• 以 NoSQL （Not only SQL） 語言操作。
• 市面上常見的關聯式資料庫：MongoSQL。
• 沒有型態上的限制，可以放入陣列物件等，因此資料會多變且較無結構性。
• 不用新增欄位也不用去改資料庫結構，可以直接儲存新的資料。
• 使用的場合：log、日誌、社群上的資訊（按讚數、被分享數）。

管理資料庫 GUI

我們可以在哪裡看到資料庫呢？

• 第一種： CLI
  以指令的方式連進資料庫。
• 第二種是 GUI 介面： phpMyAdmin、Adminer、sequel pro… 等
  讓我們可以用網頁的介面來管理資料庫，跟資料本身沒有關係，即使不用這個介面也一樣可以輸入指令的方式連進資料庫。

phpMyAdmin

使用 XAMPP 的話，連接 localhost 後直接輸入網址： http://localhost:8080/phpmyadmin/

就會看到 phpMyAdmin，是一個用 PHP 寫的 GUI 資料庫管理軟體。

資料庫結構（Table schema）簡介

是資料庫中非常重要的部分，思考你的資料庫要長麼樣子、資料的型態、大小等等，這些就是在定義資料庫的結構。而之後的資料必須符合當初設定的結構，不然會無法增加資料。

[補充說明] 資料庫結構命名
在各種欄位命名的部分建議使用小寫字母，並使用底線進行字符連接
參考資料：一个因MySQL大小写敏感导致的问题

新建一個資料庫的步驟為：建立資料庫 ➡️ 建立資料表 ➡️ 定義資料庫結構

以下將說明資料庫結構常見的基本設定 — — — —

資料必定有兩個欄位：名稱、類型

名稱

• 類似 key 的概念
• 通常每個表都會有一個 id 的欄位

類型

• 大致可分為：數值、字串、日期及時間、空間類型及 JSON， 這幾個類型可以再細分出其他的型態，詳細可參考：An overview of SQL Server data types。
• 常見的「數值」型態 — — — —
  1. INT：整數，例如：id。
  2. TINYINT：-128~127。
• 常見的「字串」型態 — — — —
  1. CHAR：固定長度 0~255，當儲存字串不夠 255 時，會用空格補齊剩餘空間，因此讀取時必須把後面空格去除。
  2. VARCHAR：可變長度 0~65535，通常比較少的字串並搭配限制資料長度使用，可以有預設值。
  3. TEXT：~65535，不可設定長度，適合用於文字量比較多的欄位，例如：文章內容。

[補充說明] 查詢速度
CHAR > VARCHAR > TEXT

• 常見的「日期及時間」型態 — — — —
  1. DATE：日期。
  2. DATETIME：時間，常搭配預設值 CURRENT_TIMESTAMP（抓取資料目前的時間）做使用。
• 在 phpMyAdmin 只要停滯在選項上就會出現型態說明。

長度/值

• 限制資料的長度。

預設值

• 設定欄位的預設值。
• 常用的預設值 — — — —
  CURRENT_TIMESTAMP：目前時間。

編碼與排序

• 通常會設定在資料庫上，如果這個沒有設定好的話會出現亂碼
• utf8_unicode_ci 和 utf8_general_ci 這兩種是最常用的，兩者的差異在於

utf8_general_ci：對某些語言的支援會有一些小問題，但速度比較快，utf8_unicode_ci：比較精確但速度比較慢

屬性

• 常用的屬性

UNSIGNED：無符號，意思是只允許正整數，會搭配數值型態做使用

空值

• 是否允許空值

索引

• PRIMARY （主鍵）：最主要的 key，不能為空值也不能重複，例如：id 、會員帳號、員工編號等。
• UNIQUE（唯一）：不能重複，例如：email、姓名
• INDEX（索引）：一旦幫欄位建立索引就可以快速搜尋，必須設定索引名稱，建立索引會需要花費額外的空間以及建立索引需要時間，可以是個複合欄位。

AI(auto_increment)

• 資料會自動增加，保證遞增不保證連續。
• 經常使用在 id 這一類型的資料上。

MySQL 基礎語法介紹

定義好資料庫後就可以開始操作資料，以下會說明最基本的四個操作「新增」、「查詢」、「修改」、「刪除」。

INSERT 新增資料

• 語法：

INSERT INTO 資料表名稱 (欄位名稱, 欄位名稱, ...) VALUES (欄位值, 欄位值)

• 範例

INSERT INTO users (name, email, age) VALUES (yoyo, yoyo@gmail.com, 3)

SELECT 查詢資料

• 語法

SELECT 欄位名稱, 欄位名稱, ... FROM 資料表名稱

• 範例1. 基本查詢 — — — —

SELECT name FROM users

• 範例2. 使用 * 查詢所有欄位 — — — —

SELECT * FROM users

• 範例3. 查詢多欄位 — — — —

SELECT name mail FROM users

• 範例4. 使用 * 查詢所有欄位 — — — —
• 條件查詢 WHERE
  1. 多條件 AND
  2. 其中一個條件成立
• 範例5. 條件查詢 — — — —

SELECT name FROM users
where name = 'joy' and id=3 // 等於 & 
where name = 'joy' or id=3 // 等於 ||

• 排序 ORDER BY ：可以設定由大到小 DESC 或由小到大 ASC
• 範例6. 大到小 排序 — — — —

SELECT * FROM users
ORDER BY age DESC

UPDATE 修改資料

• 語法

UPDATE 資料表名稱 SET 欄位名稱 = 欄位值 WHERE 欄位名稱 = 欄位值

• WHERE 的條件決定更改到哪些資料。
• 範例1. 將 name = joy 的那列 把 age 設為 60 — — — —

UPDATE users SET age = 20 WHERE name= joy

DELETE 刪除資料

• 語法

DELETE FROM 資料表名稱 WHERE 欄位名稱 = 欄位值

• 範例1. 將 name = joy 的那列刪除

DELETE FROM user WHERE name = "joy"

參考資料

• [BE101] 用 PHP 與 MySQL 學習後端基礎
• [第九週] 後端基礎 — PHP、資料庫、SQL 語法
• 資料庫概念
• 「筆記」- 何謂資料庫系統
• 簡明 SQL 資料庫語法入門教學

以上有任何錯誤的地方歡迎指正，感謝。