数据库-查询构造器

Laravel

从数据表中获取所有的数据

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

从数据表中获取一行数据

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

从数据表中获取一行中的单独字段

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

获取一列的值

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

获取一列的值并指定键名

第二个参数为键名

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

结果分块

如果你需要操作数千条数据库记录，可以考虑使用 chunk 方法。这个方法每次只取出一小块结果，并会将每个块传递给一个 闭包 处理。这个方法对于编写数千条记录的 Artisan 命令 是非常有用的。例如，让我们把 users 表进行分块，每次操作 100 条数据

DB::table('users')->orderBy('id')->chunk(100,function($users){
    foreach($users as $user){
        //...
    }
});
// 你可以从 闭包 中返回 false，以停止对后续分块的处理
DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    // Process the records...
    return false;
});

聚合方法

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->min('price');
$price = DB::table('orders')->sum('price');
$price = DB::table('orders')->where('finalized', 1)->avg('price');

select distinct addSelect

$users = DB::table('users')->select('name', 'email as user_email')->get();
// 返回不重复的结果
$users = DB::table('users')->distinct()->get();
// 添加查询字段
$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();

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

left join

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

cross join

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

join 子句

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

union unionAll

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

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

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

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

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

whereNull

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

whereNotNull

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

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

$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 参数分组

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

Where 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 查询语句

本特性仅支持 MySQL 5.7+ 和 Postgres数据库

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

orderBy

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

latest / oldest

latest 和 oldest 方法允许你更容易的依据日期对查询结果排序。默认查询结果将依据 created_at 列

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

inRandomOrder

inRandomOrder 方法可以将查询结果随机排序。例如，你可以使用这个方法获取一个随机用户

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

groupBy / having / havingRaw

• groupBy 和 having 方法可用来对查询结果进行分组。having 方法的用法和 where 方法类似
• havingRaw 方法可以将一个原始的表达式设置为 having 子句的值

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

skip / take

你可以使用 skip 和 take 方法来限制查询结果数量或略过指定数量的查询

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

条件语句 when

有时候，你希望某个值为 true 时才执行查询。例如，如果在传入请求中存在指定的输入值的时候才执行这个 where 语句。你可以使用 when 方法实现

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

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

update

DB::table('users')->where('id', 1)->update(['votes' => 1]);
// 更新json
DB::table('users')->where('id', 1)->update(['options->enabled' => true]);

自增或自减increment decrement

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

truncate

如果你需要清空表，你可以使用 truncate 方法，这将删除所有行，并重置自动递增 ID 为零

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

sharedLock

共享锁可防止选中的数据列被篡改，直到事务被提交为止

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

lockForUpdate

使用「更新」锁可避免行被其它共享锁修改或选取

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