Mysq Sql

1、  当取出数据为空时，就会返回null 但后面的操作就会返回 0 ，避免空指针异常 ！ ifnull(sum(show_times),0)  

<select id="selectShow_times" parameterType="java.util.Map" resultType="double">
select     ifnull(sum(show_times),0)     from tablename
where name= #{name} 
     <if test="date != null "> and log_date >= #{date}</if> 
</select>

2、增加

<insert id="insert" parameterType="com.UserEty">
insert into user(
id,username,age
)
values (
#{id},#{username},#{age}
)
</insert>

3、删除：Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段，做计数重置归零重新计算的作用。

<delete id="deleteById" parameterType="int">
DELETE  FROM user    WHERE id=#{id}
</delete>

或

<delete id="truncateOldData">
truncate table crm_stat_adresource_amount_template;
</delete>

4、修改

<update id="updateById" parameterType="com.UserEty">
UPDATE user SET
<if test="id != null">id=#{id},</if>
<if test="order_number != null">order_number=#{order_number},</if>
<if test="type != null">type=#{type},</if>
<if test="statTime != null">statTime=#{statTime},</if>
id=#{id}
WHERE      id=#{id}
</update>

5、查询.

<select id="searchUserList" parameterType="com.UserForm" resultType="com.UserEty">
select  *
from user
WHERE statTime>=#{startTime} and 

                                statTime<![CDATA[<=]]>#{endTime} 

<if test="extLimit == null or extLimit.sort == null or extLimit.sort.toString().trim().equals('')"> ORDER BY type DESC</if>
<if test="extLimit != null">
<if test="extLimit.sort != null and extLimit.sort != null and !extLimit.sort.toString().trim().equals('')"> order by ${extLimit.sort} ${extLimit.dir}</if>
<if test="extLimit.limit != null"> limit ${extLimit.start}, ${extLimit.limit}</if>
</if>
</select>