Mybatis-Plus条件构造器练习题

一、Mybatis-Plus条件构造器练习题

1、名字中包含雨并且年龄小于40

name like ‘%雨%’ and age<40

2、名字中包含雨年并且龄大于等于20且小于等于40并且email不为空

name like ‘%雨%’ and age between 20 and 40 and email is not null

3、名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列

name like ‘王%’ or age>=25 order by age desc,id asc

queryWrapper.likeRight("name","王").ge("age",25).orderByDesc("age").orderByAsc("id");

4、创建日期为2019年2月14日并且直属上级为名字为王姓

date_format(create_time,’%Y-%m-%d’)=‘2019-02-14’ and manager_id in (select id from user where name like ‘王%’)

queryWrapper.apply("DATE_FORMAT(create_time,'%Y-%m-%d') = {0}", "2021-07-27");

5、名字为王姓并且（年龄小于40或邮箱不为空）

name like ‘王%’ and (age<40 or email is not null)

queryWrapper.likeRight("name","王").and(qw -> qw.lt("age", 40).or().isNotNull("email"));

6、名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）

name like ‘王%’ or (age<40 and age>20 and email is not null)

queryWrapper.likeRight("name","王").or(qw -> qw.lt("age",40).gt("age",20).isNotNull("email"));

7、（年龄小于40或邮箱不为空）并且名字为王姓

(age<40 or email is not null) and name like ‘王%’

queryWrapper.nested(qw -> qw.lt("age",40).or().isNotNull("email")).like("name","王");

总结：or()、and()、nested()可作为嵌套语句，其中nested()一般使用在嵌套语句在前面；and()是and (…)；or()是or(…)

8、年龄为30、31、34、35

age in (30、31、34、35)

queryWrapper.in("age",Arrays.asList(30,31,34,35));

9、只返回满足条件的其中一条语句即可

limit 1

queryWrapper.in("age",Arrays.asList(30,31,34,35)).last("limit 1");
//输出结果：
	SELECT id,name,age,create_time,update_time,sex FROM user WHERE (age IN (?,?,?,?)) limit 1

二、select中字段不全部出现的查询

10、名字中包含雨并且年龄小于40(需求1加强版)

第一种情况：select id,name
from user
where name like ‘%雨%’ and age<40

queryWrapper.select("id","name").like("name","雨").lt("age",40);

第二种情况：select id,name,age,email
from user
where name like ‘%雨%’ and age<40

queryWrapper.like("name","雨").lt("age",40).select(User.class,tableFieldInfo -> !tableFieldInfo.getColumn().equals("create_time")&&!tableFieldInfo.getColumn().equals("update_time")&&!tableFieldInfo.getColumn().equals("sex"));

三、统计查询：

11、按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。

并且只取年龄总和小于500的组。
select avg(age) avg_age,min(age) min_age,max(age) max_age
from user
group by manager_id
having sum(age) <500