SQL函数及高级用法

Adda_Chen

已于 2024-09-12 17:57:08 修改

阅读量3.1k

点赞数 1

分类专栏：学习笔记文章标签： sql 数据库 java

于 2023-08-26 23:55:55 首次发布

本文链接：https://blog.csdn.net/adda_chen/article/details/132517510

版权

学习笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

一、日期函数

1、获取当前系统时间

SELECT 
  CURRENT_TIMESTAMP() AS `day`,-- 当天 全格式 2023-08-25 14:09:20
  CURDATE() AS `date`, -- 当天 日期 2023-08-25
  CURRENT_TIME() AS `time` ; -- 当前时分秒 14:09:20

2、获取指定时间XX天之前/之后的日期

当前时间全格式

--
SELECT 
  SUBDATE(CURRENT_TIMESTAMP(), INTERVAL 5 DAY) AS `五天前`, -- 2023-08-20 14:10:20
  CURRENT_TIMESTAMP() AS `今天`, 							-- 2023-08-25 14:10:20
  ADDDATE(CURRENT_TIMESTAMP(), INTERVAL 5 DAY) AS `五天后` ; -- 2023-08-30 14:10:20

当前时间日期


--
SELECT 
  SUBDATE(CURDATE(), INTERVAL 5 DAY) AS `五天前`,			-- 2023-08-20
  CURDATE() AS `今天`,     							   -- 2023-08-25
  ADDDATE(CURDATE(), INTERVAL 5 DAY) AS `五天后` ;          -- 2023-08-30

指定时间

-- 
SELECT 
  SUBDATE('2023-01-01', INTERVAL 5 DAY) AS `五天前`,         -- 2022-12-27
  ADDDATE('2023-01-01', INTERVAL 5 DAY) AS `五天后` ;        -- 2023-01-06

3、从日期或日期时间表达式中提取日期值

给定时间格式中截取日期

SELECT 
  DATE("2017-06-15 11:11:16"),
  DATE("2017-06-15T11:11:16"),
  DATE("2017-06-15") ;

4、提取年月日

-- 当前时间 
SELECT 
  YEAR(CURRENT_TIMESTAMP()) AS `year`,
  MONTH(CURRENT_TIMESTAMP()) AS `month`,
  DAY(CURRENT_TIMESTAMP()) AS `day` ;

-- 给定时间
SELECT 
  YEAR('2023-01-01') AS `year`,
  MONTH('2023-01-01') AS `month`,
  DAY('2023-01-01') AS `day` ;

5、计算两个日期之间相隔的天数

计算方式：前一个日期减去后一个日期 d1-d2

-- 计算日期 d1-d2 之间相隔的天数
SELECT DATEDIFF('2001-01-01','2001-02-02'); -- 前一个时间减去后一个时间 -32天

SELECT DATEDIFF('2001-02-02','2001-01-01'); -- 前一个时间减去后一个时间 32天

SELECT DATEDIFF('2049-01-01',CURDATE());    -- 给定时间减去当前时间 

SELECT ABS(DATEDIFF('2023-01-01','2023-05-05')); -- 外层套一个绝对值处理函数，就不用在意两个日期之间的顺序了 124天

6、计算两个日期之间相隔的年数、天数、月数、小时

-- 相差年数
SELECT TIMESTAMPDIFF(YEAR,'2023-07-01','2023-08-26');-- 0
-- 相差月数
SELECT TIMESTAMPDIFF(MONTH,'2023-07-01','2023-08-26');-- 1
-- 相差天数
SELECT TIMESTAMPDIFF(DAY,'2023-07-01','2023-08-26'); -- 56
-- 相差周数 
SELECT TIMESTAMPDIFF(WEEK,'2023-07-01','2023-08-26'); -- 8
-- 相差季度（三个月）数
SELECT TIMESTAMPDIFF(QUARTER,'2023-02-01','2023-04-01'); -- 56

-- 相差小时数
SELECT TIMESTAMPDIFF(HOUR,'2023-08-25','2023-08-26'); -- 24
SELECT TIMESTAMPDIFF(HOUR,'2023-08-26 12:00:00','2023-08-26 12:55:00'); -- 0
SELECT TIMESTAMPDIFF(HOUR,'2023-08-26 12:00:00','2023-08-26 13:00:00'); -- 1
SELECT TIMESTAMPDIFF(HOUR,'2023-08-26 12:00:00','2023-08-26 13:30:00'); -- 1

6、时间格式化

SELECT DATE_FORMAT('2023.08.25 11:11:11','%Y-%m-%d %h:%m:%s');

7、返回今天是星期几

-- 周一Monday、周二Tuesday、周三Wednesday、周四Thursday、周五Friday、周六Saturday、周日Sunday、 
SELECT DAYNAME(CURDATE()) ;


-- 返回周几的数字，但是周日=1，周一=2，以此类推
SELECT DAYOFWEEK(CURDATE());

8、计算日期是本年、本月的第几天

-- 返回给定日期是本年的第几天
SELECT DAYOFYEAR('2023-08-25 11:11:11'); -- 237

-- 返回给定日期是本月的第几天
SELECT DAYOFMONTH('2023-08-25 11:11:11'); -- 25

-- 返回给定日期是本周的第几天(但是周日=1，周一=2，以此类推)
SELECT DAYOFWEEK('2023-08-25 11:11:11'); -- 6
SELECT (DAYOFWEEK('2023-08-25 11:11:11')-1); -- 5 可以手动减一

9、获取当月最后一天的日期

-- 获取给定日期当月的最后一天
SELECT LAST_DAY(NOW());
SELECT LAST_DAY('2023-07-01');-- 2023-07-31
SELECT LAST_DAY('2023-06-01');-- 2023-06-30

9、从给定时间中解析指定值

-- 字段解析
SELECT EXTRACT(YEAR FROM '2023-12-13 14:15:16'); -- 年 2023
SELECT EXTRACT(MONTH FROM '2023-12-13 14:15:16'); -- 月 12
SELECT EXTRACT(DAY FROM '2023-12-13 14:15:16'); -- 日 13
SELECT EXTRACT(HOUR FROM '2023-12-13 14:15:16'); -- 时 14
SELECT EXTRACT(MINUTE FROM '2023-12-13 14:15:16'); -- 分 15
SELECT EXTRACT(SECOND FROM '2023-12-13 14:15:16'); -- 秒 16
SELECT EXTRACT(MICROSECOND FROM '2023-12-13 14:15:16'); -- 微秒 0

-- 季度解析
SELECT EXTRACT(QUARTER FROM '2023-04-13 14:15:16'); -- 季度 1~3月第一季度 4~6第二季度 7~9第三季度 10~12第四季度

-- 自由拼接
SELECT EXTRACT(MINUTE_SECOND FROM '2023-04-13 14:15:16'); -- 拼接 分秒
SELECT EXTRACT(YEAR_MONTH FROM '2023-04-13 14:15:16'); -- 拼接 年月

10、时间戳\日期互转

-- 日期转时间戳
SELECT UNIX_TIMESTAMP('2023-08-25'); -- 1692892800

-- 时间戳转日期
SELECT FROM_UNIXTIME(1692892800);    -- 2023-08-25 00:00:00

二、字符串函数

1、查询字符串的字符数

SELECT LENGTH('1234'); -- 4
SELECT CHAR_LENGTH('1234'); -- 4
SELECT CHARACTER_LENGTH('1234'); -- 4

2、拼接字符串

表数据：

-- 结果拼接
SELECT CONCAT('查出的字符串为：',str) AS str FROM for_sql;

3、匹配字符串位置下标

 SELECT FIND_IN_SET("c", "a,b,c,d,e"); -- 3 以 ，作为分割，返回指定字符串在给定字符串中的位置下标
 SELECT FIND_IN_SET("000", "a,b,c,d,e");-- 0 不存在的返回0
 SELECT FIND_IN_SET("李四","王五,张三,李四,赵六,刘琦"); -- 3 汉字也能匹配
 
 SELECT CONCAT('排名为：',FIND_IN_SET("李四","王五,张三,李四,赵六,刘琦")) ;-- 可以搭配组合拼接使用

4、大小写互转

 -- 大写转小写
 SELECT LOWER('RUNOOB'); -- runoob
 -- 小写转大写
 SELECT UCASE('runoob'); -- RUNOOB

5、去除空格

-- 去除开始和结尾处的空格
SELECT TRIM('    RUNOOB    ');-- 
-- 去除左侧的空格
SELECT LTRIM('    RUNOOB    ');-- 
-- 去除右侧的空格
SELECT RTRIM('    RUNOOB    ');--

6、截取字符串

-- 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符，返回UNO
SELECT SUBSTR("RUNOOB", 2, 3); -- UNO
SELECT SUBSTRING("RUNOOB", 2, 3); -- UNO

7、反转字符串

-- 反转字符串
SELECT REVERSE('abc'); -- cba

8、字符串比较

-- 比较两个字符串，如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1。
SELECT STRCMP("runoob", "runoob"); -- 0
SELECT STRCMP("runoob", "runoob11");-- -1
SELECT STRCMP("runoob11", "runoob");-- 1

9、根据符号截取字符串

-- SUBSTRING_INDEX(str,sep,num) str表示操作的字符串  sep是区分的符号 num是一共取几个，正数是从左往右数负数是从右往左数

SELECT SUBSTRING_INDEX("RUN,OOB,STR", ',', -1);  -- STR
SELECT SUBSTRING_INDEX("RUN,OOB,STR", ',', -2); -- OOB,STR
SELECT SUBSTRING_INDEX("RUN,OOB,STR", ',', 1);  -- RUN
SELECT SUBSTRING_INDEX("RUN,OOB,STR", ',', 2); -- RUN,OOB

10、查询字符串的长度


SELECT CHAR_LENGTH(str),str FROM `for_sql`; --

在这里插入图片描述

11、查询出的多行的相同列进行拼接

初始的表视图

-- 初始表
SELECT 
  sur.`user_id` AS user_id,
  sr.`role_name`,
  sr.`role_code` 
FROM
  sys_user_role sur 
  LEFT JOIN `sys_role` sr  ON sur.`role_id` = sr.`id`

在这里插入图片描述
要将相同user_id 的角色名称拼接起来

GROUP_CONCAT（列名，‘加后缀，一般为空’）
在这里插入图片描述
注意添加group by

三、数字函数

1、将数字进行格式化，并指定小数位数

 -- 格式化数字，并保留5位小数
 SELECT FORMAT(250500.5634, 5);

2、取绝对值

-- 计算绝对值
SELECT ABS(-12); -- 12

3、聚合函数 - 求各种值

 -- 列求和 空值会被忽略
 SELECT SUM(num) FROM `for_sql`;-- 55
 -- 列求平均值 空值会被忽略
  SELECT AVG(num) FROM `for_sql`;-- 27.5
 -- 列求最大数
  SELECT MAX(num) FROM `for_sql`;-- 35
 -- 列求最小值
  SELECT MIN(num) FROM `for_sql`;-- 20
 -- 返回满足条件的行数，
  SELECT COUNT(1) FROM `for_sql`; -- 3
  SELECT COUNT(num) FROM `for_sql`; -- 2 ()内给字段的话，字段为null的行就不统计

4、向上\向下取整

-- 向上取整
SELECT CEIL(1.5);-- 2
SELECT CEILING(1.5); -- 2
-- 向下取整
SELECT FLOOR(1.5); -- 1

5、四舍五入求整

SELECT ROUND(1.000);-- 1
SELECT ROUND(1.456);-- 1
SELECT ROUND(1.5);  -- 2
SELECT ROUND(1.555);-- 2

6、四舍五入求指定位数小数

-- 上面四舍五入求整，其实就是没指定保留位数，默认为0位小数
SELECT ROUND(1.000,2);-- 1.00
SELECT ROUND(1.456,1);-- 1.5
SELECT ROUND(1.5,1);  -- 1.5
SELECT ROUND(1.555,2);-- 1.56

6、取随机数

-- 返回 0 到 1 的随机数，若()里面有数字,RAND(x),x相同时，返回值相同
SELECT RAND();-- 0.6380155502241249
SELECT RAND(12); -- （）中有值的时候，回根据给定值去计算一个随机数，括号内值不变，随机数就不变
SELECT RAND(13);

7、取列中最大值、最小值

-- 求多个列中最大值
SELECT GREATEST(3, 12, 34, 8, 25);-- 34
-- 求多个列中最小值
SELECT LEAST(3, 12, 34, 8, 25);-- 3

8、求给定值的正负号

-- 返回 给定值的符号，负数返回-1、0返回0、正数返回1
SELECT SIGN(-10);-- -1
SELECT SIGN(0);-- 0
SELECT SIGN(10);-- 1

9、指定保留小数位

-- 不进行四舍五入，不够小数位就补0
SELECT TRUNCATE(1.23456,1); -- 1.2
SELECT TRUNCATE(1.23456,3); -- 1.234
SELECT TRUNCATE(1.23456,6); -- 1.234560

四、高级函数

1、判断处理

IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2

-- num大于三十的输出，不大于三十的输出0
SELECT IF(num>30,num,0) FROM `for_sql`;

结果：

-- num值为空的按0输出，其余正常输出
SELECT IF(num IS NULL,0,num) FROM `for_sql`;

2、判空处理值

相当于是IF()的特例，只处理为空的情况

-- 如果num值为空,就用0替换
SELECT IFNULL(num,0) FROM `for_sql`;

3、case…when的用法

① 等值判断：可以实现多条件的查询值匹配筛选

-- case 某个列字段，然后when 这个列的值满足某个条件时，将额外加一列用以描述该字段，必须要使用end结尾，否则报错
-- 最好使用else将其他值统一描述，不加不会报错，只是额外用来描述的这一列，到该行数据时会为空
SELECT 
  num,
  CASE num 
  WHEN 35 THEN '分数为35' 
  WHEN 20 THEN '分数为20' 
  ELSE '其余分数' 
  END AS str 
FROM
  `for_sql` ;

②条件区间判断

-- 注意，如果是需要判断表达式，那再case的时候就不要指定字段了
-- 判断不为空，要使用 is not null
SELECT 
  num,
  CASE 
  WHEN num =100                    THEN '满分，优秀'
  WHEN num >=60 AND num < 100      THEN '分数大于30' 
  WHEN num >0  AND num <60         THEN '分数未及格' 
  WHEN num = 0 AND num IS NOT NULL THEN '零分'
  ELSE '未打分' 
  END AS str 
FROM
  `for_sql` ;

③搭配其他函数使用

可用于统计及格率，将大于60更改为1，小于60更改为0；然后使用sum进行求和，这样就只会求和及格的数量，然后在除以总数，及得到及格率；

4、坐标距离函数

-- st_distance_sphere函数计算两个point对象之间的距离
-- point()函数将经纬度转换成point对象		
SELECT 
  st_distance_sphere (
    POINT ('113.625246', '25.746557'),
    POINT ('125.625246', '25.746557')
  ) AS distance    -- 1201453.1503102053米

五、系统信息函数

1、数据库信息

-- 返回当前用户
SELECT USER(); -- webUser@223.88.63.102

-- 返回当前数据库名
SELECT DATABASE();-- module-learn

-- 返回当前数据库版本号
SELECT VERSION(); -- 8.0.13

2、将数据库自增列修改为指定值

前提：之所以不用指定字段名，是因为一张表里面只允许有一个自增列

-- 将某表的自增改为指定值
ALTER TABLE XXX AUTO_INCREMENT = 11804;

六、语句

1、更新一个表的字段值等于另一个表的字段值

MySQL:
update 
	B inner join A on B.userId=A.userId
set 
	B.username = A.username,
	B.phone = A.phone 

-- 后面还可以加 where 来针对两个表进行数据筛选

示例：
在这里插入图片描述

更新语句：

-- 将for_sql表中的str字段更新为user_info 表中的user_name字段；
-- 关联关系为for_sql与user_info 的id相同
UPDATE `for_sql` INNER JOIN user_info 
ON `for_sql`.`id`=user_info.id 
SET `for_sql`.str=user_info.user_name
WHERE user_info.`id`='1' AND for_sql.`id` = '1'

结果：

在这里插入图片描述

2、纵表转横表

有查询语句，为查某个人的不同类型的个数，按照表结构查出来是纵向的，如下：

 -- 不指定省区，只看省总下每个客户的类型的数量，旧数据客户的省区可能与省总不同（暂不考虑）
SELECT 
 IFNULL(sur.sys_user_manage_id ,oui.sys_user_id) AS manage_id,
 ui.province_code,
 ui.user_info_type,
 COUNT(1)  
FROM `user_info` ui LEFT JOIN `oc_user_info` oui ON ui.id = oui.user_info_id
LEFT JOIN `sys_user_relation`  sur ON oui.sys_user_id = sur.sys_user_id
WHERE ui.is_sys_flag = '0' AND ui.province_code IS NOT NULL
GROUP BY ui.province_code,ui.user_info_type ,manage_id
HAVING manage_id = '100252' AND province_code = '640000'

在这里插入图片描述

现在需要将它改成一条数据，也就是横向一条数据展示，即为纵表转横表
语句如下：

-- 不指定省区，只看省总下每个客户的类型的数量，旧数据客户的省区可能与省总不同（暂不考虑）
-- 纵表转横表
SELECT 
 IFNULL(sur.sys_user_manage_id ,oui.sys_user_id) AS manage_id,
 ui.province_code,
 -- 因为需要计算，因此使用sum函数，而sum函数是将每一天加起来，因此每一条满足的应该then 为 1才能正确计数
 SUM(CASE ui.user_info_type WHEN '1' THEN 1 ELSE 0 END) '1',
 SUM(CASE ui.user_info_type WHEN '2' THEN 1 ELSE 0 END) '2',
 SUM(CASE ui.user_info_type WHEN '3' THEN 1 ELSE 0 END) '3' 
FROM `user_info` ui LEFT JOIN `oc_user_info` oui ON ui.id = oui.user_info_id
LEFT JOIN `sys_user_relation`  sur ON oui.sys_user_id = sur.sys_user_id
WHERE ui.is_sys_flag = '0' AND ui.province_code IS NOT NULL
GROUP BY ui.province_code,manage_id
HAVING manage_id = '100252' AND province_code = '640000'

3、拿MD5加密后的文本去数据库过滤明文

-- 1230 MD5加密之后是 4122cb13c7a474c1976c9706ae36521d
SELECT id FROM `user_info` WHERE MD5(id) = '4122cb13c7a474c1976c9706ae36521d'

结果如图：

在这里插入图片描述

4、查询某个字段去重统计的数量

select count(distinct 去重字段) from 表

5、对字符串类型的数字进行排序

如果直接对字符串类型的数字进行排序，它会先比较首字母，然后依次往后比较，这样是不对的。可以用下面的语句进行排序

-- 针对该字段进行运算，强制将其改成数字类型，然后再排序即可
SELECT * FROM `bone_tool_box` ORDER BY id+0 DESC

6.

– 结束 –

Adda_Chen

关注

1
点赞
踩
14

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录

SQL函数及高级用法

一、日期函数

1、获取当前系统时间

2、获取指定时间XX天之前/之后的日期

3、从日期或日期时间表达式中提取日期值

4、提取年月日

5、计算两个日期 之间相隔的天数

6、计算两个日期之间相隔的年数、天数、月数、小时

6、时间格式化

7、返回今天是星期几

8、计算日期是本年、本月的第几天

9、获取当月最后一天的日期

9、从给定时间中解析指定值

10、时间戳\日期 互转

二、字符串函数

1、查询字符串的字符数

2、拼接字符串

3、匹配字符串位置下标

4、大小写互转

5、去除空格

6、截取字符串

7、反转字符串

8、字符串比较

9、根据 符号 截取字符串

10、查询字符串的长度

11、查询出的多行的相同列进行拼接

三、数字函数

1、将数字进行格式化，并指定小数位数

2、取绝对值

3、聚合函数 - 求各种值

4、向上\向下取整

5、四舍五入求整

6、四舍五入求指定位数小数

6、取随机数

7、取列中最大值、最小值

8、求给定值的正负号

9、指定保留小数位

四、高级函数

1、判断处理

2、判空处理值

3、case…when的用法

① 等值判断：可以实现多条件的查询值匹配筛选

②条件区间判断

③搭配其他函数使用

4、坐标距离函数

五、系统信息函数

1、数据库信息

2、将数据库自增列修改为指定值

六、语句

1、更新一个表的字段值等于另一个表的字段值

2、纵表转横表

3、拿MD5加密后的文本去数据库过滤明文

4、查询某个字段去重统计的数量

5、对字符串类型的数字进行排序

6.

5、计算两个日期之间相隔的天数

10、时间戳\日期互转

9、根据符号截取字符串