Laravel5.6查询构建器使用笔记整理

使用说明

使用命名空间：use Illuminate\Support\Facades\DB;

获取结果集

$users = DB::table('users')->get();//从一张表中取出所有行
$user = DB::table('users')->where('name', '学院君')->first();//数据表中获取一行数据
$email = DB::table('users')->where('name', '学院君')->value('email');//获取单个值
$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;
}//返回数组中为列值指定自定义键
DB::table('users')->orderBy('id')->chunk(100, function($users) {
    // 处理结果集...
    return false;
});//分块处理结果集减少内存消耗
$users = DB::table('users')->count();//聚合方法
$price = DB::table('orders')->max('price');//聚合方法
$price = DB::table('orders')->min('price');//聚合方法
$price = DB::table('orders')->avg('price');//聚合方法
$price = DB::table('orders')->sum('price');//聚合方法
$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');//带条件的聚合函数
return DB::table('orders')->where('finalized', 1)->exists();//判断匹配查询条件的结果是否存在
return DB::table('orders')->where('finalized', 1)->doesntExist();//判断匹配查询条件的结果是否存在

查询(Select)

$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();//添加一个查询列到已存在的select子句

原生SQL

$users = DB::table('users')
         ->select(DB::raw('count(*) as user_count, status'))
         ->where('status', '<>', 1)
         ->groupBy('status')
         ->get();
$orders = DB::table('orders')
         ->selectRaw('price * ? as price_with_tax', [1.0825])
         ->get();//selectRaw方法
$orders = DB::table('orders')
         ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
         ->get();//whereRaw方法
$orders = DB::table('orders')
         ->select('department', DB::raw('SUM(price) as total_sales'))
         ->groupBy('department')
         ->havingRaw('SUM(price) > 2500')
         ->get();//havingRaw方法
$orders = DB::table('orders')
         ->orderByRaw('updated_at - created_at DESC')
         ->get();//orderByRaw方法

连接(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();//内连接（等值连接）
$users = DB::table('users')
         ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
         ->get();//左连接

联合(Union)

$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();//多个查询条件数组
$users = DB::table('users')
         ->where('votes', '>', 100)
         ->orWhere('name', 'John')
         ->get();//或者
$users = DB::table('users')
         ->whereBetween('votes', [1, 100])->get();//Between查询
$users = DB::table('users')
         ->whereNotBetween('votes', [1, 100])
         ->get();//NotBetween查询
$users = DB::table('users')
         ->whereIn('id', [1, 2, 3])
         ->get();//In查询
$users = DB::table('users')
          ->whereNotIn('id', [1, 2, 3])
          ->get();//NotIn查询
$users = DB::table('users')
          ->whereNull('updated_at')
          ->get();//定列的值为NULL
$users = DB::table('users')
          ->whereNotNull('updated_at')
          ->get();//定列的值不为NULL
$users = DB::table('users')
         ->whereDate('created_at', '2016-10-10')
         ->get();//指定日期查询
$users = DB::table('users')
         ->whereMonth('created_at', '10')
         ->get();//指定月份查询
$users = DB::table('users')
         ->whereDay('created_at', '10')
         ->get();//指定天查询
$users = DB::table('users')
         ->whereYear('created_at', '2017')
         ->get();//指定年查询
$users = DB::table('users')
         ->whereTime('created_at', '=', '11:20')
         ->get();//指定时间查询
$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();//多个字段查询条件查询
DB::table('users')
        ->where('name', '=', 'John')
        ->orWhere(function ($query) {
            $query->where('votes', '>', 100)
                  ->where('title', '<>', 'Admin');
        })
        ->get();//OR与And同时查询
//等同：select * from users where name = '学院君' or (votes > 100 and title <> 'Admin')
DB::table('users')
        ->whereExists(function ($query) {
            $query->select(DB::raw(1))
                  ->from('orders')
                  ->whereRaw('orders.user_id = users.id');
        })
        ->get();//exists语句
//等同：select * from users where exists (select 1 from orders where orders.user_id = users.id)

排序、分组、限定

$users = DB::table('users')
        ->orderBy('name', 'desc')
        ->get();//orderBy
$user = DB::table('users')
        ->latest()
        ->first();//latest和oldest方法允许你通过日期对结果进行排序
$randomUser = DB::table('users')
        ->inRandomOrder()
        ->first();//随机排序后取一个
$users = DB::table('users')
        ->groupBy('account_id')
        ->having('account_id', '>', 100)
        ->get();//groupBy和having方法用于对结果集进行分组
$users = DB::table('users')->skip(10)->take(5)->get();//跳过给定数目并指定查询返回的结果集的数目
$users = DB::table('users')->offset(10)->limit(5)->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 = DB::table('users')->insertGetId(
            ['email' => 'john@example.com', 'votes' => 0]
        );//插入并返回自增id

更新(Update)

DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);//更新单条
DB::table('users')->increment('votes', 5);//指定字段数值加5
DB::table('users')->decrement('votes', 5);//指定字段数值减5
DB::table('users')->increment('votes', 1, ['name' => 'John']);//votes字段加1，name字段更新

删除(Delete)

DB::table('users')->delete();//全部删除
DB::table('users')->where('votes', '>', 100)->delete();//条件删除
DB::table('users')->truncate();//清空并将自增id置为0

悲观锁&乐观锁

悲观锁（Pessimistic Lock），顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁、表锁、读锁、写锁等，都是在做操作之前先上锁。
乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
//等同：select * from `users` where `votes` > '100' lock in share mode
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
//等同：select * from `users` where `votes` > '100' for update