MySql 笔记(基于注解开发)
1,动态SQL,实现不同的条件情况下,执行不同的SQL操作
@Select({"<script>",
"select vi.*,ci.*,co.user_name as counselorName,de.user_name as dealName " +
"from sys_visa vi " +
"left join sys_client ci on vi.client_id = ci.client_id " +
"left join sys_user co on vi.counselor_id = co.user_id " +
"left join sys_user de on vi.deal_id = de.user_id ",
"where 1 = 1",
"<if test='counselor_id!=null'>",
"and vi.counselor_id = #{counselor_id}",
"</if>",
"<if test = 'deal_id!=null'>",
"and vi.deal_id = #{deal_id}",
"</if>",
"<if test = 'inquiry_state!=null'>",
"and vi.inquiry_state = #{inquiry_state}",
"</if>",
"</script>"})
2,可以在SQL中执行运算操作,使用fiormat关键字,来设置小数点后保留多少位
format((t2.chenggong/t2.zongxundan),2) as success_rate #此句的含义就是,相除运算之后保留2位小数
3,在子查询SQL中可以使用count( case 字段名 when 条件值1 then 条件值2 end)统计一个字段出现的次数
count(CASE t1.inquiry_state WHEN 1 THEN 1 END) as success_count
4,当SQL中涉及到对日期的筛选时,可以使用date_format(字段名,‘format’ )格式化处理日期格式
date_format( birthday ,' %Y-%m-%d ')>= #{birthday}
DATE_FORMAT(sale_date,'%Y%m')
5, 注解开发,insert一条数据之后,MySQL默认返回的int 数字是:执行该条SQL语句之后,数据库中被改动的行数,
但是如果我们想要知道,我添加了一条数据,这条数据在表格中的Id .就可以按照如下操作.
/**
* 添加一条可复用日程
*/
@Insert("insert into sys_schedule_unRepeat(schedule_id,schedule_title,point_a,point_a_hotel,point_a_restaurant,point_a_action," +
"point_a_scenic,point_b,point_b_hotel,point_b_restaurant,point_b_action,point_b_scenic,traffic,remarks,tag) " +
"values(#{schedule_id},#{schedule_title},#{point_a},#{point_a_hotel},#{point_a_restaurant},#{point_a_action},#{point_a_scenic}," +
"#{point_b},#{point_b_hotel},#{point_b_restaurant},#{point_b_action},#{point_b_scenic},#{traffic},#{remarks},#{tag}) ")
@Options(useGeneratedKeys = true, keyProperty = "schedule_id") //如果我们已经在数据库表中指定了主键,那么keyColumn属性可以缺省。
int insertSchedule(Sys_unRepeatSchedule unRepeatSchedule);
6.当某一列的值全部为null值时,count(col) 的值是0,但是sum(col)的值是null,因此会出现空指针异常,可以
使用ISNULL()解决:
select if(ISNULL(sum(col)) , 0, sum(col))