最强总结！SQL Server/MySQL/Oracle函数完全指南！

欢迎大家来到IT世界,在知识的湖畔探索吧!

最强总结！数据库开窗函数完全指南！！

今天给大家总结的是SQL Server/MySQL/Oracle这三个关系数据库的函数内容，包含常用和不常用的。

这些总结都是此前整理好后保存的，最近集中发布，觉得有帮助，记得三连（点赞+转发+在看），笔者才会更有动力继续发布。此外，大家也可以留言需要哪方面的总结。

1. 字符串函数
2. 数值函数
3. 日期时间函数
4. 条件和控制函数
5. 窗口函数
6. JSON函数(MySQL 5.7+)
7. 加密和安全函数
8. XML函数(SQL Server)
9. 正则表达式函数
10. 系统信息函数
11. 高级聚合函数
12. 统计和数学函数
13. 字符串模式匹配函数
14. 条件和流程控制增强
15. 表分析函数
16. 实用复合函数示例

1. 字符串函数

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

欢迎大家来到IT世界,在知识的湖畔探索吧!

2. CHAR_LENGTH – 获取字符数(区别于字节长度)

欢迎大家来到IT世界,在知识的湖畔探索吧!-- MySQL & Oracle SELECT CHAR_LENGTH('你好'); -- 2 

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

4. LEFT/RIGHT – 从左/右截取

欢迎大家来到IT世界,在知识的湖畔探索吧!-- MySQL & SQL Server SELECT LEFT('Hello World', 5); -- 'Hello' SELECT RIGHT('Hello World', 5); -- 'World' 

5. REPLACE – 替换字符串

-- 所有数据库通用 SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL' 

6. STUFF – 字符串替换(SQL Server特有)

SELECT STUFF('Hello World', 1, 5, 'Hi'); -- 'Hi World' 

7. 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'); -- 7 

8. REVERSE – 反转字符串

-- 所有数据库 SELECT REVERSE('Hello'); -- 'olleH' 

9. SPACE – 生成空格字符串

-- SQL Server & MySQL SELECT 'Hello' + SPACE(1) + 'World'; -- 'Hello World' 

10. REPEAT/REPLICATE – 重复字符串

-- MySQL SELECT REPEAT('SQL', 3); -- 'SQLSQLSQL' -- SQL Server SELECT REPLICATE('SQL', 3); -- 'SQLSQLSQL' 

1.2 高级字符串函数

11. FORMAT – 格式化字符串

-- MySQL & SQL Server SELECT FORMAT(.789, 2); -- '123,456.79' 

12. 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); -- 'a' 

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

2. 数值函数

2.1 基础数学函数

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.45 

2. MOD – 取模

-- 所有数据库 SELECT MOD(10, 3); -- 1 

3. SQRT – 平方根

SELECT SQRT(16); -- 4 

4. SIGN – 获取数字符号

SELECT SIGN(-10); -- -1 SELECT SIGN(10); -- 1 SELECT SIGN(0); -- 0 

2.2 高级数学函数

5. LOG/LOG10/LN – 对数运算

SELECT LOG(10, 100); -- 2 SELECT LOG10(100); -- 2 SELECT LN(2.7); -- 0.993 

6. EXP – 指数运算

SELECT EXP(1); -- 2.9045 

7. RAND/RANDOM – 随机数

-- MySQL & SQL Server SELECT RAND(); -- Oracle SELECT DBMS_RANDOM.VALUE FROM DUAL; 

3. 日期时间函数

3.1 获取日期时间

1. NOW/GETDATE/SYSDATE – 当前日期时间

-- MySQL SELECT NOW(); -- SQL Server SELECT GETDATE(); -- Oracle SELECT SYSDATE FROM DUAL; 

2. CURDATE/CURRENT_DATE – 当前日期

-- MySQL SELECT CURDATE(); -- Oracle & SQL Server SELECT CURRENT_DATE; 

3. CURTIME/CURRENT_TIME – 当前时间

-- MySQL SELECT CURTIME(); -- Oracle & SQL Server SELECT CURRENT_TIME; 

3.2 日期时间处理

4. 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'); 

5. DATE_FORMAT/FORMAT – 日期格式化

-- MySQL SELECT DATE_FORMAT('2024-03-12', '%Y年%m月%d日'); -- '2024年03月12日' -- SQL Server SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日'); 

6. 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'); 

7. LAST_DAY – 获取月末日期

-- MySQL & Oracle SELECT LAST_DAY('2024-03-12'); -- '2024-03-31' 

4. 条件和控制函数

1. IF/IIF – 条件判断

-- MySQL SELECT IF(1 > 0, 'True', 'False'); -- SQL Server SELECT IIF(1 > 0, 'True', 'False'); 

2. IFNULL/ISNULL/NVL – NULL值处理

-- MySQL SELECT IFNULL(NULL, 'Default'); -- SQL Server SELECT ISNULL(NULL, 'Default'); -- Oracle SELECT NVL(NULL, 'Default') FROM DUAL; 

3. NULLIF – 相等返回NULL

SELECT NULLIF(10, 10); -- NULL SELECT NULLIF(10, 20); -- 10 

4. GREATEST/LEAST – 最大最小值

-- MySQL & Oracle SELECT GREATEST(1, 2, 3, 4, 5); -- 5 SELECT LEAST(1, 2, 3, 4, 5); -- 1 

5. 窗口函数

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

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

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

4. NTILE – 分组

SELECT name, salary, NTILE(4) OVER (ORDER BY salary) as quartile FROM employees; 

6. JSON函数(MySQL 5.7+)

1. JSON_EXTRACT – 提取JSON值

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- "John" 

2. JSON_OBJECT – 创建JSON对象

SELECT JSON_OBJECT('name', 'John', 'age', 30); 

3. JSON_ARRAY – 创建JSON数组

SELECT JSON_ARRAY(1, 2, 3, 4, 5); 

4. JSON_CONTAINS – 检查JSON包含

SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a'); -- 1 

7. 加密和安全函数

1. MD5 – MD5加密

-- MySQL & SQL Server SELECT MD5('password'); 

2. SHA1/SHA2 – SHA加密

-- MySQL SELECT SHA1('password'); SELECT SHA2('password', 256); 

3. ENCRYPT/DECRYPT – 加密解密

-- MySQL SET @key = 'secret_key'; SET @encrypted = AES_ENCRYPT('text', @key); SELECT AES_DECRYPT(@encrypted, @key); 

8. XML函数(SQL Server)

1. FOR XML PATH – 生成XML

SELECT name, age FROM employees FOR XML PATH('employee'), ROOT('employees') 

2. XML数据类型方法

DECLARE @xml XML SET @xml = '<root><child>value</child></root>' SELECT @xml.value('(/root/child)[1]', 'varchar(50)') 

9. 正则表达式函数

1. REGEXP/RLIKE – 正则匹配(MySQL)

SELECT 'hello' REGEXP '^h'; -- 1 SELECT 'hello' RLIKE 'l+'; -- 1 

2. REGEXP_LIKE – 正则匹配(Oracle)

SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Za-z]+@[A-Za-z]+\.[A-Za-z]{2,4}#39;); 

10. 系统信息函数

1. VERSION – 数据库版本

-- MySQL SELECT VERSION(); -- SQL Server SELECT @@VERSION; -- Oracle SELECT * FROM V$VERSION; 

2. USER/CURRENT_USER – 当前用户

-- 所有数据库 SELECT USER; SELECT CURRENT_USER; 

3. DATABASE/DB_NAME – 当前数据库

-- MySQL SELECT DATABASE(); -- SQL Server SELECT DB_NAME(); 

11. 高级聚合函数

1. GROUPING SETS – 多维度聚合

SELECT department, location, COUNT(*) FROM employees GROUP BY GROUPING SETS ( (department, location), (department), (location), () ); 

2. CUBE – 所有可能的组合

SELECT department, location, COUNT(*) FROM employees GROUP BY CUBE (department, location); 

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

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

12. 统计和数学函数

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

2. CORR – 相关系数

SELECT CORR(salary, performance_score) FROM employees; 

3. STDDEV/VARIANCE – 标准差和方差

SELECT department, AVG(salary) as avg_salary, STDDEV(salary) as salary_stddev, VARIANCE(salary) as salary_variance FROM employees GROUP BY department; 

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

13. 字符串模式匹配函数

1. LIKE模式匹配增强

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

14. 条件和流程控制增强

1. CHOOSE – 索引选择

-- SQL Server SELECT CHOOSE(2, 'First', 'Second', 'Third'); -- 返回 'Second' 

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; 

15. 表分析函数

1. PERCENT_RANK – 百分比排名

SELECT name, salary, PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile FROM employees; 

2. CUME_DIST – 累积分布

SELECT name, salary, CUME_DIST() OVER (ORDER BY salary) as salary_distribution FROM employees; 

16. 实用复合函数示例

1. 年龄计算

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

2. 工龄分析

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; 

3. 薪资分析

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; 

4. 考勤分析

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; 

5. 销售分析

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;