本文正在参加「技术专题19期 漫谈数据库技术」活动
数学函数
count()
count()
、count(field)
统计某个结果集中的总数量,field
为要统计的字段。
SELECT
count( 1 )
FROM
db1.users
sum()
sum(field)
统计某个字段累计的总和,field为要统计的字段。
SELECT
sum( price )
FROM
order_info
avg()
avg(field)
统计某个字段的平均数,field为要统计的字段。
SELECT
avg( price )
FROM
order_info
round()
round(number,x)
返回某个数字按指定位数取整后的数字,number
被操作的数字,x
为精确的位数
select round(123.4567,2) # 返回 123.4500
abs()
abs(number)
取绝对值
select abs(-500) # 返回500
rand()
rand()
返回0-1.0之间的浮点随机数
select rand() -- 可能返回 0.566122031
类型转换
cast()
cast(data as type)
将一种类型的数据转换成另一种类型。data为数据, type为类型。必须是数据库支持的数据类型。
select cast('123' as int) # 将字符的123转换成数字的123
查看数据库支持的数据类型
SELECT DATA_TYPE FROM information_schema.COLUMNS GROUP BY DATA_TYPE;
字符串函数
concat()
concat(data1,data2,data3,datan...)
拼接字符串。如果其中一个数据为null
则返回null
。
select concat(1,2,3,4) # 返回 1234
select concat(1,2,3,null) # 返回 null
concat_ws()
concat_ws(separator,data1,data2,data3,datan...)
根据指定的分隔符(separator
)拼接字符串,存在null
则拼接空白。
select concat_ws(',',1,2,3,4) # 返回 1,2,3,4
select concat_ws('-',1,2,3,4) # 返回 1-2-3-4
select concat_ws('-',1,2,3,null) # 返回 1-2-3
group_concat()
group_concat()
根据某个字段分组然后汇总数据。
# 假设有个表(id,name) 数据有5条(1,张三)(2,李四)(3,王五)(4,狗蛋)(5,狗der)
# 获取name字段的汇总
select group_concat(name) from test -- 返回 张三,李四,王五,狗蛋,狗der
# 获取name字段的汇总,根据id进行降序排序
select group_concat(name order by id desc) from test -- 返回 狗der,狗蛋,王五,李四,张三
date_format()
date_format(dateStr,pattern)
格式化时间函数。dataStr
为要格式化的时间,pattern
为格式。
SELECT DATE_FORMAT('2011-09-20 08:30:45', '%Y-%m-%d'); # 返回 2011-09-20
可以使用的格式有:
格式 | 描述 |
---|---|
%a | 缩写星期名 |
%b | 缩写月名 |
%c | 月,数值 |
%D | 带有英文前缀的月中的天 |
%d | 月的天,数值(00-31) |
%e | 月的天,数值(0-31) |
%f | 微秒 |
%H | 小时 (00-23) |
%h | 小时 (01-12) |
%I | 小时 (01-12) |
%i | 分钟,数值(00-59) |
%j | 年的天 (001-366) |
%k | 小时 (0-23) |
%l | 小时 (1-12) |
%M | 月名 |
%m | 月,数值(00-12) |
%p | AM 或 PM |
%r | 时间,12-小时(hh:mm:ss AM 或 PM) |
%S | 秒(00-59) |
%s | 秒(00-59) |
%T | 时间, 24-小时 (hh:mm:ss) |
%U | 周 (00-53) 星期日是一周的第一天 |
%u | 周 (00-53) 星期一是一周的第一天 |
%V | 周 (01-53) 星期日是一周的第一天,与 %X 使用 |
%v | 周 (01-53) 星期一是一周的第一天,与 %x 使用 |
%W | 星期名 |
%w | 周的天 (0=星期日, 6=星期六) |
%X | 年,其中的星期日是周的第一天,4 位,与 %V 使用 |
%x | 年,其中的星期一是周的第一天,4 位,与 %v 使用 |
%Y | 年,4 位 |
%y | 年,2 位 |
length()
length()
返回字符串的长度。
select length('123') # 返回3
replace()
replace()
替换指定字符串中的指定字符串为指定字符串
select replace('1234abcd','1234','aaaaa-') # 返回 aaaaa-abcd
find_in_set()
find_in_set()
查找指定的字符串在不在字符串列表中
select find_in_set('狗蛋','狗蛋,狗der,大宝贝') -- 返回1,因为找到了
select find_in_set('狗蛋1','狗蛋,狗der,大宝贝') -- 返回0 没找到
locate()
locate()
返回字符串首次出现的位置,下标从1开始
select locate('2','123') -- 返回2
select locate('1','123') -- 返回1
日期函数
curdate()
curdate()
返回今天的日期
select CURDATE() -- 返回 2022-11-08
curtime()
curtime()
返回今天的时间
select curtime() -- 返回 11:28:07
now()
now()
返回日期和时间
select now() -- 返回2022-11-08 11:28:25
逻辑函数
if()
if()
判断表达式是否成立,格式if(表达式,成立返回,不成立返回)
,注意成立和不成立的返回必须是一个类型的,要是字符串必须都是字符串,不能一个是字符串一个是数字。
if(1=1,'成立','不成立') -- 返回 成立
if(1 is not null,'成立','不成立') -- 返回 成立
if(1=2,'成立','不成立') -- 返回 不成立
if(1 is not null and 1=1,'成立','不成立') -- 返回 成立
if(1 is not null and 1=2,'成立','不成立') -- 返回 不成立
case
case
多表达式判断,返回最先成立表达式的结果,格式CASE WHEN 表达式1 THEN 表达式1成立的结果 WHEN 表达式2 THEN 表达式2成立的结果 ELSE 都不成立的结果 END
select case when 1=1 then '1=1' when 2=2 then '2=2' else '都不成立' end -- 返回 1=1
select case when 1=2 then '1=1' when 2=2 then '2=2' else '都不成立' end -- 返回 2=2
select case when 1=2 then '1=1' when 2=3 then '2=2' else '都不成立' end -- 返回 都不成立
ifnull()
ifnull()
如果传入的值为null,就返回指定的值,不为null,就返回传入的值
select ifnull(1,'111') -- 返回 1
select ifnull(null,'111') -- 返回 111
开窗函数
MySQL 8.0之后才支持!
示例数据
id | name | birthday | weight |
---|---|---|---|
1 | 大宝贝 | 2002-12-12 | 75KG |
2 | 狗蛋 | 2001-10-09 | 50KG |
3 | 狗der | 2002-10-09 | 50KG |
row_number()
根据某个字段分组,根据根据某个字段排序,给出序号
示例1 不分组,单纯获取序号,根据birthday
降序
select name ,row_number() over (order by birthday desc) as rank_sort from test_table
返回结果
name | birthday | rank_sort |
---|---|---|
大宝贝 | 2002-12-12 | 1 |
狗der | 2002-10-09 | 2 |
狗蛋 | 2001-10-09 | 3 |
示例2 根据体重weight
分组,根据birthday
降序
select name, weight ,row_number() over (partition by weight order by birthday desc) as rank_sort from test_table
返回结果
name | weight | rank_sort | 备注(这个是自己加上去,写备注的) |
---|---|---|---|
大宝贝 | 75KG | 1 | 因为75KG只有一条数据,所以rank_sort就是1 |
狗der | 50KG | 1 | 因为50KG只有两条数据,狗der的birthday第一大,所以rank_sort就是1 |
狗蛋 | 50KG | 2 | 因为50KG只有两条数据,狗蛋的birthday第二大,所以rank_sort就是2 |