数据库相关：历史使用SQL整理

悠悠 2022-12-15 02:23 103阅读 0赞

2020.10.14 将gurantee 字段更新到access\_company

查询两表数据情况

SELECT ac.legal_name,gu.guar_name,
    ac.legal_cert_no,gu.guar_id_no,
    ac.legal_cert_type,gu.guar_id_type
    from gurantee as gu
    LEFT JOIN  access_company as ac
    ON ac.access_no=gu.access_no

由于连表查询将gurantee 所有109条记录都展示了  
![共109条记录][109]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0aGlyZG9uZWw_size_16_color_FFFFFF_t_70_pic_center]联表语句更新

UPDATE access_company AS ac,gurantee AS gu
    SET ac.legal_name=gu.guar_name,
    ac.legal_cert_no=gu.guar_id_no,
    ac.legal_cert_type=gu.guar_id_type
    WHERE ac.access_no=gu.access_no;

![在这里插入图片描述][20201014173902536.png_pic_center]显示94条说明access\_company 表中有94条记录更新  
数据不一致的原因是有11条准入号相同的记录只更新了1条，还有5条未更新记录。109-10-5=94  
(其中legal\_cert\_type 为ID的4条记录更新了)  
![在这里插入图片描述][20201014182522151.png_pic_center]

--------------------

2020.10.22 将out\_orde 328记录risk\_ext 字段更新到317

UPDATE out_order o
    INNER JOIN
    (SELECT risk_ext
    FROM out_order
    WHERE id = '328') c
    SET o.risk_ext = c.risk_ext
    WHERE id = 317;

2020.10.23

MapperXML注意事项

所有where条件加入deleted=0

<select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            from user_role
            where id = #{id,jdbcType=BIGINT} and deleted=0
        </select>

删除变成更新

<update id="deleteByPrimaryKey" parameterType="java.lang.Long">
        update user_role set deleted=1
        where id = #{id,jdbcType=BIGINT} and deleted=0
      </update>

插入和更新 去掉默认的三个字段 （可以自己维护值）

created_date = #{createdDate,jdbcType=TIMESTAMP},
    modified_date = #{modifiedDate,jdbcType=TIMESTAMP},
    deleted = #{deleted,jdbcType=BIT}

<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.jd.jdd.yfk.role.domain.UserRole"
                useGeneratedKeys="true">
        insert into user_role (type, user_no, cust_no, product_code, role_code, ext_data, status)
        values (#{type,jdbcType=VARCHAR}, #{userNo,jdbcType=VARCHAR}, #{custNo,jdbcType=VARCHAR}, 
          #{productCode,jdbcType=VARCHAR}, #{roleCode,jdbcType=VARCHAR}, #{extData,jdbcType=OTHER}, 
          #{status,jdbcType=VARCHAR})
      </insert>

[109]: https://img-blog.csdnimg.cn/20201014173931492.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2x0aGlyZG9uZWw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/02fedfc9581d40128b670e30e5a53e9d.png
[20201014173902536.png_pic_center]: /images/20221123/99c8fc899c6f4428b1a9ef7ada8fc204.png
[20201014182522151.png_pic_center]: /images/20221123/2a9dad52923a423387f3a560c1c0ed41.png