Mysql常用操作

时间相关

常用的格式化

DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%s")

需要格式自行连接了解 https://www.w3school.com.cn/sql/func_date_format.asp

获取各种时间

select NOW();				#获取当前日期时间
select current_timestamp();	#获取当前日期时间
select curdate();			#返回当前日期
select curtime();			#返回当前时间
select now() + 0 ;			#返回当前毫秒数
select year(now());			#返回当前年份

时间相加减

时间相加减相关：DATE_ADD(date,INTERVAL expr type)

select date_sub(now(), interval 2 hour);	#当前时间  加 2小时
select date_sub(now(), interval -1 hour);	#当前时间 减 一小时

时间类型

类型				说明
MICROSECOND		微秒
SECOND			秒
MINUTE			分
HOUR			时
DAY				天
WEEK			星期
MONTH			月
QUARTER			3个月
YEAR			1年
SECOND_MICROSECOND	秒小一级单位 2018-09-05 14:47:26 2018-09-05 14:47:25.900000
MINUTE_MICROSECOND	秒小一级单位 2018-09-05 14:47:26 2018-09-05 14:47:25.900000
MINUTE_SECOND		秒
HOUR_MICROSECOND	秒小一级单位 2018-09-05 14:47:26 2018-09-05 14:47:25.900000
HOUR_SECOND		秒
HOUR_MINUTE		分
DAY_MICROSECOND		秒小一级单位 2018-09-05 14:47:26 2018-09-05 14:47:25.900000
DAY_SECOND		秒
DAY_MINUTE		分
DAY_HOUR		时
YEAR_MONTH		月

DATEDIFF() 函数返回两个日期之间的天数

DATEDIFF(date1,date2)

TIMESTAMPDIFF 语法： TIMESTAMPDIFF(type,date1,date2) 返回日期或日期时间表达式date1 和date2 之间的整数差。其结果的单位由type 参数给出同上

TO_DAYS相关

TO_DAYS(date)给出一个日期 date，返回一个天数(从 0 年开始的天数)

#今天的数据
select * from orderform where  TO_DAYS(NOW()) = TO_DAYS(createdtime)

#昨天数据
select * from orderform where  TO_DAYS(NOW()) - TO_DAYS(createdtime) = 1

#前天
select * from orderform where  TO_DAYS(NOW()) - TO_DAYS(createdtime) < 2 and TO_DAYS(NOW()) - TO_DAYS(createdtime) > 1

字段合并

CONCAT(参数1,参数2)

SELECT CONCAT(id,name) as id_name FROM user

参数类型转换

convert(参数1,需要转换的类型,必须是数据库所持有的字段)

可转换的类型

• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED
• 无符号整数 : UNSIGNED

id为varchar时，查询出来转换成int：select name,convert(id,int) from user

以","拆分的sql语句

SELECT 
	SUBSTRING_INDEX(SUBSTRING_INDEX(s.user_id,',',b.help_topic_id+1),',',-1) AS user_id,user_state,starttime,endtime,leaveaddress 
		FROM 
			split_userid s 
		JOIN 
			mysql.help_topic b
		ON
		b.help_topic_id < (length(s.user_id) - length(replace(s.user_id,',',''))+1)

去重 distinct，统计 + 去重

# 单向去重，去掉重复的名字
SELECT distinct name,age FROM user
# 先统计再去重
SELECT count(distinct createdtime) FROM user

给查询出来的数据添加序号

select (@i:=@i+1) as rank ,user_name from user , (select @i:=0) as any_name ORDER BY list_number

范围查询 between 相当于 >= <=

select * from tf_orderbasic t where t.third_create_time between '2021-03-07 00:00:00' and '2021-03-08 00:00:00';

SQL判断 CASE、IF等

CASE

SELECT
    t.NAME,
    (
        CASE t.sex
        WHEN 1 THEN '男' 
        WHEN 2 THEN '女'
        ELSE '未知'
        END
    ) 性别
FROM
    t_customer t

IF

IF()函数 IF(expr1,expr2,expr3):适用于对字段的简单判断，成立与不成立,不用如CASE 那般繁琐。

SELECT u.name,IF(u.体重<80,"正常","肥胖") FROM user

select 
  count(*) AS submit_total,
  count(IF(submit_type = '政务信息',true ,null)) as chief_count ,
  count(IF(submit_type = '专报信息',true,null)) as specifically_count
from 
	information 
where 
	dept = '运维部'

字符串的集合操作ELT()

例子：如果type = 1 则返回早餐，type = 2 则返回午餐。。。还可以判断更多

SELECT uid,ELT(convert(type,CHAR(12)),'早餐','午餐') as 类型 FROM orderform

多次统计判断

select id
,sum(case when type in (1,2) then [count] else 0 end) as sum1
,sum(case when type in (3) then [count] else 0 end) as sum2
,sum(case when type in (4,5) then [count] else 0 end) as sum3
from 表名
group by id