MySQL与mybatis查询精华（本人总结）持续更新中

最新推荐文章于 2022-11-06 10:42:52 发布

huicooling

最新推荐文章于 2022-11-06 10:42:52 发布

阅读量786

点赞数 1

分类专栏：数据库文章标签： mysql mybatis

本文链接：https://blog.csdn.net/weixin_40521823/article/details/89609098

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1、统计某一个字段出现的次数：

count(CASE WHEN sex_code = 1 THEN 1 END) AS 'male_number'

2、mybatis多字段输入

provinceName:参数名称,native_place_name:数据库字段名

<if test=" provinceName !=null and !provinceName.isEmpty()">
    and native_place_name in
    <foreach collection="provinceName" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</if>

前端接入的参数数据类型：

@Param("provinceName") List<String> provinceName

3、替换空格，转移符

regexp_replace(djsm,'\r|\t|\n','') as class_name

4、去除字段空格

trim(a.xsxm) as student_name

5、数据累加函数 @sum

SELECT aa.grade as year,
if(bb.sum_number IS NULL ,aa.sum_number,aa.sum_number-bb.sum_number)  AS 'sum_number',
if(bb.male_number IS NULL ,aa.male_number,aa.male_number-bb.male_number)  AS 'male_number',
if(bb.famale_number IS NULL ,aa.famale_number,aa.famale_number-bb.famale_number)  AS 'famale_number'
from
(SELECT
grade,
@csum:=@csum+num as sum_number,
@dsum:=@dsum+male_number as male_number,
@esum:=@esum+famale_number as famale_number
FROM
(SELECT
grade,
count(*) AS num,
count(CASE WHEN sex_code = 1 THEN 1 END) AS 'male_number',
count(CASE WHEN sex_code = 2 THEN 2 END) AS 'famale_number'
FROM
student_info
WHERE 1=1
and sex_code IS NOT NULL
<if test="majorCode !=99 and majorCode !=null and majorCode !=''">
 and major_code=#{majorCode}
</if>
GROUP BY
grade) a,
(SELECT @csum := 0) c,(SELECT @dsum := 0) d,(SELECT @esum := 0) e ORDER BY grade)aa
LEFT JOIN
(SELECT
grade,
@csum1:=@csum1+num as sum_number,
@dsum1:=@dsum1+male_number as male_number,
@esum1:=@esum1+famale_number as famale_number
FROM
(SELECT
grade,
count(*) AS num,
count(CASE WHEN sex_code = 1 THEN 1 END) AS 'male_number',
count(CASE WHEN sex_code = 2 THEN 2 END) AS 'famale_number'
FROM
student_info
WHERE 1=1
and sex_code IS NOT NULL
<if test="majorCode !=99 and majorCode !=null and majorCode !=''">
  and major_code=#{majorCode}
</if>
GROUP BY
grade) a,
(SELECT @csum1 := 0) c,(SELECT @dsum1 := 0) d,(SELECT @esum1 := 0) e ORDER BY grade)bb
ON aa.grade-3=bb.grade
order by
year asc

6、查询某一时间段的数据，前端传入

SELECT
    grade,
    count(*) AS number
FROM
    student_info
WHERE
 sex_code IS NOT NULL
and grade in (#{year},#{year}-1,#{year}-2)
<if test="unitCode !=99 and unitCode != null and unitCode != ''">
    and unit_code=#{unitCode}
</if>
GROUP BY
  grade

7、模糊查询

<if test="teacherName != '99' and teacherName != null and teacherName != ''">
    and a.teacher_name like CONCAT(CONCAT('%',#{teacherName}),'%')
</if>

8、限制查询的条数（如按借阅量的次数排名进行筛选）

定义一个参数：rank

order by
  history_borrowing_book_number desc
  <if test="rank !=null">
    limit  #{rank}
  </if>

但是mybatis自动会给我们参数加引号，导致不断报语法错误，故需要将#{rank}改为${rank},修改后为：

order by
  history_borrowing_book_number desc
  <if test="rank !=null">
    limit  ${rank}
  </if>

改完仍然报错，错误原因，这里用了分页插件，分页插件也有limit限制，两个limit肯定报语法错误，修正方法，将此sql语句括起来当一个新表，select * from 此表。

9、添加sql索引，可以使得查询更快

1.添加PRIMARY KEY（主键索引）：
key reader_code_index ('reader_code')
2.添加UNIQUE(唯一索引) ：
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
 
3.添加INDEX(普通索引) ：
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
 
4.添加FULLTEXT(全文索引) ：
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
 
5.添加多列索引：
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

10、将含某一字的分为一类

(case
	WHEN f.xwdm IS NULL THEN '专科及以下'
	WHEN f.xwdm LIKE '%学士%' or f.xwdm='交换学习' THEN '本科'
	WHEN f.xwdm LIKE '%硕士%' THEN '硕士'
	WHEN f.xwdm LIKE '%博士%' THEN '博士'
	END) as education_background

11、将一张表中两字段进行拼接：

concat(',',a.zc,a.dezc) AS zhicheng  #这种，如果其中一个为空，则返回空
concat_ws(',',a.zc,a.dezc) AS zhicheng  #这种，如果其中一个为空，也无所谓
group_concat(a.zc SEPARATOR ',')  #这种是sql中的分组拼接函数
concat_ws(',',collect_set(technical_post)) AS technical_post #这种是hive中的分组拼接函数

12、通过出生日期计算年龄

cast((unix_timestamp(current_date)-unix_timestamp(a.csrq,'yyyy-MM-dd'))/31536000 as INT) AS age

13、按某一列值进行排序作为一列

RANK() OVER (ORDER BY a.yjys DESC) as rank

14、读者有学生和老师，怎么判断

 if(a.xgh=b.student_code,b.unit_name,c.unit_name) as unit_name,
 if(a.xgh=b.student_code,b.major_name,null) as major_name

15、将字段名用符号隔开

GROUP_CONCAT(a.violation_reason SEPARATOR '||') as violation_reason

16、提取字段的首字母

(case
when substr(a.syh,0,1)= 'A' then '马列类'
when substr(a.syh,0,1)= 'B' then '哲学宗教类'
when substr(a.syh,0,1)= 'C' then '社科类'
when substr(a.syh,0,1)= 'D' then '政治法律类'
when substr(a.syh,0,1)= 'E' then '军事类'
when substr(a.syh,0,1)= 'F' then '经济类'
when substr(a.syh,0,1)= 'G' then '文化教育体育类'
when substr(a.syh,0,1)= 'H' then '语言文字类'
when substr(a.syh,0,1)= 'I' then '文学类'
when substr(a.syh,0,1)= 'J' then '艺术类'
when substr(a.syh,0,1)= 'K' then '历史地理类'
when substr(a.syh,0,1)= 'N' then '自然科学类'
when substr(a.syh,0,1)= 'O' then '数理科学与化学类'
when substr(a.syh,0,1)= 'P' then '天文学与地球科学类'
when substr(a.syh,0,1)= 'Q' then '生物科学类'
when substr(a.syh,0,1)= 'R' then '医药卫生类'
when substr(a.syh,0,1)= 'S' then '农业科学类'
when substr(a.syh,0,1)= 'T' then '工业技术类'
when substr(a.syh,0,1)= 'U' then '交通运输类'
when substr(a.syh,0,1)= 'V' then '航空航天类'
when substr(a.syh,0,1)= 'X' then '环境科学与安全科学类'
when substr(a.syh,0,1)= 'Z' then '综合性图书类'
end) AS book_class_name

17、获取当前年

第一种：SELECT year(NOW())
第二种：SELECT SUBSTR(current_date,1,4)

18、hive数据类型的转换

String转int
 cast(‘abc’ as int)
string转date
cast(‘abc’ as date)

19、mysql字符串的拆分

某一个人可能有多个职称，如下所示：

中教二级,助理研究员

助理工程师,副教授,工程师

助理讲师,高级实习指导教师,讲师

助理工程师,工程师

需要将这些职称进行拆分，以逗号作为拆分符：

SELECT
    SUBSTRING_INDEX(worker_technical_post,',',-1) as worker_technical_post,
    count(worker_technical_post) AS 'number'
FROM
    achievement_analyse_info
WHERE
    worker_technical_post is not null
GROUP BY
    SUBSTRING_INDEX(worker_technical_post,',',-1)
ORDER BY number DESC

20、业务逻辑层判断传入接口是否为空

if (StringUtils.isBlank(unitCode)) {
    return lengthwaysAnalyseMapper.unitProjectNumberUnitIsNull(year);
} else {
    return lengthwaysAnalyseMapper.unitProjectNumber(year,unitCode);
}

21、sql更新语句

UPDATE project_fundation_info 
	SET project_level=2
	WHERE project_level IS NULL

22、将同一字段以逗号分隔，进行拆分

SELECT
    SUBSTRING_INDEX(worker_technical_post,',',-1)
 as worker_technical_post,
    count(worker_technical_post) AS 'number'
FROM
    achievement_analyse_info

23、单点登录的配置

pom.xml

在前面需要配置：

<!-- CAS 单点登录 -->
   <org.jasig.cas.client.version>3.2.1</org.jasig.cas.client.version>
</properties>

<!-- 单点登录 统一身份认证 start -->
<dependency> 
<groupId>net.unicon.cas</groupId> 	<artifactId>cas-client-autoconfig-support</artifactId>
<version>1.4.0-GA</version> 
</dependency>
<dependency>
     <groupId>org.jasig.cas.client</groupId>
     <artifactId>cas-client-core</artifactId>
     <version>${org.jasig.cas.client.version}</version>
</dependency>
      <!-- 统一身份认证 end -->
<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-security</artifactId>
   <version>1.4.0.RELEASE</version>
</dependency>
<dependency>
   <groupId>org.springframework.security</groupId>
   <artifactId>spring-security-cas</artifactId>
   <version>4.1.1.RELEASE</version>
</dependency>

24、找出科研项目按单位，横向，纵向分组的sql语句

SELECT
    unitName,
    SUM(CASE WHEN project_type = '横向' THEN a.fundation ELSE 0 END) AS horizontalProjectFundation,
    SUM(CASE WHEN project_type = '纵向' THEN a.fundation ELSE 0 END) AS lengthwaysProjectFundation
FROM
    (SELECT
        project_company_name as unitName,
        project_type,
        sum(contract_amount) as fundation
    FROM
        ky_scientific_research_info
    WHERE project_company_name is not NULL
          and annual=#{annual}
    GROUP BY
        project_company_name,
        project_type)a
GROUP BY unitName

25、将不是yyyy-MM-dd格式的数据转换成yyyy-MM-dd格式

from_unixtime(unix_timestamp(
    (CASE
        WHEN instr(a.csrq,'JAN')>0 THEN regexp_replace(a.csrq,'JAN','01')
        WHEN instr(a.csrq,'FEB')>0 THEN regexp_replace(a.csrq,'FEB','02')
        WHEN instr(a.csrq,'MAR')>0 THEN regexp_replace(a.csrq,'MAR','03')
        WHEN instr(a.csrq,'APR')>0 THEN regexp_replace(a.csrq,'APR','04')
        WHEN instr(a.csrq,'MAY')>0 THEN regexp_replace(a.csrq,'MAY','05')
        WHEN instr(a.csrq,'JUN')>0 THEN regexp_replace(a.csrq,'JUN','06')
        WHEN instr(a.csrq,'JUL')>0 THEN regexp_replace(a.csrq,'JUL','07')
        WHEN instr(a.csrq,'AUG')>0 THEN regexp_replace(a.csrq,'AUG','08')
        WHEN instr(a.csrq,'SEP')>0 THEN regexp_replace(a.csrq,'SEP','09')
        WHEN instr(a.csrq,'OCT')>0 THEN regexp_replace(a.csrq,'OCT','10')
        WHEN instr(a.csrq,'NOV')>0 THEN regexp_replace(a.csrq,'NOV','11')
        WHEN instr(a.csrq,'DEC')>0 THEN regexp_replace(a.csrq,'DEC','12')
        END
    ),
 'dd-mm-yy'),'yyyy-mm-dd') as birthday

26、mysql算出3年前的某天

（1）3年前的今天
date_add(now(),interval -3 year)
（2）某一日期三年前的日期
date_add(SJ,interval -3 year)

27、合同日期段格式，截取合同开始时间

原始数据为：

        XXHT
2017.10.23至2021.08.31
2010.11.15至2013.11.14
2013.11.15至2014.11.14
2014.11.15至2016.08.31
2016.09.01至2017.08.31
2005.10.24至2006.10.24
2007.09.01至2008.07.31
2008.09.01至2011.07.31
2011.8.1至2014.8.31

只需要合同的开始日期，故需要进行截取，使用mysql中的substring_index函数

SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(xxht,'.',3),'至',1),'-',1) as HTKSSJ,

其截取后的字段：

  HTKSSJ
2017.10.23
2010.11.15
2013.11.15
2014.11.15
2016.09.01
2005.10.24
2007.09.01
2008.09.01
2011.8.1

huicooling

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL与mybatis查询精华（本人总结）持续更新中

1、统计某一个字段出现的次数：count(CASE WHEN sex_code = 1 THEN 1 END) AS 'male_number'2、mybatis多字段输入provinceName:参数名称,native_place_name:数据库字段名<if test=" provinceName !=null and !provinceName.isEmpty()"...
复制链接

扫一扫