苏州科建设交通学院网站,微博+wordpress,做海关授权的网站,管理系统的组成MySQL SELECT 查询#xff08;三#xff09;#xff1a;查询常用函数大全
1. 单行函数 单行函数是 SQL 中一类重要的函数#xff0c;它们可以对单行数据进行处理#xff0c;并返回单个结果。单行函数可以嵌套使用#xff0c;并提供灵活的数据处理能力。 1.1 定义
只对单…MySQL SELECT 查询三查询常用函数大全
1. 单行函数 单行函数是 SQL 中一类重要的函数它们可以对单行数据进行处理并返回单个结果。单行函数可以嵌套使用并提供灵活的数据处理能力。 1.1 定义
只对单行数据进行操作每行返回一个结果。参数可以是列名、表达式或常量。可以嵌套使用形成更复杂的表达式。
1.2 数值函数
数值函数用于处理数值数据提供各种数学运算功能。
函数用法说明ABS()返回指定数值的绝对值例如ABS(-5) 返回 5SIG()返回指定数值的符号值例如SIG(-5) 返回 -1PI()返回圆周率 π 的值例如PI() 返回 3.141592653589793CEIL()/CEILING()返回大于等于指定数值的最小整数例如CEIL(3.2) 返回 4FLOOR()返回小于等于指定数值的最大整数例如FLOOR(3.2) 返回 3MOD()返回两个数值相除的余数例如MOD(10, 3) 返回 1RAND()返回一个 0 到 1 之间的随机浮点数例如RAND() 返回一个介于 0 到 1 之间的随机浮点数RAND(因子)根据指定的因子生成随机数例如RAND(2) 返回一个介于 0 到 0.5 之间的随机浮点数ROUND()将数值四舍五入到指定的小数位数例如ROUND(3.14159, 2) 返回 3.14ROUND(数值, 保留小数位)将数值四舍五入到指定的小数位数例如ROUND(3.14159, 2) 返回 3.14TRUNCATE(数值, 截断位)将数值截断到指定的小数位数例如TRUNCATE(3.14159, 2) 返回 3.14
1.3 三角函数
三角函数用于处理角度和弧度并返回相应的三角函数值。
函数用法说明RADIANS()将角度转换为弧度例如RADIANS(45) 返回 0.7853981633974483DEGREES()将弧度转换为角度例如DEGREES(0.7853981633974483) 返回 45POW(x, y)返回 x 的 y 次幂例如POW(2, 3) 返回 8EXP(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)比较两个字符串的大小返回值0s1 s20s1 s20s1 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) 返回 NULLNULLIF(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 子句。
