SQL常用的标签关键字

标签

1.insert

<insert id="insertTest" parameterType="java.util.HashMap">
	insert into table_test (field1,field2) values (value1,value2)
</insert>

2.delete

<delete id="deleteTest" parameterType="java.util.HashMap">
	delete from table_test where id="id"
</delete>

3.update

<update id="updateTest" parameterType="java.util.HashMap">
	update table_test set value1="valueNew1",value1="valueNew1" where id="id"
</update>

4.select

<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
	select field1,field2 from table_test
</select>

5.if

<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
	select field1,field2 from table_test 
	where 1=1
	<if test="value1!=null and test!=''">
		and field1 = #{value1}
	</if>
	<if test="value2!=null and test!=''">
		and field2 = #{value2}
	</if>
</select>

6.trim

<insert id="insertTest" parameterType="java.util.HashMap">
	insert into table_test 
	<trim prefix="(" suffix=")" suffixOverrides="," >
		<if test="value1!=null and test!=''">
			field1,
		</if>
		<if test="value2!=null and test!=''">
			field2,
		</if>
		<if test="value3!=null and test!=''">
			field2,
		</if>
	</trim>
	<trim prefix="values(" suffix=")" suffixOverrides="," >
		<if test="value1!=null and test!=''">
			#{value1},
		</if>
		<if test="value2!=null and test!=''">
			#{value2},
		</if>
		<if test="value3!=null and test!=''">
			#{value3},
		</if>
	</trim>
</insert>

7.include

<!-- 和sql标签一起用 -->
<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
	select 
	<include refid= "field"/>
	from
	<include refid= "table"/>
	where
	id=#{id}
</select>

8.sql

<!-- 和标签一起用 -->
<sql id ="field">
	field1,
	field2,
	field3
</sql>

<sql id ="table">
	table_test
</sql>

9.foreach

<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
	select field1,field2 from table_test 
	where 1=1
	<if test="value1!=null and test!=''">
		and field1 = #{value1}
	</if>
	<if test="value2!=null and test!=''">
		and field2 = #{value2}
	</if>
	<if test="idlist!=null and idlist!=''">
		and id in (
		<foreach collection="idlist" item="id" index="id" separator=",">
			#{id}
		</foreach>
		)
	</if>
</select>

10.choose-when-otherwise

<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
	select field1,field2 from table_test 
	where 1=1
	<if test="value1!=null and test!=''">
		and field1 = #{value1}
	</if>
	<if test="value2!=null and test!=''">
		and field2 = #{value2}
	</if>
	<choose>
		<when test="value3 !=null and value3 !=''">
			and field3=#{value3}
		</when>
		<otherwise>
			and field3 = #{value4}
		</otherwise>
	</choose> 
</select>

关键字

1.case when

<select id="selectTest" resultType="java.util.HashMap" parameterType="java.util.HashMap">
	select case when field1 is null then '1'
		   case when field2 is not null then '2'
		   else '3' end
		   from table_test where id = #{id}
</select>

2.to_char / to_date 日期类型转换
3.with table_temp 临时表使用
4.sum(filed1+field2)
5.merge into
6.instr(string,’’)>0 包含某个字符个数
7.substr(string,’[^]+’,2) 第一第二个*字符之间的字符串
8.start within connect by prior