最强总结！SQL Server/MySQL/Oracle函数完全指南
今天给大家总结的是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.
复制
-- MySQL & Oracle
SELECT CHAR_LENGTH(你好); -- 21.2.
复制
-- 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.
复制
-- MySQL & SQL Server
SELECT LEFT(Hello World, 5); -- Hello
SELECT RIGHT(Hello World, 5); -- World1.2.3.
复制
-- 所有数据库通用
SELECT REPLACE(Hello World, World, SQL); -- Hello SQL1.2.
复制
SELECT STUFF(Hello World, 1, 5, Hi); -- Hi World1.
复制
-- 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.
复制
-- 所有数据库
SELECT REVERSE(Hello); -- olleH1.2.
复制
-- SQL Server & MySQL
SELECT Hello + SPACE(1) + World; -- Hello World1.2.
复制
-- 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.
复制
-- SQL Server
SELECT value FROM STRING_SPLIT(a,b,c, ,);
-- MySQL
SELECT SUBSTRING_INDEX(a,b,c, ,, 1); -- a1.2.3.4.5.
复制
-- 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.
复制
-- 所有数据库
SELECT MOD(10, 3); -- 11.2.
复制
SELECT SQRT(16); -- 41.
复制
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.
复制
SELECT EXP(1); -- 2.7182818284590451.
复制
-- 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.
复制
-- MySQL
SELECT CURDATE();
-- Oracle & SQL Server
SELECT CURRENT_DATE;1.2.3.4.5.
复制
-- 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.
复制
-- 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.
复制
-- 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.
复制
-- 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.
复制
-- 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.
复制
SELECT NULLIF(10, 10); -- NULL
SELECT NULLIF(10, 20); -- 101.2.
复制
-- 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.
复制
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.
复制
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.
复制
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.
复制
SELECT JSON_OBJECT(name, John, age, 30);1.
复制
SELECT JSON_ARRAY(1, 2, 3, 4, 5);1.
复制
SELECT JSON_CONTAINS({"a": 1, "b": 2}, 1, $.a); -- 11.
7. 加密和安全函数
MD5 - MD5加密复制
-- MySQL & SQL Server
SELECT MD5(password);1.2.
复制
-- MySQL
SELECT SHA1(password);
SELECT SHA2(password, 256);1.2.3.
复制
-- 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.
复制
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.
复制
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.
复制
-- 所有数据库
SELECT USER;
SELECT CURRENT_USER;1.2.3.
复制
-- 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.
复制
SELECT department, location, COUNT(*)
FROM employees
GROUP BY CUBE (department, location);1.2.3.
复制
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.
复制
-- 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.
复制
SELECT CORR(salary, performance_score)
FROM employees;1.2.
复制
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.
复制
-- 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.
复制
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.
复制
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.
THE END
