1、字符串函數
測試用例如下：
1.1 CONCAT()
將多個字符串連接成一個字符串。
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- 期望結果：'John Doe', 'Jane Smith', 'Michael Johnson'
1.2 SUBSTRING()
提取子字符串
SELECT SUBSTRING(column_name, start_pos, length) FROM table_name;
SELECT SUBSTRING(first_name, 1, 3) AS short_name FROM users;
-- 期望結果：'Joh', 'Jan', 'Mic'
1.3 UPPER()
將字符串轉換為大寫
SELECT UPPER(column_name) FROM table_name;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
-- 期望結果：'John Doe', 'Jane Smith', 'Michael Johnson'
1.4 LOWER()
將字符串轉換為小寫
SELECT LOWER(column_name) FROM table_name;
SELECT LOWER(last_name) AS lower_name FROM users;
-- 期望結果：'doe', 'smith', 'johnson'
1.5 LENGTH()
返回字符串的長度
SELECT LENGTH(column_name) FROM table_name;
SELECT LENGTH(first_name) AS name_length FROM users;
-- 期望結果：4, 4, 7
1.6 TRIM()
去除字符串兩端的空格或指定字符
SELECT TRIM(' ' FROM column_name) FROM table_name;
SELECT TRIM(' ' FROM ' John Doe ') AS trimmed_name;
-- 期望結果：'John Doe'
1.7 REPLACE()
替換字符串中的字串
SELECT REPLACE(column_name, 'old_string', 'new_string') FROM table_name;
SELECT REPLACE(first_name, 'J', 'M') AS replaced_name FROM users;
-- 期望結果：'Monn', 'Mane', 'Michael'
2、數值函數
測試用例如下：
2.1 SUM()
計算數值列的總和。
SELECT SUM(column_name) FROM table_name;
SELECT SUM(quantity) AS total_quantity FROM sales;
-- 期望結果：23
2.2 AVG()
計算數值列的平均值
SELECT AVG(column_name) FROM table_name;
SELECT AVG(unit_price) AS average_price FROM sales;
-- 期望結果：16.083333
2.3 COUNT()
計算行數或者非NULL值的數量
SELECT COUNT(*) FROM table_name;
SELECT COUNT(*) AS total_records FROM sales;
-- 期望結果：3
2.4 MAX()
返回數值列的最大值
SELECT MAX(column_name) FROM table_name;
SELECT MAX(unit_price) AS max_price FROM sales;
-- 期望結果：20.00
2.5 MIN()
返回數值列的最小值
2.6 ROUND()
對小數四舍五入到指定位數
SELECT ROUND(column_name, decimals) FROM table_name;
SELECT ROUND(unit_price, 1) AS rounded_price FROM sales;
-- 期望結果：15.5, 20.0, 12.8
