【数据库基础|第4篇】where、order by、limit 条件查询详解

前言

上一篇我们学习了 SQL 的增删改查基础语法，其中查询数据使用的是 select。

但是在真实项目中，我们很少只是简单地查询整张表。

比如员工列表页面，常见需求可能是：

• 根据姓名模糊查询
• 根据性别筛选
• 根据入职日期范围查询
• 按照更新时间倒序排序
• 每页只显示 10 条数据

这时候就需要用到 SQL 查询中非常重要的三个关键字：

• where
• order by
• limit

这一篇我们就来完整梳理一下条件查询、排序查询和分页查询。

一、准备数据

继续使用上一章的员工表 emp。

create table emp (
id int unsigned primary key auto_increment comment '员工ID',
username varchar(20) not null unique comment '用户名',
name varchar(10) not null comment '姓名',
gender tinyint unsigned comment '性别：1男，2女',
phone varchar(11) comment '手机号',
salary decimal(10, 2) comment '工资',
entry_date date comment '入职日期',
create_time datetime comment '创建时间',
update_time datetime comment '修改时间'
) comment '员工表';

可以准备几条测试数据：

insert into emp(username, name, gender, phone, salary, entry_date, create_time, update_time)
values
('zhangsan', '张三', 1, '13800000000', 8000.00, '2024-03-01', now(), now()),
('lisi', '李四', 1, '13900000000', 9500.00, '2024-04-10', now(), now()),
('wangwu', '王五', 2, '13700000000', 7200.00, '2024-05-15', now(), now()),
('zhaoliu', '赵六', 1, '13600000000', 12000.00, '2024-06-01', now(), now()),
('xiaohong', '小红', 2, '13500000000', 8800.00, '2024-06-20', now(), now());

后面所有查询都基于这些数据来演示。

二、where 条件查询

where 用来筛选符合条件的数据。

基本语法：

select 字段列表
from 表名
where 条件;

1. 根据 id 查询

select id, username, name, gender, phone
from emp
where id = 1;

这条 SQL 表示查询 id 为 1 的员工。

这是最常见的精确查询。

2. 根据性别查询

select id, username, name, gender, phone
from emp
where gender = 1;

这条 SQL 表示查询所有男性员工。

3. 查询工资大于 9000 的员工

select id, name, salary
from emp
where salary > 9000;

这里使用了比较运算符 >。

常见比较运算符如下：

三、and 和 or 多条件查询

如果查询条件不止一个，就需要使用 and 或 or。

1. and：多个条件同时满足

select id, name, gender, salary
from emp
where gender = 1 and salary > 9000;

这条 SQL 表示查询：

性别为男，并且工资大于 9000 的员工

也就是说，两个条件必须同时满足。

2. or：满足任意一个条件即可

select id, name, gender, salary
from emp
where gender = 2 or salary > 10000;

这条 SQL 表示查询：

性别为女，或者工资大于 10000 的员工

只要满足其中一个条件，就会被查询出来。

3. and 和 or 混用时建议加括号

select id, name, gender, salary
from emp
where gender = 1 and (salary > 9000 or name = '张三');

当 and 和 or 一起出现时，最好使用括号明确优先级。

这样 SQL 更清楚，也不容易写错。

四、like 模糊查询

like 用于模糊匹配，常用于姓名、用户名、手机号等字段查询。

1. 查询姓名中包含“张”的员工

select id, username, name
from emp
where name like '%张%';

这里的 % 表示任意多个字符。

'%张%'：只要包含“张”就可以

2. 查询用户名以 z 开头的员工

select id, username, name
from emp
where username like 'z%';

'z%'：以 z 开头

3. 查询用户名以 n 结尾的员工

select id, username, name
from emp
where username like '%n';

'%n'：以 n 结尾

4. like 的常见写法

在后台管理系统中，姓名搜索通常使用：

where name like concat('%', #{name}, '%')

这也是 MyBatis 中非常常见的写法。

五、between 范围查询

between … and … 用来查询某个范围内的数据。

1. 查询工资在 8000 到 10000 之间的员工

select id, name, salary
from emp
where salary between 8000 and 10000;

这个范围包含 8000 和 10000。

也就是说，它相当于：

where salary >= 8000 and salary <= 10000

2. 查询入职日期范围

select id, name, entry_date
from emp
where entry_date between '2024-04-01' and '2024-06-01';

这条 SQL 表示查询入职日期在 2024-04-01 到 2024-06-01 之间的员工。

日期范围查询在项目中非常常见，比如：

• 查询某段时间内新增的员工
• 查询某段时间内创建的订单
• 查询某段时间内的操作日志

六、in 集合查询

in 用来判断字段值是否在某个集合中。

1. 查询 id 为 1、3、5 的员工

select id, name, phone
from emp
where id in (1, 3, 5);

这条 SQL 相当于：

where id = 1 or id = 3 or id = 5

但是使用 in 更简洁。

2. 批量删除中的 in

项目中批量删除时也经常使用 in：

delete from emp
where id in (1, 3, 5);

MyBatis 中通常会配合 <foreach> 动态生成：

<delete id="deleteByIds">
delete from emp
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete>

最终 SQL 就会变成：

delete from emp where id in (1, 3, 5);

七、is null 和 is not null

查询空值不能使用 = null，而要使用：

is null

或者：

is not null

1. 查询手机号为空的员工

select id, name, phone
from emp
where phone is null;

2. 查询手机号不为空的员工

select id, name, phone
from emp
where phone is not null;

3. 为什么不能写 = null？

错误写法：

select id, name, phone
from emp
where phone = null;

在 SQL 中，null 表示未知值，不能用普通等号判断。

所以判断空值必须使用：

is null

八、order by 排序查询

order by 用来对查询结果排序。

基本语法：

select 字段列表
from 表名
order by 字段名 排序方式;

排序方式有两种：

1. 按工资升序

select id, name, salary
from emp
order by salary asc;

工资从低到高排序。

asc 是默认值，也可以省略：

select id, name, salary
from emp
order by salary;

2. 按工资降序

select id, name, salary
from emp
order by salary desc;

工资从高到低排序。

3. 按更新时间倒序

select id, name, update_time
from emp
order by update_time desc;

后台列表页面通常会按照更新时间倒序排列。

因为最新修改的数据一般希望排在前面。

九、多字段排序

如果第一个排序字段相同，可以继续按第二个字段排序。

select id, name, gender, salary
from emp
order by gender asc, salary desc;

这条 SQL 表示：

多字段排序在项目中也很常见。

比如：

order by create_time desc, id desc

表示先按创建时间倒序，如果创建时间一样，再按 id 倒序。

十、limit 分页查询

limit 用来限制查询返回的数据条数。

1. 查询前 3 条数据

select id, name, salary
from emp
limit 3;

表示只查询前 3 条记录。

2. 查询第 1 页，每页 2 条

select id, name, salary
from emp
limit 0, 2;

这里：

0 表示从第几条开始
2 表示查询几条

注意：起始位置从 0 开始。

3. 查询第 2 页，每页 2 条

select id, name, salary
from emp
limit 2, 2;

表示从第 3 条数据开始，查询 2 条。

4. 查询第 3 页，每页 2 条

select id, name, salary
from emp
limit 4, 2;

分页公式是：

起始索引 = (页码 – 1) * 每页条数

比如第 3 页，每页 2 条：

(3 – 1) * 2 = 4

所以 SQL 是：

limit 4, 2

十一、where、order by、limit 一起使用

真实项目中，它们经常一起使用。

比如查询男性员工，按照工资降序，每页查询 10 条：

select id, username, name, gender, phone, salary
from emp
where gender = 1
order by salary desc
limit 0, 10;

再比如姓名模糊查询，并按更新时间倒序：

select id, username, name, gender, phone, update_time
from emp
where name like '%张%'
order by update_time desc
limit 0, 10;

SQL 书写顺序一般是：

select 字段列表
from 表名
where 条件
order by 排序字段
limit 起始索引, 查询条数;

顺序不要写乱。

十二、对应到 Spring Boot + MyBatis 项目

在项目中，分页条件查询一般会写成这样。

1. Controller 层

@GetMapping
public Result page(
@RequestParam(defaultValue = "1") Integer page,
@RequestParam(defaultValue = "10") Integer pageSize,
String name,
Integer gender) {

PageBean pageBean = empService.page(page, pageSize, name, gender);
return Result.success(pageBean);
}

2. Service 层

@Override
public PageBean page(Integer page, Integer pageSize, String name, Integer gender) {
Integer start = (page – 1) * pageSize;

List<Emp> rows = empMapper.page(start, pageSize, name, gender);
Long total = empMapper.count(name, gender);

return new PageBean(total, rows);
}

3. Mapper 层

@Select("select id, username, name, gender, phone, salary, update_time " +
"from emp " +
"where name like concat('%', #{name}, '%') " +
"and gender = #{gender} " +
"order by update_time desc " +
"limit #{start}, #{pageSize}")
List<Emp> page(Integer start, Integer pageSize, String name, Integer gender);

这个写法能表达分页查询的基本思想。

但是它还有问题：如果 name 或 gender 为空，SQL 就不够灵活。

所以真实项目中更常用 MyBatis 动态 SQL：

<select id="page" resultType="com.example.pojo.Emp">
select id, username, name, gender, phone, salary, update_time
from emp
<where>
<if test="name != null and name != ''">
name like concat('%', #{name}, '%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
</where>
order by update_time desc
limit #{start}, #{pageSize}
</select>

这样就可以根据前端是否传参动态拼接查询条件。

十三、常见问题总结

1. where 必须写在 order by 前面吗？

是的。

正确顺序：

select ...
from ...
where ...
order by ...
limit ...

错误顺序：

select ...
from ...
order by ...
where ...

2. limit 的起始值为什么从 0 开始？

MySQL 中 limit start, size 的 start 是起始索引，从 0 开始。

第 1 页的起始索引是 0。

3. like 查询为什么要加 %？

因为 % 表示任意多个字符。

如果写：

where name like '张'

它更接近精确匹配。

如果想查询包含“张”的姓名，要写：

where name like '%张%'

4. 判断 null 能不能用 =？

不能。

判断空值要使用：

is null

判断非空要使用：

is not null

5. order by 可以按多个字段排序吗？

可以。

比如：

order by update_time desc, id desc

表示先按更新时间倒序，再按 id 倒序。

十四、实际开发建议

写条件查询时，建议注意下面几点：

十五、总结

这一篇主要学习了 SQL 查询中非常重要的三个关键字：where、order by、limit。

where 用来筛选数据，可以配合 and、or、like、between、in、is null 完成各种条件查询。

order by 用来排序，既可以升序，也可以降序，还可以按多个字段排序。

limit 用来分页，核心公式是：

起始索引 = (页码 – 1) * 每页条数

这些内容在后台管理系统中非常常见。比如员工列表、订单列表、文章列表、日志列表，基本都离不开条件查询、排序和分页。

下一篇我们继续学习聚合函数和分组查询，也就是 count、sum、avg、max、min 以及 group by 的使用。