數(shù)據(jù)庫用法(基于Laravel數(shù)據(jù)庫組件)

獲取所有行

<?php
namespace app\controller;

use support\Request;
use support\Db;

class UserController
{
    public function all(Request $request)
    {
        $users = Db::table('users')->get();
        return view('user/all', ['users' => $users]);
    }
}

獲取指定列

$users = Db::table('user')->select('name', 'email as user_email')->get();

獲取一行

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

獲取一列

$titles = Db::table('roles')->pluck('title');

指定id字段的值作為索引

$roles = Db::table('roles')->pluck('title', 'id');

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

獲取單個(gè)值(字段)

$email = Db::table('users')->where('name', 'John')->value('email');

去重

$email = Db::table('user')->select('nickname')->distinct()->get();

分塊結(jié)果

如果你需要處理成千上萬條數(shù)據(jù)庫記錄，一次性讀取這些數(shù)據(jù)會(huì)很耗時(shí)，并且容易導(dǎo)致內(nèi)存超限，這時(shí)你可以考慮使用?chunkById?方法。該方法一次獲取結(jié)果集的一小塊，并將其傳遞給?閉包?函數(shù)進(jìn)行處理。例如，我們可以將全部?users 表數(shù)據(jù)切割成一次處理 100 條記錄的一小塊：

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

你可以通過在 閉包 中返回 false 來終止繼續(xù)獲取分塊結(jié)果。

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    // Process the records...

    return false;
});

注意：不要在回調(diào)里刪除數(shù)據(jù)，那樣可能會(huì)導(dǎo)致有些記錄沒有包含在結(jié)果集中

聚合

查詢構(gòu)造器還提供了各種聚合方法，比如 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');

判斷記錄是否存在

return Db::table('orders')->where('finalized', 1)->exists();
return Db::table('orders')->where('finalized', 1)->doesntExist();

原生表達(dá)式

原型

selectRaw($expression, $bindings = [])

有時(shí)候你可能需要在查詢中使用原生表達(dá)式。你可以使用 selectRaw() 創(chuàng)建一個(gè)原生表達(dá)式：

$orders = Db::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

同樣的，還提供了 whereRaw() orWhereRaw() havingRaw() orHavingRaw() orderByRaw() groupByRaw() 原生表達(dá)式方法。

Db::raw($value)也用于創(chuàng)建一個(gè)原生表達(dá)式，但是它沒有綁定參數(shù)功能，使用時(shí)需要小心SQL注入問題。

$orders = Db::table('orders')
                ->select('department', Db::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

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();

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

// rightJoin
$users = Db::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

// crossJoin    
$users = Db::table('sizes')
            ->crossJoin('colors')
            ->get();

Union 語句

$first = Db::table('users')
            ->whereNull('first_name');

$users = Db::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Where 語句

原型

where($column, $operator = null, $value = null)

第一個(gè)參數(shù)是列名，第二個(gè)參數(shù)是任意一個(gè)數(shù)據(jù)庫系統(tǒng)支持的運(yùn)算符，第三個(gè)是該列要比較的值

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

// 當(dāng)運(yùn)算符為 等號(hào) 時(shí)可省略，所以此句表達(dá)式與上一個(gè)作用相同
$users = Db::table('users')->where('votes', 100)->get();

$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();

orWhere 方法和 where 方法接收的參數(shù)一樣：

$users = Db::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

你可以傳一個(gè)閉包給 orWhere 方法作為第一個(gè)參數(shù)：

// SQL: select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
$users = Db::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

whereBetween / orWhereBetween 方法驗(yàn)證字段值是否在給定的兩個(gè)值之間：

$users = Db::table('users')
           ->whereBetween('votes', [1, 100])
           ->get();

whereNotBetween / orWhereNotBetween 方法驗(yàn)證字段值是否在給定的兩個(gè)值之外：

$users = Db::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

whereIn / whereNotIn / orWhereIn / orWhereNotIn 方法驗(yàn)證字段的值必須存在指定的數(shù)組里：

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

whereNull / whereNotNull / orWhereNull / orWhereNotNull 方法驗(yàn)證指定的字段必須是 NULL：

$users = Db::table('users')
                    ->whereNull('updated_at')
                    ->get();

whereNotNull 方法驗(yàn)證指定的字段必須不是 NULL：

$users = Db::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

whereDate / whereMonth / whereDay / whereYear / whereTime 方法用于比較字段值與給定的日期：

$users = Db::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

whereColumn / orWhereColumn 方法用于比較兩個(gè)字段的值是否相等：

$users = Db::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

// 你也可以傳入一個(gè)比較運(yùn)算符
$users = Db::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

// whereColumn 方法也可以傳遞數(shù)組
$users = Db::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

參數(shù)分組

// select * from users where name = 'John' and (votes > 100 or title = 'Admin')
$users = Db::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

whereExists

// select * from users where exists ( select 1 from orders where orders.user_id = users.id )
$users = Db::table('users')
           ->whereExists(function ($query) {
               $query->select(Db::raw(1))
                     ->from('orders')
                     ->whereRaw('orders.user_id = users.id');
           })
           ->get();

orderBy

$users = Db::table('users')
                ->orderBy('name', 'desc')
                ->get();

隨機(jī)排序

$randomUser = Db::table('users')
                ->inRandomOrder()
                ->first();

隨機(jī)排序會(huì)對(duì)服務(wù)器性能影響很大，不建議使用

groupBy / having

$users = Db::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
// 你可以向 groupBy 方法傳遞多個(gè)參數(shù)
$users = Db::table('users')
                ->groupBy('first_name', 'status')
                ->having('account_id', '>', 100)
                ->get();

offset / limit

$users = Db::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

插入

插入單條

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]
]);

自增 ID

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

注意：當(dāng)使用 PostgreSQL 時(shí)，insertGetId?方法將默認(rèn)把?id?作為自動(dòng)遞增字段的名稱。如果你要從其他「序列」來獲取 ID ，則可以將字段名稱作為第二個(gè)參數(shù)傳遞給?insertGetId?方法。

更新

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

更新或新增

有時(shí)您可能希望更新數(shù)據(jù)庫中的現(xiàn)有記錄，或者如果不存在匹配記錄則創(chuàng)建它：

Db::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

updateOrInsert?方法將首先嘗試使用第一個(gè)參數(shù)的鍵和值對(duì)來查找匹配的數(shù)據(jù)庫記錄。 如果記錄存在，則使用第二個(gè)參數(shù)中的值去更新記錄。 如果找不到記錄，將插入一個(gè)新記錄，新記錄的數(shù)據(jù)是兩個(gè)數(shù)組的集合。

自增 & 自減

這兩種方法都至少接收一個(gè)參數(shù)：需要修改的列。第二個(gè)參數(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']);

刪除

Db::table('users')->delete();

Db::table('users')->where('votes', '>', 100)->delete();

如果你需要清空表，你可以使用 truncate 方法，它將刪除所有行，并重置自增 ID 為零：

Db::table('users')->truncate();

事務(wù)

參見數(shù)據(jù)庫事務(wù)

悲觀鎖

查詢構(gòu)造器也包含一些可以幫助你在?select?語法上實(shí)現(xiàn)「悲觀鎖定」的函數(shù)。若想在查詢中實(shí)現(xiàn)一個(gè)「共享鎖」， 你可以使用?sharedLock?方法。 共享鎖可防止選中的數(shù)據(jù)列被篡改，直到事務(wù)被提交為止:

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

或者，你可以使用?lockForUpdate?方法。使用 「update」鎖可避免行被其它共享鎖修改或選?。?/p>

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

調(diào)試

你可以使用?dd?或者?dump?方法輸出查詢結(jié)果或者 SQL 語句。 使用?dd?方法可以顯示調(diào)試信息，然后停止執(zhí)行請(qǐng)求。?dump?方法同樣可以顯示調(diào)試信息，但是不會(huì)停止執(zhí)行請(qǐng)求：

Db::table('users')->where('votes', '>', 100)->dd();
Db::table('users')->where('votes', '>', 100)->dump();

注意
調(diào)試需要安裝symfony/var-dumper,命令為composer require symfony/var-dumper