資料庫開發

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 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;

格式化 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 查詢中是否能在別名（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首頁電話: 02-27566655 ,03-5924828 email: [email protected]

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

資料庫開發

技術

電腦的使用

軟體開發

網頁開發

SQL

定義

主要功能

基本語法

常見數據類型

優點

MySQL

關聯式資料庫

特性

使用範例

適用場景

SQLite

輕量化資料庫

特性

使用範例

適用場景

用 Name 代替 ID 欄位

適用條件

優勢

潛在問題

適用範例

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

資料庫結構設計

animal 表格

cat 表格

SQL 建立表格指令

插入資料的範例

HTML 表格展示範例

動物資料

貓的特有資料

說明

查詢所有動物的資料

查詢所有貓的資料

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

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

說明

FOREIGN KEY

用途

語法

範例

注意事項

進階用法

行為選項

在 CREATE TABLE 中加入備註

使用 COMMENT 設定欄位備註

使用 COMMENT 設定資料表備註

查看欄位備註

修改欄位備註

適用範圍

PostgreSQL 設定備註

SQL DATETIME 比較

使用 TIMESTAMPDIFF

使用 ABS(TIMESTAMPDIFF)

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

使用 TIMESTAMP 直接相減

格式化 FLOAT

使用 FORMAT()

使用 ROUND()

使用 CAST() 或 CONVERT()

應用在資料表欄位

設定欄位格式

查詢欄位總計

使用 MAX()

使用 MIN()

使用 AVG()

同時查詢 MAX、MIN、AVG

依類別分組計算

使用子查詢找最大值

使用 ORDER BY + LIMIT

使用 WITH (Common Table Expression, CTE)

幾何平均數公式

使用 EXP() 和 LOG()

使用 POWER()

注意事項

SQL 計算欄位的標準差