最強(qiáng)總結(jié)！SQL Server/MySQL/Oracle函數(shù)完全指南

作者：李岳 2024-11-13 09:16:13

今天給大家總結(jié)的是SQL Server/MySQL/Oracle這三個(gè)關(guān)系數(shù)據(jù)庫(kù)的函數(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ù)庫(kù)通用
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ù)庫(kù)
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ù)庫(kù)
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ù)庫(kù)
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);    -- 0

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

5. 窗口函數(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');  -- 1

7. 加密和安全函數(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ù)庫(kù)版本

-- MySQL
SELECT VERSION();

-- SQL Server
SELECT @@VERSION;

-- Oracle
SELECT * FROM V$VERSION;

USER/CURRENT_USER - 當(dāng)前用戶

-- 所有數(shù)據(jù)庫(kù)
SELECT USER;
SELECT CURRENT_USER;

DATABASE/DB_NAME - 當(dāng)前數(shù)據(jù)庫(kù)

-- 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ù)庫(kù)開發(fā)