mysql和Mybatis常见使用问题

MySQL中时间的使用

mysql 时间查询

    <resultMap id="SectorStatisticMap2" type="java.util.HashMap">
        <result column="s_create_statistic_date" jdbcType="DATE"/>
        <result column="use_man_time" jdbcType="INTEGER"/>
    </resultMap>
    <select id="selectSector" resultType="java.util.List" resultMap="SectorStatisticMap2">
        SELECT s_create_statistic_date name,sum(use_man_time) value FROM `sector_statistic` WHERE
        s_mechanism_id = #{mechanismId}
        <if test="startDate != null and startDate != '' ">
            and DATE_FORMAT(s_create_statistic_date, '%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d')
        </if>
        <if test="endDate != null and endDate != '' ">
            and DATE_FORMAT(s_create_statistic_date, '%Y-%m-%d') &lt;= DATE_FORMAT(#{endDate},'%Y-%m-%d')
        </if>

        GROUP BY s_create_statistic_date
    </select>

mysql时间计算时间间隔

语法
DATEDIFF(datepart,startdate,enddate)
startdate 和 enddate 参数是合法的日期表达式。datepart 参数可以是下列的值：年月日时分秒等

https://www.runoob.com/sql/func-datediff.html

设置更新时间和创建时间的默认值

update

Excel数据导入时间处理

以接下来要用到的就是HSSFDateUtil这个工具类来处理时间，
Date setupTime = HSSFDateUtil.getJavaDate(Double.valueOf(sto.getSetupTime()));

sto.getSetupTime()：从Excel中获取到的日期（便是上文中的“43780”这串数字）

group by 的使用

group by 只显示一条

怎么显示多条？

在来一个group by

GROUP BY device_id, id

group by 时取每组前10条

https://blog.csdn.net/junzi528/article/details/84404412

1、直接查询出来就去重了，每组只有一个最高分

select
	s2.stuid,s2.subid,s2.grade 
from
	(
	select
	if( s1.subid = @subid, @rank := @rank + 1, @rank := 1 ) as rank,
		@subid := subid as tmp_subid,
		s1.stuid,s1.subid,s1.grade 
	from
		( select stuid,subid,grade from student_grade order by subid, grade desc ) s1,
		( select @subid := null, @rank := 1 ) tmp 
	) s2 
where
	s2.rank <= 2

2、直接查询出来，然后利用TreeSet进行去重，利用list进行

group by 和 order by 一起使用问题处理？？

根据公司id 分组，但是要根据时间排序，每个分组中取最新的三百条数据。有大佬知道这个sql 怎么写嘛

group by 时分页

另一种实现方法：

SELECT SQL_CALC_FOUND_ROWS uid from article where 1 = 1 group by uid limit 0,20;

执行此句获得相应页的数据之后，紧接着进行一次查询

SELECT FOUND_ROWS() as total 即可获得满足条件的总记录数，使用起来非常方便。


mysql分组查询group by时分页的实现。
https://blog.csdn.net/zhang197093/article/details/49425261

一对多关联查询分页

MybatisPlus增删改查

1、实体进行CRUD

1.实体继承Model类

2.mapper继承BaseMapper

3.controller  实体类参数直接调用方法

2、controller类直接写crud

​ service继承IService，实现类继承ServiceImpl

3、updateById

mybatis-plus 的默认配置 在调用 baseMapper.updateById 时 ，如果实体类中的字段属性为null，那么不将该属性更新到数据库

1）修改mybatis-plus配置

mybatis-plus:
  global-config:
    db-config:
      update-strategy: NOT_NULL
      # 字段策略 
      # IGNORED:"忽略判断,会更新NULL和空串",
      # NOT_NULL:"非 NULL判断,会更新空串",
      # NOT_EMPTY:"非空判断,不会更新NULL和空串",
      # 默认是NOT_NULL

2）自己写sql调用就好了。

MybatisPlus分页

1、分页配置类

@Configuration
@MapperScan("com.watson.traffic.blog.trafficblog.mapper")
public class MybatisPlusConfig {
    /**
     * 分页配置
     *
     * @return 分页
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
     * 
     *上面过期的时候的替代
     */
   //    @Bean
//    public MybatisPlusInterceptor mybatisPlusInterceptor() {
//        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
//        return interceptor;
//    }
//
//    @Bean
//    public ConfigurationCustomizer configurationCustomizer() {
//        return configuration -> configuration.setUseDeprecatedExecutor(false);
//    }

}


//https://blog.csdn.net/qq_41359651/article/details/112260207

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
    return interceptor;
}

2、QueryWrapper分页查询

此方法是使用PaginationInterceptor 作为分页插件.

@Test
void contextLoads() {
    Page<User> page = new Page<>(1, 2);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name", "wdh");
    Page<User> page1 = userMapper.selectPage(page, queryWrapper);
   	page.setRecords(page1.getRecords()).getRecords().forEach(System.out::println);
}

3、pageHelp的使用

依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version> 
</dependency>

代码
  PageHelper.startPage(page,pageSize);
  List<Company> List=companyDao.selectAll();
  PageInfo pageInfo = new PageInfo(list);
  return pageInfo;

4、Mybatis逻辑分页原理解析RowBounds

1

MybatisPlus查询与忽略指定字段

1、忽略字段

@TableField(exist = false)

2、查询指定字段

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name", "age");
        //只需要查询出name和age两个字段的数据

要查询出除manager_id和create_time外其它所有字段的数据

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select(User.class, info -> !info.getColumn().equals("manager_id")
                && !info.getColumn().equals("create_time"));

3、实体类字段与数据库字段不一致的问题

a、自己写sql 取别名

b、result标签指定

<resultMap id="BaseResultMap" type="com.cpiinfo.sysmgt.entity.Organization">
		<result column="id" property="id" jdbcType="VARCHAR" />

c、字段映射

@TableField("l_level")
private String level;  //级别  

d、表映射

@TableName("sys_user")
public class User {

4、数据库存储字段类型为JSON

实体类怎么操作？

字段：@TableField(typeHandler = FastjsonTypeHandler.class)  
类：@TableName(value = "t_store_expenditure_total",autoResultMap = true)

MybatisPlus使用聚合函数

1、求和

QueryWrapper<WoAssignsTask> queryWrapper1 = creatQueryWrapper(dto);
queryWrapper1.select("IFNULL(sum(operation_progress),0) as exceptionTotal");
Map<String, Object> map = getMap(queryWrapper1);

mybatis 传参

1、mapper参数为map,map中包含list

a、mapper层,

此时不用@param注解，直接取map的key，参数是对象的时候同理

List<WoAssignsExceptionAssignRepairDTO> listWoAssignsExceptionAssignRepairDTO(Map<String, Object> map);

b、xml层

 select s.id ,s.device_id ,s.exception_code ,s.diagnosis_type_name ,MAX(grade) grade,
        s.event_desc , s.fault_details_id, w.id as woAssignsExceptionAssignId
        from security_base_exception s join wo_assigns_exception_assign w on s.id = w.security_base_exception_id
        and w.repair_status ='unrepair'
        and w.handle_opinions = 'dispatch'
        <if test="diagnosisTypeName != null and diagnosisTypeName.trim() != ''">
            AND s.diagnosis_type_name = #{diagnosisTypeName}
        </if>
        <if test="grade != null and grade.trim() != '' ">
            AND s.grade = #{grade}
        </if>
        <if test="pvNode != null ">
<!--            AND s.device_id in (#{map.pvNode})-->
            AND s.device_id in
            <foreach collection="pvNode" item="oneItem" separator=","
                                        open="(" close=")">
                #{oneItem}
            </foreach>
        </if>
        group by device_id,diagnosis_type_name
    </select>

2、mybatis 执行传入的任意sql语句

a、mapper层

 /**
     * 自定义sql查询
     * @param sqlContent
     * @return
     */

    public List<LinkedHashMap<String, Object>> customQueryItem(String sqlContent);

b、mapper.xml

<select id="customQueryItem" parameterType="String"                   resultType="java.util.LinkedHashMap">
         <![CDATA[
            SELECT *  FROM (${value}) obj
          ]]>
    </select>

3、执行sql脚本文件

import org.apache.ibatis.jdbc.ScriptRunner;

import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Exec {

    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String url ="jdbc:mysql://localhost:3306/userinfo";
    private static final String username = "gfstack";
    private static final String password = "gfstack";
    private static final File file = new File("C:\\sql\\userinfo.sql");

    public static void main(String[] args) throws Exception {
        mybatisExec();
    }

    private static void mybatisExec() throws Exception {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, username, password);
        ScriptRunner runner = new ScriptRunner(conn);
        try {
            runner.setStopOnError(true);
            runner.runScript(new FileReader(file));
        } catch (Exception e) {
            e.printStackTrace();
        }
        conn.close();
    }

}

分页查询优化

原因：limit语句的查询时间与起始记录的位置成正比，limit语句对记录很多的表并不适合直接使用。

1) 尽量给出查询的大致范围

select * from order where orderId > 1000000  limit 0,10          0.03s

通过索引而不是全表扫描来找到数据，再进行分页

2) 子查询法

select  a.* from order a JOIN
(select order_id from order LIMIT 1000000, 10) b  on a.order_id=b.order_id     0.5s

​ 内层子查询通过索引扫描先进行分页得到数据，再关联外层查询通过索引查找得到最终数据，因为索引文件比数据文件小，所以内层查询时间耗时相对较小，而且内层查询中只包含索引列(覆盖索引)，数据就在查询索引上面，无需进行额外的查找

案例sql

CREATE TABLE `teacher` (
  `TID` varchar(10) NOT NULL,
  `Tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `addTime` datetime DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `total` int DEFAULT NULL,
  PRIMARY KEY (`TID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

行转列

统计每月数据，未找查到数据

SELECT 
 SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 11 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '0'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 10 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '1'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  9 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '2'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  8 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '3'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  7 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '4'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  6 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '5'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  5 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '6'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  4 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '7'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  3 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '8'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  2 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '9'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  1 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '10'
,SUM(case when FROM_UNIXTIME(addTime,'%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL  0 MONTH),'%Y-%m') AND `status` = '1' then total else 0 end) as '11'
FROM teacher

函数解释

# //对时间戳格式化成 2018-10
# FROM_UNIXTIME(addTime,'%Y-%m')
# //SQL获取当前时间格式 2019-08 ,根据expr值不同，依次获取前一个月1，前两个月2 ···
# DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 MONTH),'%Y-%m')
# // 函数用于以不同的格式显示日期/时间数据。
# DATE_FORMAT(date,format)
# //函数从日期减去指定的时间间隔。
# DATE_SUB(date,INTERVAL expr type)
# //函数返回当前的日期。
# CURDATE()

统计某个值每个月的累计

能统计出来

SELECT 
 SUM(case when month(addTime)='1'  AND `status` = '1' then total else 0 end) as '1月',
 SUM(case when month(addTime)='2'  AND `status` = '1' then total else 0 end) as '2月',
 SUM(case when month(addTime)='3'  AND `status` = '1' then total else 0 end) as '3月',
 SUM(case when month(addTime)='4'  AND `status` = '1' then total else 0 end) as '4月',
 SUM(case when month(addTime)='5'  AND `status` = '1' then total else 0 end) as '5月',
 SUM(case when month(addTime)='6'  AND `status` = '1' then total else 0 end) as '6月',
 SUM(case when month(addTime)='7'  AND `status` = '1' then total else 0 end) as '7月',
 SUM(case when month(addTime)='8'  AND `status` = '1' then total else 0 end) as '8月',
 SUM(case when month(addTime)='9'  AND `status` = '1' then total else 0 end) as '9月',
 SUM(case when month(addTime)='10'  AND `status` = '1' then total else 0 end) as '10月',
 SUM(case when month(addTime)='11'  AND `status` = '1' then total else 0 end) as '11月',
 SUM(case when month(addTime)='12'  AND `status` = '1' then total else 0 end) as '12月'
FROM teacher
where date_format(addTime,'%Y')='2021'

统计每月的累计条数

此处不能用count，能统计出来

SELECT 
 SUM(case when month(addTime)='1'  AND `status` = '1' then 1 else 0 end) as '1月',
 SUM(case when month(addTime)='2'  AND `status` = '1' then 1 else 0 end) as '2月',
 SUM(case when month(addTime)='3'  AND `status` = '1' then 1 else 0 end) as '3月',
 SUM(case when month(addTime)='4'  AND `status` = '1' then 1 else 0 end) as '4月',
 SUM(case when month(addTime)='5'  AND `status` = '1' then 1 else 0 end) as '5月',
 SUM(case when month(addTime)='6'  AND `status` = '1' then 1 else 0 end) as '6月',
 SUM(case when month(addTime)='7'  AND `status` = '1' then 1 else 0 end) as '7月',
 SUM(case when month(addTime)='8'  AND `status` = '1' then 1 else 0 end) as '8月',
 SUM(case when month(addTime)='9'  AND `status` = '1' then 1 else 0 end) as '9月',
 SUM(case when month(addTime)='10'  AND `status` = '1' then 1 else 0 end) as '10月',
 SUM(case when month(addTime)='11'  AND `status` = '1' then 1 else 0 end) as '11月',
 SUM(case when month(addTime)='12'  AND `status` = '1' then 1 else 0 end) as '12月'
FROM teacher
where date_format(addTime,'%Y')='2021'

统计数据

查询年数据

select year(addTime) as 年,
count(*) as 销售合计
from teacher t
group by year(addTime)

查询年月数据

select year(addTime) as 年,
month(addTime)as 月,
count(*)as 销售合计
from teacher t
group by year(addTime),
month(addTime)

查询年月日数据

select year(addTime)as 年,
month(addTime)as 月,
day(addTime)as 日,
count(*)as 销售合计
from teacher t
group by year(addTime),
month(addTime),
day(addTime)

sql 数据分月统计，表中只有每天的数据

现在要求求一年中每个月的统计数据（一条sql）

SELECT
  MONTH (  那个日期的字段  ),
  SUM(  需要统计的字段， 比如销售额什么的 )
FROM
  表
WHERE
  YEAR (  那个日期的字段  ) = 2010   -- 这里假设你要查 2010年的每月的统计。
GROUP BY
 MONTH (  那个日期的字段  )