最強(qiáng)總結(jié)！SQL Server/MySQL/Oracle函數(shù)完全指南
作者：李岳
今天給大家總結(jié)的是SQL Server/MySQL/Oracle這三個(gè)關(guān)系數(shù)據(jù)庫的函數(shù)內(nèi)容，包含常用和不常用的。
今天給大家總結(jié)的是SQL Server/MySQL/Oracle這三個(gè)關(guān)系數(shù)據(jù)庫的函數(shù)內(nèi)容，包含常用和不常用的。
1. 字符串函數(shù)
1.1 基礎(chǔ)字符串函數(shù)
- LENGTH/LEN/LENGTH - 獲取字符串長(zhǎng)度
-- MySQL
SELECT LENGTH('Hello World'); -- 11
-- SQL Server
SELECT LEN('Hello World'); -- 11
-- Oracle
SELECT LENGTH('Hello World') FROM DUAL; -- 11- CHAR_LENGTH - 獲取字符數(shù)(區(qū)別于字節(jié)長(zhǎng)度)
-- MySQL & Oracle
SELECT CHAR_LENGTH('你好'); -- 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;- LEFT/RIGHT - 從左/右截取
-- MySQL & SQL Server
SELECT LEFT('Hello World', 5); -- 'Hello'
SELECT RIGHT('Hello World', 5); -- 'World'- REPLACE - 替換字符串
-- 所有數(shù)據(jù)庫通用
SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'- STUFF - 字符串替換(SQL Server特有)
SELECT STUFF('Hello World', 1, 5, 'Hi'); -- 'Hi World'- 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- REVERSE - 反轉(zhuǎn)字符串
-- 所有數(shù)據(jù)庫
SELECT REVERSE('Hello'); -- 'olleH'- SPACE - 生成空格字符串
-- SQL Server & MySQL
SELECT 'Hello' + SPACE(1) + 'World'; -- 'Hello World'- REPEAT/REPLICATE - 重復(fù)字符串
-- MySQL
SELECT REPEAT('SQL', 3); -- 'SQLSQLSQL'
-- SQL Server
SELECT REPLICATE('SQL', 3); -- 'SQLSQLSQL'1.2 高級(jí)字符串函數(shù)
- FORMAT - 格式化字符串
-- MySQL & SQL Server
SELECT FORMAT(123456.789, 2); -- '123,456.79'- 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'- 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. 數(shù)值函數(shù)
2.1 基礎(chǔ)數(shù)學(xué)函數(shù)
- ROUND/TRUNC/TRUNCATE - 截?cái)?/li>
-- 所有數(shù)據(jù)庫
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 - 取模
-- 所有數(shù)據(jù)庫
SELECT MOD(10, 3); -- 1- SQRT - 平方根
SELECT SQRT(16); -- 4- SIGN - 獲取數(shù)字符號(hào)
SELECT SIGN(-10); -- -1
SELECT SIGN(10); -- 1
SELECT SIGN(0); -- 02.2 高級(jí)數(shù)學(xué)函數(shù)
- LOG/LOG10/LN - 對(duì)數(shù)運(yùn)算
SELECT LOG(10, 100); -- 2
SELECT LOG10(100); -- 2
SELECT LN(2.7); -- 0.993- EXP - 指數(shù)運(yùn)算
SELECT EXP(1); -- 2.718281828459045- RAND/RANDOM - 隨機(jī)數(shù)
-- MySQL & SQL Server
SELECT RAND();
-- Oracle
SELECT DBMS_RANDOM.VALUE FROM DUAL;3. 日期時(shí)間函數(shù)
3.1 獲取日期時(shí)間
- NOW/GETDATE/SYSDATE - 當(dāng)前日期時(shí)間
-- MySQL
SELECT NOW();
-- SQL Server
SELECT GETDATE();
-- Oracle
SELECT SYSDATE FROM DUAL;- CURDATE/CURRENT_DATE - 當(dāng)前日期
-- MySQL
SELECT CURDATE();
-- Oracle & SQL Server
SELECT CURRENT_DATE;- CURTIME/CURRENT_TIME - 當(dāng)前時(shí)間
-- MySQL
SELECT CURTIME();
-- Oracle & SQL Server
SELECT CURRENT_TIME;3.2 日期時(shí)間處理
- 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');- DATE_FORMAT/FORMAT - 日期格式化
-- MySQL
SELECT DATE_FORMAT('2024-03-12', '%Y年%m月%d日'); -- '2024年03月12日'
-- SQL Server
SELECT FORMAT(GETDATE(), 'yyyy年MM月dd日');- 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');- LAST_DAY - 獲取月末日期
-- MySQL & Oracle
SELECT LAST_DAY('2024-03-12'); -- '2024-03-31'4. 條件和控制函數(shù)
- IF/IIF - 條件判斷
-- MySQL
SELECT IF(1 > 0, 'True', 'False');
-- SQL Server
SELECT IIF(1 > 0, 'True', 'False');- IFNULL/ISNULL/NVL - NULL值處理
-- MySQL
SELECT IFNULL(NULL, 'Default');
-- SQL Server
SELECT ISNULL(NULL, 'Default');
-- Oracle
SELECT NVL(NULL, 'Default') FROM DUAL;- NULLIF - 相等返回NULL
SELECT NULLIF(10, 10); -- NULL
SELECT NULLIF(10, 20); -- 10- GREATEST/LEAST - 最大最小值
-- MySQL & Oracle
SELECT GREATEST(1, 2, 3, 4, 5); -- 5
SELECT LEAST(1, 2, 3, 4, 5); -- 15. 窗口函數(shù)
- 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;- 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;- 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;- NTILE - 分組
SELECT
name,
salary,
NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;6. JSON函數(shù)(MySQL 5.7+)
- JSON_EXTRACT - 提取JSON值
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- "John"- JSON_OBJECT - 創(chuàng)建JSON對(duì)象
SELECT JSON_OBJECT('name', 'John', 'age', 30);- JSON_ARRAY - 創(chuàng)建JSON數(shù)組
SELECT JSON_ARRAY(1, 2, 3, 4, 5);- JSON_CONTAINS - 檢查JSON包含
SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a'); -- 17. 加密和安全函數(shù)
- MD5 - MD5加密
-- MySQL & SQL Server
SELECT MD5('password');- SHA1/SHA2 - SHA加密
-- MySQL
SELECT SHA1('password');
SELECT SHA2('password', 256);- ENCRYPT/DECRYPT - 加密解密
-- MySQL
SET @key = 'secret_key';
SET @encrypted = AES_ENCRYPT('text', @key);
SELECT AES_DECRYPT(@encrypted, @key);8. XML函數(shù)(SQL Server)
- FOR XML PATH - 生成XML
SELECT name, age
FROM employees
FOR XML PATH('employee'), ROOT('employees')- XML數(shù)據(jù)類型方法
DECLARE @xml XML
SET @xml = '<root><child>value</child></root>'
SELECT @xml.value('(/root/child)[1]', 'varchar(50)')9. 正則表達(dá)式函數(shù)
- REGEXP/RLIKE - 正則匹配(MySQL)
SELECT 'hello' REGEXP '^h'; -- 1
SELECT 'hello' RLIKE 'l+'; -- 1- REGEXP_LIKE - 正則匹配(Oracle)
SELECT * FROM employees WHERE REGEXP_LIKE(email, '^[A-Za-z]+@[A-Za-z]+\.[A-Za-z]{2,4}$');10. 系統(tǒng)信息函數(shù)
- VERSION - 數(shù)據(jù)庫版本
-- MySQL
SELECT VERSION();
-- SQL Server
SELECT @@VERSION;
-- Oracle
SELECT * FROM V$VERSION;- USER/CURRENT_USER - 當(dāng)前用戶
-- 所有數(shù)據(jù)庫
SELECT USER;
SELECT CURRENT_USER;- DATABASE/DB_NAME - 當(dāng)前數(shù)據(jù)庫
-- MySQL
SELECT DATABASE();
-- SQL Server
SELECT DB_NAME();11. 高級(jí)聚合函數(shù)
- GROUPING SETS - 多維度聚合
SELECT department, location, COUNT(*)
FROM employees
GROUP BY GROUPING SETS (
(department, location),
(department),
(location),
()
);- CUBE - 所有可能的組合
SELECT department, location, COUNT(*)
FROM employees
GROUP BY CUBE (department, location);- 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);- PIVOT - 行轉(zhuǎn)列
-- 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. 統(tǒng)計(jì)和數(shù)學(xué)函數(shù)
- PERCENTILE_CONT/PERCENTILE_DISC - 百分位數(shù)
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 - 相關(guān)系數(shù)
SELECT CORR(salary, performance_score)
FROM employees;- STDDEV/VARIANCE - 標(biāo)準(zhǔn)差和方差
SELECT
department,
AVG(salary) as avg_salary,
STDDEV(salary) as salary_stddev,
VARIANCE(salary) as salary_variance
FROM employees
GROUP BY department;- FIRST/LAST - 組內(nèi)第一個(gè)/最后一個(gè)值
-- 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. 字符串模式匹配函數(shù)
- LIKE模式匹配增強(qiáng)
-- 復(fù)雜LIKE模式
SELECT * FROM employees
WHERE
name LIKE '[A-M]%' -- SQL Server, 以A到M開頭的名字
AND email LIKE '%@__%.__%'; -- 標(biāo)準(zhǔn)email模式14. 條件和流程控制增強(qiáng)
- CHOOSE - 索引選擇
-- SQL Server
SELECT CHOOSE(2, 'First', 'Second', 'Third'); -- 返回 'Second'- 復(fù)雜CASE表達(dá)式
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. 表分析函數(shù)
- PERCENT_RANK - 百分比排名
SELECT
name,
salary,
PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile
FROM employees;- CUME_DIST - 累積分布
SELECT
name,
salary,
CUME_DIST() OVER (ORDER BY salary) as salary_distribution
FROM employees;16. 實(shí)用復(fù)合函數(shù)示例
- 年齡計(jì)算
-- 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;- 工齡分析
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;- 薪資分析
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;- 考勤分析
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;- 銷售分析
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;責(zé)任編輯：武曉燕
來源：
SQL數(shù)據(jù)庫開發(fā)
