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

最新推荐文章于 2024-07-18 04:04:27 发布

原创最新推荐文章于 2024-07-18 04:04:27 发布 · 484 阅读

0 ·

CC 4.0 BY-SA版权

其他专栏收录该内容

6 篇文章

订阅专栏

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条记录在这里插入图片描述联表语句更新

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;

在这里插入图片描述显示94条说明access_company 表中有94条记录更新
数据不一致的原因是有11条准入号相同的记录只更新了1条，还有5条未更新记录。109-10-5=94
(其中legal_cert_type 为ID的4条记录更新了)

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>