mysql有表函数吗 mysql中函数的作用

编程语言2024-05-05 00:31:21

函数

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性。在SQL中也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。

mysql有表函数吗 mysql中函数的作用,mysql有表函数吗 mysql中函数的作用_mysql有表函数吗,第1张

从函数定义的角度出发，可以将函数分成内置函数和自定义函数。在SQL语言中，同样也包括内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是根据自己的需要编写的

函数说明

在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即 DBMS 。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用 || 或者 + 来做拼接符，而在 MySQL 中的字符串拼接函数为 concat() 。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

MySQL 提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。

MySQL 提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取 MySQL 信息函数、聚合函数等。这里这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）

两种SQL函数

mysql有表函数吗 mysql中函数的作用,mysql有表函数吗 mysql中函数的作用_聚合函数_02,第2张

单行函数

操作数据对象
接受参数返回一个结果
只对一行进行变换
每行返回一个结果
可以嵌套
参数可以是一列或一个值

多行函数

多行函数又称聚合函数，对行的分组进行操作，对每个组给出一个结果；如果在查询中没有指定分组，则将查询结果看作一个组

聚合函数的类型主要有： avg 平均值、 count 计数、 max 最大值、 min 最小值、 sum 合计

所有聚合函数忽略空值（不会去处理），可以使用 ifnull 或 coalesce 函数来用一个值代替空值，可以使用distinct 使查询到的数据去重。

聚合函数不能互相嵌套使用！

语法 select 函数名称 () ; 或者 select 函数名称 ( 列名称，其它参数 ) from 表名称，在 mysql 中 from 子句不是必须的

名称	描述
abs()	返回绝对值
rand()	返回一个随机浮点值
+ -	尽量转换为数字进行计算，不能转为 0
ceiling()	返回不小于参数的最小整数值
floor()	返回不大于参数的最大整数值
length() 和 char_length()	以字节返回字串长度
substr(s,index,len)	返回从字符串s的index位置其len个字符
left( 被截取字串，截取长度 )	返回指定的最左边的字符数，SELECT LEFT('www.lanou3g.com',8)
right()	返回指定的最右边的字符数
trim()	删除前导和尾随空格
curdate()	返回当前日期
concat(列1，列2，...)	返回串联字符串
ifnull(列名称,'默认值')
format()	返回格式化到指定小数位数的数字， select format(salary,1); 四舍五入
date_format(d,f)	按照表达式f要求显示日期select date_format(now(), '%Y-%m-%d')
now()	返回当前日期和时间
uuid()	返回通用唯一标识符
user()	客户端提供的用户名和主机名
database()	返回默认（当前）数据库名称

CONCAT(A, B) – 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
LENGTH(str)获取以字节为单位的字符串长度；CHAR_LENGTH函数获取字符串的长度，以字符为单位计算长度
FORMAT(X, D)- 格式化数字X到D有效数字。

FOMRAT(N,D,locale); 将数字 N 格式化为格式，如 "#,###,###.##" ，舍入到 D 位小数。它返

回一个值作为字符串。其中 N 是要格式化的数字。 D 是要舍入的小数位数。 locale 是一个可选

参数，用于确定千个分隔符和分隔符之间的分组。如果省略 locale 操作符， MySQL 将默认使

用 en_US 。

SELECT FORMAT(14500.2018, 2); 返回 14,500.20

CURDATE(), CURTIME()- 返回当前日期或时间。
NOW（） – 将当前日期和时间作为一个值返回。另外MONTH（），DAY（），YEAR（）， WEEK（），WEEKDAY（） – 从日期值中提取给定数据。HOUR（），MINUTE（）， SECOND（） – 从时间值中提取给定数据。
DATEDIFF（A，B） – 确定两个日期之间的天数差异，通常用于计算年龄

SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate

ROUND(DATEDIFF(requiredDate, orderDate) / 365, 1) 四舍五入到 1 位小数

SUBTIMES（A，B） – 用于执行时间的减法运算。

SUBTIME('2018-10-31 23:59:59','0:1:1') 返回 2018-10-31 23:58:58

FROM_DAYS（INT） – 将整数天数转换为日期值。

TO_DAYS(date) 给出一个日期 date ，返回一个天数 ( 从 0 年开始的天数 )

SELECT TO_DAYS('1997-10-07'); -> 729669

FROM_DAYS(N) 给出一个天数 N ，返回一个 DATE 值

SELECT FROM_DAYS(729669); -> '1997-10-07'

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值

SELECT IFNULL(price,0.0);

聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数类型： AVG() 、 SUM() 、 MAX() 、 MIN() 、 COUNT()

可以对数值型数据使用AVG 和 SUM 函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数
COUNT(*)返回表中记录总数，适用于任意数据类型
COUNT(expr) 返回 expr 不为空的记录总数

问题 1 ：用 count(*) ， count(1) ， count( 列名 ) 谁好呢 ?

其实对于 MyISAM 引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb 引擎的表用 count(*),count(1) 直接读行数，复杂度是 O(n) ，因为 innodb 真的要去数一

遍。但好于具体的 count( 列名 )

问题：能不能使用 count( 列名 ) 替换 count(*)?

不要使用 count( 列名 ) 来替代 count(*) ， count(*) 是 SQL92 定义的标准统计行数的语法，

跟数据库无关，跟 NULL 和非 NULL 无关。

说明： count(*) 会统计值为某个列值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL

值的行

分组操作

可以使用 GROUP BY 子句将表中的数据分成若干组

SELECT column, group_function(column) FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

明确：WHERE一定放在FROM后面，如果有where则group by应该在where的后面
在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中

select sex,avg(salary) from tb_users group by sex 正确
select username,max(salary) from tb_users group by sex 语法错误

扩展：特殊用法。使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量

SELECT department_id,AVG(salary) FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY是互相排斥的

分组过滤HAVING

行已经被分组
使用了聚合函数
满足HAVING 子句中条件的分组将被显示
HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

SELECT department_id, MAX(salary) FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000

非法使用聚合函数：不能在WHERE子句中使用聚合函数

SELECT department_id, AVG(salary) FROM employees WHERE
AVG(salary) > 8000
GROUP BY department_id;

WHERE和HAVING的对比

区别1：WHERE可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与GROUP BY配合使用，可以把分组计算的函数和分组字段作为筛选条件。这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为，在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在GROUP BY 之后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是WHERE 无法完成的。另外，WHERE排除的记录不再包括在分组中。
区别2：如果需要通过连接从关联表中获取需要的数据，WHERE是先筛选后连接，而HAVING是先连接后筛选。这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用的资源就比较多，执行效率也较低。

开发中的选择： WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和HAVING 。包含分组统计函数的条件用 HAVING ，普通条件用 WHERE 。这样，我们就既利用了 WHERE 条件的高效快速，又发挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很大的差别。

SELECT的执行过程

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件 AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ...
JOIN ... ON 多表的连接条件
JOIN ... ON ...
WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC LIMIT ...,...
#其中：
1）from：从哪些表中筛选
2）on：关联多表查询时，去除笛卡尔积
3）where：从表中筛选的条件
4）group by：分组依据
5）having：在统计结果中再次筛选
6）order by：排序
7）limit：分页

查看全文

https://www.xamrdz.com/lan/5kq1941918.html

函数