mysql函数大全及举例_MySQL函数全面总结教程

MySQL函数全面汇总教程

1. MySQL 函数说明

『概念』：类似Java中的方法，在方法体中封装了一组逻辑语句，对外暴露的是方法名；

【好处】：Ⅰ隐藏实现细节；Ⅱ提高代码可重用性；

“Call”：函数名称（实际参数列表）[来自表]；

“功能”：Ⅰ它叫什么（功能名）；Ⅱ它起什么作用（功能功能）；

『分类』：Ⅰ单排功能；Ⅱ分组功能；

“什么是单行函数：”：它作用于表中的每一行记录，每条记录都得到一个结果；

“什么是聚合函数：”：作用于一行或多行，最终返回一个结果；

2.单行函数分类

角色功能；

数学函数；

日期函数；

其他功能：

过程控制功能；

3. 字符函数

1）（str）：获取参数值的字节数；

对于utf8字符集来说，一个英文字符占1个字节；一个汉字占3个字节；

对于gbk字符集来说，一个英文字符占1个字节；一个汉字占2个字节；

操作如下：

2）（str1，str2，…）：连接字符串；

操作如下：

3）upper(str)：将字符串中所有字母转换为大写；

操作如下：

4）lower(str)：将字符串中所有字母改为小写；

操作如下：

5）(str,start,len)：从start位置开始截取字符串，len表示要截取的长度；如果不指定len长度，则表示从start位置截取字符串到结尾；如果指定了len长度，则表示从start位置截取字符串到结尾。

操作如下：

6）instr(str, 待搜索子字符串)：返回子字符串第一次出现的索引。如果未找到，则返回 0。当待搜索子字符串存在于字符串中时：返回子字符串第一次出现的索引。当待搜索子字符串不存在时：返回 0。

操作如下：

7）trim(str)：删除字符串前后的空格；该函数只能删除字符串前后的空格，不能删除字符串中间的空格。

操作如下：

8）lpad(str,len,fill )：使用指定字符在字符串左侧填充至指定长度；

操作如下：

9）rpad(str,len,fill )：使用指定字符向右填充字符串至指定长度；

操作如下：

10) (str,, )：将字符串str中的子字符串替换为另一个字符串；

操作如下：

4. 数学函数

1）round(x, [保留的位数]): 四舍五入；四舍五入正数时：按正常计算方法四舍五入。四舍五入负数时：先把符号抛在一边，去掉负号后对正数进行四舍五入，然后再添加负号。

操作如下：

2）ceil(x)：向上舍入并返回大于或等于参数的最小整数。（函数）Excel中有函数。想象一下你家的天花板，把这个数字扔到天花板上。你要找的值就是大于或等于这个数字的最小整数。

操作如下：

3）floor（x）：向下取整，返回=60，中等：70-80，良好：80-90，优秀：>=90

操作如下：

select sc.c,cname,
max(score) 最高分,min(score) 最低分,avg(score) 平均分,
sum(case when score>60 then 1 else 0 end)/count(*) 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(*) 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(*) 优良率,
sum(case when score>=90 then 1 else 0 end)/count(*) 优秀率
from sc left join course 
on sc.c = course.c
group by sc.c;

结果如下：

8.聚合函数1）聚合函数的功能及分类；

① 聚集函数的作用；

用于统计目的，也称为聚合函数、统计函数或组函数。

② 聚合函数的分类；sum 为求和 avg 为平均值 max 为最大值 min 为最小值 count 为计算次数

2）聚合函数的简单使用

3）五个聚合函数传递的参数支持哪些数据类型？

MySQL不是强类型编程语言。也就是说，有些语句可能不会报错，但是执行结果没有意义，所以我们也会认为它们是错误的。

① 试验数据；

"建表语句"
create table test(
	id int primary key auto_increment,
	name varchar(20) not null,
	sal int,
	birth date)charset=utf8;
"插入数据"
insert into test(name,sal,birth) values 
("Zoo",6500,'1993.3.20'),
("Hobby",4000,'1997.6.10'),
("Aline",5500,'2000.5.1'),
("Bob",10000,'2008.10.1');

② sum()函数和avg()函数：传入整数/小数类型才有意义；

结论如下：

sum() 和 avg() 函数对于字符串类型和日期/时间类型的计算意义不大。因此，我们仅使用 sum() 和 avg() 函数来对小数类型和整数类型进行求和。

③ max()函数与min()函数：传入整数/小数类型、日期/时间类型比较有意义；

结论如下：

“整数/小数类型”传递给max()和min()，计算出最大值和最小值。“日期类型”传递给max()和min()，max()计算出的最大值是离我们最近的日期，min()计算出的最小值是离我们最远的日期。记住这一点。字符串类型传递给max()和min()，max()计算出的最大值按字母顺序显示，min()计算出的最小值也是按字母顺序显示，意义不大。

④ count()函数：可以传入任意数据类型，但遇到null要小心；

结论如下：

count() 函数可以传递任何数据类型来统计行数。

“但以下几个知识点需要特别注意”

首先我们看一下count(sal)和count(birth)，这两句话是什么意思呢？

计算 sal 和 birth 列中的行数。由于其中一条记录为空值，因此使用 count()

当函数计数时，空行将被忽略。

其次，count(*)是统计整个表的行数，这个行数肯定是原始数据正确的行数。

统计一下，只要整张表的某一行中某一列字段的值不为null，count(*)就会认为该行是1行。

整行都填充了空值，因此不需要插入该记录。

总结：

当字段列中没有空值时，“count(列字段) = count(*)”

当字段列有空值时，将使用“count(列字段)”。因此，如果要统计整个表的行数，请使用count(*)。

实际上，所有分组函数都会忽略空值，但是上面的 count() 函数在遇到空值时需要特别注意。

⑤ 要特别注意count()函数遇到空值时的情况；

结论如下：

对于 avg(sal) 的平均值，(6500+4000+5500+10000)/4=6500。

对于下面的sum()/count(*)计算平均值，(6500+4000+5500+10000)/5=5200。

仔细看上面的例子，有时候虽然某人的分数被记录为 null，但你仍然有 5 个人，所以你必须考虑如何使用适当的函数来实现你想要的结果。

⑥ count(1)和count(0)是什么意思？

不管是sum(1),sum(0),count(1),count(0),avg(1),avg(0)，原理都是一样的，相当于在原表上增加了新的列。

其次我们知道where后面跟着的是[逻辑值]，在使用where 1、where 0的时候，原理还是一样，相当于在原表上增加了一个新列。

我们只要记住MySQL中的“非零为真，0为假”就行了。也就是说，所有低于1的位置，你都可以用任何非零的数字来代替。

示意图如下：

进行测试：

⑦ count(*)计数的效率；

在存储引擎下，count(*) 效率更高。在存储引擎下，count(*) 和 count(1) 效率差不多，比 count(field) 稍微高效一些。总结：count(*) 更受欢迎。

4）聚合函数和group by的使用“最重要”；

这个知识点我们会在后面的知识点中讨论，这里我们只需要记住一句话：SQL语句中使用group by函数时，后面的字段必须是group by+使用聚合函数后的字段。