Mysql函数

Mysql函数

  • MySQL 提供了丰富的内置函数，用于处理数据、执行计算、操作字符串、日期和时间等

数学函数

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

函数名 描述 示例
ABS(x) 返回 x 的绝对值 ABS(-10) → 10
CEIL(x) 返回大于或等于 x 的最小整数 CEIL(3.14) → 4
FLOOR(x) 返回小于或等于 x 的最大整数 FLOOR(3.14) → 3
ROUND(x, d) 将 x 四舍五入到 d 位小数 ROUND(3.14159, 2) → 3.14
MOD(x, y) 返回 x 除以 y 的余数 MOD(10, 3) → 1
POW(x, y) 返回 x 的 y 次方 POW(2, 3) → 8
SQRT(x) 返回 x 的平方根 SQRT(16) → 4
RAND() 返回 0 到 1 之间的随机数 RAND() → 0.123456

其他常用函数

  • TRUNCATE(x, d): 返回 x 截断到 d 位小数的值

    SELECT TRUNCATE(10.567, 2);  -- 返回 10.56

  • RAND(N): 使用种子 N 生成随机数。相同的种子会生成相同的随机数序列

    SELECT RAND(1);  -- 返回 0.40540353712197724

  • UUID()，生成一个36 个字符的通用唯一标识符（UUID）

    SELECT UUID();  -- 返回   6ccd780c-baba-1026-9564-5b8c656024db

  • SIGN(x): 返回 x 的符号。如果 x 为正数，返回 1；如果 x 为负数，返回 -1；如果 x 为 0，返回 0

    SELECT SIGN(-10);  -- 返回 -1
SELECT SIGN(10);   -- 返回 1
SELECT SIGN(0);    -- 返回 0

  • CONV(N, from_base, to_base): 将数字 N 从 from_base 进制转换为 to_base 进制

    SELECT CONV('A', 16, 10);  -- 返回 10

  • FORMAT(x, d): 将 x 格式化为带有 d 位小数的字符串

    SELECT FORMAT(12345.6789, 2);  -- 返回 '12,345.68'

字符串函数

字符串函数用于操作和处理文本数据。

函数名 描述 示例
CONCAT(s1, s2, ...) 连接多个字符串 CONCAT('Hello', ' ', 'World') → 'Hello World'
SUBSTRING(s, start, len) 从 s 中提取子字符串 SUBSTRING('Hello', 2, 3) → 'ell'
LENGTH(s) 返回字符串 s 的长度 LENGTH('Hello') → 5
UPPER(s) 将字符串 s 转换为大写 UPPER('hello') → 'HELLO'
LOWER(s) 将字符串 s 转换为小写 LOWER('HELLO') → 'hello'
TRIM(s) 去除字符串 s 两端的空格 TRIM(' hello ') → 'hello'
REPLACE(s, old, new) 将字符串 s 中的 old 替换为 new REPLACE('Hello', 'H', 'J') → 'Jello'
REVERSE(s) 反转字符串 s REVERSE('hello') → 'olleh'
LPAD(s, len, pad) 在字符串 s 左侧填充 pad 到指定长度 LPAD('hello', 10, '*') → '*****hello'
RPAD(s, len, pad) 在字符串 s 右侧填充 pad 到指定长度 RPAD('hello', 10, '*') → 'hello*****'

其他常用函数

  • CONCAT_WS(separator, str1, str2, ...): 使用指定的分隔符连接字符串

    SELECT CONCAT_WS('-', '2023', '10', '01');  -- 返回 '2023-10-01'

  • LEFT(str, length): 返回字符串 str 左边的 length 个字符

  • RIGHT(str, length): 返回字符串 str 右边的 length 个字符

    SELECT LEFT('MySQL', 2);  -- 返回 'My'
SELECT RIGHT('MySQL', 3);  -- 返回 'SQL'

  • REPEAT(str, count): 将字符串 str 重复 count 次

    SELECT REPEAT('MySQL ', 3);  -- 返回 'MySQL MySQL MySQL '

日期和时间函数

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

函数名 描述 示例
NOW() 返回当前日期和时间 NOW() → 2023-10-05 12:34:56
CURDATE() 返回当前日期 CURDATE() → 2023-10-05
CURTIME() 返回当前时间 CURTIME() → 12:34:56
DATE(d) 提取日期部分 DATE('2023-10-05 12:34:56') → '2023-10-05'
TIME(d) 提取时间部分 TIME('2023-10-05 12:34:56') → '12:34:56'
YEAR(d) 提取年份 YEAR('2023-10-05') → 2023
MONTH(d) 提取月份 MONTH('2023-10-05') → 10
DAY(d) 提取日 DAY('2023-10-05') → 5
DATE_ADD(d, INTERVAL n unit) 在日期 d 上添加时间间隔 DATE_ADD('2023-10-05', INTERVAL 1 DAY) → '2023-10-06'
DATEDIFF(d1, d2) 返回两个日期之间的天数差 DATEDIFF('2023-10-05', '2023-10-01') → 4

其他常用函数

  • DATE_FORMAT(date, format): 将日期 date 格式化为指定的字符串格式

    SELECT DATE_FORMAT('2023-10-01', '%Y/%m/%d');  -- 返回 '2023/10/01'
SELECT DATE_FORMAT(NOW(), '%W, %M %Y');  -- 返回 'Sunday, October 2023'

  • UNIX_TIMESTAMP([date]): 返回当前时间或指定日期时间的 UNIX 时间戳（从 1970-01-01 00:00:00 开始的秒数）

    SELECT UNIX_TIMESTAMP();  -- 返回当前时间的 UNIX 时间戳
SELECT UNIX_TIMESTAMP('2023-10-01 00:00:00');  -- 返回指定时间的 UNIX 时间戳

  • FROM_UNIXTIME(unix_timestamp, [format]): 将 UNIX 时间戳转换为日期时间，并可指定格式

    SELECT FROM_UNIXTIME(1696118400);  -- 返回 '2023-10-01 00:00:00'
SELECT FROM_UNIXTIME(1696118400, '%Y-%m-%d');  -- 返回 '2023-10-01'

  • 一些表示时间的字段

    • CURRENT_TIME: 返回当前时间，与CURTIME()相同
    • CURRENT_DATE: 返回当前日期，与CURDATE()相同
    • CURRENT_TIMESTAMP: 返回当前日期和时间，:与NOW()相同
    • LOCALTIME: 返回当前日期和时间，与 NOW() 相同

聚合函数

聚合函数用于对一组值进行计算并返回单个值。

函数名 描述 示例
COUNT(expr) 返回行数 COUNT(*) → 总行数
SUM(expr) 返回 expr 的总和 SUM(Salary) → 工资总和
AVG(expr) 返回 expr 的平均值 AVG(Salary) → 平均工资
MIN(expr) 返回 expr 的最小值 MIN(Salary) → 最低工资
MAX(expr) 返回 expr 的最大值 MAX(Salary) → 最高工资
GROUP_CONCAT(expr) 将 expr 的值连接成一个字符串 GROUP_CONCAT(Name) → 'Alice,Bob,Charlie'

条件函数

条件函数用于根据条件返回不同的值。

函数名 描述 示例
IF(expr, v1, v2) 如果 expr 为真，返回 v1，否则返回 v2 IF(1 > 0, 'Yes', 'No') → 'Yes'
IFNULL(expr, value) 如果 expr 为 NULL，返回 value IFNULL(NULL, 'Unknown') → 'Unknown'
CASE 多条件判断 见下文
COALESCE(v1, v2, ...) 返回第一个非 NULL 的值 COALESCE(NULL, 'A', 'B') → 'A'
NULLIF(expr1, expr2) 如果 expr1 等于 expr2，返回 NULL NULLIF(10, 10) → NULL
CAST(expr AS type) 将 expr 转换为指定类型 CAST('123' AS INT) → 123

CASE函数

  1. CASE 函数用于将一个表达式与多个值进行比较

    CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

SELECT Name, Salary,
       CASE Salary
           WHEN 5000 THEN 'Medium'
           WHEN 6000 THEN 'High'
           WHEN 3000 THEN 'Low'
           ELSE 'Unknown'
       END AS SalaryLevel
FROM Employees;
    • expression：需要比较的表达式
    • value1, value2, ...：与 expression 进行比较的值。
    • result1, result2, ...：当 expression 等于对应 value 时返回的结果。
    • default_result：如果没有匹配的值，则返回 default_result（可选）

  2. 搜索 CASE 函数

    • 搜索 CASE 函数用于更复杂的条件逻辑，每个条件可以独立定义
    CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

SELECT Name, Salary,
       CASE
           WHEN Salary > 5000 THEN 'High'
           WHEN Salary = 5000 THEN 'Average'
           WHEN Salary > 3000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryLevel
FROM Employees;

  3. 在 WHERE 子句中使用 CASE

    • CASE 函数可以用于动态生成过滤条件

      查询工资高于平均工资的员工，但如果平均工资低于 4000，则查询所有员工
SELECT Name, Salary
FROM Employees
WHERE Salary > CASE
                   WHEN (SELECT AVG(Salary) FROM Employees) < 4000 THEN 0
                   ELSE (SELECT AVG(Salary) FROM Employees)
               END;

  4. 在 ORDER BY 子句中使用 CASE

    • CASE 函数可以用于动态排序

      按工资水平排序，高工资在前，低工资在后
SELECT Name, Salary
FROM Employees
ORDER BY CASE
             WHEN Salary > 5000 THEN 1
             WHEN Salary > 3000 THEN 2
             ELSE 3
         END;

  5. 在 UPDATE 语句中使用 CASE

    • CASE 函数可以用于根据条件更新数据

      将工资低于 4000 的员工工资增加 10%，其他员工工资增加 5%
UPDATE Employees
SET Salary = CASE
                 WHEN Salary < 4000 THEN Salary * 1.10
                 ELSE Salary * 1.05
             END;

  6. 在 GROUP BY 中使用 CASE

    • CASE 函数可以用于动态分组

      按工资水平分组，统计每组的员工数量
SELECT CASE
           WHEN Salary > 5000 THEN 'High'
           WHEN Salary > 3000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryLevel,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY SalaryLevel;

case函数注意事项

  • 如果没有 ELSE 子句且没有条件匹配，CASE 函数会返回 NULL
  • CASE 函数会按顺序判断条件，一旦满足条件，就会返回对应的结果，后续条件不再判断

窗口函数

MySQL 中的窗口函数（Window Functions）是一种高级功能，允许在查询结果集的子集（称为“窗口”）上执行计算。窗口函数不会将多行合并为一行，而是为每一行返回一个计算结果。

基本语法

function_name(expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]
)
  • function_name: 窗口函数的名称，如 ROW_NUMBER()、RANK()、SUM() 等。
  • OVER: 定义窗口的范围。
  • PARTITION BY: 将数据分组，类似于 GROUP BY，但不会将多行合并为一行。
  • ORDER BY: 指定窗口内的排序方式
  • frame_clause: 定义窗口的框架（即计算范围），例如 ROWS BETWEEN ... AND ...

常用窗口函数

  1. 排名函数，

    • ROW_NUMBER(): 为每一行分配一个唯一的序号（从 1 开始）

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

    • RANK(): 为每一行分配一个排名，排名相同的行会有相同的值，后续排名会跳过

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

    • DENSE_RANK(): 类似于 RANK()，但排名不会跳过

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

  2. 聚合函数作为窗口函数

    • SUM(): 计算窗口内的总和

      SELECT 
    name, salary,
    SUM(salary) OVER (ORDER BY salary) AS cumulative_salary
FROM employees;

    • AVG(): 计算窗口内的平均值，MIN() 和 MAX(): 计算窗口内的最小值和最大值

posted @ 2025-03-17 21:41  QAQ001  阅读(85)  评论(0)    收藏  举报