今天给大家总结的最强总结指南是SQL Server/MySQL/Oracle这三个关系数据库的函数内容，免费信息发布网包含常用和不常用的函数。

1. 字符串函数

1.1 基础字符串函数

LENGTH/LEN/LENGTH - 获取字符串长度 复制-- MySQL SELECT LENGTH(Hello World); -- 11 -- SQL Server SELECT LEN(Hello World); -- 11 -- Oracle SELECT LENGTH(Hello World) FROM DUAL; -- 111.2.3.4.5.6. CHAR_LENGTH - 获取字符数(区别于字节长度) 复制-- MySQL & Oracle SELECT CHAR_LENGTH(你好); -- 21.2. SUBSTRING/SUBSTR - 截取字符串 复制-- 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;1.2.3.4.5.6. LEFT/RIGHT - 从左/右截取 复制-- MySQL & SQL Server SELECT LEFT(Hello World, 5); -- Hello SELECT RIGHT(Hello World, 5); -- World1.2.3. REPLACE - 替换字符串 复制-- 所有数据库通用 SELECT REPLACE(Hello World, World, SQL); -- Hello SQL1.2. STUFF - 字符串替换(SQL Server特有) 复制SELECT STUFF(Hello World, 1, 5, Hi); -- Hi World1. POSITION/INSTR/CHARINDEX - 查找子字符串位置 复制-- MySQL SELECT POSITION(World IN Hello World); -- 7 -- Oracle SELECT INSTR(Hello World, World) FROM DUAL; -- 7 -- SQL Server SELECT CHARINDEX(World, Hello World); -- 71.2.3.4.5.6.7.8. REVERSE - 反转字符串 复制-- 所有数据库 SELECT REVERSE(Hello); -- olleH1.2. SPACE - 生成空格字符串 复制-- SQL Server & MySQL SELECT Hello + SPACE(1) + World; -- Hello World1.2. REPEAT/REPLICATE - 重复字符串 复制-- MySQL SELECT REPEAT(SQL, 3); -- SQLSQLSQL -- SQL Server SELECT REPLICATE(SQL, 3); -- SQLSQLSQL1.2.3.4.5.

1.2 高级字符串函数

FORMAT - 格式化字符串 复制-- MySQL & SQL Server SELECT FORMAT(123456.789, 2); -- 123,456.791.2. STRING_SPLIT(SQL Server)/SPLIT_STRING(MySQL) - 字符串分割 复制-- SQL Server SELECT value FROM STRING_SPLIT(a,b,c, ,); -- MySQL SELECT SUBSTRING_INDEX(a,b,c, ,, 1); -- a1.2.3.4.5. GROUP_CONCAT/STRING_AGG - 字符串聚合 复制-- 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;1.2.3.4.5.6.7.8.

2. 数值函数

2.1 基础数学函数

ROUND/TRUNC/TRUNCATE - 截断 复制-- 所有数据库 SELECT ROUND(123.456, 2); -- 123.46 -- Oracle SELECT TRUNC(123.456, 2) FROM DUAL; -- 123.45 -- MySQL SELECT TRUNCATE(123.456, 2); -- 123.451.2.3.4.5.6.7.8. MOD - 取模 复制-- 所有数据库 SELECT MOD(10, 3); -- 11.2. SQRT - 平方根 复制SELECT SQRT(16); -- 41. SIGN - 获取数字符号 复制SELECT SIGN(-10); -- -1 SELECT SIGN(10); -- 1 SELECT SIGN(0); -- 01.2.3.

2.2 高级数学函数

LOG/LOG10/LN - 对数运算 复制SELECT LOG(10, 100); -- 2 SELECT LOG10(100); -- 2 SELECT LN(2.7); -- 0.9931.2.3. EXP - 指数运算 复制SELECT EXP(1); -- 2.7182818284590451. RAND/RANDOM - 随机数 复制-- MySQL & SQL Server SELECT RAND(); -- Oracle SELECT DBMS_RANDOM.VALUE FROM DUAL;1.2.3.4.5.

3. 日期时间函数

3.1 获取日期时间

NOW/GETDATE/SYSDATE - 当前日期时间 复制-- MySQL SELECT NOW(); -- SQL Server SELECT GETDATE(); -- Oracle SELECT SYSDATE FROM DUAL;1.2.3.4.5.6.7.8. CURDATE/CURRENT_DATE - 当前日期 复制-- MySQL SELECT CURDATE(); -- Oracle & SQL Server SELECT CURRENT_DATE;1.2.3.4.5. CURTIME/CURRENT_TIME - 当前时间 复制-- MySQL SELECT CURTIME(); -- Oracle & SQL Server SELECT CURRENT_TIME;1.2.3.4.5.

3.2 日期时间处理

DATE_ADD/DATEADD - 日期加减 复制-- 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);1.2.3.4.5.6.7.8.9. DATE_FORMAT/FORMAT - 日期格式化 复制-- MySQL SELECT DATE_FORMAT(2024-03-12, %Y年%m月%d日); -- 2024年03月12日 -- SQL Server SELECT FORMAT(GETDATE(), yyyy年MM月dd日);1.2.3.4.5. EXTRACT/DATEPART - 提取日期部分 复制-- 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);1.2.3.4.5.6.7.8.9. LAST_DAY - 获取月末日期 复制-- MySQL & Oracle SELECT LAST_DAY(2024-03-12); -- 2024-03-311.2.

4. 条件和控制函数

IF/IIF - 条件判断 复制-- MySQL SELECT IF(1 > 0, True, False); -- SQL Server SELECT IIF(1 > 0, True, False);1.2.3.4.5. IFNULL/ISNULL/NVL - NULL值处理 复制-- MySQL SELECT IFNULL(NULL, Default); -- SQL Server SELECT ISNULL(NULL, Default); -- Oracle SELECT NVL(NULL, Default) FROM DUAL;1.2.3.4.5.6.7.8. NULLIF - 相等返回NULL 复制SELECT NULLIF(10, 10); -- NULL SELECT NULLIF(10, 20); -- 101.2. GREATEST/LEAST - 最大最小值 复制-- MySQL & Oracle SELECT GREATEST(1, 2, 3, 4, 5); -- 5 SELECT LEAST(1, 2, 3, 4, 5); -- 11.2.3.

5. 窗口函数

ROW_NUMBER/RANK/DENSE_RANK - 排序 复制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;1.2.3.4.5.6.7. FIRST_VALUE/LAST_VALUE - 首尾值 复制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;1.2.3.4.5.6.7.8. LAG/LEAD - 前后行 复制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;1.2.3.4.5.6.7. NTILE - 分组 复制SELECT name, salary, NTILE(4) OVER (ORDER BY salary) as quartile FROM employees;1.2.3.4.5.

6. JSON函数(MySQL 5.7+)

JSON_EXTRACT - 提取JSON值 复制SELECT JSON_EXTRACT({"name": "John", "age": 30}, $.name); -- "John"1. JSON_OBJECT - 创建JSON对象 复制SELECT JSON_OBJECT(name, John, age, 30);1. JSON_ARRAY - 创建JSON数组 复制SELECT JSON_ARRAY(1, 2, 3, 4, 5);1. JSON_CONTAINS - 检查JSON包含 复制SELECT JSON_CONTAINS({"a": 1, "b": 2}, 1, $.a); -- 11.

7. 加密和安全函数

MD5 - MD5加密 复制-- MySQL & SQL Server SELECT MD5(password);1.2. SHA1/SHA2 - SHA加密 复制-- MySQL SELECT SHA1(password); SELECT SHA2(password, 256);1.2.3. ENCRYPT/DECRYPT - 加密解密 复制-- MySQL SET @key = secret_key; SET @encrypted = AES_ENCRYPT(text, @key); SELECT AES_DECRYPT(@encrypted, @key);1.2.3.4.

8. XML函数(SQL Server)

FOR XML PATH - 生成XML 复制SELECT name, age FROM employees FOR XML PATH(employee), ROOT(employees)1.2.3. XML数据类型方法 复制DECLARE @xml XML SET @xml = <root><child>value</child></root> SELECT @xml.value((/root/child)[1], varchar(50))1.2.3.

9. 正则表达式函数

REGEXP/RLIKE - 正则匹配(MySQL) 复制SELECT hello REGEXP ^h; -- 1 SELECT hello RLIKE l+; -- 11.2. REGEXP_LIKE - 正则匹配(Oracle) 复制SELECT * FROM employees WHERE REGEXP_LIKE(email, ^[A-Za-z]+@[A-Za-z]+\.[A-Za-z]{2,4}$);1.

10. 系统信息函数

VERSION - 数据库版本 复制-- MySQL SELECT VERSION(); -- SQL Server SELECT @@VERSION; -- Oracle SELECT * FROM V$VERSION;1.2.3.4.5.6.7.8. USER/CURRENT_USER - 当前用户 复制-- 所有数据库 SELECT USER; SELECT CURRENT_USER;1.2.3. DATABASE/DB_NAME - 当前数据库 复制-- MySQL SELECT DATABASE(); -- SQL Server SELECT DB_NAME();1.2.3.4.5.

11. 高级聚合函数

GROUPING SETS - 多维度聚合 复制SELECT department, location, COUNT(*) FROM employees GROUP BY GROUPING SETS ( (department, location), (department), (location), () );1.2.3.4.5.6.7.8. CUBE - 所有可能的高防服务器组合 复制SELECT department, location, COUNT(*) FROM employees GROUP BY CUBE (department, location);1.2.3. ROLLUP - 层次聚合 复制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);1.2.3.4.5.6.7. PIVOT - 行转列 复制-- SQL Server SELECT * FROM ( SELECT department, location, salary FROM employees ) AS SourceTable PIVOT ( AVG(salary) FOR location IN ([New York], [London], [Tokyo]) ) AS PivotTable;1.2.3.4.5.6.7.8.9.10.

12. 统计和数学函数

PERCENTILE_CONT/PERCENTILE_DISC - 百分位数 复制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;1.2.3.4. CORR - 相关系数 复制SELECT CORR(salary, performance_score) FROM employees;1.2. STDDEV/VARIANCE - 标准差和方差 复制SELECT department, AVG(salary) as avg_salary, STDDEV(salary) as salary_stddev, VARIANCE(salary) as salary_variance FROM employees GROUP BY department;1.2.3.4.5.6.7. FIRST/LAST - 组内第一个/最后一个值 复制-- 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;1.2.3.4.5.6.7.8.9.10.

13. 字符串模式匹配函数

LIKE模式匹配增强 复制-- 复杂LIKE模式 SELECT * FROM employees WHERE name LIKE [A-M]% -- SQL Server, 以A到M开头的名字 AND email LIKE %@__%.__%; -- 标准email模式1.2.3.4.5.

14. 条件和流程控制增强

CHOOSE - 索引选择 复制-- SQL Server SELECT CHOOSE(2, First, Second, Third); -- 返回 Second1.2. 复杂CASE表达式 复制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;1.2.3.4.5.6.7.8.9.10.

15. 表分析函数

PERCENT_RANK - 百分比排名 复制SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile FROM employees;1.2.3.4.5. CUME_DIST - 累积分布 复制SELECT name, salary, CUME_DIST() OVER (ORDER BY salary) as salary_distribution FROM employees;1.2.3.4.5.

16. 实用复合函数示例

年龄计算 复制-- 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;1.2.3.4.5.6.7.8.9.10. 工龄分析 复制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;1.2.3.4.5.6.7.8.9.10. 薪资分析 复制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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17. 考勤分析 复制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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23. 销售分析 复制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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.