mysql随笔记录（个人喜好，因为记不住，哈哈哈）

Mysql字符串截取及获取指定字符串中的数据

substring_index(input,split,index)：input为要截取的字符，split为分隔符，Index为要截取第index个分隔符左（index为正）或右(index为负)的字符串。

-- 1-获取截取符右边的字符

select substring_index('www.baidu.com','www.',-1);

====>baidu.com

-- 2-再获取截取符左边的字符

select substring_index('www.baidu.com','.com',1);

=====>www.baidu

Mysql查询某字段值重复的数据

查询user表中，phone字段值重复的数据及重复次数

SELECT phone,COUNT(*) AS COUNT FROM USER GROUP BY phone HAVING COUNT>1;

查询MySQL中某一个字段出现2次以上

SELECT * FROM 表名 WHERE 字段 IN(SELECT 字段 FROM 表 GROUP BY 字段 HAVING COUNT(字段) > 1)

mysql  JSON查询（个人记录）

{"key":"wzry_skin_num","value":"13131"}
-- 单选和多选
SELECT id,category_name,game_json FROM tb_account_info WHERE (JSON_CONTAINS(game_json, JSON_OBJECT('key', 'wzry_rare_skin')) AND JSON_CONTAINS(game_json, JSON_OBJECT('value', '幽恒')) AND 
JSON_CONTAINS(game_json, JSON_OBJECT('value', '神威'))) 
AND (JSON_CONTAINS(game_json, JSON_OBJECT('key', 'wzry_dan')) AND JSON_CONTAINS(game_json, JSON_OBJECT('value', '至尊星耀')))
AND (JSON_CONTAINS(game_json, JSON_OBJECT('key', 'wzry_hero_num')) AND JSON_CONTAINS(game_json, JSON_OBJECT('value', '3123')))
-- 查询值
SELECT JSON_UNQUOTE(JSON_EXTRACT(game_json->'$[3]','$.value'))  FROM tb_account_info WHERE category_id = 2
-- 区间搜索 
SELECT id,category_name,game_json FROM tb_account_info WHERE category_id = 2 AND  1 <= JSON_UNQUOTE(JSON_EXTRACT(game_json->'$[3]','$.value'))  AND JSON_UNQUOTE(JSON_EXTRACT(game_json->'$[3]','$.value')) <=3
-- 单查
SELECT JSON_UNQUOTE(JSON_SEARCH(game_json, 'one', 'wzry_skin_num')) AS a FROM tb_account_info WHERE category_id = 2 LIMIT 1
SELECT id,category_name,game_json FROM tb_account_info WHERE category_id = 2 AND  1 <= JSON_EXTRACT(game_json->'$[3]','$.value')  AND JSON_EXTRACT(game_json->'$[3]','$.value') <=3
-- 替换查询值
SELECT JSON_UNQUOTE(JSON_EXTRACT(game_json,JSON_UNQUOTE(REPLACE(JSON_SEARCH(game_json,'one','wzry_skin_num',NULL,'$[*].key'),'key','value')))) FROM tb_account_info WHERE category_id = 2

SELECT id,category_name,game_json FROM tb_account_info WHERE category_id = 2 AND (JSON_CONTAINS(game_json, JSON_OBJECT('key', 'wzry_rare_skin')) AND JSON_CONTAINS(game_json, JSON_OBJECT('value', '幽恒'))) 
AND JSON_UNQUOTE(JSON_EXTRACT(game_json,JSON_UNQUOTE(REPLACE(JSON_SEARCH(game_json,'one','wzry_skin_num',NULL,'$[*].key'),'key','value'))))  >= 1321
AND JSON_UNQUOTE(JSON_EXTRACT(game_json,JSON_UNQUOTE(REPLACE(JSON_SEARCH(game_json,'one','wzry_skin_num',NULL,'$[*].key'),'key','value'))))  <= 13210

mysql账号赋权远程连接：

grant all privileges on *.* to root@'%' identified by '123456' with grant option;

mysql字符串拆分： SUBSTRING_INDEX（str, delim, count）

• str 　　　　需要拆分的字符串
• delim 　　  分隔符，通过某字符进行拆分
• count 　　  当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第                     n个分隔符之后的所有字符。

-- mysql区间查询_MySQL区间分组查询

SELECT ELT( INTERVAL ( real_price, 0, 200, 500, 1000,3000,5000,10000 ),"0-200", "200-500", "500-1000", "1000-3000","3000-5000","5000-9999","10000+" ) AS price, COUNT( id ) AS '订单数量' 
FROM tb_order WHERE (trade_status = 2 OR trade_status = 3 OR trade_status = 8)
GROUP BY ELT( INTERVAL ( real_price, 0, 200, 500, 1000,3000,5000,10000 ), "0-200", "200-500", "500-1000", "1000-3000","3000-5000","5000-9999","10000+" ) 

-- 按天查询

SELECT DATE_FORMAT(create_time,'%Y%m%d') days,COUNT(id) AS '个数' FROM `tb_leave_massage` WHERE massage_type = 2 AND create_time>= '2022-08-01 0:00:00' AND create_time<= '2022-10-31 23:59:59'  GROUP BY days;