* * * * *
[TOC]
## 簡介
Laravel 的數據庫查詢構造器為創建和運行數據庫查詢提供了一個方便的接口。它能用來執行應用程序中的大部分數據庫操作,且可在所有支持的數據庫系統上運行。
Laravel 的查詢構造器使用 PDO 參數綁定來保護您的應用程序免受 SQL 注入攻擊。因此沒有必要清理作為綁定傳遞的字符串。
## 獲取結果
#### 從數據表中獲取所有行
你可以?`DB`?facade 上使用?`table`?方法來開始查詢。該?`table`?方法為給定的表返回一個查詢構造器實例,允許你在查詢上鏈式調用更多的約束,最后使用?`get`?方法獲取結果:
~~~
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\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]);
}
}
~~~
該?`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`?方法。在下面的例子中,我們將獲取角色表中標題的集合:
~~~
$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;
}
~~~
### 分塊結果
如果你需要處理數千條數據庫記錄, 可以考慮使用?`chunk`?方法。該方法每次只取出一小塊結果,并將取出的結果傳遞給?`閉包`?處理。這對于編寫數千條記錄的?[Artisan 命令](http://www.hmoore.net/tonyyu/laravel_5_6/786238)?而言是非常有用的。例如,一次處理?`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;
});
~~~
### 聚合
查詢構造器還提供了各種聚合方法,例如?`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');
~~~
#### 確定記錄是否存在
不要使用?`count`?方法來確定是否存在與查詢相匹配的記錄,應該使用?`exists`?和?`doesntExist`?方法:
~~~
return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();
~~~
## 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();
~~~
## 原生表達式
有時候你可能需要在查詢中使用原生表達式。創建一個原生表達式, 你可以使用?`DB::raw`?方法:
~~~
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();
~~~
> {note} 原生表達式將會被當做字符串注入到查詢中,因此你應該小心使用,避免創建 SQL 注入漏洞。
### 原生方法
可以使用以下的方法代替?`DB::raw`?將原生表達式插入查詢的各個部分。
#### `selectRaw`
`selectRaw`?方法可以用來代替?`select(DB::raw(...))`。這個方法的第二個參數接受一個可選的綁定參數數組:
~~~
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();
~~~
#### `whereRaw / orWhereRaw`
可以使用?`whereRaw`?和?`orWhereRaw`?方法將原生的?`where`?注入到你的查詢中。這些方法接受一個可選的綁定數組作為他們的第二個參數:
~~~
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();
~~~
#### `havingRaw / orHavingRaw`
`havingRaw`?和?`orHavingRaw`?方法可用于將原生字符串設置為?`having`?語句的值:
~~~
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
~~~
#### `orderByRaw`
`orderByRaw`?方法可用于將原生字符串設置為?`order by`?子句的值:
~~~
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();
~~~
## Joins
#### Inner Join 語句
查詢構造器也可編寫 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 語句
如果你想使用「左連接」代替「內連接」,使用?`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();
~~~
## Unions
查詢構造器還提供了將兩個查詢「聯合」起來的快捷方式。比如,你可以先創建一個查詢,然后使用?`union`?方法將其和第二個查詢進行聯合:
~~~
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
~~~
> {tip}?`unionAll`?方法也是可用的,并且和?`union`?方法用法相同。
## 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 語句
你可以一起鏈式調用 where,也可以在查詢中添加?`or`?語句。?`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 / whereTime**
`whereDate`?方法用于比較字段的值和日期:
~~~
$users = DB::table('users')
->whereDate('created_at', '2016-12-31')
->get();
~~~
`whereMonth`?方法用于比較字段的值與一年的特定月份:
~~~
$users = DB::table('users')
->whereMonth('created_at', '12')
->get();
~~~
`whereDay`?方法用于比較字段的值與一個月的特定日期:
~~~
$users = DB::table('users')
->whereDay('created_at', '31')
->get();
~~~
`whereYear`?方法用于比較字段的值與特定年份:
~~~
$users = DB::table('users')
->whereYear('created_at', '2016')
->get();
~~~
`whereTime`?用于將字段的值與特定的時間進行比較:
~~~
$users = DB::table('users')
->whereTime('created_at', '=', '11:20')
->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();
~~~
### 參數分組
有時候你需要創建更高級的 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')
~~~
### Where Exists 語句
`whereExists`?方法允許你編寫?`where exists`?SQL 語句。 該?`whereExists`?方法接受一個?`Closure`?參數,該閉包獲取一個查詢構建器實例從而允許你定義放置在 "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
)
~~~
### JSON Where 語句
Laravel 也支持查詢 JSON 類型的字段(僅在對 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();
~~~
## Ordering, Grouping, Limit, & Offset
#### orderBy
`orderBy`?方法允許你通過給定字段對結果集進行排序。?`orderBy`?的第一個參數應該是你希望排序的字段,第二個參數控制排序的方向,可以是?`asc`?或?`desc`:
~~~
$users = DB::table('users')
->orderBy('name', 'desc')
->get();
~~~
#### latest / oldest
`latest`?和?`oldest`?方法允許你通過日期對結果進行排序。默認情況下,結果集根據?`created_at`?列進行排序。或者,你可以按照你想要排序的字段作為字段名傳入:
~~~
$user = DB::table('users')
->latest()
->first();
~~~
#### inRandomOrder
`inRandomOrder`?方法可以將查詢結果隨機排序。例如, 你可以使用這個方法獲取一個隨機用戶:
~~~
$randomUser = DB::table('users')
->inRandomOrder()
->first();
~~~
#### groupBy / having
`groupBy`?和?`having`?方法對查詢結果進行分組。?`having`?方法的用法與?`where`?方法類似:
~~~
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();
~~~
可以將多個參數傳遞給?`groupBy`?方法,按多個字段進行分組:
~~~
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();
~~~
關于?`having`?更高級的用法,請查看?[`havingRaw`](http://www.hmoore.net/tonyyu/laravel_5_6/786261#_172)?方法。
#### skip / take
想要限定查詢返回的結果集的數目, 或者在查詢中跳過給定數目的結果,可以使用?`skip`?和?`take`?方法:
~~~
$users = DB::table('users')->skip(10)->take(5)->get();
~~~
或者,你也可以使用?`limit`?和?`offset`?方法:
~~~
$users = DB::table('users')
->offset(10)
->limit(5)
->get();
~~~
## 條件語句
有時你可能想要子句只適用于某個情況為真時才執行查詢。例如,你可能只想給定值在請求中存在的情況下才應用?`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`?的時候才執行給定閉包。 如果第一個參數為?`false`,那么這個閉包將不會被執行。
你可以傳遞另一個閉包作為?`when`?方法的第三個參數。該閉包會在第一個參數為?`false`?的情況下執行。為了演示這個特性如何使用,我們來配置一個查詢的默認排序:
~~~
$sortBy = null;
$users = DB::table('users')
->when($sortBy, function ($query) use ($sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();
~~~
## 插入
查詢構造器還提供了?`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`?方法。
## 更新
當然,除了插入記錄到數據庫中,查詢構造器也可通過?`update`?方法更新已有的記錄。?`update`?方法和?`insert`方法一樣,接受包含要更新的字段及值的數組。 你可以通過?`where`?子句對?`update`?查詢進行約束:
~~~
DB::table('users')
->where('id', 1)
->update(['votes' => 1]);
~~~
### 更新 JSON 字段
更新 JSON 字段時,你可以使用?`->`?語法訪問 JSON 對象上相應的值,該操作只能用于支持 JSON 字段類型的數據庫:
~~~
DB::table('users')
->where('id', 1)
->update(['options->enabled' => true]);
~~~
### 自增與自減
查詢構造器還為給定字段的遞增或遞減提供了方便的方法。 此方法提供了一個比手動編寫?`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']);
~~~
## Deletes
查詢構造器也可以使用?`delete`?方法從數據表中刪除記錄。在使用?`delete`?前,可添加?`where`?子句來約束?`delete`?語法:
~~~
DB::table('users')->delete();
DB::table('users')->where('votes', '>', 100)->delete();
~~~
如果你需要清空表,你可以使用?`truncate`?方法,這將刪除所有行,并重置自增 ID 為零:
~~~
DB::table('users')->truncate();
~~~
## 悲觀鎖
查詢構造器也包含一些可以幫助你在?`select`?語法上實現 「悲觀鎖定」的函數。若想在查詢中實現一個「共享鎖」,你可以使用?`sharedLock`?方法。共享鎖可防止選中的數據列被篡改,直到事務被提交為止 :
~~~
DB::table('users')->where('votes', '>', 100)->sharedLock()->get();
~~~
另外,你也可以使用?`lockForUpdate`?方法。使用「更新」鎖可避免行被其它共享鎖修改或選取:
~~~
DB::table('users')->where('votes', '>', 100)->lockForUpdate()->get();
~~~
- 前言
- 翻譯說明
- 發行說明
- 升級指南
- 貢獻導引
- 入門指南
- 安裝
- 配置信息
- 文件夾結構
- Homestead
- Valet
- 部署
- 核心架構
- 請求周期
- 服務容器
- 服務提供者
- Facades
- Contracts
- 基礎功能
- 路由
- 中間件
- CSRF 保護
- 控制器
- 請求
- 響應
- 視圖
- URL
- Session
- 表單驗證
- 錯誤
- 日志
- 前端開發
- Blade 模板
- 本地化
- 前端指南
- 編輯資源 Mix
- 安全相關
- 用戶認證
- Passport OAuth 認證
- 用戶授權
- 加密解密
- 哈希
- 重置密碼
- 綜合話題
- Artisan 命令行
- 廣播系統
- 緩存系統
- 集合
- 事件系統
- 文件存儲
- 輔助函數
- 郵件發送
- 消息通知
- 擴展包開發
- 隊列
- 任務調度
- 數據庫
- 快速入門
- 查詢構造器
- 分頁
- 數據庫遷移
- 數據填充
- Redis
- Eloquent ORM
- 快速入門
- 模型關聯
- Eloquent 集合
- 修改器
- API 資源
- 序列化
- 測試相關
- 快速入門
- HTTP 測試
- 瀏覽器測試 Dusk
- 數據庫測試
- 測試模擬器
- 官方擴展包
- Cashier 交易工具包
- Envoy 部署工具
- Horizon
- Scout 全文搜索
- Socialite 社會化登錄