MYSQL函数

满天丄星

已于 2024-07-12 16:51:41 修改

阅读量60

点赞数

分类专栏： Java MySQL 文章标签：数据库 sql

于 2023-11-10 18:52:27 首次发布

本文链接：https://blog.csdn.net/qq_57197323/article/details/134336710

版权

Java 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

MySQL

4 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的各种函数，如拼接（CONCAT,CONCAT_WS）,分组（GROUP_CONCAT）,条件判断（IF,IFNULL,NULLIF,ISNULL）,时间处理（INTERVAL,DATE_FORMAT,TIMESTAMPDIFF）,字符串操作（LOCATE,TRIM）以及JSON数据解析（json_extract）。

摘要由CSDN通过智能技术生成

前言

例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了机器学习的基础内容。

一、MYSQL 函数使用

1 数据表

id|name |password|birthday  |hobby      |
--+-----+--------+----------+-----------+
 1|tom  |123456  |2021-12-12|打球 lol sing|
 2|jerry|123456  |2021-12-12|movie      |

1 CONCAT() 函数用于拼接数据

格式

concat('param1','param2','param3')

说明拼接多个参数
举例

select * from `user` u 
where hobby like concat('%','球','%') 

select concat(name,':',birthday) from `user` u

2 CONCAT_WS() 函数用于拼接数据,并可以指定拼接符号

格式

concat('param1','param2','param3')

说明拼接多个参数，第一个参数为分隔符，禁止为 null
举例

select concat_ws(' like: ',name,hobby) as myLike from `user` u

3 GROUP_CONCAT() 函数用于获取列表

格式

group_concat('param1')

说明拼接多个参数，第一个参数为分隔符，禁止为 null
举例

select group_concat(distinct dept seoarator '#') as allDept from `user` u 

select group_concat( dept order by dept) as allDept from `user` u

4 IF() 函数用于获取列表

格式

IF(expression,'param1','param2')

说明 expression返回值为布尔值的表达式，表达式值true 则返回 param1 ，表达式值为false 则返回 param2
举例

select  if(u.sex=0,'男','女')as sex from user  u

5 IFNULL() 函数用于获取列表

格式

ifnull(expression,'param1')

说明 expression为null 时返回 param1 ，expression不为 null 时返回 expression
举例

select ifnull(u.hobby,'read book') from user  u

6 NULLIF() 函数用于获取列表

格式

nullif ('param1','param2')

说明 param1 与 param2 相等时返回 null ，param1 与 param2不相等时返回 param1
举例

select nullif ('a','a')from `user` u 

select nullif ('a','b')from `user` u

7 ISNULL() 函数用于获取列表

格式

isnull('param1')

说明 param1不为null 时返回0，param1不为null 时返回 1
举例

select isnull(u.hobby) as haveHobby from `user` u

8 INTERVAL 作为关键字时，表示为时间间隔，

说明常用在date_add()、date_sub()函数中，操作时间的变化
举例

select date_add("2023-11-16",interval+1 day) from `user` u 

select date_sub("2023-11-16",interval+1 day) from `user` u

9 INTERVAL() 作为函数时，表示为比较函数

格式 interval(num1,num2,num3,…)
说明比较num1后面比num1小的数字，后面的数字需要从小到大排序
举例

select interval (4,1,2,3,4,5,6,7,8) from `user` u 
// 4
select interval (4,1,2,5,6,2,5,1) from `user` u  
// 2

10 DATE_FORMAT() 函数用于规范时间格式

格式

DATE_FORMAT(date,format)

说明 date 时间数据 format: 需要转化的格式
举例

select date_format(birthday,'%Y-%m-%d %h:%m:%s') from `user` u

11 TIMESTAMPDIFF() 函数获取时间差

格式

iTIMESTAMPDIFF(unit, start_date, end_date)

说明 unit 时间单位 start_date 开始时间 end_date 结束时间返回结果结束时间 - 开始时间对应的时间单位数量可为负值
举例

select TIMESTAMPDIFF(day,date_format(birthday,'%Y-%m-%d %h-%m-%s'),'2020-12-12 12:12:12') as days from `user` u

12 LOCATE() 函数获取子字符串出现位置

格式1 locate(‘str1’,‘str2’)
格式2 locate(''str1,‘str2’,index)

iTIMESTAMPDIFF(unit, start_date, end_date)

说明格式1 返回 str2 在 str1 中出现的位置返回从index开始 str2 在 str1中出现的位置
举例

select locate('h','abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyz') as indexs from `user` u 

select locate('h','abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyz',27) as indexs from `user` u

13 TRIM() 函数去除指定符号或空格

格式1 TRIM(str)
格式2 TRIM(BTTH ‘str1’ from str2)

TRIM(BOTH '"' FROM '"TOM"')

说明去除 str2 中的 str1符号
举例

select TRIM(BOTH '"' from '"tom"') name  from user

14 json_extract 函数解析json数据

格式 json_extract(jsonData,‘$.str’
说明 jsonData为 josn格式数据 str 为 json数据中的一个key 该函数获取json数据中指定key的值
举例

create table `user` (
	`name` varchar(10) not null,
	`age` int(11) default null,
	`datum` json,
	PRIMARY key (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert into user (name,age,datum) values ('tom',
15,
'{
"sex":"男",
"like":"reading",
"notLike":"work"
}')
// 查询数据  
select name name ,age age ,json_extract(datum,'$.sex') sex ,json_extract(datum,'$.like') likes,json_extract(datum,'$.notLike') notlike
from user 
// 但是这种数据查询出来的json数据会带有双 ”“   ---- tom  | 15|"男"|"reading"|"work"   |

// 可以使用 trim函数去除 ”“
select name name ,age age ,TRIM(BOTH '"'from  json_extract(datum,'$.sex')) sex ,trim(both '"' from json_extract(datum,'$.like')) likes,trim(both '"' from json_extract(datum,'$.notLike')) notlike
from user  
-----    tom  | 15|男  |reading|work   |

15 FROM_UNIXTIME函数时间戳转date()类型

格式 FROM_UNIXTIME(time/1000 ,‘%Y-%m-%d %h:%i:%s’)
time是一个带毫秒的时间戳数据，如果不带毫秒则不需要 /1000，右侧是date格式
举例

select  FROM_UNIXTIME(ddr.create_time/1000 ,'%Y-%m-%d %h:%i:%s') from dev_device_register

16 now() CURDATE() 时间函数

now() 表示当前时间格式为 YYYY-MM-dd hh:mm:ss
curdate() 表示日期格式为 YYYY-MM-dd

17 TO_DAYS DATE_SUB DATE 时间比较函数

date_sub()函数格式 DATE_SUB(start_date,INTERVAL expr unit); 表示从start_date时间值中减去指定单位的时间数量比如 DATE_SUB(‘2024-07-12’,INTERVAL 1 DAY) 表示从2024-07-12 减去一天，为 2024-07-11
TO_DAYS TO_DAYS(‘2024-07-06’）给定一个日期date, 返回一个从年份0000-00-00开始计算的天数，比如select TO_DAYS(‘0000-01-01’) as days 返回为 1
DATE() 将时间格式化为 ‘YYYY-MM-dd’格式，比如 select DATE(‘2024-12-06 12:52:21’) as days 返回为 ‘2024-12-06’

查询今日数据

SELECT * FROM testTable a WHERE TO_DAYS(a.ApplyDate) = TO_DAYS(NOW());
SELECT * FROM testTable a WHERE DATE(a.ApplyDate) = CURDATE();

查询昨日数据

SELECT * FROM testTable a WHERE TO_DAYS(NOW()) - TO_DAYS(a.ApplyDate) = 1
SELECT * FROM testTable a WHERE DATE(a.ApplyDate) = DATE_SUB(CURDATE(), INTERVAL 1 DAY);

查询近七日数据

SELECT * FROM testTable a where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(a.ApplyDate);

18 ON DUPLICATE KEY UPDATE 插入数据时存在主键或唯一键冲突的数据时更新

所有与insert 语句中时，存在以下效果，必须要求表存在主键或唯一键，执行插入语句时，如果存在主键或存在唯一的元数据时，将进行更新操作，否则进行插入操作。

并且可以指定跟新哪些字段以下示例只更新 password 字段

insert into user  (password,birthday) values('abcdefg','2024-12-23') ON DUPLICATE KEY UPDATE password=values(password)

mybatis-plus中批量插入的时候使用示例

 	<insert id="insertStatisticsDeviceInfo">
			insert into  iot_statistics_device_info
				(record_date,count,type,extend_info,developer_id)
			values
			<foreach collection="insertDeviceLIst" index="index" separator="," item="item" >
				(#{item.recordDate},#{item.count},#{item.type},#{item.extendInfo},#{item.developerId})
			</foreach>
		ON DUPLICATE KEY UPDATE
		count = values(count),
		extend_info = values(extend_info)
	</insert>