sql小语句总结（自娱自乐）
简单的sqlwhereif查询
<select id="getListByWhere" parameterType="com.jiyun.beans.Employee" resultMap="haha">
select * from employee
<where>
<if test="id != null and id != ''">
and id = #{id}
</if>
<if test="name != null and name != ''">
and emp_name = #{name}
</if>
</where>
</select>
sql---foreach语句
<select id="getListByForeach" parameterType="list" resultMap="haha">
select * from employee where id in
<foreach collection="list" open="(" close=")" item="a" separator=",">
#{a}
</foreach>
</select>
sql---片段
<sql id="single">
where id=#{id};
</sql>
<select id="getEmployee" resultMap="haha" parameterType="int">
select * from employee
<include refid="single"></include>
</select>
sql---修改trim字段（功能最为强大）
<update id="updateByTrim" parameterType="com.jiyun.beans.Employee">
update employee
<trim prefix="set" suffixOverrides=",">
<if test="name != null and name != ''">
emp_name = #{name},
</if>
<if test="age != null and age != ''">
emp_age = #{age},
</if>
</trim>
where id=#{id};
</update>
<insert id="insertByTrim" parameterType="com.jiyun.beans.Employee">
insert into employee
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null and name != ''">
emp_name,
</if>
<if test="age != null and age != ''">
emp_age,
</if>
</trim>
<trim prefix="values(" suffix=")" suffixOverrides=",">
<if test="name != null and name != ''">
#{name},
</if>
<if test="age != null and age != ''">
#{age},
</if>
</trim>
</insert>
sql语句一对多系列（说白了可以将代码复制一下）
<mapper namespace="com.jiyun.mapper.DeptMapper">
<!-- 使用resultMap进行映射 -->
<resultMap type="com.jiyun.beans.Dept" id="haha">
<id property="id" column="id"/>
<result property="deptName" column="dept_name"/>
<!-- 要表示一对多，需要用到collection标签 -->
<!-- property是集合的名字，ofType是集合元素的泛型
column把dept和employee关联起来，column表示用1的一端哪个列去关联多的一端
select写一条额外的sql语句,用某个mapper接口的方法的全类名表示-->
<collection property="list" ofType="com.jiyun.beans.Staff" column="id" select="com.jiyun.mapper.StaffMapper.getStaffByDeptId"></collection>
</resultMap>
<!-- 根据id查询一个部门 -->
<select id="getDeptById" parameterType="int" resultMap="haha">
select * from dept where id = #{id};
</select>
</mapper>
Select * From offence_bean Where DATE_FORMAT(new_date,'%m-%d') >= '04-20' and DATE_FORMAT(new_date,'%m-%d') <= '04-21'
select * from offence_bean where offence_city='运城' and new_date<'2018-1-20' and new_date>'2007-3-30'
select q.* from (SELECT a.*,money as m FROM (SELECT * FROM customers ORDER BY money DESC)a)q where m = 666;
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
