mybatis sql 实用

1.动态sql

<!--
        Mybatis:动态sql语法 根据业务条件,动态拼接Sql
        语法:
              1. if标签: 如果判断为真 则拼接sql
              2. where标签: 去除where后边多余1个and/or
    -->
    <select id="findUser" resultType="User">
        select * from demo_user
            <where>
                <if test="id !=null">id = #{id}</if>
                <if test="name !=null">and name = #{name}</if>
                <if test="age !=null">and age = #{age}</if>
                <if test="sex !=null">and sex = #{sex}</if>
            </where>
    </select>
	
	<!--
        使用动态sql:根据对象中不为null的属性当做set条件
        标签说明: set标签在修改操作中去除1个多余的,号
    -->
    <update id="updateUser">
        update demo_user
            <set>
                <if test="name !=null">name=#{name},</if>
                <if test="age !=null">age=#{age},</if>
                <if test="sex !=null">sex=#{sex}</if>
            </set>
            where id=#{id}
    </update>
	
	
	<!-- 说明: 如果不想使用所有的条件可以使用choose 类似于java中的switch 语法
		 如果name有值,则按照name查询, if
		 如果age有值,则按照age查询, else-if
		 否则按照sex查询数据. else 
	-->
    <select id="findUserChoose" resultType="User">
        select * from demo_user where
            <choose>
                <when test="name !=null">name=#{name}</when>
                <when test="age  !=null">age=#{age}</when>
                <otherwise>sex = #{sex}</otherwise>
            </choose>
    </select>

2.查询今天,昨天,近七天,本月,上月的记录

	1、查询今天的所有记录：
	select * from visitors where to_days(createtime) = to_days(now());  

	2、查询昨天的所有记录
	select * from visitors where to_days(now()) = 1 + to_days(createtime);

	3、近7天的信息记录：
	select * from visitors where date_sub(curdate(), INTERVAL 7 DAY) < date(createtime);

	4、近30天的信息记录：
	select * from visitors where date_sub(curdate(), INTERVAL 30 DAY) < date(createtime);

	5、查询本月的记录：
	select * from visitors where date_format(createtime, '%Y%m') = date_format(curdate() , '%Y%m');

	6、上一个月的记录：
	select * from visitors where period_diff(date_format(now() , '%Y%m') , date_format(createtime, '%Y%m')) =1;

3.排序

	sql:select *  from tablename  order by filename1 desc;
	解释：意思是根据”filename1字段“排序，倒叙输出tablename表中的数据。
	备注：asc是表示升序，desc表示降序
	
	//先按照sort排序，再按照createtime排序
	Order  by sort  desc ,createtime desc 

4.去重

	//distinct：去重
	select distinct tel from visitors  where c_id=2
	
	select * ,count(distinct tel)  from visitors  where c_id=2  group by tel

5.case when 用法

	//根据quarters分组，查询reasons为学生的记录分别有多少条：
	select c.quarters,sum(case when v.reasons="学生" then 1 else 0 end)
		from visitors v left join h_community c on v.c_id=c.id  group by c.quarters

	update  employee
　　　　set    e_wage =
　　　　case
　　　　when   job_level = '1'    then e_wage*1.97
　　　　when   job_level = '2'   then e_wage*1.07
　　　　when   job_level = '3'   then e_wage*1.06
　　　　else     e_wage*1.05
　　　　end

6.mybatis中大于小于等于

	大于：>
	小于：<
	大于等于：>=
	小于等于：<=

7.指定查询(第几条开始,查询多少行) limit

	//学生是重庆的前五条数据,0:从第一条开始(不包括0)，5:5条数据
	select * from student where region="重庆"  limit 0 , 5

8.mysql字段内容后添加数据

	//mysql字段内容后添加数据
	UPDATE USER SET username= CONCAT(username,',xxx')  WHERE id= 1

效果图：

9.mysql新增一条数据后返回主键id

	/**
	 * parameterType:对应实体类
	 * useGeneratedKeys="true"属性设置该insert语句返回主键值 
	 * keyProperty:实体类对应的id
	 * keyColumn:数据库对应的主键id
	 */
	<insert id="aaa"  parameterType="User" useGeneratedKeys="true"  keyProperty="uId"  keyColumn="u_id">
        insert into user(name,tel,sex)  value(#{name},#{tel},#{sex}) 
    </insert>

10.查询id=1,2,3,4的数据

List集合方法：
controller层

    @RequestMapping("/tsetin")
    public List<User> findUserByIn(){
        Integer[] array = {1,2,3,4};
        List<User> list = userMapper.findUserByIn(array);
        return list;
    }

mapper层

List<User> findUserByIn(Integer[] array);

xml文件

<!--
        Mybatis集合操作
            1."集合"的类型: array/list/Map集合
            2. 目的:从集合中获取其中的数据. 一个一个取值
            3. 方案: Mybatis提供了循环遍历的方式
            4. 语法:
                    4.1collection需要遍历的数据
                        数组:     collection="array"
                        List集合: collection="list"
                        Map<key,array/list>集合:  collection="map中的key"
                    4.2 open/close  开始和接收标签
                    4.3 item: 定义取值的变量  通过#{xx}获取数据
                    4.4 separator: 分割符
    -->
    <select id="findUserByIn" resultType="User">
        select * from user where id in
        <foreach collection="array" open="(" close=")"
                 item="id" separator=",">
            #{id}
        </foreach>
    </select>

Map集合方法：
controller层

@RequestMapping("/tsetmap")
    public List<User> findUserByInMap(){
        Integer[] array = {1,2,3,4};
        Map map = new HashMap();
        map.put("ids",array);
        List<User> list = userMapper.findUserByInMap(map);
        return list;
    }

mapper层

 List<User> findUserByInMap(Map map);

xml文件

<!--Map数据封装array集合的写法 -->
    <select id="findUserByInMap" resultType="User">
        select * from user where id in (
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </select>