MySQL-基础-常见函数-单行函数-分组函数

常见函数

基础

概念
类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。

好处
隐藏了实现细节；提高了代码的重用性；
隐藏了实现细节

语法

select 函数名（实参列表） [from 表];
当函数的实参列表用到表的字段的时，需要加from表。

使用需求
需要了解函数名（是什么）、函数功能（做什么）

分类
单行函数：字符函数、数字函数、日期函数、其他函数、流程控制函数。
分组函数：功能做统计使用，又被称为统计函数、聚合函数、组函数。

单行函数

包含：字符函数、数字函数、日期函数、其他函数、流程控制函数。

字符函数（10）

函数介绍

函数名	功能	语法
length	获取参数值的字节数	length(参数) UTF-8 中一个汉字占三个字节，一个英文字母占一个字节 参数可以是字符串，单引号
upper	转大写	upper(参数) 参数是字符串类型
lower	转小写	lower(参数) 参数是字符串类型
substr substring	截取字符串 索引是从1开始的	①substr（参数1，参数2） ：参数1表示原始字符串，参数2表示截取开始位置的索引（截取指定索引处之后的所有字符） ②substr(参数1，参数2，参数3) ：参数1表示原始字符串、参数2表示截取开始位置的索引、参数3表示截取的长度（表示从指定索引处指定长度的字符）
instr	返回子串第一次出现位置的索引，没有则返回0	instr（串1，串2）：串1是源串，串2是子串
lpad	用指定的字符左填充指定的长度	lpad（初始字符串，总长度，填充字符） 当长度不够的时候，左填充（填充字符），长度多余的时候，从左到右依次显示
rpad	用指定的字符右填充指定的长度	rpad（初始字符串，总长度，填充字符） 当长度不够的时候，右填充（填充字符）；长度多余的时候，从左到右依次显示
trim	去掉前后的空格	①trim（‘参数’）; 去掉前后的空格 ②trim（字符 from ‘参数’）；去掉前后特定的字符（参数），对于中间的特定字符不能去掉 案例：去掉aaaaaaa张aa三aa丰aaaaa 中的a ；结果是——张aa三aa丰
concat	拼接字符串	concat(串1，串2…)
replace	替换	replace（参数1，参数2，参数3）：参数1表示源串，参数2表示查找子串，参数3表示替换成的子串 当参数1中找到多个参数2，则参数3会将多个参数2替换

案例

length、lower、upper、substr

concat、instr、lpad、rpad、trim、replace

数字函数（6）

函数介绍

函数名	功能	语法
round	四舍五入	①（参数1）：表示要进行算术的原始数据 ②（参数1，参数2）：参数1同上述，参数2表示小数的保留位数 小数的保留位表示四舍五入从第几位小数开始，保留一位小数则说明四舍五入从小数第二位开始
floor	向下取整	（参数） 返回<=该数的最大整数
ceil	向上取整	（参数） 返回>=该数的最小整数
mod	取余、取模	理解：mod（a，b）：a-a/b*b 结果的符号和被除数有关
rand	产生随机数	返回0-1之间的小数
truncate	截取	truncate(参数1，参数2) ：参数1：原始数据；参数2：表示小数点后面保留几位。 直接将小数点后的内容删除

案例

round、floor、ceil、mod、rand、truncate

日期函数（12）

函数介绍

函数	功能	举例
now	返回当前系统的时间+日期	select now(); #输出2021-05-02 10:12:11
curtime	返回当前系统的时间	select curtime();#输出10:15:03
curdate	返回当前系统的日期	select curdate();#输出2021-05-02
year	获取年	参数是now() 输出当前年份 参数是字符串’1997-05-07’ 输出的是1997 参数是 字段（该字段可能是日期类数据）输出的是一列
month monthname	获取月 获取月的英文名	month输出的是 数字（1,2,3） monthname输出的是 英文May
day dayname	获取日 获取日对应的星期英文	day输出的是 数字（6） dayname 输出的是 英文sunday
hour	获取小时	输出数字
minute	获取分钟	输出数字
second	获取秒钟	输出数字
str_to_date	将字符通过指定的格式转换成日期	str_to_date（参数1，参数2）参数2是参数1格式的说明 str_to_date（‘11-6-1997’,‘%m-%d-%Y’）输出为1997-6-11
date_format	将日期转化为字符	date_format(now(),‘%y年%m月%d日’); 输出的日期就是 xxxx年xx月xx日
datediff	返回两个日期之间的时间	datediff（datepart，startdate，enddate） 在mysql workbench中运行SELECT DATEDIFF(day,‘2008-12-29’,‘2008-12-30’) AS DiffDate 会报错，去掉参数day之后就可以执行了。

str_to_date中参数介绍

符号	说明
%Y %y	（四位）年 （两位）年
%m %c	月（01,02…） 月（1,2…）
%d	日（01,02…）
%H %h	小时（24小时制） 小时（12小时制）
%i	分钟（01,02，…59）
%s	秒钟（01,02…59）

datediff中的datepart参数

参数	说明
yy、yyyy	年
qq、q	季度
mm、m	月
dy、y dd、d	年中的日 日
wk、ww	周
dw、w	星期
hh	小时
mi、n	分钟
ss、s	秒
ms	毫秒
mcs	微秒
ns	纳秒

案例

now、curtime、cutdate、year、month、day、hour、minute、second

str_to_date、date_format

案例2022/8/3

注意：当题目涉及日期内容的时候，要下意识联想日期函数。

其他函数（5）

函数介绍

函数	功能
version	查看当前版本号（mysql）
user	查看当前用户
database	查看当前数据库
password(‘字符’)	返回字符的加密形式
md5(‘字符’ )	返回字符的md5加密形式

备注2022/7/29
使用格式为：select 函数名（）;

案例

version、user、database、password、md5

流程控制函数

if函数

if(条件表达式,表达式1,表达式2);

如果表达式值为true，则返回表达式1，否则返回表达式2；

case函数

方式一：类似于java中switch case，适用于判等
2022/7/29 备注
case when then else end

java：

switch(常量或表达式):
	case1 常量：语句1；break；
	case2 常量：语句2；break；
	.....
	default 语句n：break；

mysql：

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

既可以搭配select使用，也可以单独做语句使用。

方式二：类似于java中的多重if（适合区间判断）
java：

if(条件1){
	语句1；
}else if（条件2）{
	语句2；
}
.....
else{
	语句n；
}

mysql：

case 
when 条件1 then 要显示的值1 或 语句1；
when 条件2 then 要显示的值2 或 语句2；
....
else 要显示的值n 或 语句n；
end

案例

分组函数

概述

功能：
用作统计使用，又称为聚合函数、统计函数、组函数。

分类：
sum求和、max最大值、min最小值、count个数、avg平均值。

语法：

select 分组函数（字段） from 表；

特点

支持类型

• sum、avg，一般适用于数字型，不支持 字符型和日期型；
• max、min、count 支持任何类型；

上述分组函数都忽略null值

可以和distinct搭配实现去重

select 分组函数（distinct 字段） from 表；

count函数

• 不同参数类型介绍
  count（字段）：统计该字段非空值的个数；
  count（*）：统计结果集的行数；
  count（1）：统计结果集的行数；（相当于在结果集中加了一列，且内容为1）
• 效率
  MYISAM存储引擎下，COUNT(星号)的效率最高；
  INNODB存储引擎下，效率之间的关系是：count（星号）和count(1) > count(字段) ；

备注：2022/8/27
查询字段有分组函数，则其余字段必须出现在group by 后面。

案例