sql日记

sql积累：

给创建好的表，添加唯一约束。

ALTER TABLE `t_user` ADD unique(`cloumname`);

explain查看sql执行计划

EXPLAIN SELECT sql_no_cache count(*) FROM user_info WHERE unit_id in(32012000,32012000,32009000,32006000,32001213);

查看表中的索引

show index from table_name;

给已有的表添加索引：

ALTER TABLE `table_name` ADD INDEX index_name(column)
ALTER TABLE user_info ADD INDEX ind_unit_id(unit_id);

添加前缀索引【字段内容(值)过长】–只能用于字符串

alter table user_info add key (unit_id(5));

子查询优化性能：性能相对关联查询较低

EXPLAIN SELECT count(1) FROM user_info where unit_id in(select j.ID from unit j where j.JG_ID in(1,3,7,86));

join关联查询【性能好一点】

SELECT count(1) FROM user_info u INNER JOIN jsxf_jc_dw dw ON u.unit_id = dw.id WHERE dw.JG_ID in(1,3,7,86);

关联表查询,having进行筛选

SELECT count(1) FROM zddwgl WHERE ZDZJ=1 and ZDDWGLZJ in(
	SELECT ID FROM unit dw WHERE dw.JG_ID in(1,3,7,88) 
) GROUP BY ZDZT HAVING ZDZT = 1;

删除索引

DROP INDEX ind_unit_id ON table_name;

关闭查询缓存

SELECT SQL_NO_CACHE count(1) FROM user_info u INNER JOIN unit dw ON u.unit_id = dw.id;

递归函数

# 通过一个id，查询当前对象以及递归查询所有的父类信息
with RECURSIVE allparentjg as
            (
                select * from t_jigou
                where ID = #{jgdwid}
                union all
                select jg.* from t_jigou jg inner join allparentjg c
                on c.SJ_ID= jg.ID
            )
    select * from allparentjg
#  通过一个id，查询当前对象以及递归所有的子类对象信息
WITH RECURSIVE alljgchild as
    		    (
                    SELECT * FROM t_jigou
                    WHERE ID = 1
                    UNION ALL
                        SELECT jg.*
                        FROM t_jigou jg INNER JOIN alljgchild ON jg.SJ_ID = alljgchild.ID
                )
 SELECT ID FROM alljgchild   

eg:
#  通过一个id，查询当前对象以及递归所有的子类对象信息
WITH RECURSIVE alljgchild as
    		    (
                    SELECT * FROM kbms_k_type
                    WHERE parent_id = 'ELNkbms498147578035044352'
                    UNION ALL
                        SELECT jg.*
                        FROM kbms_k_type jg INNER JOIN alljgchild ON jg.parent_id = alljgchild.ID
                )
 SELECT * FROM alljgchild 
 

日期条件

select * from employee WHERE birth BETWEEN '2020-09-25' AND '2020-09-30';
select * from employee WHERE birth >= '2020-09-25' AND birth <= '2020-09-30';

日期条件格式化

select
        jlzj
        from
        jsxf_xj_jcjl
        where
        DATE_FORMAT(CSSJ,'%Y-%m-%d %H')  =  DATE_FORMAT(now(),'%Y-%m-%d %H')
        and SCBZ = 1
        and JCHBZ = 0
        and JLZT = 2;

mybatis中模糊查询问题

问题：LIKE CONCAT('%','测','%') ，单引号'%'，数据查询不全或查不到。建议使用下面的双引号格式"%"

<select id="queryTzggYwzxByDw" parameterType="com.jiurun.vo.notices.JsxfTzggYwzxVo" resultType="com.jiurun.entity.notices.JsxfTzggYwzx">
        select * table_name
        <where>
            <if test="jgdwid != null">DWZJ = #{jgdwid}</if>
            <if test="advisoryStatus != null">AND ZXZT = #{advisoryStatus}</if>
            <if test="startTime != null">AND CJSJ <![CDATA[>=]]> #{startTime}</if>
            <if test="endTime != null">AND CJSJ <![CDATA[<=]]> #{endTime}</if>
            <if test="searchInfo">AND CONCAT(IFNULL(ZXYWBT,''),IFNULL(ZXYWNR,'')) LIKE CONCAT("%",#{searchInfo},"%")
            </if>
        </where>
    </select>

GROUP_CONCAT 用法

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

SELECT
 	GROUP_CONCAT(u.user_name order by u.user_name desc separator '，') as xcrName
    FROM
     jsxf_xj_jcjl jl
     LEFT JOIN sys_user u ON FIND_IN_SET(u.user_id, jl.XCR)
     where jl.jlzj = #{jlzj};

mysql插入重复时，除法更新操作

mysql特色 触发唯一约束新增转为更新

insert into db_shdwgl.sys_user
        (user_code, dept_id)
        values
        (#{userCode}, #{deptId}
        on duplicate key update
        user_code = values(user_code),
        dept_id = values(dept_id)

返回数据库名称的长度length

// length(database())返回与数据名长度，长度等于指定的数字时返回1，否则返回0
select (length(database()))=9;

列转行

select
tea.c_name as belongName,
SUM( CASE WHEN tei.category = ‘1’ THEN tead.score END ) AS industryScore,
SUM( CASE WHEN tei.category = ‘2’ THEN tead.score END ) AS populationScore,
SUM( CASE WHEN tei.category = ‘3’ THEN tead.score END ) AS cityCommScore,
SUM( CASE WHEN tei.category = ‘4’ THEN tead.score END ) AS otherScore
FROM
ps_task_execute_area_detail tead
LEFT JOIN ps_task_execute_area tea ON tea.id = tead.task_execute_area_id
LEFT JOIN ps_task_execute_item tei ON tei.model_param_id = tead.task_execute_item
GROUP BY
tea.c_name,tei.category

find_in_set()函数

场景：
一个字段的内容是逗号隔开的那种

查询当前输入的号码，是否在副号列表中。telephone_vice内容为(12345678911,98765432111)。
下面查询，只要telephone_vice字段的内容中包含其中一个值，就匹配
SELECT *
FROM s_user
WHERE find_in_set(#{telephone}, telephone_vice)

having分组筛选

1、SQL出现having的原因是，where关键字无法与聚合函数一起使用
2、having关键字放在group by关键字后面，针对分组后的数据进行筛选

SELECT
university,
avg( question_cnt ) AS avg_question_cnt,
avg( answer_cnt ) AS avg_answer_cnt
FROM
user_profile GROUP BY university
HAVING
avg_question_cnt < 5 OR avg_answer_cnt < 20