MySQL中常见的函数

MySQL中的常见函数分为两大类：单行函数和分组函数（聚合函数，组函数，统计函数）。

第一大类：单行函数

1.字符函数

（1）length 获取参数值的字节个数
一个英文字符占1个字节，utf-8中一个中文字符占3个字节，在gbk中一个中文字符占两个字节。

例如：select length('张三丰hahaha')；

（2）concat 拼接字符串

例如：select concat(last_name,'_',first_name) as 姓名 from employees；

（3）upper、lower 将字符变大写和小写

例如：select concat(upper(last_name),lower(first_name)) as 姓名 from employees；

(4)substr、substring 截取字符串
注意：数据库中的索引是从1开始的

#例如：带两个参数的substr函数截取从指定索引后面的所有字符
select substr('李莫愁爱上了陆展元',7) as out_put；
#该条语句输出结果为:陆展元

#带三个参数的substr函数截取从指定索引处指定长度的字符
select substr('李莫愁爱上了陆展元',1,3) as out_put；
#该条语句输出结果为:李莫愁

#案例：姓名中首字符大写，其他字符小写，然后用_ 拼接显示出来
select concat(upper(substr(last_name,1,1)),'_',lower(substr(last_name,2))) as out_put;

（5）instr 返回字符串第一次出现的索引，如果找不到返回0

例如：select instr('杨不悔爱上了殷六侠','殷六侠') as out_put；
#输出结果为:7

例如：select instr('杨不悔殷六侠爱上了殷六侠','殷六侠') as out_put；
#输出结果为:4

例如：select instr('杨不悔殷六侠爱上了殷六侠','殷八侠') as out_put；
#输出结果为:0

（6）trim 去除制定字符前后两端的空格和指定字符

例如：select length(trim('    张翠山      ')) as out_put；
#在utf8字符编码的情况下，输出结果为:9

例如：select length(trim('a' from 'aaa张aaa翠山aaaaaaaaaaaa')) as out_put；
#在utf8字符编码的情况下，输出结果为:张aaa翠山

例如：select length(trim('aa' from 'aaa张aaa翠山aaaa')) as out_put；
#在utf8字符编码的情况下，输出结果为:a张aaa翠山

(7)lpad 用指定的字符左填充指定长度

select lpad('殷素素',10,'*') as out_put;
#总长度为10个字符，输出结果为：********殷素素

select lpad('殷素素',2,'*') as out_put;
#总长度为2个字符，输出结果为：殷素

(8)rpad 用指定的字符右填充指定长度

select rpad('殷素素',10,'ab') as out_put;
#总长度为10个字符，输出结果为：殷素素abababa

(9)replace 替换

select replace('张无忌赵周芷若上了周芷若',周芷若,'赵敏') as out_put;
#输出结果为：张无忌赵敏爱上了赵敏

2.数学函数

（1）round 四舍五入

select round(-1.55);
#输出结果为：-2

select round(1.567,2);
#输出结果为：1.57

（2）ceil 向上取整，返回>=改参数的最小整数

select ceil(1.02);
#输出结果为：2

select ceil(-1.02);
#输出结果为：-1

（3）floor 向下取整，返回<=改参数的最大整数

select floor(9.99);
#输出结果为：9

select floor(-9.99);
#输出结果为：-10

（4）truncate 截断

select truncate(1.6999,1);
#输出结果为：1.6

(5)mod 取余，与%运算符是一样的
mod(a,b): a - a / b * b
mod(-10,-3):-10- (-10)/(-3)*(-3)=-1
从上可得：被取余数为正（负），则结果为正（负），

select mod (10,3);
#输出结果为：1

select mod (-10,3);
#输出结果为：-1

select mod (-10,-3);
#输出结果为：-1

3.日期函数

（1）now 用于返回当前日期+时间

select now();
#输出结果为：2020-10-6 17:31：38

（2）curdate 用户返回当前日期

select curdate ();
#输出结果为：2020-10-6

（3）curtime 用户返回当前时间

select curtime ();
#输出结果为：17:31：38

（4）可以获取指定的部分，年(year)、月(month、monthname)、 日(day)、小时(hour)、分(minute)、秒(second)

select year(now()) as 年;
#输出结果为：2020

select year('2020-10-6') as 年;
#输出结果为：2020

select year(日期字段名);

select month('2020-10-6') as 月;
#输出结果为：10

select monthname('2020-10-6') as 月;
#输出结果为：October

（5）str_to_date 将字符通过指定格式转换为日期

select str_to_date (‘10-06 2020’,'%c-%d %Y');
#输出结果为：2020-10-6

(6) date_format 将日期转换为指定格式的字符串

select date_format('2020-10-06','%y年%c月%d日');
#输出结果为：20年10月6日

（7）datediff 计算两个日期天数差

select datediff ('2020-10-06','2020-10-04');
#注意，第一个参数要比第二个参数大，输出结果为：2

数据库日期格式符

4.其他函数

#查询MySQL可视化工具的版本号
select version();
#查询当前数据库
select database();
#查询当前用户
select user();
#返回该字符的加密形式
select password('字符');
#返回该字符的md5加密形式
select md5('字符');

5.流程控制函数

（1）if函数：可以达到if else的效果，相当于三元运算符

select if(10>5,'大','小')；
#输出结果为：大

（2）case 函数

• case的第一个用处：相当于Java中的switch case

//在Java中
switch(变量或表达式){
	case 常量1:语句1;break;
	...
	default:语句n;break;
}

#在MySQL中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;     #如果是语句加分号，如果是值，不用加分号
...
else 要显示的值n或语句n;
end

案例：

select salary as 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

• case的第二个用处：类似于多重if

//在Java中
if(条件1){
	语句1;
}else if(条件2){
	语句2：
}
...
else{
	语句n;
}

#在MySQL中
case
when 条件1 then 要显示的值1或语句1
...
else 要显示的值n或语句n
end

案例：

select salary,
case 
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;

第二大类：分组函数

分组函数用作统计使用，又称为统计函数、聚合函数、组函数，一般是对多个值进行运算得出一个结果。
分类： sum()求和、avg()求平均、max()求最大、min()求最小、count计算个数

案例：

select sum(salary) as 和,round(avg(salary,2)) as 平均,max(salary) as 最高,min(salary) as 最低,count(salary) as 个数 from employees;

注意：count()函数查询的是非空字段的个数；sum() 和avg()函数是针对数值类型的数据求和；max()和min()函数是针对可排序类型的字段。

在所有分组函数当中， 字段为null值的不参与计算

和分组查询的字段要求是group by后面的字段