数据库：查询

Introduction
检索结果
- 分块结果
- Aggregates
Selects
Joins
Unions
Where 子句
- 高级 where 子句
- 条件条款
排序、分组、限制和偏移
Inserts
Updates
Deletes
悲观锁定
缓存查询
- 持久缓存
- 内存缓存
Debugging

Introduction

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

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

检索结果

从表中检索所有行

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

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

喜欢原始查询，这get 方法返回一个array 结果的每个结果都是 PHP 的一个实例stdClass 目的。您可以通过访问作为对象属性的列来访问每一列的值：

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

从表中检索单个行/列

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

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

echo $user->name;

如果您甚至不需要整行，您可以使用value 方法。此方法将直接返回列的值：

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

检索列值列表

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

$titles = Db::table('roles')->lists('title');

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

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

$roles = Db::table('roles')->lists('title', 'name');

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

分块结果

如果您需要处理数以千计的数据库记录，请考虑使用chunk 方法。此方法一次检索结果的一小“块”，并将每个块馈送到Closure 进行处理。这个方法对写作很有用控制台命令处理数千条记录。例如，让我们处理整个users 一次以 100 条记录为一组的表：

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

您可以通过返回来停止处理更多块false 来自Closure:

Db::table('users')->chunk(100, function($users) {
    // Process the records...

    return false;
});

如果您在对结果进行分块时更新数据库记录，则分块结果可能会以意想不到的方式发生变化。因此，在分块更新记录时，最好使用chunkById 方法代替。此方法将根据记录的主键自动对结果进行分页：

Db::table('users')->where('active', false)
    ->chunkById(100, function ($users) {
        foreach ($users as $user) {
            Db::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });

NOTE: 在块回调中更新或删除记录时，对主键或外键的任何更改都可能影响块查询。这可能会导致记录不包含在分块结果中。

Aggregates

查询构建器还提供了多种聚合方法，例如count,max,min,avg，和sum.您可以在构建查询后调用这些方法中的任何一个：

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

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

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

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

判断记录是否存在

而不是使用count 方法来确定是否存在任何符合查询约束的记录，您可以使用exists 和doesntExist 方法：

return Db::table('orders')->where('finalized', 1)->exists();

return Db::table('orders')->where('finalized', 1)->doesntExist();

Selects

指定选择子句

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

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

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

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

如果您已经有一个查询生成器实例并且您希望将一列添加到其现有的 select 子句中，您可以使用addSelect 方法：

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

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

如果您希望将列和/或字符串连接在一起，您可以使用selectConcat 指定连接值列表和结果别名的方法。如果您希望在连接中使用字符串，则必须提供带引号的字符串：

$query = Db::table('users')->selectConcat(['"Name: "', 'first_name', 'last_name'], 'name_string');

$nameString = $query->first()->name_string;   // Name: John Smith

原始表达式

有时您可能需要在查询中使用原始表达式。要创建原始表达式，您可以使用Db::raw 方法：

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

NOTE: 原始语句将作为字符串注入到查询中，因此您应该非常小心，不要创建 SQL 注入漏洞。

原始方法

而不是使用Db::raw, 您还可以使用以下方法将原始表达式插入查询的各个部分。

selectRaw

这selectRaw 方法可以用来代替addSelect(Db::raw(...)). 此方法接受一个可选的绑定数组作为其第二个参数：

$orders = Db::table('orders')
            ->selectRaw('price * ? as price_with_tax', [1.0825])
            ->get();

whereRaw / 或 WhereRaw

这whereRaw 和orWhereRaw 方法可用于注入原始where 子句到您的查询中。这些方法接受一个可选的绑定数组作为它们的第二个参数：

$orders = Db::table('orders')
            ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
            ->get();

havingRaw/或HavingRaw

这havingRaw 和orHavingRaw 方法可用于将原始字符串设置为having 条款。这些方法接受一个可选的绑定数组作为它们的第二个参数：

$orders = Db::table('orders')
                ->select('department', Db::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

orderByRaw

这orderByRaw 方法可用于将原始字符串设置为 order by 子句的值：

$orders = Db::table('orders')
                ->orderByRaw('updated_at - created_at DESC')
                ->get();

groupByRaw

这groupByRaw 方法可用于将原始字符串设置为 group by 子句的值：

$orders = Db::table('orders')
                ->select('city', 'state')
                ->groupByRaw('city, state')
                ->get();

Joins

内部连接语句

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

$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 或者rightJoin 方法。这leftJoin 和rightJoin 方法具有与join 方法：

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

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

交叉连接语句

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

$users = Db::table('sizes')
    ->crossJoin('colors')
    ->get();

高级连接语句

您还可以指定更高级的连接子句。首先，通过一个Closure 作为进入的第二个参数join 方法。这Closure 将收到一个JoinClause 允许您指定约束的对象join 条款：

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

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

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

子查询连接

您可以使用joinSub,leftJoinSub，和rightJoinSub 将查询连接到子查询的方法。这些方法中的每一个都接收三个参数：子查询、它的表别名和一个定义相关列的闭包：

$latestPosts = Db::table('posts')
    ->select('user_id', Db::raw('MAX(created_at) as last_post_created_at'))
    ->where('is_published', true)
    ->groupBy('user_id');

$users = Db::table('users')
    ->joinSub($latestPosts, 'latest_posts', function ($join) {
        $join->on('users.id', '=', 'latest_posts.user_id');
    })->get();

Unions

查询生成器还提供了一种将两个查询“合并”在一起的快速方法。例如，您可以创建一个初始查询，然后使用union 将它与第二个查询合并的方法：

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

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

这unionAll 方法也可用并且具有与相同的方法签名union.

Where 子句

简单的 where 子句

加上where 查询的子句，使用where 查询生成器实例上的方法。最基本的调用where 需要三个参数。第一个参数是列的名称。第二个参数是一个运算符，它可以是数据库支持的任何运算符。第三个参数是要针对该列进行评估的值。

例如，这是一个验证“投票”列的值是否等于 100 的查询：

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

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

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

当然，您可以在编写where 条款：

$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 约束链接在一起，也可以添加or 查询的子句。这orWhere 方法接受与where 方法：

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

Tip: 你也可以前缀or 到下面的任何 where 语句方法，使条件成为“或”条件 - 例如，orWhereBetween,orWhereIn， ETC。

“Where between”语句

这whereBetween 方法验证列的值是否介于两个值之间：

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

这whereNotBetween 方法验证列的值是否位于两个值之外：

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

“在哪里”陈述

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

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

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

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

“Where null”语句

这whereNull 方法验证给定列的值是NULL:

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

这whereNotNull 方法验证列的值是not NULL:

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

高级 where 子句

参数分组

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

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

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

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

存在陈述

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

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

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

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

JSON“where”语句

Winter CMS 还支持在提供 JSON 列类型支持的数据库上查询 JSON 列类型。要查询 JSON 列，请使用-> 操作员：

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

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

你可以使用whereJsonContains 查询 JSON 数组（SQLite 不支持）：

$users = Db::table('users')
    ->whereJsonContains('options->languages', 'en')
    ->get();

MySQL 和 PostgreSQL 支持whereJsonContains 具有多个值：

$users = Db::table('users')
    ->whereJsonContains('options->languages', ['en', 'de'])
    ->get();

你可以使用whereJsonLength 按长度查询 JSON 数组：

$users = Db::table('users')
    ->whereJsonLength('options->languages', 0)
    ->get();

$users = Db::table('users')
    ->whereJsonLength('options->languages', '>', 1)
    ->get();

条件条款

有时您可能希望仅当其他条件为真时才将子句应用于查询。例如，您可能只想应用一个where 传入请求中是否存在给定输入值的语句。您可以使用when 方法：

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

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

这when 方法仅在第一个参数为true.如果第一个参数是false，闭包将不会被执行。

你可以将另一个闭包作为第三个参数传递给when 方法。如果第一个参数的计算结果为 false，则此闭包将执行。为了说明如何使用此功能，我们将使用它来配置查询的默认排序：

$sortBy = null;

$users = Db::table('users')
    ->when($sortBy, function ($query, $sortBy) {
        return $query->orderBy($sortBy);
    }, function ($query) {
        return $query->orderBy('name');
    })
    ->get();

排序、分组、限制和偏移

排序

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

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

随机顺序

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

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

Grouping

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

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

您可以将多个参数传递给groupBy 按多列分组的方法：

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

对于更高级的having 声明，您可能希望使用havingRaw 方法。

限制和偏移

限制从查询返回的结果数，或跳过查询中给定数量的结果 (OFFSET), 你可以使用skip 和take 方法：

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

Inserts

查询构建器还提供了一个insert 向数据库表中插入记录的方法。这insert 方法接受要插入的列名和值的数组：

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

您甚至可以通过一次调用将多条记录插入到表中insert 通过传递数组数组。每个数组代表要插入表中的一行：

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

自动递增 ID

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

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

NOTE: 使用 PostgreSQL 数据库驱动程序时，insertGetId 方法需要命名自增列id.如果您想从不同的“序列”中检索 ID，您可以将序列名称作为第二个参数传递给insertGetId 方法。

Updates

除了将记录插入数据库外，查询构建器还可以使用update方法。这update 方法，比如insert 方法，接受包含要更新的列的列和值对的数组。你可以限制update 查询使用where 条款：

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

更新或插入（每行一个查询）

有时您可能希望更新数据库中的现有记录，或者在不存在匹配记录时创建它。在这种情况下，updateOrInsert 可以使用方法。这updateOrInsert 方法接受两个参数：用于查找记录的条件数组，以及包含要更新的列的列和值对数组。

这updateOrInsert 方法将首先尝试使用第一个参数的列和值对来定位匹配的数据库记录。如果记录存在，它将用第二个参数中的值更新。如果找不到该记录，将插入一个新记录，其中包含两个参数的合并属性：

Db::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

更新或插入 /`upsert()` （批量查询在一个数据库调用中处理多行）

这upsert 方法将插入不存在的行并使用新值更新已存在的行。该方法的第一个参数包含要插入或更新的值，而第二个参数列出了在关联表中唯一标识记录的列。该方法的第三个也是最后一个参数是一个列数组，如果数据库中已存在匹配记录，则应更新该列：

DB::table('flights')->upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);

NOTE: 除 SQL Server 外的所有数据库都需要第二个参数中的列upsert 具有“主要”或“唯一”索引的方法。

更新 JSON 列

更新 JSON 列时，您应该使用-> 访问 JSON 对象中适当键的语法。 MySQL 5.7+ 和 PostgreSQL 9.5+ 支持此操作：

$affected = Db::table('users')
    ->where('id', 1)
    ->update(['options->enabled' => true]);

递增/递减

查询构建器还提供了方便的方法来增加或减少给定列的值。这只是一个捷径，与手动编写相比，它提供了更具表现力和简洁的界面update 陈述。

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

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

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

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

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

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

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

Deletes

查询生成器也可用于通过以下方式从表中删除记录delete 方法：

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

你可以约束delete 通过添加语句where 调用前的子句delete 方法：

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

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

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

悲观锁定

查询生成器还包括一些函数来帮助您对您的查询进行“悲观锁定”select 声明。要使用“共享锁”运行语句，您可以使用sharedLock 查询方法。共享锁可防止所选行在您的事务提交之前被修改：

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

或者，您可以使用lockForUpdate 方法。 “for update”锁防止行被修改或被另一个共享锁选中：

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

缓存查询

持久缓存

您可以使用缓存服务.简单地链接remember 或者rememberForever 准备查询时的方法。

$users = Db::table('users')->remember(10)->get();

在此示例中，查询结果将缓存十分钟。结果被缓存时，不会对数据库运行查询，结果将从为您的应用程序指定的默认缓存驱动程序加载。

内存缓存

可以通过使用内存缓存来防止跨同一请求的重复查询。默认情况下启用此功能由模型准备的查询但不是那些直接使用Db 正面。

Db::table('users')->get(); // Result from database
Db::table('users')->get(); // Result from database

Model::all(); // Result from database
Model::all(); // Result from in-memory cache

您可以使用以下任一方式启用或禁用重复缓存enableDuplicateCache 或者disableDuplicateCache 方法。

Db::table('users')->enableDuplicateCache()->get();

如果查询存储在缓存中，当使用插入、更新、删除或截断语句时，它会自动被清除。但是，您可以使用flushDuplicateCache 方法。

Db::flushDuplicateCache();

NOTE：当通过命令行界面 (CLI) 运行时，内存中的缓存被完全禁用。

Debugging

您可以使用dd或者dump 构建查询以转储查询绑定和 SQL 时的方法。这dd 方法将显示调试信息，然后停止执行请求。这dump 方法将显示调试信息但允许请求继续执行：

Db::table('users')->where('votes', '>', 100)->dd();

Db::table('users')->where('votes', '>', 100)->dump();

数据库：查询

#Introduction

#检索结果