sql和缓存知识点总结与不常见sql整理

一个有灵魂的搬运工

已于 2023-09-27 17:45:50 修改

阅读量167

点赞数

文章标签： sql 缓存数据库

于 2023-02-17 09:55:12 首次发布

本文链接：https://blog.csdn.net/qq_45885060/article/details/129078961

版权

本文总结了SQL相关面试中关于缓存、多字段模糊查询、时间区间筛选、汇总统计查询的技巧，包括使用LIKE和CONCAT进行模糊匹配，以及日期区间的方法。同时，文章探讨了在XML配置中使用foreach处理集合的注意事项，如遍历List、Array和Map。此外，还介绍了MySQL中处理逗号分隔字段的查询策略。

摘要由CSDN通过智能技术生成

1.sql相关和有有关缓存的面试总结： http://www.cnblogs.com/qixuejia/p/3637735.html

2.一个sql入参匹配多个字段模糊查询：(关键字搜索)

<if test="keyWord != null and keyWord != ''">
    and (
        a.bank_name like concat('%', #{keyWord}, '%') 
        or b.name like concat('%', #{keyWord}, '%')
    	or a.bank_code like concat('%', #{keyWord}, '%') 
        or a.corporation_name like concat('%', #{keyWord}, '%')
        )
</if>

3.sql时间区间查询*

<if test="storageTimeStart != null"><!-- 开始时间检索 -->
    AND date_format(a.storage_time,'%Y%m%d') &lt;= date_format(#{storageTimeStart},'%Y%m%d')
</if>
<if test="storageTimeEnd != null"><!-- 结束时间检索 -->
    AND date_format(a.storage_time,'%Y%m%d') &gt;= date_format(#{storageTimeEnd},'%Y%m%d')
</if>

4.sql汇总查询

select 
    sc.cid as `课程ID`, 
    course.cname as `课程名称`,
    count(1) as `选学人数`,
    count(case when score < 60 then null else 1 end) as `合格人数`,
    count(case when score < 60 then 1 else null end) as `不合格人数`,
    round(avg(score)) as `平均成绩`,
    round(count(case when score < 60 then null else 1 end) / count(1), 2) as `通过率`,
    sum(case when score>= 85 then 1 else 0 end) as `100 - 85`,
    sum(case when score>= 70 and score< 85 then 1 else 0 end) as `85 - 70`,
    sum(case when score>= 60 and score<70 then 1 else 0 end) as `70 - 60`,
    sum(case when score< 60 then 1 else 0 end) as `60 - 0`
from sc, course
where sc.Cid = course.Cid
group by sc.Cid;

4.计算日期统计数据

方法1
下面例子中表名为tablename,条件字段名为inputdate

-- 查询今天
SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=0

-- 查询昨天
SELECT * FROM tablename where DATEDIFF(day,inputdate,GETDATE())=1

-- 查询本周
SELECT * FROM tablename where datediff(week,inputdate,getdate())=0

-- 查询上周
SELECT * FROM tablename where datediff(week,inputdate,getdate())=1

-- 查询本月
SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=0

-- 查询上月
SELECT * FROM tablename where DATEDIFF(month,inputdate,GETDATE())=1

-- 查询本季度的
select * from T_InterViewInfo where datediff(QQ,inputdate,getdate())=0

方法2

      select
      			-- 计算本月总数量
               count(case when(DATE_FORMAT (a.feedback_date,'%Y%m') =DATE_FORMAT( CURDATE( ) , '%Y%m' )) then 1 else null end) as monthTotalQuantity,
               -- 计算本月已处理数量（and后面跟已处理的条件）
               count(case when(DATE_FORMAT (a.feedback_date,'%Y%m') =DATE_FORMAT( CURDATE( ) , '%Y%m' ) and a.processing_status = 5 ) then 1 else null end) as monthProcessedNum,
               -- 计算今日已处理问题数量（and后面跟已处理的条件）
               count(case when(to_days(a.feedback_date) = to_days(now()) and a.processing_status = 5) then 1 else null end) as todayProcessedNum,
                -- 计算今日已上报问题数量（and后面跟已上报的条件）
               count(case when(to_days(a.feedback_date) = to_days(now()) and a.processing_status >0) then 1 else null end) as todayAppearNum,
               -- 计算今日问题数量（and后面跟今日问题数量检索的条件）
               count(case when(to_days(a.feedback_date) = to_days(now()) and a.processing_status >=0) then 1 else null end) as todayTotalQuantity
        from eq_problem_feedback a
        where a.is_delete = 0

5.mysql查询当天的数据

select * from table where to_days(时间字段) = to_days(now());

6.mysql查询昨天的数据

select * from table where to_days(now( ) ) - to_days( 时间字段名) <= 1

7.mysql查询近一周的数据

SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 7 DAY)

8.mysql查询近一个月的数据

SELECT * FROM table WHERE date(时间字段) >= DATE_SUB(CURDATE(),INTERVAL 30 DAY)

9.mysql查询本月的数据

select * from table where DATE_FORMAT(时间字段, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

10.mysql查询上月的数据

select * from table where PERIOD_DIFF(date_format(now(),'%Y%m') , date_format(时间字段,'%Y%m')) =1

11.mysql查询本年的数据

-- 查询本年的数据
select * from table where YEAR(时间字段)=YEAR(now()); 

-- 计算一年中每个月的数据
SELECT
            sum( CASE MONTH ( create_time ) WHEN '1' THEN 1 ELSE 0 END ) AS a1,
            sum( CASE MONTH ( create_time ) WHEN '2' THEN 1 ELSE 0 END ) AS a2,
            sum( CASE MONTH ( create_time ) WHEN '3' THEN 1 ELSE 0 END ) AS a3,
            sum( CASE MONTH ( create_time ) WHEN '4' THEN 1 ELSE 0 END ) AS a4,
            sum( CASE MONTH ( create_time ) WHEN '5' THEN 1 ELSE 0 END ) AS a5,
            sum( CASE MONTH ( create_time ) WHEN '6' THEN 1 ELSE 0 END ) AS a6,
            sum( CASE MONTH ( create_time ) WHEN '7' THEN 1 ELSE 0 END ) AS a7,
            sum( CASE MONTH ( create_time ) WHEN '8' THEN 1 ELSE 0 END ) AS a8,
            sum( CASE MONTH ( create_time ) WHEN '9' THEN 1 ELSE 0 END ) AS a9,
            sum( CASE MONTH ( create_time ) WHEN '10' THEN 1 ELSE 0 END ) AS a10,
            sum( CASE MONTH ( create_time ) WHEN '11' THEN 1 ELSE 0 END ) AS a11,
            sum( CASE MONTH ( create_time ) WHEN '12' THEN 1 ELSE 0 END ) AS a12
        FROM
            eq_problem_feedback
        WHERE is_delete = 0

12.mysql查询上一年的数据

select * from table where year(时间字段)=year(date_sub(now(),interval 1 year));

13.mysql查询本周数据（周一为第一天）

SELECT * FROM table WHERE YEARWEEK(date_format(时间字段,'%Y-%m-%d'),1) = YEARWEEK(now(),1);

14.mysql查询近五分钟的数据

SELECT * FROM table WHERE 时间字段 >= DATE_SUB(now(),INTERVAL 5 MINUTE)

15.查询最近一周的每天的数量

select a.click_date,ifnull(b.count,0) as count
from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (
  select date(时间字段) as datetime, count(*) as count
  from 表名
  group by date(时间字段)
) b on a.click_date = b.datetime;

16.在xml里面foreach的用法

foreach元素的属性主要有: collection，item，index，separator，open，close。

item：集合中元素迭代时的别名（表示集合中的每一个元素），该参数为必选。
index：在list和数组中,index是元素的序号，在map中，index是元素的key，(用于表示在迭代过程中，每次迭代到的位置)，该参数可选
open：foreach代码的开始符号，一般是(和close=")“合用。常用在in(),values()时。该参数可选
separator：元素之间的分隔符（表示在迭代时数据以什么符号作为分隔符），例如在in()的时候，separator=”,“会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。
close: foreach代码的关闭符号，一般是)和open=”("合用。常用在in(),values()时。该参数可选。
collection：表示集合，数据源在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：

如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
针对最后一条，我们来看一下官方说法：
注意：你可以将一个 List 实例或者数组作为参数对象传给 MyBatis，当你这么做的时候，MyBatis 会自动将它包装在一个 Map 中并以名称为键。List 实例将会以“list”作为键，而数组实例的键将是“array”。

记得在每次遍历之前加判空处理，不然遍历为空in()会报错

-- 单参数List类型：
<select id="countByUserList" resultType="_int" parameterType="list">
	select count(*) from users
  	<where>
  		<if test="list!=null and list.size() !=0 ">
    		id in
    		<foreach item="item" collection="list" separator="," open="(" close=")" index="">
      			#{item.id, jdbcType=NUMERIC}
    		</foreach>
    	</if>
 	 </where>
</select>

-- 单参数array数组的类型：
<select id="selectxxxByParam" resultType="com.xxx.CityCodeAndSum" parameterType="com.xxx.LessonDetailStatisticsParam">
        select cityCode
        from tb_xxx_statistics
        where 1=1 and deleted = 0 and feedbackChannel = 3
        <if test="jobTypes != null and jobTypes.length != 0">
            AND job_type IN
            <foreach close=")" collection="jobTypes" index="index" item="item" open="(" separator=" , ">
                #{item,jdbcType=VARCHAR}
            </foreach>
        </if>
        group by cityCode
</select>

-- 自己把参数封装成Map的类型
-- collection的值为paramMap，是传入的参数Map的key：
-- index="key"表示map的键，item="value"表示键对应的值，separator="and"表示每次遍历都要凭借一个and
-- 注意：这里面{key}前面需要使用$，不用#
<select id="getDemoByMap" parameterType="map" resultType="hashMap">
    select * from demo
    <where>
        <if test="paramMap != null and paramMap.size() > 0">
            <foreach collection="paramMap" index="key" item="value" separator="and">
                ${key} = #{value}
            </foreach>
        </if>
    </where>
</select>

17.数据库里字段为逗号拼接，where条件书写

FIND_IN_SET(str,strlist) ： str 要查询的字符串，strlist 需查询的字段，strlist数据库以”,”分隔，形式如 (1,2,6,8,10,22)；该函数的作用是查询字段(strlist)中是否包含(str)的结果，返回结果为null或记录。

-- #{params.type}为入参，t1.types为数据库字段
<if test="params.type != null ">
     and  FIND_IN_SET(#{params.type},t1.types)
</if>

用 in 来进行实现：

 -- #{params.type}为入参，t1.types为数据库字段
<if test="params.type != null ">
    and t1.types in #{params.type}
</if>

1、in后面只能跟常量， find_in_set()函数可以使用常量或字段。
2、in是完全匹配，find_in_set()函数是精确匹配，字段值以英文”,”分隔。
另：like是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔，Find_IN_SET查询的结果要小于like查询的结果。