查詢構造器 · laravel 5.3 中文文檔

# 數據庫：查詢構造器 - [簡介](#introduction) - [獲取結果](#retrieving-results) - [結果分塊](#chunking-results) - [聚合](#aggregates) - [Selects](#selects) - [原始表達式](#raw-expressions) - [Joins](#joins) - [Unions](#unions) - [Where 子句](#where-clauses) - [參數分組](#parameter-grouping) - [Where Exists 語法](#where-exists-clauses) - [JSON 查詢語句](#json-where-clauses) - [Ordering, Grouping, Limit, 及 Offset](#ordering-grouping-limit-and-offset) - [條件語句](#conditional-clauses) - [Inserts](#inserts) - [Updates](#updates) - [更新 JSON](#updating-json-columns) - [遞增或遞減](#increment-and-decrement) - [Deletes](#deletes) - [悲觀鎖定](#pessimistic-locking) <a name="introduction"></a> ## 簡介 Laravel 的數據庫查詢構造器提供了方便、流暢的接口，以用來創建及運行數據庫查詢。可用來執行應用程序中的大部分數據庫操作，且能在所有被支持的數據庫系統中使用。 Laravel 的查詢構造器使用 PDO 參數綁定，以保護你的應用程序不受數據庫注入攻擊。在傳入字符串作為綁定前不需要先清理它們。 <a name="retrieving-results"></a> ## 獲取結果 #### 從數據表中獲取所有的數據列若要開始進行查找，可在 `DB` facade 上使用 `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` 方法。在這個例子中，我們將取出 roles 數據表 title 字段的數組： $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; } <a name="chunking-results"></a> ### 結果分塊若你需要操作數千條數據庫記錄，則可考慮使用 `chunk` 方法。這個方法一次只取出一小「塊」結果，并會將每個區塊傳給一個`閉包`進行處理。這個方法對于要編寫處理數千條記錄的 [Artisan 命令](/docs/{{version}}/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; }); <a name="aggregates"></a> ### 聚合查詢構造器也提供了各種聚合方法，例如 `count`、`max`、`min`、`avg` 以及 `sum`。你可以在創建查找后調用其中的任意一個方法： $users = DB::table('users')->count(); $price = DB::table('orders')->max('price'); 當然，你也可以將這些方法合并到其它的子句上來構建查找： $price = DB::table('orders') ->where('finalized', 1) ->avg('price'); <a name="selects"></a> ## Selects #### 指定一個 Select 子句當然，你并不會總是想從數據表中選出所有的字段。這時可以使用 `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(); <a name="raw-expressions"></a> ## 原始表達式有時你可能需要在查詢中使用原始表達式。這些表達式會被當作字符串注入到查找中，因此要小心避免造成數據庫注入攻擊！要創建一個原始表達式，可以使用 `DB::raw` 方法： $users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get(); <a name="joins"></a> ## Joins #### Inner Join 語法查詢構造器也可用來編寫 join 語法。要操作基本的 SQL「inner join」，則可以在查詢構造器實例上使用 `join` 方法。傳入 `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 語法如果你想以操作「left join」來代替「inner join」，請使用 `leftJoin` 方法。`leftJoin` 方法和 `join` 方法有著相同的簽名： $users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); #### Cross Join 語法使用 `crossJoin` 方法和你想要交叉連接的表名來做「交叉連接」。交叉連接通過第一個表和連接表生成一個笛卡爾積： $users = DB::table('sizes') ->crossJoin('colours') ->get(); #### 高級的 Join 語法你也可以指定更高級的 join 子句。讓我們以傳入一個`閉包`當作 `join` 方法的第二參數來作為開始。此`閉包`會接收 `JoinClause` 對象，讓你可以在 `join` 子句上指定約束： DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get(); 若你想要在連接中使用「where」風格的子句，則可以在連接中使用 `where` 和 `orWhere` 方法。這些方法會比較字段和一個值，來代替兩個字段的比較： DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get(); <a name="unions"></a> ## Unions 查詢語句構造器也提供了一個快捷的方法來「合并」兩個查找。例如，你可以先創建一個初始查找，然后使用 `union` 方法將它與第二個查找進行合并： $first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get(); > {tip} 也可使用 `unionAll` 方法，它和 `union` 有著相同的方法簽名。 <a name="where-clauses"></a> ## Where 子句 #### 簡單的 Where 子句你可以在查詢語句構造器實例中使用 `where` 方法從而在查找中加入 `where` 子句。基本的 `where` 方法調用需要三個參數。第一個參數是字段的名稱；第二個參數是一個運算符，它可以是數據庫所支持的任何運算符；第三個參數是要對字段進行評估的值。例如，這是一個要驗證「votes」字段的值等于 100 的查找： $users = DB::table('users')->where('votes', '=', 100)->get(); 為了方便起見，若你只想簡單地驗證某個字段等于一個指定的值，則可以直接將這個值作為第二個參數傳入 `where` 方法： $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(); 你也可以通過一個條件數組來做 `where` 的查詢： $users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '<>', '1'], ])->get(); #### Or 語法你也可以在查找中加入 `or` 子句來跟 where 約束鏈式調用在一起。`orWhere` 方法和 `where` 方法接受相同的參數： $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` 方法驗證一個字段的值不在兩個值之內： $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-10-10') ->get(); `whereMonth` 方法可以用來比較一列的值和一年當中某一月是否相等： $users = DB::table('users') ->whereMonth('created_at', '10') ->get(); `whereDay` 方法可以用來比較一列的值和一月當中的某一天是否相等： $users = DB::table('users') ->whereDay('created_at', '10') ->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(); <a name="parameter-grouping"></a> ### 參數分組有時你可能會需要創建更高級的 where 子句，例如「where exists」或者嵌套的參數分組。Laravel 的查詢語句構造器也能處理這些。讓我們先來看下一個在括號中將約束分組的例子： DB::table('users') ->where('name', '=', 'John') ->orWhere(function ($query) { $query->where('votes', '>', 100) ->where('title', '<>', 'Admin'); }) ->get(); 如你所見，上面例子會將`閉包`傳入 `orWhere` 方法，以告訴查詢語句構造器開始一個約束分組。此`閉包`接收一個查詢語句構造器的實例，你可用它來設置應包含在括號分組內的約束。上面的例子會生成以下 SQL： select * from users where name = 'John' or (votes > 100 and title <> 'Admin') <a name="where-exists-clauses"></a> ### Where Exists 語法 `whereExists` 方法允許你編寫 `where exists` SQL 子句。此方法會接受一個`閉包`參數，此閉包接收一個查詢語句構造器實例，讓你可以定義應放在「exists」SQL 子句中的查找： 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 ) <a name="json-where-clauses"></a> ### JSON 查詢語句 Laravel 支持 MySQL 5.7 以上版本和 Postgres 數據庫的 JSON 類型的字段查詢。可以使用 `->` 運算符來查詢 JSON 列數據： $users = DB::table('users') ->where('options->language', 'en') ->get(); $users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get(); <a name="ordering-grouping-limit-and-offset"></a> ## Ordering, Grouping, Limit 及 Offset #### orderBy `orderBy` 方法允許你針對指定字段將查找結果進行排序。`orderBy` 的第一個參數為你要用來排序的字段，第二個參數則控制排序的順序，可以是 `asc` 或 `desc`： $users = DB::table('users') ->orderBy('name', 'desc') ->get(); #### inRandomOrder `inRandomOrder` 會對數據結果進行隨機排序，例如以下讀取隨機用戶： $randomUser = DB::table('users') ->inRandomOrder() ->first(); #### groupBy / having / havingRaw `groupBy` 和 `having` 方法可用來將查找結果進行分組。`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 要限制查找所返回的結果數量，或略過指定數量的查找結果（`偏移`），則可使用 `skip` 和 `take` 方法： $users = DB::table('users')->skip(10)->take(5)->get(); <a name="conditional-clauses"></a> ## 條件查詢語句有時候，你希望某個值為 true 的時候才執行查詢，例如，如果一個請求中存在給定的輸入值的時候才執行這個 `where` 語句，你可以使用 `when` 方法實現： $role = $request->input('role'); $users = DB::table('users') ->when($role, function ($query) use ($role) { return $query->where('role_id', $role); }) ->get(); 只有當 `when` 的第一個參數為 `true` 的話，匿名函數里的 `where` 語句才會被執行。如果第一個參數是 `false` 閉包將不會被執行。 <a name="inserts"></a> ## Inserts 查詢語句構造器也提供了 `insert` 方法，用來將記錄插入數據表。`insert` 方法接收一個數組，包含要插入的字段名稱及值： DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0] ); 你甚至可以在 `insert` 調用中傳入一個嵌套數組，來一次性插入多條記錄到數據表中。每個數組代表要插入數據表中的一列記錄： DB::table('users')->insert([ ['email' => 'taylor@example.com', 'votes' => 0], ['email' => 'dayle@example.com', 'votes' => 0] ]); #### 自動遞增 ID 若數據表有自動遞增的 id，則可使用 `insertGetId` 方法來插入記錄并獲取其 ID： $id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0] ); > {note} 當使用 PostgreSQL 時，insertGetId 方法將預測自動遞增字段的名稱為 `id`。若你要從不同「順序」來獲取 ID，則可以將順序名稱作為第二個參數傳給 `insertGetId` 方法。 <a name="updates"></a> ## Updates 當然，除了可在數據庫中插入記錄之外，也可使用 `update` 方法來讓查詢語句構造器更新已存在的記錄。`update` 方法和 `insert` 方法一樣，接收含有一對字段及值的數組，其中包含了要被更新的字段。可使用 `where` 子句來約束 `update` 查找： DB::table('users') ->where('id', 1) ->update(['votes' => 1]); <a name="updating-json-columns"></a> ### Updating JSON Columns 更新 JSON 列的時候，你可以使用 `->` 語法在 JSON 對象中訪問想要的鍵。只有當數據庫支持 JSON 列的時候才支持這個操作： DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]); <a name="increment-and-decrement"></a> ### 遞增或遞減查詢語句構造器也提供了便利的方法來遞增或遞減指定字段的值。此方法提供了一個比手動編寫 `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']); <a name="deletes"></a> ## Deletes 查詢語句構造器也可通過 `delete` 方法來將記錄從數據表中刪除。在調用 `delete` 方法之前，也可加上 `where` 子句來約束 `delete` 語法： DB::table('users')->delete(); DB::table('users')->where('votes', '>', 100)->delete(); 若你希望截去整個數據表的所有數據列，并將自動遞增 ID 重設為零，則可以使用 `truncate` 方法： DB::table('users')->truncate(); <a name="pessimistic-locking"></a> ## 悲觀鎖定查詢語句構造器也包含一些可用以協助你在 `select` 語法上作「悲觀鎖定」的函數。若要以「共享鎖」來運行語句，則可在查找上使用 `sharedLock` 方法。共享鎖可避免選擇的數據列被更改，直到事務被提交為止： DB::table('users')->where('votes', '>', 100)->sharedLock()->get(); 此外，你也可以使用 `lockForUpdate` 方法。「用以更新」鎖可避免數據列被其它共享鎖修改或選取： DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get(); ## 譯者署名 | 用戶名 | 頭像 | 職能 | 簽名 | |---|---|---|---| | [@賀鈞威](https://phphub.org/users/5711) | <img class="avatar-66 rm-style" src="https://dn-phphub.qbox.me/uploads/avatars/5711_1473489317.jpg?imageView2/1/w/100/h/100"> | 翻譯 | 感謝[BlueStone](http://bluestoneapp.thexrverge.com/)翻譯支持，[@賀鈞威](https://github.com/HejunweiCoder/) at Github |