MySQL常见函数

一、单行函数：

做处理使用。如concat, length, ifnull等；

1、字符函数：
（1）length（获取参数值的字节个数）：

select length('john');  #返回字符长度
select length('张三丰hahaha')；# 返回15（一个字母占1个字节，一个汉字占3个字节）

（2）concat（字符串拼接）：

select concat(lastName, '_', firstName) 姓名 from emps;

（3）upper（转大写）、lower（转小写）：

select upper('john');
select lower('joHn');

（4）substr、substring（截取字符）：
注意：索引从1开始。

selct substr('李莫愁爱上了陆展元',  7);   # 输出'陆展元'

select substr(str, index);  # 截取从指定索引至最后的所有字符
select substr(str, index, size);  # 截取从指定索引处指定字符长度的字符

（5）instr（返回子串第一次出现的索引，若找不到则返回0）：

select instr('杨不悔爱上了殷六侠'， ’殷六侠‘)  # 输出7
select instr('杨不悔爱上了殷六侠'， ’殷八侠‘)  # 输出0

（6）trim（去除前后空格）：

select trim('张 翠 山');
select trim('a' from 'aaa张aaa翠aaa山')  # 去除前后的字符a

（7）lpad（用指定的字符实现左填充指定长度）：

select lpad('殷素素', 5, '*');   # 输出'aa殷素素'

（8）rpad（用指定的字符实现右填充指定长度）：

select rpad('殷素素', 5, 'ab');    # 输出'殷素素aa'

（9）replace（替换）：

select replace('张无忌爱上了周芷若', '周芷若', '赵敏');  # 输出'张无忌爱上了赵敏'

2、数学函数：
（1）round（四舍五入）；

select round(1.65)        # 输出2
select round(1.567, 2)    # 输出1.57（保存2位小数点）

（2）ceil（向上取整：返回大于等于该参数的最小整数）：

select ceil(-1.02)      # 输出-1

（3）floor（向下取整：返回小于等于该参数的最大整数）：

select floor(-9.99)      # 输出-10

（4）truncate（截断）：

select truncate(1.65, 1)       # 输出1.6
select truncate(1.6999, 1)     # 输出1.6

（5）mod（取余）：
mod(a, b) : 等价于a-a/b*b

select mod(10, 3)         # 输出1
select mod(-10, -3)       # 输出-1
select mod(-10, 3)        # 输出-1

3、日期函数：
（1）now（返回系统当前日期+时间）：

select now();    # 输出2020-05-28 18:36:29

（2）curdate（返回系统当前日期，不包含时间）：

select curdate();    # 输出2020-05-28

（3）curtime（返回系统当前时间，不包含日期）：

select curtime();    # 输出18:36:29

（4）获取指定的部分（年、月、日、消失、分钟、秒）：

select year(now())  年;
select year('2020-05-28') 年；
select year(birthday) 年 from emps;
select monthname(now()) 月;    # 当前是5月，故输出May

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

str_to_date('11-29-1992', '%m-%d-%Y')  # 输出1992-11-29

日期格式如下：

（6）date_format（将日期转换成字符）：

date_format('1992/11/29', '%Y年%m月%d日')  # 输出1992年11月29日

4、其他函数：
（1）select version(); # 查询当前数据库版本
（2）select database(); # 查询当前数据库
（3）select user(); # 查询当前用户
5、流程控制函数：
（1）if函数：等价与if else；

select if(10 > 5, '1', '0');

（2）case函数：
使用1：类似于switch case效果；
java中：

switch(变量或表达式) {
	case 常量1: 语句1; break;
	······
	default: 语句n; break;

mysql中：

case 要判断的字段或表达式
	when 常量1 then 要显示的值1或语句1；
	······
	else 要显示的值n或语句n；

例：查询员工的工资，要求：
deptId=30, 显示的salary为1.1倍；
deptId=40,显示的salary为1.2倍；
deptId=50, 显示的salary为1.3倍；

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

使用2：类似于多重if
java中：

if (条件1) {
	语句1;
} else if (条件2) {
		语句2;
	   }
	   ······
else {
	语句n;
}

mysql中：

case 
	when 条件1 then 要显示的值1或语句1;
	······
	else 要显示的值n或语句n
end

例：查询员工的工资情况：
若salary > 20000, 显示A级别；
若salary > 15000, 显示B级别；
若salary > 10000, 显示C级别；

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

二、分组函数：

做统计使用。又称为统计函数、聚合函数、组函数。

分类：sum，avg，max，min，count。
1、简单使用：
（1）sum（求和）

select sum(salary) from emps;

（2）avg（平均值）：

select avg(salary) from emps;

（3）max（最大值）：

select max(salary) from emps;

（4）min（最小值）：

select min(salary) from emps;

（5）count（统计个数）：

select count(salary) from emps;

也可：

select 
	sum(salary) 和，  
	avg(salary) 平均值， 
	max(salary) 最高，
	min(salary) 最低，
	count(salary) 个数
from emps;

2、特点：
（1）支持的参数类型：

sum, avg一般用于处理数值型；
max, min, count可以处理任何类型；

（2）是否忽略null值：
所有分组函数都忽略null值；
（3）可以和distinct搭配实现去重的运算：

select sum(distinct salary), sum(salary) from emps;
select count(distinct salary), count(salary) from emps;

（4）count函数的详细介绍：
一般使用count(*)统计行数。

select count(salary) from emps;
select count(*) from emps;      # 一般用于统计行数
select count(1) from emps;

效率：
myisam存储引擎下，count()效率高；
innodb存储引擎下，count()和count(1)效率差不多，比count(字段) 要高一些。
（5）和分组函数一起查询的字段要求是group by后的字段。