【MyBatis SQL 查询总结】

亦凉 2023-10-08 20:31 139阅读 0赞

#### 目录 ####

*  一、格式
 *  二、查询
 *   *  (1)入参为JSONObject
     *  (2)入参为该实体类的查询参数类
 *  三、更新
 *   *  (1)更新单条数据(根据主键id更新)
     *   *  1.传入固定参数更新
         *  2.传入map参数更新
         *  3.传入可变dto参数更新
     *  (2)批量更新(根据id逻辑删除)
     *   *  1.写法一
         *  2.写法二
 *  四、插入
 *   *  (1)固定参数插入
     *  (2)可变参数插入
 *  五、其他动态sql的使用
 *   *  （1）where语句和Choose(when,otherwise)
     *  （2）set语句
     *  （3）Trim
     *  （4）foreach语句
     *  （5）SQL块
     *  （6）Bind
     *  （7）if语句
     *  （8）模糊查询
     *  （9）日期格式化
     *  （10）开始结束时间查询
     *  （11）更新表中与其他表关联得数据
     *  （12）检测sql执行快慢及列表
     *  （13）结果为空时进行赋值
     *  （14）基本聚合函数
     *  （15）直接赋值给某属性
     *  （16）可在查询的属性中直接进行加减运算
     *  （17）对条件进行判断性添加
     *  （18）limit
     *  （19）GROUP BY
     *  （20）ORDER BY xxx ASC|DESC( 升序|降序) 默认升序

## 一、格式 ##

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) ###

![在这里插入图片描述][b6a3fa37e62a4db799591e03ef634643.png]

### （2）set语句 ###

![在这里插入图片描述][bd949861297641f785139010ab990446.png]

### （3）Trim ###

![在这里插入图片描述][c8348ed68de7404ea60f900316ca329b.png]  
![在这里插入图片描述][f5caa2ea0247410898151b3995591e45.png]

### （4）foreach语句 ###

![在这里插入图片描述][2bed1197a698458982b57fd9ed9137f4.png]

### （5）SQL块 ###

![在这里插入图片描述][1be1a733cf1847deb43e44e0c9a5d6fc.png]

### （6）Bind ###

![在这里插入图片描述][6f69e7287b434e25b2f7be85a1ca9ecb.png]

### （7）if语句 ###

![在这里插入图片描述][14bd4b2dfb2d436dadb10bbd30b89ef6.png]

### （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 >= CONCAT(#{startTime}, ' 00:00:00') //从00点00分00秒开始查询
    </if>
    <if test="endTime != null and endTime != ''">
        and cancellation_time <= CONCAT(#{endTime}, ' 23:59:59') //到该日期的23点59分59秒结束
    </if>
    或
    <if test="startTime != null and startTime != ''">
      and pu.purchase_time >= #{startTime}
    </if>
    <if test="endTime != null and endTime != ''">
      and pu.purchase_time <= 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 pu.purchase_time >= #{startTime}
    </if>
    <if test="endTime != null and endTime != ''">
      and pu.purchase_time <= 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

[b6a3fa37e62a4db799591e03ef634643.png]: https://img-blog.csdnimg.cn/b6a3fa37e62a4db799591e03ef634643.png
[bd949861297641f785139010ab990446.png]: https://img-blog.csdnimg.cn/bd949861297641f785139010ab990446.png
[c8348ed68de7404ea60f900316ca329b.png]: https://img-blog.csdnimg.cn/c8348ed68de7404ea60f900316ca329b.png
[f5caa2ea0247410898151b3995591e45.png]: https://img-blog.csdnimg.cn/f5caa2ea0247410898151b3995591e45.png
[2bed1197a698458982b57fd9ed9137f4.png]: https://img-blog.csdnimg.cn/2bed1197a698458982b57fd9ed9137f4.png
[1be1a733cf1847deb43e44e0c9a5d6fc.png]: https://img-blog.csdnimg.cn/1be1a733cf1847deb43e44e0c9a5d6fc.png
[6f69e7287b434e25b2f7be85a1ca9ecb.png]: https://img-blog.csdnimg.cn/6f69e7287b434e25b2f7be85a1ca9ecb.png
[14bd4b2dfb2d436dadb10bbd30b89ef6.png]: https://img-blog.csdnimg.cn/14bd4b2dfb2d436dadb10bbd30b89ef6.png