數據庫 ―― 查詢構建器 · Laravel 5.1 中文文檔

# 數據庫 —— 查詢構建器 ## 1、簡介數據庫。查詢構建器可以用于執行應用中大部分數據庫操作，并且能夠在支持的所有數據庫系統上工作。 > 注意：Laravel查詢構建器使用PDO參數綁定來避免SQL注入攻擊，不再需要過濾傳遞到綁定的字符串。 ## 2、獲取結果集 ### 2.1 從一張表中取出所有行在查詢之前，使用`DB`門面的`table`方法，`table`方法為給定表返回一個查詢構建器，允許你在查詢上鏈接更多約束條件并最終返回查詢結果。在本例中，我們使用`get`方法獲取表中所有記錄： ~~~ <?php namespace App\Http\Controllers; use DB; use App\Http\Controllers\Controller; class UserController extends Controller{ /** * 顯示用戶列表 * * @return Response */ public function index() { $users = DB::table('users')->get(); return view('user.index', ['users' => $users]); } } ~~~ 和[原生查詢](http://laravelacademy.org/post/124.html)一樣，`get`方法返回結果集的數據組，其中每一個結果都是PHP對象的`StdClass`實例。你可以像訪問對象的屬性一樣訪問列的值： ~~~ foreach ($users as $user) { echo $user->name; } ~~~ ### 2.2 從一張表中獲取一行/一列如果你只是想要從數據表中獲取一行數據，可以使用`first`方法，該方法將會返回單個`StdClass`對象： ~~~ $user = DB::table('users')->where('name', 'John')->first(); echo $user->name; ~~~ ### 2.3 從一張表中獲取組塊結果集如果你需要處理成千上百條數據庫記錄，可以考慮使用`chunk`方法，該方法一次獲取結果集的一小塊，然后填充每一小塊數據到要處理的閉包，該方法在編寫處理大量數據庫記錄的[Artisan命令](http://laravelacademy.org/post/170.html)的時候非常有用。比如，我們可以將處理全部users表數據處理成一次處理100記錄的小組塊： ~~~ DB::table('users')->chunk(100, function($users) { foreach ($users as $user) { // } }); ~~~ 你可以通過從閉包函數中返回false來中止組塊的運行： ~~~ DB::table('users')->chunk(100, function($users) { // 處理結果集... return false; }); ~~~ ### 2.4 獲取數據列值列表如果想要獲取包含單個列值的數組，可以使用`lists`方法，在本例中，我們獲取所有`title`的數組： ~~~ $titles = DB::table('roles')->lists('title'); foreach ($titles as $title) { echo $title; } ~~~ 在還可以在返回數組中為列值指定更多的自定義鍵（該自定義鍵必須是該表的其它字段列名，否則會報錯）： ~~~ $roles = DB::table('roles')->lists('title', 'name'); foreach ($roles as $name => $title) { echo $title; } ~~~ ### 2.5 聚合函數隊列構建器還提供了很多聚合方法，比如`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'); ~~~ ## 3、查詢（Select） ### 3.1 指定查詢子句當然，我們并不總是想要獲取數據表的所有列，使用`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(); ~~~ ### 3.2 原生表達式有時候你希望在查詢中使用原生表達式，這些表達式將會以字符串的形式注入到查詢中，所以要格外小心避免被SQL注入。想要創建一個原生表達式，可以使用?`DB::raw`方法： ~~~ $users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get(); ~~~ ## 4、連接（Join） ### 4.1 內連接（等值連接）查詢構建器還可以用于編寫基本的SQL“內連接”，你可以使用查詢構建器實例上的`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(); ~~~ ### 4.2 左連接如果你是想要執行“左連接”而不是“內連接”，可以使用`leftJoin`方法。該方法和`join`方法的使用方法一樣： ~~~ $users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); ~~~ ### 4.3 高級連接語句你還可以指定更多的高級連接子句，傳遞一個閉包到`join`方法作為該方法的第2個參數，該閉包將會返回允許你指定`join`子句約束的`JoinClause`對象： ~~~ 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(); ~~~ ## 5、聯合（Union）查詢構建器還提供了一條“聯合”兩個查詢的快捷方式，比如，你要創建一個獨立的查詢，然后使用`union`方法將其和第二個查詢進行聯合： ~~~ $first = DB::table('users') ->whereNull('first_name'); $users = DB::table('users') ->whereNull('last_name') ->union($first) ->get(); ~~~ `unionAll`方法也是有效的，并且和`union`有同樣的使用方法。 ## 6、Where子句 ### 6.1 簡單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(); ~~~ ### 6.2 or 你可以通過方法鏈將多個`where`約束鏈接到一起，也可以添加`or`子句到查詢，`orWhere`方法和`where`方法接收參數一樣： ~~~ $users = DB::table('users') ->where('votes', '>', 100) ->orWhere('name', 'John') ->get(); ~~~ ### 6.3 更多Where子句 ### 6.3.1 whereBetween `whereBetween`方法驗證列值是否在給定值之間： ~~~ $users = DB::table('users') ->whereBetween('votes', [1, 100])->get(); ~~~ ### 6.3.2 whereNotBetween `whereNotBetween`方法驗證列值不在給定值之間： ~~~ $users = DB::table('users') ->whereNotBetween('votes', [1, 100]) ->get(); ~~~ ### 6.3.3 whereIn/whereNotIn `whereIn`方法驗證給定列的值是否在給定數組中： ~~~ $users = DB::table('users') ->whereIn('id', [1, 2, 3]) ->get(); ~~~ `whereNotIn`方法驗證給定列的值不在給定數組中： ~~~ $users = DB::table('users') ->whereNotIn('id', [1, 2, 3]) ->get(); ~~~ ### 6.3.4 whereNull/whereNotNull `whereNull`方法驗證給定列的值為NULL： ~~~ $users = DB::table('users') ->whereNull('updated_at') ->get(); ~~~ `whereNotNull`方法驗證給定列的值不是NULL： ~~~ $users = DB::table('users') ->whereNotNull('updated_at') ->get(); ~~~ ### 6.4 高級Where子句 ### 6.4.1 參數分組有時候你需要創建更加高級的`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') ~~~ ### 6.4.2 exists語句 `whereExists`方法允許你編寫`where exist`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 ) ~~~ ## 7、排序、分組、限定 ### 7.1 orderBy `orderBy`方法允許你通過給定列對結果集進行排序，`orderBy`的第一個參數應該是你希望排序的列，第二個參數控制著排序的方向——asc或desc： ~~~ $users = DB::table('users') ->orderBy('name', 'desc') ->get(); ~~~ ### 7.2 groupBy / having / havingRaw `groupBy`和`having`方法用于對結果集進行分組，`having`方法和`where`方法的用法類似： ~~~ $users = DB::table('users') ->groupBy('account_id') ->having('account_id', '>', 100) ->get(); ~~~ `havingRaw`方法可以用于設置原生字符串作為`having`子句的值，例如，我們要找到所有售價大于`$2500`的部分： ~~~ $users = DB::table('orders') ->select('department', DB::raw('SUM(price) as total_sales')) ->groupBy('department') ->havingRaw('SUM(price) > 2500') ->get(); ~~~ ### 7.3?skip / take 想要限定查詢返回的結果集的數目，或者在查詢中跳過給定數目的結果，可以使用`skip`和`take`方法： ~~~ $users = DB::table('users')->skip(10)->take(5)->get(); ~~~ ## 8、插入（Insert）查詢構建器還提供了`insert`方法來插入記錄到數據表。`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] ]); ~~~ ### 8.1 自增ID 如果數據表有自增ID，使用`insertGetId`方法來插入記錄將會返回ID值： ~~~ $id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0] ); ~~~ > 注意：當使用`PostgresSQL`時`insertGetId`方法默認自增列被命名為`id`，如果你想要從其他”序列“獲取ID，可以將序列名作為第二個參數傳遞到`insertGetId`方法。 ## 9、更新（Update）當然，除了插入記錄到數據庫，查詢構建器還可以通過使用`update`方法更新已有記錄。`update`方法和`insert`方法一樣，接收列和值的鍵值對數組包含要更新的列，你可以通過`where`子句來對`update`查詢進行約束： ~~~ DB::table('users') ->where('id', 1) ->update(['votes' => 1]); ~~~ ### 9.1 增加/減少查詢構建器還提供了方便增減給定列名數值的方法。相較于編寫`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']); ~~~ ## 10、刪除（Delete）當然，查詢構建器還可以通過`delete`方法從表中刪除記錄： ~~~ DB::table('users')->delete(); ~~~ 在調用`delete`方法之前可以通過添加`where`子句對`delete`語句進行約束： ~~~ DB::table('users')->where('votes', '<', 100)->delete(); ~~~ 如果你希望清除整張表，也就是刪除所有列并將自增ID置為0，可以使用`truncate`方法： ~~~ DB::table('users')->truncate(); ~~~ ## 11、悲觀鎖查詢構建器還包含一些方法幫助你在select語句中實現”悲觀鎖“。可以在查詢中使用`sharedLock`方法從而在運行語句時帶一把”共享鎖“。共享鎖可以避免被選擇的行被修改直到事務提交： ~~~ DB::table('users')->where('votes', '>', 100)->sharedLock()->get(); ~~~ 此外你還可以使用`lockForUpdate`方法。”for update“鎖避免選擇行被其它共享鎖修改或刪除： ~~~ DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get(); ~~~ > 擴展閱讀 1：[實例教程 —— 使用查詢構建器對數據庫進行增刪改查](http://laravelacademy.org/post/908.html) > 擴展閱讀?2：[實例教程 —— 使用查詢構建器實現對數據庫的高級查詢](http://laravelacademy.org/post/920.html)