统计语句

1、mysql内置函数加解密aes

--mysql内置函数加解密;
AES_ENCRYPT(plainText,key)：返回用密钥key对明文利用高级加密算法加密后的结果，结果是一个二进制字符串，以BLOB类型存储;
AES_DECRYPT(cipherText,key)：针对上一个函数的解密算法;
EXPLAIN
select HEX(AES_ENCRYPT('19802126263', 'abcdefghijkefg12')) FROM DUAL,
SELECT AES_DECRYPT(UNHEX('076c48033f7248d34dde231b1b1446ac'),'abcdefghijkefg12') from dual;
select HEX(AES_ENCRYPT('中文账号', 'abcdefghijkefg12')),
SELECT AES_DECRYPT(UNHEX('605EFE069F133393B3D3E11FDDC44644'),'abcdefghijkefg12') from system_base_user;

2、

INNER JOIN t_cod_canton canton ON org.areaCode = canton.`code`
LEFT JOIN t_cod_canton canton_p ON canton_p.`code` = canton.parentCode
WHERE
FIND_IN_SET('320000', canton.fullCode)

GROUP BY
operate.operateType,
operate.orgId

CONCAT(org_p.orgName,'/', org.orgName) AS orgName,

AND dic.id = SUBSTRING(
e.callingId,
POSITION(',' IN e.callingId) + 1
)

CASE
WHEN (ent.callingId IS NULL OR ent.callingId = '') THEN
'02d5dade45084364af7400be93100019,02d5dade45084364af7400be93100104'
ELSE
ent.callingId
END AS callingId

substr(callingId,(POSITION(',' IN callingId)+1))

canton.`level` IN ('1', '2');

AND YEAR (complain.createTime) = YEAR (NOW())

and DATE_FORMAT(complain.createTime,'%Y-%m') BETWEEN #{registerStartTimeStr} AND #{registerEndTimeStr}

AND DATE_FORMAT(complain.createTime,'%Y%m') = DATE_FORMAT( CURDATE() ,'%Y%m')
AND YEARWEEK(date_format(complain.createTime,'%Y-%m-%d')) = YEARWEEK(now())

3、

-- 更新表字段值从查询结果中取值，需要多套一层
UPDATE xxx_complain_manage AS a
SET a.orgId = (
SELECT
b.manageOrgId
FROM
(
SELECT
orguser.orgId AS manageOrgId,
manage.id AS manageId
FROM
xxx_complain_manage manage
LEFT JOIN system_org_user orguser ON manage.createBy = orguser.userId
) b
WHERE
b.manageId = a.id
);

4、

-- 显示索引
SELECT * FROM xxx_operate;
SHOW INDEX FROM xxx_operate;
SHOW KEYS FROM xxx_operate;
SHOW INDEX FROM t_cod_canton;
SHOW INDEX FROM system_org;
SHOW INDEX FROM xxx_complain_manage;
SHOW INDEX FROM xxx_satisfaction_evaluate;
-- 添加外键
ALTER TABLE xxx_operate ADD CONSTRAINT FK_operate_complainId FOREIGN KEY (complainId) references xxx_complain(id);
ALTER TABLE xxx_complain_manage ADD CONSTRAINT fk_manage_complainId FOREIGN key (complainId) REFERENCES xxx_complain(id);
ALTER TABLE xxx_satisfaction_evaluate add CONSTRAINT fk_sat_complainId foreign key (complainId) REFERENCES xxx_complain(id);
-- 添加索引
ALTER TABLE xxx_operate ADD INDEX complainIdIndex(complainId);
ALTER TABLE xxx_complain_manage add INDEX complainIdIndex(complainId);
ALTER TABLE xxx_satisfaction_evaluate add INDEX complainIdIndex(complainId);
-- 查看索引
SHOW INDEX FROM xxx_operate;
SHOW CREATE TABLE xxx_operate;
SHOW KEYS FROM xxx_operate;
-- 删除索引
DROP INDEX complainIdIndex ON xxx_operate;
-- 删除外键
ALTER TABLE xxx_operate DROP FOREIGN key FK_operate_complainId;
-- 索引测试
EXPLAIN SELECT * FROM xxx_operate WHERE complainId = '22';
EXPLAIN SELECT * FROM xxx_operate ORDER BY id ;
EXPLAIN SELECT compalinId,* FROM xxx_operate ORDER BY complainId;
-- 联合索引
ALTER TABLE xxx_operate ADD index unionIndex (complainId, operateType);
EXPLAIN SELECT * FROM xxx_operate WHERE complainId='11' AND operateType='BL';
EXPLAIN SELECT * FROM xxx_operate WHERE complainId='11' OR operateType='BL';
EXPLAIN SELECT * FROM xxx_operate WHERE complainId='11';

5、重复数据

SELECT complainId,COUNT(1) FROM xf_complain_manage GROUP BY complainId HAVING COUNT(1)>1;

6、

-- 更新字段脚本
ALTER TABLE xf_enterprise ADD isQiTa CHAR(1) Default '0';

SELECT
SUM(satisfact.satisfactionNum) AS satisfactionNum
FROM
(
SELECT
CASE
WHEN (
resultSatisfaction = '1'
OR resultSatisfaction = '2'
) THEN
1
ELSE
0
END AS satisfactionNum
FROM
xf_return_visit rv
) satisfact;

SELECT
CASE WHEN resultSatisfaction='1' THEN '满意'
WHEN resultSatisfaction='2' THEN '基本满意'
WHEN resultSatisfaction='0' THEN '不满意'
WHEN resultSatisfaction='3' THEN '其他' END AS dictionaryName,
count(1) as countNum
FROM xf_return_visit rv
GROUP BY resultSatisfaction;

7 查看事务隔离级别

SELECT @@tx_isolation;

8