单行函数
单行函数仅对单条数据中的列进行操作并且返回一个结果
###操作数据
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');
- length(column_name|str):返回字符串存储长度
- char_length(column_name|str):返回字符串中字符个数
#1、
select length(name),name from student
#2、
select char_length(name),name from student
例1结果
例2结果
- concat(column_name1|str1, column_name2|str2,…):将多个字符串首尾相连后返回
- 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结果均为:
- trim([{both | leading | trailing} [remstr] from] str):返回去掉str源字符串两端、前缀或后缀字符串remstr;不指定both、leading、trailing ,则默认为 both,此时不能使用from关键字;不指定remstr,则去掉str两端的空格
select trim(' 张三 ')
结果:
- substr(str,pos[,len]):从源字符串str中的指定位置pos开始取一个字串并返回
#1、从第七个字符截取到最后
select substring('hello world',7)
#2、从第七个字符开始截取,截取5个字符停止
select substring('hello world',7,5)
结果:
- 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)、
- if(expr1,expr2,expr3): expr1为TRUE,返回expr2,否则返回expr3
- 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关键字使组函数不考虑重复值;
常用组函数
函数:
- avg(input):求平均值,例如:select avg(age) from student;#计算学生平均年龄,包括重复的年龄
- max(input):求最大值,例如:select max(age) from student——获取学生表中最大年龄
- min(input):求最小值,例如:select min(age) from student——获取学生表中最小年龄
- sum(input):求和,例如:select sum(age) from student——计算学生表中年龄之和
- count(input):求行数,如果使用*则不会忽略空值的行
- 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