统计语句
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
POSITION('320100' IN canton.fullCode)>0 AND (canton.`code` = '320100' OR (left(canton.`code`, 4) = LEFT('320100',4) ))
9
//多条插入 int mulAddIds(List list); // mybatis的xml语句 <!--一次插入多条记录 将所有信息插入ids表里面,传入参数为list,通过<foreach>来遍历list--> <insert id="mulAddIds" parameterType="java.util.ArrayList"> insert into ids (id) VALUES <foreach collection="list" item="id" separator=","> (#{id}) </foreach> </insert>
10
打听下这个变量名谁起的