Sql 日常笔记（不断更新。。。）

mybatis实现一个标签执行多条sql语句：

配置jdbc:关键代码allowMultiQueries=true
jdbc:mysql://localhost:3306/mes_?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true

Sql写法：
<delete id="deleteAllId" parameterType="Integer">
	delete from alarm_record where isdelete=#{id};
	delete from entrance_guard where isdelete=#{id};
	delete from fileinfo where isdelete=#{id};
	delete from filetype where isdelete=#{id};
	delete from robot_equipment where isdelete=#{id};
	delete from staff where isdelete=#{id};
</delete>

---

多对多查询：

SELECT A.a, C.c
  FROM A, B, C
 WHERE A.a = B.a AND B.b = C.b;

---

外连接：

select 
e.name,d.name 
from emp e left/rigth join dept d on e.deptno=d.deptno

---

多表查询：

select a.a1,a.a2,a.a3,b.b2,c.c2,d.d2
from a,b,c,d
where a.a1=b.b1 and b.b1=c.c1 and c.c1=d.d1
第二种是：
select a.a1,a.a2,a.a3,b.b2,c.c2,d.d2
from a inner join b on a.a1=b.b1
inner join c on b.b1=c.c1
inner join d on c.c1=d.d1

---

高效批量修改：

在写批量修改的语句时遇到一个很无语的错误嵌套异常；经过不断测试update 顶格写结果运行正常了

<update id="updateAll">
update ${formname}<trim suffixOverrides="end," prefix="set">
		<foreach collection="params.keys" item="key">
            ${key} = case id 
                 <foreach collection="list" item="is" index="index">
                         WHEN  #{is.id} then
                          <foreach collection="is.keys" item="maps" index="index">
                          <if test="key.toString==maps.toString">
                           #{is.${maps}}
                          </if>
                          </foreach>
                 </foreach>
                 end,
           </foreach>
             </trim>
           END WHERE id IN
           
           <foreach collection="list" separator="," open="(" close=")" item="i">
    					#{i.id}
    	    </foreach>
</update>

---