函数

苓渊大约 4 分钟

聚合函数

聚合函数用于对一组数据进行计算并返回单一的结果。它们通常与 GROUP BY 子句一起使用。

COUNT()： 计算一组数据中非空值的数量。

SELECT COUNT(*) FROM employees;

SUM()： 计算一组数值列的总和。

SELECT SUM(salary) FROM employees WHERE department = 'Sales';

AVG()： 计算一组数值列的平均值。

SELECT AVG(salary) FROM employees;

MAX()： 返回一组数据中的最大值。

SELECT MAX(age) FROM employees;

MIN()： 返回一组数据中的最小值。

SELECT MIN(age) FROM employees;

字符串函数

字符串函数用于操作和转换字符串。

CONCAT()： 将多个字符串连接成一个字符串，可以使用 || 代替。

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

-- 将查询到的多列拼接成一列
SELECT 'full_name: ' || first_name || last_name full_name FROM user;

SUBSTRING()： 返回字符串的子字符串。

SELECT SUBSTRING('Hello World', 1, 5);  -- 输出 'Hello'

UPPER()： 将字符串转换为大写。

SELECT UPPER('hello');  -- 输出 'HELLO'

LOWER()： 将字符串转换为小写。

SELECT LOWER('HELLO');  -- 输出 'hello'

LENGTH()： 返回字符串的长度。

SELECT LENGTH('Hello World');  -- 输出 11

TRIM()： 去除字符串首尾的空格或指定字符。

SELECT TRIM('   Hello World   ');  -- 输出 'Hello World'

数值函数

数值函数用于执行数学运算。

ROUND()： 对数值进行四舍五入。

SELECT ROUND(123.456, 2);  -- 输出 123.46

ABS()： 返回数值的绝对值。

SELECT ABS(-123);  -- 输出 123

CEIL() / FLOOR()： 返回大于等于或小于等于数值的整数部分。

SELECT CEIL(123.45);   -- 输出 124
SELECT FLOOR(123.45);  -- 输出 123

MOD()： 返回除法运算的余数。

SELECT MOD(10, 3);  -- 输出 1

POWER()： 返回数值的指定次方。

SELECT POWER(2, 3);  -- 输出 8

日期和时间函数

日期和时间函数用于处理日期、时间数据。

NOW()： 返回当前的日期和时间。

SELECT NOW();  -- 输出当前时间

CURDATE()： 返回当前日期。

SELECT CURDATE();  -- 输出当前日期

CURTIME()： 返回当前时间。

SELECT CURTIME();  -- 输出当前时间

DATE()： 从日期时间中提取日期部分。

SELECT DATE('2024-10-16 12:34:56');  -- 输出 '2024-10-16'

YEAR() / MONTH() / DAY()： 分别提取日期中的年、月、日。

SELECT YEAR('2024-10-16');   -- 输出 2024
SELECT MONTH('2024-10-16');  -- 输出 10
SELECT DAY('2024-10-16');    -- 输出 16

DATEDIFF()： 返回两个日期之间的天数。

SELECT DATEDIFF('2024-12-31', '2024-01-01');  -- 输出 364

DATE_ADD()： 向日期添加指定的时间间隔。

SELECT DATE_ADD('2024-10-16', INTERVAL 10 DAY);  -- 输出 '2024-10-26'

DATE_SUB()： 从日期中减去指定的时间间隔。

SELECT DATE_SUB('2024-10-16', INTERVAL 5 DAY);  -- 输出 '2024-10-11'

系统函数

系统函数返回与数据库系统或当前会话相关的信息。

USER()： 返回当前会话的用户名。

SELECT USER();  -- 输出当前用户名

DATABASE()： 返回当前使用的数据库名。

SELECT DATABASE();  -- 输出当前数据库名

VERSION()： 返回数据库服务器的版本信息。

SELECT VERSION();  -- 输出 MySQL 版本信息

转换函数

转换函数用于将一种数据类型转换为另一种类型。

CAST()： 将一个值转换为指定的数据类型。

SELECT CAST(123 AS CHAR);  -- 输出 '123'

CONVERT()： 类似于 CAST()，也用于转换数据类型。

SELECT CONVERT('2024-10-16', DATE);  -- 输出日期 '2024-10-16'

COALESCE()： 返回第一个非空的表达式。

SELECT COALESCE(NULL, NULL, 'Hello', NULL);  -- 输出 'Hello'

nvl(column, value)： 空值转换函数，当 column 为空的时候返回 value，否则返回 column 的值，如果两个参数都为 NULL，则返回 NULL.

SELECT nvl(username, '游客') FROM user;

分析函数

可以在组内对数据进行排序，返回一个数字。

partition by 在分析函数中用来替代 group by.
row_number 返回连续的排位，不论值相等。
rank 具有相等值的排位相同，序数然后跳跃。
dense_rank 具有相等值的排位相同，序号是连续的。

-- 1、通过分析函数获得每个部门的最高工资
select e.*,
row_number()over(partition by deptno orderby sal) rn,
rank()over(partition by deptno order by sal) r,
dense_rank()over(partition by deptno order by sal) dr
from emp e;

-- 2、查询学员成绩，按成绩排序，并计算出名次

-- 2.1、要求不论成绩是否相同，名次是连续的序号
select estuid,examresult,examsubject,
 row_number()over(partition by examsubject order by examresult) 
from studentexam;

-- 2.2、要求成绩相等的排位相同，名次随后跳跃
select estuid,examresult,examsubject,
 rank()over(partition by examsubject order by examresult) 
from studentexam;

-- 2.3、要求成绩相等的排位相同，名次是连续的
select estuid,examresult,examsubject,
 dense_rank()over(partition by examsubject order by examresult) 
from studentexam;