今天给大家总结的是SQL Server/MySQL/Oracle这三个关系数据库的函数内容，包含常用和不常用的。
这些总结都是此前整理好后保存的，最近集中发布，觉得有帮助，记得三连（点赞+转发+在看），岳哥才会更有动力继续发布。此外，大家也可以留言需要哪方面的总结。
1. 字符串函数1.1 基础字符串函数LENGTH/LEN/LENGTH - 获取字符串长度代码语言：javascript复制-- MySQL
SELECT LENGTH('Hello World'); -- 11
-- SQL Server
SELECT LEN('Hello World'); -- 11
-- Oracle
SELECT LENGTH('Hello World') FROM DUAL; -- 11
CHAR_LENGTH - 获取字符数(区别于字节长度)代码语言：javascript复制-- MySQL & Orac
SELECT CHAR_LENGTH('你好'); -- 2
SUBSTRING/SUBSTR - 截取字符串代码语言：javascript复制-- MySQL & SQL Server
SELECT SUBSTRING('Hello World', 1, 5); -- 'Hello'
SELECT SUBSTRING('Hello World', -5); -- 'World'
-- Oracle
SELECT SUBSTR('Hello World', 1, 5) FROM DUAL;
LEFT/RIGHT - 从左/右截取代码语言：javascript复制-- MySQL & SQL Server
SELECT LEFT('Hello World', 5); -- 'Hello'
SELECT RIGHT('Hello World', 5); -- 'World'
REPLACE - 替换字符串代码语言：javascript复制-- 所有数据库通用
SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'
STUFF - 字符串替换(SQL Server特有)代码语言：javascript复制SELECT STUFF('Hello World', 1, 5, 'Hi'); -- 'Hi World'
POSITION/INSTR/CHARINDEX - 查找子字符串位置代码语言：javascript复制-- MySQL
SELECT POSITION('World' IN 'Hello World'); -- 7
-- Oracle
SELECT INSTR('Hello World', 'World') FROM DUAL; -- 7
-- SQL Server
SELECT CHARINDEX('World', 'Hello World'); -- 7
REVERSE - 反转字符串代码语言：javascript复制-- 所有数据库
SELECT REVERSE('Hello'); -- 'olleH'
SPACE - 生成空格字符串代码语言：javascript复制-- SQL Server & MySQL
SELECT 'Hello' + SPACE(1) + 'World'; -- 'Hello World'
REPEAT/REPLICATE - 重复字符串代码语言：javascript复制-- MySQL
SELECT REPEAT('SQL', 3); -- 'SQLSQLSQL'
-- SQL Server
SELECT REPLICATE('SQL', 3); -- 'SQLSQLSQL'
1.2 高级字符串函数FORMAT - 格式化字符串代码语言：javascript复制-- MySQL & SQL Server
SELECT FORMAT(123456.789, 2); -- '123,456.79'
STRING_SPLIT(SQL Server)/SPLIT_STRING(MySQL) - 字符串分割代码语言：javascript复制-- SQL Server
SELECT value FROM STRING_SPLIT('a,b,c', ',');
-- MySQL
SELECT SUBSTRING_INDEX('a,b,c', ',', 1); -- 'a'
GROUP_CONCAT/STRING_AGG - 字符串聚合代码语言：javascript复制-- MySQL
SELECT GROUP_CONCAT(name SEPARATOR ',') FROM employees;
-- SQL Server
SELECT STRING_AGG(name, ',') FROM employees;
-- Oracle
SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) FROM employees;
2. 数值函数2.1 基础数学函数ROUND/TRUNC/TRUNCATE - 截断代码语言：javascript复制-- 所有数据库
SELECT ROUND(123.456, 2); -- 123.46
-- Oracle
SELECT TRUNC(123.456, 2) FROM DUAL; -- 123.45
-- MySQL
SELECT TRUNCATE(123.456, 2); -- 123.45
MOD - 取模代码语言：javascript复制-- 所有数据库
SELECT MOD(10, 3); -- 1
SQRT - 平方根代码语言：javascript复制SELECT SQRT(16); -- 4
SIGN - 获取数字符号代码语言：javascript复制SELECT SIGN(-10); -- -1
SELECT SIGN(10); -- 1
SELECT SIGN(0); -- 0
2.2 高级数学函数LOG/LOG10/LN - 对数运算代码语言：javascript复制SELECT LOG(10, 100); -- 2
SELECT LOG10(100); -- 2
SELECT LN(2.7); -- 0.993
EXP - 指数运算代码语言：javascript复制SELECT EXP(1); -- 2.718281828459045
RAND/RANDOM - 随机数代码语言：javascript复制-- MySQL & SQL Server
SELECT RAND();
-- Oracle
SELECT DBMS_RANDOM.VALUE FROM DUAL;
3. 日期时间函数3.1 获取日期时间NOW/GETDATE/SYSDATE - 当前日期时间代码语言：javascript复制-- MySQL
SELECT NOW();
-- SQL Server
SELECT GETDATE();
-- Oracle
SELECT SYSDATE FROM DUAL;
CURDATE/CURRENT_DATE - 当前日期代码语言：javascript复制-- MySQL
SELECT CURDATE();
-- Oracle & SQL Server
SELECT CURRENT_DATE;
CURTIME/CURRENT_TIME - 当前时间代码语言：javascript复制-- MySQL
SELECT CURTIME();
-- Oracle & SQL Server
SELECT CURRENT_TIME;
3.2 日期时间处理DATE_ADD/DATEADD - 日期加减代码语言：javascript复制-- MySQL
SELECT DATE_ADD('2024-03-12', INTERVAL 1 DAY);
SELECT DATE_ADD('2024-03-12', INTERVAL 1 MONTH);
SELECT DATE_ADD('2024-03-12', INTERVAL 1 YEAR);
-- SQL Server
SELECT DATEADD(day, 1, '2024-03-12');
SELECT DATEADD(month, 1, '2024-03-12');
SELECT DATEADD(year, 1, '2024-03-12');
DATE_FORMAT/FORMAT - 日期格式化代码语言：javascript复制-- MySQL
SELECT DATE_FORMAT('2024-03-12', '%Y年%m月%d日'); -- '2024年03月12日'
-- SQL Server
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日');
EXTRACT/DATEPART - 提取日期部分代码语言：javascript复制-- MySQL & Oracle
SELECT EXTRACT(YEAR FROM '2024-03-12');
SELECT EXTRACT(MONTH FROM '2024-03-12');
SELECT EXTRACT(DAY FROM '2024-03-12');
-- SQL Server
SELECT DATEPART(year, '2024-03-12');
SELECT DATEPART(month, '2024-03-12');
SELECT DATEPART(day, '2024-03-12');
LAST_DAY - 获取月末日期代码语言：javascript复制-- MySQL & Oracle
SELECT LAST_DAY('2024-03-12'); -- '2024-03-31'
4. 条件和控制函数IF/IIF - 条件判断代码语言：javascript复制-- MySQL
SELECT IF(1 > 0, 'True', 'False');
-- SQL Server
SELECT IIF(1 > 0, 'True', 'False');
IFNULL/ISNULL/NVL - NULL值处理代码语言：javascript复制-- MySQL
SELECT IFNULL(NULL, 'Default');
-- SQL Server
SELECT ISNULL(NULL, 'Default');
-- Oracle
SELECT NVL(NULL, 'Default') FROM DUAL;
NULLIF - 相等返回NULL代码语言：javascript复制SELECT NULLIF(10, 10); -- NULL
SELECT NULLIF(10, 20); -- 10
GREATEST/LEAST - 最大最小值代码语言：javascript复制-- MySQL & Oracle
SELECT GREATEST(1, 2, 3, 4, 5); -- 5
SELECT LEAST(1, 2, 3, 4, 5); -- 1
5. 窗口函数ROW_NUMBER/RANK/DENSE_RANK - 排序代码语言：javascript复制SELECT
name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
RANK() OVER (ORDER BY salary DESC) as rank_num,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank_num
FROM employees;
FIRST_VALUE/LAST_VALUE - 首尾值代码语言：javascript复制SELECT
name,
department,
salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as highest_salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as lowest_salary
FROM employees;
LAG/LEAD - 前后行代码语言：javascript复制SELECT
name,
department,
salary,
LAG(salary) OVER (PARTITION BY department ORDER BY salary) as prev_salary,
LEAD(salary) OVER (PARTITION BY department ORDER BY salary) as next_salary
FROM employees;
NTILE - 分组代码语言：javascript复制SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;
6. JSON函数(MySQL 5.7+)JSON_EXTRACT - 提取JSON值代码语言：javascript复制SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- "John"
JSON_OBJECT - 创建JSON对象代码语言：javascript复制SELECT JSON_OBJECT('name', 'John', 'age', 30);
JSON_ARRAY - 创建JSON数组代码语言：javascript复制SELECT JSON_ARRAY(1, 2, 3, 4, 5);
JSON_CONTAINS - 检查JSON包含代码语言：javascript复制SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a'); -- 1
7. 加密和安全函数MD5 - MD5加密代码语言：javascript复制-- MySQL & SQL Server
SELECT MD5('password');
SHA1/SHA2 - SHA加密代码语言：javascript复制-- MySQL
SELECT SHA1('password');
SELECT SHA2('password', 256);
ENCRYPT/DECRYPT - 加密解密代码语言：javascript复制-- MySQL
SET @key = 'secret_key';
SET @encrypted = AES_ENCRYPT('text', @key);
SELECT AES_DECRYPT(@encrypted, @key);
8. XML函数(SQL Server)FOR XML PATH - 生成XML代码语言：javascript复制SELECT name, age
FROM employees
FOR XML PATH('employee'), ROOT('employees')
XML数据类型方法代码语言：javascript复制DECLARE @xml XML
SET @xml = '
SELECT @xml.value('(/root/child)[1]', 'varchar(50)')
9. 正则表达式函数REGEXP/RLIKE - 正则匹配(MySQL)代码语言：javascript复制SELECT 'hello' REGEXP '^h'; -- 1
SELECT 'hello' RLIKE 'l+'; -- 1
REGEXP_LIKE - 正则匹配(Oracle)代码语言：javascript复制SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Za-z]+@[A-Za-z]+\.[A-Za-z]{2,4}$');
10. 系统信息函数VERSION - 数据库版本代码语言：javascript复制-- MySQL
SELECT VERSION();
-- SQL Server
SELECT @@VERSION;
-- Oracle
SELECT * FROM V$VERSION;
USER/CURRENT_USER - 当前用户代码语言：javascript复制-- 所有数据库
SELECT USER;
SELECT CURRENT_USER;
DATABASE/DB_NAME - 当前数据库代码语言：javascript复制-- MySQL
SELECT DATABASE();
-- SQL Server
SELECT DB_NAME();
11. 高级聚合函数GROUPING SETS - 多维度聚合代码语言：javascript复制SELECT department, location, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
(department, location),
(department),
(location),
()
);
CUBE - 所有可能的组合代码语言：javascript复制SELECT department, location, COUNT(*)
FROM employees
GROUP BY CUBE (department, location);
ROLLUP - 层次聚合代码语言：javascript复制SELECT
COALESCE(department, 'Total') as department,
COALESCE(location, 'Subtotal') as location,
COUNT(*) as employee_count,
AVG(salary) as avg_salary
FROM employees
GROUP BY ROLLUP (department, location);
PIVOT - 行转列代码语言：javascript复制-- SQL Server
SELECT *
FROM (
SELECT department, location, salary
FROM employees
) AS SourceTable
PIVOT (
AVG(salary)
FOR location IN ([New York], [London], [Tokyo])
) AS PivotTable;
12. 统计和数学函数PERCENTILE_CONT/PERCENTILE_DISC - 百分位数代码语言：javascript复制SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) as discrete_median
FROM employees;
CORR - 相关系数代码语言：javascript复制SELECT CORR(salary, performance_score)
FROM employees;
STDDEV/VARIANCE - 标准差和方差代码语言：javascript复制SELECT
department,
AVG(salary) as avg_salary,
STDDEV(salary) as salary_stddev,
VARIANCE(salary) as salary_variance
FROM employees
GROUP BY department;
FIRST/LAST - 组内第一个/最后一个值代码语言：javascript复制-- Oracle
SELECT
department,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY hire_date) as first_salary,
LAST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY hire_date
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as last_salary
FROM employees;
13. 字符串模式匹配函数LIKE模式匹配增强代码语言：javascript复制-- 复杂LIKE模式
SELECT * FROM employees
WHERE
name LIKE '[A-M]%' -- SQL Server, 以A到M开头的名字
AND email LIKE '%@__%.__%'; -- 标准email模式
14. 条件和流程控制增强CHOOSE - 索引选择代码语言：javascript复制-- SQL Server
SELECT CHOOSE(2, 'First', 'Second', 'Third'); -- 返回 'Second'
复杂CASE表达式代码语言：javascript复制SELECT
employee_name,
salary,
CASE
WHEN salary <= (SELECT AVG(salary) FROM employees) THEN 'Below Average'
WHEN salary <= (SELECT AVG(salary) + STDDEV(salary) FROM employees) THEN 'Average'
WHEN salary <= (SELECT AVG(salary) + 2*STDDEV(salary) FROM employees) THEN 'Above Average'
ELSE 'Exceptional'
END as salary_category
FROM employees;
15. 表分析函数PERCENT_RANK - 百分比排名代码语言：javascript复制SELECT
name,
salary,
PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile
FROM employees;
CUME_DIST - 累积分布代码语言：javascript复制SELECT
name,
salary,
CUME_DIST() OVER (ORDER BY salary) as salary_distribution
FROM employees;
16. 实用复合函数示例年龄计算代码语言：javascript复制-- MySQL
SELECT
name,
birthdate,
TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) as age,
DATE_ADD(birthdate,
INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) YEAR) as last_birthday,
DATE_ADD(birthdate,
INTERVAL TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) + 1 YEAR) as next_birthday
FROM employees;
工龄分析代码语言：javascript复制SELECT
name,
hire_date,
CASE
WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 2 THEN 'Junior'
WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 5 THEN 'Intermediate'
WHEN DATEDIFF(YEAR, hire_date, GETDATE()) < 10 THEN 'Senior'
ELSE 'Expert'
END as experience_level
FROM employees;
薪资分析代码语言：javascript复制WITH salary_stats AS (
SELECT
department,
AVG(salary) as avg_salary,
STDDEV(salary) as salary_stddev
FROM employees
GROUP BY department
)
SELECT
e.name,
e.department,
e.salary,
s.avg_salary,
(e.salary - s.avg_salary) / s.salary_stddev as z_score,
PERCENT_RANK() OVER (PARTITION BY e.department ORDER BY e.salary) as dept_percentile
FROM employees e
JOIN salary_stats s ON e.department = s.department;
考勤分析代码语言：javascript复制WITH daily_attendance AS (
SELECT
employee_id,
attendance_date,
check_in_time,
check_out_time,
CASE
WHEN check_in_time > '09:00:00' THEN 'Late'
WHEN check_out_time < '17:00:00' THEN 'Early Leave'
ELSE 'Normal'
END as attendance_status
FROM attendance
)
SELECT
e.name,
COUNT(*) as total_days,
SUM(CASE WHEN a.attendance_status = 'Late' THEN 1 ELSE 0 END) as late_days,
SUM(CASE WHEN a.attendance_status = 'Early Leave' THEN 1 ELSE 0 END) as early_leave_days,
FORMAT(COUNT(*) * 1.0 /
(SELECT COUNT(DISTINCT attendance_date) FROM attendance), 'P') as attendance_rate
FROM employees e
JOIN daily_attendance a ON e.id = a.employee_id
GROUP BY e.name;
销售分析代码语言：javascript复制WITH monthly_sales AS (
SELECT
YEAR(sale_date) as year,
MONTH(sale_date) as month,
SUM(amount) as total_sales,
COUNT(DISTINCT customer_id) as customer_count
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date)
)
SELECT
year,
month,
total_sales,
customer_count,
total_sales / customer_count as avg_customer_value,
LAG(total_sales) OVER (ORDER BY year, month) as prev_month_sales,
total_sales - LAG(total_sales) OVER (ORDER BY year, month) as sales_growth,
FORMAT((total_sales - LAG(total_sales) OVER (ORDER BY year, month)) /
LAG(total_sales) OVER (ORDER BY year, month), 'P') as growth_rate
FROM monthly_sales;