MySQL常用函数

数据处理函数

字符串相关

转大写upper和ucase

例如：查询所有员工名字，以大写的形式展示

select upper(ename) as ename from emp;
select ucase(ename) as ename from emp;

转小写lower和lcase

select lower(ename) as ename from emp;
select lcase(ename) as ename from emp;

截取字符串

有两种写法：

第一种：substr('被截取的字符串', 起始下标, 截取长度)

第二种：substr('被截取的字符串', 起始下标)，当第三个参数“截取长度”缺失时，截取到字符串末尾 注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）

 截取方向向右，-1从最右边开始向后截取，f后面没有字符串，结果只能是f

获取字符串长度

一个汉字3个字节长度，length求所占空间大小，charlength求字符串的字符个数

字符串拼接

 语法：concat('字符串1', '字符串2', '字符串3'....) 拼接的字符串数量没有限制。

注意：在mysql8之前，双竖线||也是可以完成字符串拼接的。但在mysql8之后，||只作为逻辑运算符，不能再进行字符串拼接了。

去掉字符串前后空白

 

 默认是去除前后空白，也可以去除指定的前缀后缀，例如： 去除前置0

去除后置0

前置0和后置0全部去除

数字相关

 rand()和rand(x)

rand()生成0到1的随机浮点数。

rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。

再次使用rand(1)生成的随机数固定

round(x)和round(x,y)四舍五入

round(x)四舍五入，保留整数位，舍去所有小数

 round(x,y) 四舍五入，保留y位小数

truncate(x,y)舍去

保留y位小数，其他全部舍去

 

ceil与floor

数字处理函数除了以上的之外，还有ceil和floor函数：

• ceil函数：返回大于或等于数值x的最小整数（向上取整）

• floor函数：返回小于或等于数值x的最大整数（向下取整）

空处理

ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。

ifnull(comm, 0)，表示如果员工的津贴是NULL时当做0处理。

在SQL语句中，凡是有NULL参与的数学运算，最终的计算结果都是NULL：

日期和时间相关函数 

获取当前日期和时间

now()和sysdate()的区别：

• now()：获取的是执行select语句的时刻。

• sysdate()：获取的是执行sysdate()函数的时刻。

获取当前日期

获取当前日期有三种写法，掌握任意一种即可：

• curdate()

• current_date()

• current_date

获取当前时间

获取当前时间有三种写法，掌握其中一种即可：

• curtime()

• current_time()

• current_time

获取单独的年、月、日、时、分、秒

 

 注意：这些函数在使用的时候，需要传递一个日期参数给它，它可以获取到你给定的这个日期相关的年、月、日、时、分、秒的信息。

一次性提取一个给定日期的“年月日”部分，可以使用date()函数，例如：

 一次性提取一个给定日期的“时分秒”部分，可以使用time()函数，例如：

date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。

date_add函数的语法格式：date_add(日期, interval expr 单位)，例如：

 

以'2024-06-11'为基准，间隔3个月之后的日期：'2024-09-11' 详细解释一下这个函数的相关参数：

• 日期：一个日期类型的数据

• interval：关键字，翻译为“间隔”，固定写法

• expr：指定具体的间隔量，一般是一个数字。也可以为负数，如果为负数，效果和date_sub函数相同。

• 单位：

  • year：年

  • month：月

  • day：日

  • hour：时

  • minute：分

  • second：秒

  • microsecond：微秒（1秒等于1000毫秒，1毫秒等于1000微秒）

  • week：周

  • quarter：季度

 请分析下面这条SQL语句所表达的含义：

 以上SQL表示：以2022-10-01 10:10:10为基准，在这个时间基础上添加-1微秒，也就是减去1微秒。

以上SQL也可以采用date_sub函数完成，例如：

 

另外，单位也可以采用复合型单位，例如：

• SECOND_MICROSECOND

• MINUTE_MICROSECOND

• MINUTE_SECOND：几分几秒之后

• HOUR_MICROSECOND

• HOUR_SECOND

• HOUR_MINUTE：几小时几分之后

• DAY_MICROSECOND

• DAY_SECOND

• DAY_MINUTE

• DAY_HOUR：几天几小时之后

• YEAR_MONTH：几年几个月之后

 如果单位采用复合型的话，expr该怎么写呢？例如单位采用：day_hour，假设我要表示3天2小时之后，怎么写？

3,2'这个应该很好理解，表示3天2个小时之后。'3,2'和day_hour是对应的。  

date_format日期格式化函数

将日期转换成具有某种格式的日期字符串，通常用在查询操作当中。（date类型转换成char类型） 语法格式：date_format(日期, '日期格式')

该函数有两个参数：

• 第一个参数：日期。这个参数就是即将要被格式化的日期。类型是date类型。

• 第二个参数：指定要格式化的格式字符串。

  • %Y：四位年份

  • %y：两位年份

  • %m：月份（1..12）

  • %d：日（1..30）

  • %H：小时（0..23）

  • %i：分（0..59）

  • %s：秒（0..59）

例如：获取当前系统时间，让其以这个格式展示：2000-10-11 20:15:30

注意：在mysql当中，默认的日期格式就是：%Y-%m-%d %H:%i:%s，所以当你直接输出日期数据的时候，会自动转换成该格式的字符串：

str_to_date函数

该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）

假设有一个学生表t_student，学生有一个生日的字段，类型是date类型：

drop table if exists t_student;
create table t_student(
  name varchar(255),
  birth date
);
desc t_student;

我们要给这个表插入一条数据：姓名zhangsan，生日85年10月1日，执行以下insert语句：

insert into t_student(name,birth) values('zhangsan','10/01/1985');

会产生错误，错误原因：日期值不正确。意思是：birth字段需要一个日期，你给的这个字符串'10/01/1985'我识别不了。这种情况下，我们就可以使用str_to_date函数进行类型转换：

insert into 
    t_student(name,birth) 
values
    ('zhangsan',str_to_date('10/01/1985','%m/%d/%Y'));

当然，如果你提供的日期字符串格式能够被mysql解析，str_to_date函数是可以省略的，底层会自动调用该函数进行类型转换：

insert into t_student(name,birth) values('zhangsan','1985-01-01')
insert into t_student(name,birth) values('zhangsan','85-02-01')
insert into t_student(name,birth) values('zhangsan','85/03/01')
insert into t_student(name,birth) values('zhangsan','1985/04/01')

如果日期格式符合以上的几种格式，mysql都会自动进行类型转换的。

dayofweek、dayofmonth、dayofyear函数

dayofweek：一周中的第几天（1~7），周日是1，周六是7。

dayofmonth：一个月中的第几天（1~31）

dayofyear：一年中的第几天（1~366）

last_day函数

获取给定日期所在月的最后一天的日期：

datediff函数

计算两个日期之间所差天数：

时分秒不算，只计算日期部分相差的天数。

timediff函数

计算两个日期所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒。

if函数

如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”：

相当于编程里面的三目运算符

例如：如果名字是SMITH的，工资上调10%，其他员工工资正常显示。

cast函数

cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型

语法：cast(值 as 数据类型)

例如：cast('2020-10-11' as date)，表示将字符串'2020-10-11'转换成日期date类型。

在使用cast函数时，可用的数据类型包括：

• date：日期类型

• time：时间类型

• datetime：日期时间类型

• signed：有符号的int类型（有符号指的是正数负数）

• char：定长字符串类型

• decimal：浮点型

加密函数

md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的：

分组函数

分组函数的执行原则：先分组，然后对每一组数据执行分组函数。如果没有分组语句group by的话，整张表的数据自成一组。 分组函数包括五个：

• max：最大值

• min：最小值

• avg：平均值

• sum：求和

• count：计数

max

找出员工的最高薪资

select max(sal) from emp;

min

找出员工的最低工资

select min(sal) from emp;

avg

计算员工的平均薪资

select avg(sal) from emp;

sum

计算员工的工资和

select sum(sal) from emp;

计算员工的津贴之和

select sum(comm) from emp;

重点：所有的分组函数都是自动忽略NULL的。

count

统计员工人数

select count(ename) from emp;
select count(*) from emp;
select count(1) from emp;

count(*)和count(1)的效果一样，统计该组中总记录行数。

count(ename)统计的是这个ename字段中不为NULL个数总和。  

统计岗位数量

select count(distinct job) from emp;

分组函数组合使用

select count(*),max(sal),min(sal),avg(sal),sum(sal) from emp;

分组函数注意事项

分组函数不能直接使用在where子句当中 select ename,job from emp where sal > avg(sal); 这个会报错的 原因：分组的行为是在where执行之后才开始的。

面试题

count(comm)和count(*)的区别，count(单个字段)统计不为空数据，count(*)统计全部数据条数，有几条算几条