一、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