Apong's Blog

当你快坚持不住的时候，困难也快坚持不住了

0%

MySQL(二) 数据处理函数

发表于 分类于

在数据库操作中，除了需要查询数据，

有时候还需要对数据进行一些简单的处理，

用于优化数据显示效果等。

select的其他用法

select 除了可以选择列名外，还可以选择常量。

1
2
select 3; # 3
select 'hello world'; # hello world

upper、lower

分别用于将字符串转为大写和小写

ucase, lcase 分别也可以转大小写。

应用

当不知道查询内容的大小写时，可以统一转为大写/小写来匹配查询。

在 Oracle 数据库中区分大小写，Linux 的 MySQL 也区分

1
select * from emp where upper(ename)='smith';

substr

用于截取字符串

用法

  1. substr(‘被截取的字符串’, 起始下标, 截取长度)
  2. substr(‘被截取的字符串’, 起始下标)

如果不写截取长度，就直接截取到尾部。

截取规则：

  1. 下标从 1 开始，-1 表示右侧第一个。

  2. 截取的方向永远往右边截取。

1
select substr('abcd', -1, 2); # d

上述 sql 只能得到 ‘d’，而不是 ‘cd’

应用

1
select * from emp where substr(ename, 2, 1) = 'A';

选择 ename 第二个字符是 A 的行。

length、char_length

length 用于统计字符串的字节数

char_length 用于统计字符数

1
2
select length('你好'); # 6
select char_length('你好'); # 2

concat

用于拼接字符串，可以传入任意个参数。

1
select concat('hello ', 'world'); # hello world

mysql8之前可以采用 || 进行拼接。

‘hello ‘ || ‘world’ => ‘hello world’

trim

去除字符串前后空白字符。

1
2
select trim('  a  b  c  ');
# a  b  c

扩展

leading

1
2
select trim(leading '0' from '000111');
# 111

去除前面的 0 字符。

trailing

1
2
select trim(trailing '0' from '111000');
# 111

去除后面的 0 字符

both

1
2
select trim(both 'x' from 'xx111xx');
# 111

去除前后的 x 字符

rand()

生成 0 到 1 之间的随机浮点数。

还有一种写法：rand(x)

通过传入一个值，保留本次随机数的值。

1
2
select rand(1); # 0.918204912
select rand(1); # 0.918204912

这个 x 就相当于一个 key，每次获取同样的 x，得到的值都是一样的。

这个 x 应该是“种子”吧，如果种子相同得到的结果自然是一样的。

round(x)

四舍五入

1
select round(3.5); # 4

指定保留的小数位

1
select round(3.63, 1); #3.6

truncate(x, y)

只保留 y 个小数，剩下小数位舍弃

1
select truncate(3.6666, 1); # 3.6

ceil(x)、floor(x)

ceil：向上取整

floor：向下取整

忽略四舍五入

1
select ceil(4.7); # 5
1
select floor(4.7); # 4

空处理

在 MySQL 和 Oracle 中，任何和 NULL 运算的结果都是 NULL；

ifnull(x, y)

如果 x 为 NULL，就用 y 代替

1
select ename, sal, ((sal + ifnull(comm, 0)) * 12) as year_sal from emp;

当 comm 为空时（员工没有津贴时），统一用 0 代替，避免运算结果为 NULL。

获取当前 datetime

now()

获取当前时间（select 执行的时间）

image-20240712221917658

sysdate()

获取该函数执行的时间

image-20240712222000512

底层应该是 now 直接被转化为当前时间，而 sysdate 按照程序执行顺序获取时间。

获取当前 date/time

current_date() 获取当前日期

current_time() 获取当前时间

可以省略括号()

获取单独的年月日时分秒

year(时间)

month(时间)

day(时间)

hour(时间)

minute(时间)

second(时间)

这里的时间可以是 now()，或者符合日期格式的字符串。

1
2
select year('2024-07-12 22:26:31');
# 2024

date_add

用于日期时间的运算

语法格式：

date_add(日期，interval expr 单位)

例如：

1
2
select date_add('2020-10-14', interval 3 day);
# 2020-10-17

只提供“日期”，可以用“时分秒”等次级单位来增减间隔。

但是如果只提供“时分秒”，就无法用上一级的单位“年月日”来增减间隔了。

运算向下兼容。

参数介绍

日期：一个日期类型的数据或字符串

interval：固定写法，关键字

expr：指定一个具体的间隔量

可以为负数，效果和 date_sub 一致，表示往后退。

单位：

  • 年月日
  • 时分秒
  • microsecond 微妙
  • week 周
  • quarter 一个季度（3个月）

复合单位

两个单位通过下划线组合在一起。

例如：

  • year_month
  • day_hour
  • day_minute
  • day_second

没有 month_day，不知道为什么。。

1
2
select date_add('2020-10-14', interval '1,1' year_month);
# 2021-11-14

注意：expr 需用引号包裹，表示一个表达式。

date_format

用于日期格式化

语法格式：

date_format(日期, 格式字符串)

举例

1
2
select date_format(now(), '%y-%m-%d %H:%i:%s');
# 24-07-14 23:28:11

参数介绍

日期：可以是日期类型数据或字符串

格式字符串：由“%”+代表年月日时间的字符，和其他分隔字符组成。

日期字符：

  • Y / y：Y：四位完整的年份，y：完整年份的后两位数。
  • m：月份
  • d：日
  • H：小时
  • i：分钟
  • s：秒

str_to_date

将字符串转为日期

语法格式：

str_to_date(日期, 格式字符串)

格式字符串与 date_format 一致。

例如：

1
insert into t_student(name, birth) values('lisi', str_to_date('10/01/1999', '%m/%d/%Y'));

或者直接输入遵循默认日期格式的字符串（%y-%m-%d %H:%i:%s）。

dayofweek, dayofmonth, dayofyear

分别给出目标日期对应一个星期，一个月，一年中的哪一天

语法格式：

dayofweek(日期)

同，可以是日期类型数据或者字符串。

1
2
3
select dayofweek(now());
select dayofmonth(now());
select dayofyear(now());

注：周日是一周的第一天

last_day

给出当前月份的最后一天的日期。

语法格式：

last_day(日期)

同，可以是日期类型数据或者字符串。

datediff, timediff

datediff：显示两个日期相差的天数。

timediff：求出两个日期相差的小时数，以时:分:秒的形式显示。

语法格式：

datediff(日期1, 日期2)

timediff(日期1, 日期2)

例如：

1
2
3
4
select datediff('2000-10-10 10:10:10', '2000-10-9 10:10:10');
# 1
select timediff('2000-10-10 10:10:10', '2000-10-9 10:10:10');
# 24:00:00

结果是以日期1比日期2晚 xx 时间计算的，前者时间晚于后者，结果为正数，反之为负数。

if

类似 Java 中的三目运算符。

语法格式：

if(条件, x, y)

条件符合返回 x，反之返回 y。

例如：

1
select if(1<2, 1, 2); # 1

支持嵌套运算

当 job 为 SALESMAN 时薪水乘以 1.1 倍，为 MANAGER 时薪水乘以 2 倍，请给出结果。

1
select sal, job, if(job='SALESMAN', sal*1.1, if(job='MANAGER', sal*2, sal)) as new_sal from emp;

image-20240714234512305

case…when…then…else…end

类似 Java 中的 switch

语法格式：

case 变量 when 匹配值 then 结果 else 结果 end

例如：（修改 if 嵌套）

:x:错误写法

1
2
3
4
5
select sal, job, (case job
when job='SALESMAN' then sal*1.1
when job='MANAGER' then sal*2
else sal end) as new_sal
from emp;

:question: 为什么写成 job=’xxx’ 反而是 manager 的薪水乘以 1.1，salesman 的薪水乘以 2 了。

正确写法

1
2
3
4
5
select sal, job, (case job
when 'SALESMAN' then sal*1.1
when 'MANAGER' then sal*2
else sal end) as new_sal
from emp;

image-20240714235253338

结果和 if 嵌套写法一致。

建议先写好模板，然后一个一个条件填入

cast

用于类型转换

语法格式：

cast(值 as 数据类型)

数据类型：

  • date：日期
  • time：时间
  • datetime：日期时间
  • signed：有符号的 int 类型
  • char：char(字符长度)，字符。
  • decimal：decimal(有效数字位，保留小数位)，浮点数。

有效数字位包括小数位。

转化为 decimal 时如果指定的实际值超过了有效数字位，

会返回要求有效数字位的最大值。

如：将 ‘123.456’ 转为 decimal(3, 1) => 99.9

例如：将字符串转为浮点数

1
2
select cast('000123.456' as decimal(4,1));
# 123.5 四舍五入

md5

单向加密算法，结果是一个固定长度 32 位的字符串。