【数据库】常见操作汇总(统计、数据清理、正则查询持续更新...)

一、统计

1. 按日期统计表记录

SELECT
	date_format( a.created_time, "%Y-%m-%d" ) AS time,
	count( DISTINCT a.id ) 
FROM
	${tablename} a
GROUP BY
	date_format( a.created_time, "%Y-%m-%d" )

2. 按日期统计表记录，时间范围

oracle时间格式化to_date，mysql、dm时间格式化date_format

SELECT
        date_format(a.created_time, '%Y-%m-%d') AS time,
        count( DISTINCT a.id )
FROM
        global_log_request a
where
        a.created_time>'2022-01-01'
GROUP BY
        date_format(a.created_time, '%Y-%m-%d')

二、数据清理[更新]

1. 联表更新，根据部门全称、简称更新用户部门id

UPDATE
        auth_user A
SET
        DEPT_ID=
        (
                SELECT
                        max(ID)
                FROM
                        auth_dept B
                WHERE
                        (
                                B.DEPT_NAME LIKE CONCAT('%', A.DEPT_NAME, '%')
                             OR B.DEPT_NAME LIKE CONCAT('%', A.SHORT_NAME, '%')
                        )
                ORDER BY
                        B.ID
        )

2. 统一张表中某一字符串类型字段的格式

UPDATE ${tablename} t
SET t.NAME = substr( NAME, 1, instr( NAME, '-', 1, 3 ) - 1 ) 
WHERE
	substr( NAME, 1, instr( NAME, '-', 1, 3 ) - 1 ) IS NOT NULL;

3. 数据去重

delete A where id not in(select min(id) from A group by ${去重字段})

4. 两表建立关联

建立业务日志表和登录表的关联，用于记录业务操作属于哪次登录

UPDATE yw_log
SET login_id = ( SELECT MAX( id ) FROM auth_user_login_log WHERE DAY ( created_time ) = DAY ( yw_log.created_time ) )

5. 截取上级序号.etc 3.3 上级序号 3；3.3.3 上级序号 3.3

①上级序号

-- 验证
select SUBSTR(view_order_no,1,(LENGTH(view_order_no)-LOCATE('.', REVERSE(view_order_no)))) pid 
from table_A
where view_order_no!=SUBSTR(view_order_no,1,(LENGTH(view_order_no)-LOCATE('.', REVERSE(view_order_no)))) and  tenant_id=1703682858563108866;
-- 更新
update table_A a 
set pid=SUBSTR(view_order_no,1,(LENGTH(view_order_no)-LOCATE('.', REVERSE(view_order_no)))) 
where view_order_no!=SUBSTR(view_order_no,1,(LENGTH(view_order_no)-LOCATE('.', REVERSE(view_order_no)))) and  tenant_id=1703682858563108866;

②无上级序号置为0

update table_A a 
set pid=0 
where view_order_no=SUBSTR(view_order_no,1,(LENGTH(view_order_no)-LOCATE('.', REVERSE(view_order_no)))) and  tenant_id=1703682858563108866;

③根据上级序号设置pid

-- 需要先copy table_A 
update table_A a 
set a.pid=(select b.id from table_A_copy b where b.view_order_no=a.pid) 
where tenant_id=1703682858563108866 and pid!=0;

三、正则查询

1. 正则替换处理:王老6【00】（22）\【00】（22）王老6\【00】王老（22）6

select regexp_replace(name,"\【[0-9]+\】+|\（[0-9]+\）+|[0-9]+|\s+",'') 
FROM #{tablename};

分析：特殊字符通过’‘转义，数字通过字符集匹配[]，1…*匹配用’+‘，空格匹配’\s’

2. 正则查询:匹配中文

-- ^在方括号表达式中使用时，表示不接受该方括号表达式中的字符集合
SELECT * from #{tablename}
WHERE regexp_like(name,'^[\x00-\xff]');

3. 否定正则查询

SELECT * from #{tablename}
WHERE regexp_like(name,'^[\x00-\xff]')!=1;

4. 删除以5000开头数字串

DELETE FROM #{tablename} 
WHERE regexp_like(id,'^5000[0-9]+');