SQL批量操作技巧-优快云博客

开启事务（注解）

@Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)

sql–批量添加数据

注意：如果不加separator=","会导致产生的sql语句有问题，编译报sql语法错误；没有那个分割符产生的sql: insert into examdetial (idcard,enrollnumber…) VALUES (?,?..) (?,?..) ,即两个(?,?,)间少了一个逗号

 @Insert("<script>" +
            "insert into `examdetial` (idcard,enrollnumber,exambatch,examnum,exampost,examsubject)" +
            " VALUES " +
            " <foreach collection='detials' item='person' separator=','> " +
            "(#{person.idcard},#{person.enrollnumber},#{person.exambatch}," +
            " #{person.examnum},#{person.exampost},#{person.examsubject}) " +
            "</foreach>" +
            "</script>"

    )
    int adddlist(@Param("detials") List<Examdetial> examdetialList);

sql–批量删除数据

   @Delete("<script>" +
            "DELETE FROM `examdetial` WHERE enrollNumber IN " +
            "<foreach item='item' index='index' collection='par' open='(' separator=',' close=')'>" +
            "#{item} " +
            "</foreach>" +
            "</script>"  )
    Integer deleteExamnees(@Param("par") List<String> parlist);

sql–行转列 / 列转行

CREATE TABLE `TEST_TB_GRADE` (
  `ID` INT(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` VARCHAR(20) DEFAULT NULL,
  `COURSE` VARCHAR(20) DEFAULT NULL,
  `SCORE` FLOAT DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  VALUES
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

SELECT user_name ,
    MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
    MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
    MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
FROM test_tb_grade
GROUP BY USER_NAME;

MySQL批量插入数据时如果存在就更新，不存在就插入

首先在这里我们给stuid字段建立一个唯一索引，要注意的是唯一索引项不能够存储中文，否则添加索引为报错，如果表没有唯一键或没有设置合适的唯一约束，“INSERT INTO … ON DUPLICATE KEY UPDATE” 将不会生效。
在这里插入图片描述
接下来在SQL中加入 ON DUPLICATE KEY UPDATE 。。。（。。。是你想要更新的那些字段）

sql — 将一个表的数据更新至另一个表

INSERT INTO `stuscore` (stuid, kemu, defen)
	(SELECT stuid,kemu,defen FROM `opresults`  op
	LEFT JOIN `stulinkscore` lin  ON op.idcard = lin.zcard WHERE zid IS NOT NULL )

mysql 多表联合删除：当关联表没有数据的时候，其他表数据也无法删除

   DELETE g, w, p FROM `plan_work_grade` g 
   LEFT JOIN `plan_worktype` w ON g.wid = w.id 
   LEFT JOIN `testplans` p ON w.planid = p.id WHERE p.earecode=#{earecode}

使用mybatis 动态批量修改数据

只更新一个字段

Integer updatePasscodeByzcard(List<String> passStuidlist);
<update id="updatePasscodeByzcard">
    UPDATE
    kcstudent
    <set>
      passremark = 1
    </set>
    <where>
      zcard in (
      <foreach collection="list" separator="," item="item">
        #{item}
      </foreach>
      )
    </where>
  </update>

更新多个字段
foreach

int updateKcstudentByzcard(List<Kcstudent> kcstudentList) ;

<update id="updateKcstudentByzcard" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" separator=";" >
      UPDATE kcstudent
      <set> passremark = #{item.passremark} </set>
      WHERE zcard = #{item.zcard}
    </foreach>
  </update>