MySQL字符串截取与常用查询 - Yc-Ma Blog

link管理
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
截取SQL

SELECT * FROM HORGShareHold_Hold_Split01  WHERE fromName='null'
SELECT substring(`from`, 4, length) FROM HORGShareHold_Hold_Split01  WHERE fromName='null'
SELECT SUBSTRING_INDEX(`from`, '_', 1) FROM HORGShareHold_Hold_Split01  WHERE fromName='null' LIMIT 1
SELECT SUBSTRING(SUBSTRING_INDEX(`from`, '_', 1),6) FROM HORGShareHold_Hold_Split01  WHERE fromName='null' LIMIT 1
UPDATE HORGShareHold_Hold_Split01 SET fromName=SUBSTRING(SUBSTRING_INDEX(`from`, '_', 1),6) WHERE fromName='null'
UPDATE HORGShareHold_Hold_Split01 SET toName=SUBSTRING(SUBSTRING_INDEX(`to`, '_', 1),6) WHERE toName='null'
将数据从一个表转到另一个表
INSERT IGNORE MAIN_DIC (SELECT * FROM MSTR_PERSON_DIC)
2015-02-03 00:00:00 -》 20150203000000
SELECT hcode,name,type,CONVERT(DATE_FORMAT(release_date,'%Y%m%d%H%i%S'),UNSIGNED INTEGER) AS release_date,CONVERT(DATE_FORMAT(start_time,'%Y%m%d%H%i%S'),UNSIGNED INTEGER) AS start_time,CONVERT(DATE_FORMAT(end_time,'%Y%m%d%H%i%S'),UNSIGNED INTEGER) AS end_time,data_source FROM MSTR_EVENT LIMIT 10
从一个表拿数据更新到另外一个表
UPDATE MSTR_EVENT INNER JOIN (SELECT nature,hcode FROM MSTR_EVENT_TEST) source ON MSTR_EVENT.hcode=source.hcode SET MSTR_EVENT.nature=source.nature
分组排重统计
name1 213
name1 352
name2 123
name2 123
name1 2
name2 1
SELECT guaranteed_orig,COUNT(DISTINCT guaranteed_id) AS count FROM regu GROUP BY guaranteed_orig
查询字段并合并成一个字段返回【一行多字段聚合】
SELECT CONCAT_WS(',',skillname) FROM TABLE WHERE people_id=26790936
查询字段并合并成一个字段返回【多行聚合】
SELECT GROUP_CONCAT(skillname) FROM TABLE WHERE id=26790936
指定索引查询
EXPLAIN SELECT * FROM MSTR_ORG_PRE FORCE INDEX (ORG_INFO_src) WHERE src = 'qixinbao' AND puid>0 ORDER BY puid ASC LIMIT 200
获取24H之前的系统时间
SELECT * FROM MSTR_EVENT WHERE hupdatetime>=DATE_SUB(NOW(),INTERVAL 1 DAY) LIMIT 10
-- parent_code关联自己的HCODE
-- SELECT parent_code AS `from`,name AS `to` FROM MSTR_INDUSTRY WHERE parent_code!='' AND parent_code IS NOT NULL
SELECT CONCAT('HINDUSTRY',MD5(a.name)) AS `from`,CONCAT('HINDUSTRY',MD5(b.name)) AS `to` FROM MSTR_INDUSTRY a RIGHT JOIN MSTR_INDUSTRY b ON a.hcode=b.parent_code WHERE b.parent_code!='' AND b.parent_code IS NOT NULL
UPDATE ONGDB_TASK_CHECK_POINT_LOCK updateLock INNER JOIN (SELECT task_lock,hcode FROM ONGDB_TASK_CHECK_POINT_LOCK selectLock WHERE task_lock=0 AND hcode='HGRAPHTASK(HORGGuaranteeV001)-[担保]->(HORGGuaranteeV001)') selectLock ON updateLock.hcode=selectLock.hcode SET updateLock.task_lock=1
UPDATE fin_secu_sam_product_calc calc INNER JOIN std_org_base_stock_merge mt ON calc.company_id=mt.companyCode SET calc.company_name=mt.name
查询SQL进程并KILL
SHOW PROCESSLIST
KILL 1159363
更新默认值
ALTER TABLE ESG_SHAREHOLD_PLEDGOR_ANALYSIS ALTER src SET DEFAULT 'APG';
增加自动更新时间字段
ALTER TABLE xiniudata.city ADD hupdatetime DATETIME DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL COMMENT '自动更新时间';
增加字段BTREE索引
CREATE INDEX hupdatetime_IDX USING BTREE ON xiniudata.city (hupdatetime);
指定FROM和TO字段分组导出JSON-DETAIL数据
SELECT JSON_ARRAYAGG(JSON_OBJECT('report_date',CONVERT(DATE_FORMAT(report_date,'%Y%m%d%H%i%S'),UNSIGNED INTEGER),'income',income,'profit',profit,'product_income',product_income,'product_income_ratio',product_income_ratio,'product_profit',product_profit,'product_profit_ratio',product_profit_ratio,'uniqueField','id','uniqueFieldID',id,'jsDataApi','chinascope_supply_chain.fin_secu_sam_product_calc')) AS detail,product_hcode,company_hcode FROM fin_secu_sam_product_calc GROUP BY product_hcode,company_hcode
条件分支查询
SELECT
    ITCODE,NAME,
    (CASE WHEN SRC='caihui1' THEN 'TQ_COMP_INFO'
        WHEN SRC='caihui2' THEN 'TCR0001'
				WHEN SRC='caihui2api' THEN 'api'
        ELSE 'unknown' END) AS SRC
    CAIHUI_ORG_LIST
SELECT REPLACE(PARENT_PCODE,SRC,'') AS ITCODE,NAME,SRC FROM CAIHUI_ORG_LIST LIMIT 1000
UPDATE CAIHUI_ORG_LIST SET ITCODE=REPLACE(PARENT_PCODE,SRC,'')
多字段分组统计
SELECT COUNT(*) FROM CH_COMPANY_LIST GROUP BY ITCODE,NAME,SRC
INSERT INTO MSTR_ORG_PRE (create_by) VALUES ('mayc01') ON DUPLICATE KEY UPDATE create_by=?;
INSERT IGNORE INTO PRECESS_TABLE_STATUS (`ID`,`TABLE`,`DATABASE`,`TYPE`,LAST_UPDATE,CREATE_BY,UPDATE_BY) VALUES (?,?,?,?,?,'mayc01','mayc01');
查询数据写入新表
INSERT INTO ONGDB_SCHEMA_MAPPING (data_schema) (SELECT hcode AS data_schema FROM ONGDB_TASK_CHECK_POINT)
UPDATE ONGDB_SCHEMA_MAPPING SET update_by='mayc01',create_by='mayc01'
从分组结果中继续分组统计
SELECT A.parent_pcode AS parent_pcode,COUNT(*) AS count FROM (SELECT parent_pcode,credit_code,COUNT(*) AS count FROM MSTR_ORG_PRE_1 GROUP BY parent_pcode,credit_code) A WHERE count>1 GROUP BY A.parent_pcode LIMIT 10
分组排序获取TOPN并只返回某研报的数据
SELECT * FROM (select a.* from XXXY_YANBAO_CONCEPT a where 20 > (select count(*) from XXXY_YANBAO_CONCEPT where yanbao_hcode = a.yanbao_hcode and weight > a.weight ) order by a.yanbao_hcode,a.weight) tp WHERE tp.yanbao_hcode='HDOC05129351cfccb98776699a2411ea43eb'
# 分组获取TOP20
SELECT a.* FROM XXXY_YANBAO_CONCEPT_CALC_NO_WEIGHT a WHERE 20 > (SELECT count(*) FROM XXXY_YANBAO_CONCEPT_CALC_NO_WEIGHT WHERE ybId = a.ybId AND tfidf>a.tfidf) ORDER BY a.ybId,a.tfidf DESC
# 分组获取TOP20【不加排序】
SELECT a.ybId AS ybId,a.kwId AS kwId,a.tfidf AS tfidf FROM XXXY_YANBAO_CONCEPT_CALC_NO_WEIGHT a WHERE 20 > (SELECT count(*) FROM XXXY_YANBAO_CONCEPT_CALC_NO_WEIGHT WHERE ybId = a.ybId AND tfidf>a.tfidf)
// 按name分组取最大的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
// 按name分组取最小的两个(N个)val
select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
SQL内连接
SELECT a.ybId AS ybId,a.kwId AS kwId,a.tfidf AS weight FROM XXXY_YANBAO_CONCEPT_CALC a INNER JOIN XXXY_YANBAO_CONCEPT_CALC_NO_WEIGHT b ON a.ybId=b.ybId AND a.kwId=b.kwId
SQL求差集
SELECT b.ybId AS ybId,b.kwId AS kwId FROM XXXY_YANBAO_CONCEPT_CALC_NO_WEIGHT b WHERE b.ybId NOT IN (SELECT ybId FROM XXXY_YANBAO_CONCEPT_CALC a WHERE a.ybId=b.ybId) AND b.kwId NOT IN (SELECT kwId FROM XXXY_YANBAO_CONCEPT_CALC a WHERE a.kwId=b.kwId)
MySQL中文匹配
SELECT v,(v REGEXP '[吖-座]')
   SELECT 'sql server是微软开发的数据库管理系统'  AS v UNION ALL
   SELECT 'C-123456789' AS v UNION ALL
   SELECT 'MySQL是一个开源的数据库管理系统' AS v UNION ALL
   SELECT 'sdalfkj'   UNION ALL
   SELECT '吖' AS v UNION ALL
   SELECT '座' AS v
WHERE v REGEXP '[吖-座]'
连接多个字符串
SELECT CONCAT_WS(',','First name','Last Name','SADSAD' )
正则替换字符串中得数字
SELECT REGEXP_REPLACE('01234abcde56789','[0-9]','')
SELECT REGEXP_REPLACE('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金','[^a-zA-Z]','')
SELECT REGEXP_REPLACE('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金','[\u4e00-\u9fa5]','')
存储过程提取数据
-- 提取数字
SELECT REGEX_EXTRACT_STR('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金',0,NULL);
-- 提取字母
SELECT REGEX_EXTRACT_STR('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金',1,NULL);
-- 提取数字+字母
SELECT REGEX_EXTRACT_STR('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金',2,NULL);
-- 提取汉字【不支持去除特殊字符】
SELECT REGEX_EXTRACT_STR('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金',3,NULL);
-- 提取数字+字母+汉字【支持去除特殊字符】
SELECT REGEX_EXTRACT_STR('宁波拾贝投资管理合伙asdsada企21312321业(有限合伙)-拾贝回报投资基金',4,'()-');
DROP FUNCTION IF EXISTS `REGEX_EXTRACT_STR`;
CREATE FUNCTION `REGEX_EXTRACT_STR`(Aimstring VARCHAR(1024)CHARSET utf8,flag INT,symbol VARCHAR(128)CHARSET utf8) RETURNS VARCHAR(1024) CHARSET utf8
BEGIN
	DECLARE len INT DEFAULT 0;
	DECLARE Tmp VARCHAR(1024) DEFAULT '';
	SET len=CHAR_LENGTH(Aimstring);
	IF flag = 0
		WHILE len > 0 DO
		-- 使用MID对字符串从后往前截取，对每个元素进行正则匹配
		-- REGEXP'[0-9]' 匹配数字
		IF MID(Aimstring ,len,1)REGEXP'[0-9]' THEN
		SET Tmp=CONCAT(Tmp,MID(Aimstring ,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=1
		WHILE len > 0 DO
		-- 使用MID对字符串从后往前截取，对每个元素进行正则匹配
		-- REGEXP '[a-zA-Z]') 匹配字母
		IF (MID(Aimstring,len,1)REGEXP '[a-zA-Z]')
		SET Tmp=CONCAT(Tmp,MID(Aimstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=2
		WHILE len > 0 DO
		-- 使用MID对字符串从后往前截取，对每个元素进行正则匹配
		-- 匹配数字+字母
		IF ( (MID(Aimstring,len,1)REGEXP'[0-9]')
		OR (MID(Aimstring,len,1)REGEXP '[a-zA-Z]') )
		SET Tmp=CONCAT(Tmp,MID(Aimstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=3
		WHILE len > 0 DO
		-- 使用MID对字符串从后往前截取，对每个元素进行正则匹配
		-- [u0391-uFFE5] :中文之外的字符，正则匹配不在这范围内的则为中文
		IF NOT (MID(Aimstring,len,1)REGEXP '^[u0391-uFFE5]')
		SET Tmp=CONCAT(Tmp,MID(Aimstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
	ELSEIF flag=4
		WHILE len > 0 DO
		-- 使用MID对字符串从后往前截取，对每个元素进行正则匹配
		-- 提取：数字+字母+中文【排除特殊符号】
		IF (( (MID(Aimstring,len,1)REGEXP'[0-9]')
		OR (MID(Aimstring,len,1)REGEXP '[a-zA-Z]')
		OR (NOT (MID(Aimstring,len,1)REGEXP '^[u0391-uFFE5]'))
		) AND (symbol NOT LIKE CONCAT_WS('','%',MID(Aimstring,len,1),'%')) )
		SET Tmp=CONCAT(Tmp,MID(Aimstring,len,1));
		END IF;
		SET len = len - 1;
		END WHILE;
		SET Tmp = 'Error: The second paramter should be in (0,1,2,3,4)';
		RETURN Tmp;
	END IF;
	RETURN REVERSE(Tmp);
INSERT INTO ONGDB_TASK_CHECK_POINT_CURSOR (hcode,create_by,update_by) (SELECT hcode,'mayc01' AS create_by,'mayc01' AS update_by FROM ONGDB_TASK_CHECK_POINT);
INSERT IGNORE INTO ONGDB_TASK_CHECK_POINT_CURSOR (hcode,create_by,update_by) (SELECT hcode,'mayc01' AS create_by,'mayc01' AS update_by FROM ONGDB_TASK_CHECK_POINT);
修改更新时间字段【设置默认值】
ALTER TABLE crawler_fund_data.TS_FUND_EMPLOYEES_HIS MODIFY COLUMN UPDATE_TIME timestamp DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP NULL;
正则提取数字、英文、汉字
SELECT REGEXP_REPLACE('{}[]Hellosad #$#$#$#@%%^&&**)-=W123orld!中文号码號碼``,;.[]', '[^a-zA-Z0-9\\u4e00-\\u9fa5]', '')
MySQL全文检索
# 相似检索
SELECT ID,SRC_ID,SUMMARY FROM exreport.TS_REPORT_INFO WHERE MATCH(SUMMARY) AGAINST ('ChatGPT')
# 词包含检索
SELECT ID,SRC_ID,SUMMARY FROM exreport.TS_REPORT_INFO WHERE MATCH(SUMMARY) AGAINST ('+chatgpt' IN BOOLEAN MODE)