數(shù)據(jù)庫 —— 查詢構建器

1、簡介

數(shù)據(jù)庫。查詢構建器可以用于執(zhí)行應用中大部分數(shù)據(jù)庫操作，并且能夠在支持的所有數(shù)據(jù)庫系統(tǒng)上工作。

注意：Laravel查詢構建器使用PDO參數(shù)綁定來避免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]);
    }
}

和原生查詢一樣，get方法返回結果集的數(shù)據(jù)組，其中每一個結果都是PHP對象的StdClass實例。你可以像訪問對象的屬性一樣訪問列的值：

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

2.2 從一張表中獲取一行/一列

如果你只是想要從數(shù)據(jù)表中獲取一行數(shù)據(jù)，可以使用first方法，該方法將會返回單個StdClass對象：

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

2.3 從一張表中獲取組塊結果集

如果你需要處理成千上百條數(shù)據(jù)庫記錄，可以考慮使用chunk方法，該方法一次獲取結果集的一小塊，然后填充每一小塊數(shù)據(jù)到要處理的閉包，該方法在編寫處理大量數(shù)據(jù)庫記錄的Artisan命令的時候非常有用。比如，我們可以將處理全部users表數(shù)據(jù)處理成一次處理100記錄的小組塊：

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

你可以通過從閉包函數(shù)中返回false來中止組塊的運行：

DB::table('users')->chunk(100, function($users) {
    // 處理結果集...
    return false;
});

2.4 獲取數(shù)據(jù)列值列表

如果想要獲取包含單個列值的數(shù)組，可以使用lists方法，在本例中，我們獲取所有title的數(shù)組：

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

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

在還可以在返回數(shù)組中為列值指定更多的自定義鍵（該自定義鍵必須是該表的其它字段列名，否則會報錯）：

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

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

2.5 聚合函數(shù)

隊列構建器還提供了很多聚合方法，比如count,?max,?min,?avg, 和?sum，你可以在構造查詢之后調用這些方法：

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

當然，你可以聯(lián)合其它查詢字句和聚合函數(shù)來構建查詢：

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

3、查詢（Select）

3.1 指定查詢子句

當然，我們并不總是想要獲取數(shù)據(jù)表的所有列，使用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注入。想要創(chuàng)建一個原生表達式，可以使用?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方法的第一次參數(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();

4.2 左連接

如果你是想要執(zhí)行“左連接”而不是“內連接”，可以使用leftJoin方法。該方法和join方法的使用方法一樣：

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

4.3 高級連接語句

你還可以指定更多的高級連接子句，傳遞一個閉包到join方法作為該方法的第2個參數(shù)，該閉包將會返回允許你指定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、聯(lián)合（Union）

查詢構建器還提供了一條“聯(lián)合”兩個查詢的快捷方式，比如，你要創(chuàng)建一個獨立的查詢，然后使用union方法將其和第二個查詢進行聯(lián)合：

$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最基本的方法需要三個參數(shù)，第一個參數(shù)是列名，第二個參數(shù)是一個數(shù)據(jù)庫系統(tǒng)支持的任意操作符，第三個參數(shù)是該列要比較的值。

例如，下面是一個驗證“votes”列的值是否等于100的查詢：

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

為了方便，如果你只是簡單比較列值和給定數(shù)值是否相等，可以將數(shù)值直接作為where方法的第二個參數(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();

6.2 or

你可以通過方法鏈將多個where約束鏈接到一起，也可以添加or子句到查詢，orWhere方法和where方法接收參數(shù)一樣：

$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方法驗證給定列的值是否在給定數(shù)組中：

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

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

$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 參數(shù)分組

有時候你需要創(chuàng)建更加高級的where子句比如”where exists“或者嵌套的參數(shù)分組。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 existSQL子句，whereExists方法接收一個閉包參數(shù)，該閉包獲取一個查詢構建器實例從而允許你定義放置在”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的第一個參數(shù)應該是你希望排序的列，第二個參數(shù)控制著排序的方向——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

想要限定查詢返回的結果集的數(shù)目，或者在查詢中跳過給定數(shù)目的結果，可以使用skip和take方法：

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

8、插入（Insert）

查詢構建器還提供了insert方法來插入記錄到數(shù)據(jù)表。insert方法接收數(shù)組形式的列名和值進行插入操作：

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

你甚至可以一次性通過傳入多個數(shù)組來插入多條記錄，每個數(shù)組代表要插入數(shù)據(jù)表的記錄：

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

8.1 自增ID

如果數(shù)據(jù)表有自增ID，使用insertGetId方法來插入記錄將會返回ID值：

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

注意：當使用PostgresSQL時insertGetId方法默認自增列被命名為id，如果你想要從其他”序列“獲取ID，可以將序列名作為第二個參數(shù)傳遞到insertGetId方法。

9、更新（Update）

當然，除了插入記錄到數(shù)據(jù)庫，查詢構建器還可以通過使用update方法更新已有記錄。update方法和insert方法一樣，接收列和值的鍵值對數(shù)組包含要更新的列，你可以通過where子句來對update查詢進行約束：

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

9.1 增加/減少

查詢構建器還提供了方便增減給定列名數(shù)值的方法。相較于編寫update語句，這是一條捷徑，提供了更好的體驗和測試接口。

這兩個方法都至少接收一個參數(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']);

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語句中實現(xiàn)”悲觀鎖“。可以在查詢中使用sharedLock方法從而在運行語句時帶一把”共享鎖“。共享鎖可以避免被選擇的行被修改直到事務提交：

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

此外你還可以使用lockForUpdate方法?！眆or update“鎖避免選擇行被其它共享鎖修改或刪除：

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

擴展閱讀 1：實例教程 —— 使用查詢構建器對數(shù)據(jù)庫進行增刪改查
擴展閱讀?2：實例教程 —— 使用查詢構建器實現(xiàn)對數(shù)據(jù)庫的高級查詢