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;

转载于:https://www.cnblogs.com/chywx/p/6803076.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值