數(shù)據(jù)庫：查詢生成器

簡介

Laravel 的數(shù)據(jù)庫查詢生成器提供了一種方便流利的接口來構(gòu)建和運行數(shù)據(jù)庫查詢。它可以用來在應用中提供執(zhí)行大多數(shù)的數(shù)據(jù)庫操作，并且它可以在所有支持的數(shù)據(jù)庫系統(tǒng)中進行協(xié)作。

注意：Laravel 的查詢生成器使用了 PDO 參數(shù)綁定來針對可能的 SQL 注入攻擊涝影。所以這里不需要手動的去清洗作為綁定所傳遞的字符串痹届。

檢索結(jié)果

從表中檢索所有的行

在開始流暢的執(zhí)行查詢之前，你需要先使用 DB 假面的 table 方法薇组，table 方法會返回一個給定表的查詢生成器實例，這允許你鏈式添加多種查詢約束坐儿，并最終獲取結(jié)果律胀。我們來看一個示例，我們只是調(diào)用 get 來獲取所有的表中的記錄：

<?php

namespace App\Http\Controllers;

use 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 方法會返回一個 array 炭菌，其每一項結(jié)果都是一個 StdClass PHP 類的對象。你可以通過其屬性來訪問每一列的值：

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

從表中檢索一個單獨的行或者列

如果你只需要從數(shù)據(jù)表中檢索出單獨的一行逛漫，你可以使用 first 方法黑低，該方法會返回一個單獨的 StdClass 對象：

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

echo $user->name;

如果你甚至不需要一個完整的行，那么你可以通過使用 value 方法從記錄中獲取一個精確的列的值酌毡。該方法會直接返回列中的值：

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

對結(jié)果進行分塊

如果你與表中的數(shù)千條記錄協(xié)作克握，你可以考慮使用 chunk 方法，該方法一次會從結(jié)果中檢索出一小塊枷踏。并且將這每一塊放進閉包中提供給進程菩暗。該方法對于編寫 Artisan 命令來協(xié)作數(shù)千條的記錄非常有用。比如呕寝，讓我們將整個用戶表分塊成每次 100 條來工作：

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

你可以通過在 Closure 中返回 false 來停止進一步的處理塊的操作：

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

  return false; 
});

檢索表列的值

如果你希望獲取一個數(shù)組來包含一個列中所有的值勋眯，你可以使用 pluck 方法，在這個例子中下梢，我們將獲取所有的角色名稱：

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

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

你也可以為所返回的數(shù)組指定自定義的鍵：

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

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

合計

查詢生成器也提供了多種合計的方法客蹋。比如 count，max孽江，min讶坯，avg，和 sum岗屏。你可以在查詢構(gòu)建后調(diào)用這些方法：

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

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

當然辆琅，你可以結(jié)合其它約束來構(gòu)建你的查詢：

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

選擇

指定一個選擇約束

當然，你不會總是想要從數(shù)據(jù)庫中獲取所有的列这刷。你可以使用 select 方法來指定一個自定義的 select 約束到查詢：

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

distinct 方法允許你強迫查詢返回一個不重復的結(jié)果：

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

如果你已經(jīng)獲取了查詢生成器的實例婉烟，并且你想要在已經(jīng)添加過選擇約束的實例中添加額外一列的檢索，你可以使用 addSelect 方法：

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

$users = $query->addSelect('age')->get();

原生的表達式

有時候暇屋，你可能需要在查詢時使用原生的表達式似袁，這些表達式會以字符串的方式注入到查詢中，所以你應該小心的不要構(gòu)造出任何的 SQL 注入點。你可以使用 DB::raw 方法來構(gòu)造一個原生的表達式：

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

Joins

內(nèi)連接聲明

查詢生成器也可以用來編寫 join 聲明昙衅。為了執(zhí)行一個基礎的 SQL 內(nèi)連接扬霜，你可以在查詢生成器中使用 join 方法。join 方法所接收的第一個參數(shù)應該是你需要加入的表的名稱而涉，而其它的參數(shù)則是對加入提供約束著瓶。當然，就如你所看到的啼县，你可以在一個查詢中加入多個表：

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

左連接聲明

如果你需要執(zhí)行 left join 來代替 inner join材原，你可以使用 leftJoin 方法，leftJoin 方法具有和 join 方法相同的使用方法：

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

交叉連接聲明

你可以使用 crossJoin 方法來執(zhí)行一個交叉的連接操作季眷，你需要為 crossJoin 提供一個你想要交叉連接的表名华糖。交叉連接會生成第一個表和加入的表的笛卡爾積：

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

高級的 Join 聲明

你也可以指定更高級的 join 約束。你可以傳遞一個 Closure 作為 join 方法的第二個參數(shù)瘟裸。Closure 會接收一個 JoinClause 對象并且該對象允許你指定 join 約束：

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

如果你喜歡在 joins 中使用 where 風格約束，那么你可以在 join 上使用 where 和 orWhere 方法诵竭。該方法會比較列的值而取代直接比較兩列：

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

Unions（聯(lián)合）

查詢生成器也提供了一種快捷的方式來將兩個查詢聯(lián)合起來话告。比如，你可以構(gòu)建一個初始化的查詢卵慰，然后使用 union 方法來將其與之后的查詢聯(lián)合起來：

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

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

unionAll 方法也是可用的沙郭，并且它與 union 方法具有相同的簽證方式。

Where 子句

簡單的 Where 子句

你可以在查詢生成器中使用 where 方法來在查詢中添加 where 約束病线。大多數(shù)基礎的 where 調(diào)用都需要提供三個參數(shù)鲤嫡，第一個參數(shù)是列的名稱，第二個參數(shù)是比較操作的方式暖眼，第三個參數(shù)則是針對列所需要評估的值。

比如诫肠，這里的查詢用來驗證 "votes" 列的值等于 100:

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

為了方便，你可以直接傳遞需要比較的值到第二個參數(shù)來表明列值與給定值相等的判斷：

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

當然栋豫，你也可以在使用 where 子句時使用其它的操作符：

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

你也可以傳遞一個數(shù)組所組成的條件到 where 方法中：

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

或聲明

你可以將 where 約束連在一起挤安，以及添加 or 子句到查詢中。orWhere 方法接收和 where 方法相同的參數(shù)：

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

其它 Where 子句

whereBetween

whereBetween 方法確定列的值是否在兩個給定值之間：

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

whereNotBetween

whereNotBetween 方法確定列的值應該不在所給定值區(qū)間內(nèi)：

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

whereIn / whereNotIn

whereIn 方法驗證給定的列的值應該是給定的數(shù)組值中之一：

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

whereNotIn 方法驗證所給定的列的值應該不在給定的數(shù)組中：

$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::('users')
           ->whereNotNull('updated_at')
           ->get();

whereColumn

whereColumn 方法可以用來判斷兩列的值是否相等：

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

你也可以傳遞一個比較符到方法中：

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

whereColumn 方法可以接收一個包含了多個限制條件的數(shù)組丧鸯。這些條件會被使用 add 判定操作：

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

高級 Where 子句

參數(shù)分組

有時候你需要構(gòu)建一些更加高級的 where 子句蛤铜，比如，“where exists”或者嵌套的參數(shù)分組。Laravel 的查詢生成器也能很好的處理這些昂羡。讓我們來看一個分組括號內(nèi)約束的例子：

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

就如你所看到的絮记，傳遞一個 Closure 到 orWhere 方法來指導查詢生成器開始一個組約束。Closure 會接收一個查詢生成器的實例虐先，這樣你就可以在括號組內(nèi)構(gòu)建一些約束怨愤。上面的示例將會生成下面的 SQL：

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

Exists 聲明

whereExists 方法允許你編寫 where exists SQL 子句。whereExists 方法接收一個 Closure 參數(shù)蛹批，這個閉包會接收一個查詢生成器實例撰洗，這允許你在 "exists" 子句中去構(gòu)建查詢：

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 列類型的數(shù)據(jù)庫查詢 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();

排序帜消，分組猪勇，限制 & 位移

orderBy

orderBy 方法允許你對給定列的查詢結(jié)果進行排序设褐。orderBy 所接受的第一個參數(shù)應該是你希望進行排序的列，而第二個參數(shù)應該是你決定進行排序的方向泣刹，它們應該是 asc 或者 desc:

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

inRandomOrder

inRandomOrder 方法可以用來對查詢的結(jié)果進行隨機排序助析。比如，你可以使用這個方法來隨機的獲取用戶：

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

groupBy / having / havingRaw

groupBy 和 having 方法可以用來對查詢結(jié)果進行分組椅您。having 方法具有和 where 方法相同的簽證：

$users = DB::table('users')
           ->groupBy('account_id')
           ->having('account_id', '>', 100)
           ->get();

havingRaw 方法可以用來在 having 子句中使用原生的字符串值外冀，比如，我們可以找到所有銷售額大于 $2,500 的部門：

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

skip / take

你可以使用 take 和 skip 方法來限制查詢所返回結(jié)果的數(shù)量和在查詢中跳過一定的數(shù)目（offset）：

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

條件語句

有時候你可能希望只有當某些條件達成時才去添加一些查詢到語句中掀泳。又或者你想要在只有進入的請求數(shù)據(jù)中含有指定的字段時才會判斷在 where 子句中進行加入雪隧。你可以使用 when 方法來做到這些：

$role = $request->input('role');

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

when 方法只有所給定的第一個參數(shù)為 true 時才會執(zhí)行給定的閉包。如果第一個參數(shù)是 false员舵，那么閉包將不會被執(zhí)行固灵。

插入

查詢生成器也為插入記錄到數(shù)據(jù)庫中提供了 insert 方法。insert 方法可以接受列名所組成的鍵值對數(shù)組作為參數(shù)：

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

你也可以在一次調(diào)用 insert 方法時添加多條記錄到數(shù)據(jù)庫中丛忆，你需要傳遞一個嵌套的數(shù)組熄诡，數(shù)組中的每一個數(shù)組都代表了數(shù)據(jù)庫中的一行：

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

自動遞增的 IDs

如果挑中存在自動遞增的 id，你可以使用 insertGetId 方法來插入記錄的同時獲取 ID：

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

注意：當使用 PostgreSQL 時诗力，insertGetId 方法期望自增的列名為 id我抠。如果你想要檢索的 ID 是一個其他列名菜拓，你需要傳遞列名到 insertGetId 方法的第二個參數(shù)纳鼎。

更新

當然裳凸，除了新增記錄之外，查詢生成器也提供了為數(shù)據(jù)庫中存在的記錄更新的 update 方法逗宁。update 方法像 insert 方法一樣瞎颗，接收一個鍵值對所組成的數(shù)組來進行列值的更新言缤。你可以在使用 update 查詢時使用 where 子句：

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

增量 / 遞減

查詢生成器也提供一些方便的方法來對列的值進行增量和遞減。這只是一種快捷的方式轿曙，用來提供比手動寫入 update 語句更具表現(xiàn)力和簡潔的接口。

這兩種方法都接收最少一個參數(shù)：待修改的列名守谓，第二個參數(shù)是一個可選的參數(shù)斋荞，它用來控制遞增或者遞減的數(shù)量：

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

刪除

當然平酿，查詢生成器也提供了從數(shù)據(jù)庫中刪除記錄的 delete 方法：

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

你可以在調(diào)用 delete 方法之前添加一些 where 子句：

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

如果你希望清除整個表的數(shù)據(jù)蜈彼，你可以使用 truncate 方法幸逆，它將清除所有的行并且重置自增的 ID 到 0：

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

悲觀鎖

查詢生成器也提供了一些方法來幫助你在 select 聲明中實現(xiàn)“悲觀鎖”还绘。你可以在查詢中使用 sharedLock 方法來運行“共享鎖”。共享鎖可以保證所選定的行在事務提交前不會被修改：

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

另外抚太，你也可以使用 lockForUpdate 方法凭舶。更新鎖可以防止行被修改或者被另一共享鎖所選中：

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