Laravel5.8
  1. 安装配置及核心内容
  2. 框架基础
  3. 进阶知识
  4. 数据库相关
    1. Laravel - 数据库相关 - 简介与配置
    2. Laravel - 数据库相关 - 原生 SQL 操作
    3. Laravel - 数据库相关 - 查询构造器
    4. Laravel - 数据库相关 - 分页
    5. Laravel - 数据库相关 - 数据迁移
    6. Laravel - 数据库相关 - 数据填充
    7. Laravel - 数据库相关 - redis
    8. Laravel - Eloquent 模型 - 入门
    9. Laravel - Eloquent 模型 - 查询作用域(全局,本地,动态)
    10. Laravel - Eloquent 模型 - 事件与监听方法
    11. Laravel - Eloquent 模型 - 关联关系
    12. Laravel - Eloquent 模型 - 关联查询
    13. Laravel - Eloquent 模型 - 访问器和修改器

Laravel - 数据库相关 - 查询构造器

程序员日记      2019-09-05

简介

数据库查询构建器提供了一个方便的流接口用于创建和执行数据库查询。

查询构建器可以用于执行应用中绝大部分数据库操作,并且能够在 Laravel 支持的所有数据库系统上工作。

Laravel 查询构建器使用 PDO 参数绑定来避免 SQL 注入攻击,不再需要过滤以绑定方式传递的字符串。


查询构造器的用法

从一张表中取出所有行

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

说明

get 方法返回包含结果集的 Illuminate\Support\Collection,其中每一个结果都是 PHP StdClass 对象实例

你可以像访问对象的属性一样访问字段的值:

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


从一张表中获取一行/一列

$user = DB::table('users')->where('name', '程序员日记')->first();

说明

该方法将会返回单个 StdClass 对象,可以这样调用数据

echo $user->name;


单个列值

$email = DB::table('users')->where('name', '程序员日记')->value('email');



列值列表

示例.获取角色标题数组

DB::table('roles')->pluck('title');
foreach ($titles as $title) {
    echo $title;
}


组块结果集

示例

DB::table('users')->orderBy('id')->chunk(100, function($users) {
    foreach ($users as $user) {
        //处理结果集
        return false;
    }
});


终止组块的运行

DB::table('users')->orderBy('id')->chunk(100, function($users) {
    // return false终止组块运行
    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]);
        }
    });

注:在组块回调中更新或删除记录时,对主键或外键的任何更改都会影响组块查询,甚至会导致相应记录不被包含在组块结果集中。


聚合函数

DB::table('users')->where('id','<>',1)->count('id');//返回符合指定条件的记录总数
DB::table('users')->where('id','<>',1)->max('age');//获取符合指定条件的指定列的最大值
DB::table('users')->where('id','<>',1)->min('age');//获取符合指定条件的指定列的最小值
DB::table('users')->where('id','<>',1)->avg('age');//平均值
DB::table('users')->where('id','<>',1)->sum('age');//列总和


判断记录是否存在

DB::table('orders')->where('finalized', 1)->exists();
DB::table('orders')->where('finalized', 1)->doesntExist();


指定查询字段

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


去掉重复值

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


有了一个查询构建器实例并且希望添加一个查询列到已存在的 select 子句,可以使用 addSelect 方法

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


原生表达式

DB:raw

想要创建一个原生表达式,可以使用 DB::raw 方法

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

说明

原生语句会以字符串的形式注入查询,所以这里尤其要注意避免 SQL 注入攻击。


selectRaw

selectRaw 方法可用于替代 select(DB::raw(...)),该方法接收一个可选的绑定数组作为第二个参数

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


whereRaw / orWhereRaw

whereRaw 和 orWhereRaw 方法可用于注入原生 where 子句到查询,这两个方法接收一个可选的绑定数组作为第二个参数

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


havingRaw / orHavingRaw

havingRaw orHavingRaw 方法可用于设置原生字符串作为 having 子句的值

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


orderByRaw

orderByRaw 方法可用于设置原生字符串作为 order by 子句的值

DB::table('orders')
    ->orderByRaw('updated_at - created_at DESC')
    ->get();


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

说明

要实现一个简单的"内连接",你可以使用查询构建器实例上的 join 方法,

传递给 join 方法的第一个参数是你需要连接到的表名,

剩余的其它参数则是为连接指定的列约束。


左连接/右连接

如果你是想要执行「左连接」或「右连接」而不是「内连接」,

可以使用 leftJoin 或 rightJoin 方法。

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

说明

交叉连接在第一张表和被连接表之间生成一个笛卡尔积


高级链接语句

传递闭包作为join方法的第二个参数,可以在闭包中添加相关的约束

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


联合查询(Union)

查询构建器还提供了“联合”两个查询的快捷方式,比如,你可以先创建一个查询,然后使用 union 方法将其和第二个查询进行联合

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

说明

unionAll 方法也是有效的,并且和 union 使用方式相同。


Where 子句

简单的where语句

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

传递数组

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


orWhere

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

whereNotBetween 方法验证列值不在给定值之间    

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


whereIn/whereNotIn

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

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


whereNull/whereNotNull

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

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


whereDate

whereDate 方法用于比较字段值和日期

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


whereMonth 

whereMonth 方法用于比较字段值和一年中的指定月份

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


whereDay

whereDay 方法用于比较字段值和一月中的指定日期

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


whereYear

whereYear 方法用于比较字段值和指定年

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


whereTime 

whereTime 方法用于比较字段值和指定时间

$users = DB::table('users')
            ->whereTime('created_at', '=', '11:20')
            ->get();


whereColumn

whereColumn 方法用于验证两个字段是否相等

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

传比较运算符到这个方法

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

传递数组

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


where约束分组

有时候你需要创建更加高级的 where 子句,比如“where exists”或者嵌套的参数分组。

Laravel 查询构建器也可以处理这些。

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

上述语句等价于下面的 SQL

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


where exists 子句

whereExists 方法允许你编写 where exists SQL 子句,

whereExists 方法接收一个闭包参数,

该闭包获取一个查询构建器实例从而允许你定义放置在“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 子句

Laravel 还支持在提供 JSON 字段类型的数据库(目前是 MySQL 5.7、 PostgresSQL、SQL Server 2016 以及 SQLite 3.9.0)上使用操作符 -> 获取指定 JSON 字段值

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


whereJsonContains 查询 JSON 数组

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


排序、分组、限定

orderBy

通过给定字段对结果集进行排序

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

说明

orderBy 的第一个参数应该是你希望排序的字段,

第二个参数控制着排序的方向 —— asc 或 desc


latest / oldest

latest / oldest方法结果集根据 created_at 字段进行排序,

$user = DB::table('users')
        ->latest()
        ->first();


inRandomOrder

inRandomOrder 方法可用于对查询结果集进行随机排序

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


groupBy / having

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 方法。


skip / take

想要限定查询返回的结果集的数目,

或者在查询中跳过给定数目的结果,

可以使用skip 和 take 方法。

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

作为替代方法,还可以使用 limit 和 offset 方法

$users = DB::table('users')
        ->offset(10)
        ->limit(5)
        ->get();


条件字句

有时候你可能想要某些条件为 true 的时候才将条件子句应用到查询。例如,你可能只想给定值在请求中存在的情况下才应用 where 语句,这可以通过 when 方法实现

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

说明

1.when 方法只有在第一个参数为 true 的时候才执行给定闭包,如果第一个参数为 false,则闭包不执行。

2.你可以传递另一个闭包作为 when 方法的第三个参数,该闭包会在第一个参数为 false 的情况下执行。

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

插入(Insert)

插入单条记录

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


插入多条记录

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


获取自增ID

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

更新(Update)

更新数据

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


更新或插入数据

更新数据库中已存在的某条记录,如果对应记录不存在的话,则插入这条记录。

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


更新 JSON 字段

更新 JSON 字段的时候,需要使用 -> 语法访问 JSON 对象上相应的值,该操作只能用于支持 JSON 字段类型的数据库

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

说明

这两个方法都至少接收一个参数:需要修改的列。


删除(Delete)

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

清除整张表,也就是删除所有列并将自增 ID 置为 0,可以使用 truncate 方法

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

悲观锁和乐观锁

悲观锁

悲观锁(Pessimistic Lock),顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁、表锁、读锁、写锁等,都是在做操作之前先上锁。

示例

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

等价于这条语句

select * from `users` where `votes` > '100' lock in share mode


乐观锁

乐观锁(Optimistic Lock),顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于 write_condition 机制的其实都是提供的乐观锁。

示例

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

等价于这条语句

select * from `users` where `votes` > '100' for update

for update 与 lock in share mode 都是用于确保被选中的记录值不能被其它事务更新(上锁),

两者的区别在于 lock in share mode 不会阻塞其它事务读取被锁定行记录的值,

而 for update 会阻塞其他锁定性读对锁定行的读取(非锁定性读仍然可以读取这些记录,lock in share mode 和 for update 都是锁定性读)。

这么说比较抽象,我们举个计数器的例子:在一条语句中读取一个值,然后在另一条语句中更新这个值。使用 lock in share mode 的话可以允许两个事务读取相同的初始化值,所以执行两个事务之后最终计数器的值+1;而如果使用 for update 的话,会锁定第二个事务对记录值的读取直到第一个事务执行完成,这样计数器的最终结果就是+2了。