MySQL之常见函数

一、什么是MySQL函数

数据库中的函数与Java中的函数类似，都是完成一定功能的代码的集合。

根据函数操作数据行数可将SQL函数分为单行函数和多行函数：
单行函数：单行函数仅对单条数据中的列进行操作并且返回一个结果；
例如：select length(‘Tom’) from dual;#获取字符串字符个数
多行函数：多行函数可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数；
例如：select sex,count(id) from student group by sex ;#获取student表中男女人数。

二、为什么要使用MySQL函数

1、实现某些查询时所需的条件；
2、提高了代码的重用性。

三、操作数据

create table student(
id char(1) primary key,
name varchar(8),
sex char(2) default '男' ,
age int(3) default 0
)
 
insert into student values ('1','王明','男',18);
insert into student values ('2','孙丽','女',17);
insert into student values ('3','王明','男',27);
insert into student (id,sex,age) values ('4','男',27);

四、单行函数

1、字符串函数

（1）length(column_name|str)：返回字符串存储长度
例子：

select length(name) length from student

结果

（2）char_length(column_name|str)：返回字符串中字符个数
例子：

select char_length(name) length from student

结果

（3）concat(column_name1|str1, column_name2|str2,…)：将多个字符串首尾相连后返回；
例子：

select concat(id,name,sex,age) info from student

结果

（4）concat_ws(separator,column_name1|str1, column_name2|str2,…)：将多个字符串按照执行separator进行首尾相连；
例子：

select concat_ws('，',id,name,sex,age) info from student

结果

（5）trim([{both | leading | trailing} [remstr] from] str)：返回去掉str源字符串两端、前缀或后缀字符串remstr；不指定both、leading、trailing ，则默认为 both，此时不能使用from关键字；不指定remstr，则去掉str两端的空格；
例子：

select trim('  Tom  ') from dual;

结果

（6）substr(str,pos[,len])：从源字符串str中的指定位置pos开始取一个字串并返回；
注意：
pos：从1开始；
len指定子串的长度，如果省略则一直取到字符串的末尾；len为负值表示从源字符串的尾部开始取起。
函数substr()是函数substring()的同义词。
例子：

select substring('hello world',7);
select substring('hello world',2,6);

结果


（7）replace(str, from_str, to_str)：将源字符串str中所有子串form_str（大小写敏感替代成字符串to_str并返回替换后的字符串；
例子：

select replace('prefix.mysql.com','prefix','www');

结果

（8）reverse(str)：返回字符串str反转结果
例子：

select reverse('abcdef');

结果

（9）strcmp(expr1,expr2)：两个字符串相同则返回0；第一个小于第二个返回-1，否则返回1；
例子：

select strcmp('text','text');
select strcmp('text','1');

结果

2、数值函数

（1）mod(x,y)：取x与y的余数；
例子：

select mod(5,3) from dual

结果

（2）round(x[,y])：返回参数x的四舍五入值，该值有y位小数；不指定第二个参数，则默认为0；
例子：

select round(1.58,0),round(1.58),round(1.298,1);

结果

（3）truncate(x,y)：返回数字x截断后的结果，该值有y位小数；
例子：

select truncate(1.58,0),truncate(1.298,1);

结果

3、日期函数

（1）now()：获得当前日期+时间
例子：

select now();

结果

（2）date_format(date,format):获取指定格式的日期
例子：

select date_format(now(),'%Y-%m-%d-%H-%i-%s');

结果

（3）datediff(date1,date2)：返回(date1-date2)天；
例子：

select datediff(now(),'2018-12-31');

结果

（4）timediff(time1,time2)：返回time1-time2，注意：函数的两个参数类型必须相同
例子：

select timediff(now(),'2019-05-01 00:00:00'); 

结果

4、转换函数

convert(value,type)：将value转换为type类型，type可以是char（字符型）、date（日期型）、time（时间型）、datetime（日期时间型）、 signed（整型） 和decimal（浮点型）类型中的一个;
例子：

select convert(now(),char(10)); 

结果

select convert(now(),date);                           

结果

select convert(now(),time);

结果

select convert(now(),datetime);

结果

select convert('99',signed)+1;

结果

select convert('99.99',decimal(5,2))+0.01;

结果

5、其他函数

（1）if(expr1,expr2,expr3)： expr1为TRUE，返回expr2，否则返回expr3
例子：

select if(name is NULL,'未知',name) from student;

结果

（2）ifnull(expr1,expr2)：expr1不是NULL，返回expr1，否则返回expr2。
例子：

select ifnull(name,'未知') from student;

结果

五、多行函数

多行函数又称组函数，这类函数用于对多行数据进行操作，在使用时需要注意一下几点：
1、组函数忽略空值——可以通过ifnull函数或if(expr1, expr1, expr1)用一个值代替空值；
2、组函数默认考虑重复值——可以通过distinct关键字使组函数不考虑重复值；

常用组函数：

1、avg(input)：求平均值
例如：

select avg(age) from student;#计算学生平均年龄，包括重复的年龄

结果

2、max(input)：求最大值
例如：

select max(age) from student——获取学生表中最大年龄

结果

3、min(input)：求最小值
例如：

select min(age) from student——获取学生表中最小年龄

结果

4、sum(input)：求和
例如：

select sum(age) from student——计算学生表中年龄之和

结果

5、count(input)：求行数，如果使用*则不会忽略空值的行，
例如：

select count(name) from student;#student表中有一条数据中的name为空  

结果

select count(distinct name) from student;#student表中有一条数据中的name为空，有两条数据name的值重复

结果

select count(ifnull(name,' ')) from student;#将name为空的值置为1个空格

结果

select count(*) from student;#*不会忽略空值的行

结果

6、stddev(input)：求标准差
7、variance(input)：求方差

注意：count、max和min组函数操作的数据的数据类型可以是char、varchar、int或date，但不能为clob；avg、sum、stddev和varlance仅能用在数字类型的数据上。