Mysql数据库中的内置函数

单行函数

单行函数仅对单条数据中的列进行操作并且返回一个结果

###操作数据
create table student(
   id char(36) primary key,
   name varchar(8) not null,
   age int(3) default 0,
   mobile char(11),
   address varchar(150)
)
insert into student 
values ('9b4435ec-372c-456a-b287-e3c5aa23dff4','张三',24,'12345678901','北京海淀');
insert into student 
values ('a273ea66-0a42-48d2-a17b-388a2feea244','李%四',10,'98765432130',null);
insert into student 
values ('eb0a220a-60ae-47b6-9e6d-a901da9fe355','张李三',11,'18338945560','安徽六安');
insert into student 
values ('6ab71673-9502-44ba-8db0-7f625f17a67d','王_五',28,'98765432130','北京朝阳区');
insert into student 
values ('0055d61c-eb51-4696-b2da-506e81c3f566','王_五%%',11,'13856901237','吉林省长春市宽平区');
insert into student(id,age,address) 
values ('123',12,'qwert');

1. length(column_name|str)：返回字符串存储长度
2. char_length(column_name|str)：返回字符串中字符个数

#1、
select length(name),name from student
#2、
select char_length(name),name from student

例1结果

例2结果

3. concat(column_name1|str1, column_name2|str2,…)：将多个字符串首尾相连后返回
4. concat_ws(separator,column_name1|str1, column_name2|str2,…)：将多个字符串按照执行separator进行首尾相连

#1、
select concat(id,'-',name,'-',age)as name_age from student
#2、
select concat_ws('-',id,name,age)as name_age from student

1、2结果均为：

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

select trim('  张三   ')

结果：

6. substr(str,pos[,len])：从源字符串str中的指定位置pos开始取一个字串并返回

#1、从第七个字符截取到最后
select substring('hello world',7)
#2、从第七个字符开始截取，截取5个字符停止
select substring('hello world',7,5)

结果：

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

select replace('study#make#me$happy','#','*')

结果：

8. reverse(str)：返回字符串str反转结果

select reverse('qwerty')

结果：

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

select strcmp('wang','xu')

结果：

10. mod(x,y)：取x与y的余数
11. round(x[,y])：返回参数x的四舍五入值，该值有y位小数；不指定第二个参数，则默认为0
12. truncate(x,y)：返回数字x截断后的结果，该值有y位小数

select mod(2,1),round(1.318,2),truncate(1.2337,3)

结果：

13. now()：获得当前日期+时间
14. date_format(date,format):获取指定格式的日期
15. convert(value,type)：将value转换为type类型，type可以是char（字符型）、date（日期型）、time（时间型）、datetime（日期时间型）、 signed（整型） 和decimal（浮点型）类型中的一个

#1、
select now()
#2、
select date_format(now(),'%Y-%m')
#3、
select convert(date_format(now(),'%Y%m'),char)

结果分别为：
（1）、

（2）、

（3）、

16. if(expr1,expr2,expr3)： expr1为TRUE，返回expr2，否则返回expr3
17. ifnull(expr1,expr2)：expr1不是NULL，返回expr1，否则返回expr2

#1、
select if(name is null,'',name)name from student
#2、
select ifnull(name,'')name from student

结果均为

多行函数

create table newstar(
id char(1) primary key,
name varchar(8),
sex char(2) default '男' ,
age int(3) default 0
)
 

insert into newstar values ('1','王明','男',18);
insert into newstar values ('2','孙丽','女',17);
insert into newstar values ('3','王明','男',27);
insert into newstar (id,sex,age) values ('4','男',27);

多行函数又称组函数，这类函数用于对多行数据进行操作，在使用时需要注意一下几点

• 组函数忽略空值——可以通过ifnull函数或if(expr1, expr1, expr1)用一个值代替空值
• 组函数默认考虑重复值——可以通过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)：求行数，如果使用*则不会忽略空值的行
6. group by用于将表中数据划分为若干个组，group by后面用于指定分组的依据

#1、
select count(id) from newstar
#2、
select count(id) from newstar group by sex
#3、
select count(id),sex from newstar where 1=1 group by sex
##先查询表中符合where条件的结果，再根据分组条件分组，最后统计每组个数
##使用group by则，查询结果字段，要么是分组依据列，要么是聚合函数列

执行结果：
（1）、

（2）、

（3）、

7. having：where后面不能使用多行函数，只能使用单行函数和字段，having关键字弥补了这一不足：having子句用于对分组结果进行约束

#先根据where条件进行筛选，然后分组，最后通过having筛选显示结果
select name from newstar where 1=1 group by name having count(name)>1