MyBatis与SQL实用技巧实用语法-摩杜云开发者社区

数据库SQL技巧

数值转字符

<select id="getMaterialsList" resultType="java.util.Map">
     select
         material_id materialId,
         material_name materialName,
         unit,
specification,
CONVERT(unit_price,CHAR) unitPrice
     from trace_agriculture_material
     where tenant_id = #{tenantId}
      and organize_code =#{organizeCode}
 </select>

数据库中unit_price字段类型为decimal(10,2)
展示在前端使用字符串即可，所以使用CONVERT(unit_price,CHAR)将数据库中的decimal精确数值类型转变为字符型，这样前端将字符串展示出来即可。

日期格式化

DATE_FORMAT(ate.plant_end_time,‘%Y-%m-%d’) plantEndTime
这句话是MySQL中的一个DATE_FORMAT函数，它用于将日期时间字段按照指定的格式进行格式化。
在这个例子中，ate.plant_end_time是一个日期时间字段，而’%Y-%m-%d’是你希望将其格式化的格式。DATE_FORMAT函数会将ate.plant_end_time字段的值转换为指定的格式，并将结果命名为plantEndTime。
具体来说，%Y代表四位数的年份，%m代表两位数的月份，%d代表两位数的日期。所以，'%Y-%m-%d’就代表将日期时间格式化为"YYYY-MM-DD"的格式。
举个例子，如果ate.plant_end_time字段的值是"2023-07-19 15:30:00"，那么DATE_FORMAT函数就会返回"2023-07-19"，并命名为plantEndTime。

"2023~07~19 15:30:00" DATE_FORMAT(your_date_column, '%Y~%m~%d %H:%i:%s')

时间作为过滤条件

select * from demo where createTime between '2018-12-12' and '2018-12-12'

注意这样几乎查不到任何数据，除非时间刚好等于’2018-12-12 00:00:00’
是由于时间精度引起的问题，createTime是精确到时分秒的，但是我们给出的过滤条件只精确到了天，所以在过滤的时候默认认为都是 "00:00:00”
也就相当于select * from demo where createTime between ‘2018-12-12 00:00:00’ and ‘2018-12-12 00:00:00’
可以改为select * from demo where createTime between ‘2018-12-12’ and ‘2018-12-13’
也就是后面的日期加一天
或者是将时间精确到时分秒
mybatis中有如下两种写法：

select * from demo where createTime between CONCAT(#{createTime},' 00:00:00') and CONCAT(#{createTime},' 23:59:59')
select * from demo where createTime between #{createTime and date_add(#{createTime}, INTERVAL 1 day)

数值四舍五入保留2位小数

ROUND(xx, 2) 可以将xx四舍五入保留2位小数
取一个不为null的值
SELECT IFNULL(SUM(TOTAL_WEIGHT),0) as TOTAL_WEIGHT FROM
其中IFNULL(SUM(TOTAL_WEIGHT),0)是取第一个不为null的值，也就是说我们希望查询不到的时候和为0，这样才符合逻辑

条件转换子语句

SELECT employee_name, position, experience,  
  CASE  
    WHEN position = 'Manager' AND experience >= 5 THEN 5000  
    WHEN position = 'Assistant Manager' AND experience >= 3 THEN 4000  
    ELSE 3000  
  END AS salary  
FROM employee_table;

员工工资计算：在一个公司中，根据员工的职位和工作经验，工资可能有所不同。你可以使用CASE语句根据职位和工作经验计算员工的工资。

left join

左连接返回左表中的所有行，即使右表中没有匹配的行。它能够查询出左表的所有记录，如果在右表中没有匹配项，则对应的结果字段为NULL。换句话说，左连接是基于左表的每一行，查找右表是否有匹配的行，如果有，则返回匹配的结果，如果没有，则返回NULL。

例如，考虑以下使用场景：一个电子商务网站有用户表（users）和订单表（orders）。用户表存储了所有用户的信息，订单表存储了每个用户的订单信息。现在需要查询所有用户及其对应的订单信息。在这个场景中，可以使用左连接来查询用户表中的所有用户以及他们的订单信息，即使有些用户没有订单。

以下是左连接的使用案例：
SELECT users.id, users.name, orders.order_id, orders.order_date
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

inner join

内连接（inner join）是基于两个表的连接谓词，将两个表的列组合到一起产生新的结果表。只有当至少有一个匹配时，内连接才会返回行。换句话说，内连接只会返回两个表中都存在的匹配行。
内连接的应用场景主要是在需要查询两个或多个表中共有的数据时。例如，假设有两个表，一个是学生表，一个是课程表，它们之间有一个成绩表，需要查询每个学生的成绩，那么就需要使用内连接。
以下是内连接的使用案例：
SELECT students.name, grades.subject, grades.grade
FROM students
INNER JOIN grades ON students.student_id = grades.student_id;
在这个查询中，我们使用了学生表（students）和成绩表（grades）进行内连接，连接条件是两个表中的学生ID（student_id）相等。通过这个查询，我们可以获取每个学生的姓名、科目和成绩。