sql

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;