查詢構(gòu)造器
查詢構(gòu)造器
數(shù)據(jù)庫:查詢構(gòu)造器
簡介
Laravel 的數(shù)據(jù)庫查詢構(gòu)造器為創(chuàng)建和運(yùn)行數(shù)據(jù)庫查詢提供了一個方便的接口。它可用于執(zhí)行應(yīng)用程序中大部分?jǐn)?shù)據(jù)庫操作,且可在所有支持的數(shù)據(jù)庫系統(tǒng)上運(yùn)行。
Laravel 的查詢構(gòu)造器使用 PDO 參數(shù)綁定來保護(hù)您的應(yīng)用程序免受 SQL 注入攻擊。因此沒有必要清理作為綁定傳遞的字符串。
獲取結(jié)果
從一個數(shù)據(jù)表中獲取所有行
你可以 DB
facade
上使用 table
方法來開始查詢。該 table
方法為給定的表返回一個查詢構(gòu)造器實例,允許你在查詢上鏈?zhǔn)秸{(diào)用更多的約束,最后使用 get
方法獲取結(jié)果:
<?php namespace App\Http\Controllers; use Illuminate\Support\Facades\DB; use App\Http\Controllers\Controller; class UserController extends Controller{ /** * 顯示所有應(yīng)用程序的用戶列表。 * * @return Response */ public function index() { $users = DB::table('users')->get(); return view('user.index', ['users' => $users]); } }
該 get
方法返回一個包含 Illuminate\Support\Collection
的結(jié)果,其中每個結(jié)果都是 PHP
StdClass
對象的一個實例。你可以訪問字段作為對象的屬性來訪問每列的值:
foreach ($users as $user) { echo $user->name; }
從數(shù)據(jù)表中獲取單行或單列
如果你只需要從數(shù)據(jù)表中獲取一行數(shù)據(jù),你可以使用 first
方法。該方法返回一個 StdClass
對象:
$user = DB::table('users')->where('name', 'John')->first(); echo $user->name;
如果你甚至不需要整行數(shù)據(jù),則可以使用 value
方法從記錄中獲取單個值。該方法將直接返回該字段的值:
$email = DB::table('users')->where('name', 'John')->value('email');
獲取一列的值
如果你想獲取包含單列值的集合,則可以使用 pluck
方法。在下面的例子中,我們將獲取角色表中標(biāo)題的集合:
$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; }
分塊結(jié)果
如果你需要處理上千條數(shù)據(jù)庫記錄,你可以考慮使用 chunk
方法。該方法一次獲取結(jié)果集的一小塊,并將其傳遞給 閉包
函數(shù)進(jìn)行處理。該方法在 Artisan 命令 編寫數(shù)千條處理數(shù)據(jù)的時候非常有用。例如,我們可以將全部 users
表數(shù)據(jù)切割成一次處理 100 條記錄的一小塊:
DB::table('users')->orderBy('id')->chunk(100, function ($users) { foreach ($users as $user) { // } });
你可以通過在 閉包
中返回 false
來終止繼續(xù)獲取分塊結(jié)果:
DB::table('users')->orderBy('id')->chunk(100, function ($users) { // Process the records... return false; });
如果要在分塊結(jié)果時更新數(shù)據(jù)庫記錄,則塊結(jié)果可能會和預(yù)計的返回結(jié)果不一致。 因此,在分塊更新記錄時,最好使用 chunkById
方法。 此方法將根據(jù)記錄的主鍵自動對結(jié)果進(jìn)行分頁:
DB::table('users')->where('active', false) ->chunkById(100, function ($users) { foreach ($users as $user) { DB::table('users') ->where('id', $user->id) ->update(['active' => true]); } });
{提示} 在塊的回調(diào)里面更新或刪除記錄時,對主鍵或外鍵的任何更改都可能影響塊查詢。 這可能會導(dǎo)致記錄沒有包含在分塊結(jié)果中。
聚合
查詢構(gòu)造器還提供了各種聚合方法,比如 count
, max
,min
, avg
,還有 sum
。你可以在構(gòu)造查詢后調(diào)用任何方法:
$users = DB::table('users')->count(); $price = DB::table('orders')->max('price');
當(dāng)然,你也可以將這些聚合方法與其他的查詢語句相結(jié)合:
$price = DB::table('orders') ->where('finalized', 1) ->avg('price');
判斷記錄是否存在
除了通過 count
方法可以確定查詢條件的結(jié)果是否存在之外,還可以使用 exists
和 doesntExist
方法:
return DB::table('orders')->where('finalized', 1)->exists(); return DB::table('orders')->where('finalized', 1)->doesntExist();
Selects
指定一個 Select 語句
當(dāng)然你可能并不總是希望從數(shù)據(jù)庫表中獲取所有列。使用 select
方法,你可以自定義一個 select
查詢語句來查詢指定的字段:
$users = DB::table('users')->select('name', 'email as user_email')->get();
distinct
方法會強(qiáng)制讓查詢返回的結(jié)果不重復(fù):
$users = DB::table('users')->distinct()->get();
如果你已經(jīng)有了一個查詢構(gòu)造器實例,并且希望在現(xiàn)有的查詢語句中加入一個字段,那么你可以使用 addSelect
方法:
$query = DB::table('users')->select('name'); $users = $query->addSelect('age')->get();
原生表達(dá)式
有時候你可能需要在查詢中使用原生表達(dá)式。你可以使用 DB::raw
創(chuàng)建一個原生表達(dá)式:
$users = DB::table('users') ->select(DB::raw('count(*) as user_count, status')) ->where('status', '<>', 1) ->groupBy('status') ->get();
{提示} 原生表達(dá)式將會被當(dāng)做字符串注入到查詢中,因此你應(yīng)該小心使用,避免創(chuàng)建 SQL 注入的漏洞。
原生方法
可以使用以下方法代替 DB::raw
,將原生表達(dá)式插入查詢的各個部分。
selectRaw
selectRaw
方法可以代替 select(DB::raw(...))
。該方法的第二個參數(shù)是可選項,值是一個綁定參數(shù)的數(shù)組:
$orders = DB::table('orders') ->selectRaw('price * ? as price_with_tax', [1.0825]) ->get();
whereRaw / orWhereRaw
whereRaw
和 orWhereRaw
方法將原生的 where
注入到你的查詢中。這兩個方法的第二個參數(shù)還是可選項,值還是綁定參數(shù)的數(shù)組:
$orders = DB::table('orders') ->whereRaw('price > IF(state = "TX", ?, 100)', [200]) ->get();
havingRaw / orHavingRaw
havingRaw
和 orHavingRaw
方法可以用于將原生字符串設(shè)置為 having
語句的值:
$orders = DB::table('orders') ->select('department', DB::raw('SUM(price) as total_sales')) ->groupBy('department') ->havingRaw('SUM(price) > ?', [2500]) ->get();
orderByRaw
orderByRaw
方法可用于將原生字符串設(shè)置為 order by
子句的值:
$orders = DB::table('orders') ->orderByRaw('updated_at - created_at DESC') ->get();
Joins
Inner Join Clause
查詢構(gòu)造器也可以編寫 join
方法。若要執(zhí)行基本的
「內(nèi)鏈接」,你可以在查詢構(gòu)造器實例上使用 join
方法。傳遞給 join
方法的第一個參數(shù)是你需要連接的表的名稱,而其他參數(shù)則使用指定連接的字段約束。你還可以在單個查詢中連接多個數(shù)據(jù)表:
$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 語句
如果你想使用 「左連接」或者 「右連接」代替「內(nèi)連接」 ,可以使用 leftJoin
或者 rightJoin
方法。這兩個方法與 join
方法用法相同:
$users = DB::table('users') ->leftJoin('posts', 'users.id', '=', 'posts.user_id') ->get(); $users = DB::table('users') ->rightJoin('posts', 'users.id', '=', 'posts.user_id') ->get();
Cross Join 語句
使用 crossJoin
方法和你想要連接的表名做 「交叉連接」。交叉連接在第一個表和被連接的表之間會生成笛卡爾積:
$users = DB::table('sizes') ->crossJoin('colours') ->get();
高級 Join 語句
你可以指定更高級的 join 語句。比如傳遞一個 閉包
作為 join
方法的第二個參數(shù)。此 閉包
接收一個JoinClause
對象,從而指定 join
語句中指定的約束:
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id')->orOn(...); }) ->get();
如果你想要在連接上使用「where」 風(fēng)格的語句,你可以在連接上使用 where
和 orWhere
方法。這些方法會將列和值進(jìn)行比較,而不是列和列進(jìn)行比較:
DB::table('users') ->join('contacts', function ($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get();
子連接查詢
你可以使用 joinSub
,leftJoinSub
和 rightJoinSub
方法關(guān)聯(lián)一個查詢作為子查詢。他們每一種方法都會接收三個參數(shù):子查詢,表別名和定義關(guān)聯(lián)字段的閉包:
$latestPosts = DB::table('posts') ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at')) ->where('is_published', true) ->groupBy('user_id');$users = DB::table('users') ->joinSub($latestPosts, 'latest_posts', function($join) { $join->on('users.id', '=', 'latest_posts.user_id'); }) ->get();
Unions
查詢構(gòu)造器還提供了將兩個查詢 「聯(lián)合」 的快捷方式。比如,你可以先創(chuàng)建一個查詢,然后使用 union
方法將其和第二個查詢進(jìn)行聯(lián)合:
$first = DB::table('users') ->whereNull('first_name');$users = DB::table('users') ->whereNull('last_name') ->union($first) ->get();
{提示} 你也可以使用
unionAll
方法,用法union
方法是的一樣。
Where 語句
簡單的 Where 語句
在構(gòu)造 where
查詢實例的中,你可以使用 where
方法。調(diào)用 where
最基本的方式是需要傳遞三個參數(shù):第一個參數(shù)是列名,第二個參數(shù)是任意一個數(shù)據(jù)庫系統(tǒng)支持的運(yùn)算符,第三個是該列要比較的值。
例如,下面是一個要驗證 「votes」 字段的值等于 100 的查詢:
$users = DB::table('users')->where('votes', '=', 100)->get();
為了方便,如果你只是簡單比較列值和給定數(shù)值是否相等,可以將數(shù)值直接作為 where
方法的第二個參數(shù):
$users = DB::table('users')->where('votes', 100)->get();
當(dāng)然,你也可以使用其他的運(yùn)算符來編寫 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
函數(shù)中:
$users = DB::table('users')->where([ ['status', '=', '1'], ['subscribed', '<>', '1'], ])->get();
Or 語句
你可以一起鏈?zhǔn)秸{(diào)用 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
方法驗證字段值是否在給定的兩個值之外:
$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::table('users') ->whereNotNull('updated_at') ->get();
whereDate / whereMonth / whereDay / whereYear / whereTime
whereDate
方法用于比較字段值與給定的日期:
$users = DB::table('users') ->whereDate('created_at', '2018-09-08') ->get();
whereMonth
方法用于比較字段值與一年中指定的月份:
$users = DB::table('users') ->whereMonth('created_at', '9') ->get();
whereDay
方法用于比較字段值與一月中指定的日期:
$users = DB::table('users') ->whereDay('created_at', '8') ->get();
whereYear
方法用于比較字段值與指定的年份:
$users = DB::table('users') ->whereYear('created_at', '2018') ->get();
whereTime
方法用于比較字段值與指定的時間(時分秒):
$users = DB::table('users') ->whereTime('created_at', '=', '11:20:45') ->get();
whereColumn
whereColumn
方法用于比較兩個字段的值 是否相等:
$users = DB::table('users') ->whereColumn('first_name', 'last_name') ->get();
你也可以傳入一個比較運(yùn)算符:
$users = DB::table('users') ->whereColumn('updated_at', '>', 'created_at') ->get();
whereColumn
你也可以傳遞數(shù)組 用 and
運(yùn)算符鏈接:
$users = DB::table('users') ->whereColumn([ ['first_name', '=', 'last_name'], ['updated_at', '>', 'created_at'] ])->get();
參數(shù)分組
有時候你需要創(chuàng)建更高級的 where 子句,例如「where exists」或者嵌套的參數(shù)分組。 Laravel 的查詢構(gòu)造器也能夠處理這些。下面,讓我們看一個在括號中進(jìn)行分組約束的例子:
DB::table('users') ->where('name', '=', 'John') ->where(function ($query) { $query->where('votes', '>', 100) ->orWhere('title', '=', 'Admin'); }) ->get();
你可以看到,通過一個 Closure
寫入 where
方法構(gòu)建一個查詢構(gòu)造器 來約束一個分組。這個 Closure
接收一個查詢實例,你可以使用這個實例來設(shè)置應(yīng)該包含的約束。上面的例子將生成以下 SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')
{提示} 你應(yīng)該用
orWhere
調(diào)用這個分組,以避免應(yīng)用全局作用出現(xiàn)意外.
Where Exists 語句
whereExists
方法允許你使用 where exists
SQL 語句。 whereExists
方法接收一個 Closure
參數(shù),該 whereExists
方法接受一個 Closure 參數(shù),該閉包獲取一個查詢構(gòu)建器實例從而允許你定義放置在 exists
字句中查詢:
DB::table('users') ->whereExists(function ($query) { $query->select(DB::raw(1)) ->from('orders') ->whereRaw('orders.user_id = users.id'); }) ->get();
上述查詢將產(chǎn)生如下的 SQL 語句:
select * from users where exists ( select 1 from orders where orders.user_id = users.id)
JSON Where 語句
Laravel 也支持查詢 JSON 類型的字段(僅在對 JSON 類型支持的數(shù)據(jù)庫上)。目前,本特性僅支持 MySQL 5.7、PostgreSQL、SQL Server 2016 以及 SQLite 3.9.0 (with the JSON1 extension)。使用 ->
操作符查詢 JSON 數(shù)據(jù):
$users = DB::table('users') ->where('options->language', 'en') ->get();$users = DB::table('users') ->where('preferences->dining->meal', 'salad') ->get();
你也可以使用 whereJsonContains
來查詢 JSON 數(shù)組:
$users = DB::table('users') ->whereJsonContains('options->languages', 'en') ->get();
MySQL 和 PostgreSQL 的 whereJsonContains
可以支持多個值:
$users = DB::table('users') ->whereJsonContains('options->languages', ['en', 'de']) ->get();
你可以使用 whereJsonLength
來查詢 JSON 數(shù)組的長度:
$users = DB::table('users') ->whereJsonLength('options->languages', 0) ->get();$users = DB::table('users') ->whereJsonLength('options->languages', '>', 1) ->get();
Ordering, Grouping, Limit, & Offset
orderBy
orderBy
方法允許你通過給定字段對結(jié)果集進(jìn)行排序。 orderBy
的第一個參數(shù)應(yīng)該是你希望排序的字段,第二個參數(shù)控制排序的方向,可以是 asc
或 desc
:
$users = DB::table('users') ->orderBy('name', 'desc') ->get();
latest / oldest
latest
和 oldest
方法可以使你輕松地通過日期排序。它默認(rèn)使用 created_at
列作為排序依據(jù)。當(dāng)然,你也可以傳遞自定義的列名:
$user = DB::table('users') ->latest() ->first();
inRandomOrder
inRandomOrder
方法被用來將結(jié)果隨機(jī)排序。例如,你可以使用此方法隨機(jī)找到一個用戶。
$randomUser = DB::table('users') ->inRandomOrder() ->first();
groupBy / having
groupBy
和 having
方法可以將結(jié)果分組。 having
方法的使用與 where
方法十分相似:
$users = DB::table('users') ->groupBy('account_id') ->having('account_id', '>', 100) ->get();
你可以向 groupBy
方法傳遞多個參數(shù):
$users = DB::table('users') ->groupBy('first_name', 'status') ->having('account_id', '>', 100) ->get();
對于更高級的 having
語法,參見 havingRaw
方法。
skip / take
要限制結(jié)果的返回數(shù)量,或跳過指定數(shù)量的結(jié)果,你可以使用 skip
和 take
方法:
$users = DB::table('users')->skip(10)->take(5)->get();
或者你也可以使用 limit
和 offset
方法:
$users = DB::table('users') ->offset(10) ->limit(5) ->get();
條件語句
有時候你可能想要子句只適用于某個情況為真是才執(zhí)行查詢。例如你可能只想給定值在請求中存在的情況下才應(yīng)用 where
語句。 你可以通過使用 when
方法:
$role = $request->input('role');$users = DB::table('users') ->when($role, function ($query, $role) { return $query->where('role_id', $role); }) ->get();
when
方法只有在第一個參數(shù)為 true
的時候才執(zhí)行給的的閉包。如果第一個參數(shù)為 false
,那么這個閉包將不會被執(zhí)行
你可以傳遞另一個閉包作為 when
方法的第三個參數(shù)。 該閉包會在第一個參數(shù)為 false
的情況下執(zhí)行。為了說明如何使用這個特性,我們來配置一個查詢的默認(rèn)排序:
$sortBy = null;$users = DB::table('users') ->when($sortBy, function ($query, $sortBy) { return $query->orderBy($sortBy); }, function ($query) { return $query->orderBy('name'); }) ->get();
插入
查詢構(gòu)造器還提供了 insert
方法用于插入記錄到數(shù)據(jù)庫中。 insert
方法接收數(shù)組形式的字段名和字段值進(jìn)行插入操作:
DB::table('users')->insert( ['email' => 'john@example.com', 'votes' => 0]);
你甚至可以將數(shù)組傳遞給 insert
方法,將多個記錄插入到表中
DB::table('users')->insert([ ['email' => 'taylor@example.com', 'votes' => 0], ['email' => 'dayle@example.com', 'votes' => 0] ]);
自增 ID
如果數(shù)據(jù)表有自增 ID ,使用 insertGetId
方法來插入記錄并返回 ID 值
$id = DB::table('users')->insertGetId( ['email' => 'john@example.com', 'votes' => 0] );
{注意} 當(dāng)使用 PostgreSQL 時,
insertGetId
方法將默認(rèn)把id
作為自動遞增字段的名稱。如果你要從其他「序列」來獲取 ID ,則可以將字段名稱作為第二個參數(shù)傳遞給insertGetId
方法。
更新
當(dāng)然, 除了插入記錄到數(shù)據(jù)庫中,查詢構(gòu)造器也可以通過 update
方法更新已有的記錄。 update
方法和 insert
方法一樣,接受包含要更新的字段及值的數(shù)組。你可以通過 where
子句對 update
查詢進(jìn)行約束:
DB::table('users') ->where('id', 1) ->update(['votes' => 1]);
更新或者新增
有時您可能希望更新數(shù)據(jù)庫中的現(xiàn)有記錄,或者如果不存在匹配記錄則創(chuàng)建它。 在這種情況下,可以使用 updateOrInsert
方法。 updateOrInsert
方法接受兩個參數(shù):一個用于查找記錄的條件數(shù)組,以及一個包含要更該記錄的鍵值對數(shù)組。
updateOrInsert
方法將首先嘗試使用第一個參數(shù)的鍵和值對來查找匹配的數(shù)據(jù)庫記錄。 如果記錄存在,則使用第二個參數(shù)中的值去更新記錄。 如果找不到記錄,將插入一個新記錄,更新的數(shù)據(jù)是兩個數(shù)組的集合:
DB::table('users') ->updateOrInsert( ['email' => 'john@example.com', 'name' => 'John'], ['votes' => '2'] );
更新 JSON 字段
更新 JSON 字段時,你可以使用 ->
語法訪問 JSON 對象中相應(yīng)的值,此操作只能支持 MySQL 5.7+:
DB::table('users') ->where('id', 1) ->update(['options->enabled' => true]);
自增與自減
查詢構(gòu)造器還為給定字段的遞增或遞減提供了方便的方法。此方法提供了一個比手動編寫 update
語句更具表達(dá)力且更精練的接口。
這兩種方法都至少接收一個參數(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']);
刪除
查詢構(gòu)造器也可以使用 delete
方法從表中刪除記錄。 在使用 delete
前,可以添加 where
子句來約束 delete
語法:
DB::table('users')->delete(); DB::table('users')->where('votes', '>', 100)->delete();
如果你需要清空表,你可以使用 truncate
方法,它將刪除所有行,并重置自增 ID 為零:
DB::table('users')->truncate();
悲觀鎖
查詢構(gòu)造器也包含一些可以幫助你在 select
語法上實現(xiàn)「悲觀鎖定」的函數(shù)。若想在查詢中實現(xiàn)一個「共享鎖」, 你可以使用 sharedLock
方法。 共享鎖可防止選中的數(shù)據(jù)列被篡改,直到事務(wù)被提交為止 :
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
或者,你可以使用 lockForUpdate
方法。使用 「update」鎖可避免行被其它共享鎖修改或選?。?/p>
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();