Laravel 文档阅读：数据库之查询语句构造器（上篇）

翻译、衍生自：https://learnku.com/docs/laravel/5.4/queries

简介

Laravel 的查询构造器（query builder）提供流畅的接口，帮助你构造、执行数据库查询。这里的「查询」，并不只是指select 语句，还有 update、delete 和 insert 语句等。在所有支持的数据库系统中都运行良好。

Laravel 的查询构造器使用 PDO 参数绑定保护程序免受 SQL 注入攻击，所以你传递的绑定参数无需进行清理操作了。

查询数据

获得表格里所有数据

使用 DB 门面的 table 方法开启查询。table 方法返回给定表格的查询构造器实例，允许你用链式调用的方式为查询添加约束条件，最后使用 get 方法获得结果。

<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

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

get 方法返回一个 Illuminate\Support\Collection 集合实例，集合里的每个元素都是一个 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');

获得表格里的一列数据

使用 pluck 方法可获得一个列的值的集合。在下面的例子里，我们获得所有的角色名：

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

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

也可以在返回的集合里指定自定义键：

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

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

分块输出数据

如果要处理成千上万条数据，考虑使用 chunk 方法。这个方法一次取出一小块数据，放到闭包里处理。这在写 Artisan 命令处理成千上万条数据非常有用。例如，从 users 表里分块、每次输出 100 条记录：

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

也可以通过在闭包里 return false 停止继续分块输出数据。

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

    return false;
});

聚合

查询构造器也提供了许多聚合方法：count、max、min、avg 和 sum。在构造好你的查询后即可使用：

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

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

聚合方法也可以结合查询子句使用：

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

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();

原生表达式

有时要在一个查询语句里使用原生表达式，那么使用 DB::raw 方法就行，传递给该方法的内容会作为字符串插入到最终的查询语句里。

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

Joins

内连接

查询语句构造器也可以用来写连接语句（join statements）。「内连接」就是用 join 方法，该方法的第一个参数是要连接的表，剩下的参数就是连接约束条件了。不仅如此，你可以在一个构造语句里连接多个表格：

$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 方法，它的方法签名和 join 是一样的：

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

交叉连接

交叉连接使用 crossJoin 方法。

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

高级连接子句

如果要使用更加高级的连接子句，就要通过 join 方法的第二个参数，它是一个闭包。闭包接收一个 JoinClause 对象，用于对 join子句添加约束条件：

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

你还可以在 join 子句上使用「where」约束，得用到 where 或者 orWhere 方法——限定列的取值范围：

DB::table('users')
            ->join('contatcs', function ($join) {
                $join->on('users.id', '=', 'contatcs.user_id')
                         ->where('contacts.user_id', '>', 5);
            })
            ->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 方法最基本的用法是给它传递 3 个参数。第一个参数是字段名，第二个参数是操作符（数据库支持的任何操作符），第三个参数是字段值。

例如，下面的查询查出所有「投票」数是 100 的记录：

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

为了方便，当验证某个字段是否等于某个值时，可以省略中间的 =。

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

除了 = ，这里还有使用其他操作符的例子：

$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 方法传递一个数组，指定多个限制条件：

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

Or 子句

使用 orWhere 方法添加 or 子句约束。orWhere 的方法签名和 where 是一样的：

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

其他子句

whereBetween

whereBetween 方法验证字段值是否在给定范围之间。

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

whereNotBetween

whereBetween 方法验证字段值是否在给定范围之外。

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

whereIn / whereNotIn

whereIn 方法验证字段值是否在给定值列表里。

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

whereNotIn 方法验证字段值是否不在给定值列表里。

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

whereNull / whereNotNull

whereNull 方法验证指定的字段值是否是 null。

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

whereNotNull 方法验证指定的字段值是否不是 null。

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

whereDate / whereMonth / whereDay / whereYear

whereDate 用来比较字段值是否满足给定的日期。

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

whereMonth 用来比较字段值是否满足给定的月份。

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

whereDay 用来比较字段值是否满足给定的日期。

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

whereYear 用来比较字段值是否满足给定的年份。

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

whereColumn

whereColumn 用来比较两字段值是否相等：

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

也可以使用比较操作符。

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

whereColumn 也接受多条件判断，这些条件会使用 and 操作符连起来：

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

分组参数

有时需要创建些高级的 where 子句，比如「where exists」或者内嵌分组参数。交给 Laravel 的查询语句构造器照样 OK。看个例子：

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')

Where Exists 子句

whereExists 方法用来写 where exists SQL 子句。whereExists 方法接收一个闭包，包含一个查询语句构造器实例，用于定义「where」子句里的内容：

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 子句

Laravel 也支持数据库 JSON 字段类型的查询，前提是数据库支持 JSON 字段类型。现在 MySQL 5.7 和 Postgres 都支持。查询 JSON 字段，使用 -> 操作符：

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

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