MySQL常用函数-单行函数-分组函数

常用函数

调用方法：select 函数名（实参列表）

单行函数

字符函数

length()

获取参数值的字符个数；

select length('join');输出4；

concat()

拼接字符串；

select concat(id,'_',name) from user;输出id和name字段用下划线连接起来；

upper()和lower()

把参数值变大写和小写；

select concat(upper(id),lower(name)) from user;

substr()

截取字符；第一个参数是字符，如果2个参数表示从第二个参数指定的索引处，截取到末尾；如果3个参数，表示从指定索引处截取第三个参数给定长度的字符；

select concat(upper(substr(name,1,1)),'_',lower(substr(name,2))) from user;输出把name字段首字母大写，其他字符小写并之间用下划线拼接起来 ；

instr()

返回子字符在大字符中首次出现的位置；找不到返回0；

select instr('大哥好','哥');输出2；

trim()

去除字符两边的空格；

select trim('   dage   ');输出dage；

select trim('a' from 'aaaaa大哥aaaaa');输出大哥，意思是去掉两边的a；

lpad()rpad()

用指定字符实现左填充指定长度；

select lpad('大哥',4,'*');输出**大哥，如果第二个参数给定的长度小于字符本身长度，会从字符末尾截取小于的长度的字符；

replace()

替换字符；

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

数学函数

round()

四舍五入；

select round(4.5);输出5；

select round(1.456,2);输出1.46；第二个参数表示小数点后保留位数；

ceil()

向上取整（返回>=该参数的最小整数）；

select ceil(1.4);输出2；

select ceil(1.00);输出1；

floor()

向下取整（返回<=该参数的最小整数）；

select floor(1.2);输出1；

select floor(1.00);输出1；

truncate()

截断

select truncate(1.4999,1);输出1.4；第二个参数表示保留几位小数，后面部分不论大小，全部去掉；

mod()

取余；公式 a-a/b*b

select mod(10,3);输出1；

日期函数

now()

返回当前年月日时分秒

select now();输出 2020-05-17 20：56：23

datediff()

查询两个日期之间差多少天

select datediff('2020-4-3','2020-4-4');输出1；

curdate()

返回当前年月日

select curdate();输出2020-05-17

curtime()

返回当前时分秒

select curtime();输出20：56：23

year()类似月日时分秒都可以

返回当前年份

select year(now());输出2020

unix_timestamp()将时间格式转化为时间戳

select unix_timestamp(now());

获取三天前的时间

SELECT NOW() - interval 72 hour;

时间转时间戳

select from_unixtime(1515980716);

其他函数

select version()：查看数据库版本；

select database()：查看当前库名；

select user()：查看当前用户名；

password('字符')：返回字符加密后的数据（自动加密）

md5()：加密

流程控制函数

if()

if(10>4,'大','小');输出大；类似三元运算符，第一个参数是表达式，表达式成立返回第二个参数值，不成立返回第三个参数值；

case

使用方式一：

case 要判断的字段或表达式

when 常量1 then 要显示的值或语句;

when 常量2 then 要显示的值或语句;

...

else 默认要显示的值或语句;

end

示例：

select price 原价,typeid,

case typeid

when 1 then price*1.1

when 2 then price*1.2

else price

end as 新价

from good;

表示查询商品表的各个类别价格，当类别为1时价格提高1.1倍，为2时价格提升1.2倍，其他保持不变；

使用方式二：

case

when 条件1 then 要显示的值1或语句1

when 条件2 then 要显示的值2或语句2

...

else 要显示的值n或语句n

end

示例：

select price

case

when price>300 then 'A'

when price>200 then 'B'

when price>100 then 'C'

else 'D'

end as 商品级别

from good;

表示查询商品的价格，价格高于300的为A级别，高于200为B级别，高于100为C级别，其他为D级别

单行函数总结

字符函数：length、upper、lower、lpad、rpad、substr、replace、concat、instr、trim

数学函数：ceil、floor、round、truncate、mod

日期函数：now、year、str_to_date、curdate、curtime、monthname、date_format

其他函数：version、database、user

控制函数：if、case

分组函数

功能：用作统计使用；又称为聚合函数或统计函数或组函数；

特点：

sum和avg只适用于处理数值型，其他型虽不报错，但已失去实际意义；忽略null值；

max和min只要能排序的数值型都可以处理；忽略null值；

count只统计不为null的值的数量；

可以和distinct搭配使用实现去重的运算；

和分组函数一同查询的字段要求是group by后的字段，其他字段虽不报错，但无意义；

sum()

求和

select sum(price) from good;输出商品表价格总和；

avg()

求平均值

select avg(price) from good;输出商品表价格的平均值；

max()

求最大值

select max(price) from good;输出商品表价格的最大值；

min()

求最小值

select min(price) from good;输出商品表价格的最小值；

count()

计算非null值的数量

select count(*) from good;输出商品表数据总条数；