Day 10: SQL Database - SQL Selection - iT 邦幫忙::一起幫忙解決難題，拯救 IT 人的一天

2023 iThome 鐵人賽

DAY 10

DevOps

從 0 開始培育成為自動化測試工程師的學習指南系列第 10 篇

Day 10: SQL Database - SQL Selection

15th鐵人賽 mysql

yingngai

2023-09-15 00:02:35

722 瀏覽

分享至

學習原因：

一般系統都會對 Database 作大量的操作，因此作為自動化測試工程師，必須要了解如何應用 Database 作資料的驗證。甚至在理解 Database 的設計原則之後，在系統設計時，便已經可以檢閱設計是否符合需求。

數據庫可以分為 2 個主要類型，SQL Database (關聯式數據庫) 和 NoSQL Database (非關聯式數據庫)。針對初學者，會先以 SQL Database 帶出 Database 的基本概念，後續進階需要再來學習 NoSQL Database。

而這一篇主要先講述 SQL Selection 的語法，以及應用時需要注意效能的問題。

學習目標:

認識 SELECT SQL 的基本語法
認知如何提高 SQL Selection 的效能

Database 是資料庫就像一個超級大的文件夾，裡面可以存放很多的表格。每個表格就像是一個電子表格，裡面可以存放各種各樣的資料，就像是我們平常用電子表格保存數據一樣。

但數據庫是一個 有組織的數據存儲空間，讓我們可以有效地 保存、查詢和管理大量的資料，方便隨時找到所需的信息。

安裝 MySQL

作為起始的學習先安裝 MySQL 來玩玩看，MySQL 的開源版本稱為 MySQL Community Edition，它可以免費使用，並且提供了一系列功能來管理和操作數據庫。也是比較多公司在用，很適合初學者學習。

需要先安裝 MySQL Server ，才可以建立 Database。跟著指示安裝並設定 root user 的 password 即可。
然後安裝 MySQL Workbench，它是一個 MySQL 的應用介面。
安裝以後 Workbench 以後，在 MySQL Connections 的旁按 + 建立 Database Connection。由於在安裝 Server 後已自動 Start Server，在這裡可以直接做連線。
輸入 Connection Name 和剛在 MySQL Server 設定 root user 的 password，最後點 Test Connection 確認是否可以連接成功。成功就可以點 Close 關閉視窗。
MySQL Connections 會出現剛新增的 Connection，點擊即可進入 Database 的操作頁面。
建立 Schema （Schema 是用於組織和管理 Database 中數據的結構，關係和訪問權限。一個 Database 可以有多個 Schema。）
點擊 new schema 的圖案，輸入 schema name，點擊 apply
會顯示即將執行的 SQL 語法 CREATE SCHEMA 'testing'; 即建立一個名為 testing 的 Schema。
完成後點擊 Schemas 的 tab ，會看到 testing schema。

認識 SELECT SQL 語法

SQL（Structured Query Language) 是一種管理 SQL Database 的標準化查詢語言，用於執行各種 Database 的操作，包括數據查詢 SELECT、數據插入 INSERT、數據更新 UPDATE、數據删除 DELETE 等操作。允許使用者與 Database 交互，從而取得數據以及對數據進行操作和管理。

由於在測試的階段，我們主要會用到 SELECT SQL，以下會主要講述常用的 SELECT SQL 語法，其他的語法在需要的時候可再自行學習。

首先在 MySQL Workbench 先建立學生分數的 Score Table :

把以下這段 SQL 貼在 Query 1 並按執行

# 指定使用哪個 schema，請改為你剛建的 schema name
USE <schema>;

# 建立名為 Score 的 Table
CREATE TABLE `Score` (
  `id` int NOT NULL AUTO_INCREMENT,
  `class` varchar(45) DEFAULT NULL,
  `student` varchar(45) DEFAULT NULL,
  `math` int DEFAULT NULL,
  `chinese` int DEFAULT NULL,
  `english` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);

# 插入資料到 Score Table
INSERT INTO `Score` VALUES (1,'A','Alice',50,60,70),(2,'B','Bob',90,80,100),(3,'B','Alice',20,50,50),(4,'A','Charlie',30,50,30);

重整 Tables 則可看到 Score 資料表，點擊則可看到資料如下：

id	class	student	math	chinese	english
1	A	Alice	50	60	70
2	B	Bob	90	80	100
3	B	Alice	20	50	50
4	A	Charlie	30	50	30

接下來會介紹 SELECT SQL 常用的語法，都可以在 MySQL Workbench 執行來看結果。

SELECT:

SELECT <column1>, <column2>, ... FROM <table>；

為 SELECT 最基本的應用，說明要從哪個 Table 取得哪些 行 (Column) 的資料
```
SELECT class, student FROM Score;
```
Output:

class student

A | Alice |
B | Bob |
B | Alice |
A | Charlie |
WHERE

用作定義條件來選擇資料作 CRUD
```
SELECT class, student FROM Score WHERE name = "Alice";
```
Output:

class math

A | 50 |
B | 20 |

AND / OR

應用在 WHERE 之後，使可以定義多個條件

# 取得名為 Alice 和 班別為 B 的數學分數
SELECT math FROM Score WHERE student = "Alice" AND class = "B";

Output:

math

20 |

# 取得中文分數高於 60 **或是** 英文分數高於 60 的 班別和姓名資料
SELECT class, student FROM Score WHERE chinese >= 60 OR english >= 60;

Output:

class	student

A | Alice |
B | Bob |

IN

應用在 WHERE 之後，用來篩選出滿足指定條件的資料，可避免使用太多的 OR
```
# 取得名為 Bob, Charlie 的數學分數
SELECT student, math FROM Score WHERE student IN ("Bob", "Charlie");
```
Output:

student math

Bob | 90 |
Charlie | 30 |
LIKE

作模糊匹配字符串的查詢
```
# 取得名字最後的部分為 lie 的學生名字
SELECT student FROM Score WHERE student LIKE '%lie';
```
Output:

student

Charlie |

可以使用特定的符號來表示不確定的字符。以下是 LIKE 的常用符號：
1. %：表示任意字符。
  
  例如:
  
  LIKE 'a%' 會匹配以 'a' 開頭的任何字符串。
  
  LIKE '%a' 會匹配以 'a' 結尾的任何字符串。
  
  LIKE '%a%' 會匹配中間字符為 'a' 的任何字符串。
```
SELECT student FROM Score WHERE student LIKE 'B%';
```
  Output:
  
  student
  
  Bob |
2. _：表示任意單個字符。
  
  例如，LIKE '_o%' 會匹配第二個字符為 'o' 的任何三個字符長度的字符串。
```
SELECT student FROM Score WHERE student LIKE '_h%';
```
  Output:
  
  student
  
  Charlie |

ORDER BY

處理取得資料的排序方式，會放在 SQL 的最後

# 取得各人的班別和名稱，分別按 class 和 student 順序排列
# 會先排 class 的順序，再按 name
SELECT class, student FROM Score ORDER BY class, student;

Output:

class	student

A | Alice |
A | Charlie |
B | Alice |
B | Bob |

# 後面加上 DESC 是指以降序排序，需要個別 Column 標記
SELECT class, student FROM Score ORDER BY class DESC, student DESC;

Output:

class	student

B | Bob |
B | Alice |
A | Charlie |
A | Alice |

LIMIT

限制取得資料的數量
```
# 只讀取 2 筆資料
SELECT class, student FROM Score Limit 2;
```
Output:

class student

A | Alice |
B | Bob |

OFFSET

放在 LIMIT 之後，用來忽略多少筆的資料。

# 忽略前 2 筆的資料，只讀取後面的 2 筆
SELECT class, student FROM Score LIMIT 2 OFFSET 2 ;

Output:

class	student

B | Alice |
A | Charlie |

比較常用作取 分頁 (Pagination) 資料

# page_no : 目前的頁數
# records_per_page : 每頁顯示的筆數
# (page_no - 1) * records_per_page ： 計算顯示的頁面資料，要去掉多少筆
SELECT <column> FROM <table> 
OFFSET <(page_no - 1)* records_per_page> LIMIT <records_per_page>;

# 像是每頁只顯示 10 筆，要取得第 6 頁的資料會變成
# OFFSET 50 LIMIT 10

DISTINCT

用於從結果集中去除重覆的行，使得每一行的值都是唯一的
```
SELECT DISTINCT class FROM Score;
```
Output:

class

A |
B |

接下來會介紹針對結果的一些簡單運算

# 計算數學分數的**平均值**
SELECT AVG(math) FROM Score; # Output: 47.5

# 統計結果的數量
# 取得數學高於 60 分的學生數量
SELECT COUNT(name) FROM Score WHERE math >= 60; # Output: 1

# 取得最大值
SELECT MAX(english) FROM Score; # Output: 100

# 取得最小值
SELECT MIN(chinese) FROM Score; # Output: 50

# 取得總和
SELECT SUM(math) FROM Score; # Output: 190

# 把結果四捨五入
# 取得數學的平均值，再四捨五入
SELECT ROUND(AVG(math)) FROM Score; # Output: 48

以上簡單介紹常用的 SQL Selection 語法，其實不管手動測試，還是自動化測試都需要的技能，可以驗證 DB 的資料是否正確。

以下再介紹一些免費學習資源:
Basic MySQL 課程： https://www.udemy.com/course/learn-sqlmysql-database-basics-for-free/
SQL 語法的練習： https://www.sql-practice.com/

提高 SQL Selection 效能

Automation Test 將會快速做大量的測試，會對 Database 作出高頻查詢，若沒有注意 SQL 的效能，是可以導致 DB 伺服器整個崩潰的。因此以下有一些建議的做法可以提高 SQL Selection 的效能：

應用套有 Index 的欄位作條件查詢

(Database Indexing 這個是比較深的課題，有興趣再細看內容。我們簡單知道套用 Index 的欄位作條件查詢會更有效率就好，至於哪些欄位會套用 Index，需要問 Developer 甚至是 DBA (Database Administrator)。
只搜尋所需的欄位，避免用全選 SELECT * 的語法
WHERE 多於一個條件的時候，先放有 Index 且資料性重覆性不高的欄位在前面，因為會按條件的排序作搜尋。

重覆性不高的資料能作比較有意義的篩選，想像假設用性別來篩選，非男即女，能篩掉的資料比較少
盡量使用 Search Argument (搜尋參數)（I.e. where id = 1)

若使用模糊查詢 (I.e. LIKE, <>)，不明確的條件會讓 Index 會失效
不要在條件加入運算步驟