【MyBatis SQL 查询总结】

一、格式

SELECT 
查询属性
FROM  
表名 
LEFT JOIN  表名  ON 连接条件
WHERE
查询条件
GROUP BY
分组属性 或分组属性1，分组属性2 
ORDER BY
排序属性1，排序属性2

二、查询

(1)入参为JSONObject

• Mapper

• Xml

<select id="getWarningList" resultType="com.test.vo.WarningVo">
    SELECT
      a.early_warning_id,
      a.user_id,
      a.early_warning_status,
      a.creator,
      a.receive_time,
      a.early_warning_mode,
      a.early_warning_interval,
      a.receive_mode,
      a.receive_time_interval_start,
      a.receive_time_interval_end,
      a.early_warning_notice,
      b.nickname,
      b.account,
      b.expiration_time
    FROM early_warning a
    LEFT JOIN sys_admin b
    ON a.user_id = b.id
    where a.status = 1
    <if test="mobile != null and mobile != ''">
      and b.mobile like concat('%',#{mobile},'%')
    </if>
    <if test="account != null and account != ''">
        and b.account like concat('%',#{account},'%')
    </if>
    <if test="nickname != null and nickname != ''">
        and b.nickname like concat('%',#{nickname},'%')
    </if>
    <if test="userIds != null and userIds.size() > 0">
        and a.user_id in
        <foreach collection="userIds" item="userId" index="index" open="(" close=")" separator=",">
            #{userId}
        </foreach>
    </if>
    order by a.create_time DESC
</select>

(2)入参为该实体类的查询参数类

(推荐这样写)

• Mapper

• Xml

<select id="getAccountConfigList" resultType="com.test.vo.AccountConfigVO" parameterType="com.test.params.AccountConfigParams">
  SELECT
  zt.*
  FROM
  account_config zt
  where
  zt.status = 1
  <if test="params != null and params != ''">
    and (zt.zhi_tui_unit_id LIKE concat('%',#{params},'%')
    OR zt.zhi_tui_user_id LIKE concat('%',#{params},'%')
    <if test="createIdList != null and createIdList.size>0">
      OR zt.create_id in
      <foreach collection="createIdList" item="createId" index="index" open="(" close=")" separator=",">
        #{createId}
      </foreach>
    </if>
     )
  </if>
  <if test="accountStatus != null">
    and zt.account_status = #{accountStatus}
  </if>
  <if test="id != null">
    and zt.id = #{id}
  </if>
  ORDER BY zt.create_time DESC
</select>

三、更新

(1)更新单条数据(根据主键id更新)

1.传入固定参数更新

• mapper

• xml

<update id="updateClientAccount">
  UPDATE client_account
  SET valid_end_date = #{validEndDate},
      update_time=now()
  WHERE app_id = #{appId}
</update>

2.传入map参数更新

• mapper

• xml

<update id="doDelete" >
    UPDATE test_user
    SET status=#{status},
    update_time=now()
    WHERE
    test_user_id=#{testUserId}
</update>

3.传入可变dto参数更新

• mapper

• Xml

<update id="updateRelationModel" parameterType="com.test.dto.RelationUpdateDto">
  UPDATE
    relation_model
    SET
    <trim prefix="" suffix="" suffixOverrides="," >
        <if test="corpusJson != null and corpusJson!=''">
            corpus_json = #{corpusJson},
        </if>
        <if test="relationVerifierState != null">
            verifier_state = #{relationVerifierState},
        </if>
        <if test="state != null">
            state = #{state},
        </if>
        <if test="operator != null and operator!=''">
            operator = #{operator},
        </if>
        <if test="eventType != null">
            event_type = #{eventType},
        </if>
        <if test="databaseType != null and databaseType == 2 ">
            database_type = #{databaseType},
        </if>
        update_time = NOW(),
    </trim>
    WHERE
    <if test="id != null">
        id = #{id}
    </if>
</update>

(2)批量更新(根据id逻辑删除)

注:传入的参数非实体类时mapper中需要加@Param()注解 但有的又没有加 我也搞不很清楚了 一般我都加上

1.写法一

• mapper

• xml

<update id="unboundAccountByUserIds">
       update user_weixin
       set status = 0 where user_id in
       <foreach collection="list" item="id" index="index" open="(" close=")" separator=",">
           #{id}
       </foreach>
</update>

2.写法二

• mapper

• xml

<update id="updateStatusByIds">
  UPDATE
  account_config
  SET
  <if test="type != null">
    <choose>
      <when test="type == 3">
        account_status = 1
      </when>
      <when test="type == 2">
        account_status = 0
      </when>
      <otherwise>
        status = 0
      </otherwise>
    </choose>
  </if>
  <if test="type == null">
    status = 0
  </if>
  WHERE
  <if test="ids != null and ids.size>0">
    id in
    <foreach collection="ids" item="id" index="index" open="(" close=")" separator=",">
      #{id}
    </foreach>
  </if>
</update>

四、插入

(1)固定参数插入

• mapper

• xml

<insert id="addDataUser" keyProperty="id" useGeneratedKeys="true" parameterType="com.test.po.DataUser">
    insert into user_weixin (user_id,
                                open_id,
                                access_token,
                                expires_in,
                                union_id,
                                headimgurl,
                                subscribe,
                                nickname,
                                language,
                                subscribe_time,
                                remark,
                                group_id,
                                subscribe_scene,
                                qr_scene,
                                qr_scene_str)
    values (#{userId},
            #{openId},
            #{accessToken},
            #{expiresIn},
            #{unionId},
            #{headimgurl},
            #{subscribe},
            #{nickname},
            #{language},
            #{subscribeTime},
            #{remark},
            #{groupId},
            #{subscribeScene},
            #{qrScene},
            #{qrSceneStr});
</insert>

(2)可变参数插入

• Mapper

• Xml

<insert id="add" keyProperty="id" keyColumn="id"
        parameterType="com.test.po.PlatformDataConfig" useGeneratedKeys="true">
    insert into platform_data_config
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="dataConfigName != null">
            data_config_name,
        </if>
        <if test="filterKeyword != null">
            filter_keyword,
        </if>
        <if test="dataSolidifyMin != null">
            data_solidify_min,
        </if>
        <if test="dataSolidifyMax != null">
            data_solidify_max,
        </if>
        <if test="operator != null">
            operator,
        </if>
        <if test="creator != null">
            creator,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="dataConfigName != null">
            #{dataConfigName},
        </if>
        <if test="filterKeyword != null">
            #{filterKeyword},
        </if>
        <if test="dataSolidifyMin != null">
            #{dataSolidifyMin},
        </if>
        <if test="dataSolidifyMax != null">
            #{dataSolidifyMax},
        </if>
        <if test="operator != null">
            #{operator},
        </if>
        <if test="creator != null">
            #{creator},
        </if>
    </trim>
</insert>

五、其他动态sql的使用

（1）where语句和Choose(when,otherwise)

（2）set语句

（3）Trim

（4）foreach语句

（5）SQL块

（6）Bind

（7）if语句

（8）模糊查询

<if test=" importantEventName != null and importantEventName != ''">
  and fs.data_config_name LIKE concat('%',#{importantEventName},'%')
</if>

（9）日期格式化

返回给前台年-月-日的格式

• DATE_FORMAT( bill_time, ‘%Y-%m-%d’ ) billTime

返回给前台年-月-日-时-分的格式

• DATE_FORMAT(sale_time,‘%Y-%m-%d %H:%i’) AS saleTimeStr

返回给前台年-月-日-时-分-秒的格式

• DATE_FORMAT( a.create_time, ‘%Y-%m-%d %H:%m:%s’ ) createTime,

（10）开始结束时间查询

<if test="startTime!=null and startTime!=''">
    <![CDATA[
                            and create_time >= #{startTime}
            ]]>
</if>
<if test="endTime!=null and endTime!=''">
    <![CDATA[
                            and create_time <= #{endTime}
            ]]>
</if>

或

<if test="startTime != null and startTime != ''">
    and cancellation_time &gt;= CONCAT(#{startTime}, ' 00:00:00') //从00点00分00秒开始查询
</if>
<if test="endTime != null and endTime != ''">
    and cancellation_time &lt;= CONCAT(#{endTime}, ' 23:59:59') //到该日期的23点59分59秒结束
</if>
或
<if test="startTime != null and startTime != ''">
  and pu.purchase_time &gt;= #{startTime}
</if>
<if test="endTime != null and endTime != ''">
  and pu.purchase_time &lt;= ADDDATE(#{endTime},1) //结束的那天加一天不然会查不到结束那天的信息
</if>

（11）更新表中与其他表关联得数据

update sale_new os
 left join product p on os.product_id = p.code
 LEFT JOIN purchase pu ON pu.delete_flg = 0 AND pu.number = os.t_number
 LEFT JOIN supplier su ON su.delete_flg = 0 AND pu.supplier_id = su.`code`
 LEFT JOIN brand b ON b.delete_flg = 0 AND pu.brand_id = b.`code`
 LEFT JOIN division d ON d.delete_flg = 0 AND pu.division_id = d.`code`
 set 
os.product_name = p.name,
os.product_type = p.type,
os.customer_code = pu.supplier_id,
os.customer_name = su.name,
os.brand_code = pu.brand_id,
os.brand_name = b.name,
os.division_code = pu.division_id,
os.division_name = d.name;

（12）检测sql执行快慢及列表

http://127.0.0.1:8080/{projectName}/druid/index.html

（13）结果为空时进行赋值

IFNULL(manageMoney,0)
IFNULL( COUNT(*), 0 )

（14）基本聚合函数

聚合函数一般和分组（GROUP BY）一起用根据某个属性进行汇总

• SUM( deposit )
• COUNT(*)
• COUNT(DISTINCT *) 错误语句
• COUNT(DISTINCT id) 返回彼此不相同非null得行数 不存在返回0 只使用- DISTINCT是不过滤null的
• COUNT(某列) 返回该列不为null的行数 不存在返回（bigint）0 通常使用这种方式来判断是否有满足条件的数据
• COUNT（*）直接统计所有行不会过滤null
• COUNT(属性列1，属性列2) 错误写法 他只能作用在单列
• MAX（字段名）
• MIN（字段名）
• AVG（字段名）
• CONCAT（xx,xx）第一个字段与第二个字段拼接 也可拼接多个字段

（15）直接赋值给某属性

0.00 AS printMoney 或 “打印服务费” AS optionStyle

（16）可在查询的属性中直接进行加减运算

• aa.zheng - aa.fu AS nowNum 返回给前台的数为这两个数的差
• -aa.fu AS nowNum 返回给前台的数为该数的相反数
• CONCAT(‘挂失日期:’,DATE_FORMAT(rl.report_time,‘%Y-%m-%d %H:%i:%s’),’ 理由:',rl.report_loss_reason) AS reportLossReason
  拼接字段进行返回

（17）对条件进行判断性添加

>= 不能直接写成<=
不等条件

<if test="startTime != null and startTime != ''">
    and cancellation_time &gt;= CONCAT(#{startTime}, ' 00:00:00') //从00点00分00秒开始查询
</if>
<if test="endTime != null and endTime != ''">
    and cancellation_time &lt;= CONCAT(#{endTime}, ' 23:59:59') //到该日期的23点59分59秒结束
</if>

或

<if test="startTime != null and startTime != ''">
  and pu.purchase_time &gt;= #{startTime}
</if>
<if test="endTime != null and endTime != ''">
  and pu.purchase_time &lt;= ADDDATE(#{endTime},1) //结束的那天加一天不然会查不到结束那天的信息
</if>

相等条件

<if test="billNo != null and billNo != ''">
  and sa.bill_no = #{billNo}
</if>

模糊查询

<if test="idNumber != null and idNumber != ''">
    AND a.id_number like CONCAT('%',#{idNumber},'%')
</if>

（18）limit

select * from table limit m,n

其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。

select * from tablename limit 2,4

从下标2开始取4条 即取出第3条至第6条，4条记录

（19）GROUP BY

• GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里，
• GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
  如：统计每个用户在每种商品上总共花了多少钱

SELECT Product，Buyer, SUM(Spending)
FROM `Order`
GROUP BY Product, Buyer

（20）ORDER BY xxx ASC|DESC( 升序|降序) 默认升序

• ORDER BY X
• ORDER BY X, Y