sql 常用内置函数盘点

---

一、前言

在SQL语句中，有许多内置函数可用于各种数据操作和计算。下面盘点常用的sql内置函数。

二、常用内置函数盘点

2.1 数值计算、字符串操作、日期处理

2.1.1 COUNT(): 统计行数

SELECT COUNT(*) FROM USER;

2.1.2 SUM(): 计算数值列的总和

SELECT SUM(score) FROM USER;

2.1.3 AVG(): 计算数值列的平均值

SELECT AVG(score) FROM USER;

2.1.4 MIN(): 返回列中的最小值

SELECT MIN(score) FROM USER;

2.1.5 MAX(): 返回列中的最大值

SELECT MAX(score) FROM USER;

2.1.6 LENGTH(): 返回字符串的长度

SELECT LENGTH(name) FROM USER;

2.1.7 SUBSTRING(): 提取字符串的一部分

SELECT SUBSTRING(name, 1, 3) FROM USER;

2.1.8 REPLACE(): 替换字符串中的子串

SELECT REPLACE(name, 'John', 'Jonathan') FROM USER;

2.1.9 UPPER(): 将字符串转换为大写

SELECT UPPER(name) FROM USER;

2.1.10 LOWER(): 将字符串转换为小写

SELECT LOWER(name) FROM USER;

2.2 数据操作和计算

2.2.1 CONCAT(): 连接两个或多个字符串

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM USER;

2.2.2 TRIM(): 去除字符串两端的空格或其他字符

SELECT TRIM('   Hello World!   ') AS trimmed_string;

2.2.3 ROUND(): 对数值进行四舍五入

SELECT ROUND(salary, 2) FROM USER;

2.2.4 CEIL(): 返回大于等于指定数值的最小整数

SELECT CEIL(123.45) AS ceiling_value;

2.2.5 FLOOR(): 返回小于等于指定数值的最大整数

SELECT FLOOR(123.45) AS floor_value;

2.2.5 NOW(): 返回当前的日期和时间

SELECT NOW() AS current_date_time;

2.2.6 DATE(): 从日期时间中提取日期部分

SELECT DATE(NOW()) AS current_date;

2.2.7 DATEDIFF(): 返回两个日期之间的天数差

SELECT DATEDIFF(NOW(), '2024-01-01') AS days_difference;

2.2.8 IFNULL(): 如果表达式的结果为NULL，则返回指定的值

SELECT IFNULL(middle_name, 'N/A') FROM USER;

2.2.9 COALESCE(): 返回第一个非NULL的表达式

SELECT COALESCE(middle_name, first_name, 'N/A') FROM USER;

2.3 数据类型转换、条件判断和其他常用操作

2.3.1 CAST(): 将一个表达式转换为另一种数据类型

SELECT CAST(score AS DECIMAL(10, 2)) FROM USER;

2.3.2 CONVERT(): 另一种用于数据类型转换的函数

SELECT CONVERT(VARCHAR, hire_date, 101) FROM USER;

2.3.3 ABS(): 返回数值的绝对值

SELECT ABS(-123.45) AS absolute_value;

2.3.4 POWER(): 返回一个数的指定次方

SELECT POWER(score, 2) FROM USER;

2.3.5 SQRT(): 返回一个数的平方根

SELECT SQRT(score) FROM USER;

2.3.6 MOD(): 返回两个数相除的余数

SELECT MOD(score, 100) FROM USER;

2.3.7 SIGN(): 返回数值的符号，负数返回-1，零返回0，正数返回1

SELECT SIGN(score) FROM USER;

2.3.8 RAND(): 返回一个0到1之间的随机数

SELECT RAND() AS random_number;

2.3.9 CASE: 基于条件返回不同的值

SELECT 
    name,
    CASE 
        WHEN score < 60 THEN 'Low'
        WHEN score BETWEEN 60 AND 80 THEN 'Medium'
        ELSE 'High'
    END AS score_level
FROM USER;

2.3.10 DECODE(): 另一种实现条件判断的函数，类似于CASE

SELECT 
    name,
    DECODE(score, 
           60, 'Low', 
           80, 'Medium', 
           'High') AS salary_level
FROM USER;

2.3.11 GREATEST(): 返回多个数值中的最大值

SELECT GREATEST(score, bonus, commission) FROM USER;

2.3.12 LEAST(): 返回多个数值中的最小值

SELECT LEAST(salary, bonus, commission) FROM employees;

2.3.13 IF(): 实现条件判断，如果条件为真则返回一个值，否则返回另一个值

SELECT 
    name,
    IF(salary > 50000, 'Above Average', 'Below Average') AS salary_status
FROM employees;

2.3.14 LEFT(): 从字符串的左边开始提取指定数量的字符

SELECT LEFT(name, 3) FROM employees;

2.3.15 RIGHT(): 从字符串的右边开始提取指定数量的字符

SELECT RIGHT(name, 3) FROM employees;

2.3.16 REVERSE(): 反转字符串

SELECT REVERSE(name) FROM USER;

2.3.17 CHAR_LENGTH(): 返回字符串中的字符数

SELECT CHAR_LENGTH(name) FROM USER;

2.3.18 POSITION(): 查找子串在字符串中的位置

SELECT POSITION('Smith' IN name) FROM employees;

2.3.19 LOCATE(): 查找子串在字符串中的位置，返回子串的位置

SELECT LOCATE('Smith', name) FROM USER;

2.3.20 GROUP_CONCAT(): 将一个组中的值连接成一个字符串

SELECT GROUP_CONCAT(name) FROM USER;

2.4 窗口函数、日期时间操作

2.4.1 ROW_NUMBER(): 窗口函数，用于为结果集中的每一行分配唯一的行号

SELECT 
    name, 
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

2.4.2 RANK(): 窗口函数，用于为结果集中的每一行分配排名，允许并列

SELECT 
    name, 
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

2.4.3 DENSE_RANK(): 窗口函数，类似于RANK()，但排名连续，没有间断

SELECT 
    name, 
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

2.4.4 NTILE(): 窗口函数，将结果集划分为指定数量的桶，并为每一行分配桶号

SELECT 
    name, 
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS USER
FROM employees;

2.4.5 LAG(): 窗口函数，用于访问前一行的值

SELECT 
    name, 
    salary,
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_salary
FROM employees;

2.4.6 LEAD(): 窗口函数，用于访问后一行的值

SELECT 
    name, 
    salary,
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

2.4.7 FIRST_VALUE(): 窗口函数，返回分区中的第一个值

SELECT 
    name, 
    salary,
    FIRST_VALUE(salary) OVER (ORDER BY salary DESC) AS first_salary
FROM employees;

2.4.8 LAST_VALUE(): 窗口函数，返回分区中的最后一个值

SELECT 
    name, 
    salary,
    LAST_VALUE(salary) OVER (ORDER BY salary DESC) AS last_salary
FROM employees;

2.4.9 DATE_ADD(): 向日期添加指定的时间间隔

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week;

2.4.10 DATE_SUB(): 从日期减去指定的时间间隔

SELECT DATE_SUB(NOW(), INTERVAL 7 DAY) AS last_week;

2.4.11 YEAR(): 从日期中提取年份部分

SELECT YEAR(hire_date) AS hire_year FROM employees;

2.4.12 MONTH(): 从日期中提取月份部分

SELECT MONTH(hire_date) AS hire_month FROM employees;

2.4.13 DAY(): 从日期中提取天部分

SELECT DAY(hire_date) AS hire_day FROM employees;

2.4.14 HOUR(): 从日期时间中提取小时部分

SELECT HOUR(NOW()) AS current_hour;

2.4.15 MINUTE(): 从日期时间中提取分钟部分

SELECT MINUTE(NOW()) AS current_minute;

2.4.16 SECOND(): 从日期时间中提取秒部分

SELECT SECOND(NOW()) AS current_second;

2.4.17 EXTRACT(): 从日期时间中提取指定的部分（年份、月份、天、小时、分钟、秒）

SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;

2.4.18 FORMAT(): 按照指定格式格式化日期或数值

SELECT FORMAT(salary, 2) AS formatted_salary FROM employees;

2.4.19 UNIX_TIMESTAMP(): 返回当前时间的Unix时间戳

SELECT UNIX_TIMESTAMP() AS current_timestamp;

2.4.20 FROM_UNIXTIME(): 将Unix时间戳转换为日期时间

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP()) AS current_datetime;