统计语句

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