資料庫開發

SQL

定義

SQL（Structured Query Language）是結構化查詢語言，用於管理和操作關聯式數據庫。它是數據庫管理系統中使用最廣泛的語言之一。

主要功能

數據查詢：從數據表中檢索所需的數據。
數據插入：向數據表中新增資料。
數據更新：修改數據表中的現有資料。
數據刪除：從數據表中移除不需要的資料。
數據表管理：創建、修改和刪除表結構。

基本語法

-- 查詢數據
SELECT * FROM 表名 WHERE 條件;

-- 插入數據
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

-- 更新數據
UPDATE 表名 SET 列1 = 值1 WHERE 條件;

-- 刪除數據
DELETE FROM 表名 WHERE 條件;

-- 創建表
CREATE TABLE 表名 (
    列名1 數據類型,
    列名2 數據類型
);

常見數據類型

INT：整數類型。
VARCHAR(n)：變長字元串，最多 n 個字元。
DATE：日期類型。
FLOAT：浮點數類型。
BOOLEAN：布林值（真或假）。

優點

易於學習和使用，語法直觀。
提供高效的數據處理能力。
支持複雜的數據查詢和操作。
廣泛應用於各種行業和場景。

MySQL

關聯式資料庫

MySQL 是一種流行的開源關聯式資料庫管理系統 (RDBMS)，使用 SQL 作為查詢語言，適合中小型到大型應用程式。

特性

高效能和穩定性
支援多使用者和多線程
跨平台運行
豐富的社群和商業支援

使用範例

mysql -u root -p
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

適用場景

動態網站和線上商務平台
大型資料儲存與管理
需要高效能和可靠性的應用程式

SQLite

輕量化資料庫

SQLite 是一種嵌入式資料庫，無需獨立伺服器進行管理，適合用於輕量級應用程式。

特性

單一檔案儲存所有資料
支援 ACID 交易處理
跨平台支援
開源且免費

使用範例

sqlite3 example.db
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO users (name, age) VALUES ('Alice', 30);
SELECT * FROM users;

適用場景

嵌入式系統與行動應用程式
小型網站或工具的資料儲存
快速開發與測試

用 Name 代替 ID 欄位

適用條件

資料表的 name 欄位值是唯一的。
name 欄位的長度適中，避免過長影響效能。
應用程式的操作主要以名稱為基準，而非數字型 ID。

優勢

使用名稱更直觀，易於閱讀和管理。
簡化查詢語句，例如直接以名稱搜尋。
避免額外建立數字型 ID 欄位的需求。

潛在問題

名稱若無法保持唯一性，可能導致資料衝突。
過長的名稱可能降低索引查詢效能。
名稱變更後，相關關聯可能需要同步更新，增加複雜度。

適用範例

如設定檔的類型資料表：

CREATE TABLE config_types (
  name VARCHAR(50) PRIMARY KEY,
  description TEXT
);

INSERT INTO config_types (name, description) VALUES ('general', 'General settings');
SELECT * FROM config_types WHERE name = 'general';

關聯式資料庫設計繼承物件

資料庫結構設計

animal 表格

此表格將存放所有「動物」的通用屬性。

欄位名稱	資料型態	說明
id	INT	動物的唯一識別碼
species	VARCHAR(50)	動物的種類
age	INT	動物的年齡

cat 表格

此表格將繼承 animal 表格的 id，並儲存「貓」的特有屬性。

欄位名稱	資料型態	說明
id	INT	對應到 animal 表格的 id
breed	VARCHAR(50)	貓的品種
favorite_food	VARCHAR(50)	貓的喜愛食物

SQL 建立表格指令

CREATE TABLE animal (
    id INT PRIMARY KEY AUTO_INCREMENT,
    species VARCHAR(50) NOT NULL,
    age INT NOT NULL
);

CREATE TABLE cat (
    id INT PRIMARY KEY,
    breed VARCHAR(50),
    favorite_food VARCHAR(50),
    FOREIGN KEY (id) REFERENCES animal(id)
);

插入資料的範例

INSERT INTO animal (species, age) VALUES ('Cat', 3);

INSERT INTO cat (id, breed, favorite_food) VALUES (1, 'Siamese', 'Fish');

HTML 表格展示範例

動物資料

動物ID	種類	年齡
1	Cat	3

貓的特有資料

動物ID	品種	喜愛食物
1	Siamese	Fish

說明

在此範例中，animal 表格儲存了所有動物的共同屬性，而 cat 表格則儲存貓的特有屬性。cat 表格中的 id 是參照 animal 表格的 id，代表這是一種繼承關係。

查詢所有動物的資料

SELECT * FROM animal;

查詢所有貓的資料

這個查詢將返回所有貓的完整資料，包括繼承自 animal 表格的通用屬性。

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id;

查詢特定動物的資料（例如特定 ID）

SELECT * FROM animal WHERE id = 1;

查詢特定貓的資料（例如特定品種）

SELECT animal.id, animal.species, animal.age, cat.breed, cat.favorite_food
FROM animal
JOIN cat ON animal.id = cat.id
WHERE cat.breed = 'Siamese';

說明

在這些查詢範例中，我們使用 JOIN 將 animal 表格和 cat 表格結合起來，以取得貓的完整資料。這種方法確保查詢結果包含繼承屬性與特有屬性。

FOREIGN KEY

用途

FOREIGN KEY（外鍵）用於建立兩個資料表之間的關聯性，確保資料的參考完整性。例如，一個資料表的欄位值必須參考另一個資料表中的主鍵或唯一值。

語法

CREATE TABLE 子資料表 (
  欄位名稱 資料類型,
  FOREIGN KEY (外鍵欄位) REFERENCES 父資料表(主鍵欄位)
);

範例

建立一對多的關聯，例如訂單與客戶：

-- 建立父資料表 (customers)
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
);

-- 建立子資料表 (orders) 並設定外鍵
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

注意事項

父資料表中的參考欄位必須是 PRIMARY KEY 或 UNIQUE。
插入或更新資料時，必須遵循外鍵約束。
刪除父資料表的資料時，外鍵約束可能會導致錯誤，除非設定 ON DELETE 行為。

進階用法

可以透過 ON DELETE 和 ON UPDATE 指定外鍵行為：

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);

行為選項

CASCADE：同步刪除或更新子資料表中的相關資料。
SET NULL：將子資料表中的外鍵欄位設為 NULL。
RESTRICT：阻止刪除或更新，預設行為。
NO ACTION：與 RESTRICT 類似，延遲約束檢查。

在 CREATE TABLE 中加入備註

使用 COMMENT 設定欄位備註

在 MySQL 中，可以使用 COMMENT 來為欄位添加備註。

CREATE TABLE users (
  id INT PRIMARY KEY COMMENT '使用者唯一識別碼',
  name VARCHAR(50) COMMENT '使用者姓名',
  age INT COMMENT '使用者年齡'
);

使用 COMMENT 設定資料表備註

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
) COMMENT = '使用者資料表';

查看欄位備註

可透過以下語法查詢欄位的備註：

SHOW FULL COLUMNS FROM users;

修改欄位備註

ALTER TABLE users MODIFY COLUMN name VARCHAR(50) COMMENT '修改後的備註';

適用範圍

MySQL 支援 COMMENT，但 SQLite 不支援。
其他資料庫系統（如 PostgreSQL）通常使用 COMMENT ON 指令。

PostgreSQL 設定備註

COMMENT ON COLUMN users.name IS '使用者姓名';

SQL 欄位名稱使用到保留字

在 SQL 查詢中，當欄位名稱與資料庫系統的保留字（Keyword）衝突時，會導致語法錯誤。處理這個問題需要從預防和解決兩個面向進行全面考量。

1. 根本解決方案：避免使用保留字 (資料庫設計)

這是最推薦的做法，旨在從源頭消除問題。

重新命名：將欄位名稱更改為不會產生衝突的名稱。例如，將 interval 改為 time_interval，將 rank 改為 user_rank。
查閱官方保留字列表：在設計階段查閱目標資料庫（如 MySQL、PostgreSQL、SQL Server）的官方文件，以確保選擇的名稱不在保留字列表中。

2. 技術解決方案：使用引號包裹識別符號 (程式碼實作)

當無法避免使用保留字時（例如：處理現有資料庫結構），必須使用適當的引號將欄位名稱包裹起來，以明確告知 SQL 引擎這是一個識別符號而非 SQL 命令。

資料庫系統	引號符號	範例
MySQL / MariaDB	反引號 (`)	SELECT `interval`, `time` FROM table;
PostgreSQL / Oracle	雙引號 (`"`)	`SELECT "interval", "time" FROM table;`
SQL Server	方括號 (`[]`)	`SELECT [interval], [time] FROM table;`

3. 程式碼自動化處理 (動態生成 SQL)

在 Python 等程式語言中，若需要動態生成 SQL 語句，應實作一個函式來自動執行引號包裹，確保生成的語句安全且正確。

定義保留字集合：建立一個集合包含所有需預防的保留字。
欄位清單處理：編寫一個函式，用於檢查欄位名稱是否為保留字。如果是，則使用目標資料庫的反引號包裹該名稱。
WHERE 條件處理：處理 WHERE 子句時，必須使用正規表達式（搭配單詞邊界 \b）來精確匹配欄位名稱，避免錯誤地替換字串值或較長的名稱。

4. ORM 框架的優勢

如果使用 ORM（例如 SQLAlchemy 或 Django ORM），框架會自動處理保留字和不同資料庫之間的引號差異，將底層的 SQL 輸出抽象化，這大大簡化了開發工作並提高了程式碼的穩定性。

SQL DATETIME 比較

使用 TIMESTAMPDIFF

計算兩個 DATETIME 欄位的時間差（單位：秒）。

SELECT * FROM table_name
WHERE TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2) < 5;

使用 ABS(TIMESTAMPDIFF)

確保時間差為絕對值，避免順序影響。

SELECT * FROM table_name
WHERE ABS(TIMESTAMPDIFF(SECOND, datetime_column1, datetime_column2)) < 5;

使用 DATEDIFF（僅適用於天數比較）

若只需比較是否為同一天，可以使用 DATEDIFF。

SELECT * FROM table_name
WHERE DATEDIFF(datetime_column1, datetime_column2) = 0;

使用 TIMESTAMP 直接相減

適用於支援時間戳記運算的資料庫，如 MySQL。

SELECT * FROM table_name
WHERE ABS(UNIX_TIMESTAMP(datetime_column1) - UNIX_TIMESTAMP(datetime_column2)) < 5;

MySQL 處理毫秒級時間戳

您提供的數值 1763251200000 是一個標準的 **毫秒級時間戳**（Unix Epoch Time in milliseconds）。

MySQL 內建的函式（例如 FROM_UNIXTIME）預設處理的是 **秒級時間戳**。因此，您需要在使用前將毫秒級時間戳轉換為秒級。

修正方法：除以 1000

將您的毫秒值除以 $1000$ 即可得到 MySQL 函式所需的秒級時間戳：

1763251200000 / 1000 = 1763251200

1. 轉換為 DATETIME 格式

使用 FROM_UNIXTIME() 函式將秒級時間戳轉換為標準的 MySQL DATETIME 格式：

SELECT FROM_UNIXTIME(1763251200000 / 1000);

如果您的 MySQL 版本支援毫秒，並且您希望在結果中保留毫秒精度，您可以使用第二個參數：

SELECT FROM_UNIXTIME(1763251200000 / 1000, '%Y-%m-%d %H:%i:%s.%f');

其中：

%Y-%m-%d %H:%i:%s 是標準的日期時間格式。
%f 捕獲微秒（Microseconds），因此您需要將剩餘的毫秒作為小數部分傳入。

2. 轉換為 UNIX_TIMESTAMP (秒級)

如果您的目的是將毫秒值儲存在一個整數（例如 INT 或 BIGINT）欄位中作為秒級時間戳，則只需要簡單的除法運算：

SELECT 1763251200000 / 1000;

或者，如果您的資料儲存在表格欄位中（例如欄位名為 timestamp_ms）：

SELECT timestamp_ms / 1000 AS unix_timestamp_s FROM your_table;

3. 儲存到 DATETIME/TIMESTAMP 欄位

當您將此值插入到具有 DATETIME 或 TIMESTAMP 型別的欄位時，您需要在 VALUES 部分執行轉換：

INSERT INTO your_table (datetime_column) 
VALUES (FROM_UNIXTIME(1763251200000 / 1000));

4. 處理 UTC 時區

FROM_UNIXTIME() 函式會將 UTC 時間戳轉換為您 MySQL 伺服器配置的時區。如果您的時間戳是基於 UTC，並且您希望結果也是 UTC，您需要確保您的伺服器或連線時區設置正確，或使用 CONVERT_TZ 函式進行明確的時區處理。

格式化 FLOAT

使用 FORMAT()

在 MySQL 中，可使用 FORMAT() 來格式化浮點數。

SELECT FORMAT(123.4567, 2); -- 結果: '123.46'

使用 ROUND()

ROUND() 用於四捨五入至固定小數位數。

SELECT ROUND(123.4567, 2); -- 結果: 123.46

使用 CAST() 或 CONVERT()

將 FLOAT 轉換為 DECIMAL 以保持固定小數位數。

SELECT CAST(123.4567 AS DECIMAL(10,2)); -- 結果: 123.46
SELECT CONVERT(123.4567, DECIMAL(10,2)); -- 結果: 123.46

應用在資料表欄位

SELECT id, FORMAT(price, 2) AS formatted_price FROM products;

設定欄位格式

建立資料表時可直接設定小數點位數。

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) -- 兩位小數
);

查詢欄位總計

使用 MAX()

取得欄位的最大值。

SELECT MAX(price) AS max_price FROM products;

使用 MIN()

取得欄位的最小值。

SELECT MIN(price) AS min_price FROM products;

使用 AVG()

計算欄位的平均值。

SELECT AVG(price) AS avg_price FROM products;

同時查詢 MAX、MIN、AVG

SELECT 
  MAX(price) AS max_price, 
  MIN(price) AS min_price, 
  AVG(price) AS avg_price
FROM products;

依類別分組計算

SELECT category, 
       MAX(price) AS max_price, 
       MIN(price) AS min_price, 
       AVG(price) AS avg_price
FROM products
GROUP BY category;

使用子查詢找最大值

對另一個查詢結果求最大值。

SELECT MAX(price) FROM (SELECT price FROM products WHERE category = 'electronics') AS subquery;

使用 ORDER BY + LIMIT

排序後取最大值。

SELECT price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 1;

使用 WITH (Common Table Expression, CTE)

WITH filtered_products AS (
  SELECT price FROM products WHERE category = 'electronics'
)
SELECT MAX(price) FROM filtered_products;

幾何平均數公式

幾何平均數（Geometric Mean）計算公式：

GM = (x1 * x2 * ... * xn)^(1/n)

使用 EXP() 和 LOG()

在 SQL 中，可以透過對數運算來計算幾何平均數。

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

使用 POWER()

使用 POWER() 計算 n 次方根：

SELECT POWER(EXP(SUM(LOG(price))), 1 / COUNT(price)) AS geometric_mean 
FROM products WHERE price > 0;

注意事項

價格（或數值）不可為負數或零，否則 LOG() 會導致錯誤。
若資料中包含零，可先過濾掉：

SELECT EXP(AVG(LOG(price))) AS geometric_mean FROM products WHERE price > 0;

SQL 計算欄位的標準差

使用 STDDEV() 計算標準差

在 MySQL / MariaDB 中，可以使用 STDDEV() 來計算標準差。

範例：

SELECT STDDEV(salary) AS salary_stddev FROM employees;

區分母標準差與樣本標準差

SQL 提供兩種標準差計算方式：

STDDEV_POP() - 計算母體標準差（Population Standard Deviation）
STDDEV_SAMP() - 計算樣本標準差（Sample Standard Deviation）

範例：

SELECT 
    STDDEV_POP(salary) AS population_stddev, 
    STDDEV_SAMP(salary) AS sample_stddev
FROM employees;

手動計算標準差

如果 SQL 版本不支援 STDDEV()，可以使用以下公式：

SELECT SQRT(
    SUM(POW(salary - (SELECT AVG(salary) FROM employees), 2)) / COUNT(salary)
) AS salary_stddev
FROM employees;

結論

使用 STDDEV() 可以直接計算標準差。
可用 STDDEV_POP() 計算母體標準差，STDDEV_SAMP() 計算樣本標準差。
若不支援內建函數，可用 SQL 公式手動計算。

SQL WHERE 子句

SQL 的 WHERE 子句用於從表格中提取那些符合指定條件的記錄（列/行）。它是資料操作語言（DML）中最基礎也最重要的部分之一，用於精確篩選所需的資料。

角色與用途

行級過濾： WHERE 子句在資料庫讀取表格時，對每一行（Row）進行評估。只有當某一行的資料滿足 WHERE 後面的條件時，該行才會被包含在結果集中。
應用範圍： 它可以與 SELECT, UPDATE, DELETE 等 SQL 命令一起使用。

基本語法

WHERE 子句通常緊跟在 FROM 子句之後：

SELECT column_name(s)
FROM table_name
WHERE condition;

或用於修改或刪除資料時：

UPDATE table_name
SET column1 = value1
WHERE condition;

常用的比較和邏輯運算子

WHERE 子句中使用運算子來建立條件，最常見的有：

運算子類型	運算子	描述
比較運算子	`=`	等於
比較運算子	`>`, `<`, `>=`, `<=`	大於、小於、大於等於、小於等於
比較運算子	`<>` 或 `!=`	不等於
邏輯運算子	`AND`	同時滿足多個條件
邏輯運算子	`OR`	滿足其中任一條件
邏輯運算子	`NOT`	條件不成立
特殊運算子	`BETWEEN`	在某一範圍內（包含邊界）
特殊運算子	`LIKE`	模糊匹配字串模式（搭配 `%` 或 `_`）
特殊運算子	`IN`	值在清單中的任一項
特殊運算子	`IS NULL` / `IS NOT NULL`	欄位值是否為 NULL

使用範例

假設有一個名為 Employees 的表格，包含 EmployeeID, LastName（姓氏）, Salary（薪水）和 Department（部門）。

範例 1：單一條件

找出所有部門為 'Sales' 的員工：

SELECT EmployeeID, LastName
FROM Employees
WHERE Department = 'Sales';

範例 2：數值比較與邏輯 AND

找出薪水大於等於 50000 且部門不是 'HR' 的員工：

SELECT *
FROM Employees
WHERE Salary >= 50000 AND Department != 'HR';

範例 3：範圍 BETWEEN

找出薪水在 60000 到 80000 之間（包含 60000 和 80000）的員工：

SELECT *
FROM Employees
WHERE Salary BETWEEN 60000 AND 80000;

範例 4：清單 IN

找出部門為 'Marketing' 或 'Finance' 的員工：

SELECT *
FROM Employees
WHERE Department IN ('Marketing', 'Finance');

這等同於使用 WHERE Department = 'Marketing' OR Department = 'Finance'。

SQL GROUP BY 子句

SQL 的 GROUP BY 子句用於將 SELECT 陳述式中，具有相同值（或多個欄位的值組合）的行分組成彙總列。它經常與 SQL 的彙總函數一起使用，以便為每個群組計算出一個摘要值。

角色與用途

分組資料： GROUP BY 的核心功能是將表格中具有相同值的多個單一行，合併成一個單獨的群組（Group）。
產生摘要： 通過與 COUNT()、SUM()、AVG()、MAX() 或 MIN() 等彙總函數結合，您可以計算出每個群組的統計摘要，而不是整個表格的摘要。

基本語法

GROUP BY 子句必須在 WHERE 子句之後，但在 HAVING 和 ORDER BY 子句之前。

SELECT column_name(s), aggregate_function(column_to_summarize)
FROM table_name
WHERE condition_on_rows     -- (可選) 在分組前過濾單行
GROUP BY column_name(s)     -- 指定用來分組的欄位
HAVING condition_on_groups  -- (可選) 在分組後過濾群組
ORDER BY column_name(s);

重要規則： 任何出現在 SELECT 列表中，但沒有被彙總函數包圍的欄位，都必須包含在 GROUP BY 子句中。

使用範例

假設有一個名為 Products 的表格，包含 Category（產品類別）和 Price（價格）。

需求：找出每個產品類別的平均價格和產品數量。

SELECT Category, AVG(Price) AS AveragePrice, COUNT(ProductID) AS TotalProducts
FROM Products
GROUP BY Category;

範例說明

GROUP BY Category：指令資料庫將所有具有相同 Category 值的行分組。
AVG(Price)：計算每個分組（每個類別）中所有產品價格的平均值。
COUNT(ProductID)：計算每個分組（每個類別）中的產品總數量。

多欄位分組

您可以根據多個欄位進行分組。只有當所有指定的欄位的值都相同時，這些行才會被分在同一個群組中。

需求：找出每個產品類別（Category）和供應商（Supplier）組合的總銷售額。

SELECT Category, Supplier, SUM(SalesAmount) AS TotalSales
FROM SalesRecords
GROUP BY Category, Supplier
ORDER BY Category, Supplier;

這將產生一個結果，其中每一行代表一個獨特的「類別 + 供應商」組合的總銷售額。

常用的彙總函數

函數	功能
`COUNT()`	計算群組中的行數。
`SUM()`	計算群組中數值欄位的總和。
`AVG()`	計算群組中數值欄位的平均值。
`MAX()`	找出群組中欄位的最大值。
`MIN()`	找出群組中欄位的最小值。

SQL HAVING 子句的用法

在 SQL 中，HAVING 子句是用來過濾群組（Group）的結果，它通常與 GROUP BY 子句一起使用。

雖然 WHERE 子句用於過濾單個列的行（Rows），但在需要基於群組的摘要值（例如 COUNT(), SUM(), AVG() 等彙總函數的結果）來過濾群組時，就必須使用 HAVING 子句。

HAVING 與 WHERE 的區別

特徵	WHERE 子句	HAVING 子句
執行時機	在資料被分組（GROUP BY）之前，過濾原始的行。	在資料被分組（GROUP BY）之後，過濾彙總的群組。
可使用的條件	不能直接使用彙總函數（如 COUNT、SUM、AVG）。	必須使用彙總函數來設定過濾條件。
應用對象	單一的列值。	群組（Group）的結果。

基本語法

SELECT column_name(s), aggregate_function(column_name)
FROM table_name
WHERE condition_on_rows  -- (選擇性) 過濾單行
GROUP BY column_name(s)
HAVING condition_on_groups -- (必須) 過濾群組
ORDER BY column_name(s);

使用範例

假設有一個名為 Orders 的表格，包含 CustomerID（客戶 ID）和 TotalAmount（訂單總金額）。我們想找出所有平均訂單金額超過 500 的客戶。

SQL 查詢

SELECT CustomerID, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING AVG(TotalAmount) > 500;

範例說明

GROUP BY CustomerID：將所有訂單按客戶 ID 分組。
AVG(TotalAmount)：計算每個客戶的平均訂單金額。
HAVING AVG(TotalAmount) > 500：這是過濾條件。它告訴 SQL **只顯示那些平均訂單金額（即 AVG(TotalAmount) 的結果）大於 500 的群組（客戶）**。

複合條件範例

假設我們想找出所有訂單總數**大於等於 3** 並且平均訂單金額**小於 1000** 的客戶。

SQL 查詢

SELECT CustomerID, COUNT(OrderID) AS TotalOrders, AVG(TotalAmount) AS AverageOrder
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) >= 3 AND AVG(TotalAmount) < 1000;

範例說明

HAVING COUNT(OrderID) >= 3：過濾掉訂單總數少於 3 的客戶群組。
AND AVG(TotalAmount) < 1000：同時過濾掉平均訂單金額大於或等於 1000 的客戶群組。

SQL 查詢中是否能在別名（Alias）中引用另一個計算欄位

問題描述

在 SELECT 查詢中，如果定義了 expr1 AS field1，能否在 expr2 中使用 field1？

答案：不行，因為 SQL 查詢的執行順序

SQL 的執行順序決定了別名不能在同一個 SELECT 內部被再次引用：

SELECT price * 1.1 AS new_price, new_price + 10 AS final_price FROM products; -- 錯誤

錯誤訊息：

Unknown column 'new_price' in 'field list'

解決方法

方法 1：使用子查詢

可以在子查詢中先計算 new_price，然後在外部查詢中引用：

SELECT new_price, new_price + 10 AS final_price
FROM (SELECT price * 1.1 AS new_price FROM products) AS subquery;

方法 2：使用 CTE（WITH 語句）

若 SQL 支援 Common Table Expressions（CTE），可用 WITH 來簡化：

WITH cte AS (
    SELECT price * 1.1 AS new_price FROM products
)
SELECT new_price, new_price + 10 AS final_price FROM cte;

方法 3：重複表達式

如果只是簡單運算，可以直接重複計算（但不推薦，因為可讀性較差）：

SELECT price * 1.1 AS new_price, price * 1.1 + 10 AS final_price FROM products;

結論

SQL 不能在同一個 SELECT 查詢內引用別名。
可使用子查詢或 CTE（WITH）來解決。
對於簡單運算，可以直接重複計算。

JOIN

JOIN 簡介

JOIN 用於合併多個資料表的相關資料，根據某個欄位（通常是外鍵）建立關聯。

INNER JOIN

只返回兩個資料表中符合條件的資料。

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN

返回左表的所有資料，若右表沒有對應資料，則顯示 NULL。

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

RIGHT JOIN

返回右表的所有資料，若左表沒有對應資料，則顯示 NULL。

SELECT customers.name, orders.order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

FULL JOIN

返回左右兩表的所有資料，若沒有匹配則顯示 NULL。

MySQL 不支援 FULL JOIN，可用 LEFT JOIN 和 RIGHT JOIN 組合模擬。

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

CROSS JOIN

返回兩個表的所有可能組合（笛卡兒積）。

SELECT customers.name, products.product_name
FROM customers
CROSS JOIN products;

SELF JOIN

用於同一個表內部的關聯，如員工的上級關係。

SELECT A.name AS employee, B.name AS manager
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

在執行 .sql 時，包含其他 .sql 文件

使用 SOURCE 或 \i 指令

MySQL 或 MariaDB

可以在 MySQL 或 MariaDB 的腳本中使用 `SOURCE` 指令包含其他 `.sql` 文件：

-- abc.sql
SOURCE other_file.sql;
SOURCE another_file.sql;

PostgreSQL (psql)

在 PostgreSQL 中，可以使用 `\i` 指令包含其他 `.sql` 文件：

-- abc.sql
\i other_file.sql
\i another_file.sql

使用批次腳本執行

當 SQL 客戶端不支援直接包含文件時，可以使用批次腳本來依序執行多個 `.sql` 文件。

Linux Shell 範例

#!/bin/bash
mysql -u user -p database_name < abc.sql
mysql -u user -p database_name < other_file.sql

進階方式：預處理 SQL 文件

如果 SQL 客戶端無法直接支援文件包含，可以先將主 SQL 文件與參考的 `.sql` 文件合併，然後再執行。

使用 Shell Script 合併文件

cat abc.sql other_file.sql another_file.sql > combined.sql
mysql -u user -p database_name < combined.sql

注意事項

1. **執行順序**：確保所包含的文件按正確順序執行，避免資料表或函數的相依性問題。 2. **資料庫特定語法**：不同資料庫的指令語法可能不同，請參閱相應的文件。 3. **文件路徑**：正確使用絕對或相對路徑來引用 `.sql` 文件。 4. **存取權限**：確保 SQL 客戶端有權限讀取被包含的文件。

總結

通過上述方法，可以將 SQL 腳本模組化，方便管理和重複使用。

包含其他 .sql 文件時傳遞參數

MySQL 和 MariaDB

MySQL 和 MariaDB 不直接支援在 `SOURCE` 指令中傳遞參數，但可以使用變數配合包含的 .sql 文件。以下是方法：

使用變數傳遞參數

1. 在主 SQL 文件中設置變數：

   SET @param1 = 'value1';
   SOURCE other_file.sql;

2. 在 `other_file.sql` 中引用變數：

   SELECT * FROM table WHERE column = @param1;

PostgreSQL (psql)

PostgreSQL 支援透過 `\set` 指令設置變數並傳遞到其他文件中：

使用變數傳遞參數

1. 在主 SQL 文件中設置變數：

   \set param1 'value1'
   \i other_file.sql

2. 在 `other_file.sql` 中使用變數：

   SELECT * FROM table WHERE column = :'param1';

使用命令列工具傳遞參數

透過命令列執行時傳遞參數到 SQL 文件是一種常見方法。

MySQL 命令列範例

1. 使用 `sed` 或其他工具在執行時替換參數：

   sed "s/{param1}/value1/g" abc.sql | mysql -u user -p database_name

2. 在 SQL 文件中使用佔位符 `{param1}`，由命令列工具替換。

PostgreSQL 命令列範例

1. 在 `psql` 命令中直接設置參數：

   psql -d database_name -v param1=value1 -f abc.sql

2. 在 SQL 文件中使用 `:'param1'` 表示變數。

使用程式碼生成 SQL

透過程式語言（如 Python 或 Bash）動態生成 SQL 是另一種解決方案： 1. 在程式中動態構建包含參數的 SQL 文件。 2. 執行生成的 SQL 文件。

注意事項

1. **安全性**：避免直接將用戶輸入的參數嵌入 SQL，應考慮 SQL 注入風險。 2. **環境變數**：某些工具支援使用環境變數作為參數傳遞。

總結

雖然 SQL 文件的參數傳遞不總是直接支援，但可以透過變數、命令列工具或程式語言來實現，靈活性取決於工具和數據庫的特性。

儲存程序

儲存程序簡介(預存常式)

Stored Procedure（預存常式或儲存程序）是一組預先編譯並存儲在資料庫中的 SQL 語句，可以透過呼叫執行，提高效率並減少重複代碼。

建立儲存程序

DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
  SELECT * FROM products;
END //
DELIMITER ;

呼叫儲存程序

CALL GetAllProducts();

帶參數的儲存程序

輸入參數

查詢特定類別的商品：

DELIMITER //
CREATE PROCEDURE GetProductsByCategory(IN category_name VARCHAR(50))
BEGIN
  SELECT * FROM products WHERE category = category_name;
END //
DELIMITER ;

呼叫：

CALL GetProductsByCategory('electronics');

輸出參數

計算某個類別的商品總數：

DELIMITER //
CREATE PROCEDURE GetProductCountByCategory(IN category_name VARCHAR(50), OUT total_count INT)
BEGIN
  SELECT COUNT(*) INTO total_count FROM products WHERE category = category_name;
END //
DELIMITER ;

呼叫：

CALL GetProductCountByCategory('electronics', @count);
SELECT @count;

儲存程序內使用變數

DELIMITER //
CREATE PROCEDURE CalculateTotalRevenue()
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT SUM(price) INTO total FROM sales;
  SELECT total AS total_revenue;
END //
DELIMITER ;

呼叫：

CALL CalculateTotalRevenue();

儲存程序內使用條件控制

IF 條件

DELIMITER //
CREATE PROCEDURE CheckStock(IN product_id INT, OUT stock_status VARCHAR(20))
BEGIN
  DECLARE stock INT;
  SELECT quantity INTO stock FROM inventory WHERE id = product_id;

  IF stock > 10 THEN
    SET stock_status = 'In Stock';
  ELSEIF stock > 0 THEN
    SET stock_status = 'Low Stock';
  ELSE
    SET stock_status = 'Out of Stock';
  END IF;
END //
DELIMITER ;

呼叫：

CALL CheckStock(1, @status);
SELECT @status;

儲存程序內使用 LOOP

DELIMITER //
CREATE PROCEDURE CountDown(IN start_num INT)
BEGIN
  DECLARE i INT;
  SET i = start_num;

  loop_label: LOOP
    IF i <= 0 THEN
      LEAVE loop_label;
    END IF;
    SELECT i;
    SET i = i - 1;
  END LOOP;
END //
DELIMITER ;

呼叫：

CALL CountDown(5);

刪除儲存程序

DROP PROCEDURE IF EXISTS GetAllProducts;

結論

儲存程序可提高 SQL 效率並封裝業務邏輯。
可使用參數傳遞值，支援條件控制與迴圈。
使用 DELIMITER 避免 SQL 語法衝突。

Stored Procedure 的參數預設值

MySQL / MariaDB 不支援直接設定參數的預設值

在 MySQL 和 MariaDB 中，儲存程序的參數不能直接設定預設值（不像 SQL Server 或 PostgreSQL）。不過，可以使用 IF 條件語句來模擬預設值。

方法 1：使用 IF 條件設定預設值

假設我們要查詢 users 表，當參數 user_id 沒有提供時，預設查詢 ID 為 1：

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    IF user_id IS NULL THEN
        SET user_id = 1; -- 預設值
    END IF;

    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

執行方式

CALL GetUserById(NULL); -- 會查詢 id = 1
CALL GetUserById(5); -- 查詢 id = 5

方法 2：使用 COALESCE() 設定預設值

COALESCE() 會在參數為 NULL 時回傳指定的預設值：

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = COALESCE(user_id, 1);
END //
DELIMITER ;

執行方式

CALL GetUserById(NULL); -- 預設為 1
CALL GetUserById(10); -- 查詢 id = 10

方法 3：使用可選參數（變通方式）

如果想要參數可選，可以建立多個 Stored Procedure。例如：

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

執行方式

CALL GetAllUsers(); -- 無參數，查詢所有
CALL GetUserById(3); -- 查詢 id = 3

結論

MySQL 和 MariaDB 不支援直接在參數中設定 DEFAULT 值。
可以透過 IF 或 COALESCE() 模擬預設值。
若參數為可選，建議建立不同的 Stored Procedure。

Stored Procedure 的回傳值用法

方法 1：使用 OUT 參數回傳值

MySQL / MariaDB 的 Stored Procedure 不支援 RETURN 回傳查詢結果，但可以使用 OUT 參數來回傳值。

DELIMITER //
CREATE PROCEDURE GetUserCount(OUT user_count INT)
BEGIN
    SELECT COUNT(*) INTO user_count FROM users;
END //
DELIMITER ;

呼叫 Stored Procedure 並獲取回傳值

CALL GetUserCount(@total);
SELECT @total; -- 顯示用戶數量

方法 2：使用 SELECT 回傳結果集

如果要回傳一個查詢結果，直接 SELECT 即可：

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

呼叫 Stored Procedure

CALL GetUserById(5); -- 查詢 ID 為 5 的用戶

方法 3：使用 RETURN 回傳單一數值

雖然 MySQL 支援 RETURN，但只能回傳單一數值，通常用於控制流程：

DELIMITER //
CREATE PROCEDURE GetMaxSalary()
BEGIN
    DECLARE max_salary DECIMAL(10,2);
    SELECT MAX(salary) INTO max_salary FROM employees;
    RETURN max_salary; -- 但這樣在 MySQL 不會直接返回值
END //
DELIMITER ;

MySQL 不能直接 `CALL` 來獲取 RETURN 值，因此建議使用 OUT 參數：

DELIMITER //
CREATE PROCEDURE GetMaxSalary(OUT max_salary DECIMAL(10,2))
BEGIN
    SELECT MAX(salary) INTO max_salary FROM employees;
END //
DELIMITER ;

CALL GetMaxSalary(@max);
SELECT @max; -- 顯示最高薪資

方法 4：回傳多個值

使用多個 OUT 參數回傳不同的計算結果：

DELIMITER //
CREATE PROCEDURE GetUserStats(OUT total_users INT, OUT avg_age DECIMAL(5,2))
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
    SELECT AVG(age) INTO avg_age FROM users;
END //
DELIMITER ;

呼叫 Stored Procedure 並取得多個回傳值

CALL GetUserStats(@total, @avg);
SELECT @total, @avg; -- 顯示用戶總數與平均年齡

結論

若回傳單一值，建議使用 OUT 參數。
若回傳一個查詢結果集，直接使用 SELECT。
若回傳多個數值，使用多個 OUT 參數。
MySQL 不支援 RETURN 回傳查詢結果，只適用於流程控制。

在 CALL 儲存程序之外使用回傳值

方法 1：使用 OUT 參數並存入變數

MySQL Stored Procedure 可以透過 OUT 參數回傳值，然後在 CALL 之外用 SELECT 取得該值。

建立 Stored Procedure

DELIMITER //
CREATE PROCEDURE GetTotalUsers(OUT total_users INT)
BEGIN
    SELECT COUNT(*) INTO total_users FROM users;
END //
DELIMITER ;

呼叫 Stored Procedure 並使用回傳值

CALL GetTotalUsers(@total);
SELECT @total AS UserCount; -- 在 CALL 之外使用回傳值

方法 2：使用變數存入回傳的查詢結果

如果 Stored Procedure 使用 SELECT 回傳結果，不能直接存入變數，但可以用 INSERT INTO ... SELECT。

建立 Stored Procedure

DELIMITER //
CREATE PROCEDURE GetMaxSalary()
BEGIN
    SELECT MAX(salary) AS max_salary FROM employees;
END //
DELIMITER ;

使用變數存取查詢結果

CREATE TEMPORARY TABLE temp_result (max_salary DECIMAL(10,2));

INSERT INTO temp_result EXECUTE GetMaxSalary();

SELECT max_salary FROM temp_result; -- 在 CALL 之外使用

方法 3：使用 Prepared Statement 動態存取結果

如果 Stored Procedure 產生的結果需要在變數中存取，可以使用 PREPARE 和 EXECUTE。

建立 Stored Procedure

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT name FROM users WHERE id = user_id;
END //
DELIMITER ;

呼叫並存入變數

SET @sql = 'CALL GetUserById(5)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

結論

使用 OUT 參數回傳單一值，透過 @變數 存取。
若回傳查詢結果，可用 INSERT INTO ... CALL 存入暫存表。
若需動態存取結果，可使用 PREPARE 和 EXECUTE。

MySQL / MariaDB 不允許函數回傳結果集的解決方案

問題描述

在 MySQL / MariaDB 中，Stored Function（函數）不能回傳 SELECT 結果集，否則會出現錯誤：

ERROR 1415 (0A000): Not allowed to return a result set from a function

解決方法

方法 1：改用 Stored Procedure

函數不能回傳結果集，但 Stored Procedure 可以。

錯誤的函數：

DELIMITER //
CREATE FUNCTION GetUsers()
RETURNS TABLE
BEGIN
    RETURN (SELECT * FROM users); -- 這是不允許的
END //
DELIMITER ;

正確的 Stored Procedure：

DELIMITER //
CREATE PROCEDURE GetUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

呼叫 Stored Procedure：

CALL GetUsers();

方法 2：使用函數回傳單一值

如果只需要回傳單一值（如計數或最大值），可以用 RETURN。

DELIMITER //
CREATE FUNCTION GetUserCount()
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM users;
    RETURN total;
END //
DELIMITER ;

使用函數：

SELECT GetUserCount();

方法 3：使用 Temporary Table

如果真的需要在函數內回傳多行結果，可以讓函數插入資料到 Temporary Table，然後在外部查詢。

DELIMITER //
CREATE FUNCTION PopulateTempUsers()
RETURNS INT DETERMINISTIC
BEGIN
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_users AS (SELECT * FROM users);
    RETURN 1;
END //
DELIMITER ;

使用 Temporary Table 讀取資料：

SELECT PopulateTempUsers();
SELECT * FROM temp_users;

結論

MySQL / MariaDB 不允許函數回傳結果集，應改用 Stored Procedure。
若只需回傳單一值，可在函數內使用 RETURN。
若需回傳多行結果，可用 Temporary Table 存儲後查詢。

HeidiSQL

簡介

HeidiSQL 是一款免費的開源 SQL 客戶端，支援 MySQL、MariaDB、PostgreSQL 和 MS SQL Server，提供 GUI 來管理資料庫、執行 SQL 查詢、匯入/匯出資料等。

主要功能

管理資料庫、表格、索引、檢視、觸發器
執行 SQL 查詢，支援多標籤編輯
視覺化資料表結構與 ER 圖
批量編輯資料
匯入/匯出 CSV、SQL、XML、JSON 等格式
管理使用者權限

下載與安裝

1. 前往官方網站下載 HeidiSQL: https://www.heidisql.com/download.php
2. 執行安裝程式並按照指示完成安裝
3. 開啟 HeidiSQL，設定新連線

連接 MySQL / MariaDB

1. 開啟 HeidiSQL
2. 點擊「新增」建立新連線
3. 設定：
   - 主機名/IP：127.0.0.1 或 遠端伺服器 IP
   - 使用者名稱：root 或其他使用者
   - 密碼：對應的密碼
   - 連接埠：3306（MySQL / MariaDB）
4. 點擊「開啟」連接資料庫

執行 SQL 查詢

在 HeidiSQL 查詢視窗中輸入 SQL 語句：

SELECT * FROM users WHERE status = 'active';

點擊「執行」按鈕，即可查看結果。

匯入 / 匯出資料

匯出 SQL

1. 右鍵點擊資料庫 → 選擇「匯出 SQL」
2. 選擇要匯出的資料表
3. 設定匯出格式（.sql、.csv、.json）
4. 點擊「匯出」

匯入 SQL

1. 打開 HeidiSQL，選擇目標資料庫
2. 點擊「工具」→「執行 SQL 檔案」
3. 選擇 .sql 檔案並執行

管理使用者權限

1. 進入「工具」→「管理使用者權限」
2. 選擇要管理的使用者
3. 設定資料庫權限（SELECT、INSERT、UPDATE、DELETE 等）
4. 點擊「儲存」

結論

HeidiSQL 是輕量但強大的 SQL 客戶端
適合開發者、DBA 管理 MySQL、MariaDB、PostgreSQL
提供視覺化管理功能，提高效率

HeidiSQL 新增儲存程序

步驟 1：連接資料庫

1. 開啟 HeidiSQL。
2. 連接 MySQL 或 MariaDB 伺服器。
3. 在左側的「資料庫」列表中選擇目標資料庫。

步驟 2：建立新儲存程序

1. 在左側的資料庫名稱上點擊右鍵，選擇「建立新的」→「儲存程序」。
2. HeidiSQL 會打開一個新的 SQL 編輯視窗，並提供預設的儲存程序模板。

步驟 3：編寫儲存程序

以下是一個簡單的範例，返回 users 表中的所有資料：

DELIMITER //
CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //
DELIMITER ;

步驟 4：執行儲存程序

1. 點擊「執行」按鈕（綠色閃電）。
2. 若執行成功，可在左側的「儲存程序」欄位中找到該程序。

步驟 5：測試儲存程序

CALL GetAllUsers();

進階：帶參數的儲存程序

傳入參數來篩選數據，例如根據用戶 ID 查詢：

DELIMITER //
CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

呼叫帶參數的儲存程序：

CALL GetUserById(1);

刪除儲存程序

DROP PROCEDURE IF EXISTS GetAllUsers;

結論

HeidiSQL 提供 GUI 方式管理儲存程序。
使用 DELIMITER 確保語法正確。
可以使用 CALL 測試儲存程序。

email: [email protected]

T:0000

資訊與搜尋 | 回dev首頁
email: Yan Sa [email protected] Line: 阿央

電話: 02-27566655 ,03-5924828

阿央
泱泱科技
捷昱科技泱泱企業

資料庫開發

技術

電腦的使用

軟體開發

網頁開發

SQL

定義

主要功能

基本語法

常見數據類型

優點

MySQL

關聯式資料庫

特性

使用範例

適用場景

SQLite

輕量化資料庫

特性

使用範例

適用場景

用 Name 代替 ID 欄位

適用條件

優勢

潛在問題

適用範例

關聯式資料庫設計繼承物件

資料庫結構設計

animal 表格

cat 表格

SQL 建立表格指令

插入資料的範例

HTML 表格展示範例

動物資料

貓的特有資料

說明

查詢所有動物的資料

查詢所有貓的資料

查詢特定動物的資料（例如特定 ID）

查詢特定貓的資料（例如特定品種）

說明

FOREIGN KEY

用途

語法

範例

注意事項

進階用法

行為選項

在 CREATE TABLE 中加入備註

使用 COMMENT 設定欄位備註

使用 COMMENT 設定資料表備註

查看欄位備註

修改欄位備註

適用範圍

PostgreSQL 設定備註

SQL 欄位名稱使用到保留字

1. 根本解決方案：避免使用保留字 (資料庫設計)

2. 技術解決方案：使用引號包裹識別符號 (程式碼實作)

3. 程式碼自動化處理 (動態生成 SQL)

4. ORM 框架的優勢

SQL DATETIME 比較

使用 TIMESTAMPDIFF

使用 ABS(TIMESTAMPDIFF)

使用 DATEDIFF（僅適用於天數比較）

使用 TIMESTAMP 直接相減

MySQL 處理毫秒級時間戳

修正方法：除以 1000

1. 轉換為 DATETIME 格式

2. 轉換為 UNIX_TIMESTAMP (秒級)

3. 儲存到 DATETIME/TIMESTAMP 欄位

4. 處理 UTC 時區

格式化 FLOAT

使用 FORMAT()

使用 ROUND()

使用 CAST() 或 CONVERT()

應用在資料表欄位

設定欄位格式

查詢欄位總計

使用 MAX()

使用 MIN()