Thinkphp5.0模型和数据库 第三章：查询构造器

本章主要来学习和使用查询构造器的用法，掌握查询构造器对于掌握数据库和模型的查询操作非常关键，学习内容主要包含：

• 创建查询类
• 数据库CURD操作
• 使用链式方法
• 查询语言
• 总结

在第一章我们已经学习了如何使用原生查询，不过原生查询的话就失去了数据库抽象访问层的意义了，所以数据库抽象访问层的优势就是使用查询构造器进行查询。

查询构造器就是利用查询类和生成类完成最终的查询语句构造（注意这里的查询是一个泛指，包括数据库的读写操作）。由于生成类是由查询类自动调用的，可以说查询构造器的用法其实就是查询类的用法，因此首先我们必须清楚查询类怎么调用或者说是在什么时候调用。

为了方便学习和掌握数据库的用法，我们建议在开始学习后面的内容之前，进行如下设置。

应用配置文件中开启调试模式和页面Trace，以及修改日志配置为error和sql日志单独记录（便于我们查错和进行SQL分析）：

    // 开启应用调试模式
    'app_debug'              => true,
    // 开启应用Trace
    'app_trace'              => true,   
    'log'                    => [
        // 日志记录方式
        'type'  => 'File',
        // error和sql日志单独记录
        'apart_level'   =>  ['error','sql'],
    ],

然后在数据库配置文件开启数据库调试模式

    // 数据库调试模式
    'debug'       => true,

开启数据库调试模式和页面Trace的目的是为了便于在页面直观的看到当前请求的数据库连接情况和执行的SQL语句，另外所有的历史查询SQL都可以在runtime\log\当前日期\sql.log中查看，本书不打算给出每个查询用法的最终SQL语句，最好的学习方式是亲自验证每个查询的SQL语句是什么。

本书后面的示例代码大多数情况不会写完整的控制器文件和方法（这不是本书的重点），只是局部关键代码的实现，也假设你在调用Db类方法之前已经使用use引入了think\Db类，请确保你已经掌握控制器的用法以及如何访问和测试（如果不清楚，请参考官方快速入门第三部控制器从入门到精通）。

创建查询类

框架内置的查询类就是think\db\Query类，使用查询构造器一般都是自动实例化查询类，无需手动实例化。通过第一章的学习我们已经知道调用Db类的任何方法都会自动调用connect方法返回连接对象实例，然后调用连接对象的查询构造器方法会自动实例化查询类。

以查询构造器的table方法调用为例，如果用下面方式调用：

// 调用Db类的table方法
Db::table('data');

实际解析过程相当于调用连接类的table方法：

// 先实例化连接类然后调用table方法
Db::connect()->table('data');

然后继续调用连接类的getQuery方法（该方法中会自动实例化查询类）后再调用查询类的table方法：

// 先实例化连接类然后获取查询类实例，调用查询类的table方法
Db::connect()->getQuery()->table('data');

虽然实际的调用过程如上所示，但内部的调用过程被数据访问层封装并衔接了，你只需要知道并使用下面的调用方法（除非你需要动态切换数据库连接则需要调用connect方法先）：

// 调用Db类的table方法
Db::table('data');

Db类可以直接调用查询类的方法，数据访问层的细分只是一种内部的架构设计，对于开发者来说，你只需要明白一件事：数据库操作就是用Db类，至于内部怎么相互调用完全不需要操心。

【5.1须知】

---

5.1 版本则简化了调用流程，Db的静态方法调用直接就是调用的查询类的方法。也就是说，上面的方法其实就是调用了Query类的table方法，而没有中间过程。

事实上，系统还额外提供了一个助手函数db用于完成上述方法，所以

$db = db('data');

和前面的用法等效，但是——有一点必须引起重视。

db助手函数默认每次调用都会重新连接数据库（目的是确保你的每次db函数调用不会相互影响），你可以使用db('data',[],false)方式解决。（好消息是V5.0.9+版本已经完美解决，无需这样调用了）

了解了查询类的调用机制后，后面我们就要逐步来讲解查询类的各种查询方法了，查询器类内置了大量的查询方法用于构建查询，如果你仍然觉得不够用，可以自己扩展然后使用下面的方式配置调用。

如果你自定义或者扩展了核心的查询类，那么可以在数据库配置文件中设置：

// 定义数据库的查询类
'query'           => '\\app\\db\\Query',

定义后，查询构造器就会自动调用app\db\Query查询类（一般来说会继承核心的think\db\Query类）。

数据库CURD操作

使用查询构造器进行查询，起码需要掌握查询类的几个关键的方法：

查询方法	作用描述
table	指定查询数据表
field	指定查询字段
where	指定查询条件
order	指定结果排序
limit	指定查询结果数
find	查询一条记录
select	查询数据集
insert	写入数据
update	更新数据
delete	删除数据

这些方法对SQL稍微了解一点的用户理解起来应该不难，find和select方法的区别在于find方法只是查询一条记录（即使满足条件的记录有很多），并且返回的是一个一维数组（没有结果返回Null），而select方法返回的是一个二维数组（没有结果返回空数组），除此之外，他们的查询语法都是相同的。

这些常用方法其实包含两种大的类别，一个是辅助方法（辅助查询用的，也称为链式方法，例如table、field、where、order和limit等方法），一个是真正的查询方法（find、select、insert、update和delete方法），查询方法是必须的，而辅助方法是可选的，并且辅助方法必须在查询方法之前被调用，并且在查询调用之后自动失效。

【5.1须知】

---

5.1版本每次查询完成后链式方法的条件参数会保留。

关于table方法这里作出一个特别的说明，在新版框架的架构设计规范中，我们建议数据表的命名不使用前缀设计，表前缀其实已经是一种过时的设计了，很多时候跨库的设计比表前缀的设计来的更灵活和实用，而且前缀设计（尤其是在混合用的情况下）带来的一些困惑和问题却是很多新手最大的苦恼，所以何必自寻烦恼（如果你一定要采用前缀设计，那么请用name方法替代table方法，并且在数据库配置文件中配置prefix参数，我也不拦着你，哭的时候别找我^_）。

使用查询构造器进行查询，能够最大程度的避免写针对特定数据库的查询语句，减少跨数据库类型的迁移成本。

下面我们对数据库的CURD操作给出基本的用法。

创建（Create）

创建操作指往数据表添加新的记录，下面是示例代码：

// 插入单个记录
Db::table('data')
    ->insert(['id' => 8, 'name' => 'thinkphp']);

// 插入多个记录
Db::table('data')
    ->insertAll([
        ['id' => 9, 'name' => 'thinkphp'],
        ['id' => 10, 'name' => 'topthink'],
    ]);

insertAll方法的数据集中的元素请确保字段列表一致，否则会出错。

由于insert和insertAll方法最终都是调用连接类的execute方法，我们已经知道execute方法的返回值是影响的记录数，所以insert和insertAll方法的返回值也是影响（新增）的记录数，并不会返回主键值。

主键id如果是自增类型，可以使用：

// 插入单个记录
Db::table('data')
    ->insert(['name' => 'kancloud']);

如果需要获取自增id的值，可以在insert方法之后紧接着调用getLastInsID方法：

// 插入单个记录
Db::table('data')
    ->insert(['name' => 'kancloud']);
// 获取上次写入的自增Id
$id = Db::getLastInsID();

由于PDO内部的原因，insertAll方法后调用getLastInsID方法返回的自增Id可能存在偏差。

或者直接合并上面的代码为：

// 插入单个记录 并返回自增Id
$id = Db::table('data')
    ->insertGetId(['name' => 'kancloud']);

对于不在数据表中的字段写入，系统默认会抛出异常，但可以配合strict(false)方法忽略错误继续执行，下面的test数据会被忽略（data表不存在test字段）。

// 插入单个记录 并返回自增Id
$id = Db::table('data')
    ->strict(false)
    ->insertGetId([
        'name' => 'kancloud',
        'email' =>  'kancloud@qq.com',
        'test'  => '这个数据不会被写入',
    ]);

你并不需要考虑写入数据失败的情况，数据库操作过程有任何的错误都会抛出异常，你需要做的只是修正BUG或者捕获异常自行处理。

更新（Update）

更新操作指更改数据表记录的单个或者多个字段，下面是示例代码：

// 更新记录
Db::table('data')
    ->where('id', 8)
    ->update(['name' => "framework"]);

出于数据安全考虑，ThinkPHP的update方法必须使用更新条件而不允许无条件更新，如果没有指定更新条件，则会从更新数据中获取主键作为更新条件，例如当id是主键的时候下面的写法依然有效：

// 更新记录
Db::table('data')
    ->update(['id' => 8, 'name' => "framework"]);

可以过滤需要更新的字段列表，例如只允许更新name字段的值（假设data表还存在email字段）

// 更新记录
Db::table('data')
    ->field(['name'])
    ->where('id', 8)
    ->update([
            'name' => 'framework', 
            'email' => 'thinkphp@qq.com'
        ]);

实际更新的字段只有name，email字段的数据会被忽略。

一般来说，update方法用于更新数据的多个字段，如果只是更新某个字段的值，也可以用setField方法，例如：

// 更新记录
Db::table('data')
    ->where('id', 8)
    ->setField('name','framework');

返回值和update方法一致，因为setField最终也是调用的update方法。

对于数字类型的字段的步长更新，框架提供了两个专门的方法用于递增和递减操作。

递增操作：

// score 字段加 1
Db::table('user')
    ->where('id', 1)
    ->setInc('score');
// score 字段加 5
Db::table('user')
    ->where('id', 1)
    ->setInc('score', 5);

递减操作：

// score 字段减 1
Db::table('user')
    ->where('id', 1)
    ->setDec('score');
// score 字段减 5
Db::table('user')
    ->where('id', 1)
    ->setDec('score', 5);

setInc/setDec支持延时写入，延时写入的含义是会把需要递增/递减的数据缓存起来（在缓存中进行递增和递减操作），在达到指定的时间计时后才会把最终计算的缓存数据写入数据库，避免频繁操作数据库带来的性能开销，下例中延时10秒，给score字段增加1：

Db::table('user')
    ->where('id', 1)
    ->setInc('score', 1, 10);

setInc和setDec可以同时使用延时写入，系统会自动计算最终需要写入数据库的值。

读取（Read）

读取操作就是指对数据表（包括单表和多表）的各种查询操作，该操作涉及的内容和营养较多，也是数据库操作中最复杂和最难掌握的，所以我们会陆续花大量的篇幅来讲解，这里先简单了解下几个单表查询最基础的方法，用法示例：

查询单个数据：

// 查询查个数据
$data = Db::table('data')
    ->where('id', 8)
    ->find();
dump($data);
// 简化写法，直接传入主键查询
$data = Db::table('data')
    ->find(8);

find方法存在查询结果的话返回一个数组，没有的话返回null，要获取查询记录的值，可以使用数组方式操作：

echo $data['id'];
echo $data['name'];

查询多个数据：

// 查询多个数据
$list = Db::table('data')
    ->where('id','in', [1, 5, 8])
    ->select();
dump($list);
// 直接传入多个主键的值查询
$list = Db::table('data')
    ->select([1,5,8]);

select方法存在查询结果的话返回一个二维数组，如果没有数据则返回一个空数组。

可以遍历获取记录的值，例如：

foreach ($list as $data) {
    echo $data['name'];
}

我们修改数据库配置文件中的resultset_type值

    // 数据集返回类型
    'resultset_type'  => 'collection',

select方法的返回值就会变成一个数据集对象（think\Collection）。

二维数组和数据集对象的区别在于，数据集对象提供了更多的内置数据处理方法，但在基本使用上，这两种方式没有不同，很多开发者一看到对象的输出信息就不知所措了，完全没必要，除非你有“对象”恐惧症，关于数据集的用法我们会在后续进行详细讲解。

一个特殊的情况是，使用分页查询方法的话，无论数据库配置返回数据集类型设置什么，都会返回分页类think\Paginator的对象实例，用法基本上和数据集对象一致。

5.1版本和5.0的一个关键的区别，在于5.1版本开始，每次查询完成后不会清空查询条件，但每次Db类的静态调用都是一个全新的查询。

获取记录的某个字段值：

// 返回某个字段的值
Db::table('data')
    ->where('id', 1)
    ->value('name');

获取记录某个列的值：

// 获取name列的数组
Db::table('data')
    ->where('status', 1)
    ->column('name');

// 指定索引
Db::table('data')
    ->where('status', 1)
    ->column('name', 'id');

column的返回类型永远都是数组，不受resultset_type参数的影响。

你可能现在会对where方法的用法心存疑虑，确实查询构造器大部分的工作和复杂度都集中在where方法，不过不用着急，请耐心往下看，在学习完本章和下一章的内容后你基本上就会对where方法驾轻就熟了。

删除（Delete）

删除操作指对数据表的单个记录或者多个记录的删除操作，示例代码如下：

// 删除数据
Db::name('data')
    ->where('id', 18)
    ->delete();

和更新操作一样，ThinkPHP不允许使用无条件删除操作，如果不带条件可以直接使用主键删除：

// 删除数据
Db::name('data')
    ->delete(18);
// 删除多条数据
Db::name('data')
    ->delete([1, 5, 8]);

如果你确定要执行无条件删除操作， 可以使用下面的方式：

// 删除所有数据
Db::name('data')
    ->delete(true);

事实上，对于业务数据表，基本上不建议使用删除操作，而是使用软删除（逻辑删除，其实执行的是数据表的更新操作）替代实际的物理删除。软删除属于模型的功能设计，我们会在后面的章节给你讲解。

使用链式方法

掌握了基本的CURD操作后，我们就来熟悉下链式方法的概念，其实就是前面我们提到的辅助查询方法。首先链式方法的目的是为了让查询更清晰和直观，下面的两个代码实现哪个更清晰易懂大家可以比较下。

常规的方法实现：

Db::table('data');
Db::where('id', '>', 1);
Db::limit(8);
$list = Db::select();

事实上，如果在5.1版本中，上面的用法是无效的。

使用链式方法实现：

$list = Db::table('data')
    ->where('id', '>', 1)
    ->limit(8)
    ->select();

事实上，我们前面使用的table、where、limit之类的方法都称之为链式方法，辨别一个方法是否属于链式方法的一个显著特征就是看这个方法是否返回当前的对象实例。

正因为链式操作方法返回的是当前对象实例，所以不同的链式方法在调用的顺序上没有先后的概念，但相同的链式方法调用顺序会影响最终的查询，下面的两个例子完全等效：

$list = Db::table('data')
    ->where('id', '>', 1)
    ->limit(8)
    ->select();

和

$list = Db::limit(8)
    ->where('id', '>', 1)
    ->table('data')
    ->select();

上面两个查询最终生成的SQL语句是完全相同的。

但下面两个查询最终生成的SQL是不同的

$list = Db::table('data')
    ->where('id', '>', 1)
    ->where('name', 'like', '%think%')
    ->order('id', 'desc')
    ->order('create_time', 'desc')
    ->limit(8)
    ->select();

和

$list = Db::table('data')
    ->where('name', 'like', '%think%')
    ->where('id', '>', 1)
    ->order('create_time', 'desc')
    ->order('id', 'desc')
    ->limit(8)
    ->select();

多个where方法和order方法的调用顺序最终影响了生成的SQL语句，虽然有时候并不会影响查询结果。上面的两个例子where方法的顺序并不会影响查询条件，而order方法的顺序则改变了最终数据的排序。

链式方法的调用顺序取决于你的思维习惯或者说团队规范。

查询类的大部分方法都是采用链式方法实现，给你的CURD查询带来便利。

查询类的所有链式方法调用都不具备记忆性，每次查询操作完成后几乎所有的链式方法带来的影响将不复存在，简单来说就是链式方法的结果不会带入后面的其它查询。

系统支持的链式操作方法有：

连贯操作	作用	支持的参数类型
where*	用于AND查询	字符串、数组和对象
whereOr*	用于OR查询	字符串、数组和对象
whereXor*	用于XOR查询	字符串、数组和对象
whereTime*	用于时间日期的快捷查询	字符串
table	用于定义要操作的数据表名称	字符串和数组
alias	用于给当前数据表定义别名	字符串和数组
field*	用于定义要查询的字段（支持字段排除）	字符串和数组
order*	用于对结果排序	字符串和数组
limit	用于限制查询结果数量	字符串和数字
page	用于查询分页（内部会转换成limit）	字符串和数字
group	用于对查询的group支持	字符串
having	用于对查询的having支持	字符串
join*	用于对查询的join支持	字符串和数组
union*	用于对查询的union支持	字符串、数组和对象
view*	用于视图查询	字符串、数组
distinct	用于查询的distinct支持	布尔值
lock	用于数据库的锁机制	布尔值
cache	用于查询缓存	支持多个参数
relation*	用于关联查询	字符串
with*	用于关联预载入	字符串、数组
bind*	用于数据绑定操作	数组或多个参数
comment	用于SQL注释	字符串
force	用于数据集的强制索引	字符串
master	用于设置主服务器读取数据	布尔值
strict	用于设置是否严格检测字段名是否存在	布尔值
sequence	用于设置Pgsql的自增序列名	字符串
failException	用于设置没有查询到数据是否抛出异常	布尔值
partition	用于设置分表信息	数组 字符串
data*	用于设置写入数据（5.0.5+）	数组
inc*	用于设置字段递增 （5.0.5+）	字符串
dec*	用于设置字段递减（5.0.5+）	字符串
exp*	用于设置SQL表达式（5.0.5+）	字符串

其中带*标识的表示支持多次调用。

鉴于链式方法有很多，而且手册也有详细的描述（完全开发手册对链式方法给出了详细的解释，可以参阅），这里想声明几点，避免产生困惑：

• 链式方法支持所有的CURD操作；
• 链式方法本身只是返回查询对象，只有执行查询后才会返回结果，而且只能在查询方法之前被调用；
• 不同链式方法的调用顺序不影响查询；
• 相同链式方法的调用顺序可能会影响查询（至少会影响SQL语句）
• 链式方法在完成查询后会自动失效；
• 同一个链式方法在CURD操作中的作用可能不同；
• 链式方法仅针对CURD方法，对原生查询无效；

一个典型的例子就是field方法，在查询和写入操作中的代表的作用完全不同。

查询语言

在所有的链式方法中，最复杂的莫过于查询条件方法以及和其它方法的配合。和查询条件相关的用法我们称之为查询语言，查询语言可以用于数据库的URD操作，要掌握查询语言的核心，谨记：2个方法，3个用法，8个要诀。

2个方法

其中两个方法是：

方法	描述
where	AND条件查询
whereOr	OR条件查询

上面两个方法以及和其它的链式方法配合可以组合出满足需求的所有条件查询语法，where和whereOr方法可以在一次查询操作中多次调用，并且在调用Query类的find/select/update/delete方法（及其衍生方法）的时候，由Builder类最终生成一个包含查询条件的SQL语句。

下面是一个测试查询代码：

Db::table('data')
    ->where('name', 'like', '%thinkphp')
    ->whereOr('id', '>', 1)
    ->find();

和下面的查询结果可以自行比较下，然后揣测下where和whereOr方法的区别以及调用顺序的影响：

Db::table('data')
    ->where('id', '>', 1)
    ->whereOr('name', 'like', '%thinkphp')
    ->find();

3个用法

3个用法就是表达式、数组和闭包用法，并且是支持混合使用的。

要掌握查询语言的用法，必须在上面两个方法和两个用法的基础上多尝试，并且及时在页面Trace中查看最终生成的SQL语句是否是满足实际查询需求（或者说是期望的查询条件），这才是掌握查询的不二法门。

先来说表达式用法，这是查询语言的基础，查询方法（包括where和whereOr，这里以where为例）参数用法为：

where('字段名','表达式','查询条件')

[info]#### where('字段名','查询条件（等于）')
[info]#### where('字符串查询条件','参数绑定（数组）')

表达式不分大小写，支持的查询表达式有下面几种，分别表示的含义是：

表达式	含义	查询条件类型
=	等于	字符串或者数字
<>	不等于	字符串或者数字
>	大于	字符串或者数字
>=	大于等于	字符串或者数字
<	小于	字符串或者数字
<=	小于等于	字符串或者数字
[not] like	模糊查询	字符串
[not] between	（不在）区间查询	字符串或者数组
[not] in	（不在）IN 查询	字符串、数组或闭包
[not] null	查询字段是否（不）是NULL	空
[not] exists	EXISTS查询	字符串或者闭包
exp	表达式查询，支持SQL语法	字符串
> time	时间比较	字符串或者数字
< time	时间比较	字符串或者数字
>= time	时间比较	字符串或者数字
<= time	时间比较	字符串或者数字
between time	时间比较	字符串或者数字
notbetween time	时间比较	字符串或者数字

测试代码如下：

Db::table('data')
    ->where('id', '<>', 8)
    ->where('id', 'between', [1, 20])
    ->whereOr('name', 'like', '%thinkphp')
    ->select();

关于表达式查询语法这里补充说明下，下面的几种都是等效的：

表达式用法一	表达式用法二
not like	notlike
not null	notnull
not in	notin
not between	notbetween
not exists	notexists
not between time	notbetween time

所有带between的表达式，查询条件必须包含两个元素（或者用逗号分隔的两个元素），否则会抛出异常，所以在传入查询条件之前最好先验证下。

数组用法其实是多字段的表达式用法，在一个方法完成所有的查询条件，用法如下：

where([

'字段名1' => ['表达式', '查询条件'],
'字段名2' => ['表达式', '查询条件'],
'字段名2' => '条件（等于）',
...
])

测试代码：

Db::table('data')
    ->where(['name' => ['like', '%thinkphp'], 'id' => ['>', 1]])
    ->whereOr(['id' => ['<', 10], 'name' => ['like', '%php%']])
    ->find();

5.1须知

---

如果是5.1版本，数组查询条件必须改成如下用法：

Db::table('data')
    ->where([ 
      ['name', 'like', '%thinkphp'], 
        ['id', '>', 1]
    ])
    ->whereOr([ 
      ['id', '<', 10], 
        ['name', 'like', '%php%']
    ])
    ->find();

数组用法不够灵活，有时候需要和其它用法配合使用，出于安全考虑也并不推荐。

闭包用法指的是直接在where或者whereOr方法中传入闭包，和前面两种用法配合可以完成复杂的查询条件，例如：

$result = Db::table('data')
    ->where(function ($query) {
        $query->where('id', 1)->whereOr('id', '>', 2);
    })
    ->whereOr(function ($query) {
        $query->where('name', 'like', '%think%')->where('id', '<>', 8);
    })
    ->select();

闭包方法只有一个查询对象参数，如果需要在闭包中使用外部的变量，可以使用闭包的use语法，例如：

$id     = 1;
$name   = 'think';
$result = Db::table('data')
    ->where(function ($query) use ($id) {
        $query->where('id', $id);
    })
    ->whereOr(function ($query) use ($id, $name) {
        $query->where('name', 'like', '%' . $name . '%')->where('id', '<>', $id);
    })
    ->select();

8个要诀：

在使用查询的过程中谨记下面8个要诀，帮助你更好的完成查询。

• 查询条件的调用次序就是生成SQL的条件顺序；
• 查询字段用&分割表示对多个字段使用AND查询；
• 查询字段用|分割表示对多个字段使用OR查询；
• 对同一个查询字段多次调用非等查询条件会合并查询；
• 闭包查询和EXP查询会在生成的查询语句两边加上括号；
• 用闭包查询替代3.2版本的组合查询；
• 除了EXP查询外，其它查询都会自动使用参数绑定；
• 如果查询条件来自用户输入，尽量使用表达式和闭包查询，数组条件查询务必使用官方推荐的方法获取变量；

总结

本章我们学习了数据库的基础查询用法，以及如何使用链式方法完成查询构造器。下一章我们会来学习下更多的高级查询用法。

上一篇：第二章：数据创建和迁移
下一篇：第四章：高级查询技巧