1. IT分享知识网首页
  2. 开发数据
  3. mysql

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

老牧童 • • mysql, Oracle, 未分类 • 阅读 44

最强总结！SQL Server/MySQL/Oracle函数完全指南！最强总结 数据库开窗函数完全指南 今天给大家总结的是 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
  1. CHAR_LENGTH – 获取字符数(区别于字节长度)
-- MySQL & Oracle SELECT CHAR_LENGTH('你好'); -- 2
  1. 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. LEFT/RIGHT – 从左/右截取
-- MySQL & SQL Server SELECT LEFT('Hello World', 5); -- 'Hello' SELECT RIGHT('Hello World', 5); -- 'World'
  1. REPLACE – 替换字符串
-- 所有数据库通用 SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'
  1. STUFF – 字符串替换(SQL Server特有)
SELECT STUFF('Hello World', 1, 5, 'Hi'); -- 'Hi World'
  1. 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
  1. REVERSE – 反转字符串
-- 所有数据库 SELECT REVERSE('Hello'); -- 'olleH'
  1. SPACE – 生成空格字符串
-- SQL Server & MySQL SELECT 'Hello' + SPACE(1) + 'World'; -- 'Hello World'
  1. REPEAT/REPLICATE – 重复字符串
-- MySQL SELECT REPEAT('SQL', 3); -- 'SQLSQLSQL' -- SQL Server SELECT REPLICATE('SQL', 3); -- 'SQLSQLSQL'

1.2 高级字符串函数

  1. FORMAT – 格式化字符串
-- MySQL & SQL Server SELECT FORMAT(.789, 2); -- '123,456.79'
  1. 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'
  1. 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
  1. MOD – 取模
-- 所有数据库 SELECT MOD(10, 3); -- 1
  1. SQRT – 平方根
SELECT SQRT(16); -- 4
  1. SIGN – 获取数字符号
SELECT SIGN(-10); -- -1 SELECT SIGN(10); -- 1 SELECT SIGN(0); -- 0

2.2 高级数学函数

  1. LOG/LOG10/LN – 对数运算
SELECT LOG(10, 100); -- 2 SELECT LOG10(100); -- 2 SELECT LN(2.7); -- 0.993
  1. EXP – 指数运算
SELECT EXP(1); -- 2.9045
  1. 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;
  1. CURDATE/CURRENT_DATE – 当前日期
-- MySQL SELECT CURDATE(); -- Oracle & SQL Server SELECT CURRENT_DATE;
  1. CURTIME/CURRENT_TIME – 当前时间
-- MySQL SELECT CURTIME(); -- Oracle & SQL Server SELECT CURRENT_TIME;

3.2 日期时间处理

  1. 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. 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. 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. 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');
  1. IFNULL/ISNULL/NVL – NULL值处理
-- MySQL SELECT IFNULL(NULL, 'Default'); -- SQL Server SELECT ISNULL(NULL, 'Default'); -- Oracle SELECT NVL(NULL, 'Default') FROM DUAL;
  1. NULLIF – 相等返回NULL
SELECT NULLIF(10, 10); -- NULL SELECT NULLIF(10, 20); -- 10
  1. 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;
  1. 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. 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. 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"
  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'); -- 1

7. 加密和安全函数

  1. MD5 – MD5加密
-- MySQL & SQL Server SELECT MD5('password');
  1. SHA1/SHA2 – SHA加密
-- MySQL SELECT SHA1('password'); SELECT SHA2('password', 256);
  1. 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')
  1. 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
  1. 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;
  1. USER/CURRENT_USER – 当前用户
-- 所有数据库 SELECT USER; SELECT CURRENT_USER;
  1. 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), () );
  1. CUBE – 所有可能的组合
SELECT department, location, COUNT(*) FROM employees GROUP BY CUBE (department, location);
  1. 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. 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;
  1. CORR – 相关系数
SELECT CORR(salary, performance_score) FROM employees;
  1. 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. 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'
  1. 复杂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;
  1. 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;
  1. 工龄分析
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. 薪资分析
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. 考勤分析
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. 销售分析
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;

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://yundeesoft.com/123995.html

赞 (0)
老牧童老牧童
0
上一篇 2024-11-22 13:33
下一篇 2024-11-22 13:45

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注微信