实战代码（十四）：常用MySQL语句速查笔记

序

本文主要记录下日常略微复杂且比较常用的SQL语句，持续更新……

一、重复数据

查看是否存在重复数据

SELECT `code`, guid, COUNT(1) FROM crosshistory WHERE in_or_out = 1 GROUP BY guid having COUNT(1) > 1

清除重复数据

DELETE FROM crosshistory WHERE `code` in ( SELECT `code` from (SELECT `code` FROM crosshistory WHERE in_or_out = 1 GROUP BY guid having COUNT(1) > 1 ) AS temp )

二、插入/更新

如果数据不存在则执行插入操作，如果数据已经存在则执行更新操作

INSERT INTO visitors(`timestamp`,unique_visitors) VALUES("2021-01-01",10) on DUPLICATE KEY UPDATE `timestamp` = "2021-01-01", unique_visitors = 44;

三、时间格式化

# CONCAT:拼接字符串
# DATE_FORMAT:日期格式化
SELECT value,value1,value2,value3,value4,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') AS createTime,CONCAT(value,value1,DATE_FORMAT(create_time,'%Y%m%d%H%i%s’),value2) AS rowkey FROM table_name

四、字符串处理

// 32号楼a栋__5层__512
// substring_index有分隔字符串的作用，取第一个分隔符前的字符串，结果：32号楼a栋
substring_index(building_code, "__", 1) 
// 取第2个分隔符前的字符串，结果：32号楼a栋__5层
substring_index(building_code, "__", 2)

五、IF

// 如果A等于0，返回yes，否则返回no
if(A == 0, yes, no)

六、去除空格

UPDATE config SET event_name = TRIM(event_name)
# 去除左侧空格
UPDATE config SET event_name = LTRIM(event_name)

七、生成随机数

取 20-100之间的随机数
ROUND(RAND() * 100 + 20)

八、多表联查--左连接

# 左连接
SELECT a.*, IF(b.equip_name IS null, "设备缺失", b.equip_name) as equip_name FROM operate_alarm_video as a
LEFT JOIN basic_equipment AS b
ON a.signal_id = b.signal_id
WHERE a.data_time >= #{startTime} AND a.data_time < #{stopTime}

九、获取表的字段

select COLUMN_NAME from information_schema.COLUMNS where table_name = 'user' and table_schema = 'database_name';