mysql 使用笔记

varchar类型时间范围取值 2020-1、2020-12

select 
IFNULL(SUM(carbon_total),0) 
from finance_summary 
where deleted = 0 and configure_id <> ''  
and enterprise_id = '1' and date_type = 'month' 

-- 使用CONCAt()将只有年月的时间拼接成标准格式 
-- 使用STR_TO_DATE()将字符串时间进行格式化 只有年月的字符串无法进行格式化
-- STR_TO_DATE(CONCAt(date_value,'-1'),'%Y-%m-%d')  也可以用在对 '2021-8' 这种时间排序上面
and STR_TO_DATE(CONCAt(date_value,'-1'),'%Y-%m-%d') 
-- 使用BETWEEN 对date_value进行范围取值
BETWEEN STR_TO_DATE(CONCAt('2021-1','-1'),'%Y-%m-%d') 
and STR_TO_DATE(CONCAt('2021-11','-1'),'%Y-%m-%d') 

-- 使用if判断类型后对时间字符串进行截取
CONCAT(SUBSTRING_INDEX(yaa.date_value,'-',-1),IF(yaa.date_type = 'month','月','')) 'date'

-- 使用if判断类型后进行字符串拼接，并且时间格式化
ORDER BY STR_TO_DATE(CONCAt(yaa.date_value,IF(yaa.date_type = 'year','-1-1','-1')),'%Y-%m-%d') 

按季度分类取值

SELECT CONCAT('第',a.qu,'季度') 'date' , a.DGDQ 'DGDQ' ,b.DZXX 'DZXX',c.SBZZ 'SBZZ' FROM
(select DISTINCT QUARTER(CONCAt(ytii.data_target_instance_year,'-',ytii.data_target_instance_month,'-1')) qu , sum(ytii.data_target_instance_value) 'DGDQ'
from yueqing_target_instance_info ytii  where ytii.deleted = 0 and ytii.data_target_type = 'HYTPFQD' and ytii.data_target_code in ('DGDQ') and ytii.data_target_instance_month <> ''  and ytii.data_target_instance_area_code = '330382000000' and ytii.data_target_instance_year = '2021' group by qu) a LEFT JOIN
(select DISTINCT QUARTER(CONCAt(ytii.data_target_instance_year,'-',ytii.data_target_instance_month,'-1')) qu , sum(ytii.data_target_instance_value) 'DZXX'
from yueqing_target_instance_info ytii  where ytii.deleted = 0 and ytii.data_target_type = 'HYTPFQD' and ytii.data_target_code in ('DZXX') and ytii.data_target_instance_month <> ''  and ytii.data_target_instance_area_code = '330382000000' and ytii.data_target_instance_year = '2021' group by qu) b ON a.qu = b.qu LEFT JOIN
(select DISTINCT QUARTER(CONCAt(ytii.data_target_instance_year,'-',ytii.data_target_instance_month,'-1')) qu , sum(ytii.data_target_instance_value) 'SBZZ'
from yueqing_target_instance_info ytii  where ytii.deleted = 0 and ytii.data_target_type = 'HYTPFQD' and ytii.data_target_code in ('SBZZ') and ytii.data_target_instance_month <> ''  and ytii.data_target_instance_area_code = '330382000000' and ytii.data_target_instance_year = '2021' group by qu) c ON a.qu = c.qu ORDER BY a.qu

-- 本段需求为，在某一年内各个指标按季度分组求和，算出各个指标每个季度的情况
-- 通过ytii.data_target_code in ('DGDQ') 将一年内的每个指标筛选出来 
-- QUARTER(CONCAt(ytii.data_target_instance_year,'-',ytii.data_target_instance_month,'-1')) qu  以及  group by qu 进行季度分组 
-- 最后使用sum函数求和
-- 其中QUARTER(2021-5-6) 会返回一个季度数字 2 
-- 根据情况来因为我的年月日是分开字段存储的 因此在这里使用CONCAt() 函数进行了拼接

效果

SQL for循环 根据查询出来的数据进行循环

-- 创建存储过程之前需判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS init_reportUrl; 
-- 创建存储过程
CREATE PROCEDURE init_reportUrl()
BEGIN
	-- 定义变量
	DECLARE s int DEFAULT 0;
	DECLARE ent_id varchar(255);
	-- 定义游标，并将sql结果集赋值到游标中
	DECLARE report CURSOR FOR SELECT id FROM finance_enterprise_info where deleted = 0 and enterprise_type = 'up' and id <> 1;
	-- 声明当游标遍历完后将标志变量置成某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=-1;
	-- 打开游标
	open report;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		fetch report into ent_id;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		while s<>-1 do
			-- 执行业务逻辑
-- 			update patrolreporthistory set reportUrl = CONCAT('patrolReport.html?monitorId=',substring(report_url,15,1),'&reportId=',report_id) where reportId=report_id;
			select ent_id ;
			-- 将游标中的值再赋值给变量，供下次循环使用
			fetch report into ent_id;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	-- 关闭游标
	close report;
END;


-- 执行存储过程
call init_reportUrl();

数据脱敏处理

UPDATE ent_info e
INNER JOIN (
  SELECT
    ent_id,
    CONCAT(
-- 截取前5位
      LEFT ( ent_name, 5 ),
-- 中间添加原字段长度-2 个*
    REPEAT
        ( '*', CHAR_LENGTH( ent_name )- 2 ),
-- 截取后四位
      RIGHT ( ent_name, 4 )) AS ent_name 
    FROM
    ent_info 
  ) a on a.ent_id=e.ent_id
  set 
  e.ent_name = a.ent_name

指定某几条数据置顶

-- 方法一 
select * from energy_type where deleted = 0 and `year` = 2021 ORDER BY id not in(0024) , update_time desc LIMIT 0,3 

-- 方法二
select * from energy_type where deleted = 0 and `year` = 2021 ORDER BY case when id in (0024,0022,0025) then 0 else id end LIMIT 3,3 

更新数据名称

select CONCAT('RENAME TABLE ', table_schema, '.', table_name, ' TO ', table_schema, '.', REPLACE(table_name, '_copy1', ''), '; ')
FROM information_schema.tables     
WHERE table_schema = '数据库名' AND table_name LIKE '%_copy1';