| 函數 | 說明 | 示例 |
|---|---|---|
COUNT(*) | 統計行數 (含 NULL) | SELECT COUNT(*) FROM users; |
COUNT(col) | 統計非 NULL 個數 | SELECT COUNT(email) FROM users; |
SUM(col) | 求和 | SELECT SUM(amount) FROM orders; |
AVG(col) | 平均值 | SELECT AVG(score) FROM exams; |
MAX/MIN(col) | 最大/最小值 | SELECT MAX(created_at) FROM logs; |
GROUP_CONCAT(col SEPARATOR ',') | 分組拼接字符串 | SELECT dept_id, GROUP_CONCAT(name) FROM emp GROUP BY dept_id; |
STDDEV/Variance | 標準差/方差 | SELECT STDDEV(salary) FROM employees; |
| 函數 | 說明 | 示例 |
|---|---|---|
CONCAT(s1, s2, ...) | 拼接字符串 | SELECT CONCAT(first_name, ' ', last_name) FROM users; |
CONCAT_WS(sep, s1, s2) | 帶分隔符拼接 (自動跳過 NULL) | SELECT CONCAT_WS('-', year, month, day) FROM dates; |
SUBSTRING(str, pos, len) | 截取子串 (1-based) | SELECT SUBSTRING(phone, 1, 3) AS area_code FROM users; |
LEFT/RIGHT(str, len) | 左/右截取 | SELECT LEFT(id_card, 6) AS region_code FROM users; |
TRIM([BOTH|LEADING|TRAILING] 'x' FROM str) | 去除首尾字符 | SELECT TRIM(' ' FROM name) FROM users; |
REPLACE(str, from, to) | 替換所有匹配 | UPDATE users SET phone = REPLACE(phone, '-', ''); |
UPPER/LOWER(str) | 大小寫轉換 | SELECT LOWER(email) FROM users; |
LENGTH/CHAR_LENGTH(str) | 字節長度/字符長度 | SELECT CHAR_LENGTH(name) FROM users; |
LOCATE(substr, str, [pos]) | 查找子串位置 | SELECT LOCATE('@', email) FROM users; |
LPAD/RPAD(str, len, padstr) | 左/右填充 | SELECT LPAD(id, 8, '0') FROM users; |
| 函數 | 說明 | 示例 |
|---|---|---|
NOW() / CURRENT_TIMESTAMP | 當前日期時間 | INSERT INTO logs VALUES (NOW(), 'msg'); |
CURDATE() / CURTIME() | 當前日期/時間 | WHERE created_at >= CURDATE() |
DATE_ADD/DATE_SUB(date, INTERVAL expr unit) | 日期加減 | WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) |
DATEDIFF(date1, date2) | 日期差 (天) | SELECT DATEDIFF(NOW(), hiredate) FROM emp; |
DATE_FORMAT(date, format) | 格式化輸出 | SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i') FROM logs; |
STR_TO_DATE(str, format) | 字符串解析為日期 | WHERE created_at = STR_TO_DATE('2023-01-01', '%Y-%m-%d') |
UNIX_TIMESTAMP([date]) | 轉時間戳 | SELECT UNIX_TIMESTAMP() * 1000 AS ms; |
FROM_UNIXTIME(ts) | 時間戳轉日期時間 | SELECT FROM_UNIXTIME(1672567200); |
LAST_DAY(date) | 月份最後一天 | SELECT LAST_DAY('2023-02-15'); -- 2023-02-28 |
| 函數 | 說明 | 示例 |
|---|---|---|
IF(condition, true_val, false_val) | 三元運算 | SELECT IF(score >= 60, 'Pass', 'Fail') FROM exams; |
CASE WHEN cond THEN val ... ELSE val END | 多分支 | SELECT CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END FROM users; |
COALESCE(val1, val2, ...) | 返回第一個非 NULL | SELECT COALESCE(phone, email, 'N/A') FROM users; |
NULLIF(val1, val2) | 相等返回 NULL | SELECT NULLIF(qty, 0) FROM inventory; |
IFNULL(val1, val2) | MySQL 專用 COALESCE 兩參數版 | SELECT IFNULL(bonus, 0) FROM salary; |
-- 需求：統計 vpdm 欄位各值出現頻次
SELECT `vpdm`, COUNT(*) AS freq
FROM `ipos_qtlsd`
GROUP BY `vpdm`
ORDER BY freq DESC;-- 行號、排名、分組內排序
SELECT
`name`, `dept`, `salary`,
ROW_NUMBER() OVER (PARTITION BY `dept` ORDER BY `salary` DESC) AS rn,
RANK() OVER (PARTITION BY `dept` ORDER BY `salary` DESC) AS rk,
LAG(`salary`) OVER (PARTITION BY `dept` ORDER BY `salary`) AS prev_sal
FROM `employees`;掌握上述核心函數可覆蓋 90%+ 日常開發需求。關鍵注意點：字符串函數區分字節/字符長度、日期格式化模板大小寫敏感、聚合函數自動忽略 NULL (除 COUNT(*))、窗口函數需 8.0+。複雜邏輯優先在 SQL 完成，減少應用層數據傳輸與處理。
