SQL常用函数收藏

  • 开发
  • 22

日期时间相关

【date_format】

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。
参考地址：https://www.runoob.com/sql/func-date-format.html

-- 函数：date_format
select DATE_FORMAT(Now(),"%Y-%m-%d %H:%i:%S") as v_current_date, 
DATE_FORMAT(datetime,"%m - %w") 
from user

【from_unixtime】

from_unixtime() 是 SQL 中用于将 UNIX 时间戳转换为日期和时间格式的函数。
UNIX 时间戳是指从1970 年 1 月 1 日（UTC）开始的秒数。

-- 函数: FROM_UNIXTIME(unix_timestamp)
-- 注意:这里的unix_timestamp值单位必须为秒，毫秒将转化失败
select DATE_FORMAT(FROM_UNIXTIME(unixtime), "%Y-%m-%d %H:%i:%S") as datetime
from user

【unix_timestamp】

UNIX_TIMESTAMP 返回一个 UNIX® 时间戳，即自 '1970-01-01 00:00:00’以来的秒数（和小数秒）

-- 函数：unix_timestamp
select UNIX_TIMESTAMP(datetime), UNIX_TIMESTAMP(NOW()) from user

【date_sub】

DATE_SUB()是一个函数，用于从当前日期中减去指定的时间间隔;
DATE_SUB(date, INTERVAL unit value)
DATE_SUB() 函数的基本用法：
DATE_SUB() 函数接受三个参数：日期时间值、时间间隔和时间单位。
日期时间值可以是日期类型、日期时间类型或时间戳类型。
时间间隔是一个整数，表示要减去的数量。
时间单位可以是年份 (YEAR)、月份 (MONTH)、日期 (DAY)、小时 (HOUR)、分钟 (MINUTE)、秒钟 (SECOND) 等等。
返回计算后的日期时间值。
参考地址：https://www.jianshu.com/p/47783a06526f

-- 函数: date_sub
-- 1、从日期上减去指定的天数，这里减去7天后的日期
-- SELECT date_sub(DATE_FORMAT(Now(), "%Y-%m-%d"), interval 7 day)
-- 2、从日期上减去指定小时数, 这里从当前时间减去12个小时
-- SELECT date_sub(Now(), interval 12 HOUR)
-- 3、从日期上减去指定分钟,这里从当前日期减去60分钟
-- SELECT date_sub(Now(), interval 60 MINUTE)
-- 4、从日期上减去指定年, 这里从当前日期减去1年
-- SELECT date_sub(DATE_FORMAT(Now(), "%Y-%m-%d"), interval 1 YEAR)
-- 5、从日期上减去指定月，
-- SELECT date_sub(DATE_FORMAT(Now(), "%Y-%m-%d"), interval 1 MONTH)

【date_add】

DATE_ADD() 函数是用于在日期时间值上添加指定的时间间隔的函数。
它可以根据你的需求，在日期或日期时间字段中增加年、月、日、小时、分钟或秒钟等时间单位。

-- 函数: date_add
-- 1、从日期上加指定的天数，这里添加7天后的日期
-- SELECT date_add(DATE_FORMAT(Now(), "%Y-%m-%d"), interval 7 day)
-- 2、从日期上加指定小时数, 这里从当前时间加12个小时
-- SELECT date_add(Now(), interval 12 HOUR)
-- 3、从日期上加指定分钟,这里从当前日期加上60分钟
-- SELECT date_add(Now(), interval 60 MINUTE)
-- 4、从日期上加指定年, 这里从当前日期加1年
-- SELECT date_add(DATE_FORMAT(Now(), "%Y-%m-%d"), interval 1 YEAR)
-- 5、从日期上加指定月
-- SELECT date_add(DATE_FORMAT(Now(), "%Y-%m-%d"), interval 1 MONTH)

【NOW()】

NOW() 函数返回的是当前时间的年月日时分秒

-- 函数: NOW() 
-- 返回格式如：2024-02-21 14:31:45
SELECT NOW() FROM user;

【curdate】

返回当前年月日信息

-- 函数：curdate
SELECT curdate(); #2024-02-21

【curtime】

返回当前的时分秒信息

-- 函数：CURTIME()
select CURTIME(); #17:56:39

【datediff】

DATEDIFF函数用于返回两个日期的天数
格式：DATEDIFF(date1,date2)
DATEDIFF函数返回date1 - date2的计算结果，date1和date2两个参数需是有效的日期或日期时间值;
如果参数传递的是日期时间值，DATEDIFF函数仅将日期部分用于计算，并忽略时间部分(只有值的日期部分参与计算)
参考文档:https://blog.csdn.net/Hudas/article/details/124466093

-- 函数：DATEDIFF(expr1,expr2)
-- SELECT NOW(), datetime, DATEDIFF(NOW(), datetime) as date_diff from user
SELECT DATEDIFF('2024-04-30 13:00:00','2024-04-29 14:00:00');

【timediff】

返回两个时间相减得到的差值，time1-time2
语法：timediff(time1,time2)

-- 函数：TIMEDIFF(expr1,expr2)
-- 返回格式：02:32:55, 必须是有效日期时间格式
-- SELECT NOW(), datetime, TIMEDIFF(NOW(), datetime) as time_diff from user
SELECT TIMEDIFF('2024-02-21 00:00:00', '2024-02-20 00:00:00');

【timestampdiff】

返回（时间2-时间1）的时间差，结果单位由interval参数给出。
语法：timestampdiff(interval, datetime1,datetime2)
参考文档：https://blog.csdn.net/goodboxs/article/details/122545490
注意：MySQL 5.6之后才支持毫秒的记录和计算，如果是之前的版本，最好是在数据库除datetime类型之外的字段，再建立用于存储毫秒的int字段，然后自己进行转换计算。

-- 函数：timestampdiff
SELECT TIMESTAMPDIFF(SECOND,'2024-2-19','2024-2-20');  # 86400秒
SELECT TIMESTAMPDIFF(MINUTE,'2024-2-19','2024-2-20');  # 1440分钟
SELECT TIMESTAMPDIFF(HOUR,'2024-2-19','2024-2-20');    # 24小时
SELECT TIMESTAMPDIFF(DAY,'2024-1-19','2024-2-20');     # 32天
SELECT TIMESTAMPDIFF(WEEK,'2024-1-19','2024-2-20');    # 4周
SELECT TIMESTAMPDIFF(MONTH,'2024-1-19','2024-2-20');   # 1个月
SELECT TIMESTAMPDIFF(QUARTER,'2023-1-19','2024-2-20'); # 4个季度
SELECT TIMESTAMPDIFF(YEAR,'2023-1-19','2024-2-20');    # 1年

字符串相关

【upper】

SQL UPPER 函数 以大写形式显示结构化查询语言中的所有字符串字符。它将小字符或一组小字符转换为大写字母。

-- 函数: upper
select id, upper(username), password, IFNULL(city, '暂无省份') 
from user

【lower】

SQL LOWER 函数 以大写形式显示结构化查询语言中的所有字符串字符。它将小字符或一组小字符转换为大写字母。

-- 函数: LOWER(str)
select id, lower(username), password, IFNULL(city, '暂无省份') 
from user

【concat】

CONCAT函数用于将两个字符串连接起来，形成一个单一的字符串。

-- 函数: concat
-- 注意: 这里当city或area为null值时将返回null字符串
-- SELECT  concat(username, password) as userpass, concat(city, area) as address from user
-- 解决方案：使用上面的ifnull来设置为空值
SELECT  concat(username, password) as userpass, concat(ifnull(city, ""), ifnull(area, "")) as address from user

【concat_ws】

CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。
第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
注意： 如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

-- 函数：CONCAT_WS(separator,str1,str2,...)
SELECT  concat_ws(':', username, password) as userpass, 
concat_ws("-", city, area) as address from user

【group_concat】

Group_concat() 函数 将一个分组中的指定元素（列或表达式）的值，拼接成一个字符串返回（用逗号分隔）
完整的语法如下： group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
参考地址：https://blog.csdn.net/weixin_46484674/article/details/126662192

-- 函数: grup_concat
-- SELECT city, GROUP_CONCAT(area) as area_list
-- from user
-- where city='四川'

-- 1、查询city=四川的用户和密码
SELECT 
	city, 
	GROUP_CONCAT(
	CONCAT_WS( '-', username, password) order by username DESC SEPARATOR ' / ') as user_list
from user
where city='四川'

【repeat】

用来复制字符串,如下’ab’表示要复制的字符串，2表示复制的份数

-- 函数: repeat
-- select REPEAT(city,5) as repeat_city from user

【substring_index】

字符串截取,格式：substring_index(str,delim,count)
注释：str：要处理的字符串；delm：分隔符；count：计数
参考文档：https://blog.csdn.net/belief_009/article/details/123680354

-- 函数:SUBSTRING_INDEX(str,delim,count)
-- 1、正数值从左到右返回
-- select substring_index(username, ".", 1) from user where username = 'www.xlc.cn'
-- 2、负数值从右到左返回
select substring_index(username, ".", -1) from user where username = 'www.xlc.cn'

【substring】

substring 函数是用来截取字符串中的一部分字符

-- 函数：substring
-- 1、这里的值取中文和英文数值一样；
-- 从city里第1位开始取2个字符，从username中第3位开始，取3个字符(这里的3包括第三个字符，开始值从1，不是从0开始计算)
select substring(city, 1, 2), substring(username, 3, 3) from user

其它

【CAST 】

CAST函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型
CAST(value AS datatype)
AS关键字用于分隔两个参数，在AS之前的是要处理的数据，在AS之后的是要转换的数据类型
datatype格式如下：
1、DATE 将value转换成’YYYY-MM-DD’格式
2、DATETIME 将value转换成’YYYY-MM-DD HH：MM：SS’格式
3、TIME 将value转换成’HH：MM：SS’格式
4、CHAR 将value转换成CHAR(固定长度的字符串)格式
5、SIGNED 将value转换成INT(有符号的整数)格式
6、UNSIGNED 将value转换成INT(无符号的整数)格式
7、DECIMAL 将value转换成FLOAT(浮点数)格式
8、BINARY 将value转换成二进制格式
参考文档：https://blog.csdn.net/Hudas/article/details/124399908

-- 函数：CAST
-- 1.将值转换为DATE数据类型
-- SELECT CAST('2024-02-21' AS DATE); #2024-02-21 将日期字符串 转化成 date格式
-- SELECT CAST(NOW() AS DATE);  #2024-02-21 将NOW()返回的年月日 时分钞格式转化成日期格式

-- 2.将值转换为DATETIME数据类型
-- SELECT CAST('2024-02-21' AS DATETIME);  #2024-02-21 00:00:00

-- 3.将值转换为TIME数据类型
-- SELECT CAST('21:25:10' AS TIME); #14:06:10
-- SELECT CAST('2024-04-27 14:06:10' AS TIME);  #14:06:10

-- 4.将值转换为CHAR数据类型
-- SELECT CAST(150 AS CHAR); 
-- SELECT CONCAT('Hello World ',CAST(437 AS CHAR));


-- 5.将值转换为SIGNED数据类型
-- SELECT CAST('5.0' AS SIGNED); #5
-- SELECT (1 + CAST('3' AS SIGNED))/2; #2.0000
-- SELECT CAST(5-10 AS SIGNED); #-5
-- SELECT CAST(6.4 AS SIGNED);  #6四舍五入
-- SELECT CAST(6.5 AS SIGNED);  #7四舍五入
-- SELECT CAST(-6.5 AS SIGNED); #-7

-- 6.将值转换为UNSIGNED数据类型
-- SELECT CAST('5.0' AS UNSIGNED); #5
-- SELECT CAST(6.4 AS UNSIGNED);   #6
-- SELECT CAST(-6.4 AS UNSIGNED);  #0
-- SELECT CAST(6.5 AS UNSIGNED);   #7
-- SELECT CAST(-6.5 AS UNSIGNED);  #0

--  7.将值转换为DECIMAL数据类型
-- SELECT CAST('9.0' AS DECIMAL); #9
-- -- DECIMAL(数值精度，小数点保留长度)
-- -- DECIMAL(10,2)可以存储最多具有8位整数和2位小数的数字
-- -- 精度与小数位数分别为10与2
-- -- 精度是总的数字位数，包括小数点左边和右边位数的总和小数位数是小数点右边的位数
-- SELECT CAST('9.5' AS DECIMAL(10,2)); #9.50
-- SELECT CAST('1234567890.123' AS DECIMAL(10,2)); #99999999.99
-- SELECT CAST('220.23211231' AS DECIMAL(10, 3));  #220.232
-- SELECT CAST(220.23211231 AS DECIMAL(10, 3));    #220.232

【round】

在mysql中，round函数用于数据的四舍五入，它有两种形式：
1、round(x,d) ，x指要处理的数，d是指保留几位小数
这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；
2、round(x),其实就是round(x,0),也就是默认d为0；

-- 函数：ROUND
select round(1123.26723,2);  #1123.27
select round(1123.26723,1);  #1123.3(小数四舍五入)
select round(1123.26723,0);  #1123
select round(1123.26723,-1); #1120(-1位用0补充)
select round(1123.26723,-2); #1100(-1位用0补充)
select round(1123.26723); #1123

【coalesce】

COALESCE是一个函数， (expression_1, expression_2, …,expression_n)依次参考各参数表达式，
遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
使用COALESCE在于大部分包含空值的表达式最终将返回空值。

-- 函数：coalesce
--  SELECT * from user
-- 1、当city值为null时返回第二个参数，注意:这里不是指存储的为"null"字符串
-- select id, username, password, COALESCE(city,"暂无") as city from user

-- 2、当city不为null，那么无论area是否为null，都将返回city的真实值（因为city是第一个参数）
--    当city为null，而area不为null的时候，返回area的真实值。
--    当city和area均为null的时候，将返回“暂无”。
select id, username, password, COALESCE(city, area, "暂无") as city, area from user

【case when】

用法：1：case 字段名 when 字段值 then 值1 else 值2 end
这一种是之前比较常用的一种方式，相当于是大部分编程语言中的switch case的用法,
通过字段名，去匹配字段值，适合字段值比较固定的情况下使用，特点是比较简洁易用。

-- 3、统计score 小于60分人数和大于60分人数,求不合格的平均分数和合格的平均分数
select 
count(case when score >=0 and score<=59 then 1 end) as user_count_0_59,
count(case when score >=60 then 1 end) as user_count_60_100,
AVG(
   case 
		when score >=0 and  score<=59 then score
	  when score >=60 and score<=100 then score
		else null
		end 
) as average_score_0_59,
AVG(
   case 
	  when score >=60 and score<=100 then score
		else null
		end 
) as average_score_60_100
from user


-- 4、需求：ID=1、5、6用户score设置为0，其它ID使用正常score值
-- SELECT id, username,
-- (case 
--  when id in(1, 5, 6) then 0
--  else score 
--  end ) as score
-- from user

【ifnull()】

在mysql中IFNULL() 函数用于判断第一个表达式是否为 NULL，如果第一个值不为NULL就执行第一个值。第一个值为 NULL 则返回第二个参数的值。
如果a不为空，则返回a，如果a为空（就是查找不到的意思），就返回b

-- 函数：isnull
select id, username, password, IFNULL(city, '暂无省份')
from user
阅读全部

相关推荐

  1. SQL常用函数收藏

    2024-02-22 00:50:02       23 阅读

  2. SQL常用函数

    2024-02-22 00:50:02       18 阅读

  4. hive sql常用函数

    2024-02-22 00:50:02       37 阅读

  6. SQL常用时间处理函数总结

    2024-02-22 00:50:02       39 阅读

  7. SQL之常用字符串函数

    2024-02-22 00:50:02       23 阅读

  8. sql sqlserver常用日期函数

    2024-02-22 00:50:02       21 阅读

  10. sql常用函数积累（非窗口函数）

    2024-02-22 00:50:02       32 阅读

最近更新

  4. TCP协议是安全的吗?

    2024-02-22 00:50:02       16 阅读

  12. 阿里云服务器执行yum，一直下载docker-ce-stable失败

    2024-02-22 00:50:02       16 阅读

  13. 【Python教程】压缩PDF文件大小

    2024-02-22 00:50:02       15 阅读

  18. 通过文章id递归查询所有评论（xml）

    2024-02-22 00:50:02       18 阅读

热门阅读

  1. 前端关于Vue跳转外部链接（百度为例）

    2024-02-22 00:50:02       31 阅读

  4. P8662 [蓝桥杯 2018 省 AB] 全球变暖--2024蓝桥杯冲刺省一

    2024-02-22 00:50:02       34 阅读

  5. firewall防火墙配置实战

    2024-02-22 00:50:02       30 阅读

  7. 面试指南：C++之STL知识点 C++内存分配策略 深入理解STL空间分配器(一): new_allocator 深入理解STL空间分配器(二)：mt_allocator 深入理解STL空间分配器(三)：pool_allocator 深入理解STL空间分配器(四)：bitmap_allocator

    2024-02-22 00:50:02       27 阅读

  9. Python提取xml节点

    2024-02-22 00:50:02       32 阅读

  12. Android批量加载图片OOM问题

    2024-02-22 00:50:02       30 阅读

  13. Android输入法相关(一)

    2024-02-22 00:50:02       24 阅读

  14. Mysql卸载

    2024-02-22 00:50:02       26 阅读

  15. C# action,delegate,func的用法和区别

    2024-02-22 00:50:02       29 阅读

  17. 如何解决AI场景下的冯诺伊曼陷阱？

    2024-02-22 00:50:02       26 阅读

  20. RESTful 风格是指什么

    2024-02-22 00:50:02       27 阅读

  21. vue从入门到进阶的30个vue代码示例

    2024-02-22 00:50:02       32 阅读

  22. Docker基于本地文件安装Nacos单机版

    2024-02-22 00:50:02       28 阅读

  26. 设计模式学习笔记 - 面向对象 - 1.面向对象到底讨论的是什么

    2024-02-22 00:50:02       26 阅读

  28. SQL语句分为以下三种类型

    2024-02-22 00:50:02       29 阅读

  29. Python 机器学习 决策树 分类原理

    2024-02-22 00:50:02       31 阅读

  30. C#程序反编译经验总结

    2024-02-22 00:50:02       31 阅读