MySQL SELECT 查询（三）：查询常用函数大全

MySQL SELECT 查询（三）：查询常用函数大全

1. 单行函数

---

单行函数是 SQL 中一类重要的函数，它们可以对单行数据进行处理，并返回单个结果。单行函数可以嵌套使用，并提供灵活的数据处理能力。

1.1 定义

• 只对单行数据进行操作，每行返回一个结果。
• 参数可以是列名、表达式或常量。
• 可以嵌套使用，形成更复杂的表达式。

1.2 数值函数

数值函数用于处理数值数据，提供各种数学运算功能。

函数	用法	说明
ABS()	返回指定数值的绝对值	例如：ABS(-5) 返回 5
SIG()	返回指定数值的符号值	例如：SIG(-5) 返回 -1
PI()	返回圆周率 π 的值	例如：PI() 返回 3.141592653589793
CEIL()/CEILING()	返回大于等于指定数值的最小整数	例如：CEIL(3.2) 返回 4
FLOOR()	返回小于等于指定数值的最大整数	例如：FLOOR(3.2) 返回 3
MOD()	返回两个数值相除的余数	例如：MOD(10, 3) 返回 1
RAND()	返回一个 0 到 1 之间的随机浮点数	例如：RAND() 返回一个介于 0 到 1 之间的随机浮点数
RAND(因子)	根据指定的因子生成随机数	例如：RAND(2) 返回一个介于 0 到 0.5 之间的随机浮点数
ROUND()	将数值四舍五入到指定的小数位数	例如：ROUND(3.14159, 2) 返回 3.14
ROUND(数值, 保留小数位)	将数值四舍五入到指定的小数位数	例如：ROUND(3.14159, 2) 返回 3.14
TRUNCATE(数值, 截断位)	将数值截断到指定的小数位数	例如：TRUNCATE(3.14159, 2) 返回 3.14

1.3 三角函数

三角函数用于处理角度和弧度，并返回相应的三角函数值。

函数	用法	说明
RADIANS()	将角度转换为弧度	例如：RADIANS(45) 返回 0.7853981633974483
DEGREES()	将弧度转换为角度	例如：DEGREES(0.7853981633974483) 返回 45
POW(x, y)	返回 x 的 y 次幂	例如：POW(2, 3) 返回 8
EXP(x)	返回 e 的 x 次幂	例如：EXP(1) 返回 2.718281828459045

1.4 进制转换函数

进制转换函数用于将数值从一种进制转换为另一种进制。

函数	用法	说明
BIN(x)	将十进制数值转换为二进制字符串	例如：BIN(10) 返回 “1010”
HEX(x)	将十进制数值转换为十六进制字符串	例如：HEX(10) 返回 “A”
OCT(x)	将十进制数值转换为八进制字符串	例如：OCT(10) 返回 “12”
CONV(x, f1, f2)	将 x 从 f1 进制转换为 f2 进制	例如：CONV(10, 10, 2) 返回 “1010”

1.5 字符串函数

字符串函数在 SQL 中用于处理文本数据，允许用户进行多种字符串操作。

函数	用法	说明
ASCII(char)	返回指定字符的 ASCII 码	例如：ASCII('A') 返回 65。
CHAR_LENGTH(string)	返回字符串的字符数	例如：CHAR_LENGTH('Hello') 返回 5。
LENGTH(string)	返回字符串的字节数	例如：LENGTH('Hello') 返回 5；若为多字节字符，则可能大于此值。
CONCAT(s1, s2, ...)	连接多个字符串	例如：CONCAT('Hello', ' ', 'World') 返回 ‘Hello World’。
CONCAT_WS(separator, s1, s2, ...)	用指定的分隔符连接多个字符串，忽略 NULL 值	例如：CONCAT_WS(',', 'Apple', 'Banana', NULL) 返回 ‘Apple,Banana’。
INSERT(str, idx, len, replacestr)	将 replacestr 插入到 str 中，从 idx 开始的 len 个字符位置替换	例如：INSERT('Hello', 2, 2, 'XX') 返回 ‘HXXlo’。
REPLACE(str, old_str, new_str)	将 str 中的所有 old_str 替换为 new_str	例如：REPLACE('Hello World', 'World', 'SQL') 返回 ‘Hello SQL’。
UPPER(str)	将字符串转换为大写	例如：UPPER('hello') 返回 ‘HELLO’。
LOWER(str)	将字符串转换为小写	例如：LOWER('HELLO') 返回 ‘hello’。
LEFT(str, len)	返回字符串左侧的 len 个字符	例如：LEFT('Hello', 3) 返回 ‘Hel’。
RIGHT(str, len)	返回字符串右侧的 len 个字符	例如：RIGHT('Hello', 3) 返回 ‘llo’。
LPAD(str, len, padstr)	如果字符串长度不足 len，则在左侧用 padstr 填充	例如：LPAD('SQL', 10, '-') 返回 ‘-------SQL’。
RPAD(str, len, padstr)	如果字符串长度不足 len，则在右侧用 padstr 填充	例如：RPAD('SQL', 10, '-') 返回 ‘SQL-------’。
TRIM(str)	去除字符串首尾的空格	例如：TRIM(' SQL ') 返回 ‘SQL’。
LTRIM(str)	去除字符串左侧的空格	例如：LTRIM(' SQL') 返回 ‘SQL’。
RTRIM(str)	去除字符串右侧的空格	例如：RTRIM('SQL ') 返回 ‘SQL’。
TRIM(s1 FROM s2)	去除 s2 首尾的 s1 字符	例如：TRIM('!#' FROM '!#Hello#!!#') 返回 ‘Hello’。
TRIM(LEADING s1 FROM s2)	去除 s2 开头处的 s1 字符	例如：TRIM(LEADING '!' FROM '!!!Hello') 返回 ‘Hello’。
TRIM(TRAILING s1 FROM s2)	去除 s2 结尾处的 s1 字符	例如：TRIM(TRAILING '#' FROM 'Hello###') 返回 ‘Hello’。
REPEAT(str, n)	重复 str n 次	例如：REPEAT('A', 3) 返回 ‘AAA’。
SPACE(n)	返回 n 个空格字符	例如：SPACE(5) 返回 ’ '（5个空格）。
STRCMP(s1, s2)	比较两个字符串的大小，返回值：<0（s1 < s2），0（s1 = s2），>0（s1 > s2）	例如：STRCMP('abc', 'xyz') 返回 -1。
SUBSTR(str, index, len)	返回 str 中从 index 开始的 len 个字符	例如：SUBSTR('Hello', 2, 3) 返回 ‘ell’。
LOCATE(substr, str)	返回 substr 在 str 中首次出现的位置，未找到返回 -1	例如：LOCATE('o', 'Hello World') 返回 5。
ELT(i, s1, s2, ...)	返回列表中第 i 项。i 从 1 开始	例如：ELT(2, 'one', 'two', 'three') 返回 ‘two’。
FIELD(s, s1, s2, ...)	返回字符串 s 在字符串列表中首次出现的位置	例如：FIELD('b', 'a', 'b', 'c') 返回 2。
FIND_IN_SET(s1, s2)	返回字符串 s1 在以逗号为分隔符的字符串 s2 中的位置	例如：FIND_IN_SET('b', 'a,b,c') 返回 2。
REVERSE(s)	反转字符串	例如：REVERSE('Hello') 返回 ‘olleH’。
NULLIF(s1, s2)	若 s1 与 s2 相等则返回 NULL，否者返回 s1	例如：NULLIF(1, 1) 返回 NULL，NULLIF(1, 2) 返回 1。

1.6 日期和时间函数

获取日期与时间的函数

• 当前日期和时间

  • CURDATE(), CURRENT_DATE(): 返回当前日期，格式为 YYYY-MM-DD。

  • NOW(), SYSDATE(): 返回当前日期和时间，格式为 YYYY-MM-DD HH:MM:SS。

  • CURTIME(): 返回当前时间，格式为 HH:MM:SS。

     SELECT CURDATE(), CURRENT_DATE(), NOW(), SYSDATE(), CURTIME();
    

• UTC 日期和时间

  • UTC_DATE: 返回当前 UTC 日期，格式为 YYYY-MM-DD。

  • UTC_TIME: 返回当前 UTC 时间，格式为 HH:MM:SS。

    SELECT UTC_DATE(), UTC_TIME();
    

日期与时间戳的转换函数

• 日期转换为时间戳

  • UNIX_TIMESTAMP(date): 返回日期的时间戳（自 1970-01-01 00:00:00 UTC 开始的秒数）。
  • UNIX_TIMESTAMP(): 返回当前日期的时间戳。

• 时间戳转换为日期

  • FROM_UNIXTIME(timestamp): 返回时间戳对应的日期和时间。

    SELECT UNIX_TIMESTAMP('2023-09-30'), FROM_UNIXTIME(1664355200);
    

获取月份、星期、星期数、天数

• 日期组成部分

  • YEAR(date): 返回日期的年份。

  • MONTH(date): 返回日期的月份。

  • DAY(date): 返回日期的天数。

  • HOUR(date): 返回日期的小时。

  • MINUTE(date): 返回日期的分钟。

  • SECOND(date): 返回日期的秒。

  • MONTHNAME(date): 返回日期的月份名称。

  • DAYNAME(date): 返回日期的星期名称。

  • WEEKDAY(date): 返回日期的星期索引，周一是 0。

    SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()), 	MINUTE(NOW()), SECOND(NOW());
    

日期的操作函数

• 日期加减

  • DATE_ADD(datetime, INTERVAL expr type): 在日期上增加一个指定的时间间隔。

  • DATE_SUB(datetime, INTERVAL expr type): 在日期上减少一个指定的时间间隔。

    SELECT DATE_ADD(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 	DAY);
    

日期的格式化

• 日期格式化

  • DATE_FORMAT(date, format): 返回按照指定格式格式化后的日期。

    SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 格式化日期时间为年-	月-日 时:分:秒
    

  • 格式化符号：

    • %Y: 四位数字的年份
    • %y: 两位数字的年份
    • %m: 月份（01-12）
    • %d: 月份中的日（01-31）
    • %H: 24小时制的小时（00-23）
    • %i: 分钟（00-59）
    • %s: 秒（00-59）

2. 聚合函数

---

聚合函数在 SQL 中用于对一组值执行计算，并返回单个结果。这些函数在执行数据分析、统计计算等任务时非常有用。

2.1 常用的聚合函数

函数	作用
AVG()	计算指定列的平均值
SUM()	计算指定列的总和
MAX()	返回指定列中的最大值
MIN()	返回指定列中的最小值
COUNT()	计算指定列中非 NULL 值的数量

2.2 COUNT() 函数详解

COUNT() 函数用于计算指定列中非 NULL 值的数量，它是统计记录总数的首选函数。

• 计算字段出现次数

  SELECT COUNT(1), COUNT(employee_id), COUNT(*), COUNT(2)
  FROM employees e;
  

  • 注意事项：
    • 使用 COUNT(1) 或 COUNT(*) 可以计算表中的记录总数，忽略 NULL 值。
    • 使用 COUNT(具体字段) 会计算该字段非 NULL 值的数量，可能会受到 NULL 值的影响。
    • 在新版本 MySQL 中，COUNT(1) 和 COUNT(*) 的效率高于 COUNT(具体字段)。

2.3 AVG() 与 SUM() 的关系

AVG() 函数计算平均值，等于 SUM() 函数除以 COUNT() 函数的结果。

SELECT AVG(salary), SUM(salary) / COUNT(salary),AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct),SUM(commission_pct) / 107
FROM employees e;

2.4 GROUP BY 子句

GROUP BY 子句用于对结果集进行分组，并与聚合函数结合使用。

• 分组查询示例

  SELECT department_id, AVG(salary)
  FROM employees e
  GROUP BY department_id;
  

• 连续分组

  SELECT department_id, job_id, AVG(salary)
  FROM employees e
  GROUP BY department_id, job_id;
  

• 注意事项：

  • 在 GROUP BY 子句中查询的字段必须在 GROUP BY 子句中列出。
  • GROUP BY 子句通常位于 WHERE 子句之后，ORDER BY 子句之前。

2.5 WITH ROLLUP 选项

WITH ROLLUP 选项可以在 GROUP BY 子句中使用，它会在分组的基础上添加一行，显示每个分组的总计和所有行的总计。

SELECT department_id, AVG(salary)
FROM employees e
GROUP BY department_id WITH ROLLUP;

• 注意事项：
  • 使用 WITH ROLLUP 后不能使用 ORDER BY 子句，因为它会与 ROLLUP 产生的总计行产生冲突。

2.6 HAVING 子句

HAVING 子句用于在 GROUP BY 分组之后对结果集进行过滤。

• 使用场景：

  • 当过滤条件包含聚合函数时，必须使用 HAVING 子句。
  • 通常与 GROUP BY 子句一起使用。

• 注意事项：

  • HAVING 子句的位置在 GROUP BY 子句之后。
  • 由于 HAVING 子句是在分组之后应用过滤，因此它的效率通常低于 WHERE 子句。
  • 在没有聚合函数的情况下，通常使用 WHERE 子句而不是 HAVING 子句。