在資料函式庫系統中，資料型別的選擇和使用至關重要，直接影響資料的儲存效率和查詢效能。本文將會詳細說明布林值和二進位資料的儲存方式，並比較不同資料函式庫系統的實作差異。此外，還會探討 SQL 中各種操作和函式的用法，包含邏輯運算子、比較運算子、數學函式、字串函式、日期函式以及處理空值的相關函式，搭配實際案例說明，讓讀者能更有效地運用 SQL 進行資料操作和分析。理解這些核心概念，有助於開發者設計更優良的資料函式庫結構，並撰寫更高效的 SQL 查詢陳述式，提升整體資料函式庫應用程式的效能和穩定性。
資料型別與操作詳解
在資料函式倉管理系統中，正確理解和使用資料型別是至關重要的。本篇文章將探討布林資料、二進位資料以及相關的操作和函式。
布林資料型別
布林資料型別用於儲存邏輯值 TRUE 和 FALSE。不同的資料函式庫系統對布林型別的支援有所不同。
布林型別的支援
- MySQL、PostgreSQL 和 SQLite 支援布林資料型別，可以直接建立布林欄位。
CREATE TABLE my_table (
my_boolean_column BOOLEAN
);
INSERT INTO my_table VALUES (TRUE), (FALSE), (1);
SELECT * FROM my_table;
不支援布林型別的處理方式
- Oracle 和 SQL Server 沒有原生布林型別，但可以透過其他型別來模擬：
- Oracle：使用 CHAR(1) 儲存 ‘T’ 或 ‘F’，或使用 NUMBER(1) 儲存 1 或 0。
- SQL Server：使用 BIT 型別，儲存 1、0 或 NULL。
外部檔案儲存
對於影像、檔案等大型檔案，有兩種常見的儲存方式：
方法一：儲存檔案路徑
- 推薦用於大檔案（>1MB），例如 iPhone 照片通常幾 MB。
- 將檔案儲存在檔案系統中，並在資料函式庫中儲存路徑。
CREATE TABLE my_table (
file_path VARCHAR(100)
);
INSERT INTO my_table VALUES ('/Users/images/img_001.jpg');
方法二：以二進位值儲存
- 適合小檔案，直接儲存在資料函式庫中，方便備份。
- 需要將檔案轉換為二進位格式，並儲存在 BLOB 欄位中。
二進位與十六進位資料
二進位資料是電腦可讀的原始資料，通常以十六進位格式呈現給使用者。
十六進位表示法
- 將二進位資料轉換為 16 進位表示（0-9, A-F）。
- 不同資料函式庫系統對十六進位的表示法支援不同：
- MySQL：支援 X’AF12’、x’AF12’ 和 0xAF12。
- PostgreSQL：支援 X’AF12’ 和 x’AF12’。
- SQL Server 和 SQLite：支援 0xAF12。
二進位資料型別
用於儲存二進位資料的欄位型別，在不同資料函式庫系統中有不同的名稱：
常見二進位型別
- MySQL、Oracle 和 SQLite：使用 BLOB。
- PostgreSQL：使用 BYTEA。
- SQL Server：使用 VARBINARY。
CREATE TABLE my_table (
my_binary_column BLOB
);
INSERT INTO my_table VALUES ('a'), ('aaa'), ('ae$ iou');
SELECT * FROM my_table;
資料函式庫系統對二進位型別的支援比較
| 資料函式庫系統 | 二進位型別 | |
-|
-| | MySQL | BLOB | | Oracle | BLOB, RAW | | PostgreSQL | BYTEA | | SQL Server | VARBINARY | | SQLite | BLOB |
操作與函式詳解
SQL 中的操作和函式用於執行計算、比較和資料轉換。
運算子與函式的區別
- 運算子（如 +, =, OR）用於執行運算或比較。
- 函式（如 UPPER(), YEAR()）接受輸入並傳回結果。
常見操作與函式範例
-- 調整員工薪資
SELECT name, pay_rate + 5 AS new_pay_rate
FROM employees
WHERE UPPER(title) = 'ANALYST'
OR YEAR(start_date) BETWEEN 2016 AND 2018;
本篇文章詳細介紹了布林資料、二進位資料的儲存方式，以及相關的操作和函式。正確理解和應用這些概念，將有助於提升資料函式庫設計和操作的效率與準確性。透過實際範例和不同資料函式庫系統的比較，希望能為讀者提供實用的參考和指引。
SQL 操作與函式詳解
SQL 語言中包含了豐富的操作與函式，用於資料的查詢、運算及處理。本文將重點介紹 SQL 中的操作與函式，包括邏輯運算、比較運算、數學運算及各種實用函式。
邏輯運算
邏輯運算是用於修改條件表示式，結果為 TRUE、FALSE 或 NULL。常見的邏輯運算包括 AND、OR 及 NOT。
SELECT *
FROM employees
WHERE start_date IS NOT NULL
AND (title = 'analyst' OR pay_rate < 25);
在上述範例中，查詢條件結合了多個邏輯運算，需注意使用括號明確運算順序。
| 邏輯運算 | 描述 |
|---|---|
AND | 當兩個條件皆為 TRUE 時傳回 TRUE，否則傳回 FALSE 或 NULL |
OR | 當至少一個條件為 TRUE 時傳回 TRUE，否則傳回 FALSE 或 NULL |
NOT | 對條件結果取反，若條件為 FALSE 則傳回 TRUE，反之亦然 |
邏輯運算範例
假設有一個名為 name 的欄位，使用 NOT 運算的結果如下表所示：
| name | name IN ('Henry', 'Harper') | name NOT IN ('Henry', 'Harper') |
|
|
-|
|
| Henry | TRUE | FALSE |
| Lily | FALSE | TRUE |
| NULL | NULL | NULL |
比較運算
比較運算是用於判斷資料之間的關係，結果同樣為 TRUE、FALSE 或 NULL。比較運算可分為符號運算與關鍵字運算。
符號比較運算
| 運算子號 | 描述 | |
|
|
| = | 等於測試 |
| !=, <> | 不等於測試 |
| < | 小於測試 |
| <= | 小於等於測試 |
| > | 大於測試 |
| >= | 大於等於測試 |
關鍵字比較運算
| 關鍵字 | 描述 | |
-|
|
| BETWEEN | 檢查值是否在指定範圍內 |
| EXISTS | 檢查子查詢是否有結果 |
| IN | 檢查值是否在指定列表中 |
| IS NULL | 檢查值是否為空值 |
| LIKE | 檢查值是否符合簡單模式 |
常見比較運算範例
BETWEEN 範例：查詢年齡介於 35 至 44 之間的資料。
SELECT * FROM my_table WHERE age BETWEEN 35 AND 44;EXISTS 範例：查詢同時存在於員工表與客戶表的資料。
SELECT e.id, e.name FROM employees e WHERE EXISTS (SELECT * FROM customers c WHERE c.email = e.email);
數學與字串函式
SQL 提供了豐富的內建函式，包括聚合函式、數值函式、字串函式及日期函式等。
聚合函式
| 函式名稱 | 功能描述 | |
|
|
| COUNT() | 統計資料筆數 |
| SUM() | 總和計算 |
| AVG() | 平均值計算 |
| MIN() | 最小值查詢 |
| MAX() | 最大值查詢 |
字串函式範例
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- 結果：Hello World
日期函式範例
SELECT CURRENT_DATE AS today_date;
-- 結果：目前日期
Null 相關函式
處理空值（Null）是資料函式庫操作中的常見需求，SQL 提供如 COALESCE() 等函式來處理空值問題。
SELECT COALESCE(column_name, 'default_value') AS result
FROM table_name;
SQL 操作與函式詳解
比對操作
SQL 提供多種比對操作，用於篩選資料表中符合特定條件的資料列。
IN 操作
IN 操作用於檢查某個值是否在給定的值列表中。以下範例查詢特定員工的資料：
SELECT *
FROM employees
WHERE e.id IN (10001, 10032, 10057);
此查詢傳回 id 為 10001、10032 或 10057 的員薪水料。
NOT IN 操作
NOT IN 操作則用於檢查某個值是否不在給定的值列表中。以下範例查詢未使用過休假的員工：
SELECT e.id
FROM employees e
WHERE e.id NOT IN (SELECT v.emp_id FROM vacations v);
注意： 若子查詢中的欄位包含 NULL 值，NOT IN 操作將不會傳回任何資料列。建議在這種情況下使用 NOT EXISTS 操作：
SELECT e.id
FROM employees e
WHERE NOT EXISTS (SELECT * FROM vacations v WHERE v.emp_id = e.id);
IS NULL 操作
IS NULL 或 IS NOT NULL 操作用於檢查某個值是否為 NULL。以下範例查詢沒有長官經理的員工：
SELECT *
FROM employees
WHERE manager IS NULL;
反之，若要查詢有長官經理的員工，可以使用 IS NOT NULL：
SELECT *
FROM employees
WHERE manager IS NOT NULL;
LIKE 操作
LIKE 操作用於比對簡單的模式。百分號 % 是萬用字元，代表零個或多個字元。以下範例查詢包含特定字串的資料列：
SELECT *
FROM my_table
WHERE txt LIKE '%you%';
在 MySQL、SQL Server 和 SQLite 中，模式比對不區分大小寫；而在 Oracle 和 PostgreSQL 中，則區分大小寫。
若要比對以特定字串開頭的資料列，可以使用以下查詢：
SELECT *
FROM my_table
WHERE txt LIKE 'You%';
若要搜尋包含特殊字元 % 的資料列，需要使用 ESCAPE 關鍵字：
SELECT *
FROM my_table
WHERE txt LIKE '%!%%' ESCAPE '!';
此查詢將傳回包含 % 字元的資料列。
數學運算
SQL 中的數學運算包括基本的算術運算，如加法、減法、乘法和除法。以下範例計算員工的每週薪水：
SELECT salary / 52 AS weekly_pay
FROM my_table;
常見的數學運算子號包括：
| 運算子號 | 描述 |
|---|---|
+ | 加法 |
- | 減法 |
* | 乘法 |
/ | 除法 |
% (或 MOD 在 Oracle 中) | 取餘數 |
在 PostgreSQL、SQL Server 和 SQLite 中，整數除法會傳回整數結果。若要得到小數結果，可以將其中一個運算元轉換為小數或浮點數：
SELECT 15 / 2.0; -- 傳回 7.5
或者使用 CAST 函式進行型別轉換：
-- PostgreSQL 和 SQL Server
SELECT CAST(15 AS DECIMAL) / CAST(2 AS DECIMAL);
-- SQLite
SELECT CAST(15 AS REAL) / CAST(2 AS REAL);
聚合函式
聚合函式對多行資料進行計算並傳回單一值。常見的聚合函式包括：
| 函式 | 描述 |
|---|---|
COUNT() | 計算值的數量 |
SUM() | 計算某欄位的總和 |
AVG() | 計算某欄位的平均值 |
MIN() | 取得某欄位的最小值 |
MAX() | 取得某欄位的最大值 |
聚合函式通常用於統計分析，例如計算資料表的總行數或平均值：
SELECT COUNT(*) AS total_rows, AVG(age) AS average_age
FROM my_table;
在包含聚合函式和非聚合欄位的查詢中，非聚合欄位必須出現在 GROUP BY 子句中，以確保結果的正確性：
SELECT region, MIN(age), MAX(age)
FROM my_table
GROUP BY region
HAVING MIN(age) < 18;
不同的資料函式庫系統對於未正確使用 GROUP BY 的查詢處理方式不同，有些會傳回錯誤，有些則可能傳回不準確的結果。因此，建議始終檢查查詢結果以確保其正確性。
數值函式與字串函式在SQL中的應用
在SQL查詢中，數值函式和字串函式是兩種非常重要的工具，能夠幫助開發者有效地處理和操作資料函式庫中的資料。本文將探討數值函式和字串函式的使用方法，並提供實際的範例來說明其在不同資料函式倉管理系統（RDBMS）中的應用。
數值函式
數值函式主要用於對數字資料進行各種數學運算。以下是一些常見的數值函式：
1. 基本數學運算
ABS: 取得數字的絕對值。
SELECT ABS(-5);結果：5
SIGN: 判斷數字的正負號，回傳-1（負數）、0（零）或1（正數）。
SELECT SIGN(-5);結果：-1
2. 指數與對數運算
POWER: 將一個數字提升到指定的冪次。
SELECT POWER(5, 2);結果：25
SQRT: 計算數字的平方根。
SELECT SQRT(25);結果：5
EXP: 計算e的指定次方。
SELECT EXP(2);結果：7.38905609893065
LOG: 計算指定底數的對數。不同RDBMS中，LOG函式的用法可能有所不同。
-- MySQL, PostgreSQL SELECT LOG(2, 10); -- SQL Server SELECT LOG(10, 2);
3. 隨機數生成
不同RDBMS生成隨機數的方法有所不同：
MySQL, SQL Server: 使用
RAND()函式。SELECT RAND();Oracle: 使用
DBMS_RANDOM.VALUE或DBMS_RANDOM.RANDOM。SELECT DBMS_RANDOM.VALUE FROM dual;PostgreSQL: 使用
RANDOM()函式。SELECT RANDOM();SQLite: 使用
RANDOM()函式，但結果範圍不同。SELECT RANDOM();
#### 內容解密：
上述範例展示瞭如何在不同資料函式庫系統中生成隨機數。每個系統都有其特定的函式來實作這一功能，開發者需要根據所使用的RDBMS選擇適當的方法。
4. 四捨五入與截斷
CEIL/CEILING: 將數字無條件進位到最接近的整數。
SELECT CEIL(98.7654);結果：99
FLOOR: 將數字無條件捨去到最接近的整數。
SELECT FLOOR(98.7654);結果：98
ROUND: 對數字進行四捨五入，可以指定小數位數。
SELECT ROUND(98.7654, 2);結果：98.77
TRUNC/TRUNCATE: 將數字截斷到指定的小數位數。
SELECT TRUNC(98.7654, 2);結果：98.76
#### 內容解密：
這些函式在處理財務資料或需要精確控制小數位數的場合非常有用。開發者可以根據需求選擇合適的函式來進行數字處理。
資料型別轉換
在比較不同資料型別的資料時，可能需要進行型別轉換。CAST函式是SQL中用於轉換資料型別的標準方法。
SELECT *
FROM my_table
WHERE CAST(str_col AS DECIMAL) > 3;
#### 內容解密：
此範例展示瞭如何將字串欄位轉換為十進位制數字，以便進行比較。正確的資料型別轉換能夠避免查詢錯誤，並確保結果的正確性。
字串函式
字串函式主要用於處理文字資料。以下是一些常見的字串函式：
#### 字串長度計算：
使用LENGTH函式（在SQL Server中為LEN）來取得字串的長度。
SELECT LENGTH(name)
FROM my_table;
#### 篩選特定長度的字串：
SELECT *
FROM my_table
WHERE LENGTH(name) < 10;
#### 使用Plantuml圖表展示字串處理流程：
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title SQL資料型別與操作函式詳解
package "資料庫架構" {
package "應用層" {
component [連線池] as pool
component [ORM 框架] as orm
}
package "資料庫引擎" {
component [查詢解析器] as parser
component [優化器] as optimizer
component [執行引擎] as executor
}
package "儲存層" {
database [主資料庫] as master
database [讀取副本] as replica
database [快取層] as cache
}
}
pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中
master --> replica : 資料同步
note right of cache
Redis/Memcached
減少資料庫負載
end note
@enduml此圖示展示了根據字串長度篩選資料的流程。
