数据库：查询构建器

介绍
检索结果
- 聚合
选择
连接
联合
Where 子句
- 高级 Where 子句
- JSON Where 子句
排序、分组、限制和偏移
条件语句
插入
更新
删除
悲观锁定

介绍

数据库查询构建器提供了一个方便、流畅的接口来创建和运行数据库查询。它可以用于在应用程序中执行大多数数据库操作，并适用于所有支持的数据库系统。

NOTE

Laravel 查询构建器使用 PDO 参数绑定来保护您的应用程序免受 SQL 注入攻击。无需清理作为绑定传递的字符串。

检索结果

从表中检索所有行

要开始流畅的查询，请在 DB facade 上使用 table 方法。table 方法返回给定表的流畅查询构建器实例，允许您将更多约束链接到查询中，然后最终获取结果。在此示例中，让我们从表中 get 所有记录：

php

<?php

namespace App\Http\Controllers;

use DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * 显示应用程序所有用户的列表。
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

与原始查询类似，get 方法返回一个结果 array，其中每个结果都是 PHP StdClass 对象的实例。您可以通过将列作为对象的属性来访问每个列的值：

php

foreach ($users as $user) {
    echo $user->name;
}

从表中检索单行/列

如果您只需要从数据库表中检索一行，可以使用 first 方法。此方法将返回一个 StdClass 对象：

php

$user = DB::table('users')->where('name', 'John')->first();

echo $user->name;

如果您甚至不需要整行，可以使用 value 方法从记录中提取单个值。此方法将直接返回列的值：

php

$email = DB::table('users')->where('name', 'John')->value('email');

从表中分块结果

如果您需要处理数千条数据库记录，请考虑使用 chunk 方法。此方法一次检索一小块结果，并将每个块传递给 Closure 进行处理。此方法对于编写处理数千条记录的 Artisan 命令非常有用。例如，让我们一次以 100 条记录的块处理整个 users 表：

php

DB::table('users')->orderBy('id')->chunk(100, function($users) {
    foreach ($users as $user) {
        //
    }
});

您可以通过从 Closure 返回 false 来停止进一步的块处理：

php

DB::table('users')->orderBy('id')->chunk(100, function($users) {
    // 处理记录...

    return false;
});

检索列值列表

如果您想检索包含单列值的数组，可以使用 pluck 方法。在此示例中，我们将检索角色标题的数组：

php

$titles = DB::table('roles')->pluck('title');

foreach ($titles as $title) {
    echo $title;
}

您还可以为返回的数组指定自定义键列：

php

$roles = DB::table('roles')->pluck('title', 'name');

foreach ($roles as $name => $title) {
    echo $title;
}

聚合

查询构建器还提供了多种聚合方法，例如 count、max、min、avg 和 sum。您可以在构建查询后调用这些方法：

php

$users = DB::table('users')->count();

$price = DB::table('orders')->max('price');

当然，您可以将这些方法与其他子句结合使用来构建查询：

php

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

选择

指定选择子句

当然，您可能并不总是希望从数据库表中选择所有列。使用 select 方法，您可以为查询指定自定义 select 子句：

php

$users = DB::table('users')->select('name', 'email as user_email')->get();

distinct 方法允许您强制查询返回不同的结果：

php

$users = DB::table('users')->distinct()->get();

如果您已经有一个查询构建器实例，并希望向其现有选择子句中添加列，可以使用 addSelect 方法：

php

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

原始表达式

有时您可能需要在查询中使用原始表达式。这些表达式将作为字符串注入到查询中，因此请小心不要创建任何 SQL 注入点！要创建原始表达式，可以使用 DB::raw 方法：

php

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

连接

内连接语句

查询构建器还可以用于编写连接语句。要执行基本的 SQL "内连接"，可以在查询构建器实例上使用 join 方法。传递给 join 方法的第一个参数是您需要连接的表的名称，而其余参数指定连接的列约束。当然，正如您所见，您可以在单个查询中连接多个表：

php

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

左连接语句

如果您想执行 "左连接" 而不是 "内连接"，请使用 leftJoin 方法。leftJoin 方法的签名与 join 方法相同：

php

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

交叉连接语句

要执行 "交叉连接"，请使用 crossJoin 方法，并指定您希望交叉连接的表的名称。交叉连接在第一个表和连接的表之间生成笛卡尔积：

php

$users = DB::table('sizes')
            ->crossJoin('colours')
            ->get();

高级连接语句

您还可以指定更高级的连接子句。要开始，请将 Closure 作为第二个参数传递给 join 方法。Closure 将接收一个 JoinClause 对象，允许您在 join 子句上指定约束：

php

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

如果您想在连接上使用 "where" 样式的子句，可以在连接上使用 where 和 orWhere 方法。这些方法将比较列与值，而不是比较两个列：

php

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

联合

查询构建器还提供了一种快速的方法来将两个查询 "联合" 在一起。例如，您可以创建一个初始查询，然后使用 union 方法将其与第二个查询联合：

php

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

unionAll 方法也可用，其方法签名与 union 相同。

Where 子句

简单的 Where 子句

要向查询中添加 where 子句，请在查询构建器实例上使用 where 方法。对 where 的最基本调用需要三个参数。第一个参数是列的名称。第二个参数是运算符，可以是数据库支持的任何运算符。第三个参数是要与列进行比较的值。

例如，这里有一个查询，验证 "votes" 列的值是否等于 100：

php

$users = DB::table('users')->where('votes', '=', 100)->get();

为了方便起见，如果您只想验证列是否等于给定值，可以将值直接作为 where 方法的第二个参数传递：

php

$users = DB::table('users')->where('votes', 100)->get();

当然，您可以在编写 where 子句时使用多种其他运算符：

php

$users = DB::table('users')
                ->where('votes', '>=', 100)
                ->get();

$users = DB::table('users')
                ->where('votes', '<>', 100)
                ->get();

$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

您还可以将条件数组传递给 where 函数：

php

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

Or 语句

您可以将 where 约束链接在一起，并向查询中添加 or 子句。orWhere 方法接受与 where 方法相同的参数：

php

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

其他 Where 子句

whereBetween

whereBetween 方法验证列的值是否在两个值之间：

php

$users = DB::table('users')
                    ->whereBetween('votes', [1, 100])->get();

whereNotBetween

whereNotBetween 方法验证列的值是否在两个值之外：

php

$users = DB::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

whereIn / whereNotIn

whereIn 方法验证给定列的值是否包含在给定数组中：

php

$users = DB::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();

whereNotIn 方法验证给定列的值是否不包含在给定数组中：

php

$users = DB::table('users')
                    ->whereNotIn('id', [1, 2, 3])
                    ->get();

whereNull / whereNotNull

whereNull 方法验证给定列的值是否为 NULL：

php

$users = DB::table('users')
                    ->whereNull('updated_at')
                    ->get();

whereNotNull 方法验证列的值是否不为 NULL：

php

$users = DB::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

whereColumn

whereColumn 方法可用于验证两个列是否相等：

php

$users = DB::table('users')
                ->whereColumn('first_name', 'last_name');

您还可以将比较运算符传递给该方法：

php

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at');

whereColumn 方法还可以传递多个条件的数组。这些条件将使用 and 运算符连接：

php

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ]);

高级 Where 子句

参数分组

有时您可能需要创建更高级的 where 子句，例如 "where exists" 或嵌套参数分组。Laravel 查询构建器也可以处理这些。要开始，让我们看一个在括号内分组约束的示例：

php

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

如您所见，将 Closure 传递给 orWhere 方法指示查询构建器开始一个约束组。Closure 将接收一个查询构建器实例，您可以使用它来设置应包含在括号组中的约束。上面的示例将生成以下 SQL：

php

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

存在语句

whereExists 方法允许您编写 where exists SQL 子句。whereExists 方法接受一个 Closure 参数，该参数将接收一个查询构建器实例，允许您定义应放置在 "exists" 子句中的查询：

php

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

上面的查询将生成以下 SQL：

php

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

JSON Where 子句

Laravel 支持在提供 JSON 列类型支持的数据库上查询 JSON 列类型。目前，这包括 MySQL 5.7 和 Postgres。要查询 JSON 列，请使用 -> 运算符：

php

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

排序、分组、限制和偏移

orderBy

orderBy 方法允许您按给定列对查询结果进行排序。orderBy 方法的第一个参数应为您希望排序的列，而第二个参数控制排序方向，可以是 asc 或 desc：

php

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

inRandomOrder

inRandomOrder 方法可用于随机排序查询结果。例如，您可以使用此方法来获取随机用户：

php

$randomUser = DB::table('users')
                ->inRandomOrder()
                ->first();

groupBy / having / havingRaw

groupBy 和 having 方法可用于对查询结果进行分组。having 方法的签名与 where 方法类似：

php

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

havingRaw 方法可用于将原始字符串设置为 having 子句的值。例如，我们可以找到所有销售额大于 $2,500 的部门：

php

$users = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

skip / take

要限制查询返回的结果数量，或在查询中跳过给定数量的结果（OFFSET），可以使用 skip 和 take 方法：

php

$users = DB::table('users')->skip(10)->take(5)->get();

条件语句

有时您可能希望语句仅在某些条件为真时应用于查询。例如，您可能只希望在传入请求中存在给定输入值时应用 where 语句。您可以使用 when 方法实现此目的：

php

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();

when 方法仅在第一个参数为 true 时执行给定的 Closure。如果第一个参数为 false，则不会执行 Closure。

插入

查询构建器还提供了 insert 方法，用于将记录插入到数据库表中。insert 方法接受一个包含要插入的列名和值的数组：

php

DB::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]
);

您甚至可以通过传递数组的数组来在单次调用 insert 时插入多条记录。每个数组表示要插入表中的一行：

php

DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

自动递增 ID

如果表具有自动递增的 id，请使用 insertGetId 方法插入记录，然后检索 ID：

php

$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

NOTE

使用 PostgreSQL 时，insertGetId 方法期望自动递增的列名为 id。如果您希望从不同的 "序列" 中检索 ID，可以将序列名称作为 insertGetId 方法的第二个参数传递。

更新

当然，除了将记录插入数据库之外，查询构建器还可以使用 update 方法更新现有记录。update 方法与 insert 方法一样，接受包含要更新的列和值对的数组。您可以使用 where 子句约束 update 查询：

php

DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);

增量/减量

查询构建器还提供了方便的方法来增加或减少给定列的值。这只是一个快捷方式，提供了比手动编写 update 语句更具表现力和简洁的接口。

这两种方法至少接受一个参数：要修改的列。可以选择传递第二个参数来控制列应增加/减少的数量。

php

DB::table('users')->increment('votes');

DB::table('users')->increment('votes', 5);

DB::table('users')->decrement('votes');

DB::table('users')->decrement('votes', 5);

您还可以在操作期间指定要更新的其他列：

php

DB::table('users')->increment('votes', 1, ['name' => 'John']);

删除

当然，查询构建器还可以通过 delete 方法从表中删除记录：

php

DB::table('users')->delete();

您可以通过在调用 delete 方法之前添加 where 子句来约束 delete 语句：

php

DB::table('users')->where('votes', '>', 100)->delete();

如果您希望截断整个表，这将删除所有行并将自动递增的 ID 重置为零，可以使用 truncate 方法：

php

DB::table('users')->truncate();

悲观锁定

查询构建器还包括一些函数，帮助您在 select 语句上进行 "悲观锁定"。要使用 "共享锁" 运行语句，可以在查询上使用 sharedLock 方法。共享锁会阻止所选行在事务提交之前被修改：

php

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();

或者，您可以使用 lockForUpdate 方法。"for update" 锁定会阻止行被修改或与另一个共享锁一起选择：

php

DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

数据库：查询构建器 ​

介绍 ​

检索结果 ​

从表中检索所有行 ​

从表中检索单行/列 ​

从表中分块结果 ​

检索列值列表 ​

聚合 ​

选择 ​

指定选择子句 ​

原始表达式 ​

连接 ​

内连接语句 ​

左连接语句 ​

交叉连接语句 ​

高级连接语句 ​

联合 ​

Where 子句 ​

简单的 Where 子句 ​

Or 语句 ​

其他 Where 子句 ​

高级 Where 子句 ​

参数分组 ​

存在语句 ​

JSON Where 子句 ​

排序、分组、限制和偏移 ​

orderBy ​

inRandomOrder ​

groupBy / having / havingRaw ​

skip / take ​

条件语句 ​

插入 ​

自动递增 ID ​

更新 ​

增量/减量 ​

删除 ​

悲观锁定 ​