Laravel 文档阅读:数据库之查询语句构造器(上篇)

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

简介

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

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

聚合

查询构造器也提供了许多聚合方法:countmaxminavgsum。在构造好你的查询后即可使用:

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

当传递 ClosureorWhere 方法的时候,就表示开始一个分组约束了。 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();
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 5
feiliu3k

总结得真好,简单明了,比看文档舒服得多了!:thumbsup:

1年前
zhangbao

@feiliu3k 谢谢,希望能帮到你 :)

1年前

$users = DB::table('users')->distinct()->get(); 这个对所有列去重?岂不几乎没作用

1年前
zhangbao

是的。文档中虽然这样写,但还是以实际使用情况为准

1年前
Complicated

两的确实不错,比文档强,,学习了!

8个月前

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!